The SAP Java Connector (JCo) and SQL
Wouldn't it be nice if you could use SQL operations on BAPI tables? Here's how to do it!
Most BAPIs use table parameters. And the easiest way to work with tables is SQL. Wouldn't it be nice if you could...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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(); } }