Bug #14040 for Class-DBI-FormBuilder: CDBI upgrade problem with MSSQL

Sun Aug 07 04:49:46 2005 Guest - Ticket created

Subject:

CDBI upgrade problem with MSSQL

I just tried to upgrade CDBI::FormBuilder from .351 to .421 and had problems with my MSSQL table. I'm using CDBI::MSSQL as a base class and the odbc driver (dsn = dbi:ODBC:dbname). Here is the error I get: undef error - DBD::ODBC::st execute failed: [unixODBC][Driver Manager]Function sequence error (SQL-HY010)(DBD: st_execute/SQLExecute err=-1) [for Statement "SQLColumns"] at /usr/local/lib/perl5/site_perl/5.8.6/DBIx/ContextualFetch.pm line 52. - at /usr/local/lib/perl5/site_perl/5.8.6/Maypole.pm line 123 I found that if I eliminate the execute() statement from _load_meta, I get further. Then I get: :undef error - DBD::ODBC::st fetchall_hashref failed: Field 'TABLE_CAT' does not exist (not one of table_schem char_octet_length column_size column_def ordinal_position buffer_length data_type column_name sql_datetime_sub decimal_digits remarks ss_data_type is_nullable sql_data_type type_name table_cat nullable num_prec_radix table_name) [for Statement "SQLColumns"] at /usr/local/lib/perl5/site_perl/5.8.6/Class/DBI/FormBuilder.pm line 841. - at /usr/local/lib/perl5/site_perl/5.8.6/Maypole.pm Changing the columns to lowercase in the fetchall_hashref call gets past that, but then there are problems in column_meta with this line: my @rv = map { $them->__fb_meta->{ $_ }->{ $k } } @columns; To fix that, in _load_meta I replaced: my $column_info = $sth->fetchall_hashref( [ qw(TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME)])->{''}->{''}->{$table}; with: my $column_info = $sth->fetchall_hashref( [ qw( table_cat table_schem table_name column_name ) ] )->{'pathdb'}->{'dbo'}->{ $table }; and changed: $meta->{ $col } = { map { $_ => $column_info->{ $col }->{ $_ } } to: $meta->{ $col } = { map { $_ => $column_info->{ $col }->{ lc($_) } } This resolved my issue for mssql, but of course breaks it for mysql. I'm not sure how to best integrate these changes transparently.

Tue Aug 09 05:09:40 2005 cpan.zerofive [...] googlemail.com - Taken

Tue Aug 16 17:42:07 2005 cpan.zerofive [...] googlemail.com - Correspondence added

[guest - Sun Aug 7 04:49:46 2005]: Show quoted text

> I just tried to upgrade CDBI::FormBuilder from .351 to .421 and had > problems with my MSSQL table. I'm using CDBI::MSSQL as a base > class and the odbc driver (dsn = dbi:ODBC:dbname). Here is the > error I get: > undef error - DBD::ODBC::st execute failed: [unixODBC][Driver > Manager]Function sequence error (SQL-HY010)(DBD: > st_execute/SQLExecute err=-1) [for Statement "SQLColumns"] at > /usr/local/lib/perl5/site_perl/5.8.6/DBIx/ContextualFetch.pm line > 52. > - at /usr/local/lib/perl5/site_perl/5.8.6/Maypole.pm line 123 > > I found that if I eliminate the execute() statement from _load_meta, I > get further. Then I get: > :undef error - DBD::ODBC::st fetchall_hashref failed: Field > 'TABLE_CAT' does not exist (not one of table_schem > char_octet_length column_size column_def ordinal_position > buffer_length data_type column_name sql_datetime_sub decimal_digits > remarks ss_data_type is_nullable sql_data_type type_name table_cat > nullable num_prec_radix table_name) [for Statement "SQLColumns"] at > /usr/local/lib/perl5/site_perl/5.8.6/Class/DBI/FormBuilder.pm line > 841. > - at /usr/local/lib/perl5/site_perl/5.8.6/Maypole.pm > > Changing the columns to lowercase in the fetchall_hashref call gets > past that, but then there are problems in column_meta with this > line: > my @rv = map { $them->__fb_meta->{ $_ }->{ $k } } @columns; > > To fix that, in _load_meta I replaced: > my $column_info = $sth->fetchall_hashref( [ qw(TABLE_CAT TABLE_SCHEM > TABLE_NAME COLUMN_NAME)])->{''}->{''}->{$table}; > with: > my $column_info = $sth->fetchall_hashref( [ qw( table_cat table_schem > table_name column_name ) ] )->{'pathdb'}->{'dbo'}->{ $table }; > > and changed: > $meta->{ $col } = { map { $_ => $column_info->{ $col }->{ $_ } } > to: > $meta->{ $col } = { map { $_ => $column_info->{ $col }->{ lc($_) } } > > This resolved my issue for mssql, but of course breaks it for mysql. > I'm not sure how to best integrate these changes transparently.

Ron, if you leave the execute() statement in, but change the terms to lowercase, does it still work? From the above I understand that you are getting usable results with the execute statement removed? I don't understand that. d.

Thu Aug 18 16:55:18 2005 Guest - Correspondence added

From:

ron mcclain

[DAVEBAIRD - Tue Aug 16 17:42:07 2005]: Show quoted text

> [guest - Sun Aug 7 04:49:46 2005]: >

