inserting binary data to DB2 proxy table
Spencer Wasden
I'm trying to insert binary data on a proxy table to DB2 through a
DataDirect ODBC Wire 5.3 driver. The remote server is of class DB2ODBC.
The table was created like this:
CREATE EXISTING TABLE "DBA"."HHSIG_et"(
"CUST#" numeric(8,0) NOT NULL,
"DOC#" numeric(8,0) NOT NULL,
"DOCTYP" char(3) NOT NULL,
"SIGNATURE" long binary NOT NULL
) AT 'DB2_DD;;HHTEST;HHSIG';
The DB2 datatype for SIGNATURE is BLOB.
I can see data by running a select query.
I ran this in DBISQL:
insert into HHSIG_et ("CUST#", "DOC#", "DOCTYP", "SIGNATURE") values
(1,1,'a', convert(long binary, 33))
That failed with:
[DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and
AS/400]VALUE OF INPUT HOST VARIABLE NUM 4 NOT USED; WRONG DATA TYPE.
SIGNATURE.
I started ODBC tracing--this was the translated SQL (see partial log output
at bottom of post):
"INSERT INTO HHTEST.HHSIG ("CUST#", "DOC#", DOCTYP, SIGNATURE) VALUES (1
, 1 , 'a' , x'00000021' ) \ 0"
Running this query *directly* against DB2 works (note the BLOB function):
INSERT INTO HHTEST/HHSIG (CUST#, DOC#, DOCTYP, SIGNATURE) VALUES (1, 1 ,
'a' , BLOB(x'00000021'))
In DBISQL, when I wrap the binary data with BLOB (hoping preprocessing will
pass it through)...
insert into HHSIG_et ("CUST#", "DOC#", "DOCTYP", "SIGNATURE") values
(1,1,'a', BLOB(convert(long binary, 32)))
...I get "Procedure BLOB not found"
Any ideas on how to make this work? Thanks in advance
--------------------------------------------------
ODBC Trace:
--------------------------------------------------
dbsrv10 980-b28 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 03351830
SQLHANDLE * 02EBF614
dbsrv10 980-b28 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 03351830
SQLHANDLE * 0x02EBF614 ( 0x03352778)
dbsrv10 980-b28 ENTER SQLExecDirectW
HSTMT 03352778
WCHAR * 0x015A76A8 [ -3] "INSERT INTO
HHTEST.HHSIG ("CUST#", "DOC#", DOCTYP, SIGNATURE) VALUES (1 , 1 , 'a' ,
x'00000021' ) \ 0"
SDWORD -3
dbsrv10 980-b28 EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 03352778
WCHAR * 0x015A76A8 [ -3] "INSERT INTO
HHTEST.HHSIG ("CUST#", "DOC#", DOCTYP, SIGNATURE) VALUES (1 , 1 , 'a' ,
x'00000021' ) \ 0"
SDWORD -3
DIAG [HY000] [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2
for iSeries and AS/400]VALUE OF INPUT HOST VARIABLE NUM 4 NOT USED; WRONG
DATA TYPE. SIGNATURE (-301)
dbsrv10 980-b28 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 03352778
SQLSMALLINT 1
SQLWCHAR * 0x02EBF5C0 (NYI)
SQLINTEGER * 0x015A41E0
SQLWCHAR * 0x015A7778 (NYI)
SQLSMALLINT 511
SQLSMALLINT * 0x02EBF564
dbsrv10 980-b28 EXIT SQLGetDiagRecW with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 03352778
SQLSMALLINT 1
SQLWCHAR * 0x02EBF5C0 (NYI)
SQLINTEGER * 0x015A41E0 (-301)
SQLWCHAR * 0x015A7778 (NYI)
SQLSMALLINT 511
SQLSMALLINT * 0x02EBF564 (147)
dbsrv10 980-b28 ENTER SQLFreeStmt
HSTMT 03352778
UWORD 1 <SQL_DROP>
Karim Khamis
binary properly, but DB2 BLOB columns are a bit tricky. I do not think
there is anything you can do to get around the problem short of altering
the data type of the SIGNATURE column in DB2. It is probably best to
open a bug report for the problem and we'll fix it in SA.
Karim
Spencer Wasden
news:481b17ea@forums-1-dub...
> The Remote Data Access layer in SA handles DB2 columns of type long binary
> properly, but DB2 BLOB columns are a bit tricky. I do not think there is
> anything you can do to get around the problem short of altering the data
> type of the SIGNATURE column in DB2. It is probably best to open a bug
> report for the problem and we'll fix it in SA.
When you say "DB2 columns of type *long binary*" which of these DB2 types
are you referring to (these are all the binary types I could see from the
second section of
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbmlen10/ml-ibm-datatypes-ml-ref.html):
CHAR(n) FOR BIT DATA
VARCHAR(n) FOR BIT DATA
LONG VARCHAR FOR BIT DATA
BLOB
BINARY (not on that list)
We've already tried without success BLOB and VARBINARY (not on your list).
Thanks
Spencer
Karim Khamis
FOR BIT DATA. See if redefining the column as such in DB2 will do the
trick. Nevertheless, the problem is a bug in SA and I will make sure a
bug reported is created to get the issue fixed.
Karim
Karim
Unknown
the communications with Edgar indicates that
column to be
BINCHAR
and not a strict binary data type.
Now, I cannot find a single useful (IBM) hit on this but
the industry practice around that data type name is an
ASCII/CHARACTER encoded version of a binary.
BASE64, BASE36, Hex, Octal and a whole bunch of
other possibilities come to mind with hits on BASE64
and HEX in other context.
So (IFF it is a binding supported by the ODBC driver
and DB2 'shell', ..., etc.) binding it as a long varchar
may be the real requirement here.
If someone has an active login to the IBM DB2 support
site, maybe they can look that datatype up.
"Karim Khamis" <kkh...@sybase.com> wrote in message
news:481f48d3$1@forums-1-dub...
Reg Domaratzki (Sybase iAnywhere)
available in v10.0.1 build 3698, and will also be in the first
EBF we release for v11.0.0.
I should also point out that when accessing another RDBMS using
proxy tables and connecting to an RDBMS system that we support
as a Mobilink Consolidated database, you should always use the
ODBC drivers that we recommend for MobiLink to make your proxy
table connection as well.
See http://www.sybase.com/detail?id=1011880 for the list of
recommended ODBC Drivers.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Docs : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"
"Spencer Wasden" <spencer.wasden@mobiledataforce_xspm.com> wrote in
news:481a36d7$1@forums-1-dub: