Difference between revisions of "EDT:Tutorial: RUI With DataBase Lesson 6"

From Eclipsepedia

Jump to: navigation, search
(New page: Access a database with EGL Rich UI {| style="float: right" |< Previous | [[EDT:Tutor...)
 
(Create binding to database connection)
 
(24 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
 
[[EDT:Tutorial: Access a database with EGL Rich UI|Access a database with EGL Rich UI]]
 
[[EDT:Tutorial: Access a database with EGL Rich UI|Access a database with EGL Rich UI]]
  
Line 6: Line 5:
 
|[[EDT:Tutorial: RUI With DataBase Lesson 5|&lt; Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 7|Next >]]
 
|[[EDT:Tutorial: RUI With DataBase Lesson 5|&lt; Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 7|Next >]]
 
|}
 
|}
= 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'''.   Use these steps to use the '''Derby'' connection from your SQL service.  
+
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.]]
+
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.
#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
+
#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'''. <br>&nbsp;<br>PaymentService.egldd was specified as the deployment descriptor when you defined the project'''. <br><br>'''Click '''OK''' to close the window.'''[[Image:EDT Tutorial edt richui sql06 egldd property.jpg|Deployment descriptor property for service project.]]'''
#Add a new resource binding named '''DerbyResource''' of binding type '''SQL database binding'''.
+
#In the PaymentService project, right-click the '''PaymentService.egldd''' file and open the file with the EGL Deployment Descriptor editor.<br> [[Image:EDT Tutorial edt richui sql06 open deployment descriptor.jpg|Open deployment descriptor for service project.]]  
#Select radio button '''Reference the selected workspace connection below [retrieved at runtime]'''.
+
#Select the '''Resource Bindings''' tab.
#Select '''Derby''' connection details.
+
#Add a new resource binding named '''Derby''' of binding type '''SQL database binding'''.  
#Clink on '''Finish'''.[[Image:EDT_Tutorial_edt_richui_sql05_add_sql_binding.jpg|Add SQL binding to deployment descriptor.]]
+
#If you are using EDT 0.7.0, 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.
 +
#If you are using EDT 0.8.0, you will want to select radio button '''Reference the selected workspace connection below (retrieved at runtime)'''.  When the application is deployed to the application server, the connection will be accessed at runtime using a JNDI name as a resource reference.  If you deploy to an  Apache Tomcat Server, EGL deployment uses both the JNDI name and the connection details to create the JNDI <resource-ref> entry in the '''WEB-INF\web.xml''' file and the <resource> entry in the '''META-INF\context.xml''' file that will be used at run time. The only additional thing you need to do is make sure the '''derbyclient.jar''' file is copied to the Tomcat lib directory.
 +
#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'''
 
#Close file '''PaymentServices.egldd'''
  
== Using an SQL resource binding in service program
+
== Using an SQL resource binding in service program ==
  
Insert a dataSource variable in the service program following the program name:<code>
+
Insert an SQLDataSource variable in the service program following the program name:<br><br>  
  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.
+
*For EDT .7:<br>
  
== Handling SQL exceptions ==
+
package services;
 +
 +
service SQLService
 +
  ds SQLDataSource? { @Resource { bindingKey="Derby" } }&nbsp;;    // EDT 0.7.O syntax
 +
end
  
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.
+
<br>
  
Cut and paste these logging function to the service program before the final '''end'' statement:
+
*For EDT .8:
  
== Add a payment record ==
+
package services;
 +
 +
service SQLService
 +
  ds SQLDataSource? { @Resource { uri="binding:Derby" } }&nbsp;;  // EDT 0.8.O syntax
 +
end
  
Include a function to the service program that uses the '''add''' statement to insert a new
+
<br>
row to the database.
+
  
To code the function:
+
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.
  
#In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before
+
== Handling SQL exceptions ==
the '''end''' statement:<code>
+
  function addPayment(newPayment paymentRec inOut)
+
  add newPayment;
+
  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>
+
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.
  
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_add.html add considerations for SQL]
+
Copy and paste these logging functions to the service program before the final '''end''' statement:<code></code>
[../../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 ==
+
    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&nbsp;!= 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
  
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
+
== Add a payment record  ==
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>
+
Include a function to the service program that uses the '''add''' statement to insert a new row to the database.
  
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_get.html get considerations for SQL]
+
To code the function:
  
== Replace a record ==
+
*In the EGL editor, copy and paste the following lines into '''SQLService.egl''' before the logging functions:
  
The <tt>editPayment</tt> function replaces an
+
  function addPayment(newPayment paymentRec in)
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).
+
        logEntry ( "addPayment" )&nbsp;;
 +
        try
 +
            add newPayment to ds&nbsp;;
 +
        onException(ex sqlException)
 +
            logException(ex);
 +
        end
 +
  end
  
To
+
*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 &gt; Organize Imports'''.
code the function:
+
  
