Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
EDT:Working with a database
This page contains code snippets for database access.
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),
Contents
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 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
In either case, the GeneratedValue annotation in the Record type has the following effect: the add statement places a DBMS-generated value into the PAYMENT_ID column of a new row and into the paymentID
field of the record being added.
Getting a row
The following code shows alternative ways to get a record:
function getPayment(someId Int in} returns (PaymentRec) aPayment PaymentRec; // retrieves a table row that has a key value of someID // and places the content of that row in the customer record. get aPayment from ds using(someId); // alternative that shows the customizable SQL code. get aPayment from ds using(someId) with #sql{ SELECT * FROM PAYMENT WHERE PAYMENT_ID = ? }; // a future alternative (not yet scheduled) // declares a dictionary and creates a key/value pair // for each item of column data retrieved from the database. vals Dictionary; get vals from ds using(someId) with #sql{ SELECT * FROM PAYMENT WHERE PAYMENT_ID = ? }; return (aPayment); end
Getting multiple rows with one statement
The following code shows alternative ways to get a list of records:
function getPayments(} returns (PaymentRec[]) // declares a new list of payment records. payments PaymentRec[]; // retrieves the data from each row into any element in the list. get payments from ds; // alternative that retrieves a subset of rows. get payments from ds with #sql { SELECT * FROM PAYMENT WHERE STATE = 'CO' }; // alternative that includes a parameter in the query. state String = "CO"; get payments from ds using(state) with #sql { SELECT * FROM PAYMENT WHERE STATE = ? }; end
Looping through an SQL result set
The following code opens an SQL cursor and loops through the results.
function loopPayment() // declare a new data source ds SQLDataSource?{@resource{uri = "binding:myDB" }}; // .8 syntax // declares a new result set rs SQLResultSet?; // uses the specified SQL query to open that result set open rs from ds with #sql{ SELECT * FROM PAYMENT }; myPayment PaymentRec; // loops through the results and // writes the payment details to the standard output. while(rs.getNext()) get myPayment from rs; Syslib.writeStdOut ("Payment purpose and amount: " + myPayment.description + " (" + myPayment.amount + ")"); 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 Record type details from a database management system.
♦ Code snippets main page