Re: Fw: Weird case : Mysql can not accept insert data from perl!! PLEASE - HELP!!
am 21.02.2004 07:50:37 von Ulrich Borchers
--Alt-Boundary-3354.1914693
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
you don't need a script for this.
use
load data local infile into table
see
http://www.mysql.com/documentation/mysql/bychapter/manual_Tu torial.html#Loadi
ng_tables
if you still want to use your script, then maybe
while ($line = )
is better than
foreach $line ()
Uli
On 21 Feb 2004 at 12:32, Victor Alamo wrote:
>
>
> Hi,
>
> I am dropping this email because I have already tried all possible ways.
>
> Goal: Migrate Excel to Mysql using perl .
>
> Howto: I have save the xls file as CVS ( comma - separate ) format like:
>
> i.e:
> ELTID,Telecom ID,ELTID8,ELTID Greek ATX (Cook),682-60127,Greece-->Cook Island,GR,27-Apr-98
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +
> perl program:
>
> #Initialization of Libraries needed it.
> use DBI;
> use DBD::mysql;
>
> #Initialization of database
>
> my $table="dnis";
> my $db="stats";
> my $user="user";
> my $pass=" password";
> my $sth2;
> my $i=0;
> my $sqlrecords;
> my $export_sql;
> my $source="/home/canario/eltid.txt";
>
>
>
> #$LOGFILE="ELTID.csv ";
>
>
> # Tell the script that we will use Mysql database
>
> my $drh=DBI->install_driver('mysql');
>
> # Establish connectin with the database
>
> my $dbh=$drh->connect($db,$user,$pass);
>
> # A simple check to see if we connect6
>
> if (!$dbh) {
>
> print "cannot connect: $DBI::errstr
";
> die;
> }
>
> open(LOG,$source) or die("Could not open log file.");
>
> foreach $line (){
> #while () {
> my ($ipcode, $ip_name, $prod_id, $prod_name, $actual_num, $rate_desc, $country, $updated)=split(/,/,$line);
>
> print "column: $i\n";
> print "ipcode:$ipcode\n";
> print "ip_name:$ip_name\n";
> print "prod_id:$prod_id\n";
> print "prod_name:$prod_name\n";
> print "actual_num:$actual_num\n";
> print "rate_desc:$rate_desc\n";
> print "country:$country\n";
> print "updated:$updated\n";
>
> $i++;
>
> #if ipcode is not null then -> Export the data to the Mysql database
>
> #if ($ipcode !="") {
> $sqlrecords = "insert into $table(ipcode,ip_name,prod_id,prod_name,actual_num,rate_desc ,country,updated) values ('$ipcode','$ip_name','$prod_id','$prod_name','$actual_num', '$rate_desc','$country','$updated')";
>
> print "$sqlrecords\n";
> # }
>
> #$sth2=$dbh->prepare($sqlrecords);
> $sth2=$dbh->do($sqlrecords);
> #$export_sql=$sth2->execute();
>
>
> }
>
> close(LOG);
>
> $sth2->finish;
> $dbh->disconnect;
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++
>
> Description of program:
> It read from cvs file row by row and it goes insert the data read from file into mysql table. Just a simple loop with single query.
>
> I have checked the log file from Mysql and I can see 700 insert queries , but , when I check the table there is ONLY A SINGLE ROW ENTRY - REST OF 699 ROW OF DATA HAS NOT BEEN INSERTED ....WHY???
>
>
>
> Please HELP!!
>
> Thanks in advance.
>
> Cheers,
>
> Vic
--Alt-Boundary-3354.1914693--
Re: Fw: Weird case : Mysql can not accept insert data from perl!! PLEASE - HELP!!
am 21.02.2004 07:50:37 von Ulrich Borchers
--Alt-Boundary-3354.1914693
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
you don't need a script for this.
use
load data local infile into table
see
http://www.mysql.com/documentation/mysql/bychapter/manual_Tu torial.html#Loadi
ng_tables
if you still want to use your script, then maybe
while ($line = )
is better than
foreach $line ()
Uli
On 21 Feb 2004 at 12:32, Victor Alamo wrote:
>
>
> Hi,
>
> I am dropping this email because I have already tried all possible ways.
>
> Goal: Migrate Excel to Mysql using perl .
>
> Howto: I have save the xls file as CVS ( comma - separate ) format like:
>
> i.e:
> ELTID,Telecom ID,ELTID8,ELTID Greek ATX (Cook),682-60127,Greece-->Cook Island,GR,27-Apr-98
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +
> perl program:
>
> #Initialization of Libraries needed it.
> use DBI;
> use DBD::mysql;
>
> #Initialization of database
>
> my $table="dnis";
> my $db="stats";
> my $user="user";
> my $pass=" password";
> my $sth2;
> my $i=0;
> my $sqlrecords;
> my $export_sql;
> my $source="/home/canario/eltid.txt";
>
>
>
> #$LOGFILE="ELTID.csv ";
>
>
> # Tell the script that we will use Mysql database
>
> my $drh=DBI->install_driver('mysql');
>
> # Establish connectin with the database
>
> my $dbh=$drh->connect($db,$user,$pass);
>
> # A simple check to see if we connect6
>
> if (!$dbh) {
>
> print "cannot connect: $DBI::errstr
";
> die;
> }
>
> open(LOG,$source) or die("Could not open log file.");
>
> foreach $line (){
> #while () {
> my ($ipcode, $ip_name, $prod_id, $prod_name, $actual_num, $rate_desc, $country, $updated)=split(/,/,$line);
>
> print "column: $i\n";
> print "ipcode:$ipcode\n";
> print "ip_name:$ip_name\n";
> print "prod_id:$prod_id\n";
> print "prod_name:$prod_name\n";
> print "actual_num:$actual_num\n";
> print "rate_desc:$rate_desc\n";
> print "country:$country\n";
> print "updated:$updated\n";
>
> $i++;
>
> #if ipcode is not null then -> Export the data to the Mysql database
>
> #if ($ipcode !="") {
> $sqlrecords = "insert into $table(ipcode,ip_name,prod_id,prod_name,actual_num,rate_desc ,country,updated) values ('$ipcode','$ip_name','$prod_id','$prod_name','$actual_num', '$rate_desc','$country','$updated')";
>
> print "$sqlrecords\n";
> # }
>
> #$sth2=$dbh->prepare($sqlrecords);
> $sth2=$dbh->do($sqlrecords);
> #$export_sql=$sth2->execute();
>
>
> }
>
> close(LOG);
>
> $sth2->finish;
> $dbh->disconnect;
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++
>
> Description of program:
> It read from cvs file row by row and it goes insert the data read from file into mysql table. Just a simple loop with single query.
>
> I have checked the log file from Mysql and I can see 700 insert queries , but , when I check the table there is ONLY A SINGLE ROW ENTRY - REST OF 699 ROW OF DATA HAS NOT BEEN INSERTED ....WHY???
>
>
>
> Please HELP!!
>
> Thanks in advance.
>
> Cheers,
>
> Vic
--Alt-Boundary-3354.1914693--
Re: Fw: Weird case : Mysql can not accept insert data from perl!! PLEASE - HELP!!
am 21.02.2004 08:56:54 von Patrick Galbraith
Victor,
Couple tips:
One thing I can say, is the while() was fine. Secondly, in that
loop, you should probably do a 'chomp', which cuts of any trailing newlines.
while() {
chomp;
my ($ipcode, $ip_name, $prod_id, $prod_name, $actual_num,
$rate_desc, $country, $updated)=split(/,/,$_);
# you can print your stuff here...do database inserts... etc. $_ is
the current line of the file being processed.
...
}
Also, you shouldn't have to 'use DBD::mysql' or call 'install_driver'. A
simple DBI->connect() with the proper $dsn value such as:
$dsn = "DBI:mysql:database=$db;host=$yourhost";
my $dbh = DBI->connect($dsn, $user, $pass)
Now, the most important thing, and why your values aren't being saved,
but while you are seeing them in the log:
my $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 1});
Hope this helps!
regards,
Patrick
Victor Alamo wrote:
>Hi,
>
>I am dropping this email because I have already tried all possible ways.
>
>Goal: Migrate Excel to Mysql using perl .
>
>Howto: I have save the xls file as CVS ( comma - separate ) format like:
>
>i.e:
>ELTID,Telecom ID,ELTID8,ELTID Greek ATX (Cook),682-60127,Greece-->Cook Island,GR,27-Apr-98
>
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++
>perl program:
>
>#Initialization of Libraries needed it.
>use DBI;
>use DBD::mysql;
>
>#Initialization of database
>
>my $table="dnis";
>my $db="stats";
>my $user="user";
>my $pass=" password";
>my $sth2;
>my $i=0;
>my $sqlrecords;
>my $export_sql;
>my $source="/home/canario/eltid.txt";
>
>
>
>#$LOGFILE="ELTID.csv ";
>
>
># Tell the script that we will use Mysql database
>
>my $drh=DBI->install_driver('mysql');
>
># Establish connectin with the database
>
>my $dbh=$drh->connect($db,$user,$pass);
>
># A simple check to see if we connect6
>
>if (!$dbh) {
>
>print "cannot connect: $DBI::errstr
";
>die;
>}
>
>open(LOG,$source) or die("Could not open log file.");
>
>foreach $line (){
>#while () {
>my ($ipcode, $ip_name, $prod_id, $prod_name, $actual_num, $rate_desc, $country, $updated)=split(/,/,$line);
>
>print "column: $i\n";
>print "ipcode:$ipcode\n";
>print "ip_name:$ip_name\n";
>print "prod_id:$prod_id\n";
>print "prod_name:$prod_name\n";
>print "actual_num:$actual_num\n";
>print "rate_desc:$rate_desc\n";
>print "country:$country\n";
>print "updated:$updated\n";
>
>$i++;
>
>#if ipcode is not null then -> Export the data to the Mysql database
>
>#if ($ipcode !="") {
>$sqlrecords = "insert into $table(ipcode,ip_name,prod_id,prod_name,actual_num,rate_desc ,country,updated) values ('$ipcode','$ip_name','$prod_id','$prod_name','$actual_num', '$rate_desc','$country','$updated')";
>
>print "$sqlrecords\n";
># }
>
>#$sth2=$dbh->prepare($sqlrecords);
>$sth2=$dbh->do($sqlrecords);
>#$export_sql=$sth2->execute();
>
>
>}
>
>close(LOG);
>
>$sth2->finish;
>$dbh->disconnect;
>
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++
>
>Description of program:
>It read from cvs file row by row and it goes insert the data read from file into mysql table. Just a simple loop with single query.
>
>I have checked the log file from Mysql and I can see 700 insert queries , but , when I check the table there is ONLY A SINGLE ROW ENTRY - REST OF 699 ROW OF DATA HAS NOT BEEN INSERTED ....WHY???
>
>
>
>Please HELP!!
>
>Thanks in advance.
>
>Cheers,
>
>Vic
>
>
--
Patrick Galbraith, Senior Systems Engineer
MySQL AB, www.mysql.com
Office: +1 206 719 2461
Are you MySQL certified? www.mysql.com/certification
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Fw: Weird case : Mysql can not accept insert data from perl!! PLEASE - HELP!!
am 21.02.2004 08:56:54 von Patrick Galbraith
Victor,
Couple tips:
One thing I can say, is the while() was fine. Secondly, in that
loop, you should probably do a 'chomp', which cuts of any trailing newlines.
while() {
chomp;
my ($ipcode, $ip_name, $prod_id, $prod_name, $actual_num,
$rate_desc, $country, $updated)=split(/,/,$_);
# you can print your stuff here...do database inserts... etc. $_ is
the current line of the file being processed.
...
}
Also, you shouldn't have to 'use DBD::mysql' or call 'install_driver'. A
simple DBI->connect() with the proper $dsn value such as:
$dsn = "DBI:mysql:database=$db;host=$yourhost";
my $dbh = DBI->connect($dsn, $user, $pass)
Now, the most important thing, and why your values aren't being saved,
but while you are seeing them in the log:
my $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 1});
Hope this helps!
regards,
Patrick
Victor Alamo wrote:
>Hi,
>
>I am dropping this email because I have already tried all possible ways.
>
>Goal: Migrate Excel to Mysql using perl .
>
>Howto: I have save the xls file as CVS ( comma - separate ) format like:
>
>i.e:
>ELTID,Telecom ID,ELTID8,ELTID Greek ATX (Cook),682-60127,Greece-->Cook Island,GR,27-Apr-98
>
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++
>perl program:
>
>#Initialization of Libraries needed it.
>use DBI;
>use DBD::mysql;
>
>#Initialization of database
>
>my $table="dnis";
>my $db="stats";
>my $user="user";
>my $pass=" password";
>my $sth2;
>my $i=0;
>my $sqlrecords;
>my $export_sql;
>my $source="/home/canario/eltid.txt";
>
>
>
>#$LOGFILE="ELTID.csv ";
>
>
># Tell the script that we will use Mysql database
>
>my $drh=DBI->install_driver('mysql');
>
># Establish connectin with the database
>
>my $dbh=$drh->connect($db,$user,$pass);
>
># A simple check to see if we connect6
>
>if (!$dbh) {
>
>print "cannot connect: $DBI::errstr
";
>die;
>}
>
>open(LOG,$source) or die("Could not open log file.");
>
>foreach $line (){
>#while () {
>my ($ipcode, $ip_name, $prod_id, $prod_name, $actual_num, $rate_desc, $country, $updated)=split(/,/,$line);
>
>print "column: $i\n";
>print "ipcode:$ipcode\n";
>print "ip_name:$ip_name\n";
>print "prod_id:$prod_id\n";
>print "prod_name:$prod_name\n";
>print "actual_num:$actual_num\n";
>print "rate_desc:$rate_desc\n";
>print "country:$country\n";
>print "updated:$updated\n";
>
>$i++;
>
>#if ipcode is not null then -> Export the data to the Mysql database
>
>#if ($ipcode !="") {
>$sqlrecords = "insert into $table(ipcode,ip_name,prod_id,prod_name,actual_num,rate_desc ,country,updated) values ('$ipcode','$ip_name','$prod_id','$prod_name','$actual_num', '$rate_desc','$country','$updated')";
>
>print "$sqlrecords\n";
># }
>
>#$sth2=$dbh->prepare($sqlrecords);
>$sth2=$dbh->do($sqlrecords);
>#$export_sql=$sth2->execute();
>
>
>}
>
>close(LOG);
>
>$sth2->finish;
>$dbh->disconnect;
>
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++
>
>Description of program:
>It read from cvs file row by row and it goes insert the data read from file into mysql table. Just a simple loop with single query.
>
>I have checked the log file from Mysql and I can see 700 insert queries , but , when I check the table there is ONLY A SINGLE ROW ENTRY - REST OF 699 ROW OF DATA HAS NOT BEEN INSERTED ....WHY???
>
>
>
>Please HELP!!
>
>Thanks in advance.
>
>Cheers,
>
>Vic
>
>
--
Patrick Galbraith, Senior Systems Engineer
MySQL AB, www.mysql.com
Office: +1 206 719 2461
Are you MySQL certified? www.mysql.com/certification
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Fw: Weird case : Mysql can not accept insert data from perl!! PLEASE - HELP!!
am 21.02.2004 14:49:05 von Rudy Lippan
On Fri, 20 Feb 2004, Patrick Galbraith wrote:
> Now, the most important thing, and why your values aren't being saved,
> but while you are seeing them in the log:
>
> my $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 1});
>
DBI defaults to {AutoCommit => 1} when not specified, so I do not think
this is the problem. My guess would be
> >$sqlrecords = "insert into
> >$table(ipcode,ip_name,prod_id,prod_name,actual_num,rate_des c,country,updated)
> >values
> >('$ipcode','$ip_name','$prod_id','$prod_name','$actual_num' ,'$rate_desc','$country','$updated')";
> >
that there are no place holders here and that $prod_name or $rate_desc or
something has quotes in it that are causing a syntax error on $dbh->do()
> >$sth2=$dbh->do($sqlrecords);
which would not be caught because errors are ignored.
> >
> >$sth2->finish;
You only need finish() when selecing from the databse and you do not fetch
to the end of the record set.
So Victor,
Try this:
my $dbh = $dbi->connect($dsn, $user, $pass, {RaiseError =>1});
......
$dbh->do(q{
INSERT INTO $table (ip_code, ip_name, prod_id)
VALUES (?, ?, ?)
}, {}, $ip_code, $ip_name, $prod_id);
$dbh->disconnect;
One other thing you might want to look at is CSV_XS for handling the CSV
file, a simple split /,/ will not always work because fields might have
embeded ','s or fields might be quoted and those quotes would need to be
stripped out.
Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Fw: Weird case : Mysql can not accept insert data from perl!! PLEASE - HELP!!
am 21.02.2004 14:49:05 von Rudy Lippan
On Fri, 20 Feb 2004, Patrick Galbraith wrote:
> Now, the most important thing, and why your values aren't being saved,
> but while you are seeing them in the log:
>
> my $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 1});
>
DBI defaults to {AutoCommit => 1} when not specified, so I do not think
this is the problem. My guess would be
> >$sqlrecords = "insert into
> >$table(ipcode,ip_name,prod_id,prod_name,actual_num,rate_des c,country,updated)
> >values
> >('$ipcode','$ip_name','$prod_id','$prod_name','$actual_num' ,'$rate_desc','$country','$updated')";
> >
that there are no place holders here and that $prod_name or $rate_desc or
something has quotes in it that are causing a syntax error on $dbh->do()
> >$sth2=$dbh->do($sqlrecords);
which would not be caught because errors are ignored.
> >
> >$sth2->finish;
You only need finish() when selecing from the databse and you do not fetch
to the end of the record set.
So Victor,
Try this:
my $dbh = $dbi->connect($dsn, $user, $pass, {RaiseError =>1});
......
$dbh->do(q{
INSERT INTO $table (ip_code, ip_name, prod_id)
VALUES (?, ?, ?)
}, {}, $ip_code, $ip_name, $prod_id);
$dbh->disconnect;
One other thing you might want to look at is CSV_XS for handling the CSV
file, a simple split /,/ will not always work because fields might have
embeded ','s or fields might be quoted and those quotes would need to be
stripped out.
Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org