Re: 4GL Calling Stored Procedures

On Fri, 01 May 1998 14:07:09 -0500, Steven Mastandrea <stevem@cstech.com> wrote: >Actually, what I have is a stored procedure that takes parameters and >inserts records into multiple tables, depending on the parameter >values. We are using this stored procedure so that we can encapsulate >our business logic in the Stored Procedure, and because we have two >front-ends to the database, one in 4GL and the other via the web. By >putting all of the insert/business logic in the SPs, we do not have to >replicate the logic in both applications. > >For example, one SP takes several values for an inventory item. If the >quantity has changed for the inventory item, then the system inserts a >record into our audit table. The Stored Procedures work great from >DBACCESS, when you can run 'EXECUTE PROCEDURE sp_one(a,b,...) etc. >However, in 4GL, if you try and put in the EXECUTE PROCEDURE >sp_one(a,b,c) call, the compiler returns an error because EXECUTE is a >reserved word in 4GL. We also cannot use the select sp_one(a,b,c) from >systables where tabid=1 option, because our SP does inserts/updates, and >data manipulation is not allowed if it is called with the select .... >syntax. > >Has anyone done anything similar to this before? The thing we are >really trying to avoid if at all possible is dynamically creating the >SQL calls with the PREPARE / EXECUTE 4GL commands. This is what you have to do as 4GL can only execute prepared statements. There is no way of executing stored procedures directly. The 4GL language hasn't ever been updated to do that. No I don't quite see the big problem in this either. What we do is to place all the prepares (mostly with ? for parameters) in a separate function at the top of the source module. Then you can execute these statements as often as you like within the rest of the code. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)