[EMAIL PROTECTED] wrote:
>I have snipped ALL previous discussion on this as I am finding it almost
>impossible to see the wood from the trees now.
>
Thanks, this makes it much more understandable.
>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)
>
Yes, but it's just a test case to reproduce the bug
>
>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.
>
>
Actually it's not necessary to use $sth->{NUM_OF_FIELDS}. the bug shows
it self when you execute.
>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.
>
Might be bad, but it's valid code and it works in JScript + ODBC
>
>[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.
>
It's a very bad idea to call SQLNumResultCols before execute and I don't
do anything to trigger this. (I also don't think it's happening because
I can see in Profiler that my procedure is called first with my
parameter and then a second time with the wrong one. In Jeff's code
SQLNumResultCols is called after SQLExecute but he is also doing some
other stuff that might trigger the use of "SET FMTONLY ON".
This is run on the server:
set implicit_transactions on
testPrc -1
SET FMTONLY ON EXEC testPrc 0 SET FMTONLY OFF
IF @@TRANCOUNT > 0 ROLLBACK TRAN
Notice that in this situation it's totally stupid (must be a MS bug
then) to re execute the procedure. Using "SET FMTONLY ON" is like using
"where 1=2" in a select statement. It won't return more information than
the "true" call thats just been executed.
>
>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
>
>
This [2] is exactly what I would expect DBD-ODBC to use to execute my code.
>[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.
>
It's worse. It doesn't use the default parameter (I've checked this) but
a hardcoded zero.
>
>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?).
>
Yes, I'm using the latest ones from Jeff's site
ftp.esoftmatic.com/outgoing/DBI And I'm doing this because I've found
another bug in an earlier release of 0.45
>
>Change your procedure so it does not have a variable result and this problem
>will go away.
>
Can't do that. It's part of a object-relational mapping system. One
procedure handle one object and one parameter indicates if it's a
create, update or get.
>I still see no problem actually in DBD::ODBC.
>
I do :-)
0.45 is still beta or something. I know I found a very special condition
but it would be nice if it was fixed. Now that I know more I'm not
saying it's a true bug in DBI, but rather a lack of a workaround for a
problem in MS code.
>
>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?
>
It sure does but the source contains a mixture of tabs and spaces. In
some places it's indented using space and in others tab. My editor is
set to a tab-length of four or three. Just trying to help.
Once again, thanks Martin.
I wish I had your knowledge of ODBC, a compiler and lots of spare time.
Then I might be able to debug and patch Jeff's code.
>
>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
>
>
>
>
