DBI Mysql insert problem

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

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 database.

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

On Oct 12, 6:18 pm, Yuri Shtil wrote:
> The start field in an MYsql table is defined as follows:
> start TIMESTAMP
>
> I am using DBi::Mysql

There is no such module. Did you perhaps mean DBI and DBD::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);

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

> The last statement returns 1 (one row updated),

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

perldoc DBI
"execute"
$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".
[...]
"rows"
$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.


> but I cannot see it it the database.
>
> 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.

"Something fishy" is going on in your code.

> Using $dbh->do('update tests set start=NULL where db_id=1') works,

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

> Any clues ?

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

Re: DBI Mysql insert problem

On Oct 12, 6:18 pm, Yuri Shtil wrote:
> The start field in an MYsql table is defined as follows:
> start TIMESTAMP
>
> I am using DBi::Mysql

There is no such module. Did you perhaps mean DBI and DBD::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);

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

> The last statement returns 1 (one row updated),

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

perldoc DBI
"execute"
$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".
[...]
"rows"
$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.


> but I cannot see it it the database.
>
> 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.

"Something fishy" is going on in your code.

> Using $dbh->do('update tests set start=NULL where db_id=1') works,

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

> Any clues ?

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

Re: DBI Mysql insert problem

On 10/12/2007 05:18 PM, Yuri Shtil wrote:
> The start field in an MYsql table is defined as follows:
> start TIMESTAMP
>
> 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 database.
> [...]

(Comp.lang.perl.modules was cut from the newsgroups: header.)

A similar problem was discussed last month, but before we proceed we
need a *minimal but complete* script from you that demonstrates the problem.

Hint: Make sure you explicitly disconnect the DBI connection.