Date sent: Mon, 14 May 2001 11:42:49 +0300 (EEST)
From: Nick Chirca <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: RE: basic stuff
Nick, I cannot answer the complete question (especially that <STDIN>
stuff, but
>
> When I am trying to insert some fields into a mysql table, I get this
> error:
> "DBD::mysql::db do failed: You have an error in your SQL syntax near 'll
> Know','2001-05-03','2001-05-14')' at line 4 at add_mysql line 39, <F> line
> 975."
This come because you are interpolating the values for your SQL-
statement anew for every line of your text file into a double-quoted
string. Line 975 of your input file contains the string "I'll Know"
somewhere in it, and so you have one single quote too much in your
SQL statment and you get the error above. You get around this using
placeholders (and perhaps do some error checking, BTW). My suggestion
is:
#!/usr/bin/perl
use warnings;
use strict;
use vars qw();
use DBI;
my $database_name = "nick";
my $location = "ads";
my $port_num = "3306"; # This is default for mysql
my $database = "dbi:mysql:$database_name:$location:$port_num";
my $db_user = "";
my $db_password = "";
# connect to the sql server.
my $dbh = DBI->connect($database,$db_user,$db_password) or die DBI::errstr();
my $sql = "INSERT INTO yahoo_pers1 (id,sex,race,bodytype,age,city,titlu,post,fill_date)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
my $sth = $dbh->prepare($sql) or die $dbh->errstr();
my $race = "small-dog";
my $bodytype = "slim";
my $data_azi = "2001-05-14";
my @a;
my @d;
my $new_date;
open F, "in.txt" or die "Couldn't open input file :$!";
while (<F>){
chomp;
@a = split /\t/;
#print "$id $data $age $loc\n$title";
@d = split(/\//,$a[4]);
$new_date = "20$d[2]-$d[0]-$d[1]";
$sth->execute($a[0], 'f', $race, $body_type, $a[2], $a[1], $a[3], $new_date,
$data_azi) or die $sth->errstr;
}
close F;
$rc = $dbh->disconnect;
print "$rc\n";
I could not test it, but it should work somehow. BTW, although it
will work here, @a is not a good array name, because $a is a reserved
Perl variable and there could arise some confusion with it.
HTH
Bodo
[EMAIL PROTECTED]
> I am actually reading lines from a file and I want to insert all the
> lines into the mysql table. Unfortunately, this is working ONLY if I end
> every loop (read-transformate-write_in_mysql) with a <STDIN>. If I do
> that, I don't get the error above and the information is posted correctly.
>
> If I don't end my loops with <STDIN>, I get the error above, but the
> information is actually inserted into the mysql table, despite the
> errors.
>
> Could somebody please give me a solution ?
>
> #! /usr/bin/perl
>
> $race="small-dog";
> $bodytype="slim";
> $data_azi="2001-05-14";
> open F, "in.txt";
>
>
> use DBI;
> my $database_name = "nick";
> my $location = "ads";
> my $port_num = "3306"; # This is default for mysql
>
>
> # define the location of the sql server.
> my $database = "DBI:mysql:$database_name:$location:$port_num";
> my $db_user = "";
> my $db_password = "";
>
> # connect to the sql server.
> my $dbh = DBI->connect($database,$db_user,$db_password);
>
>
> foreach $line(<F>){
> chomp $line;
> @a=split (/\t/,$line);
>
> $id=$a[0];
> $loc=$a[1];
> $age=$a[2];
> $title=$a[3];
> $data=$a[4];
>
> #print "$id $data $age $loc\n$title";
>
> @d=split(/\//,$data);
> $new_date="20$d[2]-$d[0]-$d[1]";
> #my $sql_statement =
> $dbh->do("INSERT INTO
> yahoo_pers1 (id,sex,race,bodytype,age,city,titlu,post,fill_date)
> VALUES('$id','f','$race','$bodytype',
> '$age','$loc','$title','$new_date','$data_azi');");
>
>
> #<STDIN>;
> # if I unqoute the line above, I won't get the error.
>
> }
>
>
> $rc = $dbh->disconnect;
>
>
> _________
>
>
> --
> Nick...
> ____________________________________________________________________________
> Go to my main website http://terra.vl.ro/nick/ and find out more about me.
> I am seeking a job ! See http://terra.vl.ro/nick/resume.html to read
> about my skills and experience.
> MAY ALL OUR CHOICES BE GOOD ONES !
> _____________________________________________________________________________
>
>
>
>
