Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Text::CSV and Mysql

Reply
Thread Tools

Text::CSV and Mysql

 
 
azzi.george@gmail.com
Guest
Posts: n/a
 
      02-23-2007
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:umper;
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,fieldb yitself,",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 >

 
Reply With Quote
 
 
 
 
usenet@DavidFilmer.com
Guest
Posts: n/a
 
      02-23-2007
On Feb 23, 10:14 am, (E-Mail Removed) 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)

 
Reply With Quote
 
 
 
 
azzi.george@gmail.com
Guest
Posts: n/a
 
      02-23-2007
On Feb 23, 1:58 pm, (E-Mail Removed) wrote:
> On Feb 23, 10:14 am, (E-Mail Removed) 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)


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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Python and Connecting to MySQL remotely WITHOUT MySQL installedon local computer dave Python 4 11-18-2010 04:19 AM
Re: How to insert PDF file in to MySql and read it from MySql toJAVA App Jeffrey H. Coffield Java 1 07-24-2009 12:29 AM
Re: How to insert PDF file in to MySql and read it from MySql to JAVA App Roedy Green Java 0 07-23-2009 05:30 PM
mySQL Ruby Gem and MAMP mySQL Mark Meijer Ruby 3 02-03-2008 04:19 AM
Has Anyone build A Mysql Module for ruby1.8.2 and Mysql 4.1.8 bin liu Ruby 1 12-22-2004 03:39 AM



Advertisments