Using Bind Params in MySQL

[mike]

I am using MySQL and this was working, but all of sudden it is not. I
suspect that I have some kind of error, but my error logic must not be
working.


I am getting "Insert - Complete", but no data is getting inserted to
the table.


$sql = "insert into my_posts ( type, head, head_date, msg, trans_date,
poster ) values ( ?, ?, ?, ?, ?, ? )";


$sth = $dbh->prepare( $sql );


if ( !defined $sth )
{
print "Cannot prepare statement: $DBI::errstr\n";
}
else
{
$sth->bind_param('1', $type); $sth->bind_param('2', $head);
$sth->bind_param('3', $head_date); $sth->bind_param('4', $msg);
$sth->bind_param('5', $trans_date); $sth->bind_param('6', $poster);


eval { $sth->execute; };
if ( $@ )
{
print "Error in the database: $@";
}
else
{
# execute worked was success
print "<center><b>Insert - Complete</b></center>\n";
}
}


Any help is appreciated.


Mike

 

[J. Gleixner]

I am using MySQL and this was working, but all of sudden it is not. I
suspect that I have some kind of error, but my error logic must not be
working.


I am getting "Insert - Complete", but no data is getting inserted to
the table.

Check your logs.

You don't show your connect() call, do you need to 'AutoCommit' or to
call commit()?

$sth = $dbh->prepare( $sql );

eval { $sth->execute; };
if ( $@ )
{
print "Error in the database: $@";
}
else
{
# execute worked was success
print "<center><b>Insert - Complete</b></center>\n";
}
}

I'm not sure if die is called or $@ is set on an execute error. It's
more typical/appropriate to set the RaiseError attribute or use the
errstr method.

$sth = $dbh->prepare( $sql ) or die $dbh->errstr();
$sth->execute or die $dbh->errstr();

 

[xhoster]

$sth = $dbh->prepare( $sql );

if ( !defined $sth )

This suggests that you are not using RaiseError.

eval { $sth->execute; };
if ( $@ )

This suggests that you are using RaiseError. If in fact you are not
using RaiseError, then this won't do what you want.


Xho

 

[mike]

use DBI;

$dsn = "DBI:mysql:database=mydb;host=myhost2";
$dbh -> {RaiseError} = 1;
$dbh=DBI->connect($dsn, "id", "passwd");
if ( !defined $dbh )
{
die "Cannot connect to mySQl $DBI::errstr\n";
}
else
{

#here is where I call the insert routine
.........................

#release the statement
$sth->finish;

#disconnect the database
$dbh->disconnect;
}
}