[Dbix-class] problem with dbix-class fetch from mysql vs sqlite

[Jean-Francois Lucier]

Hello, I have build an API using DBIx::Class that executes many queries on either SQLite or mysql (same database schema). I have put traces in my script. When I execute on mysql, everything goes well and my traces output the following: trace1 selfstart=45622542 selfaligmentstrand=1 me.location_id : 2884872 SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id, aligment.aligment_id, aligment.strand, aligment.reference_sequence_id, aligment.score, aligment.distant_id, aligment.distant_table FROM location me  JOIN aligment aligment ON ( aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY start*strand DESC LIMIT 1: '45622542', '507781' me.location_id : 2884873 for SQlite I get: trace1 selfstart=45622542 selfaligmentstrand=1 me.location_id : 2884872 SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id, aligment.aligment_id, aligment.strand, aligment.reference_sequence_id, aligment.score, aligment.distant_id, aligment.distant_table FROM location me  JOIN aligment aligment ON ( aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY start*strand DESC LIMIT 1: '45622542', '507781' me.location_id : 2884872 If I use directly DBI and execute the same query, evrything goes ok and I get: the following query will be executed: SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id, aligment.aligment_id, aligment.strand, aligment.reference_sequence_id, aligment.score, aligment.distant_id, aligment.distant_table FROM location me  JOIN aligment aligment ON ( aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand < 45622542 ) AND ( me.aligment_id = 507781 ) ) ) ORDER BY start*strand DESC LIMIT 1 reference_sequence_id=27 score=1 location_id=2884873 distant_id=742163 aligment_id=507781 accession= end=45620997 strand=1 distant_table=transcript start=45620932 This is the code that executes the query inside DBIx::Class along with the traces: sub prev_location {    #only for stored locations     my ($self) = @_;     warn("I am not sure this can work") unless ($self->in_storage);     print STDERR "trace1 selfstart=".$self->start." selfaligmentstrand=".$self->aligment->strand."\n";     print STDERR "me.location_id : ".$self->location_id."\n";     my $x = $self->aligment->search_related_rs(         'locations',         {'start*strand' => {'<' => $self->start * $self->aligment->strand}},         {order_by => ['start*strand DESC']}     )->slice(0, 0)->single;     print STDERR "me.location_id : ".$x->location_id."\n";     return $x; } I have runned the query inside the sqlite3 console and I get exactly the same result as in the mysql console for the this query (the right data). This problem is for sure linked to DBIx::Class with SQLite since I get the right result when I use DBI alone to execute the query. Any help would be appreciated. Thank you in advance, JF -- IMPORTANT: Veuillez penser à l'environnement avant d'imprimer ce courriel Jean-Francois Lucier Bio-informaticien B10 solutions, consultation en bioinformatique Centre de recherche clinique du CHUS 3001, 12e avenue Nord Sherbrooke (Québec) J1H 5N4 CANADA Téléphone: (819) 820-6868 ext: 12577 Fax: (819) 564-5392

_______________________________________________
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]