SAP XI/PI: Storing BINARIES (Images, PDFs etc.) in the Database (BLOBs) Using JDBC Adapter

[former_member181985]

            Recently I have seen a thread in SAP Forum Picture to JDBC adapter, asking for how to store image in the DB using SAP XI/PI. Though I never tried this case, but I gave the solution as per SAP help. Later I searched forum for the same case (threads & blogs), but to my surprise till now there is no blog in SDN on this topic. 

            Then, I thought of giving a try, using JDBC INSERT statement (I defined a table on ORACLE DB which has a field with type BLOB) by converting the input binary stream into hexadecimal encoded string and then passing it to the BLOB field in the INSERT structure. However, the receiver JDBC channel always throws "ORA-00972 identifier too long error". Later I realized that in SAP help, BLOB type field is given for Stored Procedure format. I created a simple stored procedure which has an input field of BLOB type and which internally calls INSERT statement on the previously created table by passing stored procedure input variable as value.

            Now this time, I tried to call the DB using stored procedure format and the call was successful without any errors. But, however in the DB 1 byte of data (first byte) is always missing in the table BLOB field data. This I realized when I extracted the BLOB data from DB using some Java Program (tried with XI sender JDBC channel as well) and when I tried to save the data with its corresponding extension (e.g., jpg, pdf etc), the file is not getting opened by its associated application (Image Viewer, Acrobat Reader)

            I tried with other different BINARIES such as PDF, Microsoft word docs and even for them also it is the same case i.e., always 1 byte of data (first byte) missing in the DB. After some investigation I realized that this might be happening when JDBC adapter is calling the DB (May be my understanding is wrong, please give your valuable comments on this). So, I hard coded 1 extra byte in the beginning of data stream before pushing it to the JDBC and to my surprise this logic worked. The data is getting stored in the BLOB filed properly. I tried with some other binary docs; even for them also it worked.

Scenario: File to JDBC

Java Mapping Code (Converting Binary Input Binary Stream to Hexadecimal and then forming the Stored Procedure Document Format for JDBC call)

import com.sap.aii.mapping.api.StreamTransformation;

import com.sap.aii.mapping.api.*;

import java.io.*;

import java.util.zip.*;

import java.util.*;

import java.text.*;

public class BinaryToHexDecimalEncodingJDBCBLOB implements StreamTransformation{

    //Hexadecimal characters corresponding to each half byte value.

    private static final char[] HexChars = {

    '0', '1', '2', '3', '4', '5', '6', '7',

    '8', '9', 'a', 'b', 'c', 'd', 'e', 'f'

    };

    public static final String toHexString(byte[] bytes)

    {

        StringBuffer sb = new StringBuffer();

        int i;

        for (i=0; i < bytes.length; i++)    

        {

            sb.append( HexChars[( bytes[i] & 0xf0 ) >>> 4] );

            sb.append( HexChars[bytes[i] & 0x0f] );

            //sb.append(HexChars[(bytes[i] >> 4) & 0xf]);

            //sb.append(HexChars[bytes[i] & 0xf]);

        }

        return sb.toString();

    }

    public void setParameter (Map map)

    {

        param = map;

        if (param == null)

        {

            param = new HashMap();

        }

    }

    public static void main(String args[])

    {

        BinaryToHexDecimalEncodingJDBCBLOB con = new BinaryToHexDecimalEncodingJDBCBLOB();

        try

        {

            InputStream is = new FileInputStream(args[0]);

            OutputStream os = new FileOutputStream(args[1]);

            con.execute(is, os);           

        }

        catch (Exception e)

        {

            e.printStackTrace();

        }

    }

    public void execute(InputStream inputstream, OutputStream outputstream)

    {

        try

        {

            while ((len = inputstream.read(buffer)) > 0)

                baos.write(buffer, 0, len);

            hexDecStr = toHexString(baos.toByteArray());   

            //BufferedWriter bw = new BufferedWriter(new FileWriter("Image.txt"));

            //bw.write(hexDecStr);

            //bw.close();

            outputstream.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><ns0:MT_JDBC_INSERT xmlns:ns0=\"urn:Flextronics.com/JDBCBLOBDemo\"><STATEMENT_SP><storedProcedureName action=\"EXECUTE\"><table>TESTSP</table><inputblob type=\"BLOB\">".getBytes());

             outputstream.write( ("aa" + hexDecStr).getBytes() );    //Inserted "aa" in the begining of hexadecimal stream for Extra Byte   

            outputstream.write("</inputblob></storedProcedureName></STATEMENT_SP></ns0:MT_JDBC_INSERT>".getBytes());           

        }

        catch (Exception e)

        {

            e.printStackTrace();

        }

    }

    byte[] buffer = new byte[1024*5000];

    ByteArrayOutputStream baos = new ByteArrayOutputStream();

    private Map param;

    int len;

    String hexDecStr = null;

}

Stored Procedure XML

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

<ns0:MT_JDBC_INSERT xmlns:ns0="urn:Flextronics.com/JDBCBLOBDemo">

    <STATEMENT_SP>

        <storedProcedureName action="EXECUTE">

            <table>TESTSP</table>

            <inputblob type="BLOB">HexDecimalBinaryString</inputblob>

        </storedProcedureName>

    </STATEMENT_SP>

</ns0:MT_JDBC_INSERT>

I used Oracle Database 10g Express Edition (Universal), version 10.2.0.1.0

JDBC table

JDBC Stored Procedure

Java Program for reading BLOB object and storing it to a file system

import java.io.File;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

public class ReadBLOBFromDATABASE

{

      static String url = "jdbc:oracle:thin:@localhost:1521:XE";

      static String username = "<USERNAME>";

      static String password = "<PASSWORD>";

      public static void main(String[] args) throws Exception

        {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        Connection conn = DriverManager.getConnection(url, username, password);

        String sql = "SELECT BLOBFIELD FROM JDBCBLOB";

        PreparedStatement stmt = conn.prepareStatement(sql);

        ResultSet resultSet = stmt.executeQuery();

        while (resultSet.next()) {

          //File image = new File("java.jpg");

         

          FileOutputStream fos = new FileOutputStream(new File(args[0]));

          byte[] buffer = new byte[1];

          InputStream is = resultSet.getBinaryStream(1);

          while (is.read(buffer) > 0) {

            fos.write(buffer);

          }

          fos.close();

        }

        conn.close();

      }

}

For compiling and executing the above program we need classes12.jar (http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html

) in the CLASSPATH.

We can also do the same thing by configuring a scenario in XI/PI (JDBC to File) by reading the JDBC table. (Remember convert hexadecimal string to binary stream before sending it to File Adapter, we need a java mapping program for this with the below code snippet)

public static byte[] hexStringToByteArray(String s) {

            int len = s.length();

            byte[] data = new byte[len / 2];

            for (int i = 0; i < len; i += 2) {

                        data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)

                                                                                     + Character.digit(s.charAt(i+1), 16));

            }

            return data;

}