OpenSQL

from Wikipedia, the free encyclopedia

Open SQL is a proprietary SQL derivative that originates from SAP . SAP intends to offer a uniform SQL dialect for all databases supported by SAP. Open SQL is part of the ABAP programming language . But there is also a (less important) version for Java . The use of Open SQL requires a SAP ABAP or Java Application Server. In addition, all database tables used must be created in SAP's own dictionary. The aim of Open SQL is that the SQL commands used deliver the same result on all databases. For this purpose, the respective application server contains its own SQL parser and a converter that converts the Open SQL command into the respective native database dialect.

Functionality and scope

In newer versions, Open SQL for ABAP contains large parts of the SQL-92 standard with many extensions. For example, arithmetic expressions, CASE ... ENDCASE, some SQL functions and common table expressions are supported. Through emulation within the application server, functions can also be offered that are not supported by all databases supported by SAP. An example here are e.g. B. Streams and locators with which large LOB fields in the database can be read and written section by section.

The scope of Open SQL for Java is much smaller.

particularities

  • Seamless integration into the SAP (ABAP) type system, regardless of the type system of the database.
  • Static syntax check at compile time (in contrast to other database interfaces such as JDBC ).
  • Dynamic and semi-dynamic SQL (parts of the SQL statement can be transferred dynamically at runtime. The static parts can still be checked at compile time).
  • SAP own extensions, e.g. B. FOR ALL ENTRIES (join of database tables with so-called internal tables, i.e. variables).
  • Tables can be buffered in the application server and then read using Open SQL without database access. For this purpose, the application server contains a ( in memory ) database engine (with a greatly reduced scope).

Supported databases

Current SAP releases support SAP HANA , Oracle , MaxDB , DB2 , Microsoft SQL Server and Sybase ASE. Historically, Informix was also supported.

Native SQL

SAP uses Native SQL to denote the SQL dialects of the underlying databases. An example would be the SQL of the Oracle database, which, like DB2 or MaxDB, has a different language scope than Open SQL. The ABAP runtime environment then converts the Open SQL commands into the corresponding Native SQL commands. Using ADBC or JDBC or AMDP (computer science) (ABAP only), ABAP or Java Native SQL commands can also be sent directly.

keywords

The language set of Open SQL consists of a subset of the key words of standard SQL , which has been enhanced with some SAP-specific elements. The following keywords can be used in Open SQL:

  • SELECT, ENDSELECT
  • INSERT
  • MODIFY
  • UPDATE
  • DELETE
  • OPEN CURSOR, CLOSE CURSOR
  • FETCH
  • WITH (since ABAP Kernel 749)

Open SQL does not contain any commands from the Data Definition Language . Tables or views cannot be created or changed with Open SQL methods. This is only possible using the ABAP or Java Dictionary.

System variables

Two system variables that are used to return values ​​are supported by the Open SQL commands and are part of the Open SQL language set:

SY-SUBRC
Return value of the last command called.
SY-DBCNT
Number of data records that were processed by the last SQL command called.

Web links