Re: Informix DBD and lvarchar

On Fri, 2007-03-09 at 11:36 -0600, Goldrick, Jim wrote: > I am trying to return values from the genxml datablade to a perl script. > Informix Dynamic Server version 9.40.HC5, so I should have lvarchar up > to 32K or so. The output is fine in dbaccess, but is truncated in perl. > > column type="char" name="fullname"> > Pumpkineater, Peter </column> > <column type="integer" name="id"> > 106958</column> > <column type="char" name="cat"> > UG01</column> > <column type="smallint" name="yr"> > 2002</column> > <column type="char" name="se<row> > > The name of the last column above should be sess. And there are several > columns after that. I read in the DBD::Informix that lvarchar was > limited to 2048, unless it was returning a different type. The type > returned is xmlout, a cast from lvarchar. I wondering if there is any > way to get the full output from the DBD. Here is one version of the > perl test script. > > $db1->{PrintError} = 1; > $STMT = <<"EOS"; > select distinct i.fullname, c.id, c.cat, c.yr, c.sess, c.crs_no, c.sec > from cw_rec c, id_rec i > where c.yr > 2000 > and c.crs_no matches "ENG*" > and c.stat = "R" > and c.id = i.id > into temp a with no log;> EOS > > $db1->do($STMT); > > $STMT = <<"EOS"; > create temp table xmlout(xml xmldata) with no log;> EOS > $db1->do($STMT); > > $STMT = <<"EOS"; > insert into xmlout(xml)> select genxml("row", a) row from a; > EOS > $db1->do($STMT); > > $STMT = <<"EOS"; > select xml from xmlout;> EOS > > $sth = $db1->prepare($STMT); > if ( !defined($sth) ) > { die "Cannot prepare directory select: $STMT"; } > else > { > $sth->execute(); > while ( $ref = $sth->fetchrow_hashref() ) > { print $ref->{'xml'}; } > } > 1; > > Any help would be appreciated. As the maintainer of the Python InformixDB module, the advice I'm most qualified to give is that you should try using Python instead of Perl :) If you insist on using Perl, here are a few random ideas. The README for DBD::Informix says this: DBD::Informix, Version 1.00 and later, provides limited support for user-defined data types (UDTs), treating them as CHAR(255). To handle BLOB and CLOB values, use LOTOFILE() when you fetch the data and FILETOBLOB() or FILETOCLOB() when you insert data - see t/t91udts.t for examples. To handle nonblob UDTs that exceed 255 characters in length, use server-side cast to lvarchar, as in select mycol::lvarchar from mytab; This sounds like DBD::Informix might not know how to bind the output type of genxml, so you'll probably have to work around this. I don't have that datablade, so these suggestions are not tested: * Try explicitly casting the result to lvarchar. * Googling indicates that genxml may have a sister function called genxmlclob. If that's present in the version you have, you should be able to get the full contents with something like select lotofile(genxmlclob(...),...). Hope this helps, -Carsten