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 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;
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.