Database Access using Advanced Business Application Programming (ABAP)
 
Published: 22 Feb 2007
Abstract
This article outlines the concept of database access using ABAP.
by Arindam Ghosh
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 39904/ 96

Introduction

Accessing the Database in the R/3 System is performed through the interface provided by the SAP System, which in the SAP System is referred to as the Database Interface. A user can access the database from his/her program through Open SQL and Native SQL depending upon the circumstances.

About SQL

The Structured Query Language (SQL) is a largely standardized language, which is used for accessing relational databases. It can be divided as follows:

Data Manipulation Language (DML)

These statements are for reading and changing data in database tables.

Data Definition Language (DDL)

These statements are for creating and administering database tables.

Data Control Language (DCL)

These statements are used for authorization and consistency checks.

Here, each database has a programming interface. This programming interface allows the user to access the database tables by using SQL statements. But, these SQL statements in the programming interfaces are not fully standardized. So, you must refer to the documentation of that system for a list of the SQL statements available and also their correct syntax in order to access a specific database system.

Database Interface

Each work process on an application server must have a database interface if you want to make the R/3 system independent of the database system, and also to use it correctly despite the differences in the SQL syntax between various databases. By means of this interface only, the R/3 system can communicate with the database. All of the database requests from the R/3 system are converted into the correct Standard SQL statements for the database system by the database interface. In order to perform this function, it has to use a database-specific component, which shields the differences between database systems from the rest of the database interface. You have to choose the appropriate layer when installing the R/3 system. A user can access a database from a program through Open SQL and Native SQL.

Open SQL

Open SQL are statements that make up a subset of Standard SQL which is fully integrated in ABAP. Open SQL consists of Data Manipulation Language (DML) which is a part of Standard SQL.

One of the ways to access the database from a program is Open SQL. These Open SQL statements are nothing but a subset of Standard SQL, which is fully integrated in ABAP. Irrespective of which database system the R/3 installation is using, they allow you to access data. When I said that, Open SQL consists of the Data Manipulation Language (DML). I meant that it allows you to read (i.e. to SELECT) and change (i.e. to INSERT, UPDATE, DELETE) data.

Moreover, Open SQL also goes beyond Standard SQL. This is to provide statements that can simplify or speed up database access in conjunction with other ABAP constructions. Apart from that, it also gives you the freedom to buffer certain tables on the application server, thereby enabling you to save excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. As far as buffer storage is concerned, they may be stored in two parts: the working memory of the current work process, and the shared memory for all work processes on an application server. The data in the various buffers is synchronized at set intervals by buffer management where an R/3 system is distributed across more than one application server. It should be noted that data in the buffer is not always up to date when you are buffering the database. That is why you should only use the buffer for data which does not change often. You can specify whether a table can be buffered in its definition in the ABAP Dictionary.

Open SQL consists of a set of ABAP statements. These statements perform operations on the central database in the R/3 system. The results of the operations and any error messages which come out of it are independent of the current database system. Thus, uniform syntax and semantics for all of the database systems supported by SAP is provided by Open SQL. Regardless of the current database system, the ABAP programs, which use Open SQL statements only, will work in any R/3 system. Moreover, Open SQL statements work only with database tables that have been created in the ABAP Dictionary.

You have the freedom to combine columns belonging to different database tables to a database view (or view for short) in the ABAP Dictionary. Views are also handled in exactly the same way as database tables in Open SQL statements.

Some Open SQL keywords are as follows:

SELECT - It reads data from database tables.

INSERT - It adds rows to database tables.

UPDATE - It changes the contents of rows of database tables.

MODIFY - It inserts rows into database tables or changes the content of existing rows.

DELETE - It deletes rows from database tables.

OPEN CURSOR, FETCH, CLOSE CURSOR - It reads rows of database tables using the cursor.

Return Codes

The following two system fields are filled with return codes by all Open SQL statements:

SY-SUBRC: The system field SY -SUBRC contains the value 0 after every Open SQL statement if the operation was successful. When a value is other than 0, then it is unsuccessful.

SY-DBCNT: The system field SY-DBCNT contains the number of database lines processed after an open SQL statement.

Native SQL

The other possible way to access the database from a program is Native SQL. It is only loosely integrated into ABAP. It allows access to all of the functions contained in the programming interface of the respective database system. Native SQL statements are not checked and converted as compared to Open SQL statements. Unlike Open SQL, these are sent directly to the database system. The function of the database-dependent layer remains minimal when you use Native SQL. The Programs which use Native SQL are written specifically for a database system. You should avoid using Native SQL wherever possible when writing R/3 applications. However, you can use it in some parts of the R/3 Basis System, for instance, for creating or changing table definitions in the ABAP Dictionary.

Regardless of the database platform that your R/3 system is using, Open SQL allows you to access database tables, which are declared in the ABAP Dictionary. Native SQL allows you to use database specific SQL statements in an ABAP program. This means that you can use database tables that are not administered by the ABAP Dictionary. Aside from that, you can also integrate data that is not part of the R/3 system.

