Re: DBI, DBD::Informix and Statement SIze

[Rasoul Hajikhani]

Jonathan Leffler wrote:
Rasoul Hajikhani wrote:

Jonathan Leffler wrote:

Rasoul Hajikhani wrote:

Here is a brief description of my DBD::Informix

    Type:                  dr
    Name:                  Informix
    Version:               2003.04
    Attribution:           Jonathan Leffler <[EMAIL PROTECTED]>
    Product:               INFORMIX-ESQL Version 9.51.UC1
    Product Version:       951
I am trying to insert a 16MB data into a idn_mrLvarchar data type 
which has 2GIG capacity. This I can achieve using LOAD statement 
within 'dbaccess'. However, going through DBI, I can only achieve 
256Bytes of data insertion. DBI::bind_param, and 
DBI::bind_param_inout are not implemented. I have used place holders 
with no effect either. Short of asking 'What Next', I am wondering 
if DBI does not care about the size of a SQL statement, which some 
of this group's members suggested some other day in their email to 
me, then what is preventing this insertion? I have a read some old 
email on this list and in one of them it was suggested that the sql 
size limit is 64K (True/False?). Is there any way of changing size 
limit in DBI, if such functionality exists at all?


The bind_param method is implemented, 


True, my mistake... :(

though bind_param_inout is not.

Informix poses a limit of 64 KB on the size of a statement - the DBMS 
and access code such as ESQL/C does this.  DBD::Informix cannot 
change it.  However, you can provide parameters which are much larger.

Since I don't recognize idn_mrLvarchar as a data type - though I 
suspect it is a DataBlade (probably the multi-representational long 
varchar data type from the Informix Developer Network), and someone 
had a question about this on comp.databases.informix a week or so ago 
(a bit before I took some vacation) - I suspect that most of the 
people on the dbi-users mailing list won't recognize it either.


 From 
http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/db_mrLvarchar.html: 

The idn_mrLvarchar user-defined type (UDT) stores character data up to 
2 gigabytes. It is like the built-in lvarchar SQL type, but without 
the 2K column limit.


OK - it is the multi-rep varchar data type.  I will have to get it 
installed on my machine, I suppose.

idn_mrLvarchar is implemented as a multirep, which means that text 
shorter than 2K is stored in-row and text longer than 2K is stored in 
a smart blob. (Ok, 8 bytes of housekeeping information gets maintained 
in the UDT value. Specifically, a value <= 2040 bytes is stored 
in-row. Anything larger gets stored in a smart blob.)

Since you don't show us any code - let alone provide a simple 
reproduction of your problem - I can't yet provide very much more help.


use DBI;
#Read the contents of a file w/ 12mb of data...
my $theFile     = $ARGV[0];
undef $/;
my $fh          = new IO::File "$theFile","r" or die "IO::FILE NEW 
FAILED $theFile: $!\n";
my $data        = <$fh>;
close $fh or die "CLOSE FAILED: $!\n";

my $sql         = "INSERT INTO secure_users 
(code,username,gpgusername,gpgpassphrase_mrlvc) VALUES (?,?,?,?)";

my $sth         = $dbh->prepare($sql) || die "ERROR: $DBI::errstr\n";
my $rc          = $sth->bind_param(1,1456);
$rc             = $sth->bind_param(2,'yadayada');
$rc             = $sth->bind_param(3,'yayayay');
$rc             = $sth->bind_param(4,"$data");
$sth->execute();
$sth->finish();
Using the above method and code, only 256b are inserted into the 
table. The large data gets truncated at insertion time.


OK: the problem is, I think, that the DBD::Informix code only handles 
UDTs up to 256 bytes at the moment.  That was a limit that was going to 
have to go, sooner or later, and now maybe sooner rather than later.  
However, I've got another change in the works which is, I think, a 
pre-requisite for this and a few other changes (including 
bind_param_inout!).

In the meantime, try this untested workaround:

use DBI;
use DBD::Informix qw(:ix_types);
...
$rc  = $sth->bind_param(4, $data, { ix_type => SQLLVARCHAR });
Here is my code:

$rc             = $sth->bind_param(4,"$data",{TYPE => SQL_LONGVARCHAR});

Well, now there is an issue of casting. This statement fails because:

"DBD::Informix::st execute failed: SQL: -9634: No cast from text to 
idn_mrlvarchar."

I am not quite sure what type of an implicit cast to create since the 
DBD::Informix refers to the SQL_LONGVARCHAR as a TEXT Blob. I qoute:

"And the only type in this third group is treated as equivalent to a 
TEXT blob."

Any ideas there?

Thanks in advance
-r
...

That may well get you around the problem.

You'll need to check the spelling of the RHS of the => -- that's in the 
documentation for DBD::Informix (v2003.04).

The theory is that if you tell it that the type is an LVARCHAR, it will 
transfer up to 2 GB of data and the DBMS will handle the conversion to 
your UDT.  Whether the practice matches the theory remains to be seen.