Does the choice in quoting have an affect here?
If the field is CHAR(8) why does the command line give sixteen characters?
I'm still wondering whether or not the $ on the field name is the culprit.
Try creating a view that gives the column a different name and see what
happens.
JB
__________
I tried your suggestion.
DB2 also has a CAST(field AS CHAR( )).
$sth = $dbh->prepare ( " SELECT CAST(\$moid AS CHAR(16)) FROM abc.xyz);
but the result was binary data with 8 additional spaces. Is there something
wrong in the above code
If you have any other suggestion please let me know
Thanks
Sachin
Stephen,
Using DB2 V7.1 : DBI : 1.21 DBD::DB2 0.76
db2 describe table catenv.cattabl
Column name: $MOID Type name: CHARACTER Length: 8
This is different from the information I got Thru DBI
Column Name: $MOID Type: -2 (Which is SQL_BINARY) Precision: 8
So do I conclude a bug in DBD::DB2 ??
Any place to ask for more details or submit a problem report
Thanks
Sachin
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
----------
I don't know DB2, but I'm wondering if you're getting a weird binary
rendition of characters.
Each character is one byte and each byte contains two nybbles, each nybble
being represented by one character.
For instance, the hexadecimal ASCII code for the letter "H" is "48", so
"Hello." would yield "48656C6C6F2EXXXX" (using X to pad out to
sixteen
characters)
and "7AF9FA30" (a pointer value) would yield "3741463946413330".
Perhaps your column contains eight bytes of binary data and the & tells the
system to apply such a translation.
The DB2 documentation should explain it.
You should be able to compare the output from both the command line and
your
program and see if this is true.
The difference between the ways you see the data is not surprising.
When I select a date using the SQL interface (command line) I get it as a
string like "2002-02-27 12:34:56.78"
but when I select a date in a program it is the binary rendition unless I
use a function...
Oracle: TO_CHAR ( field , 'YYYY-MM-DD HH24:MI.SS' )
RDB: CAST ( field AS CHAR(22) )
-----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
-------------------------------------------------------
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.
**********************************************************************
