Re^4: turning off Autocommit (MySql)

Here is a test prog I wrote:

#! /usr/bin/perl

use DBI ;

$root = "root" ;
$dbc = "dbi:mysql::localhost" ;
$dbh = DBI->connect($dbc, $root, $passwd, {AutoCommit => 0, RaiseError
+ => 1}) ;
$dbh->{'AutoCommit'} = 0 ;


$dbh->do("CREATE DATABASE xxx") ;
$dbh->do("USE xxx") ;
$dbh->do("CREATE TABLE test (str VARCHAR(50))") ;

$str = "INSERT INTO test SET str='hello'" ;
$sth = $dbh->prepare($str) ;
$r   = $sth->execute() ;

if (! $r) {
    print "could not insert\n" ;
}
else {
    print "ok\n" ;
    $dbh->rollback() ;
}
$dbh->disconnect() ;
[download]

But somehow it didn't work, the row is inserted anyway
Any suggestions ?

Luca

Comment on Re^4: turning off Autocommit (MySql) Download Code

Replies are listed 'Best First'.
Re^5: turning off Autocommit (MySql) by tirwhan (Abbot) on Nov 11, 2005 at 15:38 UTC
I currently don't have a MySQL database handy to test this with, but if I try this code out with Postgres it warns me that "Create database" cannot run within a transaction block. Try checking/setting Autocommit again after you're created and "used" the database. You could also try calling begin_work explicitly before you want your transaction to start. Also, you may want to check that xxx gets created as table type InnoDB (with "SHOW TABLE STATUS"), that depends on your MySQL configuration. Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan	[reply]
Re^6: turning off Autocommit (MySql) by jeanluca (Deacon) on Nov 11, 2005 at 15:49 UTC
Its a MyISAM table. I've never dealt with MyISAM or InnoDB, so is this wrong ? If yes, how do I create InnoDB tables ? Luca	[reply]
Re^7: turning off Autocommit (MySql) by tirwhan (Abbot) on Nov 11, 2005 at 16:01 UTC
Try `"CREATE TABLE test (str VARCHAR(50)) ENGINE=InnoDB"`. If that doesn't work it's likely you need to change your mysql configuration and/or initialise the tablespace, check the MySQL docs here for that, it's somewhat out of scope for this forum. Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan	[reply] [d/l]
Re^7: turning off Autocommit (MySql) by jeanluca (Deacon) on Nov 11, 2005 at 16:00 UTC
guess this is the solution:CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; Yep rollback works!!!!! Thanks a lot! Luca	[reply]