Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EDT:Tutorial: Access a database with EGL Rich UI Lesson 6"
Line 8: | Line 8: | ||
= Lesson 6: Add code for the service functions = | = Lesson 6: Add code for the service functions = | ||
− | In EGL, I/O statements such as '''add''' and '''get''' access | + | 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. |
− | 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 | + | 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. |
− | a relational database. Add the functions in order, before the final '''end''' statement | + | |
− | in SQLService.egl. | + | |
== Create binding to database connection == | == Create binding to database connection == | ||
− | In Lesson 3, you defined a database connection named '''Derby'''. | + | In Lesson 3, you defined a database connection named '''Derby'''. Use these steps to use the '''Derby'' connection from your SQL service. |
− | #EGL projects have associated deployment descriptor ('''.egldd''') files. | + | #EGL projects have associated deployment descriptor ('''.egldd''') files. Click on '''PaymentService''' in the project explorer and select '''Properties > EGLDevelopmentDeploymentDescriptor" to see that file '''PaymentService.egldd''' was created when you defined project '''Payment Service'''. Click on '''OK''' to close the properties window.[[Image:EDT_Tutorial_edt_richui_sql06_deployment_descriptor.jpg|Deployment descriptor property for service project.]] |
#Open file '''PaymentService.egldd''' with the EGL employment descriptor editor. [[Image:EDT_Tutorial_edt_richui_sql06_open_deployment_descriptor.jpg|Open deployment descriptor for service project.]] | #Open file '''PaymentService.egldd''' with the EGL employment descriptor editor. [[Image:EDT_Tutorial_edt_richui_sql06_open_deployment_descriptor.jpg|Open deployment descriptor for service project.]] | ||
#Select the '''Resource Bindings''' folder | #Select the '''Resource Bindings''' folder | ||
Line 91: | Line 86: | ||
end | end | ||
</code> | </code> | ||
− | *Before you continue, you must resolve the reference to | + | *Before you continue, you must resolve the reference to the <tt>paymentRec</tt> Record part. 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'''. |
− | the <tt>paymentRec</tt> Record part. You can automatically | + | :EGL adds the following statement to the beginning of the file:<code> |
− | 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:<code> | + | |
import records.paymentRec; | import records.paymentRec; | ||
</code> | </code> | ||
− | The | + | :The reference is now resolved. You will use this feature often, whether by selecting the menu item or by pressing Ctrl-Shift-O. |
− | reference is now resolved. You will use this feature often, whether | + | *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:<code> |
− | 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:<code> | + | |
function addPayment(newPayment paymentRec in) | function addPayment(newPayment paymentRec in) | ||
logEntry ( "addPayment" ) ; | logEntry ( "addPayment" ) ; | ||
Line 153: | Line 137: | ||
end | end | ||
</code> | </code> | ||
− | The EGL '''get''' statement generates | + | :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. |
− | 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. | *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. | *Save the file. | ||
Line 167: | Line 147: | ||
== Replace a record == | == Replace a record == | ||
− | The <tt>editPayment</tt> function replaces an | + | The <tt>editPayment</tt> 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). |
− | 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 | + | To code the function: |
− | code the function: | + | |
− | *In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before | + | *In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before the logging functions:<code> |
− | the logging functions:<code> | + | |
function editPayment(chgPayment paymentRec in) | function editPayment(chgPayment paymentRec in) | ||
logEntry("editPayment"); | logEntry("editPayment"); | ||
Line 184: | Line 161: | ||
end | end | ||
</code> | </code> | ||
− | The EGL '''replace''' statement | + | The EGL '''replace''' statement generates an SQL UPDATE statement. |
− | generates an SQL UPDATE statement. | + | |
*Save the file. | *Save the file. | ||
Line 194: | Line 170: | ||
== Delete a record == | == Delete a record == | ||
− | The <tt>deletePayment</tt> function deletes the | + | The <tt>deletePayment</tt> function deletes the specified record from the table. |
− | specified record from the table. | + | |
To code the function: | To code the function: | ||
Line 213: | Line 188: | ||
end | end | ||
</code> | </code> | ||
− | The EGL '''delete''' statement generates | + | :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, |
− | 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, | + | |
in some onException logic. | in some onException logic. | ||
− | When a function catches but ignores | + | :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". |
− | 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 | + | :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 |
− | 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 | accessed, the requester receives an exception of type ServiceInvocationException | ||
or ServiceBindingException, depending on the details of the error. | or ServiceBindingException, depending on the details of the error. | ||
Line 242: | Line 206: | ||
== Create test data == | == Create test data == | ||
− | The <tt>createDefaultTable</tt> function creates | + | The <tt>createDefaultTable</tt> function creates a set of data for testing your completed application. |
− | a set of data for testing your completed application. | + | |
− | To code | + | To code the function: |
− | the function: | + | |
*In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before | *In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before | ||
Line 289: | Line 251: | ||
The code acts as follows: | The code acts as follows: | ||
− | <ul><li>The EGL '''execute''' statement runs a literal | + | <ul><li>The EGL '''execute''' statement runs a literal SQL statement that deletes all rows from the PAYMENT table. |
− | SQL statement that deletes all rows from the PAYMENT table. | + | <li>The <tt>ispDate</tt> 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 <tt>dueDate</tt> field in the database. |
− | <li>The <tt>ispDate</tt> variable receives a date value from | + | <li>The <tt>addPayment</tt> function is repeatedly invoked to add new rows to the PAYMENT table. |
− | the '''dateTimeLib.dateValueFromGregorian()''' system | + | <li>The call to the <tt>getAllPayments</tt> function returns an array of rows that were retrieved from the table. |
− | function. The content of the variable is then in a format that is | + | |
− | appropriate for insertion into the <tt>dueDate</tt> field | + | |
− | in the database. | + | |
− | <li>The <tt>addPayment</tt> function is repeatedly invoked | + | |
− | to add new rows to the PAYMENT table. | + | |
− | <li>The call to the <tt>getAllPayments</tt> function returns | + | |
− | an array of rows that were retrieved from the table. | + | |
</ul> | </ul> | ||
− | *Press Ctrl-Shift-F to format the code. If | + | *Press Ctrl-Shift-F to format the code. If you see any red Xs, compare your code with the finished code in [[EDT:Tutorial: RUI With DataBase Lesson 6 Code|Code for PaymentFileMaintenance.egl after lesson 6]] |
− | you see any red Xs, compare your code with the finished code in [[EDT:Tutorial: RUI With DataBase Lesson 6 Code|Code for PaymentFileMaintenance.egl after lesson 6]] | + | |
*Save and close the file. | *Save and close the file. | ||
Line 321: | Line 275: | ||
In the next lesson, you will create a widget to hold the table | In the next lesson, you will create a widget to hold the table | ||
of expense data. | of expense data. | ||
− | |||
− | |||
{| style="float: right" | {| style="float: right" | ||
|[[EDT:Tutorial: RUI With DataBase Lesson 5|< Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 7|Next >]] | |[[EDT:Tutorial: RUI With DataBase Lesson 5|< Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 7|Next >]] | ||
|} | |} | ||
− | |||
[[Category:EDT]] | [[Category:EDT]] |
Revision as of 16:12, 29 November 2011
Access a database with EGL Rich UI
< Previous | Next > |
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.
- EGL projects have associated deployment descriptor (.egldd) files. Click on PaymentService in the project explorer and select Properties > EGLDevelopmentDeploymentDescriptor" to see that file PaymentService.egldd was created when you defined project Payment Service. Click on OK to close the properties window.
- Open file PaymentService.egldd with the EGL employment descriptor editor.
- Select the Resource Bindings folder
- Add a new resource binding named DerbyResource of binding type SQL database binding.
- Select radio button Reference the selected workspace connection below [retrieved at runtime].
- Select Derby connection details.
- Clink on Finish.
- Close file PaymentServices.egldd
Using an SQL resource binding in service program
Insert a dataSource variable in the service program following the program name:
package services; service SQLService ds dataSource? { @Resource { bindingKey="Derby" } } ; end
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.
Cut and paste these logging function to the service program before the final end statement:
logActive boolean = true; activeService string; private function logEntry(serviceFunction string in) activeService = serviceFunction; log("Entry: SQLService, " + serviceFunction); end private function logException(ex sqlException?) accumulatedMessage string = "Exception: SQLService, " + activeService; while(ex != null) accumulatedMessage = accumulatedMessage + ", SQLSTATE = " + ex.SQLState + ", message = " + ex.message; ex = ex.nextException; end log(accumulatedMessage); throw new anyException{message = accumulatedMessage}; end private function log(text string in) if(logActive) sysLib.writeStdOut(text); end end
Add a payment record
Include a function to the service program that uses the add statement to insert a new row to the database.
To code the function:
- In the EGL editor, copy and paste the following lines into SQLService.egl before
the end statement:
function addPayment(newPayment paymentRec in) logEntry ( "addPayment" ) ; try add newPayment to ds ; onException(ex sqlException) logException(ex); end end
- Before you continue, you must resolve the reference to the paymentRec Record part. 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:
function addPayment(newPayment paymentRec in) logEntry ( "addPayment" ) ; try add newPayment to ds with #sql{ insert into PAYMENT (CATEGORY, DESCRIPTION, AMOUNT, FIXED_PAYMENT, DUE_DATE, PAYEE_NAME, PAYEE_ADDRESS1, PAYEE_ADDRESS2) values (?, ?, ?, ?, ?, ?, ?, ?) }; onException(ex sqlException) logException(ex); end end
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
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_add.html add considerations for SQL] [../../com.ibm.egl.lr.doc/topics/regl_core_function.html Functions] [../../com.ibm.egl.lr.doc/topics/regl_core_import.html import] [../../com.ibm.egl.lr.doc/topics/regl_data_sql_overview.html 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 end statement:
function getAllPayments() returns(paymentRec[]) payments paymentRec[]; logEntry("getAllPayments"); try get payments from ds; onException(ex sqlException) logException(ex); end return(payments); end
- 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
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_get.html 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:
function editPayment(chgPayment paymentRec in) logEntry("editPayment"); try replace chgPayment to ds; onException(ex SQLException) logException(ex); end end
The EGL replace statement generates an SQL UPDATE statement.
- Save the file.
Related reference
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_replace.html replace considerations for SQL]
Delete a record
The deletePayment function deletes the specified record from the table.
To code the function:
- In the EGL editor, copy and paste the following lines into SQLService.egl before
the end statement:
function deletePayment(delPayment paymentRec in) logEntry("deletePayment"); try delete delPayment from ds; onException(ex SQLException) if(ex.SQLState != "02000") // sql state is five digits logException sqlException; end end end
- 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
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_delete.html delete considerations for SQL] [../../com.ibm.egl.lr.doc/topics/regl_core_xcpt.html 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 end statement:
function createDefaultTable() returns(paymentRec[]) payments paymentRec[]; logEntry("createDefaultTable"); try try execute from ds with #sql{ delete from PAYMENT }; onException(ex SQLException) if(ex.SQLState != "02000") // sqlState is five digits throw ex; end end ispDate date = dateTimeLib.dateFromGregorian(20140405); addPayment(new paymentRec{category = 1, description = "Apartment" , amount = 880, fixedPayment = yes, dueDate = new date , payeeName = "A Jones", payeeAddress1 = "100 Jones Dr" , payeeAddress2 = "Jonesboro, NC" }); addPayment(new paymentRec{category = 2, description = "Groceries" , amount = 450, fixedPayment = no, dueDate = new date , payeeName = "B Jones", payeeAddress1 = "200 Jones Dr" , payeeAddress2 = "Jonesboro, NC" }); addPayment(new paymentRec{category = 5, description = "ISP" , amount = 19.99, fixedPayment = no, dueDate = ispDate , payeeName = "C Jones", payeeAddress1 = "300 Jones Dr" , payeeAddress2 = "Jonesboro, NC" }); payments = getAllPayments(); onException(ex anyException) logException(ex); end return(payments); end
The code acts as follows:
- 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.
- Press Ctrl-Shift-F to format the code. If you see any red Xs, compare your code with the finished code in Code for PaymentFileMaintenance.egl after lesson 6
- Save and close the file.
Related reference
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_execute.html execute considerations for SQL] [../../com.ibm.egl.lr.doc/topics/regl_core_date_date_value_greg.html dateValueFromGregorian()]
Lesson checkpoint
You learned how to complete the following tasks:
- Add embedded SQL code to a program and modify that code
- Automatically create and organize import statements
In the next lesson, you will create a widget to hold the table of expense data.
< Previous | Next > |