Hey guys,

I have a question related to inserting binary data into LVARCHAR fields.
This is what we do:
1) We use Zlib and compress a bunch of data (level 9 compression is been
used)
2) The through ESQL/C we pass these data into an SPL through an LVARCHAR
field.
3) Then through Perl we read the data and we uncompress them using
Compress::Zlib.

Now everything goes well until one of those compressed binary data contains
\000 chatacters. At this point only the data up to the first \000 get
stored into the LVARCHAR column. If you ask me it makes sense, considering
this is an LVARCHAR field.

The question is if there is any work-around you are aware of?

FYI, I used the Compress::Zlib::compress function and no \000 were created,
I am guessing this is becuase different options are used through it than
what my colueges do in ESQL/C.

Ohhh we my sandbox is:

Linux 2.4.20
Perl 5.6.1
IDS 9.3
Informix-ESQL Version 9.50.UC1

Anyways, any help will be greatly appreciated.


George

> Hey guys,
>
> I have a question related to inserting binary data into LVARCHAR fields.
> This is what we do:
> 1) We use Zlib and compress a bunch of data (level 9 compression is been
> used)
> 2) The through ESQL/C we pass these data into an SPL through an LVARCHAR
> field.
> 3) Then through Perl we read the data and we uncompress them using
> Compress::Zlib.
>
> Now everything goes well until one of those compressed binary data
contains
> \000 chatacters. At this point only the data up to the first \000 get
> stored into the LVARCHAR column. If you ask me it makes sense,
considering
> this is an LVARCHAR field.
>
> The question is if there is any work-around you are aware of?
>
> FYI, I used the Compress::Zlib::compress function and no \000 were
created,
> I am guessing this is becuase different options are used through it than
> what my colueges do in ESQL/C.
>
> Ohhh we my sandbox is:
>
> Linux 2.4.20
> Perl 5.6.1
> IDS 9.3
> Informix-ESQL Version 9.50.UC1
>
> Anyways, any help will be greatly appreciated.
>
>
> George
>
>

Well it seems that INSERTing the data is not the problem but rather SELECT
is. Using octet_length() gives me the correct amount of data stored.

George

> > Hey guys,
> >
> > I have a question related to inserting binary data into LVARCHAR fields.
> > This is what we do:
> > 1) We use Zlib and compress a bunch of data (level 9 compression is been
> > used)
> > 2) The through ESQL/C we pass these data into an SPL through an LVARCHAR
> > field.
> > 3) Then through Perl we read the data and we uncompress them using
> > Compress::Zlib.
> >
> > Now everything goes well until one of those compressed binary data
> contains
> > \000 chatacters. At this point only the data up to the first \000 get
> > stored into the LVARCHAR column. If you ask me it makes sense,
> considering
> > this is an LVARCHAR field.
> >
> > The question is if there is any work-around you are aware of?
> >
> > FYI, I used the Compress::Zlib::compress function and no \000 were
> created,
> > I am guessing this is becuase different options are used through it than
> > what my colueges do in ESQL/C.
> >
> > Ohhh we my sandbox is:
> >
> > Linux 2.4.20
> > Perl 5.6.1
> > IDS 9.3
> > Informix-ESQL Version 9.50.UC1
> >
>
> Well it seems that INSERTing the data is not the problem but rather SELECT
> is. Using octet_length() gives me the correct amount of data stored.
>

Our DBA went looking and he told me that after the first \000 character the
remaining data are replaced by a bunch of spaces, so it seems this is an
INSERT problem afterall.

George Karabotsos wrote (at three levels of indentation):
>>>I have a question related to inserting binary data into LVARCHAR fields.
>>>This is what we do:
>>>1) We use Zlib and compress a bunch of data (level 9 compression is been
>>>used)
>>>2) The through ESQL/C we pass these data into an SPL through an LVARCHAR
>>>field.

So, the field in the database is LVARCHAR? We may yet need to see the
minimal ESQL/C code that does the job.

>>>3) Then through Perl we read the data and we uncompress them using
>>>Compress::Zlib.

OK - Perl and what? Your own module? Or DBI + DBD::Informix? Or
something else? Can we see the code?

>>> Now everything goes well until one of those compressed binary
>>> data contains \000 characters. At this point only the data up
>>> to the first \000 get stored into the LVARCHAR column. If you
>>> ask me it makes sense, considering this is an LVARCHAR field.

Embedded ASCII NUL '\0' characters are problematic. How do you
determine lengths?

>>>The question is if there is any work-around you are aware of?
>>>
>>> FYI, I used the Compress::Zlib::compress function and no \000
>>> were created, I am guessing this is becuase different options
>>> are used through it than what my colueges do in ESQL/C.

Ouch - if your compression doesn't produce the same as their
compression, then your decompression won't produce what their
decompression would produce?

One workaround is to use a Base64 encoding of the compressed data.
That loses some efficiency, of course. Another is to replace any
embedded NUL characters with some escape sequence - and deal with the
escape character too - and then store that.

