IDS Forum

Re: Strange behavior for INSERT

Posted By: Jonathan Leffler
Date: Wednesday, 9 September 2009, at 3:14 p.m.

In Response To: Re: Strange behavior for INSERT (Jonathan Leffler)

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

Messages In This Thread

• Strange behavior for INSERT
  JOERG REDEMANN -- Wednesday, 9 September 2009, at 6:08 a.m.
  • Re: Strange behavior for INSERT
    Art Kagel -- Wednesday, 9 September 2009, at 9:56 a.m.
    • Re: Strange behavior for INSERT
      JOERG REDEMANN -- Wednesday, 9 September 2009, at 10:31 a.m.
    • Re: Strange behavior for INSERT
      Jonathan Leffler -- Wednesday, 9 September 2009, at 12:24 p.m.
      • Re: Strange behavior for INSERT
        Jonathan Leffler -- Wednesday, 9 September 2009, at 3:14 p.m.
  • Re: Strange behavior for INSERT
    JOSé LUIS CABRERA SACCO -- Wednesday, 9 September 2009, at 3:14 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.