Calling stored procedure from ODBC API

[jryden]

I have an ODBC implementation that calls a stored procedure. I'm
working on a bug report that says that the stored procedures being
called were returning no rows, even though there was data if the same
procedures were called using the query analyzer. After doing some
testing and experimentation, I came to a bizarre conclusion. The ODBC
implementation can only call stored procedures that contain one and
only one select statement and nothing else. In fact, a select
statement preceded by a simple DECLARE statement seems to cause this
issue.

Is this a familiar issue to anybody? I'm sure this is a problem with
the code. Unfortunately, I'm relatively inexperienced at looking at
ODBC so I haven't clue even where to start. Any suggestions?

[Sylvain Lafontaine]

You don't say how you are calling your SP via ODBC, so we are down here to
making some wild guesses - likely without any connection to your real
problem - but here's my first suggestion: adding an � SET NOCOUNT ON �
statement at the very beginning of the SP will often help. A second
suggestion would be to use the latest ODBC provider - The Native Provider -
available, see:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"jryden" <jry...@gmail.com> wrote in message
news:e5836449-613e-4ef1...@37g2000yqm.googlegroups.com...

[jryden]

Thanks for your response. I understand my original message is vague.
I simply don't have the expertise yet to ask a detailed question.

You ask how we are calling the SP. I'm honestly not sure how to
answer the question but I will give it a shot. There is quite a bit
of code that eventually leads up to a line that looks like this:

SQLExecDirect(m_hstmt, (SQLCHAR*)"EXECUTE spMyStoredProcedure",
SQL_NTS);

This call returns 0 (SUCCESS)

We then try this:

ret = SQLGetDiagField(SQL_HANDLE_STMT, // Handle type
m_hstmt, // handle
0, // record number
SQL_DIAG_CURSOR_ROW_COUNT, // diag
identifier
&rowCount, // diag info ptr
0, // ignored
for integer types
&numBytes); // num bytes written to
rowCount

Which also returns 0 (SUCCESS) but returns a rowcount of -1. An
attempt to call SQLFetch() after ignoring the -1 rowcount value
generates an actually error:

24000
Invalid cursor state
The StatementHandle was in an executed state but no result set was
associated with the StatementHandle.

It is interesting you mention SET NOCOUNT ON. The ODBC code we have
here is validating the SQLExecDirect call by looking at the number of
rows returned by SQL_DIAG_CURSOR_ROW_COUNT. If SET NOCOUNT ON is set,
we always get -1 even if there is valid data available to SQLFetch().

In this case, it appears that there is in fact no valid data, due to
the error from SQLFetch().

Just to clarify, here is the stored procedures I'm using for testing.
Commenting out the DECLARE line, allows this to work as expected.

ALTER PROCEDURE [dbo].[spMyStoredProcedure]
AS
BEGIN
DECLARE @s int
select * from agent
END

Note, I have tried this with both the Native provider and the (non
native?) one. I don't really know what is different about these two
providers but the behavior is the same with either one.

Again thanks for your help.

On Nov 16, 4:32 pm, "Sylvain Lafontaine"

<sylvainlafontaine2...@yahoo.ca> wrote:
> You don't say how you are calling your SP via ODBC, so we are down here to
> making some wild guesses - likely without any connection to your real
> problem - but here's my first suggestion: adding an « SET NOCOUNT ON »
> statement at the very beginning of the SP will often help.   A second
> suggestion would be to use the latest ODBC provider - The Native Provider -
> available, see:
>

> http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA2...

[Sylvain Lafontaine]

Oh, you are using C++. Sorry to say that but I'm out of my waters here.
However, you could try the old canonical ODBC format for calling a
procedure:

SQLExecDirect(m_hstmt, (SQLCHAR*)"{call spMyStoredProcedure}", SQL_NTS);

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"jryden" <jry...@gmail.com> wrote in message

news:4f58ccc6-4e18-4e99...@w19g2000yqk.googlegroups.com...

[John Elliot]

You need to post some more code that appears after SQLExecDirect.
SQLGetDiagField is just to retrieve errors, not the actual data.

Question: are you calling SQLMoreResults() to see if more than resultset
are generated?