|
|
| (2 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | | + | Please go to [[EDT:Tutorial: RUI With DataBase Lesson 6]] |
| − | [[EDT:Tutorial: Access a database with EGL Rich UI|Access a database with EGL Rich UI]]
| + | |
| − | | + | |
| − | | + | |
| − | {| style="float: right"
| + | |
| − | |[[EDT:Tutorial: RUI With DataBase Lesson 5|< Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 7|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.[[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.]]
| + | |
| − | #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'''.[[Image:EDT_Tutorial_edt_richui_sql05_add_sql_binding.jpg|Add SQL binding to deployment descriptor.]]
| + | |
| − | #Close file '''PaymentServices.egldd'''
| + | |
| − | | + | |
| − | == Using an SQL resource binding in service program
| + | |
| − | | + | |
| − | Insert a dataSource variable in the service program following the program name:<code>
| + | |
| − | package services;
| + | |
| − |
| + | |
| − | service SQLService
| + | |
| − | ds dataSource? { @Resource { bindingKey="Derby" } } ;
| + | |
| − | end
| + | |
| − | </code>
| + | |
| − | | + | |
| − | 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:<code>
| + | |
| − | 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
| + | |
| − | <code/>
| + | |
| − | | + | |
| − | == 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 <tt>SQLService.egl</tt> before
| + | |
| − | the '''end''' statement:<code>
| + | |
| − | function addPayment(newPayment paymentRec in)
| + | |
| − | logEntry ( "addPayment" ) ;
| + | |
| − | try
| + | |
| − | add newPayment to ds ;
| + | |
| − | onException(ex sqlException)
| + | |
| − | logException(ex);
| + | |
| − | end
| + | |
| − | end
| + | |
| − | </code>
| + | |
| − | #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'''.
| + | |
| − | EGL adds
| + | |
| − | the following statement to the beginning of the file:<code>
| + | |
| − | import records.paymentRec;
| + | |
| − | </code>
| + | |
| − | 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:<code>
| + | |
| − | add newPayment to ds with
| + | |
| − | #sql{
| + | |
| − | insert into PAYMENT
| + | |
| − | (CATEGORY, DESCRIPTION, AMOUNT, FIXED_PAYMENT, DUE_DATE,
| + | |
| − | PAYEE_NAME, PAYEE_ADDRESS1, PAYEE_ADDRESS2)
| + | |
| − | values
| + | |
| − | (?, ?, ?, ?, ?, ?, ?, ?)
| + | |
| − | } ;
| + | |
| − | </code>
| + | |
| − | 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 '''<br>
| + | |
| − | | + | |
| − | [../../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 <tt>getAllPayments</tt> 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 <tt>SQLService.egl</tt> before
| + | |
| − | the '''end''' statement:<code>
| + | |
| − | function getAllPayments() returns (paymentRec[])
| + | |
| − | paymentArray paymentRec[];
| + | |
| − | get paymentArray from ds;
| + | |
| − | return (paymentArray);
| + | |
| − | end
| + | |
| − | </code>
| + | |
| − | 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 '''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 '''<br>
| + | |
| − | | + | |
| − | [../../com.ibm.egl.lr.doc/topics/regl_data_sql_get.html get considerations for SQL]
| + | |
| − | | + | |
| − | == Replace a record ==
| + | |
| − | | + | |
| − | 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).
| + | |
| − | | + | |
| − | To
| + | |
| − | code the function:
| + | |
| − | | + | |
| − | <ol><li>In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before
| + | |
| − | the '''end''' statement:<code>
| + | |
| − | function editPayment(chgPayment paymentRec inOut)
| + | |
| − | replace chgPayment to ds ;
| + | |
| − | end
| + | |
| − | The EGL '''replace''' statement
| + | |
| − | generates an SQL UPDATE statement.
| + | |
| − | <li>Save the file.
| + | |
| − | </ol>
| + | |
| − | | + | |
| − | ''' Related reference '''<br>
| + | |
| − | | + | |
| − | [../../com.ibm.egl.lr.doc/topics/regl_data_sql_replace.html replace considerations for SQL]
| + | |
| − | | + | |
| − | == Delete a record ==
| + | |
| − | | + | |
| − | | + | |
| − | | + | |
| − | The <tt>deletePayment</tt> function deletes the
| + | |
| − | specified record from the table.
| + | |
| − | | + | |
| − | To code the function:
| + | |
| − | | + | |
| − | | + | |
| − | <ol><li>In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before
| + | |
| − | the '''end''' statement:
| + | |
| − | | + | |
| − | function deletePayment(delPayment paymentRec inOut)
| + | |
| − | | + | |
| − | try
| + | |
| − | delete delPayment nocursor;
| + | |
| − | | + | |
| − | onException(exception SQLException)
| + | |
| − | if(SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)
| + | |
| − | throw exception;
| + | |
| − | 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.
| + | |
| − | | + | |
| − | <li>Save the file.
| + | |
| − | </ol>
| + | |
| − | | + | |
| − | ''' Related reference '''<br>
| + | |
| − | | + | |
| − | [../../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 <tt>createDefaultTable</tt> function creates
| + | |
| − | a set of data for testing your completed application.
| + | |
| − | | + | |
| − | To code
| + | |
| − | the function:
| + | |
| − | | + | |
| − | | + | |
| − | <ol><li>In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before
| + | |
| − | the '''end''' statement:
| + | |
| − | | + | |
| − | function createDefaultTable() returns (paymentRec[])
| + | |
| − | | + | |
| − | try
| + | |
| − | execute #sql{
| + | |
| − | delete from PAYMENT
| + | |
| − | };
| + | |
| − | | + | |
| − | onException(exception SQLException)
| + | |
| − | | + | |
| − | if (SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)
| + | |
| − | throw exception;
| + | |
| − | end
| + | |
| − | end;
| + | |
| − | | + | |
| − | ispDate DATE = dateTimeLib.dateValueFromGregorian(20110405);
| + | |
| − | addPayment(new paymentRec{category = 1, description = "Apartment",
| + | |
| − | amount = 880, fixedPayment = YES});
| + | |
| − | addPayment(new paymentRec{category = 2, description = "Groceries",
| + | |
| − | amount = 450, fixedPayment = NO});
| + | |
| − | addPayment(new paymentRec{category = 5, description = "ISP",
| + | |
| − | amount = 19.99, fixedPayment = YES, dueDate = ispDate });
| + | |
| − | return (getAllPayments());
| + | |
| − | end
| + | |
| − | | + | |
| − | The code acts as follows:
| + | |
| − | | + | |
| − | <ul><li>The EGL '''execute''' statement runs a literal
| + | |
| − | 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>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>
| + | |
| − | | + | |
| − | <li>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]]
| + | |
| − | <li>Save and close the file.
| + | |
| − | </ol>
| + | |
| − | | + | |
| − | ''' Related reference '''<br>
| + | |
| − | | + | |
| − | [../../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:
| + | |
| − | | + | |
| − | <ul><li>Add embedded SQL code to a program and modify that code
| + | |
| − | <li>Automatically create and organize '''import''' statements
| + | |
| − | </ul>
| + | |
| − | | + | |
| − | In the next lesson, you will create a widget to hold the table
| + | |
| − | of expense data.
| + | |
| − | | + | |
| − | | + | |
| − | | + | |
| − | {| style="float: right"
| + | |
| − | |[[EDT:Tutorial: RUI With DataBase Lesson 5|< Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 7|Next >]]
| + | |
| − | |}
| + | |
| − | | + | |
| − | | + | |
| − | [[Category:EDT]]
| + | |
