Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have written the below Perl script to extract data from a pipe delimited text file to an Access database.

I am currently running this script from a batch file which has a single statement: perl perlscript2.txt where perlscript2.txt is the pipe delimited text file which looks like this:

1009087|10/02/03|abbcdef|1124567|5679.34
1998023|12/06/02|rrrasdd|7468758|300.78

[download]
I keep getting an error message: 'DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement'


My code looks like this: 

# !/usr/bin/perl -w

use DBI qw(:sql_types);
use DBD::ODBC;


$dsn = "dbi:ODBC:Daily_Rec"; 
$dbh = DBI->connect($dsn,' ',' ', 
{RaiseError=>1,PrintError=>1,AutoCommit=>1} );

open(DAT, "c:/Temp/heldreceipts_TempFile.txt") || die "could 
not open Held Receipts file";

while(<DAT>)
{
    chomp;
    @line = split(/\|/, $_);
    $var1=$line[0];
    $var2=$line[1];
    $var3=$line[2];
    $var4=$line[3];
    $var5=$line[4];
    $var6=$line[5];
    $var7=$line[6];
    $var8=$line[7];
    $query=qq{insert into Held_Receipts values (?,?,?,?,?,?,?,?)};

    
$sth = $dbh->prepare($query) or die "cannot prepare query";
$sth->execute($var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8) 
or $DBI::err and die $DBI::errstr;
    
}
$sth->finish();
$dbh->disconnect();

[download]

when I print out $dbh i get HASH along with numbers in brackets (which means the $dbh part works fine right?).

Any help on resolving this issue will be greatly appreciated!!! Thanks!

update (broquaint): added <code> tags

Replies are listed 'Best First'.
Re: Extracting data from a pipe delimited text file into a table in Access
by pfaut (Priest) on May 03, 2003 at 02:01 UTC

    I don't know what Access is complaining about but here's some hints to clean up your code.

    • You don't need a 'use' statement for the DBD backend you will be using. DBI takes care of loading a driver based on the connect string you provide.
    • Prepare your statements outside the loop, execute them inside. Once prepared, a statement can be reused an unlimited number of times.
    • You can take the list returned by split and pass it directly to $sth->execute(). You don't have to use all of those nasty looking scalars (BTW, ++ for using placeholders).
    • You should add 'use strict' to the top of your script and declare your variables. This will help you catch a lot of errors at compile time. 
    # !/usr/bin/perl -w

use strict;
use DBI qw(:sql_types);

my $dsn = "dbi:ODBC:Daily_Rec";
my $dbh = DBI->connect($dsn,'','', 
            {RaiseError=>1,PrintError=>1,AutoCommit=>1} );

open(DAT, "c:/Temp/heldreceipts_TempFile.txt")
    or die "could not open Held Receipts file: $!";
my $query = qq{insert into Held_Receipts values (?,?,?,?,?,?,?,?)};
my $sth = $dbh->prepare($query)
    or die "cannot prepare query: $DBI::errstr";

while(<DAT>)
{
    chomp;
    my @line = split /\|/;
    $sth->execute(@line) 
    or die "Execute error: $DBI::errstr";
}

$sth->finish();
$dbh->disconnect();

    [download]

    Update: I think TVSET's advice is good. If you can, attempt to run the statement through some other interface and see if it complains, too.

    90% of every Perl application is already written. ⇒
    dragonchild
[reply]
[d/l]
[select]
      Thanks for your help! when i run your script i dont get any errors and the table does not get populated.
      also when i print out $query
      i get:
      insert into Held_Receipts values (?,?,?,?,?,?,?,?)
      any help would be greatly appreciated!!
[reply]
Re: Extracting data from a pipe delimited text file into a table in Access
by TVSET (Chaplain) on May 03, 2003 at 01:48 UTC
[reply]
      when i print out $query i get
      insert into Held_Receipts values (?,?,?,?,?,?,?,?)
      my code is as follows:
      # !/usr/bin/perl -w
      use strict;
      use DBI qw(:sql_types);
      my $dsn = "dbi:ODBC:Daily_Rec";
      my $dbh = DBI->connect($dsn,'','', {RaiseError=>1,PrintError=>1,AutoCommit=>1} );
      open(DAT, "c:/Temp/heldreceipts_TempFile.txt") or die "could not open Held Receipts file: $!";
      my $query = qq{insert into Held_Receipts values (?,?,?,?,?,?,?,?)};
      print $query;
      my $sth = $dbh->prepare($query) or die "cannot prepare query: $DBI::errstr";
      while(<DAT>)
      {
      chomp;
      my @line = split /\|/;
      $sth->execute(@line)
      or die "Execute error: $DBI::errstr";
      }
      $sth->finish();
      $dbh->disconnect();
      There are no errors when i run this code. But the table Held_Receipts is blank. If i use the import feature in access to import the text file, I get all my records into the table Held_Receipts I am completely stumped on this one!!!
[reply]
Re: Extracting data from a pipe delimited text file into a table in Access
by cciulla (Friar) on May 05, 2003 at 15:28 UTC

    Have you checked your DSN?

    The reason I ask is when I run your code unmodified, everything's kosher. Could it be that you have more than one DNS environment on your box (e.g., version one, version two, test, etc.)? I've been bit by this before -- run code against one database expecting the data to show up in another?

    FWIW, I'm running:

    • ActiveState 5.8.0.806
    • DBI 1.35
    • DBD-ODBC 1.05
    ... and when I tried a couple of different things (e.g., five fields vs. eight, fields six through eight "not null", etc.) perl complains rather loudly.

    Cē

[reply]
      Thanks c2!
      As far as I know i have one DNS environment
      I set up the DSN the usual way
      - Control Panel ->ODBC Data Sources->System DSN->Daily_Rec(which is linked to the database)
      I also checked if the Read-Only checkbox was unchecked
[reply]

        Okie doke... If you have access to another machine with perl and Access on it, try to recreate the error there.

        If it works there, then there's probably something hosed up with your config (e.g., wrong version of the driver or something silly like that).

        Cē

[reply]

Back to Seekers of Perl Wisdom