SapJdbc Driver

Introduction

The SAP JDBC driver enables reading data from SAP systems via RFC function modules. The functionality corresponds to the read part of DsModSAP.

Metadata (table list, columns) is read from the SAP Data Dictionary tables DD02L and DD03L. The COLUMNS clause is therefore optional.

The following conventions apply:

  • the connection jdbc:sap: is used as the database
  • the URL contains the SAP connection parameters: host:systemNr:client:language
  • the schema is the function module (e.g. RFC_READ_TABLE, ZRFC_READ_TABLE)
  • the table is the SAP table name (e.g. MARA, KNA1)
  • COLUMNS clause is optional – if missing, columns are read from DD03L
  • the FILTER clause contains native SAP/ABAP syntax and is passed as OPTIONS to the RFC

Supported Function Modules

  • RFC_READ_TABLE
  • ZRFC_READ_TABLE
  • ZRFC_READ_TABLE_TEXT
  • ZRFC_READ_TABLE_BSAD (OPTIONS_BKPF for filter)
  • ZRFC_READ_TABLE_BSEG (OPTIONS_BKPF for filter)

Connection

The URL is specified when establishing the connection.

URL format: jdbc:sap:host:systemNr:client:language

Example: jdbc:sap:sapserver.example.com:00:800:de

Properties:

  • user – SAP username
  • password – SAP password
  • destFile – Path to a JCo destination file (format like .jcoDestination); overrides URL and user/password when set
  • sapVerifyRfc – when true: RFC_PING directly after connect (optional)
  • sapColumnSource – Column source: DD03L (default) = DD03L like DsModSAP, fully including .INCLU--AP; RFC = RFC_READ_TABLE NO_DATA (simplified structure)

Connection with user/password:

connect 'jdbc:sap:sapserver.example.com:00:800:de|user=myuser,password=secret'

Connection with destFile:

Instead of user/password, a JCo destination file can be used. The file contains the connection parameters in JCo format:

jco.client.ashost=<host>
jco.client.sysnr=<systemNr>
jco.client.client=<client>
jco.client.user=<user>
jco.client.passwd=<password>
jco.client.lang=<language>

Example:

connect 'jdbc:sap:localhost:00:003:DE|destFile=DEV_SAP.jcoDestination'

The file is searched: first as a file (relative to the working directory or absolute), then in the classpath. Typically it is located in the working directory of the program.

Example Query

First open the connection, then execute the query. In the FROM clause, schema (function module) and table (SAP table name) are specified.

Without COLUMNS (columns from DD03L):

SELECT MATNR, MTART, MEINS
FROM "RFC_READ_TABLE"."MARA"
FILTER rownumber <= 10
;

With COLUMNS (backward compatibility):

SELECT MATNR, MTART, MEINS
FROM "RFC_READ_TABLE"."MARA"
COLUMNS(MATNR, MTART, MEINS)
FILTER rownumber <= 10
;

System Tables

The following virtual tables are available via the schema system:

Table Source Columns
table_list DD02L + DD02T table_cat, table_schem, table_name, table_type, remarks, type_cat, type_schem, type_name, self_referencing_col_name, ref_generation
column_list DD03L / NO_DATA table_cat, table_schem, table_name, column_name, data_type, type_name, column_size, buffer_length, decimal_digits, num_prec_radix, nullable, remarks, column_def, sql_data_type, sql_datetime_sub, char_octet_length, ordinal_position, is_nullable, scope_catalog, scope_schema, scope_table, source_data_type, is_autoincrement, …
primary_key_list DD03L KEYFLAG table_cat, table_schem, table_name, column_name, key_seq, pk_name

The language for Remarks in table_list comes from the connection (URL or jco.client.lang in destFile).

FILTER for system tables: The fields table_schem, table_name and column_name (only for column_list) are passed as patterns to getTables/getColumns/getPrimaryKeys – thereby fewer metadata is loaded.

Syntax: table_schem = 'X', table_name = 'MARA' or ABAP-style table_name EQ 'MARA'. SQL wildcards (%, _) are allowed in the values (e.g. table_name = 'MA%').

SELECT table_schem, table_name, table_type, remarks
FROM "system"."table_list"
WHERE rownumber <= 20
;

-- FILTER: only tables of schema RFC_READ_TABLE
SELECT * FROM "system"."table_list"
FILTER table_schem = 'RFC_READ_TABLE'
;

-- FILTER: load only columns of table MARA (faster)
SELECT * FROM "system"."column_list"
FILTER table_name = 'MARA'
;

-- FILTER: table_schem and table_name
SELECT * FROM "system"."column_list"
FILTER table_schem = 'RFC_READ_TABLE' AND table_name = 'MARA'
;

-- FILTER: column_name for column_list (e.g. only MATNR)
SELECT * FROM "system"."column_list"
FILTER table_name = 'MARA' AND column_name = 'MATNR'
;

With FILTER (Native SAP Syntax)

The FILTER clause contains native SAP/ABAP Open SQL syntax – analogous to Salesforce (SOQL) or Eloqua. The content is passed unchanged as OPTIONS to the RFC.

Passing to SAP:

  • The FILTER content is written line by line to the OPTIONS table of the RFC (max. 72 characters per line)
  • Each line corresponds to a WHERE condition fragment in ABAP syntax
  • Multi-line OPTIONS are allowed

Special case rownumber:

  • rownumber <= N or rownumber < N is removed from the FILTER and passed as ROWCOUNT to the RFC
  • Thereby the row count is limited on the server side (without reading all rows)

Examples:

-- Row limit only (ROWCOUNT)
SELECT BUKRS, BUTXT FROM "RFC_READ_TABLE"."T001"
COLUMNS(BUKRS, BUTXT)
FILTER rownumber <= 5
;

-- Native ABAP WHERE condition
SELECT MATNR, MTART, MEINS FROM "RFC_READ_TABLE"."MARA"
COLUMNS(MATNR, MTART, MEINS)
FILTER MTART = 'FERT'
;

-- Combination
SELECT MATNR, MTART FROM "RFC_READ_TABLE"."MARA"
COLUMNS(MATNR, MTART)
FILTER MTART = 'FERT' AND rownumber <= 100
;

Use in Java Programs

A standalone Java program needs the driver, SAP JCo and the native JCo library. The native library (e.g. libsapjco3.so, sapjco3.dll or libsapjco3.jnilib) comes with the SAP JCo package and must be found via java.library.path.

Example – driver and JCo in a directory lib/ (sapjdbc.jar, sapjco3.jar, native library):

java -Djava.library.path=lib -cp ".:lib/*" YourMainClass

Example – in project root:

java -Djava.library.path=. -cp ".:sapjdbc.jar:sapjco3.jar" YourMainClass

The native library must be in the java.library.path (e.g. lib/). Platform-specific:

  • Linux: libsapjco3.so
  • Windows: sapjco3.dll
  • macOS: libsapjco3.jnilib or libsapjco3.dylib

On Windows separate the classpath with ; instead of :.

The complete SAP JCo library is available from the SAP Software Download Center (SAP Connector for Microsoft .NET and Java).