Simple BSP application to Create, Modify and Delete the database entries

This tutorial will give you blow-by-blow description of how to design a simple BSP application that would fetch entries from database table and create entries, if there exists no entry.

Before building a BSP application, you’ll have to have database table.

STEP 1: Call transaction SE11; create a database table using predefined types.

STEP 2: Push some entries in the table;

Enter a bunch of employee-ids, corresponding date-of-birth, date-of-joining and salary.

STEP 3: Call transaction SE80; create a BSP application.

STEP 4: Right-click the object name and create a page with flow-logic.

STEP 5: Choose the Type Definitions tab and create a type-definition.

STEP 6: Choose the tab Page Attributes and define the work variables, which you are going to use in your application. Besides defining work-variables, you have to define an internal table and a field string.

To define a field-string, use types: ff_progmr. And to define an internal table, use table-type of ‘ZART_PROGRAMMER’ table. Open table using SE11, press Ctrl+Shift+F3 (Where-Used-List), or click the icon , deselect other checkboxes and select Table-Types, purse down the table-type name for the table ZART_PROGRAMMER.

Or create a new table-type, if there exists no table-type. Call transaction SE11->Data type->Create->Table Type->Line Type: ZART_PROGRAMMER->Save and Activate.

fl_flag		TYPE		INT4

fs_progmr	TYPE		FF_PROGMR

t_progmr	TYPE 		ZART_TT

w_dob		TYPE 		DATS

w_doj		TYPE		DATS

w_emno	TYPE		ZART_PROGRAMMER-EMNO

w_ext_dob	TYPE		CHAR10

w_ext_doj	TYPE		CHAR10

w_index	TYPE		INT4

STEP 10: On the click of create button, we set the fl_flag as 2, that can be used to display create layout with an insert button on the same page. Further, on click of insert button, values of all input fields are processed and inserted into database using a simple INSERT query.

Add the following code in OnInputProcessing;

ELSEIF w_eventid EQ 'create'.
fl_flag = 2.

Now, add this piece of code in the layout;

<% elseif fl_flag eq 2.
%>
<center>
<table bgcolor="ivory">
<tr>
<td>
label for = "ip_emno"
labelType = "MEDIUM"
text = "Employee Number" />
td>
<td>
id = "ip_emno”
disabled = "FALSE" />
td>tr>
<tr>
<td>
label for = "ip_dob"
labelType = "MEDIUM"
text = "Date of Birth" />
td>
<td>
id = "ip_dob" />
td>tr>
<tr>
<td>
label for = "ip_doj"
labelType = "MEDIUM"
text = "Date of Joining" />
td>
<td>
id = "ip_doj" />
td>tr>
<tr>
<td>
label for = "ip_salary"
labelType = "MEDIUM"
text = "Salary" />
td>
<td>
id = "ip_salary" />
td>tr>
<tr><td colspan = "2">
<center>
button id = "insert"
tooltip = "Create a New Record"
text = "INSERT"
onClick = "OnInputProcessing()" />
center>
td>tr>
table>

STEP 11: Now attach an action to insert button;

ELSEIF w_eventid EQ 'insert'.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_emno'
RECEIVING
data = w_object.

w_in_field ?= w_object.
w_in_value = w_in_field->value.
w_employee = w_in_value.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_dob'
RECEIVING
data = w_object.
w_in_field ?= w_object.

CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
EXPORTING
date_external = w_in_field->value
* ACCEPT_INITIAL_DATE =
IMPORTING
date_internal = w_dofb
* EXCEPTIONS
* DATE_EXTERNAL_IS_INVALID = 1
* OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_doj'
RECEIVING
data = w_object.

w_in_field ?= w_object.

CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
EXPORTING
date_external = w_in_field->value
* ACCEPT_INITIAL_DATE =
IMPORTING
date_internal = w_dofj
* EXCEPTIONS
* DATE_EXTERNAL_IS_INVALID = 1
* OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_salary'
RECEIVING
data = w_object.

w_in_field ?= w_object.
w_in_value = w_in_field->value.
w_esalary = w_in_value.

fs_progmr-emno = w_employee.
fs_progmr-dob = w_dofb.
fs_progmr-doj = w_dofj.
fs_progmr-salary = w_esalary.

INSERT zart_programmer FROM fs_progmr.

NOTE: We have to convert date-fields to internal format before inserting them in database, or else unformatted date will be inserted into database.

EXTERNAL FORMAT: 28.02.2009

INTERNAL FORMAT WITHOUT CONVERSION: 28.02.20

