PERL Miscellaneous - Text::CSV and Mysql

Author

Text::CSV and Mysql

azzi.george@gmail.com

2007-02-23, 7:07 pm

Dear Perl Programmers,

Good morning. Am having a problem loading data from a CSV file into a
test database. Below should be all of the information to duplicate my
error. Please let me know what I am doing wrong as unsure as to how to
remove the beginning [ and ] brackets. Thanks, and await for your
tested resolution.

Pierre

PERL SCRIPT
---------------------------------------------------------------------------
#!/usr/local/bin/perl

use strict;
use warnings;

use Text::CSV;
use IO::File;
use Data::Dumper;
use DBI;

my $csv = Text::CSV->new;

my $db_host = "x.x.x.x";
my $db_name = "test_db";
my $db_user = "user";
my $db_pass = "";
my $dbh = DBI->connect("dbi:mysql:$db_name:$db_host","$db_user");
; #or die "Cannot connect to the Mysql
database $db_name: $DBI:errstr\n";

my $file = "file_csv.txt";
my $fh = new IO::File "<$file" || die "Could not open $file: $! \n";

while (<$fh> ) {

my $line = $_;
my $stts = $csv->parse($_);
my @data = Dumper([$csv->fields()]);

if ($line =~ /^START/) {
my $sql_start = "INSERT INTO Start VALUES (@data);";
my $sth_start = $dbh->prepare($sql_start);
$sth_start->execute();
}
else ($line =~ /^STOP/) {
my $sql_stop = "INSERT INTO Stop VALUES (@data);";
my $sth_stop = $dbh->prepare($sql_stop);
$sth_stop->execute();
}

} #end of while loop

FILE CONTENTS BEING IMPORTED
----------------------------------------------------------
START,intent,jobs.,"testing,,can..this,be,a,fieldbyitself,",testing,end,
0,
START,tent,job,"can..this,be,a,field-by-itself,",testing,end,0,1
STOP,,tent,,,job,"can..this,be,a,field-by-itself,",,0,1

MySQL Database Structure
-------------------------------------------------------------------------------

mysql> desc Start;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | varchar(50) | YES | | NULL | |
| field2 | varchar(50) | YES | | NULL | |
| field3 | varchar(50) | YES | | NULL | |
| field4 | varchar(50) | YES | | NULL | |
| field5 | varchar(50) | YES | | NULL | |
| field6 | varchar(50) | YES | | NULL | |
| field7 | varchar(50) | YES | | NULL | |
| field8 | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

mysql> desc Stop;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| field1 | varchar(50) | YES | | NULL | |
| field2 | varchar(50) | YES | | NULL | |
| field3 | varchar(50) | YES | | NULL | |
| field4 | varchar(50) | YES | | NULL | |
| field5 | varchar(50) | YES | | NULL | |
| field6 | varchar(50) | YES | | NULL | |
| field7 | varchar(50) | YES | | NULL | |
| field8 | varchar(50) | YES | | NULL | |
| field9 | varchar(50) | YES | | NULL | |
| field10 | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

PERL ERROR
----------------------------------------------------------------------------
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '[
'START',
'intent',
'jobs.',
'testing,,' at line 1 at ./dbi_test.pl line 35, <GEN0> line
1.
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '[
'START',
'tent',
'job',
'can..this,be,' at line 1 at ./dbi_test.pl line 35, <GEN0>
line 2.
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '[
'STOP',
'',
'tent',
'',
'',
' at line 1 at ./dbi_test.pl line 40, <GEN0> line 3.
callroute:/var/home/collect/scripts >

usenet@DavidFilmer.com

2007-02-23, 7:07 pm

On Feb 23, 10:14 am, azzi.geo...@gmail.com wrote:
> Thanks, and await for your tested resolution.

Oh, not only are we supposed to fix the problems, but we're supposed
to test it as well. Would you like a mocha latte while we're working
on it?

Tell ya what - how 'bout I just point out a couple of obvious problems
and let you have another swing at it.

> my @data = Dumper([$csv->fields()]);

You want to know where those [] are coming from? Well, that's
where...

> my $sql_start = "INSERT INTO Start VALUES (@data);";

No trailing semicolon on DBI statements. This ain't a SQL shell.

> else ($line =~ /^STOP/) {

???? Did you mean elsif (...

> my $sql_stop = "INSERT INTO Stop VALUES (@data);";

Why don't you print that variable out for debugging purposes? You
will immediately see the problem caused by trying to load Dumper()
output straight into the database. This is your biggest problem.

--
The best way to get a good answer is to ask a good question.
David Filmer (http://DavidFilmer.com)

azzi.george@gmail.com

2007-02-23, 7:07 pm

On Feb 23, 1:58 pm, use...@DavidFilmer.com wrote:
> On Feb 23, 10:14 am, azzi.geo...@gmail.com wrote:
>
>
> Oh, not only are we supposed to fix the problems, but we're supposed
> to test it as well. Would you like a mocha latte while we're working
> on it?
>
> Tell ya what - how 'bout I just point out a couple of obvious problems
> and let you have another swing at it.
>
>
> You want to know where those [] are coming from? Well, that's
> where...
>
>
> No trailing semicolon on DBI statements. This ain't a SQL shell.
>
>
> ???? Did you mean elsif (...
>
>
> Why don't you print that variable out for debugging purposes? You
> will immediately see the problem caused by trying to load Dumper()
> output straight into the database. This is your biggest problem.
>
> --
> The best way to get a good answer is to ask a good question.
> David Filmer (http://DavidFilmer.com)

Wanted to give as much information as possible. And greatly appreciate
you clearing up that my issue was that the [ ] were put into the > >
my @data = Dumper([$csv->fields()]); statement! This helped a
tremendous amount as had used someone else's code for that aspect of
it.

Pierre

Sponsored Links