Re: UPDATE statements causing me grief!

On Oct 7, 1:59 am, [email protected] (Martin Hall) wrote:
> Just a thought - but how are you checking the update?  Just after the
> statement in the same Perl code or in a separate session?
>
> Cheers
>
> Martin
>
> On 06/10/2011 23:04, Eirik Toft wrote:
>
>
>
>
>
>
>
>
>
> > 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..
>
> --

Actually, I'm checking both - using the same Perl code, and also in a
separate mysql session.