Why won't this ISQL command run through Perl's DBI?

Question

A while back I was looking for a way to insert values into a text field through isql and eventually found some load command that worked out for me.

It doesn't work when I try to execute it from Perl. I get a syntax error. I have tried two separate methods and both are not working so far.

I have the SQL statement variable print out at the end of each loop cycle so I know that the syntax is correct, but just not getting across correctly.

Here's the latest snip of code I was testing:

foreach(@files)
{

$STMT = <<EOF;
load from $_ insert into some_table
EOF

$sth = $db1->prepare($STMT);
$sth->execute;

}

@files is an array whose elements are a full path/location of a pipe-delimited text file (ex. /home/xx/xx/xx/something.txt)

The number of columns in the table match the number of fields in the text file and the type-checking is fine (I've loaded test files manually without fail)

The error I get back is:

DBD::Informix::db prepare failed: SQL: -201: A syntax error has occurred.

Any idea what might be causing this?

---

EDIT to RET's & Petr's answers

$STMT = "'LOAD FROM $_ INSERT INTO table'";
    system("echo $STMT | isql $db")

I had to change it to this, because the die command would force an unnatural death and the statement had to be wrapped in single quotes.

Answer 1

A quick bit of Googling showed that the syntax for load puts quote marks around the file name. What if you change your statement to be:

load from '$_' insert into some_table

Since your statement is not using place holders, you have to put the quotes in yourself, as opposed to using the DBI quoting functionality.

Answer 2

This is because your query is not SQL query, it is an isql command that tells isql to parse the input file and generate INSERT statements.

If you think about it, the server can be on a completely different machine and has no idea what file are you talking about and how to access it.

So you basically have two options:

1. call isql and pipe the LOAD command to it - very ugly
2. parse the file yourself and generate the INSERT statements

Answer 3

Petr is exactly right, the LOAD statement is an ISQL or DB-Access extension, so you can't execute it through DBI. If you have a look at the manual, you'll see it is also invalid syntax for SPL, ESQL/C and so on.

It's not clear whether you have to use perl to execute the script, or perl is just a convenient way of generating the SQL.

If the former, and you want a pure-perl method, you have to prepare an INSERT statement (there's just one table involved by the look of it?), and slurp through the file, using split to break it up into columns and executing the prepared insert.

Otherwise, you can generate the SQL using perl and execute it through DB-Access, either directly with system or by wrapping both in either a shell script or DOS batch file.

System call version

foreach (@files) {
    my $stmt = "LOAD FROM $_ INSERT INTO table;\n";
    system("echo $stmt | dbaccess $database")
            || die "Statement $stmt failed: $!\n";
}

In a batch script version, you could write all the SQL into a single script, ie:

perl -e 'while(@ARGV){shift; print "LOAD FROM '$_' INSERT INTO table;\n"}' file1 [ file2 ... ] > loadfiles.sql
isql database loadfiles.sql

NB, the comment about quotes on the filename is only relevant if the filename contains spaces or metacharacters, the usual issue.

Also, one key difference in behaviour between isql and dbaccess is that when executed in this manner, dbaccess does not stop on error, but isql will. To make dbaccess stop processing on error, set DBACCNOIGN=1 in the environment.

Hope that's helpful.

Answer 4

Please note that the file Notes/load.unload is distributed with DBD::Informix and contains guidelines on how to handle UNLOAD operations using Perl, DBI and DBD::Informix. Somewhat to my chagrin, I see that it says "T.B.D." (more or less) for the LOAD section.

As other people have stated, the LOAD and UNLOAD statements are faked by various client-side tools to look like SQL statements, but the Informix server does not support them itself, mainly because of the issue with getting the file from a client machine (perhaps a PC) to the server machine (perhaps a Solaris machine).

To simulate the LOAD statement, you would need to analyze the INSERT INTO Table part. If it lists columns (INSERT INTO Table(Col03, Col05, Col09)), then you can expect three values in the load data file, and they go into those three columns. You would prepare a statement 'SELECT Col03, Col05, Col09 FROM Table' to get the types of the columns. Otherwise, you need to prepare a statement 'SELECT * FROM Table' to get the complete list of columns (and their types). Given the column names and the number of columns, you can create and prepare a suitable insert statement: 'INSERT INTO Table(Col03, Col05, Col09) VALUES(?,?,?)' or 'INSERT INTO Table VALUES(?,?,?,?,?,?,?,?,?)'. You could (arguably should) include column names in the second one.

With that ready, you now have parse the unloaded data. There is a document available in the SQLCMD program available from the IIUG Software Archive (which has been around a lot longer than Microsoft's upstart program of the same name). That describes the UNLOAD format in considerable detail. Perl has the ability to handle anything Informix uses - witness the UNLOAD information in the load.unload file distributed with DBD::Informix.