LESSON 5 DATA BASE DIALOG

Database tables are administered in the ABAP Dictionary. There you can find current information about a database table's technical attributes. Database tables that have been created in the database using the same line type and name are called transparent tables in the ABAP Dictionary.There are a couple of different ways in which you can navigate to transparent tables in the ABAP Dictionary:

Choose Tools->ABAP Workbench->Development->Dictionary to call the ABAP Dictionary directly and insert the name of the transparent table in the appropriate input field, or

Navigate directly to the ABAP Dictionary from the ABAP Editor while editing the program: This can be done by double -clicking on the name of the transparent table in the FROM clause of the SELECT statement.

You can search for database tables in several different ways:

Application hierarchy and the Repository Information System: You may choose application components from the application hierarchy and branch directly to the information system. There you can search for database tables according to their short texts (among other criteria).

If you have the name of a program that accesses the database table:

Input field on a screen; If you know of a program that contains a screen with input fields connected to the table you are looking for, choose F1->Technical info. and then navigate to to the ABAP Dictionary by double -clicking on the technical name of the screen field. This is often a field in a structure. Double -click on the data element and then use the where-used list function to search for transparent tables according to the field type.

Debugger: If you know the name of a program that accesses the database table that you are looking for, you can start this program in debugging mode and set a breakpoint at the SELECT statement.

Editor: Look for the SELECT statement

Object List in the Object Navigator: Pick out the subroutines that encapsulate the database accesses.

If you know of a structure field in the ABAP Dictionary.

Double-click on the data element and then use the where -used list function to search for transparent tables according to the field type.

As soon as you navigate to the definition of a database table in the ABAP Dictionary, information about all of the table's technical attributes is available.

The following information is of interest for enhancing the performance of database accesses:

Key Fields: If the lines requested from the database are being retrieved according to key fields, the Database Optimizer can perform access using a primary index. Checkboxes are on for all key fields.

Secondary Indexes: You may also use secondary indexes to select specific lines. These are displayed in a dialog box whenever you choose the 'Indexes' pushbutton. You can choose an index from the dialog box by simply double -clicking on it. The system then displays a screen with additional information about that index.

You use the Open SQL statement SELECT to read data from the database.

Underlying the SELECT statement is a complex logic that allows you to access many different types of database table.

The statement contains a series of clauses, each of which has a different task:

The SELECT clause specifies

Whether the result of the selection is to be a single line or several lines.

The fields that should be included in the result.

Whether the result may contain two or more identical lines.

The INTO clause specifies the internal data object in the program into which you want to place the selected data.

The FROM clause specifies the source of the data (database table or view).

The WHERE clause specifies conditions that selection results must fulfill. Thus, it actually

determines what lines are included in the results table.

For information about other clauses, refer to the keyword documentation in the ABAP Editor

for the SELECT statement.

Open SQL statements are a subset of Standard SQL that is fully integrated in the ABAP language.

They allow you to access the database in a uniform way from your programs, regardless of the database system being used. Open SQL statements are converted into database-specific SQL statements by the database interface .

The SELECT SINGLE* statement allows you to read a single line from a database table. To ensure that you read a unique entry, all of the key fields must be filled by the WHERE clause. The informs the database interface that all columns in that line of the database table should be read. If only a specific cross-section of columns is desired, a structure can be inserted instead.

The name of a structure to which you want the database interface to copy a data record is inserted after the INTO clause. The structure should have a structure identical to the columns of the database table being read and be left-justified.

If you use the CORRESPONDING FIELDS OF addition in the INTO clause, you can fill the target work area component by component. The system only fills those components that have identical names to columns in the database table. If you do not use this addition, the system fills the work area from the left-hand end without any regard for its structure.

If the system finds a table entry matching your conditions, SY-SUBRC has the value 0.

The SINGLE addition tells the database that only one line needs to be read. The database can then terminate the search as soon as it has found that line. Therefore, SELECT SINGLE produces better performance for single -record access than a SELECT loop if you supply values for all key fields.

If you do not use the addition SINGLE with the SELECT statement, the system reads multiple records from the database. The field list determines the columns whose data is to be read from the database.

The number of lines to be read can be restricted using the WHERE clause. The restrictions contained in the WHERE clause should either be made according to the database table's key fields or according to a secondary index. Further information about key fields and secondary indexes can be found in the ABAP Dictionary. For example, double -clicking on the database table included in the FROM clause will take you directly to the Dictionary.

You may only enter the names of the database table fields you want to be read in the WHERE clause.

Multiple logical conditions can be added to the WHERE clause using AND or OR.

The database delivers data to the database interface in packages. The ABAP runtime system copies the data records to the target area line by line using a loop. It also provides for the sequential processing of all of the statements between SELECT and ENDSELECT.

