Where´s the "illegal character"???

Jens Engel

unread,

Dec 5, 2001, 6:47:45 AM12/5/01

to

INSERT into sysfaq (TEXT_faq) values ("\"\'test\'\"")

always results

Warning: SQL error: [Informix][Informix ODBC Driver][Informix]An illegal
character has been found in the statement., SQL state S1000 in SQLExecDirect
in d:\intern\sysfaqifx\add2.php on line 297

Warning: SQL error: [Informix][Informix ODBC Driver][Informix]A syntax error
has occurred., SQL state 37000 in SQLExecDirect in
d:\intern\sysfaqifx\add2.php on line 308

WHY??? :o(((

--
Salve, JE
www.McQueenSix.de

newton klea

unread,

Dec 5, 2001, 3:47:02 PM12/5/01

to

try this:

INSERT into sysfaq (TEXT_faq) values ('"' || "'test'" || '"')

"Jens Engel" <Jens....@stadt-mh.de> wrote in message news:<9ul1h1$jb0$1...@rzcom2.stadt-mh.de>...

Jonathan Leffler

unread,

Dec 6, 2001, 12:02:39 AM12/6/01

to

Jens Engel wrote:

Newton Klea gave you a correct alternative (not the only correct
alternative), but no explanation.

The illegal character is the second backslash. The trouble is that you
are thinking of C, where a backslash is used to escape other characters.
SQL uses different rules: if you need an embedded quote in a string,
you use two of them in a row. So, your values list contains a valid
string in double quotes consisting of a single backslash, followed by a
backslash, and the backslash is an invalid character in SQL outside of a
string.

An alternative way of achieving what you wanted, without using the ||
string concatenation operator is:

INSERT INTO sysfaq (TEXT_faq) VALUES("""'test'""");
INSERT INTO sysfaq (TEXT_faq) VALUES('"''test''"');

Now there's just one residual problem - if that TEXT_faq column is a
TEXT blob, then you are about to run into the problem that there are no
blob literals in Informix. This was discussed in the last week and you
will need to use a placeholder question mark to represent the value and
pass in the value via a loc_t structure. Well, that would be what's
required in ESQL/C; I'm not sure about ODBC. I've heard rumours that
the driver attempts to fix that problem for you...

--
Jonathan Leffler (jlef...@earthlink.net, jlef...@informix.com)
Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
#include <disclaimer.h>

Fuzzy

unread,

Dec 6, 2001, 6:10:12 PM12/6/01

to

On 5 Dec 2001 12:47:02 -0800, nuk...@my-deja.com (newton klea) wrote:
>>
>> always results
>>
>> Warning: SQL error: [Informix][Informix ODBC Driver][Informix]An illegal
>> character has been found in the statement., SQL state S1000 in SQLExecDirect
>> in d:\intern\sysfaqifx\add2.php on line 297
>>
>> Warning: SQL error: [Informix][Informix ODBC Driver][Informix]A syntax error
>> has occurred., SQL state 37000 in SQLExecDirect in
>> d:\intern\sysfaqifx\add2.php on line 308
>>
>> WHY??? :o(((

Informix ODBC Drivers 2.60 thru to 3.31 all have a bug with using
single quotes (escaped correctly). Informix know about this, and
tried to fix it in 3.32. They half did. You can use escaped quotes
in 3.32 and 3.33, but only if the field doesn't also contain a control
character (e.g. carriage return.)

3.80 is better ... it only has a problem with some control characters.
So my advice is to go to www.informix.com/evaluate, get the latest
client (2.70), and install it to get the 3.80 driver.

Ciao
Fuzzy
:-)