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:
For the Informix .NET provider (ver: 3.5.xC7), do NOT, explicitly set the
IfxType
orDbType
when generating anIfxParameter
(orDbParameter
). 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").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
Postgresql - Query from Bash Script as Database User 'Postgres'
Select Latest Records by Datetime Field
How to Release Possible Postgres Row Locks
Differencebetween a Hash Join and a Merge Join (Oracle Rdbms )
SQL Missing Right Parenthesis on Order by Statement
Need to Find Average Processing Time Between All Timestamp Records in Oracle SQL
Comma-Separated Value Insertion in SQL Server 2005
MySQL Views - When to Use & When Not To
Divide the Table Data Randomly Based on Percentages
Error Trapping Code Using Ado Connections
Db2 - Returning the Top 5 of Each Category
SQL -- How Is Distinct So Fast Without an Index
Sqlserver: How to Sort Table Names Ordered by Their Foreign Key Dependency
Oracle Insert into Table2 Then Delete from Table1, Exception If Fail