Stored procedure best practices
Has anybody developed a set of project or organization standards and common routines for Informix stored procedures? My group is beginning to use SPs more extensively and I want to avoid reinventing the wheel. I am particularly interested in exception handling, effective debugging techniques, naming conventions, undocumented features (as distinct from 'bugs', but these latter, too), and general do's and don'ts. My group is developing in Java and using IDS 7.23.UC1 and will be moving to 7.30 soon. Any help would be greatly appreciated.
Hi Red, If you are beginning to use stored procedures extensively, you would be interested to try SPL Workstation. This is the best visual development and debugging environment for stored procs and triggers. It features the only interactive SPL Debugger that allows to execute procs and triggers line-by-line, set breakpoints, watch and edit variable values and more. You can use debugger with any client tool and development environment including Java. SPL Workstation also features integrated color-coded SQL Editor, Object Explorer, SPL and SQL Dependencies Explorer, Reference Analyzer and other components that allows to save a lot of time on a server-side development. You can download a free evaluation copy from www.agsltd.com Hope it helps, Arkady red_valsen@yahoo.com wrote in message <37725372.934648CD@yahoo.com>... >Has anybody developed a set of project or organization standards and >common routines for Informix stored procedures? My group is beginning >to use SPs more extensively and I want to avoid reinventing the wheel. >I am particularly interested in exception handling, effective debugging >techniques, naming conventions, undocumented features (as distinct from >'bugs', but these latter, too), and general do's and don'ts. > >My group is developing in Java and using IDS 7.23.UC1 and will be moving >to 7.30 soon. > >Any help would be greatly appreciated. >
Arkady Ginzburg <ginzba@csi.com> wrote in message news:7kuvtb$p1t@chronicle.concentric.net... > If you are beginning to use stored procedures extensively, you would be > interested to try SPL Workstation. > This is the best visual development and debugging environment for stored > procs and triggers. It is a very questionable assertion -- Andrew Svikhnushin Inist Ltd. E-mail: san@inist.ru
If you develope large application with complex logic the best decision is using 3-tier architecture and implement business logic with something more powerful then SPL. SPL doesn't include dynamic SQL, string processing routine. Has very limited interface for system call. If you plan use temporary table you can encounter problem with multiuser. All SP's that refer to newly created temp table require regeneration of query plan at the moment of SP executing. It locks sysprocplan table. For 7.1x version for HP-UX 9 and SCO I've got errors due to damage of global variables. But I use stored procedures extensively. If you interesting I can sent you MS Windows executable for implementing of SQL scripts from client with integrated C-like preprocessor. Sorry for my English. -- Andrew Svikhnushin Inist Ltd. E-mail: san@inist.ru <red_valsen@yahoo.com> wrote in message news:37725372.934648CD@yahoo.com... > Has anybody developed a set of project or organization standards and > common routines for Informix stored procedures? My group is beginning > to use SPs more extensively and I want to avoid reinventing the wheel. > I am particularly interested in exception handling, effective debugging > techniques, naming conventions, undocumented features (as distinct from > 'bugs', but these latter, too), and general do's and don'ts. > > My group is developing in Java and using IDS 7.23.UC1 and will be moving > to 7.30 soon. > > Any help would be greatly appreciated. >
san wrote: > If you develope large application with complex logic the best decision is > But I use stored procedures extensively. > If you interesting I can sent you MS Windows executable for implementing of > SQL scripts from client with integrated C-like preprocessor. > As for me the initial question was not about environment where you could write your stored procedures it was about coding guidelines, about one year ago I was looking for them over internet, but all I found was C++, Java etc. guidelines but not SQL guidelines :( I can't believe that companies that uses SQL servers don't have it, why they can't make it available for public access ?! Sincerely, Alex