|
|
| (3 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | = Lesson 6: Add code for the service functions<br> =
| + | Please go to [[EDT:Tutorial: RUI With DataBase Lesson 6]] |
| − | | + | |
| − | 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.<br><br>
| + | |
| − | | + | |
| − | === Add a payment record ===
| + | |
| − | | + | |
| − | The addPayment() function adds 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 inOut)<br> add newPayment;<br> 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 Organize Imports.<br> The menu contains an option for Organize Imports.<br> 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.<br> Save the file (Ctrl-S), and then place your cursor anywhere in the add statement. Right-click and select SQL Statement > Add.<br> The SQL Statement options.<br> This feature changes the implicit SQL that underlies the EGL add statement into embedded code that you can modify.
| + | |
| − | | + | |
| − | The embedded SQL code<br> Because the paymentID field is auto-generated, you must not overwrite it:<br> Delete PAYMENT_ID and subsequent comma from the INSERT list.<br> Delete :newPayment.paymentId and subsequent comma from the VALUES list.<br> Note: In keeping with SQL terminology, each variable that is referenced in an SQL statement is called a host variable. The word host refers to the language that embeds the SQL statement; in this case, EGL. For example, the initial colon in :newPayment.paymentId indicates a host variable.<br> The revised add statement looks like the following image:<br> Revised SQL code<br> Save the file.
| + | |
| − | | + | |
| − | Related reference
| + | |
| − | | + | |
| − | add considerations for SQL<br>Functions<br>import<br>SQL data access<br><br>
| + | |
| − | | + | |
| − | === Read all database records ===
| + | |
| − | | + | |
| − | The getAllPayments function reads 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[])<br> paymentArray paymentRec[];<br> get paymentArray;<br> return (paymentArray);<br> 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.<br> Save the file.
| + | |
| − | | + | |
| − | Related reference
| + | |
| − | | + | |
| − | get considerations for SQL<br><br>
| + | |
| − | | + | |
| − | === Replace a record ===
| + | |
| − | | + | |
| − | The editPayment function replaces an existing row in the database with an edited version. The function assumes that the user previously read the row from the database.
| + | |
| − | | + | |
| − | To code the function:
| + | |
| − | | + | |
| − | In the EGL editor, copy and paste the following lines into SQLService.egl before the end statement:
| + | |
| − | | + | |
| − | function editPayment(chgPayment paymentRec inOut)<br> replace chgPayment nocursor;<br> end
| + | |
| − | | + | |
| − | The EGL replace statement generates an SQL UPDATE statement.<br> Save the file.
| + | |
| − | | + | |
| − | Related reference
| + | |
| − | | + | |
| − | replace considerations for SQL<br><br>
| + | |
| − | | + | |
| − | === 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 inOut)
| + | |
| − | | + | |
| − | try<br> delete delPayment nocursor;
| + | |
| − | | + | |
| − | onException(exception SQLException)<br> if(SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)<br> throw exception;<br> end<br> end<br> 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.<br> Save the file.
| + | |
| − | | + | |
| − | Related reference
| + | |
| − | | + | |
| − | delete considerations for SQL<br>Exception handling<br>
| + | |
| − | | + | |
| − | === 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[])
| + | |
| − | | + | |
| − | try<br> execute #sql{<br> delete from PAYMENT<br> };
| + | |
| − | | + | |
| − | onException(exception SQLException)
| + | |
| − | | + | |
| − | if (SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)<br> throw exception;<br> end<br> end;
| + | |
| − | | + | |
| − | ispDate DATE = dateTimeLib.dateValueFromGregorian(20110405);<br> addPayment(new paymentRec{category = 1, description = "Apartment",<br> amount = 880, fixedPayment = YES});<br> addPayment(new paymentRec{category = 2, description = "Groceries",<br> amount = 450, fixedPayment = NO});<br> addPayment(new paymentRec{category = 5, description = "ISP",<br> amount = 19.99, fixedPayment = YES, dueDate = ispDate });<br> return (getAllPayments());<br> end
| + | |
| − | | + | |
| − | The code acts as follows:<br> The EGL execute statement runs a literal SQL statement that deletes all rows from the PAYMENT table.<br> 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.<br> The addPayment function is repeatedly invoked to add new rows to the PAYMENT table.<br> The call to the getAllPayments function returns an array of rows that were retrieved from the table.<br> Press Ctrl-Shift-F to format the code. If you see any red Xs, compare your code with the finished code in Finished code for SQLService.egl after lesson 6<br> Save and close the file.
| + | |
| − | | + | |
| − | Related reference
| + | |
| − | | + | |
| − | execute considerations for SQL<br>dateValueFromGregorian()<br>
| + | |
| − | | + | |
| − | === 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.
| + | |
| − | | + | |
| − | <br>
| + | |