EXTERNAL FORMAT FOR DISPLAY WITHOUT CONVERSION: 20.2..28.0

To avoid these discrepancies, we use CONVERT_DATE_TO_INTERNAL and CONVERT_DATE_TO_EXTERNAL function modules.

Now save, activate and test the page:

No sooner insert button is clicked, entries are pushed into the database table and page shows updated database status, since SELECT query is given in OnInitialization event.

Entries are very well inserted into the database table, we now make innovations to the same page to modify and delete entries.

STEP 12: Add the following piece of code in OnInitialization.

IF w_index IS NOT INITIAL.
READ TABLE t_progmr INTO fs_progmr INDEX w_index.

w_emno = fs_progmr-emno.
w_dob = fs_progmr-dob.
w_doj = fs_progmr-doj.
w_salary = fs_progmr-salary.
fl_flag = 1.
CALL FUNCTION 'CONVERT_DATE_TO_EXTERNAL'
EXPORTING
date_internal = w_dob
IMPORTING
date_external = w_ext_dob
EXCEPTIONS
date_internal_is_invalid = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CALL FUNCTION 'CONVERT_DATE_TO_EXTERNAL'
EXPORTING
date_internal = w_doj
IMPORTING
date_external = w_ext_doj
EXCEPTIONS
date_internal_is_invalid = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

ENDIF.

We use highlighted work-variables to store selected row data.

STEP 13: In the layout, add the following code;

<%
else if fl_flag eq 1.
%>
<center>
<table bgcolor="ivory">
<tr>
<td>
label for = "ip_emno"
labelType = "MEDIUM"
text = "Employee Number" />
td>
<td>
id = "ip_emno"
value = "<%= w_emno %>"
disabled = "TRUE" />
td>tr>
<tr>
<td>
label for = "ip_dob"
labelType = "MEDIUM"
text = "Date of Birth" />
td>
<td>
id = "ip_dob"
value = "<%= w_ext_dob %>" />
td>tr>
<tr>
<td>
label for = "ip_doj"
labelType = "MEDIUM"
text = "Date of Joining" />
td>
<td>
id = "ip_doj"
value = "<%= w_ext_doj %>" />
td>tr>
<tr>
<td>
label for = "ip_salary"
labelType = "MEDIUM"
text = "Salary" />
td>
<td>
id = "ip_salary"
value = "<%= w_salary %>" />
td>tr>
<tr><td>
<center>
button id = "save"
tooltip = "Modify the Content"
text = "MODIFY"
onClick = "OnInputProcessing()" />
button id = "delete"
tooltip = "Delete Selected Entry"
text = "DELETE"
onClick = "OnInputProcessing()" />
center>
td>tr>
table>
<%
endif.
%>

STEP 14: Now, when MODIFY button is clicked, the values changed should be stored in database without changing employee id, since that’s primary key.

ELSEIF w_eventid EQ 'save'.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_emno'
RECEIVING
data = w_object.

w_in_field ?= w_object.
w_in_value = w_in_field->value.
w_employee = w_in_value.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_dob'
RECEIVING
data = w_object.
w_in_field ?= w_object.

CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
EXPORTING
date_external = w_in_field->value
* ACCEPT_INITIAL_DATE =
IMPORTING
date_internal = w_dofb
* EXCEPTIONS
* DATE_EXTERNAL_IS_INVALID = 1
* OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_doj'
RECEIVING
data = w_object.

w_in_field ?= w_object.

CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
EXPORTING
date_external = w_in_field->value
* ACCEPT_INITIAL_DATE =
IMPORTING
date_internal = w_dofj
* EXCEPTIONS
* DATE_EXTERNAL_IS_INVALID = 1
* OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_salary'
RECEIVING
data = w_object.

w_in_field ?= w_object.
w_in_value = w_in_field->value.
w_esalary = w_in_value.

UPDATE zart_programmer SET emno = w_employee
dob = w_dofb
doj = w_dofj
salary = w_esalary
WHERE emno = w_employee.

STEP 15: Deleting an entry from database is quite simple, since it doesn’t require any conditions except index.

We give the same code as above, except for the query at the bottom.

ELSEIF w_eventid EQ 'delete'.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_emno'
RECEIVING
data = w_object.

w_in_field ?= w_object.
w_in_value = w_in_field->value.
w_employee = w_in_value.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_dob'
RECEIVING
data = w_object.
w_in_field ?= w_object.

CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
EXPORTING
date_external = w_in_field->value
* ACCEPT_INITIAL_DATE =
IMPORTING
date_internal = w_dofb
* EXCEPTIONS
* DATE_EXTERNAL_IS_INVALID = 1
* OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_doj'
RECEIVING
data = w_object.

