ABAP AND DATABASES

[Former Member]

Hi Experts..

I would like to know how to create a databases in SQL and have access from ABAP.. to Modify, Consult and do the basic operations like insert information, drop and modify in the respective tables.

I'm ussing MiniSap Minigui 6.2 on Windows XP and the BCUSER

Thank you very much for your help

REGARDS

[amit_khare]

Refer these threads miht be useful -

Regards,

Amit

[Former Member]

HI Araceli,

Database Access using Advanced Business Application Programming (ABAP)

Basics :

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.

So 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.

Interface thru data base

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 (For example 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

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

. 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.

Database Accesses of the NetWeaver AS ABAP

The NetWeaver AS ABAP stores long-term data in a central relational database table. In a relational database model, the real world is represented by tables. A table is a two-dimensional matrix, consisting of lines and columns (fields). The smallest possible combination of fields that can uniquely identify each line of the table is called the key. Each table must have at least one key, and each table has one key that is defined as its primary key. Relationships between tables are represented by foreign keys.

Standard SQL

SQL (Structured Query Language) is a largely standardized language for accessing relational databases. It can be divided into three areas:

· Data Manipulation Language (DML)

Statements for reading and changing data in database tables.

· Data Definition Language (DDL)

Statements for creating and administering database tables.

· Data Control Language (DCL)

Statements for authorization and consistency checks.

Each database has a programming interface that allows you to access the database tables using SQL statements. The SQL statements in these programming interfaces are not fully standardized. To access a specific database system, you must refer to the documentation of that system for a list of the SQL statements available and their correct syntax.

The Database Interface

To avoid incompatible situations between different database tables and to make the NetWeaver AS ABAP system independent of the database system in use, each work process on an ABAP application server contains a database interface. The NW AS communicates with the database solely through the database interface. The database interface converts all of the database requests from the NW AS into the correct Standard SQL statements for the database system in use. To do this, it uses a database-specific component that shields the differences between database systems from the rest of the database interface. You choose the this component when you install NetWeaver AS ABAP in accordance with the database in use.

ABAP programs have two options for accessing the database interface: Open SQL and Native SQL.

Open SQL

Open SQL statements are a fully integrated subset of Standard SQL within ABAP. They enable the ABAP programs to access data irrespective of the database system installed. Open SQL consists of the Data Manipulation Language (DML) part of Standard SQL; in other words, it allows you to read (SELECT) and change (INSERT, UPDATE, DELETE) data.

Open SQL also goes beyond Standard SQL to provide statements that, in conjunction with other ABAP constructions, can simplify or speed up database access. It also allows you to buffer certain tables on the application server, saving excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. Buffers are partly stored in the working memory of the current work process, and partly in the shared memory for all work processes on an application server. In SAP systems that are distributed across more than one application server, the data in the various buffers is synchronized at set intervals by buffer management. When buffering the database, you must remember that data in the buffer is not always up to date. For this reason, you should only use the buffer for data which does not often change. You specify whether a table can be buffered in its definition in the ABAP Dictionary.

Native SQL

Native SQL is only loosely integrated into ABAP, and allows access to all of the functions contained in the programming interface of the respective database system. Unlike Open SQL statements, Native SQL statements are not checked and converted, but instead are sent directly to the database system. When you use Native SQL, the function of the database-dependent layer is minimal. Programs that use Native SQL are specific to the database system for which they were written. When developing generally valid ABAP applications, you should – as far as possible – avoid using Native SQL. In some components of the SAP System, Native SQL is used – for example, in the ABAP Dictionary for creating or changing tables.

The ABAP Dictionary

The ABAP Dictionary, part of the ABAP Workbench, allows you to create and administer database tables. Open SQL contains no statements from the DDL part of Standard SQL. Normal application programs should not create or change their own database tables.

The ABAP Dictionary uses the DDL part of Open SQL to create and change database tables. It also administers the ABAP Dictionary in the database. The ABAP Dictionary contains meta descriptions of all database tables in the NetWeaver AS ABAP system. Only database tables that you create using the ABAP Dictionary appear in the Dictionary. Open SQL statements can only access tables that exist in the ABAP Dictionary.

Authorization and Consistency Checks

The DCL part of Standard SQL is not important in ABAP programs. The work processes within the ABAP application server are logged on to the database system as users with full authorization. The authorizations of programs or program users to read or change database tables is managed by the authorization concept. Equally, transactions must ensure their own data consistency in the database using the SAP locking concept. For more information, refer to the chapter Data Consistency.

Work Processes

Work processes execute the individual dialog steps of ABAP application programs. They are components of ABAP application servers. The next two sections describe firstly the structure of a work process, and secondly the different types of work process in NetWeaver AS ABAP.

Structure of a Work Process

The following diagram shows the components of a work process:

Screen Processor

In ABAP application programming, there is a difference between user interaction and processing logic. From a programming point of view, user interaction is controlled by screens. As well as the actual input mask, a screen also consists of flow logic, which controls a large part of the user interaction. NetWeaver AS ABAP contains a special language for programming screen flow logic. The screen processor executes the screen flow logic. Via the dispatcher, it takes over the responsibility for communication between the work process and the SAPgui, calls modules in the flow logic, and ensures that the field contents are transferred from the screen to the flow logic.

ABAP Processor

The actual processing logic of an application program is written in ABAP - SAP’s own programming language. The ABAP processor executes the processing logic of the application program, and communicates with the database interface. The screen processor tells the ABAP processor which module of the screen flow logic should be processed next. The following screen illustrates the interaction between the screen and the ABAP processors when an application program is running.

Database Interface

The database interface provides the following services:

· Establishing and terminating connections between the work process and the database.

· Access to database tables

· Access to Repository objects (ABAP programs, screens and so on)

· Access to catalog information (ABAP Dictionary)

· Controlling transactions (commit and rollback handling)

· Table buffer administration on the ABAP application server.

The following diagram shows the individual components of the database interface:

The diagram shows that there are two different ways of accessing databases: Open SQL and Native SQL.

Open SQL statements are a subset of Standard SQL that is fully integrated in ABAP. They allow you to access data irrespective of the database system that your installation is using. Open SQL consists of the Data Manipulation Language (DML) part of Standard SQL; in other words, it allows you to read (SELECT) and change (INSERT, UPDATE, DELETE) data. The tasks of the Data Definition Language (DDL) and Data Control Language (DCL) parts of Standard SQL are performed in NetWeaver AS ABAP by the ABAP Dictionary and the authorization system. These provide a unified range of functions, irrespective of database, and also contain functions beyond those offered by the various database systems.

Open SQL also goes beyond Standard SQL to provide statements that, in conjunction with other ABAP constructions, can simplify or speed up database access. It also allows you to buffer certain tables on the ABAP application server, saving excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. Buffers are partly stored in the working memory of the current work process, and partly in the shared memory for all work processes on an ABAP application server. Where NetWeaver AS ABAP is distributed across more than one ABAP application server, the data in the various buffers is synchronized at set intervals by the buffer management. When buffering the database, you must remember that data in the buffer is not always up to date. For this reason, you should only use the buffer for data which does not often change.

Native SQL is only loosely integrated into ABAP, and allows access to all of the functions contained in the programming interface of the respective database system. In Native SQL, you can primarily use database-specific SQL statements. The Native SQL interface sends them as is to the database system where they are executed. You can use the full SQL language scope of the respective database which makes all programs using Native SQL specific for the database system installed. In addition, there is a small set of SAP-specific Native SQL statements which are handled in a special way by the Native SQL interface. ABAP applications contain as little Native SQL as possible. In fact, it is only used in a few components (for example, to create or change table definitions in the ABAP Dictionary).

The database-dependent layer in the diagram serves to hide the differences between database systems from the rest of the database interface. You choose the appropriate layer when you install NetWeaver AS ABAP. Thanks to the standardization of SQL, the differences in the syntax of statements are very slight. However, the semantics and behavior of the statements have not been fully standardized, and the differences in these areas can be greater. When you use Native SQL, the function of the database-dependent layer is minimal.

Types of Work Process

Before you start NetWeaver AS ABAP, you determine how many work processes each ABAP application server will have, and what their types will be. Since all work processes have the same structure (see preceding section), the type of work process does not determine the technical attrributes of the ABAP application server but the type of tasks to be performed on it. The dispatcher starts the work processes and only assigns them tasks that correspond to their type. This means that you can distribute work process types to optimize the use of the resources on your ABAP application servers.

The following diagram shows again the structure of an ABAP application server, but this time, includes the various possible work process types:

Dialog Work Process

Dialog work processes deal with requests from an active user to execute dialog steps (see also Dialog Programming).

Update Work Process

Update work processes execute database update requests. Update requests are part of an SAP LUW that bundle the database operations resulting from the dialog in a database LUW for processing in the background.

Background Work Process

Background work processes process programs that can be executed without user interaction (background jobs).

Enqueue Work Process

The enqueue work process administers a lock table in the shared memory area. The lock table contains the logical database locks for NetWeaver AS ABAP and is an important part of the SAP LUW concept. In NW AS, you may only have one lock table. You may therefore also only have one ABAP application server with enqueue work processes. Normally, a single enqueue work process is sufficient to perform the required tasks.

Spool Work Process

The spool work process passes sequential datasets to a printer or to optical archiving. Each ABAP application server may contain only one spool work process.

Role of Work Processes

The types of its work processes determine the services that an ABAP application server offers. The application server may, of course, have more than one function. For example, it may be both a dialog server and the enqueue server, if it has several dialog work processes and an enqueue work process.

You can use the system administration functions to switch a work process between dialog and background modes while the system is still running. This allows you, for example, to switch an SAP System between day and night operation, where you have more dialog than background work processes during the day, and the other way around during the night.

thanks

karthik

reward me points if usefull