[Info-ingres] database procedures, string concatenation. long varchars and my slow descent into insanity

[Martin Bowes]

I've now shown the database procedure part was a furphy. A simple insert has the same effect.

It has also been pointed out the descent into insanity was a very short trip.

Marty

From: Martin Bowes [mailto:martin.bowes at ndph.ox.ac.uk]
Sent: 12 October 2017 14:35
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] database procedures, string concatenation. long varchars and my slow descent into insanity

Hi All,

II 10.2.0 (a64.lnx/100) +p15162

Here's some fun...

create table a_long(txt long varchar not null not default);
create table another_long(txt long varchar not null not default);

Insert a decent sized string.

Do a select txt from a_long and all is well.

Do a select '@' + txt from a_long and you are greeted with: E_SC0206

Plus a host of message in the errlog indicating the length of the object is screwed up.
DBHUB_NDPH_OX_AC_U::[43371             , 2844      ,  00007fd9d309c200, aduredeem.c:888       ]: Thu Oct 12 13:50:03 2017 E_AD7004_BAD_BLOB A peripheral datatype (BLOB) was found whose coupon length did not agree with the amount of data found.
DBHUB_NDPH_OX_AC_U::[43371             , 2844      ,  00007fd9d309c200, scsqncr.c:14173       ]: Thu Oct 12 13:50:03 2017 E_SC0216_QEF_ERROR    Error returned by QEF.
DBHUB_NDPH_OX_AC_U::[43371             , 2844      ,  00007fd9d309c200, scsqncr.c:14174       ]: Thu Oct 12 13:50:03 2017 E_SC0206_CANNOT_PROCESS   An internal error prevents further processing of this query.

Now the funny bit.  I'm going to use string concatenation inside a database procedure:
create procedure insert_long
as declare
    msg   varchar(256) not null not default;
    items long varchar not null not default;
begin
    select txt into :items from a_long;

    insert into another_long(txt)
    values(
        '@' + items
    );

end;

execute procedure insert_long...
No error is generated but a select txt from another_long shows the data is mangled.

On one consistent example I have a string which contains the element: ^F21000[10&i#nc=7&i#ety=11&i#eid=1001&i#c0=2&i#c1=4&i#c2=2001&i#c3=2002&i#c4=4001&i#c5=4002&i#c6=4003]^F21001

But the data inserted into another_long has become:
^F21000[10&i#nc=7&i#ety=11&i#eid=1001&i#c0=2&i#c1=4&i#c2=2001&i#c3=2002&i#c4=4001&i#c5=4002&i#c=4003]^F21001^

Yes I am going to raise a bug.

Martin Bowes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20171012/0189f790/attachment.html>