Jump to: navigation, search

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

(Declaring variables)
Line 55: Line 55:
 
</source>
 
</source>
  
The variable declared next refers to database-connection details that are stored in an EGL deployment descriptor:  
+
The next declaration refers to database-connection details that are stored in an EGL deployment descriptor:  
  
 
<source lang="java">
 
<source lang="java">
Line 61: Line 61:
 
</source>
 
</source>
  
For a review of the EGL technology for connecting to a database, see  
+
For a description of how to connect to a database, see  
 
[[EDT:Resource Binding Databases|SQL database bindings]].
 
[[EDT:Resource Binding Databases|SQL database bindings]].
  

Revision as of 20:10, 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), 	
   CATEGORY INT, 
   DESCRIPTION CHAR(30), 
   AMOUNT DECIMAL(10,2), 
   FIXED_PAYMENT SMALLINT, 
   DUE_DATE DATE, 
   PAYEE_NAME CHAR(30), 
   PAYEE_ADDRESS1 CHAR(30), 	
   PAYEE_ADDRESS2 CHAR(30)  );


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 refers to database-connection details that are stored in an EGL deployment descriptor:

   ds SQLDataSource?{@resource {}};

For a description of how to connect to a database, see SQL database bindings.

Adding a row

The following function accepts a record and inserts the content 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

Getting a row

function getCustomer(someId String in} returns (Customer)
    ds SQLDataSource?{@resource {}};                       // declares a data source that will use binding name "ds" (since a name is not specified)
 
    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.