CODEWITHSUNDEEP
sqldatabasems-accessodbcparameter-passing
QuestionOverflow
QuestionOverflow

Reputation: 679

How to execute a pre-defined query having parameters (i.e. a PARAMETERS declaration) in a Microsoft Access database over ODBC?

My simple question is as follows:

If I have a Microsoft Access database with a defined "query" in it (i.e. the kind of database object that MS Access calls a Query, just to avoid any ambiguity) defined to take parameters (using a PARAMETERS declaration inside its SQL definition) what is the correct SQL syntax to call it over an ODBC connection, including providing the parameter values?

EDIT / ADDITION:

I just noticed that it could be done by adding curly-braces around the entire CALL command, as follows:

{CALL myAccessQuery ('string1', 'string2', 'string3')}

This was actually exactly what they already did in the other related SO thread that I'm referring to here below, but I just thought that this was some C#-specific magic related to the prepared-statement nature of their SQL statement (using "?" in it), or some other peculiarity of their SQL library (I'm not using that language), so I previously ignored it.

Anyone who will explain what the curly-braces are, and why they allow for execution of more SQL commands than the explicitly stated supported ones in the error message below, will be an accepted answer for this question.

Some more details for my specific case:

My query takes three parameters, defined by a "PARAMETERS" clause in the beginning of the query's declaration, as so:

PARAMETERS myParam1 Text ( 255 ), myParam2 Text ( 255 ), myParam3 Text ( 255 );
SELECT ... <a bunch of not relevant stuff here> ;

Using an (already established and confirmed working) ODBC connection, I want to use this query from an external application, including providing the required three parameters for it.

Running normal queries like SELECT etc works just fine over the connection from my external application, but no matter how much I google, I cannot seem to find out the proper way to correctly provide the parameters for and run my query? So, again, my question is, what is the correct SQL syntax for doing this?

NOTE: I do NOT want any API specific solution for some certain library or similar, since this only solves the problem for a very small part of all developers who want to do this from different programming languages, so for this reason, I won't even bring up what language my external application is written in. I just want the full SQL syntax for doing this, nothing more, nothing less.

Another SO question indicates that this should be done using the "CALL" keyword, but when I try to use this from my application, I just get the following error message:

[42000] [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I.e., this is the "SQL command" (purposely avoiding the ambiguous term "query"...) that I'm trying to execute when getting this error:

CALL myAccessQuery ('string1', 'string2', 'string3')

The only one of the SQL keywords mentioned in the error message that seem likely to be useful is the "PROCEDURE" keyword, but I'm having similarly big difficulties finding any relevant documentation also for this on Google. :-(

I suspect that most of Google's lack of results in regards to this entire scenario comes from the many involved ambiguities regarding more or less all the central keywords in the context, like "queries", "parameters", "execute", "call" and "procedure", so with this SO question my hope is that it will constitute a somewhat more easily identified and indexed answer for this question to be presented by Google in the future.

Upvotes: 2

Views: 1325

Answers (1)

C. White
C. White

Reputation: 800

When you're connecting over ODBC, look at the driver-specific information to see if it supports the various ODBC extensions (indicated by curly braces in the ODBC calls):

https://msdn.microsoft.com/en-us/library/ms675326(v=vs.85).aspx

Specifically:

ODBC provides a specific syntax for calling stored procedures. For the CommandText property of a Command object, the CommandText argument to the Execute method on a Connection object, or the Source argument to the Open method on a Recordset object, passes in a string with this syntax:

    "{ [ ? = ] call procedure [ ( ? [, ? [ , … ]] ) ] }"

Each ? references an object in the Parameters collection. The first ? references Parameters(0), the next ? references Parameters(1), and so on. The parameter references are optional and depend on the structure of the stored procedure. If you want to call a stored procedure that defines no parameters, your string would look like the following:

    "{ call procedure }"

The Access ODBC driver exposes saved SELECT parameter queries as Stored Procedures, so that's why you use this syntax.

Upvotes: 2

Related Questions