Presenting SAP R / 3 data in Oracle Database using the SAP Java Connector

The next time there was a need to link the two systems known to each other, now it will be Oracle Database and SAP. There may be paid binding methods, but in this case we are talking about the need to use small chunks of data.

I’ll talk about how you can display data taken from SAP using the select statement. An example will be very simple, to demonstrate the fundamental possibility. It was created on the basis of the accompanying SAP JCo or publicly available sources.

I note right away that SAP JCo is issued only to those who pay. And Oracle Database does not welcome java calls using binary libraries, therefore, by default, this feature should be specifically enabled.

Required types for a table and its rows in Oracle Database, sap_table.tps:

drop type sap_rows;
/
drop type sap_row;
/
create or replace type sap_row as object(p1 varchar(30), p2 varchar(30), p3 varchar(30), p4 varchar(30), p5 varchar(30), dt varchar(20))
/
create or replace type sap_rows as table of sap_row
/

The main component in which the process of obtaining data from SAP and preparing for use in select, art0int_sap.java:

package com.art0int;
import java.util.*;
import java.io.*;
import java.math.*;
import java.util.Calendar;
import java.text.SimpleDateFormat;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.OracleDriver;
import java.util.HashMap;
import java.util.Properties;
import com.sap.conn.jco.AbapException;
import com.sap.conn.jco.JCoContext;
import com.sap.conn.jco.JCoDestination;
import com.sap.conn.jco.JCoDestinationManager;
import com.sap.conn.jco.JCoException;
import com.sap.conn.jco.JCoField;
import com.sap.conn.jco.JCoFunction;
import com.sap.conn.jco.JCoFunctionTemplate;
import com.sap.conn.jco.JCoStructure;
import com.sap.conn.jco.JCoTable;
import com.sap.conn.jco.ext.DataProviderException;
import com.sap.conn.jco.ext.DestinationDataEventListener;
import com.sap.conn.jco.ext.DestinationDataProvider;
public class SAP_TABLE
{
//массив для наполнения данными
    static Vector vrows;
//здесь мы будем хранить структуру, обеспечивающую доступ к данным SAP
    static MyDestinationDataProvider myProvider = null;
//собственно, этот метод и отправляет полученные из SAP данные для использования оператором select
    public static oracle.sql.ARRAY SQL_sap_rows (BigDecimal nrows) throws SQLException {
        Connection conn = new OracleDriver().defaultConnection();
//пользуемся созданными типами для таблицы и ее строк
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("SAP_ROWS", conn );
        StructDescriptor outDesc = StructDescriptor.createDescriptor("SAP_ROW", conn);
        int nrowsval = nrows.intValue();
        vrows = new Vector(nrowsval);
        Object[] out_attr = new Object[6];
        try {
	    if (myProvider == null) {
		myProvider = new MyDestinationDataProvider();
		try {
		    com.sap.conn.jco.ext.Environment.registerDestinationDataProvider(myProvider);
		} catch(IllegalStateException providerAlreadyRegisteredException) {
		    throw new Error(providerAlreadyRegisteredException);
		}
            }
//подготавливаем соединение с SAP
            String destName = "ABAP_AS";
            myProvider.changeProperties(destName, getDestinationPropertiesFromUI());
            JCoDestination dest;
            try {
//устанавливаем параметры соединения
                dest = JCoDestinationManager.getDestination(destName);
//проверяем доступность системы
                dest.ping();
                System.out.println("Destination " + destName + " works");
		JCoFunction function = dest.getRepository().getFunction("MY_SAPFUNCTION");
		try {
//получаем данные
		    function.execute(dest);
		} catch(AbapException e) {
		    System.out.println(e.toString());
		    return null;
		}
//заполняем массив
		JCoTable out =  function.getTableParameterList().getTable(0);
		for (int i = 0; i < out.getNumRows(); i++) {
		    out.setRow(i);
		    out_attr[1-1] = out.getString(1-1);
		    out_attr[2-1] = out.getString(2-1);
		    out_attr[3-1] = out.getString(3-1);
		    out_attr[4-1] = out.getString(4-1);
		    out_attr[5-1] = out.getString(5-1);
		    out_attr[6-1] = (Object)new String(now());
		    vrows.add((Object)new STRUCT(outDesc, conn, out_attr));
                    nrowsval--;
                    if (nrowsval==0) break;
		}
            } catch(JCoException e) {
                e.printStackTrace();
                System.out.println("Execution on destination " + destName+ " failed");
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
//отдаем массив для обработки в качестве таблицы Oracle        
        oracle.sql.ARRAY outArray = new oracle.sql.ARRAY(descriptor,conn,vrows.toArray());
        return outArray;
    }
//этот метод подаёт параметры подключения к SAP
    static Properties getDestinationPropertiesFromUI() {
        Properties connectProperties = new Properties();
        connectProperties.setProperty(DestinationDataProvider.JCO_R3NAME, "MY_R3NAME");
        connectProperties.setProperty(DestinationDataProvider.JCO_ASHOST, "MY_IP");
        connectProperties.setProperty(DestinationDataProvider.JCO_CLIENT, "MY_CLIENTNO");
        connectProperties.setProperty(DestinationDataProvider.JCO_USER,   "MY_SAPUSER");
        connectProperties.setProperty(DestinationDataProvider.JCO_PASSWD, "MY_SAPPASSWORD");
        connectProperties.setProperty(DestinationDataProvider.JCO_SYSNR,  "MY_SYSNR");
        connectProperties.setProperty(DestinationDataProvider.JCO_LANG,   "en");
        return connectProperties;
    }
//класс используется как поставщик данных SAP
    static class MyDestinationDataProvider implements DestinationDataProvider
    {
        private DestinationDataEventListener eL;
        private HashMap secureDBStorage = new HashMap();
        public Properties getDestinationProperties(String destinationName)
        {
            try
            {
                Properties p = secureDBStorage.get(destinationName);
                if(p!=null)
                {
                    if(p.isEmpty())
                        throw new DataProviderException(DataProviderException.Reason.INVALID_CONFIGURATION, "destination configuration is incorrect", null);
                    return p;
                }
                return null;
            }
            catch(RuntimeException re)
            {
                throw new DataProviderException(DataProviderException.Reason.INTERNAL_ERROR, re);
            }
        }
        public void setDestinationDataEventListener(DestinationDataEventListener eventListener)
        {
            this.eL = eventListener;
        }
        public boolean supportsEvents()
        {
            return true;
        }
        void changeProperties(String destName, Properties properties)
        {
            synchronized(secureDBStorage)
            {
                if(properties==null)
                {
                    if(secureDBStorage.remove(destName)!=null)
                        eL.deleted(destName);
                }
                else
                {
                    secureDBStorage.put(destName, properties);
                    eL.updated(destName);
                }
            }
        }
    }
//вспомогательные методы, они просто могут пригодиться
    public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";
    public static String now() {
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
        return sdf.format(cal.getTime());
    }
}

Wrap to load java source in Oracle Database, sap_table.js:

create or replace and compile java source named sap_table as
@art0int_sap.java
/

Wrappers for java calls from Oracle Database, SAP_TABLE.spc:

create or replace package SAP_TABLE is
function get_java_property(prop in varchar2)
    return varchar2 is
    language java name 'java.lang.System.getProperty(java.lang.String) return java.lang.String';
function sap_rows_table(nrows in number)
    return sap_rows
    IS LANGUAGE JAVA
    name 'com.art0int.SAP_TABLE.SQL_sap_rows(java.math.BigDecimal) return oracle.sql.ARRAY';
end SAP_TABLE;
/

And the final script that will put everything together. Pay attention to the granting of rights to call the sapjco3 library.

export ORACLE_SID=MYDB
sqlplus '/ as sysdba' <


Результат Вы увидите, если положите библиотеки SAP Java Connector соответствующий java.library.path каталог, который отобразится при первом вызове скрипта.