Re: PROBLEMS ON STORED PROCEDURES (Procedure not found)

On May 7, 3:58pm, Ayhan Ergul wrote: > Subject: Re: PROBLEMS ON STORED PROCEDURES (Procedure not found) > The stored procedures seemed to work fine from dbaccess and from a > simple 4GL that did nothing more than calling a single stored procedure. > Omer's code had 15-20 cursors declared and I observed that the problem > disappeared when several of them were commented out. Also, when I moved the > cursor declaration of an EXECUTE PROCEDURE statement to a point just before > its use, that stored procedure executed without any problem (however it > would then fail on another). We were not able to locate the limit for the > number of cursor declarations in a 4GL module in the manuals. Even if we > did, we would prefer to get some "Too many cursor declarations" error > rather than "Procedure ... not found". > Ayhan Ergul Internet: <ergul@rorqual.cc.metu.edu.tr> >-- End of excerpt from Ayhan Ergul This may be an old problem that you are facing. It sounds very similar to a problem that I and others have had a couple of years ago. It boils down to an incompatibility between your version of 4gl (anything before 4.12 I believe) and the V5+ engines. Informix support is aware of the problem but so few people appear to have used SP's extensively with 4GL and the errors generated are so random that I suspect that many of thier frontline support people don't recognise it. I'm now a bit sketchy on the fine details but generally the problem goes like this: 4GL only allowed a 1 byte field to hold statement ID's returned by an engine. This means that you can only have <255 SQL statements prepared in your 4GL program. Normally this is plenty but SP's generate a statement ID for each SQL statement in the SP each time it runs. Informix therefore increased the statement ID in the engine to 4 bytes. If you run large SP's in 4GL code several times and then prepare another cursor the statement id (>255) returned by the engine is truncated by the 4GL. Further attempts to access this cursor fail with random errors because 4GL quotes the truncated statement ID to the engine which replies with an error from the statement that was quoted rather than the one the programmer requested. This often generates errors on permission, ownership, wrong variable lists, mis-typing, etc. There are two workarounds: 1) Upgrade to a modified version of 4GL which uses a 4byte statement ID. 2) Prepare and declare all cursors, SQL statements in 4GL before calling any SP's. This gives them statement ID's below 255 and after that it doesn't matter how big the number gets. The downside of this is that you cannot mix dynamic SQL coding with SP calls in 4GL programs. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!