> > I just tried to upgrade CDBI::FormBuilder from .351 to .421 and had > > problems with my MSSQL table. I'm using CDBI::MSSQL as a base > > class and the odbc driver (dsn = dbi:ODBC:dbname). Here is the > > error I get: > > undef error - DBD::ODBC::st execute failed: [unixODBC][Driver > > Manager]Function sequence error (SQL-HY010)(DBD: > > st_execute/SQLExecute err=-1) [for Statement "SQLColumns"] at > > /usr/local/lib/perl5/site_perl/5.8.6/DBIx/ContextualFetch.pm line > > 52. > > - at /usr/local/lib/perl5/site_perl/5.8.6/Maypole.pm line 123 > > > > I found that if I eliminate the execute() statement from _load_meta, I > > get further. Then I get: > > :undef error - DBD::ODBC::st fetchall_hashref failed: Field > > 'TABLE_CAT' does not exist (not one of table_schem > > char_octet_length column_size column_def ordinal_position > > buffer_length data_type column_name sql_datetime_sub decimal_digits > > remarks ss_data_type is_nullable sql_data_type type_name table_cat > > nullable num_prec_radix table_name) [for Statement "SQLColumns"] at > > /usr/local/lib/perl5/site_perl/5.8.6/Class/DBI/FormBuilder.pm line > > 841. > > - at /usr/local/lib/perl5/site_perl/5.8.6/Maypole.pm > > > > Changing the columns to lowercase in the fetchall_hashref call gets > > past that, but then there are problems in column_meta with this > > line: > > my @rv = map { $them->__fb_meta->{ $_ }->{ $k } } @columns; > > > > To fix that, in _load_meta I replaced: > > my $column_info = $sth->fetchall_hashref( [ qw(TABLE_CAT TABLE_SCHEM > > TABLE_NAME COLUMN_NAME)])->{''}->{''}->{$table}; > > with: > > my $column_info = $sth->fetchall_hashref( [ qw( table_cat table_schem > > table_name column_name ) ] )->{'pathdb'}->{'dbo'}->{ $table }; > > > > and changed: > > $meta->{ $col } = { map { $_ => $column_info->{ $col }->{ $_ } } > > to: > > $meta->{ $col } = { map { $_ => $column_info->{ $col }->{ lc($_) } } > > > > This resolved my issue for mssql, but of course breaks it for mysql. > > I'm not sure how to best integrate these changes transparently.

> > Ron, > > if you leave the execute() statement in, but change the terms to > lowercase, does it still work? From the above I understand that you are > getting usable results with the execute statement removed? I don't > understand that. > > d.

If I leave the execute statement in, the mssql based model fails every time with the first error above no matter what. For some reason, taking out the execute seems to work ok on both mssql and mysql. I guess the fetch* functions must do an implicit execute or something. Sorry I can't be more detailed right now.. I'll be rolling out v.1 of my app Monday and then will be back to trying to upgrade to all the latest versions in my dev environment for the next release.

Sun Aug 21 08:22:37 2005 cpan.zerofive [...] googlemail.com - Correspondence added

[guest - Thu Aug 18 16:55:18 2005]: Show quoted text

> [DAVEBAIRD - Tue Aug 16 17:42:07 2005]: >

> > [guest - Sun Aug 7 04:49:46 2005]: > >

> > > I just tried to upgrade CDBI::FormBuilder from .351 to .421 and had > > > problems with my MSSQL table. I'm using CDBI::MSSQL as a base > > > class and the odbc driver (dsn = dbi:ODBC:dbname). Here is the > > > error I get:

Ron, can you try with the latest version? Hopefully it's fixed, if not, it should be easier to debug and come up with a fix. The metadata functionality has been moved out into a couple of helper classes. d.

Mon Sep 19 18:42:42 2005 Guest - Correspondence added

From:

rmcclain [...] megapath.net

[DAVEBAIRD - Sun Aug 21 08:22:37 2005]: Show quoted text

> [guest - Thu Aug 18 16:55:18 2005]: >

> > [DAVEBAIRD - Tue Aug 16 17:42:07 2005]: > >

> > > [guest - Sun Aug 7 04:49:46 2005]: > > >

> > > > I just tried to upgrade CDBI::FormBuilder from .351 to .421 and had > > > > problems with my MSSQL table. I'm using CDBI::MSSQL as a base > > > > class and the odbc driver (dsn = dbi:ODBC:dbname). Here is the > > > > error I get:

> > Ron, can you try with the latest version? Hopefully it's fixed, if not, > it should be easier to debug and come up with a fix. The metadata > functionality has been moved out into a couple of helper classes. > > d.

Seems to work ok now. Thanks ;)

Tue Sep 20 05:01:34 2005 cpan.zerofive [...] googlemail.com - Correspondence added

[guest - Mon Sep 19 18:42:42 2005]: Show quoted text

> [DAVEBAIRD - Sun Aug 21 08:22:37 2005]: >

> > [guest - Thu Aug 18 16:55:18 2005]: > >

> > > [DAVEBAIRD - Tue Aug 16 17:42:07 2005]: > > >

> > > > [guest - Sun Aug 7 04:49:46 2005]: > > > >

> > > > > I just tried to upgrade CDBI::FormBuilder from .351 to .421

and had Show quoted text

> > > > > problems with my MSSQL table. I'm using CDBI::MSSQL as a base > > > > > class and the odbc driver (dsn = dbi:ODBC:dbname). Here is the > > > > > error I get:

> > > > Ron, can you try with the latest version? Hopefully it's fixed, if not, > > it should be easier to debug and come up with a fix. The metadata > > functionality has been moved out into a couple of helper classes. > > > > d.

> > > Seems to work ok now. Thanks ;)

Yay! That's great, thanks for letting me know. Hopefully that means it's pretty robust to other db peculiarities too. d.

Tue Sep 20 05:01:35 2005 cpan.zerofive [...] googlemail.com - Status changed from 'new' to 'resolved'