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
Much of the code in the next sections access a database table that is defined here:
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) );
The sections:
- Following the typical pattern
- Defining a Record type
- Getting a row
- Getting multiple rows with one EGL statement
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 use a Workbench capability to retrieve details from a database management system (DBMS).
- 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 variables that you declared. For each statement, rely on the SQL code that is provided for you or customize that code.
For details on how to connect to a database, see SQL database bindings
Note: To define a Record type that matches details stored in a DBMS, create an EGL project and do as follows:
- In Project Explorer, right-click.
- At the displayed menu, click New > Record.
- On the first wizard page, select the Records from SQL Database template.
- Complete the steps required by the wizard.
Defining a Record type
// record is bound to the CUSTOMER table (or view) Record CUSTOMER type Entity{@table{name = "CUSTOMER"}} CUSTID string{@id}; // primary key field NAME string; COUNTRY string; STATE string?; // nullable field 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
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
Adding a row
function addCustomer{customer Customer in} ds SQLDataSource?{@resource {}}; add customer to ds; end
Looping through a 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