![]() |
|
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.
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
One year: $189
(includes all back issues since '99)
Renewals from $139
Two years: $289
Renewals from $239