On 2/16/07, Jim Crafton <[EMAIL PROTECTED]> wrote:
> but it fails because the embedded WHERE clause is no longer catching
> the row. It is still looking for
>
> WHERE LastName='Doe' AND
> FirstName='John' AND
> Address='100 Nowhere Ave.' AND
> Age=45;
>
> instead of
>
> WHERE LastName='Doe' AND
> FirstName='Jane' AND
> Address='100 Nowhere Ave.' AND
> Age=45;
>
Yeah I think you're right. I changed the code to *not* use the bind
functions, and just dump the values directly into the SQL statement (
I think this was a case of me trying to be too clever), and that works
like a charm now.
nothing wrong with being clever, but not using bind is not good.
Listen to what drh and other are saying. Using bind wasn't your
problem. Your problem was having a fixed WHERE clause but changing the
very values that were hardcoded in the WHERE clause. Its like, you are
looking for a green car, then painting it red, but then next time
looking for a green car again. Of course you won't find it. You just
painted it red.
Generally, bind is used for changing VALUES that you might want to
feed via an array, but prepare the statement only once. Having primary
keys is always advisable, and that allows you to not having to much
with the WHERE clause. Imagine if your table were
id INTEGER PRIMARY KEY,
lastname TEXT,
firstname TEXT,
and so on...
you could easily do something like
UPDATE table
SET lastname = ?, firstname = ?
WHERE id = ?
and then (all perl-ish pseudocode ahead)
array = (
[1, 'Doe', 'John'],
[2, 'Doe', 'Jane'],
[3, 'Doe', 'Jack'],
)
for row (array) {
execute(row->[1], row->[2], row->[0])
)
it would all work. Of course, you would feed the values in the same
order as the bind vars.
So I guess the moral of this is to use bind cautiously :)
not any more than doing anything cautiously. Bind vars definitely make
things a lot easier. Dunno about other cases, but in Perl DBI, bind is
even more helpful because DBI figures out how to correctly quote the
values, especially tricky when you have embedded single quotes.
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
