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
## -----------------------------------------------------------------
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.