2010/6/21 Peter Rabbitson <[email protected] <rabbit%[email protected]>>
> thomas Dauby wrote: > >> Hi all, >> >> I recently upgraded to DBIx::Class 0.08123, and paging does not work >> anymore. >> >> I get the following error: >> >> query: Error while executing query:DBIx::Class::ResultSet::next(): Generic >> Subquery Limit order criteria column 'me.start_time' must be unique (no >> unique constraint found) at VTOM/Repo2.pm line 322 at WEB/Action.pm line 119 >> >> I modified to order by clause to use a column with a unique contraint, but >> the generated SQL is painfully slow: >> >> > SELECT environment_name, start_time, end_time, id, return_status, > >> application_name, job_name, duration FROM ( SELECT environment.name < >> http://environment.name> AS environment_name, me.start_time, me.end_time, >> me.id <http://me.id>, me.return_status, application.name < >> http://application.name> AS application_name, job.name <http://job.name> >> AS job_name, me.duration FROM job_exec me JOIN host host ON host.id < >> http://host.id> = me.host_id JOIN users users ON users.id < >> http://users.id> = me.users_id JOIN job job ON job.id <http://job.id> = >> me.job_id JOIN application application ON application.id < >> http://application.id> = job.application_id JOIN environment environment >> ON environment.id <http://environment.id> = application.environment_id >> WHERE ( return_status NOT IN ( ? ) ) ) me WHERE ( SELECT COUNT(*) FROM >> job_exec rownum__emulation WHERE rownum__emulation.id < >> http://rownum__emulation.id> > me.id <http://me.id> ) BETWEEN 690 AND 719 >> ORDER BY me.id <http://me.id> DESC : '0' >> >> >> FYI, here's the generated SQL using the previous version: >> >> > SELECT environment.name <http://environment.name>, me.start_time, > >> me.end_time, me.id <http://me.id>, me.return_status, application.name < >> http://application.name>, job.name <http://job.name>, me.duration FROM >> job_exec me JOIN host host ON host.id <http://host.id> = me.host_id JOIN >> users users ON users.id <http://users.id> = me.users_id JOIN job job ON >> job.id <http://job.id> = me.job_id JOIN application application ON >> application.id <http://application.id> = job.application_id JOIN >> environment environment ON environment.id <http://environment.id> = >> application.environment_id WHERE ( ( environment.name < >> http://environment.name> = ? AND return_status NOT IN ( ? ) ) ) ORDER BY >> start_time desc: 'paris_divers', '0' >> >> >> The new SQL comes straight from DBIx::Class::SQLAHacks, but I dont know >> where the old sql comes from... >> >> Is there a way to go back to the old method? I'm not familiar enough with >> the codebase and the code flow to modify it, but I'm using Sybase 15, so for >> this operations, I guess scrollable cursors could be used. What shall I >> modify to do so? >> >> > Before 08122 DBIx::Class would default to software_limit => 1 when used > with Sybase, because the generic subquery method didn't function at all. > While software_limit is not documented (stupid omission, will be fixed in > the next version) it essentially removes *all* limits from the SQL query > and does the "fast-forwarding" on the client side within DBIC. For a usage > example see [1]. So this is how you can switch between the new and old > method, depending on what will be more beneficial for you. > > On your suggestion of "scrollable cursors" - we are very interested into > having this kind of functionality baked into DBIC, could you elaborate more > on how this could work? (best show a pure-DBI example, it will be clearer > for us how to incorporate that into the DBIC machinery) > > Cheers! > > [1] > http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/75limit.t;h=686161af223473ebd475d6c563cf71a395dce8ab;hb=refs/heads/master#l29 > > _______________________________________________ > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > IRC: irc.perl.org#dbix-class > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > Searchable Archive: > http://www.grokbase.com/group/[email protected] > Thanks, it worked! I see no easy way to use cursors in DBD::Sybase. Here's how I'd do it: use strict ; > use warnings; > use DBD::Sybase; > use Data::Dumper; > > my $conn = > DBI->connect('dbi:Sybase:'.$ENV{DSQUERY},$ENV{DSUSER},$ENV{DSPASSWD}, > {'loginTimeout' => 5,'timeout' => 10,'PrintError' > => 1,'AutoCommit' => 1,syb_chained_txn => 0}) || die "Error while > connecting"; > > # declare cursor must be the only statement in a query batch > $conn->do(q{ > declare process_Csr *scroll* cursor for > select name, type > from sysobjects > order by name > }); > > my $sth = $conn->prepare(q{ > /* > here we could use > set cursor rows 10 > to fetch 10 rows and stop, instead of using @cnt > */ > > open process_Csr > > declare @cnt int > select @cnt = 1 > > -- go to row 4 and fetch > fetch absolute 4 process_Csr > > while ( @@sqlstatus = 0 AND @cnt < 10 ) > begin > select @cnt = @cnt + 1 > fetch process_Csr > end > > }) || die "Error while preparing statement\n"; > > $sth->execute(); > > { > my $row; > > while($row = $sth->fetch) { > print Dumper\ $row; > } > # returns one resultset per cursor fetch > redo if $sth->{syb_more_results}; > > } > Thanks a lot, Thomas
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