>>>Ohhh we my sandbox is:
>>>
>>>Linux 2.4.20
>>>Perl 5.6.1
>>>IDS 9.3
>>>Informix-ESQL Version 9.50.UC1


Perl and Zlib module versions might be relevant.

>>Well it seems that INSERTing the data is not the problem but rather SELECT
>>is. Using octet_length() gives me the correct amount of data stored.

Sounds promising...

> Our DBA went looking and he told me that after the first \000 character the
> remaining data are replaced by a bunch of spaces, so it seems this is an
> INSERT problem after all.

How did your DBA determine the blank padding occurs? A hex page dump,
or something else?

Many more questions than answers.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

> George Karabotsos wrote (at three levels of indentation):
> >>>I have a question related to inserting binary data into LVARCHAR
fields.
> >>>This is what we do:
> >>>1) We use Zlib and compress a bunch of data (level 9 compression is
been
> >>>used)
> >>>2) The through ESQL/C we pass these data into an SPL through an
LVARCHAR
> >>>field.
>
> So, the field in the database is LVARCHAR? We may yet need to see the
> minimal ESQL/C code that does the job.
>
Yes the field in the DB is an LVARCHAR. Not sure if I can get my hands on
the ESQL/C code. I 'll try though

> >>>3) Then through Perl we read the data and we uncompress them using
> >>>Compress::Zlib.
>
> OK - Perl and what? Your own module? Or DBI + DBD::Informix? Or
> something else? Can we see the code?
>
DBI + DBD::Informix.
Here's the code, please note I have simplified it just to illustrate the
problem; I removed all the Compress::Zlib stuff since I cannot post the
data:
================================================== ====================
#!/usr/bin/perl

use strict;
use warnings;

use English;
use Compress::Zlib;
use DBI;

my $oDBHandle=DBI->connect('DBI:Informix:george_db@dbdev',
'gkarabot','giwrgos1',
{RaiseError=>1,AutoCommit=>0});
my $sCData="DATA1 DATA2 DATA3 DATA4 NULL DATA5 NULL DATA6 DATA7";
$sCData =~ s/NULL/\0/g;
print("CData:".length($sCData)."\n");
my $sInsert=<<EOSQL;
INSERT INTO dummy (data) VALUES (?)
EOSQL
my $oSQLHandle=$oDBHandle->prepare($sInsert)
|| die($oDBHandle->errstr);
$oSQLHandle->execute($sCData)
|| die($oSQLHandle->errstr);
$oDBHandle->commit();
$oDBHandle->disconnect();

Execution:
% ./c.pl
CDate:45

The table is created as:
CREATE TABLE dummy (data LVARCHAR);

Thought sqlcmd:
SQL[29]: select data::VARCHAR(100) from dummy;
DATA1 DATA2 DATA3 DATA4
SQL[30]: select length(data),octet_length(data) from dummy;
23|45

================================================== =====================

> >>> Now everything goes well until one of those compressed binary
> >>> data contains \000 characters. At this point only the data up
> >>> to the first \000 get stored into the LVARCHAR column. If you
> >>> ask me it makes sense, considering this is an LVARCHAR field.
>
> Embedded ASCII NUL '\0' characters are problematic. How do you
> determine lengths?
>
length and octet_length.

> >>>The question is if there is any work-around you are aware of?
> >>>
> >>> FYI, I used the Compress::Zlib::compress function and no \000
> >>> were created, I am guessing this is becuase different options
> >>> are used through it than what my colueges do in ESQL/C.
>
> Ouch - if your compression doesn't produce the same as their
> compression, then your decompression won't produce what their
> decompression would produce?
>
Even if the compression is not the same, decompression will work for both

> One workaround is to use a Base64 encoding of the compressed data.
> That loses some efficiency, of course. Another is to replace any
> embedded NUL characters with some escape sequence - and deal with the
> escape character too - and then store that.
>
We decided to escape the \000 characters:
Compress:
s/\0/\\0/g
s\\/\\\\/g
Uncompress
s/\\\\/\\/g
s/\\0/\0/g

> >>>Ohhh we my sandbox is:
> >>>
> >>>Linux 2.4.20
> >>>Perl 5.6.1
> >>>IDS 9.3
> >>>Informix-ESQL Version 9.50.UC1
>
>
> Perl and Zlib module versions might be relevant.
>
Compress::Zlib v1.21
DBD::Informix v1.01.PC1
DBI v1.30

> >>Well it seems that INSERTing the data is not the problem but rather
SELECT
> >>is. Using octet_length() gives me the correct amount of data stored.
>
> Sounds promising...
>
> > Our DBA went looking and he told me that after the first \000 character
the
> > remaining data are replaced by a bunch of spaces, so it seems this is an
> > INSERT problem after all.
>
> How did your DBA determine the blank padding occurs? A hex page dump,
> or something else?
>
no idea how he figured it out, but I send him an email to let me know how.
I will let you know as soon as I do.

George