CR and LF Characters in Informix

Hi, Dear net dudes! I am try to input text into a table in Informix database server, by SQL statement below: INSERT INTO tablename (col_1) VALUES ('1st line\\n2nd line\\r3rd line') where \\n is LF and \\r is CR character. Well, the SQL statement is OK iff (if and only if) the col_1 data type is char(k) or varchar(m,n). If the data type is text, Informix server will give you an error message. Because Informix requires used load, dbload, 4GL, or Embed C, etc. to insert data type text!!! So I can only used char(2048) data type for col_1. I can't used varchar(m,n). Because where maximum for m is 255. But next problem for me is the Informix JDBC drive! If the text contains CR or LF. I will catch SQLException error message for detected an illegal character!!! Does anybody out there has experiences this kind of problems? And do you have a good/better solution for me? Currently I am just filted out all invisible characters, replace CR/LF to | (a vertical bar). It works OK. But I would like better solution(s) for that. Thank you very much in advance! --Raymond -- Why we want to teach our babies to walk and talk, then later we tell them "Sit Down"! "Be Quiet"!?

On Thu, 17 Feb 2000, RC wrote: >I am try to input text into a table in Informix database server, by SQL >statement below: > >INSERT INTO tablename (col_1) VALUES ('1st line\\n2nd line\\r3rd line')> >where \\n is LF and \\r is CR character. This doesn't work reliably with most versions of Informix. In the latest versions (9.20, and maybe 7.3x), I believe it is possible to have newlines in character string literals. The reliable way to insert such data is with placeholders in the SQL and supplying the values when you execute the statement: PREPARE p FROM "INSERT INTO tablename (col_1) VALUES (?)" LET str_var = '1st line\\n2nd line\\r3rd line' EXECUTE p USING :str This is some bastardized language most closely resembling I4GL, but it should convey the idea. >Well, the SQL statement is OK iff (if and only if) the col_1 data type >is char(k) or varchar(m,n). Interesting; which server version are you using? >If the data type is text, Informix server will give you an error >message. Yes; there are no BYTE or TEXT blob literals. To insert those, you *must* use placeholders, and you must supply a blob type of data. Now, how blobs translate into Java, I've no idea; in ESQL/C you'd use a loc_t structure. >Because Informix requires used load, dbload, 4GL, or Embed C, etc. to >insert data type text!!! > >So I can only used char(2048) data type for col_1. I can't use >varchar(m,n) because where maximum for m is 255. Yup. Of course, when you use CHAR(2048), that always occupies 2048 bytes on disk since the string is blank padded to full length. >But next problem for me is the Informix JDBC drive! If the text >contains CR or LF. I will catch SQLException error message for >detected an illegal character!!! Placeholders are your friend. ODBC and JDBC must provide ways to deal with placeholders. And you'd be quite likely to find that placeholders will allow you to use TEXT or BYTE types reasonably directly. >Does anybody out there has experiences this kind of problems? Yes. It's a problem for anyone using Perl, too. >And do you have a good/better solution for me? Placeholders is the main offered solution. You'll have to read the manuals to find out if JDBC can help you with the BYTE and TEXT data. >Currently I am just filted out all invisible characters, replace CR/LF >to | (a vertical bar). It works OK. But I would like better >solution(s) for that. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v0.95 -- http://www.perl.com/CPAN "Windows is NOT a virus: a virus is small and efficient." -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v0.95 -- http://www.perl.com/CPAN "Windows is NOT a virus: a virus is small and efficient."