Ok, I've downloaded the latest DBI and DBD::ODBC for Windows (from
ftp://ftp.esoftmatic.com/outgoing/DBI) and installed that. I now see the problem
you are seeing. If my test C code in previous email is changed to add a call to
SQLMoreResults() after the SQLExecute and then SQLNumResultCols is called you
get 20 back (like when you call SQLNumResultCols after SQLPrepare and before
SQLExecute).
However, I then spotted perl -e "use DBD::ODBC;print $DBD::ODBC::VERSION;"
outputs 0.45_17 and I believe from previous posts Jeff made some change related
to this in _18.
I notice the main difference between 17 and 18 is:
> if (rc == SQL_SUCCESS_WITH_INFO) {
> AllODBCErrors(imp_sth->henv, imp_sth->hdbc, imp_sth->hstmt, DBIc_DEBUG
IV(imp_sth) >= 8, DBIc_LOGPIO(imp_dbh));
1883a1887,1891
> /* flag that we've done the describe to avoid a problem
> * where calling describe after execute returned no rows
> * caused SQLServer to provide a description of a query
> * that didn't quite apply. */
> imp_sth->done_desc = 1;
where did you get a ppm which includes this code from (i.e version _18)?
Martin
On 16-Oct-2002 Roger Perttu wrote:
> [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
>>
>>
>>
>>
--
Martin J. Evans
Easysoft Ltd, UK
Development
