Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EDT:Working with a database"

(Adding a row)
(Getting a row)
Line 96: Line 96:
 
<source lang="java">
 
<source lang="java">
  
function getCustomer(someId String in} returns (Customer)
+
function getPayment(someId String in} returns (Customer)
     ds SQLDataSource?{@resource {}};                      // declares a data source that will use binding name "ds" (since a name is not specified)
+
 
 +
     // declares a data source that will use binding name "ds";
 +
    // (since a name is not specified)
 +
    ds SQLDataSource?{@resource {}};                     
  
 
     aCust Customer;                                        // declares and empty customer record
 
     aCust Customer;                                        // declares and empty customer record

Revision as of 20:44, 12 February 2012

Much of the code on this page accesses a database table that has the following definition:

CREATE TABLE PAYMENT ( 
   PAYMENT_ID INT 
   PRIMARY KEY NOT NULL 
   GENERATED ALWAYS AS IDENTITY 
      (START WITH 1, INCREMENT BY 1), 	
   DESCRIPTION CHAR(30), 
   AMOUNT DECIMAL(10,2), 


Following the typical pattern

Here is the typical pattern for interacting with a relational database:

  • Define a Record, Handler, or external type and include annotations. For a Record type, you might retrieve details automatically from a database management system. 
  • Declare a variable that is based on the defined type. Your code will copy data between the database and that variable.
  • Declare variables to represent the SQL data sources. Each data source is a connection or a result set.
  • Configure EGL statements that reference the variables that you declared. For each statement, rely on the SQL code that is provided for you or customize that code.




Defining a Record type

/*
 * The annotations help to define the default SQL code when  
 * a record of this type is used in a given EGL statement:
 *     -- Table refers to the table being accessed. If Table is not supplied,
 *        the name of the Record type is assumed to be the table name.
 *     -- Column refers to the column being accessed. If Column is not supplied, 
 *        the name of the field is assumed to be the name of the column.
 *     -- ID indicates that the field and its value are set to equal 
 *        in the default SQL INSERT statement (for an EGL add statement) and
 *        in the default SELECT statement (for an EGL get or open statement). 
 */
Record PaymentRec  { @Table {name="PAYMENT"} } 
   paymentId int {@ID, @GeneratedValue, @Column { name="PAYMENT_ID" } };
   description string? { @Column { name="DESCRIPTION" } } ;	
   amount decimal(10,2) { @Column { name="AMOUNT" } } ;
End




Declaring variables

You might declare a new payment record as follows:

   mypayment PaymentRec;
mypayment.description = "shoes";
mypayment.amount = 123.50;

The next declaration lets your code open a database connection at the first database access and in accordance with connection details that are stored in an EGL deployment descriptor:

ds SQLDataSource?{@resource {}};

For details on how to connect to a database, see SQL database bindings.

Adding a row

The following function uses the previous data-source declaration, accepts a record, and inserts the content of that record into the database:

function addPayment(newPayment PaymentRec in)
   try
      add newPayment to ds;
      onException(ex sqlException)
 
         // invokes a custom exception handler
         logException(ex);        
    end
end

Here is a statement variation that shows the SQL code you can customize:

 
function addPayment(newPayment PaymentRec in)
   try
      add newPayment to ds
      with #sql{
          insert into PAYMENT
             (DESCRIPTION, AMOUNT)
          values
             (?, ?)
      };
      onException(ex sqlException)
          logException(ex);
   end
end

Getting a row

Here is code for getting a record:

function getPayment(someId String in} returns (Customer)
 
    // declares a data source that will use binding name "ds"; 
    // (since a name is not specified)
    ds SQLDataSource?{@resource {}};                       
 
    aCust Customer;                                        // declares and empty customer record
    get aCust from ds using(someId);                       // gets the record in the table that has a key value of someID and populates the customer record
 
    get aCust from ds using(someId) with #sql{             // alternative approach for getting a single record (allows for customizing the SQL)
	SELECT *
	FROM CUSTOMER
	WHERE id = ?
    };
 
    vals Dictionary;                                       // declares a new dictionary
    get vals from ds using(someId) with #sql{              // alternative approach for getting a single record (does not require a Record definition)
	SELECT *
	FROM CUSTOMER
	WHERE id = ?
     };                                                    // creates a key/value pair in the dictionary for each column in the result set
 
    return (aCust);
end

Getting multiple rows with one EGL statement

function getCustomer(} returns (Customer[])
    ds SQLDataSource?{@resource {}};                       // declares a data source that will use binding name "ds" (since a name is not specified)
 
    custs Customer[];                                      // declares a new dynamic array of customer records
    get custs from ds;                                     // populates the array with a Customer record for each row in the result set
 
    get custs from ds with #sql {
       select * from customer where state = 'CO'
    };                                                     // populates the array, but with a limited set of Customer records
 
    state String = "CO";
    get custs from ds using(state) with #sql {
       select * from customer where state = ?
    };                                                     // parameterized version of the previous example
 
 
end


Looping through an SQL result set

 function loopCust()
     ds SQLDataSource?{@resource{uri = "binding:myDB" }};       // declares a new data source 
     rs SQLResultSet?;                                          // declares a new result set
 
     open rs from ds with #sql{                                 // opens a result set using the specified SQL query
	SELECT * FROM CUSTOMER
     };
 
     myCust Customer;
 
     //Loop through results and write out customer name
     while(rs.getNext())
        get myCust from rs;
        Syslib.writeStdOut ("Customer name: " + myCust.name);
     end
 
 end

For More Information

For details on how to connect to a database, see SQL database bindings.

For a keystroke-by-keystroke tutorial, see Access a database with EGL Rich UI. The third lesson shows how to retrieve details for a Record type from a database management system.

Back to the top