load data to MySQL database using Perl

[xieyulong]

Dear Perl Monks:

I have a problem in loading a data file to a MySQL database using Perl. I created a MySQL database MySQL_test and then created an empty table test_pet. I have data to be loaded in a file called pet.txt.

The following Perl code is supposed to first load the data records in pet.txt into table test_pet of database MySQL_test, then insert one additional record into the table of test_pet, then do a query to select all the records and output them to a csv file called test.csv.

The Perl code run without problems and the output file test.csv contains the data records in the data file pet.txt plus the additional one inserted. However when I checked the table test_pet in MySQL database MySQL_test, it is still empty.

What did I do wrong? Your help will be highly appreciated.

Thanks

The following is the Perl code

use DBI;
use strict;

my $dsn       = "dbi:mysql:mysql_test";
my $user      = "root";
my $password  = "password";
my $dbh = DBI -> connect($dsn,$user,$password,{RaiseError=>1,AutoCommi
+t => 0});

my ($i,@row,$sth);
my $out_file = "test.csv";
open(Handle_OUT,">$out_file") || die "Could not open $out_file $!\n";

#  load data [pet.txt] into TABLE [test_pet] of DATABASE [MySQL_test]
$sth = $dbh -> prepare("LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE te
+st_pet LINES TERMINATED BY '\r\n';");
$sth -> execute;
  
# insert one more record into TABLE [test_pet]
$sth = $dbh -> prepare("INSERT INTO test_pet  VALUES ('Puffball','Dian
+e','hamster','f','1999-03-30',NULL);");
$sth -> execute;

# select all records from the [test_pet] TABLE of DATABASE [MySQL_test
+] and output to [test.csv]
$sth = $dbh -> prepare("SELECT * FROM test_pet");
$sth -> execute;
while (@row = $sth->fetchrow_array())
{
    for ($i=0;$i<@row;$i++)
    {
        print Handle_OUT "$row[$i]\t";
    }
    print Handle_OUT "\n";
 }

# disconnet  
$dbh->disconnect;

# close the output file  
close(Handle_OUT);
 
### -----------------------------------------------------------------
### THE FOLLOWING ARE COMMAND USED TO CREATE THE TEST DATABASE AND THE
+ TABLE IN THE DATABASE 
## -----------------------------------------------------------------
### create database MySQL_test;        # Create a database called [MyS
+QL_test]
### use MySQL_test;            # Select the created database [MySQL_te
+st]
###                                     # Create an empty TABLE called
+ [test_pet]
### CREATE TABLE test_pet (name VARCHAR(20),owner VARCHAR(20),species 
+VARCHAR(20),sex CHAR(1), birth DATE, death DATE);
### 
###
### -----------------------------------------------------------------
### Below is the data file [pet.txt] to be loaded into TABLE [test_pet
+] of database [MySQL_test] 
### -----------------------------------------------------------------
### Fluffy    Harold    cat    f    1993-02-04        
### Claws    Gwen    cat    m    1994-03-17        
### Buffy    Harold    dog    f    1989-05-13        
### Fang    Benny    dog    m    1990-08-27        
### Bowser    Diane    dog    m    1979-08-31    1995-07-29    
### Chirpy    Gwen    bird    f    1998-09-11        
### Whistler    Gwen    bird        1997-12-09        
### Slim    Benny    snake    m    1996-04-29
## -----------------------------------------------------------------

[ccn]

Set AutoCommit => 1 or call $dbh->commit; before disconnect.

See perldoc DBI about commiting

[xieyulong]

Yes. Thanks a lot. When I set autocommit from "off" to "on", it worked as expected.

I checked perldoc DBI about the setting of autocommit when establishing a connection, but did not understand well. In practice in which situation the autocommit should be set to "off" and then call commit to make the transaction take effect.

Thanks again.

[Bloodnok]

I suspected I recognised the problem long before reading the code, so by way of explanation of ccns most apposite reply ...

All database updates/changes are transient i.e. they will only last for the duration of the session unless any changes are auto-committed on, or the changes are manually committed before, session closure.

[gnuchu]

You could always change your connect string to:
my $dbh = DBI ->connect($dsn,$user,$password,{RaiseError=>1,AutoCommit => 1});

Notice the Autocommit value...

[Bloodnok]

Hmmm, I take it that you didn't bother to read ccns reply before posting ??