Executing a stored procedure over ODBC

Question

I have an application that allows the user to enter an SQL string with a placeholder for certain values my application produces. The application will the replace the placeholders with values and execute the SQL string through various database backends.

For the ODBC backend, I call SQLExecDirect() on the SQL strin which works nicely on regular queries, but fails for stored procedures with parameters.

Is there a simple way to extend this mechanism to support stored procedures ? A certain way how the SQL must be written ?

The only thing I can think of right now is to start parsing the SQL string and call SQLBindParameter() n times if it conatains a "call". But parsing SQL is tricky.

Any ideas ?

Working SQL example: SELECT columnA from foo where columnB = '%placeholder'

Non-working SQL: CALL StoredFoo('%placeholder')

Answer 1

How to call stored procedures (ODBC):

To run a procedure as an RPC

1. Construct a SQL statement that uses the ODBC CALL escape sequence. The statement uses parameter markers for each input, input/output, and output parameter, and for the procedure return value (if any):

   {? = CALL procname (?,?)}

2. Call SQLBindParameter for each input, input/output, and output parameter, and for the procedure return value (if any).

3. Execute the statement with SQLExecDirect.

Otherwise you need to execute the procedure as an ordinary batch (not an RPC call), ie. you need to run the batch:

EXEC procname @param1, @param2, @param3...;