I have snipped ALL previous discussion on this as I am finding it almost
impossible to see the wood from the trees now. If I understand you correctly
you have the following procedure:
create procedure roger
@p1 int = 0
as
if (@p1 >= 0) select * from systypes;
return(@p1)
You call it using $dbh->prepare("{call roger(?)}"), bind the parameter with
$sth->bind_param(-1) and then execute it. Afterwards you test
$sth->{NUM_OF_FIELDS} and get 20 when you think you should get 0.
The basic problems with this are:
[1] your procedure has a variable result i.e. if p1 >= 0 you produce a
result-set and if p1 < 0 you do not. This is a bad idea.
[2] because you are using a bound parameter and the driver does not know what
the value of the bound parameter is until execute is called the driver
cannot possibly know how to respond to SQLNumResultCols/SQLDescribeCol
properly i.e. the answer to SQLNumResultCols (before execute is called)
is either 0 or 20 and SQLNumResultCols can only return 1 number.
When you add this to the way the SQL Server ODBC driver works you are stuck.
The reason is demonstrated by the C code below which can be built in one of 3
ways:
[1] no defines on cc line calls
SQLFreeStmt(SQL_RESET_PARAMS)
SQLBindParam(-1)
SQLExecDirect("{call fred(?)}")
SQLNumResultCols
returns the correct answer - 0.
[2] add -DPREPARE_EXECUTE to the cc line and the code calls
SQLPrepare("{call fred(?)}")
SQLFreeStmt(SQL_RESET_PARAMS)
SQLBindParam(-1)
SQLExecute
SQLNumResultCols
returns the correct answer - 0
[3] add -DNUMRESULTCOLS and -DPREPARE_EXECUTE to the cc line and the code calls
SQLPrepare("{call fred(?)}")
SQLFreeStmt(SQL_RESET_PARAMS)
SQLBindParam(-1)
SQLNumResultCols
returns the wrong answer - 20
SQLExecute
SQLNumResultCols
returns the wrong answer - 20
3 could never work because ODBC says the bound parameter is NOT valid until
execute is called. As a result, when you ask the ODBC driver for the number of
resultant columns before executing, the answer (in your case) is 0 or 20.
Arguably, the SQL Server ODBC driver is broken here since because it knows it
cannot use your parameter yet it does a "set fmtonly on exec roger set fmtonly
off" and because your procedure defaults to p1=0 if p1 is not passed, this
generates an empty result-set with 20 columns.
Why this is happening if you are running DBD::ODBC 0.45_18 on Windows I don't
know because DBD::ODBC 0.45_18 does not appear to call SQLNumResultCols between
the prepare and execute - I cannot test what you are doing on Windows as I don't
have up to date DBD::ODBC for Windows (are you sure you are using DBD::ODBC
0.45_18?).
Change your procedure so it does not have a variable result and this problem
will go away. I still see no problem actually in DBD::ODBC.
On some of your other comments:
On 16-Oct-2002 Roger Perttu wrote:
> I recall that it's not possible to get output parameters and returncode
> until all data have been fetched (for SQL Server).
Because SQL Server runs the procedure SQL in batches but "set nocount on" can
affect this (stops the done proc msgs).
> Finally, I noticed that dbdimp.c uses a mix of space and tab for
> indentation. It would be nice if this could be cleaned up unless it
> messes up Jeff's source control system.
What is "unclean" about it? does your editor not handle tabs and spaces
properly?
Martin
Following code a quick hack compiled on Linux, with unixODBC driver
manager and Easysoft ODBC-ODBC Bridge to SQL Server and on Windows linked with
the MS driver manager to SQL Server. Build with PREPARE_EXECUTE defined or not
to see it working and and with PREPARE_EXECUTE and NUMRESULTCOLS defined to see
the problem.
#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
static void extract_error(
char *fn,
SQLHANDLE handle,
SQLSMALLINT type);
static void extract_error(
char *fn,
SQLHANDLE handle,
SQLSMALLINT type)
{
SQLINTEGER i = 0, native;
char state[ 7 ];
SQLCHAR text[256];
SQLSMALLINT len;
int ret;
fprintf(stderr,
"\n"
"The driver reported the following diagnostics whilst running "
"%s\n\n",
fn);
do {
ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
sizeof(text), &len );
if (SQL_SUCCEEDED(ret))
printf( "%s:%ld:%ld:%s\n", state, i, native, text );
}
while( ret == SQL_SUCCESS );
}
int main()
{
SQLRETURN retcode;
SQLINTEGER sparm1=1, sparm2=-1;
SQLINTEGER cbparm1=sizeof(SQLINTEGER);
SQLSMALLINT columns;
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
if (!SQL_SUCCEEDED(retcode)) {
fprintf(stderr, "Failed to allocate env handle\n");
exit(1);
}
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (!SQL_SUCCEEDED(retcode)) {
extract_error("SQLAllochandle for dbc", henv, SQL_HANDLE_ENV);
exit(1);
}
retcode = SQLConnect(hdbc, "test", SQL_NTS,
"user", SQL_NTS,
"pass", SQL_NTS);
if (!SQL_SUCCEEDED(retcode)) {
extract_error("SQLConnect", hdbc, SQL_HANDLE_DBC);
exit(1);
}
retcode = SQLSetConnectOption(hdbc, SQL_ATTR_AUTOCOMMIT, 0);
if (!SQL_SUCCEEDED(retcode)) {
extract_error("SQLSetConnectOption", hdbc, SQL_HANDLE_DBC);
exit(1);
}
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (!SQL_SUCCEEDED(retcode)) {
extract_error("SQLAllocHandle for stmt", hdbc, SQL_HANDLE_DBC);
exit(1);
}
#ifdef PREPARE_EXECUTE
retcode = SQLPrepare(hstmt, "{call roger (?)}", SQL_NTS);
/*retcode = SQLPrepare(hstmt, "exec roger -1", SQL_NTS);*/
if (!SQL_SUCCEEDED(retcode)) {
extract_error("SQLPrepare/SQLExecute", hstmt, SQL_HANDLE_STMT);
exit(1);
}
#endif
# ifdef DESCRIBEPARAM
{
SQLSMALLINT dt;
SQLUINTEGER ps;
SQLSMALLINT dd;
SQLSMALLINT n;
SQLDescribeParam(hstmt, 1, &dt, &ps, &dd, &n);
}
# endif
SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
retcode = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_DEFAULT,
SQL_INTEGER,0,0,&sparm1,
sizeof(sparm1) ,&cbparm1);
if (!SQL_SUCCEEDED(retcode)) {
extract_error("SQLBindParameter", hstmt, SQL_HANDLE_STMT);
exit(1);
}
sparm1=-1;
# ifdef NUMRESULTCOLS
SQLNumResultCols(hstmt, &columns);
printf("%d resultant columns\n", columns);
# endif
retcode = SQLExecute(hstmt);
#ifndef PREPARE_EXECUTE
retcode = SQLExecDirect(hstmt, "{call roger (?)}", SQL_NTS);
#endif
if (!SQL_SUCCEEDED(retcode)) {
extract_error("SQLPrepare/SQLExecute", hstmt, SQL_HANDLE_STMT);
exit(1);
}
SQLNumResultCols(hstmt, &columns);
printf("%d resultant columns\n", columns);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(0);
}
--
Martin J. Evans
Easysoft Ltd, UK
Development
