Skip to Content

            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

JDBCBLOB Table.JPG


JDBC Stored Procedure

TESTSP StoredProcedure.JPG

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;
}
To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

    1. Jan K

      Thanks Praveen for your contribution.

      Could you please advice how we can use this to retrieve pdf/image from database using JDBC adapter.

      Thanks.

      (0) 
      1. Praveen Gujjeti Post author

        Hi Jan,

        Although I haven’t tried your case requirement practically, but Select BlobField from TABLE should work in sender JDBC channel. And then use the method code in the blog, public static byte[] hexStringToByteArray(String s) to read row field structure hexadecimal string content and convert to Byte array and then write this stream to a file to realize pdf/image


        Regards,

        Praveen Gujjeti

        (0) 
        1. Rebecca Alice

          Dear Praveen,

          Could you please suggest on synchronous scenario with ABAP Proxy as sender via SAP PO to JDBC receiver as response? I want to pick the image from Oracle database..

          I want to read the blob object and in store in a ABAP table..

          Regards

          Rebecca……

          (0) 
          1. Praveen Gujjeti Post author

            Hi Rebecca,

            Please check my reply to Jan K, it should help you

            Just to add, I think you can either use a select query directly or a stored procedure with select query

            Best Regards,

            Praveen Gujjeti

            (0) 
  1. Rodrigo Scoralick

    Hi Praveen,

      Can you explain in more details the scenario XI/PI (JDBC to File) forreading the JDBC table? I created this scerario but when I send the jpeg to File Adapter Receiver it write a text file with binary content.

    Best Regards

    Rodrigo Scoralick

    (0) 
    1. Praveen Gujjeti Post author

      Hi Rodrigo,

      Sorry for my late response

      I hope you have converted hex string data from JDBC to binary data using below method

      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; 

      }

      Also you should correct file extension e.g., .jpeg for the filename so that you can open the file with the OS associated picture tool

      Best Regards,

      Praveen

      (0) 
  2. viraj sai

    Hi Praveen,

    I have JDBC(oracle) to JDBC(SQL server) Interface where we need to fetch around 10 fields from source oracle database and insert into target sql server database table.

    One of 10 fields is blob type on source side and Varbinary on target side, Do we still need to go with JAVA mapping or direct mapping with Convert function on receiver side works ??

    Thanks,

    Viraj

    (0) 

Leave a Reply