<ol><li>In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before
+
:EGL adds the following statement to the beginning of the file:
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>
+
  import records.paymentRec;
  
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_replace.html replace considerations for SQL]
+
:The reference is now resolved. You will use this feature often, whether by selecting the menu item or by pressing Ctrl-Shift-O.
  
== Delete a record ==
+
*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" )&nbsp;;
 +
        try
 +
            add newPayment to ds
 +
                with #sql{
 +
                        insert into PAYMENT
 +
                              (CATEGORY, DESCRIPTION, AMOUNT, FIXED_PAYMENT, DUE_DATE,
 +
                              PAYEE_NAME, PAYEE_ADDRESS1, PAYEE_ADDRESS2)
 +
                        values
 +
                              (?,&nbsp;?,&nbsp;?,&nbsp;?,&nbsp;?,&nbsp;?,&nbsp;?,&nbsp;?)
 +
                  };
 +
        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.
  
The <tt>deletePayment</tt> function deletes the
+
'''Related reference '''<br>
specified record from the table.
+
  
To code the function:
+
*Help topic: add considerations for SQL<br>
 +
*Help topic: Functions<br>
 +
*Help topic: import<br>
 +
*Help topic: SQL data access<br>
  
 +
== Read all database records  ==
  
<ol><li>In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before
+
The <code>getAllPayments</code> function uses the get statement with an array object to read all of the records from the table and stores them in an array.  
the '''end''' statement:
+
  
function deletePayment(delPayment paymentRec inOut)
+
To code the function:
  
try
+
*In the EGL editor, copy and paste the following lines into '''SQLService.egl''' before the logging functions:
  delete delPayment nocursor;
+
  
   onException(exception SQLException)
+
   function getAllPayments() returns(paymentRec[])
      if(SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)
+
      payments paymentRec[];
        throw exception;
+
      logEntry("getAllPayments");
      end
+
      try
 +
          get payments from ds;
 +
      onException(ex sqlException)
 +
          logException(ex);
 +
      end
 +
      return(payments);
 
   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
+
: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 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
+
*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.  
an exception, the service requester receives an exception of type
+
*Save the file.
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.
+
'''Related reference '''<br>
</ol>
+
  
''' Related reference '''<br>
+
*Help topic: get considerations for SQL<br>
  
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_delete.html delete considerations for SQL]
+
== Replace a record  ==
[../../com.ibm.egl.lr.doc/topics/regl_core_xcpt.html Exception handling]
+
  
== Create test data ==
+
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 <tt>createDefaultTable</tt> function creates
+
  function editPayment(chgPayment paymentRec in)
a set of data for testing your completed application.
+
      logEntry("editPayment");
 +
      try
 +
          replace chgPayment to ds;
 +
      onException(ex SQLException)
 +
          logException(ex);
 +
      end
 +
  end
  
To code
+
The EGL '''replace''' statement generates an SQL UPDATE statement.
the function:
+
  
 +
*Save the file.
  
<ol><li>In the EGL editor, copy and paste the following lines into <tt>SQLService.egl</tt> before
+
'''Related reference '''<br>
the '''end''' statement:
+
  
function createDefaultTable() returns (paymentRec[])
+
*Help topic: replace considerations for SQL<br>
  
  try
+
== Delete a record  ==
      execute #sql{
+
        delete from PAYMENT
+
      };
+
  
  onException(exception SQLException)
+
The '''deletePayment''' function deletes the specified record from the table.
  
      if (SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)
+
To code the function:
        throw exception;
+
      end
+
  end;
+
  
  ispDate DATE = dateTimeLib.dateValueFromGregorian(20110405);
+
*In the EGL editor, copy and paste the following lines into '''SQLService.egl''' before the logging functions:
  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:
+
  function deletePayment(delPayment paymentRec in)
 +
      logEntry("deletePayment");
 +
      try
 +
          delete delPayment from ds;
 +
      onException(ex SQLException)
 +
          if(ex.SQLState&nbsp;!= "02000") // sql state is five digits
 +
              logException(ex);
 +
          end
 +
      end
 +
  end
  
