Re: Stored procedures

Jack Parker wrote: > > A previous post mentioned Micheal Gonzales book on Stored Procedures. > Here is some other stuff that should be added to the FAQ - some of which > is direct learning (i.e. ripoff) from Micheal. > > 1 - DONT use them EVERYWHERE. > It is not always apparent that there is an SPL/trigger combo > out there doing work - makes debugging fun. > > 2 - They can improve performance because they do the work at the > engine and return only the results vs returning all of the raw > data and letting the client process it. Lowers network traffic. > > 3 - They are ideal for controlling access to tables ala > INSERT INTO foo ........ Because the SPL keeps the permissions > of it's creator - so Joe Bloe who has no insert permission > can insert using the SPL. This means that you have ONE place > where all of that is done - one place to debug and one place > to ensure that all requirements are met. > > 4 - They are nice to do recursion like when you want to explode a > bill of materials. Something that 4gl cannot do. > > 5 - They are cached in memory - so the next user who invokes one gets > it faster. This also means that you can drop an SPL and recreate > it and SOMETIMES the old SPL is still out there in memory. A > royal pain - it helps if you can tell which version is in use. > > 6 - SPL has no substring capability, no ability to read a returned > value from a system call. CURRENT is evaluated when the SPL > starts - never again - so you cannot time an SPL within itself, > you must have a calling program time it. > > 7 - You can access the SQLCA record using dbinfo: > > LET serial_no = dbinfo("sqlca.sqlerrd2"); See documentation > for other things you can use dbinfo for - I also may have the > syntax wrong for the string - all my manuals are packed up. > > 8 - SPL became available with 5.0. Triggers with 5.1. Together > they are a very versatile tool for handling things like a > cascading (or snowball for all you COBOL folks) delete. > > 9 - DO NOT, REPEAT, DO NOT use them for every little thing in the > world. You will create a nightmare to untangle. Rather do a > careful plan that revolves around why you want them - not just > because they are cool. > > Anything I left out gals/guys? Micheal? Are you with us? Yes. You miss-spelled 'can' in point 4, it does not end in 'not'. ;-) 4GL functions do support recursion, and I have used 4GL to print bill of material type reports. My rule for 4GL has always been: If it's related to a database, 4GL can do anything. Still waiting for someone to prove me wrong. ;-) Cheers, -- Mark. +-------------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +------------------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work... force it! | |Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! | |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+------------------------------------------------+