The SQL Answer Cowboy

by Andy Stapleton

Published 1999-07-13    Printer-friendly version

Andy "Cowboy" Stapleton is the acknowledged Clarion SQL guru and a regular presenter at Clarion conferences around the world. His company, Cowboy Computing Solutions, produces SQL templates and classes for Clarion.

en Castleberry: Can AS-400 files be accessed from a Clarion 5 Professional application, with the only addition being the AS-400 drivers?

Cowboy: From everything I gather this is true, as long as you have the interface from the PC to AS400. This used to be PC-Talk and was quite slow. Now a more native form of communication is available and speed has increased dramatically.

Before jumping into any client/Server arena check the communication layer associated with the systems. This includes any non-NT to NT/Windows platform. Some will perform quite well (usually those on TCPIP), others will run badly.

Scott Jordan: How do I force a field in the table to uppercase or capitalize a word? Declaring an attribute in the data dictionary does not seem to have any effect. I am using MS SQL 7

Cowboy: After searching everywhere, I finally asked a good friend Ben Williams, since he has been saddled with MS SQL for quite a while. He confirmed my suspicions. In MS SQL 6.5 or 7.0 field attributes are unavailable, and the only method to force upper case or capitalization is via a trigger or your program. This is a major shortcoming in MS SQL in my opinion.

Here is a trigger that will force uppercase on a Name/Address/City/State: 

CREATE TRIGGER  UppercaseAddress
ON  Names
FOR INSERT, UPDATE
AS
If Update(Fname) or Update(Lname) or update(Address) 
 or Update(State) or Update(City)
    update Names
    set LName = UPPER(Lname),
    Fname = UPPER(Fname),
    Address = Upper(Address),
    City  = Upper(City),
    State  =  Upper(State)
Where Namsysid = any(select Namesysid from Inserted) 
GO

Now you should also use UPPER on all your Clarion screens...

James Fortune: I was interested to read your comparison of Sybase and MS SQL, especially as I believe that the latter is derived from Version 3 of the former. My question follows on from this. In your informed view, how do Oracle and Informix fit into the picture? How would you rank these in terms of effectiveness? Especially in terms of ease of use with CW? I'm assuming that the backend platform is NT. But what about scalability? Is it still true that MS SQL will only work on NT whereas Oracle will work on anything including Linux? What about the others?

Cowboy: Yes, MS SQL does require NT. Another reason to prefer Sybase over each of these is that Sybase is scalable and also has versions for Unix and Linux. I can speak more on Oracle rather than Informix so here is my best answer.

Oracle and Informix are more of a mainframe type technology. Both are platform independent and expensive in cost and maintenance. Oracle can be difficult to say the least; a lot of the convenience we enjoy is lost in Oracle. At the moment the reference manuals that I have had to purchase to know the changes for Oracle 8.0 are somewhere around 30lbs in four books.

One of the pros of Oracle is it's quite scalable. You can continue with Oracle throughout terabytes of data with quite excellent performance. If you are going into a Oracle shop and working with Oracle, here is a list of books that I find essential:

Oracle 8 Tuning ISBN:1-57610-217-3

Oracle8 The Complete Reference (Oracle Press) ISBN: 0-07-8822406-0

Oracle8 DBA handbook ISBN: 0-07-882396-x

Printer-friendly version

Copyright © 1999-2009 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942