Lesson 6: Add code for the service functions

In EGL, I/O statements such as add and get access data that resides in different kinds of persistent data storage, from file systems to queues to databases. The coding is similar for the different cases.

In this lesson, you add functions that access rows in a relational database. Add the functions in order, before the final end statement in SQLService.egl.

Create binding to database connection

In Lesson 3, you defined a database connection named Derby. Use these steps to use the Derby connection from your SQL service.

Note: The SQL binding will already be included in the deployment descriptor if you checked Save data source configuration to deployment descriptor when retrieving the table definition from the database.

EGL projects have associated deployment descriptor (.egldd) files. To see the name of the file that is in use at development time, right click PaymentService in the project explorer, click Properties, and at the Properties dialog, click EGL Development Deployment Descriptor.

PaymentService.egldd was specified as the deployment descriptor when you defined the project.

Click OK to close the window.

In the PaymentService project, right-click the PaymentService.egldd file and open the file with the EGL Deployment Descriptor editor.

Select the Resource Bindings tab.

Add a new resource binding named Derby of binding type SQL database binding.

Select radio button Add the information from the selected connection below (hard-coded information). The runtime connection is not supported in EDT 7.0 or earlier.

Select Derby connection details.

Clink on Finish.

Close file PaymentServices.egldd

Using an SQL resource binding in service program

Insert an SQLDataSource variable in the service program following the program name:

The syntax directs the service to use the binding named "Derby" defined as a resource in the deployment descriptor file associated with the service project.

Handling SQL exceptions

SQL operations can fail for a multitude of reasons. For our simple example, our service will catch all SQL and log all SQL exceptions on the server and then throw the exception back to the service client. It will also log each service invocation.

Copy and paste these logging functions to the service program before the final end statement:

Before you continue, you must resolve the reference to the paymentRec Record type. You can automatically create import statements by using the Organize Imports feature. Right-click any blank area in the editor and click EGL Source > Organize Imports.

EGL adds the following statement to the beginning of the file:

import records.paymentRec;

The reference is now resolved. You will use this feature often, whether by selecting the menu item or by pressing Ctrl-Shift-O.

Save the file (Ctrl-S), and then place your cursor anywhere in the add statement. Press keys CTRL-1 and select Add SQL Statement from the popup menu. This feature changes the implicit SQL that underlies the EGL add statement into embedded code that you can modify:

Notice the PAYMENT_ID field is not included in the record fields added to the table. The @GeneratedValue annotation for the field in the record definition tells the EGL SQL builder not to explicitly add the column.

Related reference

Help topic: add considerations for SQL

Help topic: Functions

Help topic: import

Help topic: SQL data access

Read all database records

The getAllPayments function uses the get statement with an array object to read all of the records from the table and stores them in an array.

To code the function:

In the EGL editor, copy and paste the following lines into SQLService.egl before the logging functions:

The EGL get statement generates an SQL SELECT statement to retrieve a result set. When the target of the get statement is a dynamic array of records, EGL retrieves all matching rows from the result set and inserts each successive row into the next array element.

As with the add statement, you may place the cursor anywhere in the statement and press CNTL-1 to see the SQL statement EGL will use to retrieve the rows. You may again save the default statement and modify it if it does do what you want.

Save the file.

Related reference

Help topic: get considerations for SQL

Replace a record

The editPayment function replaces an existing row in the database with an edited version. The default statement replaces all records in the table that have key values equal to the contents of the key fields in the record variable. Key fields are those fields declared with the @Id attribute (field payment_id in our example).

To code the function:

In the EGL editor, copy and paste the following lines into SQLService.egl before the logging functions:

The EGL delete statement generates an SQL DELETE statement. If no rows are present, the Derby database returns an SQLState value of "02000", and the EGL runtime code throws an exception that the function catches: that is, processes, in some onException logic.

When a function catches but ignores an exception, processing continues without interruption. That rule applies to the preceding logic, when the value of SQLState is "02000". When a function uses the throw statement to throw an exception, the exception stays active. That rule also applies to the preceding logic, when the value of SQLState is other than "02000".

At run time, if a service does not handle an exception, the service requester receives an exception of type ServiceInvocationException. Incidentally, if the service cannot be accessed, the requester receives an exception of type ServiceInvocationException or ServiceBindingException, depending on the details of the error.

Save the file.

Related reference

Help topic: delete considerations for SQL

Help topic: Exception handling

Create test data

The createDefaultTable function creates a set of data for testing your completed application.

To code the function:

In the EGL editor, copy and paste the following lines into SQLService.egl before the logging functions:

The EGL execute statement runs a literal SQL statement that deletes all rows from the PAYMENT table.

The ispDate variable receives a date value from the dateTimeLib.dateValueFromGregorian() system function. The content of the variable is then in a format that is appropriate for insertion into the dueDate field in the database.

The addPayment function is repeatedly invoked to add new rows to the PAYMENT table.

The call to the getAllPayments function returns an array of rows that were retrieved from the table.