simple perl help needed

digitalninja

I've been staring at this for ages no, I had it working yesterday but then I deleted the code because I am a moron, I'm trying to populate a mysql db from a parsed input file using split, it's saying incorrect syntax, can someone help?
I think the problem lies on line 11, I know it's something simple it's been a long day.

#!/usr/bin/perl -w
use strict;
use DBI;
use warnings;
my $inputfile;
my @arr;
$inputfile = shift;
open FH,"$inputfile" or die "unable to open the file";
my $dbh = DBI->connect('dbi:mysql:maillogs','root','yeahright;') || die "Database connection not made: $DBI::errstr";
my $insert_handle =
$dbh->prepare_cached('INSERT INTO data_sample (connid0,connid1,profileid,mailingid,toid,mail,from,toaddress,blank,rcpt,to,recipientid,blank0,data,blank1,messagesize,blank2,status,time,deliverytime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
while(<FH>){
@arr = split /\s|\t/;
$insert_handle->execute($arr[0],$arr[1],$arr[2],$arr[3],$arr[4],$arr[5],$arr[6],$arr[7],$arr[8],$arr[9],$arr[10],$arr[11],$arr[12],$arr[13],$arr[14],$arr[15],$arr[16],$arr[17],$arr[18],$arr[19]);
}

Solved TO and FROM are reserved names in SQL, once i changed them it populated no problem

castie

@personal = split(/:/, $info);

would be how to use the split function to split by :

You seem to be missing brackets

daymobrew

castie wrote: »

You seem to be missing brackets

Perl is quite lack wrt brackets - the split docs don't show brackets (see header part and last example).

The script compiled okay for me.

Can you paste the error message. Is it from DBI or from perl?

digitalninja

Here is the 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 'from,toaddress,blank,rcpt,to,recipientid,blank0,data,blank1,messagesize,blank2,s' at line 1 at ./textinput.pl line 14, <FH> line 1.

brianmc

digitalninja wrote: »

Here is the 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 'from,toaddress,blank,rcpt,to,recipientid,blank0,data,blank1,messagesize,blank2,s' at line 1 at ./textinput.pl line 14, <FH> line 1.

Perhaps a single quote (') in your data?

digitalninja

nah i checked the data, it's just various numbers and email addresses:/

brianmc

digitalninja wrote: »

nah i checked the data, it's just various numbers and email addresses:/

Well, the Perl seems ok but I've never used DBI. The error says it's an SQL problem and the SQL error (assuming it's exactly copied and pasted) seems to list 12 comma separated values in the quotes. Your SQL insert statement seems to require more (20?).

Is there something unusual about maybe just the first line of data in the file? Is it a header line with a slightly different format? Fewer fields maybe?

digitalninja

no, i actually had it working with the data in the inputfile already, i acidentally
deleted the file and now i can't remember how i did it, frustration
central!!!

digitalninja

solved:


TO and FROM are reserved names in SQL, once i changed them it populated no problem:)