Sachin:
What version of DB2 and DBI are you using. We had a problem with the
Perl DBI on certain select statements (mostly recusive selects over
common tables created in with clauses) where CHAR and VARCHAR data
got converted to a numerical representation of their ASCII values
much like John Boucher was thinking. It was a bug in the IBM
implementation of DBI that was corrected with the DBI for V7.1
databases. If you are reading from and older DB2 database or using
and older version of DBI (not sure which had the problem exactly),
you might be running into that issue.
As a workaround you can try an explicit cast in your query:
my $sth=$dbh->prepare('select char($moid) from catenv.cattabl');
I don't know if this will work.
I tried lots of stuff to work around this issue, but the recusion was
clouding my success. Eventually gave up and wrote a standalone CLI
program in C to do the one query that we couldn't reengineer. IBM
eventually fixed the problem. If you run the db2 describe command
(db2 describe table catenv.cattabl), what does the database return?
Stph
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 27, 2002 10:27 AM
To: Stephen Keller
Cc: [EMAIL PROTECTED]
Subject: RE: SELECT \$moid FROM xyz.abc
Hi Stephen,
I am assuming it is a pointer. The column definition says $MOID stores
&CHAR(8).
The actual data I am trying to get is a string like "413567EAFFCXXX" 16
characters.
I would think this is not a LOB/BLOB...
Also when I do a table_info( ) with DBI the TYPE comes up as -2
(SQL_BINARY).
As I have said earlier : when I use command line "SELECT \$moid from ...."
I get the 16 characters but from DBI::DB2 I get binary data
Thanks
Sachin
Hi Sachin,
Hopefully, I've not completely misunderstood your question. I'm
stuggling with how to interpret what you mean by the "&char" data
value: that looks like C notation for a pointer. If by "&char is
being stored in that you column" you mean it is a CLOB or other
type of large object, you may need to DBI blob_read method,
depending on it's length and the version of the DB2 database.
The blob_read is a Statement Handler method and is employed
something like this (DATA is a LOB in this case).
my $sth = $dbh->prepare ('select $moid from catenv.cattabl');
$sth->execute();
my $resultsValue = 0;
my $count = 0;
while ( $resultsValue = $sth->fetch() ) {
$count++;
if ( open ( OFILE, sprintf (">ofile.%03d", $count ))) {
# we got some LOB data.
while ( $buff = $sth->blob_read(1,0,4096) ) {
print OFILE $buff;
$buff = q();
}
}
else {
printf STDERR ("Cannot open output file #%03d", $count);
}
}
}
else {
printf STDERR ("Cannot open output file: $!\n");
}
At this point, the data LOB data for each record will be stored in
a files named ofile.001, ofile.002, ... ofile.00n in your current
working directory.
I hope I'm getting at what you are asking.
stph
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 27, 2002 5:28 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: SELECT \$moid FROM xyz.abc
Hello,
Still need help.....
So far the discussions have revolved around getting the $moid to be passed
on to DB2.
I think this is already being done because I do get a list.
The problem is that the list is all SQL_BINARY data since &char is being
stored in that column.
The question is : How do I DEREF the address?
[snip...]
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
**********************************************************************
