Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.


Subject: selectcol_arrayref() fails with SQL-HY104 against SQL Server 2000 (Invalid precision value)
Download (untitled) / with headers
text/plain 1.5k
selectcol_arrayref() fails with Invalid precision value (SQL-HY104) against SQL Server 2000 for certain queries. The initial query that showed the problem was quite complex. I was able to simply it down to the following code though upon further simplification (say just the inner query) the bug disappeared. my $dbh = DBI->connect("dbi:ODBC:testdb", '', '', {AutoCommit => 1, RaiseError => 0} ); if (!defined($dbh)) { die "Unable to connect to database."; } my $sql = <<'DONE'; select top 1 st_id from st_name where st_specificity = 'acc' and st_parent_id = ( select top 1 st_id from st_name where st_specificity = 'taxon' and st_name = ? ) DONE my $p = $dbh->selectcol_arrayref($sql, undef, 'Blahblahblahblah'); FWIW, the st_name.st_name column is a varchar(64) and I am binding a string (shorter than 64 characters) to it when it fails. The _id columns are all integer primary keys. The database is big and it would be some effort to strip things down further though if it were needed for further troubleshooting I might be able to construct a complete example, i.e. with table creation and population statements demonstrating the problem. I am seeing this problem under ActivePerl 5.10 (both builds 1005 and 1007) running on XP SP3 boxes, fully patched. I do not see this issue using DBD-ODBC 1.16 package that ships with the 1005 build when I remove the 1.23 site version. Also I downloaded 1.22 from CPAN and manually compiled and installed it in the site library and again do not see the problem. It seems to be a new problem in the 1.23 version.
From: bitcard_kiddm [...] ghctechnologies.com
Download (untitled) / with headers
text/plain 1.9k
On Sat Mar 20 03:58:07 2010, kiddm wrote: Show quoted text
> selectcol_arrayref() fails with Invalid precision value (SQL-HY104) > against SQL Server 2000 for certain queries. The initial query that > showed the problem was quite complex. I was able to simply it down to > the following code though upon further simplification (say just the > inner query) the bug disappeared. > > my $dbh = DBI->connect("dbi:ODBC:testdb", '', '', > {AutoCommit => 1, RaiseError => 0} ); > if (!defined($dbh)) { die "Unable to connect to database."; } > my $sql = <<'DONE'; > select top 1 st_id from st_name > where st_specificity = 'acc' > and st_parent_id = ( > select top 1 st_id from st_name > where st_specificity = 'taxon' and st_name = ? > ) > DONE > > my $p = $dbh->selectcol_arrayref($sql, undef, 'Blahblahblahblah'); > > FWIW, the st_name.st_name column is a varchar(64) and I am binding a > string (shorter than 64 characters) to it when it fails. The _id columns > are all integer primary keys. The database is big and it would be some > effort to strip things down further though if it were needed for further > troubleshooting I might be able to construct a complete example, i.e. > with table creation and population statements demonstrating the problem. > > I am seeing this problem under ActivePerl 5.10 (both builds 1005 and > 1007) running on XP SP3 boxes, fully patched. I do not see this issue > using DBD-ODBC 1.16 package that ships with the 1005 build when I remove > the 1.23 site version. Also I downloaded 1.22 from CPAN and manually > compiled and installed it in the site library and again do not see the > problem. It seems to be a new problem in the 1.23 version.
Further investigation reveals that the bug is not seen if a bind variable is not used, i.e. the string is simply made part of the SQL statement. In my specific test case 'Blahblahblahblah' is 22 characters long though a similar failure is observed with strings of slightly different length.
Download (untitled) / with headers
text/plain 3.1k
On Sun Mar 21 13:22:51 2010, kiddm wrote: Show quoted text
> On Sat Mar 20 03:58:07 2010, kiddm wrote:
> > selectcol_arrayref() fails with Invalid precision value (SQL-HY104) > > against SQL Server 2000 for certain queries. The initial query that > > showed the problem was quite complex. I was able to simply it down to > > the following code though upon further simplification (say just the > > inner query) the bug disappeared. > > > > my $dbh = DBI->connect("dbi:ODBC:testdb", '', '', > > {AutoCommit => 1, RaiseError => 0} ); > > if (!defined($dbh)) { die "Unable to connect to database."; } > > my $sql = <<'DONE'; > > select top 1 st_id from st_name > > where st_specificity = 'acc' > > and st_parent_id = ( > > select top 1 st_id from st_name > > where st_specificity = 'taxon' and st_name = ? > > ) > > DONE > > > > my $p = $dbh->selectcol_arrayref($sql, undef, 'Blahblahblahblah'); > > > > FWIW, the st_name.st_name column is a varchar(64) and I am binding a > > string (shorter than 64 characters) to it when it fails. The _id columns > > are all integer primary keys. The database is big and it would be some > > effort to strip things down further though if it were needed for further > > troubleshooting I might be able to construct a complete example, i.e. > > with table creation and population statements demonstrating the problem. > > > > I am seeing this problem under ActivePerl 5.10 (both builds 1005 and > > 1007) running on XP SP3 boxes, fully patched. I do not see this issue > > using DBD-ODBC 1.16 package that ships with the 1005 build when I remove > > the 1.23 site version. Also I downloaded 1.22 from CPAN and manually > > compiled and installed it in the site library and again do not see the > > problem. It seems to be a new problem in the 1.23 version.
> > Further investigation reveals that the bug is not seen if a bind > variable is not used, i.e. the string is simply made part of the SQL > statement. In my specific test case 'Blahblahblahblah' is 22 characters > long though a similar failure is observed with strings of slightly > different length.
Hi, The likely problem is that SQL Server was unable to turn your SQL into the SQL required to obtain parameter information. SQL Server supports SQLDescribeParam rather poorly in some case. It takes your SQL and attempts to turn it into SQL which would select the columns equivalent to your parameters. If you run your code and watch SQL Server you will see the SQL submitted and I'm guessing it will be wrong. The easy way to workaround this is to specify SL_VARCHAR in the bind_param call (see TYPE). However, I've seen a couple of people saying something changed between 1.22 and 1.23 that may have caused an issue but never managed to get anyone to help dig into it. Could you run you code against 1.23 with logging turned on and post the log? It would also be really useful to repeat with 1.22. You can enable logging by doing this: Add the following to the top of your script: use DBD::ODBC; DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode')); set DBI_TRACE=15=x.log. See http://search.cpan.org/~mjevans/DBD-ODBC-1.23/ODBC.pm#Tracing and http://search.cpan.org/~timb/DBI-1.609/DBI.pm#TRACING. Martin -- Martin J. Evans Wetherby, UK
From: bitcard_kiddm [...] ghctechnologies.com
Download (untitled) / with headers
text/plain 3.6k
On Mon Mar 22 05:14:50 2010, MJEVANS wrote: Show quoted text
> On Sun Mar 21 13:22:51 2010, kiddm wrote:
> > On Sat Mar 20 03:58:07 2010, kiddm wrote:
> > > selectcol_arrayref() fails with Invalid precision value (SQL-HY104) > > > against SQL Server 2000 for certain queries. The initial query that > > > showed the problem was quite complex. I was able to simply it down to > > > the following code though upon further simplification (say just the > > > inner query) the bug disappeared. > > > > > > my $dbh = DBI->connect("dbi:ODBC:testdb", '', '', > > > {AutoCommit => 1, RaiseError => 0} ); > > > if (!defined($dbh)) { die "Unable to connect to database."; } > > > my $sql = <<'DONE'; > > > select top 1 st_id from st_name > > > where st_specificity = 'acc' > > > and st_parent_id = ( > > > select top 1 st_id from st_name > > > where st_specificity = 'taxon' and st_name = ? > > > ) > > > DONE > > > > > > my $p = $dbh->selectcol_arrayref($sql, undef, 'Blahblahblahblah'); > > > > > > FWIW, the st_name.st_name column is a varchar(64) and I am binding a > > > string (shorter than 64 characters) to it when it fails. The _id
columns Show quoted text
> > > are all integer primary keys. The database is big and it would be some > > > effort to strip things down further though if it were needed for
further Show quoted text
> > > troubleshooting I might be able to construct a complete example, i.e. > > > with table creation and population statements demonstrating the
problem. Show quoted text
> > > > > > I am seeing this problem under ActivePerl 5.10 (both builds 1005 and > > > 1007) running on XP SP3 boxes, fully patched. I do not see this issue > > > using DBD-ODBC 1.16 package that ships with the 1005 build when I
remove Show quoted text
> > > the 1.23 site version. Also I downloaded 1.22 from CPAN and manually > > > compiled and installed it in the site library and again do not see the > > > problem. It seems to be a new problem in the 1.23 version.
> > > > Further investigation reveals that the bug is not seen if a bind > > variable is not used, i.e. the string is simply made part of the SQL > > statement. In my specific test case 'Blahblahblahblah' is 22 characters > > long though a similar failure is observed with strings of slightly > > different length.
> > Hi, > > The likely problem is that SQL Server was unable to turn your SQL into > the SQL required to obtain parameter information. SQL Server supports > SQLDescribeParam rather poorly in some case. It takes your SQL and > attempts to turn it into SQL which would select the columns equivalent > to your parameters. If you run your code and watch SQL Server you will > see the SQL submitted and I'm guessing it will be wrong. > > The easy way to workaround this is to specify SQL_VARCHAR in the > bind_param call (see TYPE). > > However, I've seen a couple of people saying something changed between > 1.22 and 1.23 that may have caused an issue but never managed to get > anyone to help dig into it. Could you run you code against 1.23 with > logging turned on and post the log? It would also be really useful to > repeat with 1.22. You can enable logging by doing this: > > Add the following to the top of your script: > > use DBD::ODBC; > DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode')); > > set DBI_TRACE=15=x.log. See > http://search.cpan.org/~mjevans/DBD-ODBC-1.23/ODBC.pm#Tracing and > http://search.cpan.org/~timb/DBI-1.609/DBI.pm#TRACING. > > Martin
As you suspected, explicitly setting the data type to SQL_VARCHAR via the bind_param() method does solve the problem. I also generated the trace logs (attached) for both 1.22 and 1.23 when the binding is not done, i.e. when version 1.23 shows the bug. diff shows the files to be very similar.
Subject: odbc-1.23.log
Download odbc-1.23.log
application/octet-stream 8.3k