w_in_field ?= w_object.

CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
EXPORTING
date_external = w_in_field->value
* ACCEPT_INITIAL_DATE =
IMPORTING
date_internal = w_dofj
* EXCEPTIONS
* DATE_EXTERNAL_IS_INVALID = 1
* OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

CLEAR: w_object,w_in_field,w_in_value.

CALL METHOD cl_htmlb_manager=>get_data
EXPORTING
request = runtime->server->request
name = 'inputfield'
id = 'ip_salary'
RECEIVING
data = w_object.

w_in_field ?= w_object.
w_in_value = w_in_field->value.
w_esalary = w_in_value.

fs_progmr-emno = w_employee.
fs_progmr-dob = w_dofb.
fs_progmr-doj = w_dofj.
fs_progmr-salary = w_esalary.

DELETE zart_programmer FROM fs_progmr.

ENDIF.

STEP 16: Save, activate and test the program.

w_salary	TYPE		ZART_PROGRAMMER-SALARY

STEP 7: Choose the tab Layout and design a web-page that should contain a table-view and table-view-columns. Table-View chooses the internal table from which the data has to be fetched and displays it in formatted manner. Table-View-Column.

To get a table-view, choose Tag-Browser from the left-hand pane; pull down BSP Extensions->Transportable->HTMLB. Here you get a lot of htmlb controls, drag Table-View control and drop it in the layout (For additional details about it, right-click the tag and go through documentation).

content design="design2003" >
title="Main Page " >
form>
<%
if t_progmr[] is not initial.
%>
<center>
<table>
<tr>
<td>
id = "Programmer_Data"
table = "<%= t_progmr %>"
visibleRowCount = "5"
selectionMode = "SINGLESELECT"
onRowSelection = "rowSelection" >
"EMNO"
title = "Employee Number" />
"DOB"
title = "Date of Birth" />
"DOJ"
title = "Date of Joining" />
"SALARY"
title = "Salary" />

td>tr>
<tr> <td>
<center>
button id = "create"
tooltip = "Create New Entries"
text = "CREATE"
onClick = "OnInputProcessing()" />
center>
td>tr>
table>
center>
<%
endif.
%>

When you activate this page and choice to view the output, it can’t happen since internal table contains no data.

STEP 8: To populate data in internal table, choose Event Handler tab and select OnInitialization, this event is triggered no sooner the page gets refreshed or called for the first time.

OnInitialization:

SELECT * FROM zart_programmer INTO TABLE t_progmr.

Save, activate and test the page:

Selection-Mode for the table-view has been defined as ‘SINGLESELECT’, this selects desired row. Now, we need Selected-Row-Index, which eases our task to play around with any kind of manipulation we desire.

STEP 8: Choose tab Event Handler and select OnInputProcessing.

OnInputProcessing handles the events for checking and processing user input and for defining navigation.

Code the following in OnInputProcessing event;

DATA:
w_event TYPE REF TO cl_htmlb_event,
w_eventid TYPE string,
w_object TYPE REF TO object,
w_fieldid TYPE string,
w_in_field TYPE REF TO cl_htmlb_inputfield,
w_in_value TYPE string,
w_employee TYPE zart_programmer-emno,
w_dofb TYPE char10,
w_dofj TYPE char10,
w_esalary TYPE zart_programmer-salary.

CALL METHOD cl_htmlb_manager=>get_event
EXPORTING
request = runtime->server->request
* fast_exit_event_id =
* fast_exit_event_class =
RECEIVING
event = w_event.
w_eventid = w_event->id.

IF w_eventid EQ 'Programmer_Data'.

CLASS cl_htmlb_manager DEFINITION LOAD.

CASE event_id.

WHEN cl_htmlb_manager=>event_id.

DATA:
event TYPE REF TO if_htmlb_data,
selrow TYPE REF TO cl_htmlb_tableview.

event = cl_htmlb_manager=>get_event_ex( request ).
selrow ?= cl_htmlb_manager=>get_data( request = request
name = 'tableView'
id = 'Programmer_Data).

DATA: tv_data TYPE REF TO cl_htmlb_event_tableview.
tv_data = selrow->data.

w_index = tv_data->selectedrowindex.

ENDCASE.

w_eventid contains the id of button, table-view, etc.

w_index contains selected-row-index value.

To catch the event, we make use of Class cl_htmlb_manager and method get_event.

Similarly, to catch data from input-fields, get_data method is used.