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

[former_member662815]

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

[former_member190293]

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.