Roger,
To offer you a quick summary of my position on this:
1) I can/could offer a workaround, but it breaks other, existing tests,
which lead me to believe it would/could break other's code, since the tests
specifically fix problems people have had -- maybe even one of yours :)
2) I believe this is a BUG in SQL Server's driver, where it's calling your
stored procedure with the wrong values for what you want, rather than using
the values you have supplied (i.e. it's doing the SET FMTONLY behind the
scenes during/after the prepare, not DBD::ODBC).
3) You *may* be able to work around this by not using placeholders, but I'm
not sure. I'm suspicious that it may only happen where the driver has to
"default" values for placeholders to call the procedure -- but I haven't
tested that myself, nor to I know how to use the profiler, etc.
Therefore, when .45_19 is released, it will not include any fixes for your
code, but fix where I errantly put in your code. I will have the code
commented out, but present, if you wish to see it.
If someone supplies a patch to make this work better, without breaking
existing tests, I will be happy to entertain it, but I think the cruxt of
the problem is the fact that the stored procedure returns vastly different
result set columns depending upon the input and "bugs" related to that in
the SQL Server driver. I have tried to make workarounds for it and there
may be a workaround, I just don't see it now and have put it more time to it
than I can afford.
Regards,
Jeff
> -----Original Message-----
> From: Roger Perttu [mailto:roger.perttu@;easit.se]
> Sent: Thursday, October 17, 2002 6:08 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Bug in DBD-ODBC 0.45_18
>
>
> [EMAIL PROTECTED] wrote:
>
> >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.
> >
> Great, because I have very limited knowledge of ODBC so it's hard for
> me to explain what's going on.
>
> > 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 get:
> E:\Projekt\Helpdesk\Perl\UpgradeDB>perl -e "use DBD::ODBC;print
> $DBD::ODBC::VERSION;"
> 0.45_18
>
> I spotted the problem we have been discussing in 0.46_16 and according
> to an earlier post by Jeff it should have been fixed in 0.45_18. I
> started this thread when I failed to verify that.
>
> >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)?
> >
> Download from ftp://ftp.esoftmatic.com/outgoing/DBI
> Install using:
> perl -S ppm remove DBD-ODBC
> perl -S ppm install E:\_temp1\DBI_ODBC045_18\DBD-ODBC.ppd
>
> Maybe Jeff has included the wrong ODBC.dll in the package? When I right
> click and select Properties I don't see the Version tab that you usually
> see with dll's.
> I don't have the tools to build from source.
>
> >
> >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
> >
> >
> >
> >
>
>
>
>