<ul><li>The EGL '''execute''' statement runs a literal
+
*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,&nbsp;processes, in some onException logic.
SQL statement that deletes all rows from the PAYMENT table.
+
*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".  
<li>The <tt>ispDate</tt> variable receives a date value from
+
*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.  
the '''dateTimeLib.dateValueFromGregorian()''' system
+
*Save the file.
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
+
'''Related reference '''<br>
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>
+
*Help topic: delete considerations for SQL<br>
 +
*Help topic: Exception handling<br>
  
[../../com.ibm.egl.lr.doc/topics/regl_data_sql_execute.html execute considerations for SQL]
+
== Create test data  ==
[../../com.ibm.egl.lr.doc/topics/regl_core_date_date_value_greg.html dateValueFromGregorian()]
+
  
== Lesson checkpoint ==
+
The '''createDefaultTable''' function creates a set of data for testing your completed application.
  
You learned how to complete the following tasks:
+
To code the function:  
  
<ul><li>Add embedded SQL code to a program and modify that code
+
*In the EGL editor, copy and paste the following lines into '''SQLService.egl''' before the logging functions:
<li>Automatically create and organize '''import''' statements
+
</ul>
+
  
In the next lesson, you will create a widget to hold the table
+
  function createDefaultTable() returns(paymentRec[])
of expense data.
+
      payments paymentRec[];
 +
      logEntry("createDefaultTable");
 +
      try
 +
          try
 +
              execute from ds
 +
                  with #sql{
 +
                    delete from PAYMENT
 +
              };
 +
        onException(ex SQLException)
 +
            if(ex.SQLState&nbsp;!= "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 <code>dueDate</code> 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.
  
{| style="float: right"
+
*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 SQLService.egl after lesson 6]]  
|[[EDT:Tutorial: RUI With DataBase Lesson 5|&lt; Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 7|Next >]]
+
*Save and close the file.
|}
+
  
 +
'''Related reference '''<br>
 +
 +
*Help topic: execute considerations for SQL<br>
 +
*Help topic: 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.
 +
 +
{| style="float: right" class="FCK__ShowTableBorders"
 +
|-
 +
| [[EDT:Tutorial: RUI With DataBase Lesson 5|&lt; Previous]] &#124; [[EDT:Tutorial: RUI With DataBase Lesson 7|Next &gt;]]
 +
|}
  
 
[[Category:EDT]]
 
[[Category:EDT]]

Latest revision as of 09:30, 12 April 2012

Access a database with EGL Rich UI


< Previous | Next >

Contents

[edit] 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.

[edit] 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.

  1. 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.Deployment descriptor property for service project.
  2. In the PaymentService project, right-click the PaymentService.egldd file and open the file with the EGL Deployment Descriptor editor.
    Open deployment descriptor for service project.
  3. Select the Resource Bindings tab.
  4. Add a new resource binding named Derby of binding type SQL database binding.
  5. If you are using EDT 0.7.0, 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.
  6. If you are using EDT 0.8.0, you will want to select radio button Reference the selected workspace connection below (retrieved at runtime). When the application is deployed to the application server, the connection will be accessed at runtime using a JNDI name as a resource reference. If you deploy to an Apache Tomcat Server, EGL deployment uses both the JNDI name and the connection details to create the JNDI <resource-ref> entry in the WEB-INF\web.xml file and the <resource> entry in the META-INF\context.xml file that will be used at run time. The only additional thing you need to do is make sure the derbyclient.jar file is copied to the Tomcat lib directory.
  7. Select Derby connection details.
  8. Clink on Finish.Add SQL binding to deployment descriptor.
  9. Close file PaymentServices.egldd

[edit] Using an SQL resource binding in service program

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

  • For EDT .7:
package services;

service SQLService
  ds SQLDataSource? { @Resource { bindingKey="Derby" } } ;    // EDT 0.7.O syntax
end


  • For EDT .8:
package services;

service SQLService
  ds SQLDataSource? { @Resource { uri="binding:Derby" } } ;   // EDT 0.8.O syntax
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.

[edit] 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:

    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


[edit] 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 logging functions:
  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 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:
  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

  • Help topic: add considerations for SQL
  • Help topic: Functions
  • Help topic: import
  • Help topic: SQL data access

[edit] 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:
  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

  • Help topic: get considerations for SQL

[edit] 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

  • Help topic: replace considerations for SQL

[edit] 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 logging functions:
  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(ex);
          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

  • Help topic: delete considerations for SQL
  • Help topic: Exception handling

[edit] 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:
  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.

Related reference

  • Help topic: execute considerations for SQL
  • Help topic: dateValueFromGregorian()

[edit] 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 >