Re: UPDATE statements causing me grief!

Eirik Toft <[email protected]> wrote on 10/06/2011 05:04:10 PM:

> Eirik Toft <[email protected]> 
> 10/07/2011 03:51 AM
> 
> To
> 
> <[email protected]>
> 
> cc
> 
> Subject
> 
> UPDATE statements causing me grief!
> 
> Greetings, been a DBI user for years now working with stuff from
> unixODBC, Oracle, MySQL, etc...etc...
> 
> So, the issue I'm having is with a simple update statement to a mysql
> database.
> 
> Here's a small snippet of code....
> 
> my $MAPSQL="UPDATE varmaptest SET value=? WHERE grpid=(SELECT id FROM
> machinegroups WHERE name=?) AND varid=(SELECT id FROM variables WHERE
> name=?)";
> 
> my $DBUSER="user";
> my $DBPASS="password";
> my $SQLDS="dbi:mysql:database=$CADB;host=databasehost";
> 
> my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);
> 
> my $sth = $dbh->prepare($MAPSQL);
> unless ($sth->execute("newval","group1.mygroup.mine","varname1")) {
>    warn "Database error: ".$dbh->errstr;
> }
> 
> So, for some reason, the update never happens, and I get no errors. I
> have enabled SQL tracing and from the output, I get the following
> statement:
> 
> UPDATE varmaptest SET value='newval' WHERE grpid=(SELECT id FROM
> machinegroups WHERE name='group1.mygroup.mine') AND varid=(SELECT id
> FROM variables WHERE name='varname1');
> 
> Now, if I actually run the mysql client, and paste this statement into
> it and execute it, it works perfectly.
> 
> I'm using perl 5.10.1, mysql database version 5.1.49, DBI version
> 1.612.
> 
> On a side note, because I know someone is going to ask, I use the same
> database handle ($dbh) on other SELECT statements and they all return
> data fine (so it's not a database connectivity issue).
> 
> Does anyone have any ideas, or at least can give me a good kick in the
> head as to where I should look next?
> 
> Thanks in advance;
> 
> Eirik..
> 
> 

Eirik,

        I know it sounds silly but did you forget to commit? I've been 
doing DB programming for years but every now and again I waste 10-15 
minutes on mysteriously disappearing data because I forgot to commit.


Please consider the environment before printing this email.