Accessing a relational database

To access a relational database, you declare one or both of two types of data sources and then use the data source or sources in action statements. Although EGL offers default processing for many kinds of database access, you can write statements in SQL to handle the most complex requirements.

The types of data sources are as follows:
You can introduce yourself to the EGL support for SQL by following the examples in this topic, starting with the following definitions:
// The Payment type has the Table annotation, which specifies the table name. 
Record Payment {@Table {name = "Payment_Table"}}

   // The first field in Payment has two annotations:
   // GeneratedValue means that the database management system 
   // sets the value of the column that corresponds to the first field.
   // ID means that when a record based on the Record type is used in 
   // an action statement such as add, the field is treated as a key field 
   // for the purpose of creating the default SQL statement.
   payment_ID Int {@GeneratedValue, @ID};

   category Int;
   description String;
   amount Decimal(10,2);	
   dueDate Date;
End

// declare a variable that references connection details
connectionURL String = "jdbc:derby:C:\\databases\\PaymentDB;create=true";
ds SQLDataSource = new SQLDataSource(connectionURL);	   

// declare a variable that references a result set
rs SQLResultSet?;

// declare a record that is based on the Record type shown earlier
myPayment Payment;

Add statement examples

The add statement adds a single row to a database table. The value being added is in one of two categories:
  • A record, handler, or external type; or
  • A list of values, each of which corresponds to a table column.

For example, the following code adds four rows to the database table:

// assign values for use with the add statement
myPayment.category    = 5;
myPayment.description = "Goods";
myPayment.amount      = 10.50;
myPayment.due_date    = "01/25/2012";

// to add the first row, use the entity
add myPayment to ds;

// to add the second row, use a set of simple values, 
// and make the SQL INSERT statement explicit
add 6, "Services", 12.00, myPayment.due_date to ds with
   #sql{
      insert into Payment
         (category, description, amount, due_date)
      values
         (?, ? , ?, ?)
      };

// to add the third row, set the entity fields, use them as simple values
// in the add statement, and update the SQL INSERT statement.
myPayment.category = 7;
myPayment.description = "Repeated payment";
myPayment.amount = 18000.00;

add myPayment.category, myPayment.description, myPayment.amount to ds with
   #sql{
      insert into Payment
         (category, description, amount)
      values
         (?, ? , ?)
      };

// That last code assumes that the date column in the table is nullable 
// and without a default value. In that case, the statement adds a row 
// that includes a null for the date column.