The SAP Java Connector (JCo) and SQL

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 [email protected]) 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
 * [email protected]
 * 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();
  }
}