As a rule, an ABAP program that contains database-specific SQL statements will not run under different database systems. You have to use Open SQL statements only, if your program is used on more than one database platform.

You must proceed with the EXEC SQL statement, and follow the ENDEXEC statement to use a: Native SQL statement. For example

Listing 1

EXEC SQL [PERFORMING <form>].
      <native SQL, statement>
ENDEXEC.

There is no period after Native SQL statements. Also, using quotation marks (") or an asterisk (*) at the beginning of a native SQL statement's line does not introduce a comment as it would in normal ABAP syntax. You need to know if the table and field names are case-sensitive in your chosen database.

The data is transported between the database table and the ABAP program using host variables in Native SQL statements. These are preceded in a Native SQL statement by a colon (:) and are declared in the ABAP program. The elementary structures can be used as host variables. The structures of an INTO clause are treated exceptionally, as though all of their fields are listed individually. If the selection in a Native SQL SELECT statement is a table, then you can pass it to ABAP line by line using the PERFORMING addition. For each line read, the program calls a subroutine <form>. Further, you can process the data within the subroutine.

After the ENDEXEC statement, SY-DBCNT contains the number of lines processed as it does in Open SQL. In almost all cases, SY-SUBRC contains the value a after the ENDEXEC statement. Cursor operations form an exception: after FETCH, SY-SUBRC is 4 if no more records could be read. This is also applied when you read a result set using EXEC SQL PERFORMING.

Native SQL Scope

Native SQL is very important as it allows you to execute nearly all available statements through the SQL programming interface (usually known as SQL Call Interface or similar) for directly executing SQL program code (using EXEC IMMEDIATE or a similar command). The statements that are not supported are listed in the following section:

·         Native SQL and the Database Interface,

·         Native SQL and Transactions

·         Native SQL and the Database Interface

Native SQL statements bypass the R/3 database interface. With the database buffer on the application server, there is no table logging, and no synchronization. Therefore, you should use Open SQL to change database tables declare in the ABAP dictionary wherever possible. Since the columns contain extra database specific length information for the column tables declared in the ABAP dictionary, containing long columns with the type LCHAR or LRAW should only be addressed using Open SQL. Native SQL may not produce the correct result, as it does not take this information into account. Native SQL does not support automatic client handling. Instead, you must treat a client field like any other field

Native SQL and Transactions

One should not use any transaction control statement such as COMMIT, ROLLBACK WORK, or any statements that set transaction parameters using Native SQL to ensure that transaction in the R/3 System are consistent.

ABAP Dictionary

The ABAP Dictionary is nothing but a part of the ABAP Workbench. It allows you to create and administer database tables. There are no statements from the DDL part of Standard SQL in Open SQL. It should be noted that normal application programs should not create or change their own database tables.

To create and change database tables, the ABAP Dictionary has to use the DDL part of Open SQL. Besides this, it also administers the ABAP Dictionary in the database. In addition, the ABAP Dictionary contains meta-descriptions of all database tables in the R/3 system. Here, only database tables appears in the Dictionary, which you have created using the ABAP Dictionary. Open SQL statements can only access tables, which exists in the ABAP Dictionary.

Authorization and Consistency Checks

With regard to authorization and consistency checks, the DCL part of Standard SQL is not used in R/3 programs. Whereas, the work processes which are within the R/3 system are logged into the database system as users with full rights. By using the R/3 authorization concept, the authorizations of programs or users to read or change database tables is administered within the R/3 system. In addition, transactions must equally ensure their own data consistency using the R/3 locking concept.

The R/3 lock concept allows you to monitor your system with regards to lock logics. The R/3 lock concept works closely together with the R/3 updates.

As an example, say that a travel agent wants to book a flight for a customer who wants to fly to a particular city with a certain airline on a certain day. If there are still available seats on the flight, then the booking will be possible, otherwise it will lead to overbooking. Hence, the database entry corresponding to the flight must be locked against access from other transactions to avoid the possibility of overbooking. This is because two agents might both be doing this same thing at the same time, and we need to make sure that we don't overbook.

Lock Mechanisms

When the database system receives change statements (INSERT, UPDATE, MODIFY, DELETE) from a program, it automatically sets database locks. Database locks are locks on the database entries affected by statements to prevent problems. Since the lock mechanism uses a, lock flag in the entry, you can only set a lock for an existing database entry. After each database commit, these flags are automatically deleted. This means that database locks can never be set for longer than a single database LUW, a single dialog step in an R/3 application program.

Therefore, physical locks in the database system are insufficient for the requirements of an R/3 transaction. Locks in the R/3 system must remain set for the duration of a whole SAP LUW, that is, over several dialog steps. They must also be capable of being handled by different work processes and application servers. As a result, each lock must apply on all servers in that R/3 system.

References

Conclusion

I have discussed here how we can access databases by means of Open SQL and Native SQL. We have learned about Authorization and Consistency Checks as well as the Lock Mechanism which is another important aspect of accessing the ABAP database.



User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-12-08 2:42:57 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search