Strange behavior for INSERT
A user couldn't INSERT a string containing an ESC (0x1B) character via an SQL script in dbaccess — the server returned error -202 "illegal character in the statement" — even though the same data loaded fine with LOAD FROM. Art Kagel suggested it was a dbaccess limitation and recommended sqlcmd, but the poster got the identical error there, and Jonathan Leffler confirmed the rejection comes from the server itself. He judged it a bug (non-printing characters should be allowed inside quoted literals and comments, except ASCII NUL) and reported it internally as CQ idsdb00190112. No workaround or fix is recorded in the thread beyond using LOAD.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi there,
I encountered a strange behavior during INSERT statement. I have a column of
type LVARCHAR and want to insert data with non-printable char.
In this case I want to insert 'ESC' char. If opening the data with 'vi' you
would see '^['.
When I use this within an SQL script (INSERT INTO VALUES ....) I get the error:
-202 An illegal character has been found in the statement.
But when I use the SAME data within a load file and use LOAD FROM INSERT INTO
... it's working!
Both actions are done with exactly the same environment from a shell using
dbaccess.
Any explanation?
Dbaccess has trouble with non-printing character. You can try this with
Jonathan Leffler's sqlcmd tool which you can download from the IIUG Software
Repository or write a small 4GL or ESQL/C program to read the SQL and
execute it for you.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Wed, Sep 9, 2009 at 6:08 AM, JOERG REDEMANN <joerg.redemann@sabre.com>wrote:
> Hi there,
> I encountered a strange behavior during INSERT statement. I have a column
> of
> type LVARCHAR and want to insert data with non-printable char.
> In this case I want to insert 'ESC' char. If opening the data with 'vi' you
> would see '^['.
>
> When I use this within an SQL script (INSERT INTO VALUES ....) I get the
> error:
> -202 An illegal character has been found in the statement.
>
> But when I use the SAME data within a load file and use LOAD FROM INSERT
> INTO
> .... it's working!
>
> Both actions are done with exactly the same environment from a shell using
> dbaccess.
>
> Any explanation?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bdaacee2cff0473257131
FYI : Same thing... sqlcmd -d mx2@itest_tcp -f billingData.sql SQL -202: An illegal character has been found in the statement. SQLSTATE: IX000 at billingData.sql:11 [dcs@robin-dev data]$ sqlcmd -V sqlcmd: SQLCMD Version 86.00 (2008-07-15) IBM Informix CSDK Version 3.00, IBM Informix-ESQL Version 3.00.UC3DE Licenced under GNU General Public Licence Version 2 Doesn't matter if I run it against 10.00UC5 or 11.50.UC5 Thanks anyway. I will give it a try and hand this over to IBM support and ask them.
On Wed, Sep 9, 2009 at 06:56, Art Kagel<art.kagel@gmail.com> wrote:
> Dbaccess has trouble with non-printing character. You can try this with
> Jonathan Leffler's sqlcmd tool which you can download from the IIUG Software
> Repository or write a small 4GL or ESQL/C program to read the SQL and
> execute it for you.
This time, the problem is the server itself - not DB-Access. SQLCMD
quite happily send the message to IDS and rejects the statement as
containing an invalid character.
> On Wed, Sep 9, 2009 at 6:08 AM, JOERG REDEMANN
> <joerg.redemann@sabre.com>wrote:
>
>> Hi there,
>> I encountered a strange behavior during INSERT statement. I have a column
>> of
>> type LVARCHAR and want to insert data with non-printable char.
>> In this case I want to insert 'ESC' char. If opening the data with 'vi' you
>> would see '^['.
>>
>> When I use this within an SQL script (INSERT INTO VALUES ....) I get the
>> error:
>> -202 An illegal character has been found in the statement.
>>
>> But when I use the SAME data within a load file and use LOAD FROM INSERT
>> INTO
>> .... it's working!
>>
>> Both actions are done with exactly the same environment from a shell using
>> dbaccess.
>>
>> Any explanation?
Not any good one - it verges on a bug, and I'm only prevaricating
because I'm not sure exactly what the SQL rules are, or should be.
Inside a comment or a quoted string, it seems to me that ESC should be
OK; in the body of an SQL statement, the character is meaningless and
should cause an error of some kind. SELECT ESC ... (where ESC is the
escape char) should not be acceptable. It is more debatable whether
SELECT 'ESC' ... (where ESC is still the escape character) should be
acceptable. Clearly, at the moment, it is not.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Marie von Ebner-Eschenbach - "Even a stopped clock is right twice a
day." -
http://www.brainyquote.com/quotes/authors/m/marie_von_ebnereschenbac.html
On Wed, Sep 9, 2009 at 09:24, Jonathan Leffler<jleffler.iiug@gmail.com> wrote:
> On Wed, Sep 9, 2009 at 06:56, Art Kagel<art.kagel@gmail.com> wrote:
>> Dbaccess has trouble with non-printing character. You can try this with
>> Jonathan Leffler's sqlcmd tool which you can download from the IIUG Software
>> Repository or write a small 4GL or ESQL/C program to read the SQL and
>> execute it for you.
>
> This time, the problem is the server itself - not DB-Access. SQLCMD
> quite happily send the message to IDS and rejects the statement as
> containing an invalid character.
>
>> On Wed, Sep 9, 2009 at 6:08 AM, JOERG REDEMANN
>> <joerg.redemann@sabre.com>wrote:
>>
>>> Hi there,
>>> I encountered a strange behavior during INSERT statement. I have a column
of
>>> type LVARCHAR and want to insert data with non-printable char.
>>> In this case I want to insert 'ESC' char. If opening the data with 'vi' you
>>> would see '^['.
>>>
>>> When I use this within an SQL script (INSERT INTO VALUES ....) I get the
error:
>>> -202 An illegal character has been found in the statement.
>>>
>>> But when I use the SAME data within a load file and use LOAD FROM INSERT
INTO
>>> .... it's working!
>>>
>>> Both actions are done with exactly the same environment from a shell using
>>> dbaccess.
>>>
>>> Any explanation?
>
> Not any good one - it verges on a bug, and I'm only prevaricating
> because I'm not sure exactly what the SQL rules are, or should be.
> Inside a comment or a quoted string, it seems to me that ESC should be
> OK; in the body of an SQL statement, the character is meaningless and
> should cause an error of some kind. SELECT ESC ... (where ESC is the
> escape char) should not be acceptable. It is more debatable whether
> SELECT 'ESC' ... (where ESC is still the escape character) should be
> acceptable. Clearly, at the moment, it is not.
After a brief discussion internally, this is now reported as CQ bug
idsdb00190112, which has the short description (abstract, I think is
the approved jargon) of "IDS should permit any valid character to
appear inside a character string literal or comment". The only
exception to that is likely to be ASCII NUL '\\\\0' 0x00. That will most
probably continue to be regarded as an invalid character.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Stephen Leacock - "I detest life-insurance agents: they always argue
that I shall some day die, which is not so." -
http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html
hello What is the next character following the 'ESC' char ? Maybe the shell (ksh, bash, etc) translates the sequence of characters in another character ....