Skip to Content
0

How to send a payload as one blob using JDBC receiver (involving store procedure)

Nov 02, 2016 at 11:37 AM

51

avatar image

Hello SAP gurus

We have a requirement wherein payload comes into PI and has to be sent to an MSSql store procedure as a single string (Blob).

Also, I need to know where will I be configuring the details of store procedure. I couldn't find the right field to use in the JDBC Receiver configuration screen in the iflow.

Your help will be highly appreciated.

Thanks
Ram

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Evgeniy Kolmakov Nov 03, 2016 at 12:33 AM
0

Hi Ram!

Since BLOB data types are stored in DB as hexademical strings, first, you should convert your input payload into hex string. You can do it using Apache Commons Codec or using own java code, for example:

final protected static char[] hexArray = "0123456789ABCDEF".toCharArray();
public static String bytesToHex(byte[] bytes) {
char[] hexChars = new char[bytes.length * 2];
for ( int j = 0; j < bytes.length; j++ ) {
int v = bytes[j] & 0xFF;
hexChars[j * 2] = hexArray[v >>> 4];
hexChars[j * 2 + 1] = hexArray[v & 0x0F];
}
return new String(hexChars); }

Further you define JDBC document format for calling stored procedure:

<?xml version="1.0" encoding="UTF-8"?>

<MT_JDBC_Call_SP>

<Statement_SP>

<storedProcedureName action="EXECUTE">

<table>SP_name</table>

<Parameter_name type="VARBINARY">Hexademical payload representation</Parameter_name>

</storedProcedureName>

</Statement_SP>

</MT_JDBC_Call_SP>

Regards, Evgeniy.

Share
10 |10000 characters needed characters left characters exceeded