The SAP Java Connector (JCo) and SQL

Most BAPIs use table parameters. And the easiest way to work with tables is SQL. Wouldn't it be nice if you could...

use SQL operations on BAPI tables? But how? From time to time I have been looking for a pure Java database engine that supports in-memory databases. Finally, I have found one. It is called "hsql Database Engine (hsqldb)", is open source, and can be downloaded free from http://hsqldb.sourceforge.net/. It did not take me long to add support for hsqldb to the ARAsoft JCo Extension Library (trial version available via e-mail to tgs@arasoft.de) and you should be able to use it in your projects with similar ease. The following is the source code for a demo program that shows how easy it is to join information from different tables using SQL.

package de.arasoft.demo.jco;
 
import java.sql.*;
import com.sap.mw.jco.*;
import de.arasoft.sap.interfacing.*;
import de.arasoft.sap.jco.*;
 
/**
 *
 * SQL sample program.
 * Uses the pure Java hsql Database Engine (hsqldb) which you can download from http://hsqldb.sourceforge.net/
 * This engine allows in-memory databases.
 * Do not forget to include the hsqldb.jar file in your classpath.
 *
 * http://www.arasoft.de
 * tgs@arasoft.de
 * Copyright (c) 2002 ARAsoft GmbH
 *
 * @author Thomas G. Schuessler, ARAsoft GmbH
 * @version 1.0
 */
 
public class SqlDemo extends Object {
 
  JCO.Client mConnection;
  IRepository mRepository;
 
  Connection dbConnection;
  Statement  sStatement;
 
  public SqlDemo() {
    try {
      // Change the logon information to your own system/user
      mConnection =
        JCO.createClient(
          "001",                // SAP client
          "<userid>",           // userid
          "****",               // password
          null,                 // language
          "<hostname>",         // application server host name
          "00");                // system number
 
      mConnection.connect();
      mRepository = new JCoRepository(mConnection);
 
      JCoComponentConnector jcc = new JCoComponentConnector(mConnection, mRepository);
      ObjectFactory bof = new ObjectFactory(jcc);
 
// Instantiate DB driver
      Class.forName("org.hsqldb.jdbcDriver").newInstance();
 
      String url = "jdbc:hsqldb:";
      String database = ".";
      String user = "sa";
      String password = "";
 
// Create in-memory DB
      dbConnection = DriverManager.getConnection(url + database, user, password);
 
// Find all employees whose last name starts with "MU"
      JCO.Function function = jcc.createFunction("BAPI_EMPLOYEE_GETDATA");
      function.getImportParameterList().setValue("MU*", "LASTNAME_M");
      jcc.executeStateless(function);
      JCO.Table table = function.getTableParameterList().getTable("PERSONAL_DATA");
// Create an SQL table for the PERSONAL_DATA table of the BAPI
      JCoUtilities.createSqlTableFromJCoTable(dbConnection, "PersonalData", table);
 
// Retrieve the languages and create an SQL table for them
      Languages languages = bof.getSapData().getLanguages();
      languages.createSqlTable(dbConnection, "Languages");
 
// Retrieve last name, first name, and the name of the language defined for the employee
      sStatement = dbConnection.createStatement();
      String stmt = "SELECT PersonalData.LAST_NAME, PersonalData.FIRSTNAME, Languages.Description FROM PersonalData " +
                    "INNER JOIN Languages ON PersonalData.LANGU = Languages.InternalCode;";
      ResultSet rs = sStatement.executeQuery(stmt);
 
// Print the column names
      String s = "";
      for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
        s = s + rs.getMetaData().getColumnName(i) + "t";
      }
      System.out.println(s);
 
// Print the result set data
      while (rs.next()) {
        s = "";
        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
          s = s + rs.getString(i) + "t";
        }
        System.out.println(s );
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      mConnection.disconnect();
    }
  }
 
  public static void main (String args[]) {
    SqlDemo app = new SqlDemo();
  }
}