[EMAIL PROTECTED] wrote:
Great, because I have very limited knowledge of ODBC so it's hard for me to explain what's going on.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 toI get:
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.
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:Download from ftp://ftp.esoftmatic.com/outgoing/DBI
if (rc == SQL_SUCCESS_WITH_INFO) {IV(imp_sth) >= 8, DBIc_LOGPIO(imp_dbh));
AllODBCErrors(imp_sth->henv, imp_sth->hdbc, imp_sth->hstmt, DBIc_DEBUG
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)?
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 almostThanks, this makes it much more understandable.
impossible to see the wood from the trees now.
If I understand you correctly you have the following procedure:Yes, but it's just a test case to reproduce the bug
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 withActually it's not necessary to use $sth->{NUM_OF_FIELDS}. the bug shows it self when you execute.
$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:Might be bad, but it's valid code and it works in JScript + ODBC
[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 whatIt'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".
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.
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.This [2] is exactly what I would expect DBD-ODBC to use to execute my code.
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 codeIt's worse. It doesn't use the default parameter (I've checked this) but a hardcoded zero.
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'tYes, 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
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 problemCan'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.
will go away.
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: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.
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?
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
