The SQL Answer Cowboy Answers!

by Andy Stapleton

Published 1999-04-26    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.

Click here to submit your SQL question to Andy.

Tom Foley: I understand that TS no longer recommend the use of {Prop:SQL}. What is the replacement and can you explain the rationale?

Cowboy: To my knowledge {Prop:SQL} is not going away but you have to be careful using it, which is why they don't recommend it. If you use {Prop:SQL} and NEXT() with a PUT() you must insure that you have the primary key fields in the SELECT statement.

The reason is that PUT and NEXT use the primary fields to insure the update of the record is accurate. If you don't provide this in the SELECT Statement both the PUT and NEXT will not be aware of the record position and subsequently give errant results or none at all. You can get around not using the primary in the Select but you will have to create your own Update Statements.

TS is redeveloping the file drivers for additional functionality, but I cannot discuss this yet.

Yeoh Eng Loke: I have read your seminar materials for Devcon 98 where a sample file was downloadable. In that particular file called 'Devcon.doc', all the examples of stored procedures and triggers are shown using SQL Anywhere. Do you have an equivalent in MS-SQL?

Cowboy: We are currently working on an MS-SQL project and from this we will strip out examples of these items with documentation. I have also entered into an agreement with George Willbanks [TS Resources Inc] to produce a set of CD-ROM training items. Between the example programs in the final release of the CCS Templates and the CD-Roms there should be quite a bit of information about these these database flavors.

The syntax of MS-SQL is different from the syntax of Sybase but there are similarities. Here is an example of MS-SQL trigger:

[Trigger Definition ]
Trigger checks the format type in the setup table to see 
if the user wants First then Last or Last then First, 
Updates the FULLNAME field to match.
 
 
CREATE TRIGGER Names_FullName ON Names
FOR INSERT, UPDATE
AS
DECLARE @NameFormat varchar(10);
SELECT @NameFormat = NameFormat 
  FROM SETUP WHERE setupsysid = 1;
IF UPDATE(Fname) OR UPDATE(Lname)
   IF @NameFormat = 'FirstLast'
      UPDATE NAMES SET FullName = Fname + ' ' + Lname 
    WHERE Names.NameSysID = 
    ANY(SELECT NameSysID FROM INSERTED)
    AND Fname IS NOT NULL AND Lname IS NOT NULL 
    AND Fname <> ' ' AND Lname <> ' '
   ELSE
      UPDATE Names SET FullName = Lname + ', ' + Fname 
    WHERE Names.NameSysID = 
    ANY(SELECT NameSysID FROM INSERTED)
    AND Fname IS NOT NULL AND Lname IS NOT NULL 
    AND Fname <> ' ' AND Lname <> ' '

The same trigger in Sybase is:

CREATE TRIGGER Names_FullName BEFORE INSERT UPDATE
   Order 1 ON Names
 REFERENCING OLD AS OLDSTUFF
   NEW AS NEWSTUFF;
BEGIN
DECLARE pNameFormat VARCHAR(10);
SELECT NameFormat INTO pNameFormat FROM SETUP WHERE SetupSysID = 1;
IF OldStuff.Fname <> NewStuff.Fname OR OldStuff.Lname <> NewStuff.Lname
   IF pNameFormat = 'FirstLast' THEN
        SET NewStuff.FullName = NewStuff.Fname||' '||NewStuff.Lname;
   ELSE
        SET NewStuff.FullName = NewStuff.Lname||' '||NewStuff.Fname;
   END IF;
 END IF;
END

Personally I think the Sybase code is cleaner and more readable than the MS-SQL version. Also with Sybase you have the option in the trigger to allow a WHEN condition which will eliminate two lines of code within the trigger and will also stop the execution if the WHEN condition is not met.

CREATE TRIGGER Names_FullName BEFORE INSERT, UPDATE
 ORDER 1 ON NAMES
 REFERENCING  OLD AS oldStuff NEW AS newStuff
 FOR EACH ROW
 WHEN(OldStuff.Fname <> NewStuff.Fname 
  OR OldStuff.Lname <> NewStuff.Lname)
 BEGIN
 DECLARE pNameFormat VARCHAR(10); 
 SELECT NameFormat INTO pNameFormat 
   FROM SETUP WHERE setupsysid = 1;
     IF pNameFormat = 'FirstLast' THEN
        SET NewStuff.FullName = NewStuff.Fname||' '||NewStuff.Lname;
     ELSE
        SET NewStuff.FullName = NewStuff.Lname||' '||NewStuff.Fname;
     END IF;
 END

All I did was move one line of code from the IF CLAUSE to the WHEN condition of the Trigger.

All I can promise you at this time is the more I work with MS-SQL 7.0 the better it feels even with the changes, so there will be more to come...

Austin Drum: I have been using SQL Anywhere 5.5. I now see that version 6 has been released. Also, I hear a lot of talk about MS SQL. What are your thoughts and experiences and would you recommend one above the other?

Cowboy: The newer versions of Sybase (6.0 and better) use symmetrical multiprocessing now. This means that each query or stored procedure will use all of the processors in your server. For the most part the individual would not necessarily know the difference, but in a larger environment you can see a significant increase in speed.

MS-SQL has definitely came a long way since 6.5, but I still think the language needs to mature a bit more. The documentation of methods and examples is not very instructive. I recommend you read The SQL Server 7 Developers Guide (ISBN 0-07-882548-2).

Both databases are quite capable of doing an excellent job , both for the Enterprise and personal use. MS-SQL is more expensive per seat and from a Clarion standpoint it's a bit harder to grasp the nuances. On the other hand the Sybase language is more like Clarion and easier to understand.

Click here to submit your SQL question to Andy.

Printer-friendly version

Copyright © 1999-2008 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