Win2K DBI SQL example sought

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Win2K DBI SQL example sought by LanceDeeply (Chaplain) on Jun 24, 2003 at 14:52 UTC
the actual execute statment will depend on what you pass into your handle's execute call...: `my $oldStrID = "foo"; my $newStrID = "bar"; $dbh->execute($newStrID,$oldStrID)` [download] will generate sql that looks something like: `UPDATE Images SET strID='bar' WHERE strID='foo'` [download]	[reply] [d/l] [select]
Re: Win2K DBI SQL example sought by barrd (Canon) on Jun 24, 2003 at 14:56 UTC
The following is what it would look like, but doesn't make much sense. `use strict; # <- NOTE! my $dbh = DBI->connect(qw(DBI:vendor:database:host user pass), {RaiseError => 1}, ); my $strID = "foo"; my $sth = $dbh->prepare("UPDATE Images SET strID=? WHERE strID=?"); $sth->execute($strID, $strID); # ...` [download] The row exists in this instance as you are attempting an `update` rather than an `insert` to the field `strID`, but you would be only overwriting the existing field value with the same data (in the example above "foo"). i.e. `UPDATE Images SET strID=foo WHERE strID=foo` Thats what doesn't make sense to me, understand where I'm getting at? So... I think it would be more like: `my $NewStrID = "foo"; my $OldStrID = "bar"; my $sth = $dbh->prepare("UPDATE Images SET strID=? WHERE strID=?"); $sth->execute($NewStrID, $OldStrID); # ...` [download] i.e. `UPDATE Images SET strID=foo WHERE strID=bar` Hope some of that made sense. Update: {sigh}, LanceDeeply beat me to it..., must... type... faster... ;)	[reply] [d/l] [select]
Re: Win2K DBI SQL example sought by Itatsumaki (Friar) on Jun 24, 2003 at 15:30 UTC
Definitely can do. You can also look into binding of parameters, which might be a bit faster and clearer in some circumstances: `$sth = $dbh->prepare('UPDATE Images SET strID=? WHERE strID=?'); $sth->bind_param(1, $strID); $sth->bind_param(2, $strID); $sth->execute()` [download] Alternately you can follow the suggestion above another just pass in the same variable to execute twice. -Tats	[reply] [d/l]
Re: Win2K DBI SQL example sought by The Mad Hatter (Priest) on Jun 24, 2003 at 14:26 UTC
Update Oh, I see your other question now. ; ) Well, I see no reason off why you wouldn't be able to have a statement like that. `$dbstmt->execute($newstrID, $oldstrID);` [download] For each `?`, just pass `execute` an argument. Learn more by reading the docs. Update2 Urp. Thanks to Jenda for pointing out I had the two IDs reversed... ; )	[reply] [d/l] [select]
Re: Re: Win2K DBI SQL example sought by Jenda (Abbot) on Jun 24, 2003 at 23:08 UTC
You have it reversed :-) It's `$dbstmt->execute($newstrID, $oldstrID);` Jenda Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Rick Osborne Edit by castaway: Closed small tag in signature	[reply] [d/l]