Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 66430
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: carlosyr [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08191



Subject: DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Sun, 6 Mar 2011 17:48:52 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 829b
Sorry for my bad English. I found a bug in the execution of LimitDialects between DBIx::Class:SQLMaker::LimitDialects and iSeries V5R3. The error: "Unsupported data struct HASH for ORDER BY" I make work whith the next modification: !!!!!!! --- LimitDialectsOriginal.pm 2011-03-06 16:01:52.000000000 -0430 +++ LimitDialectsNew.pm 2011-03-06 16:48:07.000000000 -0430 @@ -23,7 +23,7 @@ my $last = $rows + $offset; - my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order ); + my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order->{order_by} ); $sql = " SELECT * FROM ( !!!!!! My Plataform: Debian GNU V 6.0 (Squeeze) => (uname -a): Linux os400-linux 2.6.32-5-486 #1 Wed Jan 12 03:28:50 UTC 2011 i686 GNU/Linu Perl: 5.10 DBIx::Class: 0.08127 Carlos Ramírez
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Mon, 07 Mar 2011 00:00:27 +0100
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 838b
Carlos Ramírez via RT wrote: Show quoted text
> Sun Mar 06 17:19:01 2011: Request 66430 was acted upon. > Transaction: Ticket created by carlosyr@gmail.com > Queue: DBIx-Class > Subject: DB2_400_SQL error Fetch First in LimitDialects.pm > Broken in: (no value) > Severity: (no value) > Owner: Nobody > Requestors: carlosyr@gmail.com > Status: new > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=66430 > > > > Sorry for my bad English. > > I found a bug in the execution of LimitDialects between > DBIx::Class:SQLMaker::LimitDialects and iSeries V5R3.
Hello Carlos! Am I understanding correctly you have access to DB2/AS400 ? If yes - the real fix is to test if all of the legacy code can be thrown away. Attached is a patch against 0.08127, please verify it still passes t/746db2_400.t for you. Thanks!
diff --git a/lib/DBIx/Class/SQLMaker/LimitDialects.pm b/lib/DBIx/Class/SQLMaker/LimitDialects.pm index 1c30436..b06362f 100644 --- a/lib/DBIx/Class/SQLMaker/LimitDialects.pm +++ b/lib/DBIx/Class/SQLMaker/LimitDialects.pm @@ -7,78 +7,6 @@ use Carp::Clan qw/^DBIx::Class|^SQL::Abstract|^Try::Tiny/; use List::Util 'first'; use namespace::clean; -# FIXME -# This dialect has not been ported to the subquery-realiasing code -# that all other subquerying dialects are using. It is very possible -# that this dialect is entirely unnecessary - it is currently only -# used by ::Storage::DBI::ODBC::DB2_400_SQL which *should* be able to -# just subclass ::Storage::DBI::DB2 and use the already rewritten -# RowNumberOver. However nobody has access to this specific database -# engine, thus keeping legacy code as-is -# IF someone ever manages to test DB2-AS/400 with RNO, all the code -# in this block should go on to meet its maker -{ - sub _FetchFirst { - my ( $self, $sql, $order, $rows, $offset ) = @_; - - my $last = $rows + $offset; - - my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order ); - - $sql = " - SELECT * FROM ( - SELECT * FROM ( - $sql - $order_by_up - FETCH FIRST $last ROWS ONLY - ) foo - $order_by_down - FETCH FIRST $rows ROWS ONLY - ) bar - $order_by_up - "; - - return $sql; - } - - sub _order_directions { - my ( $self, $order ) = @_; - - return unless $order; - - my $ref = ref $order; - - my @order; - - CASE: { - @order = @$order, last CASE if $ref eq 'ARRAY'; - @order = ( $order ), last CASE unless $ref; - @order = ( $$order ), last CASE if $ref eq 'SCALAR'; - croak __PACKAGE__ . ": Unsupported data struct $ref for ORDER BY"; - } - - my ( $order_by_up, $order_by_down ); - - foreach my $spec ( @order ) - { - my @spec = split ' ', $spec; - croak( "bad column order spec: $spec" ) if @spec > 2; - push( @spec, 'ASC' ) unless @spec == 2; - my ( $col, $up ) = @spec; # or maybe down - $up = uc( $up ); - croak( "bad direction: $up" ) unless $up =~ /^(?:ASC|DESC)$/; - $order_by_up .= ", $col $up"; - my $down = $up eq 'ASC' ? 'DESC' : 'ASC'; - $order_by_down .= ", $col $down"; - } - - s/^,/ORDER BY/ for ( $order_by_up, $order_by_down ); - - return $order_by_up, $order_by_down; - } -} -### end-of-FIXME - =head1 NAME DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality for DBIx::Class::SQLMaker diff --git a/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm b/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm index 29e9da9..eed265f 100644 --- a/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm +++ b/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm @@ -2,53 +2,9 @@ package DBIx::Class::Storage::DBI::ODBC::DB2_400_SQL; use strict; use warnings; -use base qw/DBIx::Class::Storage::DBI::ODBC/; +use base qw/DBIx::Class::Storage::DBI::DB2/; use mro 'c3'; -warn 'Major advances took place in the DBIC codebase since this driver' - .' (::Storage::DBI::ODBC::DB2_400_SQL) was written. However since the' - .' RDBMS in question is so rare it is not possible for us to test any' - .' of the "new hottness". If you are using DB2 on AS-400 please get' - .' in contact with the developer team:' - .' http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class.pm#GETTING_HELP/SUPPORT' - ."\n" -; - -# FIXME -# Most likely all of this code is redundant and unnecessary. We should -# be able to simply use base qw/DBIx::Class::Storage::DBI::DB2/; -# Unfortunately nobody has an RDBMS engine to test with, so keeping -# things as-is for the time being - -sub _dbh_last_insert_id { - my ($self, $dbh, $source, $col) = @_; - - # get the schema/table separator: - # '.' when SQL naming is active - # '/' when system naming is active - my $sep = $dbh->get_info(41); - my $sth = $dbh->prepare_cached( - "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM${sep}SYSDUMMY1", {}, 3); - $sth->execute(); - - my @res = $sth->fetchrow_array(); - - return @res ? $res[0] : undef; -} - -sub _sql_maker_opts { - my ($self) = @_; - - $self->dbh_do(sub { - my ($self, $dbh) = @_; - - return { - limit_dialect => 'FetchFirst', - name_sep => $dbh->get_info(41) - }; - }); -} - 1; =head1 NAME
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Sun, 6 Mar 2011 19:51:34 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 5.9k
Hi Peter First I have to answers you: - Am I understanding correctly you have access to DB2/AS400 ? Yes, I have. Now, I apply the patch and ran test and die with error en insert clausule: DBIx::Class::ResultSet::create(): DBI Exception: DBD::ODBC::db prepare_cached failed: [unixODBC][IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0029 - Falta la clausula INTO de la sentencia intercalada. (SQL-HY000) [for Statement "VALUES(IDENTITY_VAL_LOCAL())"] at t/746db2_400.t line 37 # Looks like your test exited with 11 before it could output anything. t/746db2_400.t .. Dubious, test returned 11 (wstat 2816, 0xb00) Failed 6/6 subtests I will trie to translate the spanish text: DBIx::Class::ResultSet::create(): DBI Exception: DBD::ODBC::db prepare_cached failed: [unixODBC][IBM][Controller/Driver ODBC iSeries Access][DB2 UDB]SQL0029 - Missing INTO clausule of the sentence "intercalated?". (SQL-HY000) [for Statement "VALUES(IDENTITY_VAL_LOCAL())"] at t/746db2_400.t line 37 # Looks like your test exited with 11 before it could output anything. t/746db2_400.t .. Dubious, test returned 11 (wstat 2816, 0xb00) Failed 6/6 subtests Line 37 says: my $new = $schema->resultset('Artist')->create({ name => 'foo' }); After the test, I Created the table artis manually on my OS400 and this created ok, a ran a simple insert clausule and OK. How I Can Help? Show quoted text
> diff --git a/lib/DBIx/Class/SQLMaker/LimitDialects.pm > b/lib/DBIx/Class/SQLMaker/LimitDialects.pm > index 1c30436..b06362f 100644 > --- a/lib/DBIx/Class/SQLMaker/LimitDialects.pm > +++ b/lib/DBIx/Class/SQLMaker/LimitDialects.pm > @@ -7,78 +7,6 @@ use Carp::Clan qw/^DBIx::Class|^SQL::Abstract|^Try::Tiny/; > use List::Util 'first'; > use namespace::clean; > > -# FIXME > -# This dialect has not been ported to the subquery-realiasing code > -# that all other subquerying dialects are using. It is very possible > -# that this dialect is entirely unnecessary - it is currently only > -# used by ::Storage::DBI::ODBC::DB2_400_SQL which *should* be able to > -# just subclass ::Storage::DBI::DB2 and use the already rewritten > -# RowNumberOver. However nobody has access to this specific database > -# engine, thus keeping legacy code as-is > -# IF someone ever manages to test DB2-AS/400 with RNO, all the code > -# in this block should go on to meet its maker > -{ > - sub _FetchFirst { > - my ( $self, $sql, $order, $rows, $offset ) = @_; > - > - my $last = $rows + $offset; > - > - my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order > ); > - > - $sql = " > - SELECT * FROM ( > - SELECT * FROM ( > - $sql > - $order_by_up > - FETCH FIRST $last ROWS ONLY > - ) foo > - $order_by_down > - FETCH FIRST $rows ROWS ONLY > - ) bar > - $order_by_up > - "; > - > - return $sql; > - } > - > - sub _order_directions { > - my ( $self, $order ) = @_; > - > - return unless $order; > - > - my $ref = ref $order; > - > - my @order; > - > - CASE: { > - @order = @$order, last CASE if $ref eq 'ARRAY'; > - @order = ( $order ), last CASE unless $ref; > - @order = ( $$order ), last CASE if $ref eq 'SCALAR'; > - croak __PACKAGE__ . ": Unsupported data struct $ref for ORDER BY"; > - } > - > - my ( $order_by_up, $order_by_down ); > - > - foreach my $spec ( @order ) > - { > - my @spec = split ' ', $spec; > - croak( "bad column order spec: $spec" ) if @spec > 2; > - push( @spec, 'ASC' ) unless @spec == 2; > - my ( $col, $up ) = @spec; # or maybe down > - $up = uc( $up ); > - croak( "bad direction: $up" ) unless $up =~ /^(?:ASC|DESC)$/; > - $order_by_up .= ", $col $up"; > - my $down = $up eq 'ASC' ? 'DESC' : 'ASC'; > - $order_by_down .= ", $col $down"; > - } > - > - s/^,/ORDER BY/ for ( $order_by_up, $order_by_down ); > - > - return $order_by_up, $order_by_down; > - } > -} > -### end-of-FIXME > - > =head1 NAME > > DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like > functionality for DBIx::Class::SQLMaker > diff --git a/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm > b/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm > index 29e9da9..eed265f 100644 > --- a/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm > +++ b/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm > @@ -2,53 +2,9 @@ package DBIx::Class::Storage::DBI::ODBC::DB2_400_SQL; > use strict; > use warnings; > > -use base qw/DBIx::Class::Storage::DBI::ODBC/; > +use base qw/DBIx::Class::Storage::DBI::DB2/; > use mro 'c3'; > > -warn 'Major advances took place in the DBIC codebase since this driver' > - .' (::Storage::DBI::ODBC::DB2_400_SQL) was written. However since the' > - .' RDBMS in question is so rare it is not possible for us to test any' > - .' of the "new hottness". If you are using DB2 on AS-400 please get' > - .' in contact with the developer team:' > - .' > http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class.pm#GETTING_HELP/SUPPORT > ' > - ."\n" > -; > - > -# FIXME > -# Most likely all of this code is redundant and unnecessary. We should > -# be able to simply use base qw/DBIx::Class::Storage::DBI::DB2/; > -# Unfortunately nobody has an RDBMS engine to test with, so keeping > -# things as-is for the time being > - > -sub _dbh_last_insert_id { > - my ($self, $dbh, $source, $col) = @_; > - > - # get the schema/table separator: > - # '.' when SQL naming is active > - # '/' when system naming is active > - my $sep = $dbh->get_info(41); > - my $sth = $dbh->prepare_cached( > - "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM${sep}SYSDUMMY1", {}, 3); > - $sth->execute(); > - > - my @res = $sth->fetchrow_array(); > - > - return @res ? $res[0] : undef; > -} > - > -sub _sql_maker_opts { > - my ($self) = @_; > - > - $self->dbh_do(sub { > - my ($self, $dbh) = @_; > - > - return { > - limit_dialect => 'FetchFirst', > - name_sep => $dbh->get_info(41) > - }; > - }); > -} > - > 1; > > =head1 NAME > >
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Mon, 07 Mar 2011 01:36:12 +0100
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 531b
Carlos Ramírez via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=66430 > > > Hi Peter > > First I have to answers you: > - Am I understanding correctly you have access to DB2/AS400 ? > Yes, I have.
Excellent, you will be my DB2 AS/400 tester-in-residence :) Show quoted text
> Now, I apply the patch and ran test and die with error en insert clausule: > > ... > > How I Can Help? >
Try this new patch I am sending you (again written against 0.08127, do not apply it over the old one).
diff --git a/lib/DBIx/Class/SQLMaker/LimitDialects.pm b/lib/DBIx/Class/SQLMaker/LimitDialects.pm index 1c30436..b06362f 100644 --- a/lib/DBIx/Class/SQLMaker/LimitDialects.pm +++ b/lib/DBIx/Class/SQLMaker/LimitDialects.pm @@ -7,78 +7,6 @@ use Carp::Clan qw/^DBIx::Class|^SQL::Abstract|^Try::Tiny/; use List::Util 'first'; use namespace::clean; -# FIXME -# This dialect has not been ported to the subquery-realiasing code -# that all other subquerying dialects are using. It is very possible -# that this dialect is entirely unnecessary - it is currently only -# used by ::Storage::DBI::ODBC::DB2_400_SQL which *should* be able to -# just subclass ::Storage::DBI::DB2 and use the already rewritten -# RowNumberOver. However nobody has access to this specific database -# engine, thus keeping legacy code as-is -# IF someone ever manages to test DB2-AS/400 with RNO, all the code -# in this block should go on to meet its maker -{ - sub _FetchFirst { - my ( $self, $sql, $order, $rows, $offset ) = @_; - - my $last = $rows + $offset; - - my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order ); - - $sql = " - SELECT * FROM ( - SELECT * FROM ( - $sql - $order_by_up - FETCH FIRST $last ROWS ONLY - ) foo - $order_by_down - FETCH FIRST $rows ROWS ONLY - ) bar - $order_by_up - "; - - return $sql; - } - - sub _order_directions { - my ( $self, $order ) = @_; - - return unless $order; - - my $ref = ref $order; - - my @order; - - CASE: { - @order = @$order, last CASE if $ref eq 'ARRAY'; - @order = ( $order ), last CASE unless $ref; - @order = ( $$order ), last CASE if $ref eq 'SCALAR'; - croak __PACKAGE__ . ": Unsupported data struct $ref for ORDER BY"; - } - - my ( $order_by_up, $order_by_down ); - - foreach my $spec ( @order ) - { - my @spec = split ' ', $spec; - croak( "bad column order spec: $spec" ) if @spec > 2; - push( @spec, 'ASC' ) unless @spec == 2; - my ( $col, $up ) = @spec; # or maybe down - $up = uc( $up ); - croak( "bad direction: $up" ) unless $up =~ /^(?:ASC|DESC)$/; - $order_by_up .= ", $col $up"; - my $down = $up eq 'ASC' ? 'DESC' : 'ASC'; - $order_by_down .= ", $col $down"; - } - - s/^,/ORDER BY/ for ( $order_by_up, $order_by_down ); - - return $order_by_up, $order_by_down; - } -} -### end-of-FIXME - =head1 NAME DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality for DBIx::Class::SQLMaker diff --git a/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm b/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm index 29e9da9..cdbdee6 100644 --- a/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm +++ b/lib/DBIx/Class/Storage/DBI/ODBC/DB2_400_SQL.pm @@ -2,51 +2,22 @@ package DBIx::Class::Storage::DBI::ODBC::DB2_400_SQL; use strict; use warnings; -use base qw/DBIx::Class::Storage::DBI::ODBC/; +use base qw/DBIx::Class::Storage::DBI::DB2/; use mro 'c3'; -warn 'Major advances took place in the DBIC codebase since this driver' - .' (::Storage::DBI::ODBC::DB2_400_SQL) was written. However since the' - .' RDBMS in question is so rare it is not possible for us to test any' - .' of the "new hottness". If you are using DB2 on AS-400 please get' - .' in contact with the developer team:' - .' http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class.pm#GETTING_HELP/SUPPORT' - ."\n" -; - -# FIXME -# Most likely all of this code is redundant and unnecessary. We should -# be able to simply use base qw/DBIx::Class::Storage::DBI::DB2/; -# Unfortunately nobody has an RDBMS engine to test with, so keeping -# things as-is for the time being - sub _dbh_last_insert_id { - my ($self, $dbh, $source, $col) = @_; - - # get the schema/table separator: - # '.' when SQL naming is active - # '/' when system naming is active - my $sep = $dbh->get_info(41); - my $sth = $dbh->prepare_cached( - "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM${sep}SYSDUMMY1", {}, 3); - $sth->execute(); - - my @res = $sth->fetchrow_array(); - - return @res ? $res[0] : undef; -} - -sub _sql_maker_opts { - my ($self) = @_; - - $self->dbh_do(sub { - my ($self, $dbh) = @_; - - return { - limit_dialect => 'FetchFirst', - name_sep => $dbh->get_info(41) - }; - }); + #my ($self, $dbh, $source, $col) = @_; + + # get the schema/table separator: + # '.' when SQL naming is active + # '/' when system naming is active + my $sep = $_[1]->get_info(41); + my $sth = $_[1]->prepare_cached( + "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM${sep}SYSDUMMY1", {}, 3 + ); + $sth->execute(); + + return ($sth->fetchrow_array())[0]; } 1; @@ -65,16 +36,12 @@ over ODBC =head1 DESCRIPTION -This class implements support specific to DB2/400 over ODBC, including -auto-increment primary keys, SQL::Abstract::Limit dialect, and name separator -for connections using either SQL naming or System naming. - - -=head1 AUTHORS +This class implements functionality specific to DB2/400 over ODBC, +particularly support for auto-increment primary keys. -Marc Mims C<< <marc@questright.com> >> +=head1 AUTHOR -Based on DBIx::Class::Storage::DBI::DB2 by Jess Robinson. +See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>. =head1 LICENSE
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Sun, 6 Mar 2011 22:02:19 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 1.7k
Show quoted text
> > Excellent, you will be my DB2 AS/400 tester-in-residence :) > > That's Fine :-D, I hope help
Show quoted text
> Try this new patch I am sending you (again written against 0.08127, do not > apply it over the old one). > >
.... Ok, First, a I comment to you that procedure that I'm doing to apply the patch (maybe I'm doing something wrong) 1. perl -MCPAN -e shell 2. look DBIx::Class 3. patch -p1 < /some/place/patch_you_send_me (this says ok and apply two patch) 4. perl Makefile.PL 5. Edit Makefile and delete all other test and add manually t/746db2_400.t 6. make test Now, this time die in select clausule: BIx::Class::ResultSet::next(): DBI Exception: DBD::ODBC::db prepare_cached failed: [unixODBC][IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0104 - Symbol ( not valid. Symbols valids: , FROM INTO. (SQL-42000) [for Statement " SELECT artistid, name, rank, charfield FROM ( SELECT artistid, name, rank, charfield, ROW_NUMBER() OVER( ORDER BY artistid ) AS rno__row__index FROM ( SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me ) me ) me WHERE rno__row__index BETWEEN 1 AND 3 Now I tell you that the ROW_NUMBER() Function not exist in my OS400 Versión (V5R3) but I think so, exist in more "new"(new against my version 'cause the version OS is in V7R1 :-s ) versions (like V5R4). Unfortunally I can't probe in superior version (In Inferior Version V5R1, broken the original sentence based in Fetch First 'cause this version not admit complex subquerys like Order By "anidate?") The Symbol "(" that refer the error it's the parentesis of function OVER that's no exist in my OS Version neither For my OS Version the clausule to paginate have to be based on Fetch First :(, (but your clausule it's fine as ibm documentation for release V5R4 and superior) Again sorry for my english. Carlos
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Mon, 07 Mar 2011 03:50:57 +0100
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 3.4k
Carlos Ramírez via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=66430 > >
>> Excellent, you will be my DB2 AS/400 tester-in-residence :) >> >> That's Fine :-D, I hope help
> > >
>> Try this new patch I am sending you (again written against 0.08127, do not >> apply it over the old one). >> >>
> .... > > Ok, First, a I comment to you that procedure that I'm doing to apply the > patch (maybe I'm doing something wrong) > 1. perl -MCPAN -e shell > 2. look DBIx::Class > 3. patch -p1 < /some/place/patch_you_send_me (this says ok and apply two > patch) > 4. perl Makefile.PL > 5. Edit Makefile and delete all other test and add manually t/746db2_400.t
^^^ never do this, editing the makefile is quite insane :) Show quoted text
> 6. make test
`prove -l t/746db2_400.t` is what you want Show quoted text
> Now, this time die in select clausule: > > BIx::Class::ResultSet::next(): DBI Exception: DBD::ODBC::db prepare_cached > failed: [unixODBC][IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0104 > - Symbol ( not valid. Symbols valids: , FROM INTO. (SQL-42000) [for > Statement " > SELECT artistid, name, rank, charfield FROM ( > SELECT artistid, name, rank, charfield, ROW_NUMBER() OVER( ORDER BY > artistid ) AS rno__row__index FROM ( > SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me > ) me > ) me WHERE rno__row__index BETWEEN 1 AND 3 > > > Now I tell you that the ROW_NUMBER() Function not exist in my OS400 Versión > (V5R3) but I think so, exist in more "new"(new against my version 'cause > the version OS is in V7R1 :-s ) versions (like V5R4). Unfortunally I can't > probe in superior version (In Inferior Version V5R1, broken the original > sentence based in Fetch First 'cause this version not admit complex > subquerys like Order By "anidate?") > > The Symbol "(" that refer the error it's the parentesis of function OVER > that's no exist in my OS Version neither > > For my OS Version the clausule to paginate have to be based on Fetch First > :(, (but your clausule it's fine as ibm documentation for release V5R4 and > superior) >
Excellent! It is very late now, so I am going to send you a "fetch-first" patch tomorrow. Even with this patch however you need to be aware of a major drawback of the FETCH FIRST technique - it has a serious bug that can not be worked around: Consider what happens if you have a 5-row resultset, and you do: ->search({}, { rows => 3, offset => 4 }); The resulting SQL will be roughly SELECT * FROM ( SELECT * FROM ( SELECT * FROM foo ORDER BY foo.id FETCH FIRST 7 ROWS ONLY ) ORDER BY foo.id DESC FETCH FIRST 3 ROWS ONLY ) ORDER BY foo.id instead of the expected 1 row you got 3(!). Therefore what you need to do is 1) give me a reliable way to distinguish one db2 version from another, and tell me which is the minimal version supporting ROW_NUMBER OVER. As I have shown above we always want to use RNO if it is available. 2) do some research and see if there are any other ways to do offset-limiting with your version of DB2 (again, since FETCH FIRST is potentially buggy). Here is an example how we determine the limit dialect for MSSQL: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=lib/DBIx/Class/Storage/DBI/MSSQL.pm;h=07d3a4f20965135031c13e968e9acefeb4bdfe15;hb=HEAD#l190 Show quoted text
> > Again sorry for my english.
This is the 3rd email in a row where you apologize for your decent english, can you please stop that? I can understand you and you can understand me, ergo your english is just fine :)
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Mon, 7 Mar 2011 11:48:26 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 3.6k
Hi Peter, Show quoted text
> > 5. Edit Makefile and delete all other test and add manually t/746db2_400.t
> > ^^^ never do this, editing the makefile is quite insane :) >
> > 6. make test
> > `prove -l t/746db2_400.t` is what you want >
Ok, Understood, This it's my first time that help in something like this, never gonna happen again. Thanks :) Show quoted text
> Consider what happens if you have a 5-row resultset, and you do: > > ->search({}, { rows => 3, offset => 4 }); > > The resulting SQL will be roughly > > SELECT * FROM ( >   SELECT * FROM ( >     SELECT * FROM foo ORDER BY foo.id >     FETCH FIRST 7 ROWS ONLY >   ) >   ORDER BY foo.id DESC >   FETCH FIRST 3 ROWS ONLY > ) > ORDER BY foo.id > > instead of the expected 1 row you got 3(!).
You're right it's a horrible bug. Show quoted text
> > Therefore what you need to do is > 1) give me a reliable way to distinguish one db2 version from another, and
Jejeje, You broken my head with this homework, but at the end, the solution it's a quite simple After of broken my head (and my system) for many hours, and not found any solution for obtain DB2 Version trough SQL, I look you patch and advised from function get_info for obtain the system name separator, then I make this simple perl program: #!/usr/bin/perl -w use DBI; my $dbh = DBI->connect("dbi:ODBC:DSN","USER","password"); for($i=0; $i<=41;$i++){ print $i," => ",$dbh->func($i,GetInfo),"\n"; } Note: I used 41 for you reference of separator. So, the func->(18,GetInfo) answer the DB2(and OS version) in the next format Version.Release.build_packs_and_other I ran the test against two system: V5R1 and V5R3. (I don't have access to another) this was the result: V5R1 (Versión 5 release 1): 05.01.0010 V5R3 (Versión 5 release 3): 05.03.0014 (Another tip: with 16 Obtain the SYSTEM NAME ) Show quoted text
> tell me which is the minimal version supporting ROW_NUMBER OVER. As I have > shown above we always want to use RNO if it is available.
I read IBM Doc. and say's it's V5R4. I Talked with some friends to research version of OS/DB (it' the same) but I have not positive answers yet to probe. But in multiple sites on Internet the people ran ROW_NUMBER OVER with V5R4. Other tip: V5R1 can not execute complex subquery Fetch First Not function, Order By anidate no Function neither. Another way to comunicate with AS400/DB2 it's with DBD::DB2 but it's necesary the program (and licensing) the DB2Connector. and it's for pay. for that I use ODBC. The Versión of the ODBC iSeries that i use it's 5.04 (V5R4) but there are V5R7. Show quoted text
> 2) do some research and see if there are any other ways to do offset-limiting > with your version of DB2 (again, since FETCH FIRST is potentially buggy). >
Until now, nothing. I find the use of rrn() but it's not functionally, by example: Assuming one table (artistid,name) and 5 records: Select rrn(artist) as row,* from artist: row artistid name 1 1 Carlos 2 2 Pedro 3 3 Juan 4 4 Peter 5 5 Charlie Next delete one id by example artistid 2 Delete from artist where artistid = 2 Now Select: Select rrn(artist) as row,* from artist: row artistid name 1 1 Carlos 3 3 Juan 4 4 Peter 5 5 Charlie You See? This it's 'cause the rrn() it's a position relative when was insert the record and identify each row simply and unique. Show quoted text
> > Again sorry for my english.
> > This is the 3rd email in a row where you apologize for your decent english, > can you please stop that? I can understand you and you can understand me, > ergo your english is just fine :)
I learned by my self, with nobody to practice, so, I don't know if people understand me well. But if you say that wer'e OK :) Thanks a lot.
Download (untitled) / with headers
text/plain 152b
Hi, What you sent me is really helpful, I just got super busy and didn't have a chance to send you the update. I will do so a bit later today, Cheers!
Download (untitled) / with headers
text/plain 103b
Hi Carlos, any chance you could arrange an SSH tunnel to a test DB on your AS/400 DB2 instance for us?
Download (untitled) / with headers
text/plain 275b
Please try my branch with FetchFirst fixes and other refactorings: git clone git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git dbic cd dbic git branch --track people/caelum/db2_limit_dialect origin/people/caelum/db2_limit_dialect git checkout people/caelum/db2_limit_dialect
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Mon, 28 Mar 2011 19:20:59 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 471b
On 22/03/11 06:35, Rafael Kitover via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=66430> > > Hi Carlos, > > any chance you could arrange an SSH tunnel to a test DB on your AS/400 > DB2 instance for us? >
Hi Rafael, Sorry for the too late answer. I really appreciate your help, but I can't do that for security reasons. My network environment it's quite complicated. my network administrator says no. But I can run all test that you need. Carlos
Download (untitled) / with headers
text/plain 256b
We merged the improvements into mainline. Marking the bug as patched for the time being, but please do test when you have a chance. http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commit;h=96eacdb705e37cca2a5a420ec92e353d0d8823b9
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Fri, 1 Apr 2011 14:32:59 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 954b
2011/3/28 Rafael Kitover via RT <bug-DBIx-Class@rt.cpan.org>: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=66430 > > > Please try my branch with FetchFirst fixes and other refactorings: > > git clone git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git dbic > cd dbic > git branch --track people/caelum/db2_limit_dialect > origin/people/caelum/db2_limit_dialect > git checkout people/caelum/db2_limit_dialect >
Hi Rafael/Peter, sorry for the late answer, I have been very busy in recent days. All test ran successfull. This was against System V5R3 This was the procedure: git clone git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git dbic cd dbic git branch --track people/caelum/db2_limit_dialect git branch --track origin/people/caelum/db2_limit_dialect git checkout people/caelum/db2_limit_dialect export DBICTEST_DB2_400_(DSN|_USER|_PASS)=right_value_for_every_one prove t/746db2_400.t I'm not familiar with using git. It's the procedure OK?
Download (untitled) / with headers
text/plain 1.2k
On Fri Apr 01 15:03:07 2011, carlosyr@gmail.com wrote: Show quoted text
> 2011/3/28 Rafael Kitover via RT <bug-DBIx-Class@rt.cpan.org>:
> > <URL: https://rt.cpan.org/Ticket/Display.html?id=66430 > > > > > Please try my branch with FetchFirst fixes and other refactorings: > > > > git clone git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git dbic > > cd dbic > > git branch --track people/caelum/db2_limit_dialect > > origin/people/caelum/db2_limit_dialect > > git checkout people/caelum/db2_limit_dialect > >
> > Hi Rafael/Peter, sorry for the late answer, I have been very busy in > recent days. All test ran successfull. This was against System V5R3 > > This was the procedure: > > git clone git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git dbic > cd dbic > git branch --track people/caelum/db2_limit_dialect > git branch --track origin/people/caelum/db2_limit_dialect > git checkout people/caelum/db2_limit_dialect > > export DBICTEST_DB2_400_(DSN|_USER|_PASS)=right_value_for_every_one > prove t/746db2_400.t > > > I'm not familiar with using git. It's the procedure OK?
You did the checkout correctly. However you ran prove incorrectly - prove <test> simply runs the test against you system-installed lib. What you want instead is prove -l <test>
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Sun, 3 Apr 2011 06:34:39 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 1.1k
Show quoted text
> > You did the checkout correctly. However you ran prove incorrectly - > prove <test> simply runs the test against you system-installed lib. What > you want instead is prove -l <test> > > > >
Thanks Peter, I run again with prove -l and fails: t/746db2_400.t .. DBIx::Class::ResultSet::create(): DBI Exception: DBD::ODBC::db prepare_cached failed: [unixODBC][IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL5016 - Texto de mensaje de error no disponible. El mensaje no se puede traducir satisfactoriamente. (SQL-HY000) [for Statement "SELECT IDENTITY_VAL_LOCAL() FROM sysibm.sysdummy1"] at t/746db2_400.t line 38 # Looks like your test exited with 11 before it could output anything. t/746db2_400.t .. Dubious, test returned 11 (wstat 2816, 0xb00) Failed 6/6 subtests Test Summary Report ------------------- t/746db2_400.t (Wstat: 2816 Tests: 0 Failed: 0) Non-zero exit status: 11 Parse errors: Bad plan. You planned 6 tests but ran 0. Files=1, Tests=0, 2 wallclock secs ( 0.03 usr 0.02 sys + 0.49 cusr 0.06 csys = 0.60 CPU) Result: FAIL Spanish Text: Text Message not available. Can't Translate the message. Carlos
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Mon, 04 Apr 2011 09:10:30 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 1.2k
Carlos Ramírez via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=66430 > >
>> You did the checkout correctly. However you ran prove incorrectly - >> prove <test> simply runs the test against you system-installed lib. What >> you want instead is prove -l <test> >> >> >> >>
> > Thanks Peter, > > I run again with prove -l and fails: > > t/746db2_400.t .. DBIx::Class::ResultSet::create(): DBI Exception: > DBD::ODBC::db prepare_cached failed: [unixODBC][IBM][Controlador ODBC > de iSeries Access][DB2 UDB]SQL5016 - Texto de mensaje de error no > disponible. El mensaje no se puede traducir satisfactoriamente. > (SQL-HY000) [for Statement "SELECT IDENTITY_VAL_LOCAL() FROM > sysibm.sysdummy1"] at t/746db2_400.t line 38 > # Looks like your test exited with 11 before it could output anything. > t/746db2_400.t .. Dubious, test returned 11 (wstat 2816, 0xb00) > Failed 6/6 subtests > > Test Summary Report > ------------------- > t/746db2_400.t (Wstat: 2816 Tests: 0 Failed: 0) > Non-zero exit status: 11 > Parse errors: Bad plan. You planned 6 tests but ran 0. > Files=1, Tests=0, 2 wallclock secs ( 0.03 usr 0.02 sys + 0.49 cusr > 0.06 csys = 0.60 CPU) > Result: FAIL >
Ouch, exit 11 is a segfault. Something else is fishy. Which perl version are you using?
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Mon, 4 Apr 2011 12:29:33 -0400
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 177b
Show quoted text
> Ouch, exit 11 is a segfault. Something else is fishy. Which perl version > are you using? > >
hmmm... Perl: 5.10 on Debian squeeze.... Let me do the test on another machine.
Download (untitled) / with headers
text/plain 269b
On Mon Apr 04 12:29:48 2011, carlosyr@gmail.com wrote: Show quoted text
> > Ouch, exit 11 is a segfault. Something else is fishy. Which perl
> version
> > are you using? > > > >
> > hmmm... Perl: 5.10 on Debian squeeze.... Let me do the test on another > machine.
Any luck with this?
Subject: Re: [rt.cpan.org #66430] DB2_400_SQL error Fetch First in LimitDialects.pm
Date: Sat, 30 Apr 2011 09:03:42 -0430
To: bug-DBIx-Class [...] rt.cpan.org
From: Carlos Ramírez <carlosyr [...] gmail.com>
Download (untitled) / with headers
text/plain 620b
El vie, 29-04-2011 a las 22:17 -0400, Peter Rabbitson via RT escribió: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=66430 > > > On Mon Apr 04 12:29:48 2011, carlosyr@gmail.com wrote:
> > > Ouch, exit 11 is a segfault. Something else is fishy. Which perl
> > version
> > > are you using? > > > > > >
> > > > hmmm... Perl: 5.10 on Debian squeeze.... Let me do the test on another > > machine.
> > Any luck with this? >
Sorry Peter, I can't do the test yet. I actually installing a virtual machine (kvm) for this, but for personal reasons I can't complete yet. Maybe this weekend I can't do the test again.
Download (untitled) / with headers
text/plain 448b
On Sat Apr 30 09:34:37 2011, carlosyr@gmail.com wrote: Show quoted text
> El vie, 29-04-2011 a las 22:17 -0400, Peter Rabbitson via RT escribió: > Sorry Peter, I can't do the test yet. > > I actually installing a virtual machine (kvm) for this, but for personal > reasons I can't complete yet. >
Sure thing. For the time being I will mark this ticket as resolved, as the devs are pretty confident they nailed it. Feel free to re-open it if the problem persists.


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.