Bug #91380 for DBI: Patch for "type_info_all result has 19 keys but 20 fields" errors

Fri Dec 13 07:01:51 2013 EDAVIS [...] cpan.org - Ticket created

Subject:

Patch for "type_info_all result has 19 keys but 20 fields" errors

When using type_info with DBD::Sybase/freetds against an MSSQL server, DBI fails with an error type_info_all result has 19 keys but 20 fields (If you search on the Web you'll see that this causes DBD::Sybase build failures on various systems.) The DBD::ODBC documentation has some suggestion about why this happens: Show quoted text

>Many ODBC drivers now return 20 columns in type_info_all rather >than the 19 DBI documents. The 20th column is usually called >"USERTYPE". Recent MS SQL Server ODBC drivers do this. >Fortunately this should not adversely affect you so long as you >are using the keys provided at the start of type_info_all.

I suggest that the check in DBI is a bit too fussy, and it should allow *extra* fields in type_info_all (which will then be ignored), and only fail if too few are seen. Please change the test in line 1811 from != to <, thus: if @ti && @{$ti[0]} < $idx_fields; Then if 20 fields are returned by the driver, it still works and the unrecognized twentieth field is just ignored.

Fri Dec 13 08:24:13 2013 bohica [...] ntlworld.com - Correspondence added

Message body is not shown because it is too large.

Fri Dec 13 08:24:13 2013 The RT System itself - Status changed from 'new' to 'open'

Fri May 09 02:22:22 2014 EDAVIS [...] cpan.org - Correspondence added

Reposting comment by bohica@ntlworld.com since it is not shown by the web interface: This is an off the cuff comment as I've not had a chance to look at the code yet. DBD::ODBC has returned a type_info_all with 20 columns for ages with SQL Server drivers and I've never seen this error before. Are you sure the test isn't complaining that there are 20 columns but only 19 column names in type_info_all result? e.g., for DBD::ODBC you get the following and there are 20 column names in the first hash: [big dump of data snipped, see 'Download' above to see the full comment]

Mon Nov 10 10:27:52 2014 bohica [...] ntlworld.com - Correspondence added

On Fri May 09 02:22:22 2014, EDAVIS wrote: Show quoted text

> Reposting comment by bohica@ntlworld.com since it is not shown by the > web interface: > > This is an off the cuff comment as I've not had a chance to look at > the code yet. > > DBD::ODBC has returned a type_info_all with 20 columns for ages with > SQL Server drivers and I've never seen this error before. Are you sure > the test isn't complaining that there are 20 columns but only 19 > column names in type_info_all result? e.g., for DBD::ODBC you get the > following and there are 20 column names in the first hash: > > [big dump of data snipped, see 'Download' above to see the full > comment]

Just noting again as this has gone nowhere recently: perl -MDBI -le 'my $h = DBI->connect("dbi:ODBC:gangrad","xx","xx"); my $x = $h->type_info_all; use Data::Dumper; print scalar(@{$x->[-1]});' outputs 20 Martin -- Martin J. Evans Wetherby, UK

Mon Nov 10 10:48:29 2014 bohica [...] ntlworld.com - Correspondence added

On Mon Nov 10 10:27:52 2014, MJEVANS wrote: Show quoted text

> On Fri May 09 02:22:22 2014, EDAVIS wrote:

> > Reposting comment by bohica@ntlworld.com since it is not shown by the > > web interface: > > > > This is an off the cuff comment as I've not had a chance to look at > > the code yet. > > > > DBD::ODBC has returned a type_info_all with 20 columns for ages with > > SQL Server drivers and I've never seen this error before. Are you > > sure > > the test isn't complaining that there are 20 columns but only 19 > > column names in type_info_all result? e.g., for DBD::ODBC you get the > > following and there are 20 column names in the first hash: > > > > [big dump of data snipped, see 'Download' above to see the full > > comment]

> > Just noting again as this has gone nowhere recently: > > perl -MDBI -le 'my $h = DBI->connect("dbi:ODBC:gangrad","xx","xx"); my > $x = $h->type_info_all; use Data::Dumper; print scalar(@{$x->[-1]});' > > outputs 20 > > Martin

Looking at this properly now I think the error is saying 20 fields in each type but there are only 19 keys in the first array with the mapping. As such this is not a bug in DBI but a bug in the DBD you are using which has probably called SQLGetTypeInfo and blindly added all 20 fields without adding a key to the first element. I suggest you report this to whoever maintains the DBD you were using. Martin -- Martin J. Evans Wetherby, UK

Wed Dec 31 11:08:11 2014 EDAVIS [...] cpan.org - Correspondence added

Show quoted text

>As such this is not a bug in DBI but a bug in the DBD

However, this has occurred independently with at least two different DBDs: ODBC and Sybase. Are you quite sure there is nothing in DBI which has caused this? Perhaps a change in the structure from one DBI version to another?

Wed Dec 31 11:17:09 2014 EDAVIS [...] cpan.org - Correspondence added

...although the common element to both is MSSQL. I do suggest you apply the simple patch changing != to < to keep things working until the various DBDs affected can be updated.

Fri Jan 02 04:44:57 2015 bohica [...] ntlworld.com - Correspondence added

On Wed Dec 31 11:08:11 2014, EDAVIS wrote: Show quoted text

> > As such this is not a bug in DBI but a bug in the DBD

> > However, this has occurred independently with at least two different > DBDs: ODBC and Sybase. Are you quite sure there is nothing in DBI > which has caused this? Perhaps a change in the structure from one DBI > version to another?

I am as sure as I can be that nothing in DBI has caused this issue. What has happened is that MS SQL Server has added a new field to SQLTypeInfo and the driver is including that column in the returned rows but not providing an index in the first element. I've not seen any evidence that this happens with DBD::ODBC, only DBD::Sybase. If you can make this happen with DBD::ODBC show me the code and a trace with DBI_TRACE=15. Show quoted text

> ...although the common element to both is MSSQL. I do suggest you apply the > simple patch changing != to < to keep things working until the various DBDs affected can be updated.

I don't agree with this patch. That DBI code is checking there is an index for each column in each row. I've already demonstrated that if you use an ODBC driver which works properly and DBD::ODBC, then this problem does not occur. I'll try and draw Tim's attention to this but I suspect he'll think the same. Martin -- Martin J. Evans Wetherby, UK

Tue Oct 18 04:45:37 2016 EDAVIS [...] cpan.org - Correspondence added

Hi, any update on this please?

Tue Oct 18 04:59:02 2016 EDAVIS [...] cpan.org - Correspondence added

#118422 filed against DBD::Sybase.

Tue Oct 18 05:10:52 2016 bohica [...] ntlworld.com - Correspondence added

On Tue Oct 18 04:45:37 2016, EDAVIS wrote: Show quoted text

> Hi, any update on this please?

I have not changed my opinion on this since the last time. If you turn tracing on (as I detailed previously) with DBD::ODBC and send to me I will happily look at. Martin -- Martin J. Evans Wetherby, UK

Sun Nov 06 11:34:51 2016 EDAVIS [...] cpan.org - Correspondence added

Yes, sorry for the bugspam - the problem is indeed with DBD::Sybase and I have filed a patch against that. Closing this bug.

Sun Nov 06 11:34:52 2016 EDAVIS [...] cpan.org - Status changed from 'open' to 'rejected'