You are viewing a plain text version of this content. The canonical link for it is here.
Posted to embperl@perl.apache.org by Joshua Spoerri <jo...@yucs.org> on 2001/11/08 23:03:35 UTC
PrimKey -> other fields ignored
Is this a known bug:
querying with primary keys ignores conditions for other fields
?
for example:
*i=DBIx::Recordset->Setup({
'!DataSource'=>'dbi:mysql:mydb',
'!Table'=>'mytable',
'!PrimKey'=>'a'
});
$i->Search({a=>'c', b=>'d'});
my logs indicate that the query being performed is:
1 Query SELECT * FROM mytable WHERE a= c
package versions:
DBIx-Recordset-0.24.tar.gz
HTML-Embperl-1.3.3.tar.gz
redhat-7.2
perl-5.6.0-17
mysql-3.23.41-1
Thanks in advance,
Josh Spoerri
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: PrimKey -> other fields ignored
Posted by Joshua Spoerri <jo...@yucs.org>.
On Fri, Nov 09, 2001 at 11:35:32AM -0600, Ed Grimm wrote:
> On Fri, 9 Nov 2001, Gerald Richter wrote:
>
> >> Is this a known bug:
> >> querying with primary keys ignores conditions for other fields
> >> ?
> >
> > No, that's really a feature. Since a primary key has to be unique (otherwise
> > it isn't a primary key), it's the only thing you need to find a record (at
> > least if you compare with equality). So there is no need to pass all the
> > other fields to the where expression. This is an optimazation for the case
> > where you pass a hash with a bunch of fieldnames. In this way only the
> > necessary one gets to the SQL engine.
>
> Yes, but what if you want to test the values of those other fields, and
> conditionally *not* find it? This is important functionality,
> therefore, this is a mis-classified bug.
>
You can override the initial primary key by setting '$primkey' to ''.
IMHO it's just a surprising definition for the behaviour of a primary key.
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: PrimKey -> other fields ignored
Posted by Gerald Richter <ri...@ecos.de>.
>
> Yes, but what if you want to test the values of those other fields, and
> conditionally *not* find it? This is important functionality,
> therefore, this is a mis-classified bug.
>
Ok, this is really something that doesn't work right out of the box, but
there are two solutions:
1.) Unset !PrimKey
2.) use an operator of ' = ' (with space around the equal sign, than
DBIx::Recordset will not recognize it as equal and will not do any
optimazations)
e.g.
*i=DBIx::Recordset->Setup({
'!DataSource'=>'dbi:mysql:mydb',
'!Table'=>'mytable',
'!PrimKey'=>'a'
});
$i->Search({a=>'c', '*a' => ' = ', b=>'d'});
> I think a better option would be to pass a hash which only contains what
> you want to check.
>
The interface is intended to catch most of the cases. I know it will not
catch all. The idea is that you can simply pass the whole %fdat to Search,
without having to sort out all relevant fields.
Gerald
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: PrimKey -> other fields ignored
Posted by Ed Grimm <ed...@asgard.rsc.raytheon.com>.
On Fri, 9 Nov 2001, Gerald Richter wrote:
>> Is this a known bug:
>> querying with primary keys ignores conditions for other fields
>> ?
>
> No, that's really a feature. Since a primary key has to be unique (otherwise
> it isn't a primary key), it's the only thing you need to find a record (at
> least if you compare with equality). So there is no need to pass all the
> other fields to the where expression. This is an optimazation for the case
> where you pass a hash with a bunch of fieldnames. In this way only the
> necessary one gets to the SQL engine.
Yes, but what if you want to test the values of those other fields, and
conditionally *not* find it? This is important functionality,
therefore, this is a mis-classified bug.
I think a better option would be to pass a hash which only contains what
you want to check.
Ed
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: PrimKey -> other fields ignored
Posted by Gerald Richter <ri...@ecos.de>.
> Is this a known bug:
> querying with primary keys ignores conditions for other fields
> ?
No, that's really a feature. Since a primary key has to be unique (otherwise
it isn't a primary key), it's the only thing you need to find a record (at
least if you compare with equality). So there is no need to pass all the
other fields to the where expression. This is an optimazation for the case
where you pass a hash with a bunch of fieldnames. In this way only the
necessary one gets to the SQL engine.
Gerald
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org