Re: Stored Procedures or Prepared 4GL?

Steve Proctor wrote: > I'm about to embark on a 4gl based project which will have the necessity for library > routines which perform SELECTS's on multiple tables, along with other logic based on the > results of the selects. The routines will return the data they found in the tables. > The tables will be in their own database. > > Does anyone have any comments on whether it would be better to use stored procedures as > the library routines or 4gl function libraries (with PREPAREd SQL)? > > I don't know whether it makes a difference, but one thing to bear in mind is that > multiple databases on perhaps different boxes (7.1 networking) may need to run these > "routines". > > Thanks in anticipation of any help I get from your answers. Up to version 5.xx I would stick to 4GL. Stored procedure performance was not so hot. After that you need to consider the maintenance aspect. If you are using a single development tool, then it will probably make life easier if ALL code is developed in that language, albeit in library functions. If you intend to use a number of front-end tools to access the same database, then using stored procedures allows you to centralise the business rules in the database. This is the main use of stored procedures and triggers; to attach the business rules to the database directly. This means that any and all access comples with those rules. Stored procedures and triggers are often used to implement client/server applications. This places the processing on the server. But beware of taking this theme too far. For instance the stored procedure cache has a default size of 50. Although this can be changed with the configuration parameter PC_POOLSIZE. The general rule to consider is; use stored procedures to implement business rules. If you need more than that then implement it in a library function. Hope that helps, -- Mark. +-------------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | | |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! | +------------------------+------------------------------------------------+