Message body not shown because it is not plain text.

Subject: odbc-1.22.log
Download odbc-1.22.log
application/octet-stream 8.3k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 4.5k
On Mon Mar 22 16:43:31 2010, kiddm wrote: Show quoted text
> On Mon Mar 22 05:14:50 2010, MJEVANS wrote:
> > On Sun Mar 21 13:22:51 2010, kiddm wrote:
> > > On Sat Mar 20 03:58:07 2010, kiddm wrote:
> > > > selectcol_arrayref() fails with Invalid precision value (SQL-HY104) > > > > against SQL Server 2000 for certain queries. The initial query that > > > > showed the problem was quite complex. I was able to simply it
down to Show quoted text
> > > > the following code though upon further simplification (say just the > > > > inner query) the bug disappeared. > > > > > > > > my $dbh = DBI->connect("dbi:ODBC:testdb", '', '', > > > > {AutoCommit => 1, RaiseError => 0} ); > > > > if (!defined($dbh)) { die "Unable to connect to database."; } > > > > my $sql = <<'DONE'; > > > > select top 1 st_id from st_name > > > > where st_specificity = 'acc' > > > > and st_parent_id = ( > > > > select top 1 st_id from st_name > > > > where st_specificity = 'taxon' and st_name = ? > > > > ) > > > > DONE > > > > > > > > my $p = $dbh->selectcol_arrayref($sql, undef, 'Blahblahblahblah'); > > > > > > > > FWIW, the st_name.st_name column is a varchar(64) and I am binding a > > > > string (shorter than 64 characters) to it when it fails. The _id
> columns
> > > > are all integer primary keys. The database is big and it would
be some Show quoted text
> > > > effort to strip things down further though if it were needed for
> further
> > > > troubleshooting I might be able to construct a complete example,
i.e. Show quoted text
> > > > with table creation and population statements demonstrating the
> problem.
> > > > > > > > I am seeing this problem under ActivePerl 5.10 (both builds 1005 and > > > > 1007) running on XP SP3 boxes, fully patched. I do not see this
issue Show quoted text
> > > > using DBD-ODBC 1.16 package that ships with the 1005 build when I
> remove
> > > > the 1.23 site version. Also I downloaded 1.22 from CPAN and manually > > > > compiled and installed it in the site library and again do not
see the Show quoted text
> > > > problem. It seems to be a new problem in the 1.23 version.
> > > > > > Further investigation reveals that the bug is not seen if a bind > > > variable is not used, i.e. the string is simply made part of the SQL > > > statement. In my specific test case 'Blahblahblahblah' is 22
characters Show quoted text
> > > long though a similar failure is observed with strings of slightly > > > different length.
> > > > Hi, > > > > The likely problem is that SQL Server was unable to turn your SQL into > > the SQL required to obtain parameter information. SQL Server supports > > SQLDescribeParam rather poorly in some case. It takes your SQL and > > attempts to turn it into SQL which would select the columns equivalent > > to your parameters. If you run your code and watch SQL Server you will > > see the SQL submitted and I'm guessing it will be wrong. > > > > The easy way to workaround this is to specify SQL_VARCHAR in the > > bind_param call (see TYPE). > > > > However, I've seen a couple of people saying something changed between > > 1.22 and 1.23 that may have caused an issue but never managed to get > > anyone to help dig into it. Could you run you code against 1.23 with > > logging turned on and post the log? It would also be really useful to > > repeat with 1.22. You can enable logging by doing this: > > > > Add the following to the top of your script: > > > > use DBD::ODBC; > > DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode')); > > > > set DBI_TRACE=15=x.log. See > > http://search.cpan.org/~mjevans/DBD-ODBC-1.23/ODBC.pm#Tracing and > > http://search.cpan.org/~timb/DBI-1.609/DBI.pm#TRACING. > > > > Martin
> > As you suspected, explicitly setting the data type to SQL_VARCHAR via > the bind_param() method does solve the problem. > > I also generated the trace logs (attached) for both 1.22 and 1.23 when > the binding is not done, i.e. when version 1.23 shows the bug. diff > shows the files to be very similar.
Thanks for the logs and I hope in the mean time you can continue by specifying the bind type. We obviously have a time difference between us (it is 21:45 here now). I will look into the logs but please feel free to email me and pester me if you do not hear from me in the next few days. As you can appreciate maintaining DBD::ODBC is not something I'm paid for and so I have to do it when time permits outside of work. Not withstanding that I would like to get to the bottom of the difference between 1.22 and 1.23 and it is useful to have someone who is prepared to submit the logs so I will try to look at this really soon (before your interest dies). Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 759b
On Mon Mar 22 16:43:31 2010, kiddm wrote: Show quoted text
> > As you suspected, explicitly setting the data type to SQL_VARCHAR via > the bind_param() method does solve the problem. > > I also generated the trace logs (attached) for both 1.22 and 1.23 when > the binding is not done, i.e. when version 1.23 shows the bug. diff > shows the files to be very similar.
It appears the log files were created without DBI_TRACE set. You generally need to do: export DBI_TRACE=15=x.log then run your script. On Windows I think this may be set DBI_TRACE=15=x.log or you need to set $h->{TraceLevel} = "3|ALL" after connect. Any chance you could redo them. The files should end up a lot bigger than the ones you previously submitted. Martin -- Martin J. Evans Wetherby, UK
From: bitcard_kiddm [...] ghctechnologies.com
Download (untitled) / with headers
text/plain 1.5k
On Tue Mar 23 04:44:04 2010, MJEVANS wrote: Show quoted text
> It appears the log files were created without DBI_TRACE set. > > You generally need to do: > > export DBI_TRACE=15=x.log > > then run your script. > > On Windows I think this may be > > set DBI_TRACE=15=x.log > > or you need to set $h->{TraceLevel} = "3|ALL" after connect. > > Any chance you could redo them. The files should end up a lot bigger > than the ones you previously submitted. > > Martin
I think the newly attached log files have what you need. When I diff these files the thing that stands out to me is this line. SQLBindParameter: idx=1: param_type=1, name=1, value_type=-8, SQL_Type=-9, column_size=0, d_digits=0, value_ptr=3ddf92c, buffer_length=44, cbValue=44, param_size=0 In DBD-ODBC 1.22, column_size=44, but in DBD-ODBC 1.23 column_size=0. BTW, previously I did have the environment variable set DBI_TRACE=15=x.log (well actually DBI_TRACE=15=odbc.log, which shouldn't make a difference?!). print $ENV{'DBI_TRACE'} shows that Perl is picking it up properly. For whatever reason, I had to add $h->{TraceLevel} = "3|ALL" to get the logging of the individual query requests. FWIW, it is very strange to include an equals sign in the value of an environment variable. Sure it is valid, but if nothing else it causes one to reflexively think the documentation has an error. I wish the DBI package author(s) has come up with a different convention. As for the time shift, yes I am in California. But I have a fairly strong interest in having DBD-ODBC (and DBI) work properly. - Matthew
Subject: odbc-1.23.log
Download odbc-1.23.log
application/octet-stream 13.2k

Message body not shown because it is not plain text.

Subject: odbc-1.22.log
Download odbc-1.22.log
application/octet-stream 14.3k

Message body not shown because it is not plain text.

On Tue Mar 23 12:28:11 2010, kiddm wrote: Show quoted text
> On Tue Mar 23 04:44:04 2010, MJEVANS wrote:
> > It appears the log files were created without DBI_TRACE set. > > > > You generally need to do: > > > > export DBI_TRACE=15=x.log > > > > then run your script. > > > > On Windows I think this may be > > > > set DBI_TRACE=15=x.log > > > > or you need to set $h->{TraceLevel} = "3|ALL" after connect. > > > > Any chance you could redo them. The files should end up a lot bigger > > than the ones you previously submitted. > > > > Martin
> > I think the newly attached log files have what you need.
They do - thanks. Show quoted text
> When I diff > these files the thing that stands out to me is this line.
Show quoted text
> SQLBindParameter: idx=1: param_type=1, name=1, value_type=-8, > SQL_Type=-9, column_size=0, d_digits=0, value_ptr=3ddf92c, > buffer_length=44, cbValue=44, param_size=0
Yes, that is it. Show quoted text
> In DBD-ODBC 1.22, column_size=44, but in DBD-ODBC 1.23 column_size=0. > > BTW, previously I did have the environment variable set > DBI_TRACE=15=x.log (well actually DBI_TRACE=15=odbc.log, which shouldn't > make a difference?!). print $ENV{'DBI_TRACE'} shows that Perl is picking > it up properly. For whatever reason, I had to add $h->{TraceLevel} = > "3|ALL" to get the logging of the individual query requests.
Don't understand why that was not working for you, I just retried it on my windows machine and it works fine. DBD::ODBC is not responsible for the logging, DBI is. BTW, 15 would have been better than 3 (3 is technically less detail). Show quoted text
> FWIW, it is > very strange to include an equals sign in the value of an environment > variable. Sure it is valid, but if nothing else it causes one to > reflexively think the documentation has an error. I wish the DBI package > author(s) has come up with a different convention.
First I've heard anyone mention this. Show quoted text
> As for the time shift, yes I am in California. But I have a fairly > strong interest in having DBD-ODBC (and DBI) work properly. > > - Matthew
Will try and look at this properly tomorrow latest - unfortunately I'm a bit busy tonight. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.6k
Matthew, I presume you are ok to build a new DBD::ODBC from scratch. If you are then I would like you to get DBD::ODBC 1.23_2 from CPAN then before building it edit dbdimp.c to make the following changes: Around about line 3523 it should look like this: if (phs->param_size == 0) { if ((imp_dbh->driver_type == DT_SQL_SERVER_NATIVE_CLIENT) || ((strcmp(imp_dbh->odbc_dbms_name, "Microsoft SQL Server") == 0) && (phs->sql_type == SQL_WVARCHAR) && (phs->requested_type == 0))) { column_size = 0; } could you change it to: if (phs->param_size == 0) { if ((imp_dbh->driver_type == DT_SQL_SERVER_NATIVE_CLIENT) || ((strcmp(imp_dbh->odbc_dbms_name, "Microsoft SQL Server") == 0) && (phs->sql_type == SQL_WVARCHAR) && (phs->requested_type == 0))) { column_size = 0; /* start of lines added */ if (DBIc_TRACE(imp_sth, 0, 0, 4)) { TRACE0(imp_dbh, " Resetting column_size to 0 for (max) column\n"); TRACE1(imp_dbh, " SQLDescribeParam returned %d\n", phs->describe_param_status); } /* end of lines added */ } } Then if you rebuild, set tracing to 15 and run your script again does the log file contain "Resetting column_size to 0 for (max)" and if so what is the line output after that saying SQLDescribeParam returned NN. If NN is not 0 try changing the "if" above from: if (phs->param_size == 0) { to if ((phs->param_size == 0) && (!SQL_SUCCEEDED(phs->describe_param_status))) { Martin -- Martin J. Evans Wetherby, UK
Subject: RE: [rt.cpan.org #55736] selectcol_arrayref() fails with SQL-HY104 against SQL Server 2000 (Invalid precision value)
Date: Tue, 23 Mar 2010 23:05:36 -0700
To: <bug-DBD-ODBC [...] rt.cpan.org>
From: "Matthew Kidd" <matthew.kidd [...] ghctechnologies.com>
Download (untitled) / with headers
text/plain 2.2k
Martin, The full story is in the log files (tracing level at 15). The first file is before applying your patch. The second is after applying the patch and it shows that SQLDescribeParam returns -1. The final file is after modifying the IF statement. It is essentially the same as the second file. - Matthew Show quoted text
-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Tuesday, March 23, 2010 10:08 AM To: Matthew Kidd Subject: [rt.cpan.org #55736] selectcol_arrayref() fails with SQL-HY104 against SQL Server 2000 (Invalid precision value) <URL: https://rt.cpan.org/Ticket/Display.html?id=55736 > Matthew, I presume you are ok to build a new DBD::ODBC from scratch. If you are then I would like you to get DBD::ODBC 1.23_2 from CPAN then before building it edit dbdimp.c to make the following changes: Around about line 3523 it should look like this: if (phs->param_size == 0) { if ((imp_dbh->driver_type == DT_SQL_SERVER_NATIVE_CLIENT) || ((strcmp(imp_dbh->odbc_dbms_name, "Microsoft SQL Server") == 0) && (phs->sql_type == SQL_WVARCHAR) && (phs->requested_type == 0))) { column_size = 0; } could you change it to: if (phs->param_size == 0) { if ((imp_dbh->driver_type == DT_SQL_SERVER_NATIVE_CLIENT) || ((strcmp(imp_dbh->odbc_dbms_name, "Microsoft SQL Server") == 0) && (phs->sql_type == SQL_WVARCHAR) && (phs->requested_type == 0))) { column_size = 0; /* start of lines added */ if (DBIc_TRACE(imp_sth, 0, 0, 4)) { TRACE0(imp_dbh, " Resetting column_size to 0 for (max) column\n"); TRACE1(imp_dbh, " SQLDescribeParam returned %d\n", phs->describe_param_status); } /* end of lines added */ } } Then if you rebuild, set tracing to 15 and run your script again does the log file contain "Resetting column_size to 0 for (max)" and if so what is the line output after that saying SQLDescribeParam returned NN. If NN is not 0 try changing the "if" above from: if (phs->param_size == 0) { to if ((phs->param_size == 0) && (!SQL_SUCCEEDED(phs->describe_param_status))) { Martin -- Martin J. Evans Wetherby, UK
Download odbc-1.23_2.log
application/octet-stream 13.2k

Message body not shown because it is not plain text.

Download odbc-1.23_2-patched.log
application/octet-stream 13.3k

Message body not shown because it is not plain text.

Download odbc-1.23_2-patched2.log
application/octet-stream 13.3k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 3.4k
On Wed Mar 24 02:05:52 2010, matthew.kidd@ghctechnologies.com wrote: Show quoted text
> > Martin, > > The full story is in the log files (tracing level at 15). The first file > is before applying your patch. The second is after applying the patch > and it shows that SQLDescribeParam returns -1. The final file is after > modifying the IF statement. It is essentially the same as the second > file. > > - Matthew
Oops. As I did not have your schema to verify I could not test and I got it wrong. The if statement change should be: if ((phs->param_size == 0) && (SQL_SUCCEEDED(phs->describe_param_status))) { Sorry for wasting your time. To be honest although this should work for you it causes me a problem. DBD::ODBC in the past did not use SQLDescribeParam to find out about parameters, it just bound everything as SQL_VARCHAR. However, since the introduction of unicode support to DBD::ODBC and the addition of varXXX(MAX) columns in SQL Server DBD::ODBC needs more information to bind some parameters correctly so it now calls SQLDescribeParam. If the SQLDescribeParam fails it falls back on binding the parameter as SQL_VARCHAR or SQL_WVARCHAR depending on whether it is a unicode build or not. The problem is that SQL Server's SQLDescribeParam can fail because MS have a buggy implementation which attempts to rearrange your SQL to select the columns where parameters are used. e.g., you do "insert into mytable (mycolumn) values(?)" and SQL Server issues a "select mycolumn from mytable" to find out about the column the parameter is to be bound to. This works for many cases but when you bind parameters to items in the SQL which use functions or use more complicated sub-selects SQL Server fails to rearrange the SQL and SQLDescribeParam fails. You can monitor the SQL the driver is issuing in SQL Server and see these erroneous statements. So DBD::ODBC attempts to work around this failure. However, to successfully support varXXX(max) columns the column size in the SQLBindParameter call has to be 0 and the code you are modifying is the code which would do this. After the modification varXXX(MAX) columns are only going to be bound correctly (assuming it is left to DBD::ODBC and TYPE=>xxx is not added to the bind_param method call) when SQLDesribeParam succeeds. This is probably ok, since we only know for sure the parameter is bound to a varXXX(MAX) column is SQLDescribeParam succeeds but I'll have to run through all the test code and make sure. It leaves us pretty much back where we were in that if SQLDescribeParam fails then the automatic type DBD::ODBC picks will only work if it is not a varXXX(MAX) column. Otherwise, you need to add TYPE=>xxx to tell DBD::ODBC what to do. Little of this really concerns you but I'm explaining it here a) to record it and b) to show there is really not that much I can do if an ODBC API call fails which shouldn't. For almost 2 years now I've been battling with this and I am starting to think that making DBD::ODBC try and workaround the problem is just causing me more problems since many people expect bind_param to just work without specifying a TYPE and it cannot always do that if SQLDescribeParam fails. It is safer in most situations to specify TYPE on bind_param. The exceptions are a) when it is a varXXX(max) column and b) if you are writing portable code which must work with multiple DBDs. Let me know if this change works and I'll put it in and probably do a release since it has been a while since a full release. Martin -- Martin J. Evans Wetherby, UK
From: bitcard_kiddm [...] ghctechnologies.com
Download (untitled) / with headers
text/plain 875b
On Wed Mar 24 04:50:57 2010, MJEVANS wrote: Show quoted text
> Oops. As I did not have your schema to verify I could not test and > I got it wrong. The if statement change should be: > > if ((phs->param_size == 0) && > (SQL_SUCCEEDED(phs->describe_param_status))) { > > Sorry for wasting your time.
I just tested this latest change and it works. A log file is attached. Show quoted text
> The problem is that SQL Server's SQLDescribeParam can fail because MS > have a buggy implementation...
I didn't appreciate how screwed up the situation is on Microsoft's end. Now I feel your pain. Do you know if the SQLDescribeParam issue has improved in SQL Server 2008? We have stayed on SQL Server 2000 out of inertia, no clear need for 2005/2008 features, and no desire to troubleshoot the inevitable new issues. But it wouldn't cost us that much to upgrade. Maybe the time has come. - Matthew
Subject: odbc-1.23_2-patched3.log
Download odbc-1.23_2-patched3.log
application/octet-stream 14.3k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 2.7k
On Wed Mar 24 14:17:10 2010, kiddm wrote: Show quoted text
> On Wed Mar 24 04:50:57 2010, MJEVANS wrote:
> > Oops. As I did not have your schema to verify I could not test and > > I got it wrong. The if statement change should be: > > > > if ((phs->param_size == 0) && > > (SQL_SUCCEEDED(phs->describe_param_status))) { > > > > Sorry for wasting your time.
> > I just tested this latest change and it works. A log file is attached.
Excellent and sorry for getting it wrong the first time. Show quoted text
> > The problem is that SQL Server's SQLDescribeParam can fail because MS > > have a buggy implementation...
> > I didn't appreciate how screwed up the situation is on Microsoft's end.
MS SQL Server ODBC Driver is really broken for SQLDescribeParam when the SQL gets more complicated or uses functions. Show quoted text
> Now I feel your pain. Do you know if the SQLDescribeParam issue has > improved in SQL Server 2008? We have stayed on SQL Server 2000 out of > inertia, no clear need for 2005/2008 features, and no desire to > troubleshoot the inevitable new issues. But it wouldn't cost us that > much to upgrade. Maybe the time has come. > > - Matthew > >
If you peruse the other rts for DBD::ODBC someone else hit the SQLDescribeParam bugs recently and apparently reported it to MS but I don't have the link at hand - you may even need to be a supported customer to read it - I don't know as MS don't provide me free support and I cannot afford to buy it. I doubt at this time there would be much advantage to upgrading although if you NEED varXX(max) column support the most recent native client driver is better. I'll think about this solution to the problem you reported. I may post to dbi-dev mailing list (see dbi.perl.org) suggesting I raise a warning when SQLDescribeParam fails to see what other DBD authors think. I'll leave this rt open until I decide on a resolution but at present I think the solution we have is the best of a bad lot. If you will be actively using DBD::ODBC then the best things you can do are a) send me privately your email address to mjevans at cpan.org and I'll try and keep you uptodate on potential new releases so you can test them before final release b) report problems in SQL Server SQLDescribeParam so MS know about it and the weight of complaints might have the desired effect. One of the problems I have is that it is almost impossible to keep copies of all MS SQL Server Windows drivers so when I do a release I can get a rush of failures because no one bothers to test the development releases (those ending in _NNN). I'm trying to get a farm of volunteers to test releases before they are released - it is no more than a few official ones a year. Any help with this would be appreciated. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 3.4k
Hi again, I am about to do a new development release of DBD::ODBC 1.23_4 which includes the change I suggested to you in this rt and some other changes which speed up prepared statements considerably in certain circumstances. Would you be interested in checking it out before I do a final release of 1.24? If so get back to me and I'll let you know when it is uploaded to CPAN. I'm struggling to find people to test development releases before final releases and then when the final release is done I get a bunch of rt reports so I'd appreciate it. Martin On Wed Mar 24 17:25:02 2010, MJEVANS wrote: Show quoted text
> On Wed Mar 24 14:17:10 2010, kiddm wrote:
> > On Wed Mar 24 04:50:57 2010, MJEVANS wrote:
> > > Oops. As I did not have your schema to verify I could not test and > > > I got it wrong. The if statement change should be: > > > > > > if ((phs->param_size == 0) && > > > (SQL_SUCCEEDED(phs->describe_param_status))) { > > > > > > Sorry for wasting your time.
> > > > I just tested this latest change and it works. A log file is attached.
> > Excellent and sorry for getting it wrong the first time. >
> > > The problem is that SQL Server's SQLDescribeParam can fail because MS > > > have a buggy implementation...
> > > > I didn't appreciate how screwed up the situation is on Microsoft's end.
> > MS SQL Server ODBC Driver is really broken for SQLDescribeParam when the > SQL gets more complicated or uses functions. >
> > Now I feel your pain. Do you know if the SQLDescribeParam issue has > > improved in SQL Server 2008? We have stayed on SQL Server 2000 out of > > inertia, no clear need for 2005/2008 features, and no desire to > > troubleshoot the inevitable new issues. But it wouldn't cost us that > > much to upgrade. Maybe the time has come. > > > > - Matthew > > > >
> > If you peruse the other rts for DBD::ODBC someone else hit the > SQLDescribeParam bugs recently and apparently reported it to MS but I > don't have the link at hand - you may even need to be a supported > customer to read it - I don't know as MS don't provide me free support > and I cannot afford to buy it. > > I doubt at this time there would be much advantage to upgrading although > if you NEED varXX(max) column support the most recent native client > driver is better. > > I'll think about this solution to the problem you reported. I may post > to dbi-dev mailing list (see dbi.perl.org) suggesting I raise a warning > when SQLDescribeParam fails to see what other DBD authors think. > > I'll leave this rt open until I decide on a resolution but at present I > think the solution we have is the best of a bad lot. If you will be > actively using DBD::ODBC then the best things you can do are a) send me > privately your email address to mjevans at cpan.org and I'll try and > keep you uptodate on potential new releases so you can test them before > final release b) report problems in SQL Server SQLDescribeParam so MS > know about it and the weight of complaints might have the desired effect. > > One of the problems I have is that it is almost impossible to keep > copies of all MS SQL Server Windows drivers so when I do a release I can > get a rush of failures because no one bothers to test the development > releases (those ending in _NNN). I'm trying to get a farm of volunteers > to test releases before they are released - it is no more than a few > official ones a year. Any help with this would be appreciated. > > Martin
-- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.