Authorisations
When writing programs using open SQL, one has to bear in mind the concepts of authorisation in an SAP system. An SAP system has its own security tools to ensure that users can only access data which they are authorised to see. This includes individual fields as well as individual records. The way authorisations are set up can also limit how data is used, whether a user can only display data or whether they can modify it. All the rules pertaining to this are stored as authorisation objects. These will not be examined in great detail here, but ordinarily users are assigned their own authorisation profile (or composite profile) against their user record, which for informational purposes is managed through transaction code SU01.
This profile then gives the user the correct rights within the program to then carry out their job and SAP delivers many predetermined user profiles with the base system. The system administrators can then use and enhance these to be applied to users. Once a user has one of these profiles, the system will tell them whether or not they can execute a transaction when they try to do this. For example, transaction SE38, the ABAP editor, could be tweaked so that while some users may be able to access it, perhaps they can only do so in display mode, or perhaps they can display and debug the code, but not change it themselves.
Where specific authorisations have not been implemented, programs can be made to carry out an authority check, using the statement AUTHORITY-CHECK. This must be used if a program or transaction is not sufficiently protected by the standard authorisation profiles already set up in the system.
While, this will not be examined in great detail here (the topic is huge in itself), it is important to bear authorisations in mind when working in SAP.
Fundamentals
So far, reading data from database tables has been looked at, now modifying and deleting this data will be examined. There are some important concepts to keep in mind here, for example, the architecture of the system. If one has a three-tier architecture (with a presentation layer, an application server and an underlying database), you must bear in mind that there may be a very large number of users accessing the data at any one time.
It is important to ensure that programs created do not cause any problems in the rest of the system and that the most recent version of the data held on the database is accessed when a program runs. If records are constantly being updated, programs must be able to read and work with data which is current in the system. Fortunately, most of this work is done automatically by the SAP system, and one doesn’t have to worry too much about the underlying technologies related to how data is locked and so on.
One of the key tools which can be used is Open SQL. This acts as an interface between the programs created and the database. By using Open SQL, one can read and modify data, and also buffer data on the application server, which reduces the number of database accesses the system has to perform. It is the database interface which is also responsible for synchronising the buffers with the database tables at predetermined intervals.
When one is creating programs it is important to keep in mind that if data is buffered, and this buffered data is subsequently read, it may not always be up to date. So, when tables are created, they must be created in such a way that the system is told that buffering can or cannot be used, or that it can only be used in certain situations. When the example tables were created earlier, the system was told not to use buffering. Using this setting means that every time data is read from a table, it will always use the most up to date records.
Buffering can be useful for tables which hold master data and configuration settings, because this kind of data does not get updated regularly. When one is working with transactional data however, one wants this data to be as up to date as possible. If transactional data is being used in a context where tables are using buffering, it is important to ensure that programs related to this can take this into account, and make sure that the buffer is updated with new data when this is needed.
When one uses Open SQL statements in a program, tables can only be accessed through the ABAP dictionary. This acts as an interface, one does not access the tables directly through programs. This is not a problem however, as when one uses Open SQL statements, it works just the same as if one was accessing the database directly. Open SQL manages its interface with the database by itself, without the need for the user to do anything here. Statements can be coded just as though they had direct access to the tables, though with the underlying knowledge that by using Open SQL, the data is in fact being accessed through the ABAP dictionary with a built-in level of safety to ensure the ABAP code does not have a direct effect on the SAP database system itself.
Database Lock Objects
Now, locking concepts will be considered. This refers to locking data in database tables and there are two basic types of locking which must be kept in mind. First of all, database locks. These lock data in a physical database. When a record is updated, a lock is set on this, then when it is updated the lock is released. It is there to ensure that, once set, the data can only be accessed and updated by those authorised to do so. When released, it can be accessed more widely.
These locks, though, are not sufficient in an SAP system, and are generally only used when a record is being modified in a single step dialogue process. This process refers to any time that the data in a database can be updated in a single step, on a single screen. In this case, the data can be locked, updated and released very quickly.
As you work more with SAP, the insufficiency of database locks will become clearer, because transactions in an SAP system often occur over multiple steps. If, for example, an employee record is added to the system, one may have to fill in many screens of data. The user in this case will only want the record to be added to the system at the end of the last screen, once all of the data in all of the screens has been input. If just the first screen’s data was saved into the database, then the second’s, and so on, one by one, if the user were to quit halfway through the process, an invalid and unfinished record would be in the database.
This demonstrates the hazard of using database locks with multi-step dialogue processes. For these instances, SAP has introduced a new kind of lock, independent of the database system. These are called lock objects, and allow data records to be locked in multiple database tables for the whole duration of the SAP transaction, provided that these are linked in the ABAP dictionary by foreign key relationships.
SAP lock objects form the basis of the lock concept, and are fully independent of database locks. A lock object allows one to lock a record for multiple tables for the entire duration of an SAP transaction. For this to work, the tables must be linked together using foreign keys. The ABAP dictionary is used to create lock objects, which contain the tables and key fields which make up a shared lock.
When the lock object is created, the system automatically creates two function modules, which will be discussed later. These function modules are simply modularised ABAP programs that can be called from other programs. The first of these has the action of setting a lock, and the second releases this lock. It is the programmer’s responsibility to ensure that these function modules are called at the correct place in the program. When a lock is set, a lock record is created in the central lock table for the entire SAP system. All programs must adhere to using the SAP lock concept to ensure that they set, delete and query the lock table that stores the lock records for the relevant entries.
Lock objects will not be discussed much further, however subsequent programs created, tables accessed and so on here will be done on the assumption that they are not to be used outside of one’s own system.
Using Open SQL Statements
Now, some of the Open SQL statements which can be used in programs will be looked at. As mentioned before, Open SQL statements allow one to indirectly access and modify data held in the underlying database tables. The SELECT statement, which has been used several times previously, is very similar to the standard SQL SELECT statement used by many other programming languages. With Open SQL, these kinds of statements can be used in ABAP programs regardless of what the underlying database is. The system could be running, for example, an Oracle database, a Microsoft SQL database, or any other, and by using Open SQL in programs in conjunction with the ABAP dictionary to create and modify database tables, one can be certain that the ABAP code will not have any issues accessing the data held by the specific type of database the SAP system uses.
When the first database table was created previously, the field MANDT was used, representing the client number and forming part of the database table key, highlighted below:
One may think that, given the importance of this field, it would have to be used in ABAP programs when using Open SQL statements, however, it does not. Almost all tables will include this ‘hidden’ field within them, and the SAP system is built in such a way that a filter is automatically applied to this field, based on the client ID being used. If one is logged in, for example, to client 100, the system will automatically filter all records in the database on this client key and only return those for client 100. When Open SQL is used in the programs one creates, the system manages this field itself, meaning it never has to be included in any selections or update statements used in programs. Also, this carries the benefit of security in the knowledge that any Open SQL statement executed in a program will only affect the records held in the current client.
Using Open SQL Statements – 5 Statements
There are 5 basic Open SQL statements which will be used regularly in programs from here forward. These are SELECT, INSERT, UPDATE, MODIFY and DELETE.
- The SELECT statement has, of course, already been used. This statement allows one to select records from database tables which will then be used in a program.
- INSERT allows new records to be inserted into a database table.
- UPDATE allows records which already exist in the table to be modified.
- MODIFY performs a similar task to update, with slight differences which we will discuss shortly.
- DELETE, of course, allows records to be deleted from a table.
Whenever any of these statements are used in an ABAP program, it is important to check whether the action executed has been successful. If one tries to insert a record into a database table, and it is not inserted correctly or at all, it is important to know, so that the appropriate action can be taken in the program. This is done using a system field which has already been used: SY-SUBRC. When a statement is executed successfully, the SY-SUBRC field will contain a value of 0, so this can be checked for and, if it appears, one can continue with the program. If it is not successful, however, this field will contain a different value, and depending on the statement, this value can have different meanings. It is therefore important to know what the different return codes are for the different ABAP statements, so as to recognise problems and take the correct course of action to solve them. This may sound difficult, but with practice will become second-nature.
Insert Statement
The SELECT statement has already been used, so here it will be skipped for now to focus on the INSERT statement. In this example then, a new record will be inserted into the ZEMPLOYEES table. Firstly, type INSERT, followed by the table name, and then a period:
Doing this, one must always type the table name, a variable’s name cannot be used instead. Use the check statement (IF) to include an SY-SUBRC check, telling the system to do if this does not equal 0:
This is the simplest form of the INSERT statement, and not necessarily the one which is encouraged. Using this form is no longer standard practice, though one may come across it if working with older ABAP programs.
In the above statement, nothing is specified to be inserted. This is where the concept of the work area enters. The statement here expects a work area to exist which has been created when an internal table was declared. This type of work area is often referred to as a header record:
The table above shows the yellow area as a standard table containing four records and their respective fields, the area above in grey is the header record, which is stored in memory and is the area which is accessed when the table is referenced from a program only by its table name. If an INSERT statement is executed, whatever is contained in the header record will be inserted into the table itself. The header record does not exist in the table, it is just an area stored in memory where a current record can be worked with, hence the term work area. When someone refers to the table only by its table name, it is the header record which is referred to, and this can become confusing. One thinks that one is referencing the table itself, but in fact it is the header record which is being worked with, a record held in memory with the same structure as the table. ABAP objects, which are important when one gets to a more advanced stage in ABAP, will not allow a header record to be referred to, so it is important not to do this. Header records were used commonly for this in the past, but as noted previously, this is no longer the way things are done.
To avoid confusion when working with internal tables should programs must work with separate work areas, which are perhaps similar in structure to a header record, but not attached to the table, with a separate name. These are separate structures from the initial table, which are created in a program.
To declare a work area the DATA statement is used. Give this the name “wa_employees”. Now, rather than declaring one data type for this, several fields which make up the table will be declared. The easiest way to do this is to use the LIKE statement.
So here, the wa_employees work area is declared LIKE the zemployees table, taking on the same structure without becoming a table itself. This work area will only store one record. Once this is declared, the INSERT statement can be used to insert the work area and the record it holds into the table. The code here will read “INSERT zemployees FROM wa_employees”:
Additionally, using this form of the INSERT statement allows you to specify the table name using a variable instead. It is important to note here that if one is doing this, the variable must be surrounded by brackets.
Now, the work area must be filled with some data. Use the field names from the zemployees table. This can be done by forward navigation, double-clicking the table name in the code, or by opening a new session and using SE11. The fields of the table can then be copy & pasted into the ABAP editor and the work area’s fields populated as in the image below:
The check statement can then be formulated as follows, meaning that if the record is inserted correctly, the system will state this, if not then the SY-SUBRC code which will not equal zero is will be displayed:
Check the program, save, and activate the code, then test it. The output window will display:
If you check the records in your table via the ‘Data Browser’ screen in the ABAP dictionary, a new record will be visible:
For practice use the ABAP debugger to execute the code step-by-step. First, delete the record from the table in the ABAP dictionary and put a breakpoint in the code at the beginning of the record entry to the work area:
Now execute the program. The breakpoint will cause program execution to pause at your breakpoint and the debugger will open:
Firstly, use the Fields mode to view the work area structure. Double click the wa_employees after the DATA statement and it will appear in the ‘Field names’ box at the bottom. At this point the work area is completely empty, evidenced by the zeros in the adjacent box. To display the full structure, double click the wa_employees in the left box:
Then, execute each line of code starting from the breakpoint using the F5 key, the fields within this structure view are filled one by one:
Return to the Fields view before executing the INSERT statement, and observe the SY-SUBRC field at the bottom of the window. It displays a value of 0. If there are any problems in the execution, this will then change (4 for a warning, 8 for an error). Given that this code has already been successful, you already know that it will remain 0. Once the program has been executed in the debugger, refresh the table in the Data Browser screen again, and the record will be visible.
Clear Statement
At this point, the CLEAR statement will be introduced. In ABAP programs, one will not always simply see the program start at the top, insert one data record and continue on. Loops and the like will be set up, allowing, for example, many records to be inserted at once. To do this, variables and structures are re-used repeatedly. The CLEAR statement allows a field or variable to be cleared out for the insertion of new data in its place, allowing it to be re-used. The CLEAR statement is certainly one which is used commonly in programs, as it allows existing fields to be used multiple times.
In the previous example, the work area structure was filled with data to create a new record to be inserted into the zemployees table, then a validation check performed. If one then wants to insert a new record, the work area code can then be copy & pasted below this. However, since the work area structure is already full, the CLEAR statement must be used so that it can then be filled again with the new data.
To do this, the new line of code would read “CLEAR wa_employees.”
If you just wanted to clear specific fields within your structure you just need to specify the individual fields to be cleared, as in the example below, clear the employee number field. New data can then be entered into the work area again:
Remember that the employee number is a key field for the zemployees table, so as long as this is unique, duplicate information could be entered into the other fields. If one tries to enter the same employee number again though, the sy-subrc field will display a warning with the number 4.
You can see the operation of the CLEAR statement in debug mode. The three images below display the three stages of its operation on the field contents as the code is executed:
Update Statement
The UPDATE statement allows one or more existing records in a table to be modified at the same time. In this example it will just be applied to one, but for more the same principles generally apply.
Just as with the INSERT statement, a work area is declared, filled with the new data which is then put into the record as the program is executed.
Delete the record created with the CLEAR statement as before. Here, the record previously created with the INSERT statement will be updated. Copy & paste the work area and then alter, the text stored in the SURNAME and FORENAME fields. Then on a new line, the same structure as for the INSERT statement is used, but this time using UPDATE:
As this is run line-by-line in debug mode, you can see the Field contents change as it is executed:
Once the UPDATE statement has been executed you can view the Data Browser in the ABAP Dictionary to see that the record has been changed successfully:
Modify Statement
The MODIFY statement could be said to be like a combination of the INSERT and UPDATE statements. It can be used to either insert a new record or modify an existing one. Generally, though the INSERT and UPDATE statements are more widely used for these purposes, since these offer greater clarity. Using the MODIFY statement regularly for these purposes is generally considered bad practice. However, times will arise where its use is appropriate, for example of one is writing code where a record must be inserted or updated depending on a certain situation.
Unsurprisingly, the MODIFY statement follows similar syntax to the previous two statements, modifying the record from the data entered into a work area. When this statement is executed, the key fields involved will be checked against those in the table. If a record with these key field values already exists, it will be updated, if not then a new record will be created.
In the first section of code in the image below, since employee number is the key field, and ‘10000006’ already exists, the record for that employee number will be updated with the new name in the code. A validation check is performed next. The CLEAR statement is then used so a new entry can be put into the work area, and then employee 10000007 is added. Since this is a new, unique key field value, a new record will be inserted, and another validation check executed:
When this is executed, and the data then viewed in the Data Browser, employee number 10000006 will have been updated with the new name, Peter Northmore, and a new record will have been created for number 10000007, Susan Southmore:
Delete Statement
The last statement to be looked at in this section is the DELETE statement. One must be careful using this, because if used incorrectly, there is the possibility of wiping the entire contents of the table, however, as long as it is used correctly, there should be no problem of this sort.
Unlike the previous SQL statements, the DELETE statement does not take into account most fields, only the primary key field. When you want to delete a record from a table, the system only needs to be told what the primary key field value for that record is.
In this example, the last record created, for the employee Susan Southmore will be deleted. For the zemployees table, there are two key fields, the client field and the employee number. The client field is dealt with automatically by the system, and this never has to be included in programs, so the important field here is the employee number field. The syntax to delete the last record created in the previous section would be this:
The FROM addition in the last line ensures only the record referred to by its key field in the work area will be deleted. Again, a validation check is performed to ensure the record is deleted successfully. When this is run in debug mode you can see the fields which are filled with the creation of the record are cleared as the CLEAR statement executes.
After the employee number is filled again the DELETE statement is executed. The code’s output window will indicate the success of the deletion and the record will no longer appear in the Browser view of the table:
The record is now gone from the table.
There is another form of the DELETE statement which can be used. You are not just restricted to using the table key to delete records, logic can also be used. So, rather than using the work area to specify a key field, and using the FROM addition to the DELETE statement, one can use the WHERE addition to tell the program to delete all records where a certain field matches a certain value, meaning that if one has several records which match this value, all of them will be deleted.
The next example will demonstrate this. All records with the surname Brown will be deleted. To be able to demonstrate this, create a second record containing a surname of Brown, save this and view the data:
The code for the new DELETE statement should then look like this. Note the additional FROM which must be used in this instance:
When this code is executed, both records containing a Surname of Brown will be deleted.
Note that, if one uses the following piece of code, without specifying the logic addition, all of the records will in fact be deleted:
