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

Question

I have an Informix 11.70 database.I am unable to sucessfully execute this insert statement on a table.

INSERT INTO some_table(
    col1,
    col2,
    text_col,
    col3) 
VALUES(
    5, 
    50, 
    CAST('"id","title1","title2"
"row1","some data","some other data"
"row2","some data","some other"' AS TEXT), 
    3);

The error I receive is:

[Error Code: -9634, SQL State: IX000] No cast from char to text.

I found that I should add this statement in order to allow using new lines in text literals, so I added this above the same query I have already written:

EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t');

Still, I receive the same error.

I have also read the IBM documentation that says: to alternatively allow new lines, I could set the ALLOW_NEWLINE parameter in the ONCONFIG file. I suppose the last one requires administrative access to the server to alter that config file, which I do not have, and I prefer not to take advantage of this setting.

Jonathan Leffler · Answer 1 · 2012-05-07T14:57:25.890

3

Informix's TEXT (and BYTE) columns pre-date any standard, and are in many ways very peculiar types. TEXT in Informix is very different from TEXT found in other DBMS. One of the long-standing (over 20 years) problems with them is that there isn't a string literal notation that can be used to insert data into them. The 'No cast from char to text' is saying there is no explicit conversion from string literal to TEXT, either.

You have a variety of options:

Use LVARCHAR in the table (good if your values won't be longer than a few KiB, because the total row length is approximately 32 KiB). Maximum size of an LVARCHAR column is just under 32 KiB.
Use a programming language which can handle Informix 'locator' structures — in ESQL/C, the type used to hold a TEXT is loc_t.
Consider using CLOB instead. However, this has the same limitation (no string to CLOB conversion), but you'd be able to use the FILETOCLOB() function to get the information from a file on the client to the database (and LOTOFILE transfers information from the DB to a file on the client).

If you can use LVARCHAR, that is by far the simplest alternative.

edited May 07 '12 at 14:57

answered May 07 '12 at 14:21

Jonathan Leffler

730,956
141
904
1,278

Thanks for the response. Unfortunately, it is Java that is used to communicate with the database, and an ORM (Hibernate in particular). Therefore I fear I cannot take advantage of the `loc_t` locator structure. As for the LVARCHAR type, it looks like it has approximately the same limitations as the NVARCHAR for the MS SQL Server, or am I wrong? If not, I fear that our text columns would hold longer strings than 4096 characters (if UTF-8 is used). – Ivaylo Slavov May 07 '12 at 14:33
1

LVARCHAR defaults to 2048 bytes, but can go up to a shade short of 32 KiB. But the whole row length also has the 32 KiB limit. BYTE, TEXT, BLOB and CLOB store a fixed size (56-byte or 64-byte) descriptor in the row and the data is stored elsewhere. LVARCHAR is stored in the row. How big do you need to get? Which JDBC driver are you using? The Informix driver (as opposed to Java Common Client or JCC version of JDBC) should handle BYTE, TEXT reasonably well, but probably not as a string literal as in the SQL shown. – Jonathan Leffler May 07 '12 at 14:55
I assume you tried without the CAST and it failed? People don't normally go adding CAST until they've run into a problem, but just in case... – Jonathan Leffler May 07 '12 at 14:59
We are using the IBM's Informix Driver. Most of the data stored is large CSV data, being read from files uploaded by users. The application has already been written to use PostgreSQL, therefore no direct reading of files from the DB server is done. Our task is to migrate the same application to use Informix. And, yes, I have tried using `CAST` unsuccessfully. – Ivaylo Slavov May 07 '12 at 15:01
2

You can also try to use PreparedStatement. It works well with JDBC and TEXT columns. See my question: http://stackoverflow.com/questions/483284/consistent-method-of-inserting-text-column-to-informix-database-using-jdbc-and-o – Michał Niklas May 08 '12 at 07:18
@MichałNiklas, thanks, the prepared statement was the way I resolved the issue. – Ivaylo Slavov May 10 '12 at 16:44
@JonathanLeffler, since I actually discovered the answer in a comment, I'd attempt to give it credit by creating a community-wiki answer. I just recently discovered that [this is a preferred approach](http://meta.stackoverflow.com/questions/251597/question-with-no-answers-but-issue-solved-in-the-comments/251598#251598) in such situations, discussed in meta. Please, do not feel offended in any way as I am about to un-mark this as an answer, the post is still useful and informative. Besides, the comment would not have been there if not for your answer :) – Ivaylo Slavov May 15 '14 at 21:53

score 0 · Accepted Answer · edited May 23 '17 at 10:25

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.

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

2 Answers2