ProC ESQL and Bind Variables for multiple queries ?

We have a system where there's a fixed list of potential queries (up to 200) with run-time supplied parameters. 'Till now we've just pumped the queries in using single strings of dynamic SQL, and now we'd like to use bind variables to supply the parms at run time. We use a multi-threaded environment with several threads having open connections to the database, and receiving requests from other protocol-handling threads/processes. The queries themselves are built from "metadata" in some database table, read at startup.

Questions:

• to get full use of it, presumably we'd have to just parse once and use those parsed queries for requests coming in. That seems a bit inconvenient right now because of how the thing is structured. Is there still advantages to using Dynamic SQL method 2, where the query is parsed every time it's executed? We've been warned our (non-bind var) method could lead to "latch contention" problems.
• The only relevant ESQL syntax would seem to be:

get_query_string ( myquery )

   // e.g. myquery is "select name from cars where price < :p1 "

EXEC SQL PREPARE S from :myquery;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C USING :myprice;

   Problem is if we want to process 200 "generic" queries and    store the prepare'd queries, how can we refer to the statements?    The "oracle identifer" (e.g. 'S' above) can't be an array    name or anything.

e.g.
EXEC SQL PREPARE S[i] from :myquery;

would be a lot easier. Is there anything we can do for this? It would be virtually impossible and horrible to have separate SQL statements for every different query, since the whole idea is that the queries and handling is generic, from metadata. We could have a separate thread for each possible query, but that isn't very nice for multi-threaded access, and we'd have to have hundreds of threads.

Is there any way to do this in ESQL (rather not use OCI for portability reasons).

cheers.

--
Ken Tough Received on Sat May 08 1999 - 05:23:14 CDT