Since you have to test an application to see if it works, and since you only can test it, when you have some test data, what we are doing here is an essential step: In this weblog of our series we will fill the database table YACCOUNT we have created in the last weblog with random entries. We will first manually provide a small set of test data for the check tables YBANK and YCITY in the Data Browser (SE16). Using these entries in the check table a program of some 70 lines will create random entries for the table YACCOUNT. It is these entries that will be the data for the SELECTs we will perform later to provide data for queries started in a Web Dynpro ABAP interface. You need quite some data in your database tables if a SELECT with a complex condition should return a reasonable result set. So it will be a good idea to create some thousand of datasets with the data generating program. In principle, you can choose any number of entries you want to apart from the fact that for numbers larger than a million the performance of the program will diminish. So let us better take account of the fact that we just work on a little demo system and be content with creating some thousands of test dataset. But this discussion already hints one advantage of the program we are going to write: You can create the number of data sets you want provided your system has the relevant resources.
The basic principles of how we create the entries for the database table YACCOUNT is simple:
We use a loop and create each dataset separately. We create a random value for each column of the table in a way that the value fulfills two conditions:
Let us consider the different types of semantic conditions for the different columns. First of all we have to fill the check tables that have no data so fare:
A. Fill the tables YBANK and YCITY with some data. As described in the last weblog a line of the table YCITY contains both the city and the currency assigned to the city. The customers table needs not be filled as it already contains some hundreds of datasets. The data in these check tables plus the fixed values we have defined for the column CAT define the constraints that the random values we create for the respective columns have to meet: The random value for each column has to be a member of the set defined by the entries in the check table. For example, a valid city has to be part of the table YCITY etc.
B. How do we create one dataset: For columns with a check table we have to make sure that the random value is an element of the set defined in the check table. How is this done:
C. As for the account number we have to create a number that is between 10000000 and 99999999 and to assure the uniqueness of the composite key. This is achieved by creating the values for BANK and CITY for a dataset first. Then a random account number is created and only accepted if there is no other dataset in the internal table with the same composite key. If there is already a dataset with that key, another random number is created and so on, until a unique key is created.
D. The structure of the table YCITY assures that every account has the currency that is assigned to its city: The table YCITY provides a unique currency for every city. Once a line is chosen from this table randomly the city and the currency of the respective dataset are determined at the same time.
E. The balance is simply calculated by creating a random integer between 0 and an upper limit.
F. The entry date is the system date.
G. The values for the category are just random integers between 1 and 3.
So each column of the dataset will contain random data that meet the semantic and syntactic constraints by the mechanisms described. After a dataset is created it is added to an internal table of the same line type as the database table YACCOUNT, and the next dataset is created. Once the number of datasets that the user has provided as a parameter to the program is created the whole internal table is stored in the database.
Let us now look in some more detail at how we fill the tables YBANK and YCITY with values:
The steps to fill a table with values in the Data Browser are simple:
This the button you press at the start screen of the SE16
And this is what the screen looks like where you input the data:
Input these values in table YBANK: DEUTSCHE BANK, JP MORGAN, CITIBANK, WASHINGTON MUTUAL, COMMERZBANK, UBS, UNICREDITO ITALIANO, CITYGROUP, CREDIT SUISSE, BANK OF SCOTTLAND, HYPOVEREINSBANK, and DIREKTBANK.
Input these pairs of values into the table YCITY:
BASEL, CHF
BOSTON, USD
FRANKFURT, EUR
MILANO, EUR
MUNIC, EUR
NEW YORK, USD,
PARIS, EUR
LONDON, GBP
The program that generates the test data works fine with any other set of banks and cities/currencies. So if you prefer other banks and towns just type them in. It is understood that the more banks and cities you have the more diverse will be the generated test data.
As I have told you in the last weblog the design of the tables YBANK and SCURX is not as elaborate as it should be. So a field for a country is missing in both tables. Still we can take advantage of the fact that we have defined SCURX as a check table for the table YBANK. When inputting the data in this field, we will get a value help so that we can input only currencies that are defined in table SCURX.
So much for the preparations. Now it is time to have a look at the program that generates the data. I will present the listing of this program twice. First, I will use a listing with line numbers that make it easy to refer to each line in some explanation. At the end of the weblog I will present a complete listing without line numbers so that you can easily paste it in your editor.
Let us start by having a look at the relevant data definitions we need:
First of all, there is the definition of an internal table that should hold the data for the database table YACCOUNT in line 4 and 5 plus the work area of the relevant line type in line 3. A hashed table is a table that is by a hash algorithm. There is no table index. The position of a row in the memory is calculated by specifying a key using a hash function that provides a unique value for each table row. If you want to read a dataset from a large hashed table this is faster by degrees than a search in a standard or a sorted table. In fact the time you need for a search in a hashed table increases logarithmically with the number of entries in the internal table. It is because of this advantage that we choose this type of internal table for our program. Later we have to check if the key of a new dataset we have created randomly is unique. To do this we have to search if the key of this dataset does already exist in the internal table itab_acc.
We need internal tables and work areas for all check tables of the table YACCOUNT as the random values should be take from these tables (lines 6 to 11). As these tables are not very large and only accessed by index, a standard table suffices. Again you see how easy it is in ABAP to define an internal table that has the same type as a database table. The references in the lines 15 to 20 are used for objects created by a factory method of the class cl_abap_random_int. The PARAMETERS statement enables the user to choose how many datasets should be created. I do not think I need to explain the definition of the integers.
The next lines do some more preparatory work:
In line 29 we delete all lines from the database table YACCOUNT and from line 32 to 37 we load the content of the database tables YBANK, YCITY and SCUSTOM in the relevant internal tables and get the respective number of entries in each internal table. In line 41 we create an object that produces random number between one and the number of categories. This is done by a public static factory method of the global class cl_abap_random: Public components of global classes are available in the whole system. In ABAP it is quite common to provide services as methods of global classes. Surely you remember the object oriented way to output a table also uses a method of a global class (cf. ABAP Trial Version for Newbies Part 10: A First Little Business Program).
In the same way we create random-integer-producer-objects for the three check tables (lines 43 to 47), the account number and the initial balance of each account.
Next we create the random entries in a way that assures that the syntactic and semantic constraints are met:
By looping numb_acc times it is made sure that we create the number of datasets chosen by the user. It is within this loop that the dataset is built: We create a random number between 1 and the number of banks in the check table in line 55. By using this number for an index read from the relevant internal table we get a bank randomly and move this value to the relevant component of our structure wa_acc. The mechanism works in an analogous way for each random value from a check table. Just note the advantage resulting from the fact that each row in database table YCITY contain a city and the respective currency for the city: When reading from the table YCITY we get a structure with the components city and currency, and not just any currency, but the currency assigned to the city. This way it is assured that each account has a currency that is determined by the city.
Let us now have a closer look at the inner loop from line 61 to line 68. What is this loop for? This loop is to assure that all lines of the internal table itab_acc will have a unique index. In line 63 the work area wa_acc contains all the key fields we have filled with random values. The statement READ TABLE itab FROM wa reads the dataset with the same key as the work area wa from the internal table itab. So we are looking in the internal table itab_acc if it already contains a dataset with the same key as the work area. If the account number we have created has produced a composite key that already exists another random account number should be created. If the account number is part of a unique key that is a key does not exist so far in the internal table itab_acc the sy-subrc after reading the table in line 64 is different than zero and the loop is left. From line 70 to 74 other random values are created and the entry column is filled with the system date.
Lines 78 to 80 are almost self-explanatory: The whole internal table is inserted into the database table and we output some information as to how many datasets we have created.
And that is it. To use this program yourself create an empty report y_fill_account_table in the package y_abap_demo and insert the program code provided at the end of this weblog by copy and paste.
To check if the program works, run it, insert a number such as for example 1500, and see if you get the output:
Next we open the Data Browser (SE16), input YACCOUNT and press the leftmost button Content. In the next window we press the button Number of Entries , and there we are:
The dialog window shows us that our program has done what it is made for. We can close the dialog window and press the leftmost button Execute to look at the rows in detail:
What we done in this weblog amount to three different things described from different points of view:
The Basic Principle of How to Create the Entries Randomly
The basic principles of how we create the entries for the database table YACCOUNT is simple:
We use a loop and create each dataset separately. We create a random value for each column of the table in a way that the value fulfills two conditions:
- The random value meets the semantic and syntactic constraints for the respective column satisfied.
- It is guaranteed that there is only one dataset with the same composite key: unique-key condition.
Let us consider the different types of semantic conditions for the different columns. First of all we have to fill the check tables that have no data so fare:
A. Fill the tables YBANK and YCITY with some data. As described in the last weblog a line of the table YCITY contains both the city and the currency assigned to the city. The customers table needs not be filled as it already contains some hundreds of datasets. The data in these check tables plus the fixed values we have defined for the column CAT define the constraints that the random values we create for the respective columns have to meet: The random value for each column has to be a member of the set defined by the entries in the check table. For example, a valid city has to be part of the table YCITY etc.
B. How do we create one dataset: For columns with a check table we have to make sure that the random value is an element of the set defined in the check table. How is this done:
- Load the relevant check table into an internal table.
- Get the number of entries (NmbEn) of this internal table.
- Create a random z integer between 1 and NmbEn of the relevant table.
- Select a line from the internal table randomly by index access with index z.
C. As for the account number we have to create a number that is between 10000000 and 99999999 and to assure the uniqueness of the composite key. This is achieved by creating the values for BANK and CITY for a dataset first. Then a random account number is created and only accepted if there is no other dataset in the internal table with the same composite key. If there is already a dataset with that key, another random number is created and so on, until a unique key is created.
D. The structure of the table YCITY assures that every account has the currency that is assigned to its city: The table YCITY provides a unique currency for every city. Once a line is chosen from this table randomly the city and the currency of the respective dataset are determined at the same time.
E. The balance is simply calculated by creating a random integer between 0 and an upper limit.
F. The entry date is the system date.
G. The values for the category are just random integers between 1 and 3.
So each column of the dataset will contain random data that meet the semantic and syntactic constraints by the mechanisms described. After a dataset is created it is added to an internal table of the same line type as the database table YACCOUNT, and the next dataset is created. Once the number of datasets that the user has provided as a parameter to the program is created the whole internal table is stored in the database.
How to Fill Database Tables in the Data Browser
Let us now look in some more detail at how we fill the tables YBANK and YCITY with values:
The steps to fill a table with values in the Data Browser are simple:
- Go to the Data Browser (SE16).
- Select the Create Entries Icon. (second from the left)
- Type in the values and press the Save button.
- Press the Reset button.
- Repeat this step for all values you want to input.
This the button you press at the start screen of the SE16
And this is what the screen looks like where you input the data:
Input these values in table YBANK: DEUTSCHE BANK, JP MORGAN, CITIBANK, WASHINGTON MUTUAL, COMMERZBANK, UBS, UNICREDITO ITALIANO, CITYGROUP, CREDIT SUISSE, BANK OF SCOTTLAND, HYPOVEREINSBANK, and DIREKTBANK.
Input these pairs of values into the table YCITY:
BASEL, CHF
BOSTON, USD
FRANKFURT, EUR
MILANO, EUR
MUNIC, EUR
NEW YORK, USD,
PARIS, EUR
LONDON, GBP
The program that generates the test data works fine with any other set of banks and cities/currencies. So if you prefer other banks and towns just type them in. It is understood that the more banks and cities you have the more diverse will be the generated test data.
As I have told you in the last weblog the design of the tables YBANK and SCURX is not as elaborate as it should be. So a field for a country is missing in both tables. Still we can take advantage of the fact that we have defined SCURX as a check table for the table YBANK. When inputting the data in this field, we will get a value help so that we can input only currencies that are defined in table SCURX.
Looking at the Program Itself
So much for the preparations. Now it is time to have a look at the program that generates the data. I will present the listing of this program twice. First, I will use a listing with line numbers that make it easy to refer to each line in some explanation. At the end of the weblog I will present a complete listing without line numbers so that you can easily paste it in your editor.
Let us start by having a look at the relevant data definitions we need:
1 REPORT y_fill_account_table.
2 * internal tables and work areas for database tables.
3 DATA: wa_acc TYPE yaccount,
4 itab_acc LIKE HASHED TABLE OF yaccount
5 WITH UNIQUE KEY mandt acc_num bank city,
6 wa_bank TYPE ybank,
7 itab_bank LIKE STANDARD TABLE OF ybank,
8 wa_city TYPE ycity,
9 itab_city LIKE STANDARD TABLE OF ycity,
10 wa_scustom TYPE scustom,
11 itab_scustom LIKE STANDARD TABLE OF scustom,
12 z TYPE i.
13 * References to different objects of class cl_abap_random_int
14 * and counter for the number of lines of the itabs.
15 DATA: rnd_bank TYPE REF TO cl_abap_random_int,
16 rnd_city LIKE rnd_bank,
17 rnd_scust LIKE rnd_bank,
18 rnd_cat LIKE rnd_bank,
19 rnd_account LIKE rnd_bank,
20 rnd_balance LIKE rnd_bank,
21 bank_count TYPE i,
22 city_count TYPE i,
23 scust_count TYPE i,
24 cat_count TYPE i VALUE 3,
25 max_start_balance type i value 45000,
26 cat_high type i value 3.
27 * How many datasets to create
28 PARAMETERS: numb_acc TYPE i DEFAULT 10.
First of all, there is the definition of an internal table that should hold the data for the database table YACCOUNT in line 4 and 5 plus the work area of the relevant line type in line 3. A hashed table is a table that is by a hash algorithm. There is no table index. The position of a row in the memory is calculated by specifying a key using a hash function that provides a unique value for each table row. If you want to read a dataset from a large hashed table this is faster by degrees than a search in a standard or a sorted table. In fact the time you need for a search in a hashed table increases logarithmically with the number of entries in the internal table. It is because of this advantage that we choose this type of internal table for our program. Later we have to check if the key of a new dataset we have created randomly is unique. To do this we have to search if the key of this dataset does already exist in the internal table itab_acc.
We need internal tables and work areas for all check tables of the table YACCOUNT as the random values should be take from these tables (lines 6 to 11). As these tables are not very large and only accessed by index, a standard table suffices. Again you see how easy it is in ABAP to define an internal table that has the same type as a database table. The references in the lines 15 to 20 are used for objects created by a factory method of the class cl_abap_random_int. The PARAMETERS statement enables the user to choose how many datasets should be created. I do not think I need to explain the definition of the integers.
The next lines do some more preparatory work:
29 DELETE FROM yaccount. "delete all lines from the db table yaccount
30 * Fill internal table from relevant db table
31 * and get the number of lines.
32 SELECT * FROM ybank INTO TABLE itab_bank.
33 bank_count = LINES( itab_bank ).
34 SELECT * FROM ycity INTO TABLE itab_city.
35 city_count = LINES( itab_city ).
36 SELECT * FROM scustom INTO TABLE itab_scustom.
37 scust_count = LINES( itab_scustom ).
38 * Get objects that have a method to create a
39 * random number between min and max.
40 *for account categories
41 rnd_cat = cl_abap_random_int=>create( min = 1 max = cat_high ). "
42 * for number of banks
43 rnd_bank = cl_abap_random_int=>create( min = 1 max = bank_count ).
44 * for number of customers
45 rnd_scust = cl_abap_random_int=>create( min = 1 max = scust_count ).
46 * for number of cities
47 rnd_city = cl_abap_random_int=>create( min = 1 max = city_count ).
48 * for account number which has 8 digits
49 rnd_account = cl_abap_random_int=>create( min = 10000000
50 max = 99999999 ).
51 * for initial balance
52 rnd_balance = cl_abap_random_int=>create( min = 0
53 max = max_start_balance ).
In line 29 we delete all lines from the database table YACCOUNT and from line 32 to 37 we load the content of the database tables YBANK, YCITY and SCUSTOM in the relevant internal tables and get the respective number of entries in each internal table. In line 41 we create an object that produces random number between one and the number of categories. This is done by a public static factory method of the global class cl_abap_random: Public components of global classes are available in the whole system. In ABAP it is quite common to provide services as methods of global classes. Surely you remember the object oriented way to output a table also uses a method of a global class (cf. ABAP Trial Version for Newbies Part 10: A First Little Business Program).
In the same way we create random-integer-producer-objects for the three check tables (lines 43 to 47), the account number and the initial balance of each account.
Next we create the random entries in a way that assures that the syntactic and semantic constraints are met:
54 DO numb_acc TIMES.
55 z = rnd_bank->get_next( ). " Get random number
56 READ TABLE itab_bank INDEX z INTO wa_acc-bank. "Get a bank randomly
57 z = rnd_city->get_next( ).
58 READ TABLE itab_city INDEX z INTO wa_city. "Get dataset from ycity
59 wa_acc-city = wa_city-city. "randomly
60 wa_acc-currency = wa_city-currency. "Currency depends on the city
61 DO. "get an account number that is unique for bank and city
62 z = rnd_account->get_next( ).
63 wa_acc-acc_num = z.
64 READ TABLE itab_acc FROM wa_acc TRANSPORTING NO FIELDS.
65 IF sy-subrc <> 0. "Check if there is already a dataset
66 EXIT. "with the same key in itab_acc
67 ENDIF.
68 ENDDO.
69
70 z = rnd_scust->get_next( ).
71 READ TABLE itab_scustom INDEX z INTO wa_scustom.
72 wa_acc-customer = wa_scustom-id.
73 wa_acc-balance = rnd_balance->get_next( ).
74 wa_acc-category = rnd_cat->get_next( ).
75 wa_acc-last_entry = sy-datum.
76 INSERT wa_acc INTO TABLE itab_acc.
77 ENDDO.
78 INSERT yaccount FROM TABLE itab_acc.
79 WRITE: 'Database Table yaccount successfully filled with ',
80 numb_acc,' datasets' .
By looping numb_acc times it is made sure that we create the number of datasets chosen by the user. It is within this loop that the dataset is built: We create a random number between 1 and the number of banks in the check table in line 55. By using this number for an index read from the relevant internal table we get a bank randomly and move this value to the relevant component of our structure wa_acc. The mechanism works in an analogous way for each random value from a check table. Just note the advantage resulting from the fact that each row in database table YCITY contain a city and the respective currency for the city: When reading from the table YCITY we get a structure with the components city and currency, and not just any currency, but the currency assigned to the city. This way it is assured that each account has a currency that is determined by the city.
Let us now have a closer look at the inner loop from line 61 to line 68. What is this loop for? This loop is to assure that all lines of the internal table itab_acc will have a unique index. In line 63 the work area wa_acc contains all the key fields we have filled with random values. The statement READ TABLE itab FROM wa reads the dataset with the same key as the work area wa from the internal table itab. So we are looking in the internal table itab_acc if it already contains a dataset with the same key as the work area. If the account number we have created has produced a composite key that already exists another random account number should be created. If the account number is part of a unique key that is a key does not exist so far in the internal table itab_acc the sy-subrc after reading the table in line 64 is different than zero and the loop is left. From line 70 to 74 other random values are created and the entry column is filled with the system date.
Lines 78 to 80 are almost self-explanatory: The whole internal table is inserted into the database table and we output some information as to how many datasets we have created.
And that is it. To use this program yourself create an empty report y_fill_account_table in the package y_abap_demo and insert the program code provided at the end of this weblog by copy and paste.
Running the program
To check if the program works, run it, insert a number such as for example 1500, and see if you get the output:
Next we open the Data Browser (SE16), input YACCOUNT and press the leftmost button Content. In the next window we press the button Number of Entries , and there we are:
The dialog window shows us that our program has done what it is made for. We can close the dialog window and press the leftmost button Execute to look at the rows in detail:
Summary
What we done in this weblog amount to three different things described from different points of view:
- We have implemented a program that creates the test data we need for the subsequent Web Dynpro weblogs.
- You have seen a mechanism that you can adapt easily to create test data for your own programs.
- When explaining the details of the program you have learned some more details about ABAP such as how to define references to instances of a global class, how services are provides as method of global classes, how to read lines from internal tables in different ways, and some more details.
The Complete Program Code
REPORT y_fill_account_table.
* internal tables and work areas for database tables.
DATA: wa_acc TYPE yaccount,
itab_acc LIKE HASHED TABLE OF yaccount
WITH UNIQUE KEY mandt acc_num bank city,
wa_bank TYPE ybank,
itab_bank LIKE STANDARD TABLE OF ybank,
wa_city TYPE ycity,
itab_city LIKE STANDARD TABLE OF ycity,
wa_scustom TYPE scustom,
itab_scustom LIKE STANDARD TABLE OF scustom,
z TYPE i.
* References to different objects of class cl_abap_random_int
* and counter for the number of lines of the itabs.
DATA: rnd_bank TYPE REF TO cl_abap_random_int,
rnd_city LIKE rnd_bank,
rnd_scust LIKE rnd_bank,
rnd_cat LIKE rnd_bank,
rnd_account LIKE rnd_bank,
rnd_balance LIKE rnd_bank,
bank_count TYPE i,
city_count TYPE i,
scust_count TYPE i,
cat_count TYPE i VALUE 3,
max_start_balance type i value 45000,
cat_high type i value 3.
* How many datasets to create
PARAMETERS: numb_acc TYPE i DEFAULT 10.
DELETE FROM yaccount. "delete all lines from the db table yaccount
* Fill internal table from relevant db table
* and get the number of lines.
SELECT * FROM ybank INTO TABLE itab_bank.
bank_count = LINES( itab_bank ).
SELECT * FROM ycity INTO TABLE itab_city.
city_count = LINES( itab_city ).
SELECT * FROM scustom INTO TABLE itab_scustom.
scust_count = LINES( itab_scustom ).
* Get objects that have a method to create a
* random number between min and max.
*for account categories
rnd_cat = cl_abap_random_int=>create( min = 1 max = cat_high ). "
* for number of banks
rnd_bank = cl_abap_random_int=>create( min = 1 max = bank_count ).
* for number of customers
rnd_scust = cl_abap_random_int=>create( min = 1 max = scust_count ).
* for number of cities
rnd_city = cl_abap_random_int=>create( min = 1 max = city_count ).
* for account number which has 8 digits
rnd_account = cl_abap_random_int=>create( min = 10000000
max = 99999999 ).
* for initial balance
rnd_balance = cl_abap_random_int=>create( min = 0
max = max_start_balance ).
DO numb_acc TIMES.
z = rnd_bank->get_next( ). " Get random number
READ TABLE itab_bank INDEX z INTO wa_acc-bank. "Get a bank randomly
z = rnd_city->get_next( ).
READ TABLE itab_city INDEX z INTO wa_city. "Get dataset from ycity
wa_acc-city = wa_city-city. "randomly
wa_acc-currency = wa_city-currency. "Currency depends on the city
DO. "get an account number that is unique for bank and city
z = rnd_account->get_next( ).
wa_acc-acc_num = z.
READ TABLE itab_acc FROM wa_acc TRANSPORTING NO FIELDS.
IF sy-subrc <> 0. "Check if there is already a dataset
EXIT. "with the same key in itab_acc
ENDIF.
ENDDO.
z = rnd_scust->get_next( ).
READ TABLE itab_scustom INDEX z INTO wa_scustom.
wa_acc-customer = wa_scustom-id.
wa_acc-balance = rnd_balance->get_next( ).
wa_acc-category = rnd_cat->get_next( ).
wa_acc-last_entry = sy-datum.
INSERT wa_acc INTO TABLE itab_acc.
ENDDO.
INSERT yaccount FROM TABLE itab_acc.
WRITE: 'Database Table yaccount successfully filled with ',
numb_acc,' datasets' .
- SAP Managed Tags:
6 Comments
| User | Count |
|---|---|
| 6 | |
| 5 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 | |
| 1 |
