<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
Hello,<br>
<br>
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.<br>
<br>
When I execute on mysql, everything goes well and my traces output the
following:<br>
<br>
<blockquote type="cite">trace1 selfstart=45622542 selfaligmentstrand=1<br>
me.location_id : <b>2884872</b><br>
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'<br>
me.location_id : <b>2884873</b><br>
</blockquote>
<br>
for SQlite I get:<br>
<br>
<blockquote type="cite">trace1 selfstart=45622542 selfaligmentstrand=1<br>
me.location_id : <b>2884872</b><br>
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'<br>
me.location_id : <b>2884872</b><br>
</blockquote>
<br>
If I use directly DBI and execute the same query, evrything goes ok and
I get: <br>
<br>
<blockquote type="cite">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<br>
reference_sequence_id=27<br>
score=1<br>
location_id=<b>2884873</b><br>
distant_id=742163<br>
aligment_id=507781<br>
accession=<br>
end=45620997<br>
strand=1<br>
distant_table=transcript<br>
start=45620932<br>
</blockquote>
<br>
This is the code that executes the query inside DBIx::Class along with
the traces:<br>
<blockquote type="cite"><br>
sub prev_location { #only for stored locations<br>
my ($self) = @_;<br>
warn("I am not sure this can work") unless ($self->in_storage);<br>
print STDERR "trace1 selfstart=".$self->start."
selfaligmentstrand=".$self->aligment->strand."\n";<br>
<b>print STDERR "me.location_id : ".$self->location_id."\n";</b><br>
my $x = $self->aligment->search_related_rs(<br>
'locations',<br>
{'start*strand' => {'<' => $self->start *
$self->aligment->strand}},<br>
{order_by => ['start*strand DESC']}<br>
)->slice(0, 0)->single;<br>
<b>print STDERR "me.location_id : ".$x->location_id."\n";</b><br>
return $x;<br>
}</blockquote>
<br>
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.<br>
<br>
Any help would be appreciated.<br>
<br>
Thank you in advance,<br>
<br>
JF<br>
<br>
<br>
<pre class="moz-signature" cols="72">--
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</pre>
</body>
</html>