DBI Mysql insert problem

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
The start field in an MYsql table is defined as follows:

I am using DBi::Mysql and am trying to update a column as follows:

  $sth = $dbh->prepare_cached('update tests set start=? where db_id=?');
  $sth->execute('NULL', 1);

The last statement returns 1 (one row updated), but I cannot see it it the

What I noticed also, the server connection goes away after a number of like
updates issued, however the server logs show nothing about update statements.
Something fishy is going on on the client side.

Using $dbh->do('update tests set start=NULL where db_id=1') works,
doing the same from a command line works as well.

I am using perl v5.8.8 built for i686-linux, DBI version 1.58,  DBD::mysql
version 4.005, MySQL Version: MySQL 5.0.45-community-log.

Any clues ?

Re: DBI Mysql insert problem

Quoted text here. Click to load it

There is no such module.  Did you perhaps mean DBI and DBD::mysql?

Quoted text here. Click to load it

That says to update the tests table and set start equal to the four-
character string 'NULL', not to the special database value NULL.

Quoted text here. Click to load it

No.  execute() does not return the number of rows updated.  Have you
read the documentation for the module you're using?

perldoc DBI
           $rv = $sth->execute                or die $sth->errstr;
           $rv = $sth->execute(@bind_values)  or die $sth->errstr;

         Perform whatever processing is necessary to execute the
         prepared statement.  An "undef" is returned if an error
         occurs.  A successful "execute" always returns true
         regardless of the number of rows affected, even if it's
         zero (see below). It is always important to check the
         return status of "execute" (and most other DBI methods)
         for errors if you're not using "RaiseError".
           $rv = $sth->rows;

         Returns the number of rows affected by the last row
         affecting command, or -1 if the number of rows is not
         known or not available.

Quoted text here. Click to load it

"Something fishy" is going on in your code.

Quoted text here. Click to load it

Of course it does.  But would you expect
UPDATE tests SET start='NULL' WHERE db_id = 1;
to work as well?

Quoted text here. Click to load it

Read the documentation for the modules you're using.  Again from
perldoc DBI:
     NULL Values

     Undefined values, or "undef", are used to indicate NULL
     values.  You can insert and update columns with a NULL value
     as you would a non-NULL value.  These examples insert and
     update the column "age" with a NULL value:

       $sth = $dbh->prepare(qq{
         INSERT INTO people (fullname, age) VALUES (?, ?)
       $sth->execute("Joe Bloggs", undef);

       $sth = $dbh->prepare(qq{
         UPDATE people SET age = ? WHERE fullname = ?
       $sth->execute(undef, "Joe Bloggs");

Paul Lalli

Site Timeline