SY-SUBRC = 0 if the system was able to select at least one entry. After the SELECT statement is executed in each loop pass, the system field SY-DBCNT contains the number of lines read. After the ENDSELECT statement, it contains the total number of lines read.

The addition INTO TABLE causes the ABAP runtime system to copy the contents of the database interface directly to the internal table itab. This is called an array fetch.

Since an array fetch is not logically a loop, no ENDSELECT statement is used.

SY-SUBRC = 0 if the system was able to read at least one table entry.

For further information about array fetch and internal tables, refer to the Internal Tables unit of this course.

The program must contain a data object with a suitable type for each column that is required from a database table. For reasons of program maintenance, you must use the corresponding Dictionary objects to assign types to the data objects. The INTO clause specifies the data object into which you want to place the data from the database table. There are two different ways to do this:

Flat structure: You define a structure in your program that has the fields in the same sequence as the field list in the SELECT clause. Then you enter the structure name in the INTO clause. The contents are copied by position. The structure field names are disregarded.

Single data objects: You enter a set of data objects in the INTO clause.

If you use the INTO CORRESPONDING FIELDS clause, the data is placed in the structure fields that have the same name.

Advantages of this construction:

The structure does not have to be structured in the same way as the field list and does not need to be left-justified

This construction is easy to maintain, since extending the field list does not require other changes to be made to the program, as long as there is a field in the structure that has the same name and type.

Disadvantages of this construction:

INTO CORRESPONDING FIELDS is more runtime-intensive than INTO. The runtime may therefore be longer.

If you want to place data into internal table columns of the same name using an array fetch, use INTO CORRESPONDING FIELDS OF TABLE .

The SAP authorization concept recognizes a large number of different authorizations. These are all managed centrally in the user master record for every user.

Authorizations are not directly assigned to users, but stored in work center descriptions (profiles).

These profiles are generated using the Profile Generator, which administers the profiles as activity groups.

Users can belong to one or more activity groups and are then assigned the authorizations contained in those activity groups.

Release 4.6 contains a large number of pre-defined activity groups. You can use these as is or copy and tailor them to your specific needs.

You should carry out an authorization check before accessing the database. The AUTHORITYCHECK statement first checks whether the user has the authorization containing all the required values. You then check the code value in the system field SY-SUBRC. If this value is 0, the user has the required authorization and the program can continue. If the value is not 0, the user does not possess the required authorization and you should display a message and take the appropriate action.

All data in the SAP system must be protected from unauthorized access by users who do not explicitly have permission to access it.

The system administrator assigns user authorization when maintaining user master data. During this process, you should determine exactly which data users are allowed to access and what kind of access should be allowed.

This is carried out by an authorization object composed of the fields 'Activity' and 'Airline carrier' that has to be addressed both during the authorization assignment process and whenever your program performs an authorization check.

Authorization objects simply define the combination of fields that need to be addressed

simultaneously and serve as templates for both authorizations and authorization checks. They are organized into object classes in order to make it easier to find and administer them; one object class or several may exist in each application. You call the authorization object maintenance transaction from the 'Development' menu in the ABAP Workbench. A complete list of all development objects, sorted according to class and including their corresponding fields and documentation, is part of this transaction.

When making authorization checks in programs, you specify the object and values the user needs in an authorization to be able to access the object. You do not have to specify the name of the authorization.

Important: The Authority-Check statement performs the authority check and returns an appropriate return code value in SY-SUBRC. When checking this return code, you can specify the consequences of a missing authorization (for example: terminate the program or display a message and skip some lines of code).

You must specify all fields of the object in an AUTHORITY-CHECK, otherwise you receive a return code not equal to zero. If you do not want to carry out a check for a particular field, enter DUMMY after the field name.

The most important return codes for AUTHORITY-CHECK are:

0: The user has an authorization containing the required values.

4: The user does not have the required authorization.

8: The check could not successfully be carried out since not all fields of the object were

specified.

The keyword documentation for AUTHORITY-CHECK contains a complete list of return codes.

You can only specify a single field after the FIELD addition, not a selection table. There are function modules which carry out the AUTHORITY-CHECK for all values in the selection table.

If reusable components that encapsulate complex data retrieval are available , then you must use them. There are four techniques available for doing this.

Methods of global classes

Methods of business objects

Function modules

Logical databases are data retrieval programs delivered by SAP that return data in a hierarchically logical sequence.

You can find information on the various techniques in the Reuse Components unit.

Views are application-specific views of different ABAP Dictionary tables. Views can contain a selection of fields from a single very large table or fields from several different tables.

Views allow you to gather information from the fields of different tables and present it to users in the form they require when working with the R/3 System.

Views are mainly used for programming with ABAP and for F4 online help.

If there are no components available that are suitable for your purposes, you can carry out complex database access using ABAP-OPEN- SQL statements. To do this you have to compare the merits of various techniques, as using an unsuitable technique can result in considerable performance problems.