step by step to insert binary files(.xlsx, .doc, .pdf, .mp3, .mp4, .pgp) into database table column of type BLOB/VARBINARY using SAP PI/PO Java mapping

Hi All,

Requirement: I want to insert .xlsx files into database table column of type VARBINARY.

Step 1: For above requirement I have created a stored procedure in database with below parameters.

Step 2: To hold .xlsx file as input, I have taken string as a datatype in sender side.

Step 3: As per the stored procedure syntax we created target data type.

Step 4: I have implemented Java mapping to convert .xlsx file to hex format.

Note:  If we want to insert binary files into database column, first we need to convert binary file into hex format i.e, by using java mapping we can read .xlsx file as a binary then convert into hex format using below java code.

package com.mappingfilestream;
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import com.sap.aii.mapping.api.AbstractTransformation;
import com.sap.aii.mapping.api.DynamicConfiguration;
import com.sap.aii.mapping.api.DynamicConfigurationKey;
import com.sap.aii.mapping.api.StreamTransformationException;
import com.sap.aii.mapping.api.TransformationInput;
import com.sap.aii.mapping.api.TransformationOutput;

public class MapFileStream extends AbstractTransformation
{
private StringBuffer sb;
private String digits = “0123456789ABCDEF”;

@Override
public void transform(TransformationInput in, TransformationOutput out)
throws StreamTransformationException
{

sb = new StringBuffer();
BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(out.getOutputPayload().getOutputStream()));
InputStream is = in.getInputPayload().getInputStream();

// Access dynamic configuration for getting file name
DynamicConfigurationKey KEY_FILENAME =
DynamicConfigurationKey.create(“http://sap.com/xi/XI/System/File”,”FileName”);
DynamicConfiguration conf = in.getDynamicConfiguration();
String filename = conf.get(KEY_FILENAME);

// Open inbound XML message stream
sb.append(“<?xml version=\”1.0\” encoding=\”UTF-8\”?>”);
sb.append(“<ns0:Mt_file_In xmlns:ns0=\”http://file_insert.com\”>”);
sb.append(“<FILE><FILE_SP action=\”EXECUTE\”>\r\n”);
sb.append(“<TABLE>NWPS_SHIPPER_REPORT_INSERT</TABLE><FileName type=\”VARCHAR\”>”+filename+”</FileName>”);
sb.append(“<FileData type=\”VARBINARY\”>”);

// Read binary file as input stream and convert to hex
try
{
byte[] bytes = toByteArrayUsingJava(is);
for (int i = 0; i < bytes.length; i++)
{
if(i == 1)
{
sb.append(“50”);//Inserted “50” in the beginning of hexadecimal stream for Extra Byte
}
int c = bytes[i] & 0xFF;
sb.append(digits.charAt(c >> 4));
sb.append(digits.charAt(c & 0x0F));
}
sb.append(“</FileData></FILE_SP><FILE></ns0:Mt_file_In>”);
bw.write(sb.toString());
bw.flush();
bw.close();
}
catch (IOException e)
{
e.printStackTrace();
}
}
/*
* Read bytes from inputStream and writes to OutputStream,
* later converts OutputStream to byte array in Java.
*/
public byte[] toByteArrayUsingJava(InputStream is) throws IOException{
ByteArrayOutputStream baos = new ByteArrayOutputStream();
int reads = is.read();

while(reads != -1){
baos.write(reads);
reads = is.read();
}

return baos.toByteArray();
}
}

Step 5: Convert .java file into .jar and import into PI mapping.

Step 6: Create operation mapping by using java class.

Hope this was useful for any type of binary file insert into database table column.

Thank you,
Narasaiah T