Informix 7.3 Isql Insert Statement - Text/Blob/Clob Field Insert Error

Informix 7.3 isql insert statement - text/blob/clob field insert error

in iSQL....

Load from desc.txt insert into crsabstr_rec;

3 row(s) loaded.

desc.txt is a | (pipe) delimited text file and the number of fields in the txt have to match the number of fields in the table

How can I INSERT data in TEXT datatype field? (Informix)

Using a | (pipe) delimited file, you can use the LOAD command to insert values into blob & text data types. I had the same problem in the past - go to link in my comment

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

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.

Error Inserting Entry With Text Column That Contains New Line And Quotes

I forgot to mention an important detail in the question - I use Java and the Hibernate ORM to access my Informix database, thus some of the suggested approaches (the loc_t handling in particular) in Jonathan Leffler's answer are unfortunately not applicable. Also, I need to store large data of dynamic length and I fear the LVARCHAR column would not be sufficient to hold it.

The way I got it working was to follow Michał Niklas's suggestion from his comment, and use PreparedStatement. This could potentially be explained by Informix handing the TEXT data type in its own manner.

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

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.

Informix (C#): How do I properly set/unset a blob field?

I figured that since no one stated that what I experienced isn't a bug, I'll post my findings as an answer:

  1. For the Informix .NET provider (ver: 3.5.xC7), do NOT, explicitly set the IfxType or DbType when generating an IfxParameter (or DbParameter). Instead, just generate the parameter and allow the Informix .NET provider to map the correct types for you. This allows you to not have to worry about typecasting your parameters (i.e. avoid, "?::blob" and "?::byte").

  2. Due to a bug (ver: 3.5.xC7), inserting a byte[] array into a Blob field works fine, but updating that Blob field using a byte[] array will result in a 609 error. Instead, an IfxBlob will have to be created and set as the value. This also works when doing inserts.



Related Topics



Leave a reply



Submit