Re: 4GL Calling Stored Procedures

Sujit.Pal@alltel.com wrote: > Steve > > Maybe I've not fully understood your question, but from what I > gather > you want a set of data from a stored procedure that you will > process > in a 4GL program. The way to do it is to make the procedure > RETURN the > values WITH RESUME and have a loop of some kind (FOR, WHILE, > FOREACH) > in the 4GL program that will call the SP repeatedly and get the > values. > > HTH > > Sujit Pal > 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. Thanks in advance for any help, --Steve