parameter markers - SQLBindParameter issue

[Nile]

I am having a problem with my application that uses ODBC. I am doing
the following:

* prepare a statement
* Bind
* execute

The problem is that my application needs to accept only insert queries
with parameter markers since it later writes to the database depending
on the values in the SQL query.

It seems that SQLBindParameter does not return any Error/Info when
there are no parameter markers to bind variables to in the statement.
I would expect this call to fail or at least generate some info to
suggest to the caller that the query had no parameters to bind
variables to!

Is there any way to find out using ODBC calls given a handle to a
statement whether this is an insert statement and how many parameter
markers are in the statement? I tried the SQLGetDiagField function
with the SQL_DIAG_DYNAMIC_FUNCTION identifier to get the kind of
statement but this would not tell me the number of parameter markers
in the query. Moreover this approach may not work if the user uses a
stored procedure in it. I want to avoid parsing the sql statement to
get this info.

I would appreciate if someone could give me some information on this.

TIA,
Nilesh

[Bob Hairgrove]

On 7 Nov 2003 12:47:33 -0800, ni****@cs.albany.edu (Nile) wrote:

I am having a problem with my application that uses ODBC. I am doing
the following:

* prepare a statement
* Bind
* execute

The problem is that my application needs to accept only insert queries
with parameter markers since it later writes to the database depending
on the values in the SQL query.

It seems that SQLBindParameter does not return any Error/Info when
there are no parameter markers to bind variables to in the statement.
That's right ... you can call SQLPrepare and SQLExecute on any kind of
statement.
I would expect this call to fail or at least generate some info to
suggest to the caller that the query had no parameters to bind
variables to!

Is there any way to find out using ODBC calls given a handle to a
statement whether this is an insert statement and how many parameter
markers are in the statement? <
Yes. SQLNumParams has been around since ODBC 1.0.

There is also SQLDescribeParam, but in my experience it is not
supported by many ODBC data sources. At any rate, the documentation to
this function says that the type of parameter (i.e. in, out, or
in/out) is not returned by the function.
I tried the SQLGetDiagField function
with the SQL_DIAG_DYNAMIC_FUNCTION identifier to get the kind of
statement but this would not tell me the number of parameter markers
in the query. Moreover this approach may not work if the user uses a
stored procedure in it. I want to avoid parsing the sql statement to
get this info.
To deduce whether or not the SQL is an insert statement, one would
normally examine the SQL statement and see if it starts with the
keyword "INSERT". Not a whole lot to parse there...

I don't know how one would do this if only the statement handle were
available ... normally, you start with a connection handle and an SQL
string in order to allocate a statement handle. How is the statement
handle allocated?

I would appreciate if someone could give me some information on this.

TIA,
Nilesh

--
Bob Hairgrove
No**********@Home.com

[Nile]

> >I tried the SQLGetDiagField function

with the SQL_DIAG_DYNAMIC_FUNCTION identifier to get the kind of
statement but this would not tell me the number of parameter markers
in the query. Moreover this approach may not work if the user uses a
stored procedure in it. I want to avoid parsing the sql statement to
get this info.

To deduce whether or not the SQL is an insert statement, one would
normally examine the SQL statement and see if it starts with the
keyword "INSERT". Not a whole lot to parse there...

I don't know how one would do this if only the statement handle were
available ... normally, you start with a connection handle and an SQL
string in order to allocate a statement handle. How is the statement
handle allocated?

Thanks for the info. The reason why I am not doing something simple
like looking at the first word to see if it is INSERT is because the
user can use a stored procedure named "foo" which has the insert
statement in it! So I want it to be more general. Any suggestions?

Thanks again,
Niles

[Bob Hairgrove]

On 10 Nov 2003 11:11:03 -0800, ni****@cs.albany.edu (Nile) wrote:

Thanks for the info. The reason why I am not doing something simple
like looking at the first word to see if it is INSERT is because the
user can use a stored procedure named "foo" which has the insert
statement in it! So I want it to be more general. Any suggestions?

In that case, the only parameters you would have are those of the
procedure call for which all you seem to have is the statement handle.

If you know the name of the SP, you would need documentation of the
procedure to know what it does; there is no way that I know of to gain
access to the "inside" of a SP from ODBC. Besides, if the parameters
have default values, or are objects passed by reference and not simple
data types, it might prove to be rather difficult...

Also, there is the issue of permissions. Usually, in a properly
designed database, SPs are there so that ordinary users can perform
certain updates, etc. for which they would otherwise not have the
necessary permissions. The reason for this is so that said updates,
etc. can happen under a controlled environment with regard to
exception handling, data consistency which cannot be enforced at the
engine level, etc.
--
Bob Hairgrove
No**********@Home.com