Re: Stored procedures

> > Hi all, > > I've been using Informix 4gl & SQL for 6 years now, and I'm ashamed to > admit that I've never used (or indeed heard of) stored procedures > until a few days ago. > > I've been playing with them now for a few days and I think they could > revolutionize my programming. > > Two questions for all you Informix experts, one is their anything I > should know about/any pitfalls before I start using these all over the > place, and secondly does anybody know of any good reference material > giving me more information on stored procedures & triggers. > > Any help would be much appreciated > > Thanks 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? cheers j. ________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA (x65388) jparker@boi.hp.com Currently on loan to CTSB/DTC ________________________________________________________________________ Outside of a dog a book is a man's best friend. Inside of a dog it's too dark to read. (Groucho Marx) ________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. ________________________________________________________________________