How to handle the BOLB column Using JDBC adapter

[Former Member]

Hi,

I want to sending of a BOLB column from a DB2 database table to another DB2 database, and the sender and receiver both by using JDBC adapter. The two tables in each other database have the same columns.

Here is the table's structure:

<ID>string type</ID>

<PDF>blob type</PDF>

Also, i haved create two DataTypes in PI system.

Sender DataType:

ns:DT_PDF_Req xmlns:ns="http://XXXXX.com/sap/xi">

<row>

<ID>1</ID>

<PDF></PDF>

</row>

</ns:DT_PDF_Req>

Receiver DataType:

<ns0:DT_PDF_Res xmlns:ns0="http://XXXX.com/sap/xi">

<STMT>

<dbtable action="INSERT">

<table>tablename</table>

<access>

<ID></ID>

<PDF></PDF>

</access>

</dbtable>

</STMT>

</ns0:DT_PDF_Res>

When test this interface, i found the that we can get the data, but when execute the insert stms the follow errors occured in RWB :

<ERROR>

Could not execute statement for table/stored proc. "DBDPUSER.pdf" (structure "STMT") due to com.ibm.db2.jcc.b.nm: DB2 SQL Error: SQLCODE=-103, SQLSTATE=42604, SQLERRMC=255044462d312e330a25c7ec8fa20a352030206f626a0a3c3c2f4c656e677468203620, DRIVER=3.50.153

JDBC Message processing failed, due to Error processing request in sax parser: Error when executing statement for table/stored proc. 'DBDPUSER.pdf' (structure 'STMT'): com.ibm.db2.jcc.b.nm: DB2 SQL Error: SQLCODE=-103, SQLSTATE=42604, SQLERRMC=255044462d312e330a25c7ec8fa20a352030206f626a0a3c3c2f4c656e677468203620, DRIVER=3.50.153

</ERROR>

Can anybody tell me how to resolve the problems?

And tell me about how to deal BOLB column using PI.

Best Regards

Terry

[markangelo_dihiansan · Accepted solution]

Hello Terry,

Maybe you can refer to this blog in handling Binaries using JDBC Adapter

/people/praveen.gujjeti/blog/2010/03/28/sap-xipi-storing-binaries-images-pdfs-etc-in-the-database-blobs-using-jdbc-adapter

Hope this helps,

Mark

[Former Member]

Hi，

Thanks both of your kindly help.

I view the bolgs again, if i want to achieve that i have to do some java mapping and may be write a database SP.

It is said that we can not handle the bolb column as the normal type column using JDBC adapter.

Thanks again.

Terry

[RaghuVamseedhar]

Hi Terry Qin,

I understand, you are getting below XML from sender JDBC channel. But when you getting SAX parser error, in receiver JDBC.

<ns:DT_PDF_Req xmlns:ns="http://XXXXX.com/sap/xi">
<row>
<ID>1</ID> 
<PDF></PDF> 
</row>
</ns:DT_PDF_Req>

I think it is because the XML which is going to receiver JDBC channel is not well formed (because pdf can containg < & characters).

You can achive this sceanrio, by selecting receiver JDBC channel as Message Protocol "Native SQL Format" [Link1|http://help.sap.com/saphelp_nwpi711/helpdata/en/44/7c24a75cf83672e10000000a114a6b/frameset.htm] you can send non XML to receiver channel.

Before that, you have convert above input XML into SQL statement, using Java Mapping.

Regards,

Raghu_Vamsee

[Former Member]

Hi Mark,

Thank you for your help!

I have readed this bolgs, but it no fit to me.

Anybody have done the same scenarios can share the experience?

Best Regards,

terry

[markangelo_dihiansan]

Hello Terry,

I have not really implemented a similar scenario. But based from the blog, the blob field in the XML SQL is of xsd:hexBinary type. The scenario is file to JDBC, so this is what happens:

1.) The file adapter reads the binary payload

2.) The payload is transformed into hexadecimal stream using java mapping and is placed inside the stored procedure XML SQL (code is provided in the blog)

3.) The JDBC takes care of the rest.

Hope this helps,

Mark