The Standard Path to Persistence 

Access the Data Base Independently of Tables: Open SQL

Whenever you wish to read data from the database or write data to the database, the best choice is SQL (Structured Query Language). This language has been in use since 1987 as the standard language for querying relational databases and is also the language accepted as the ISO standard. You can integrate SQL statements into the ABAP program. Contrary to what you are used to from programming with Java, you do not need to use a manager to start a connection with a database and then return or close it in a pool.

Based on the architecture of the SAP system, you always have an open connection to the integrated database at your disposal. Here the SAP NW Application Server supports the standard databases beyond normal levels. This means that you can program database accesses independently of the manufacture type and do not have to write different code for the different databases. This would involve a lot of effort and would require code modifications in the case of porting.

With the SAP NW AS, you can program your database accesses in the Open SQL of SAP – independently of the proprietary specifications of the database your system works with.  This is not always to be taken for granted. SQL is the standard language for querying relational databases.  But, in effect, each database producer uses its own proprietary variant of SQL that works with different manufacturer-dependent SQL statements.  

One important service of the SAP NW AS is that you, as a programmer, do not have to take care of these differences and your ABAP program can still work with the databases of different producers. The SAP NW AS uses Open SQL. This is a subset of the manufacturer-dependent SQL statements, plus other statements that go beyond the standard range of manufacturer-dependent SQL commands. 

Indeed, the database interface hides all the manufacturer-dependent details from you. You write all SQL statements in one language – irrespective of which database the system is connected to. You enter statements in Open SQL simply as lines in your program so that the syntax of these lines can be checked already at compiling time.  You can write the results of the queries into data objects of the ABAP program and, conversely, you can write the values of these data objects to database tables.

Declaring Database Tables on a Metalanguage Basis: The ABAP Dictionary

So far, we only talked about the fact that you can read and write data using Open SQL.  You will rightly ask what command you can use to create and manage database tables.  Naturally, you can create a database table using the SAP NW Application Server.  But you do not do this with code within your program, but declaratively.

In the ABAP Dictionary, you define database tables and define the properties you need.

·        Do you wish to have the row type of the table so that it can be enhanced or not? Or in other words: Should it be possible later on to add further fields to the row type?

·        Which size should the database table have? This actually means how many data records are to be stored there later on?

·        Is the database table to be buffered, and if so, in which way?

Here you must revert to the built-in database types of the Dictionary for the types of the individual database fields or, when reverting to these types, you define yourself your own more complex semantic types. You wish to create a table for address data, for example.  For this, you require – in the first step – the fields STREET, HOUSE_NUMBER, CITY, ZIP CODE, and so on. You must declare these also as data fields in the ABAP Dictionary.

So you enter the metadata of the fields and the tables you require in the ABAP Dictionary. As soon as you activate the table and the fields, the table is created in the database and the fields are available system-wide. The advantages of this approach – crate data objects as declarations – are very clear.

·        The database tables are available for transports to another system as soon as the respective, transported ABAP Dictionary objects are activated.  If you were to declare the tables in the program it would require more effort and would be more awkward to determine the time as of which they are available in another system.

·        The declarative options of the ABAP Dictionary provide you with features that reach beyond the options available in SQL in this regard.

·        In the case of SQL queries, the system can easily check whether the queried tables exist.

·        In addition, that part of SQL with which you create and manage tables is implemented by the respective database manufacturers in a more proprietary fashion than that part of the language with which you write and read data.

Editing Mass Data with Good Performance – Open SQL and Internal Tables

Occasionally, you will require only one particular data record.  Or you wish to read a certain set of data with certain properties from the database and edit this further in your program.  In this case, you can see how ABAP is ideally optimized for the requirements of business programming.  You can write the entire result set of an SQL statement into one data object of the internal table type.

An internal table – you must remember – has important properties in common with database tables. It is define by its row type and the key. In contrast to database tables, internal tables – as the name suggests – are located in internal mode of the respective ABAP program.  The third defining feature is the table type, which defines whether the table is sorted, unsorted, or a Hash table. The fact that they are dynamic data objects makes internal tables so practical for the user.  The number of rows adapts itself dynamically and is not defined in the declaration.

The internal table, therefore, should be your first choice for the set of data selected by your SELECT statement or for the data you want to write to the database. In particular, you should remember that you can use internal tables not only for looping – as you are used to in collections in Java – but you can also use ABAP commands to search in internal tables for data records that meet certain requirements.  Using internal tables you hold the individual business objects of the same type in an internal table with this row type.