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.
Difference between revisions of "EDT:Working with a database"
Line 1: | Line 1: | ||
− | Much of the code on this page accesses a database table that | + | Much of the code on this page accesses a database table that has the following definition:<br> |
<pre>CREATE TABLE PAYMENT ( | <pre>CREATE TABLE PAYMENT ( | ||
PAYMENT_ID INT | PAYMENT_ID INT | ||
Line 40: | Line 40: | ||
Record PaymentRec { @Table {name="PAYMENT"} } | Record PaymentRec { @Table {name="PAYMENT"} } | ||
paymentId int {@ID, @GeneratedValue, @Column { name="PAYMENT_ID" } }; | paymentId int {@ID, @GeneratedValue, @Column { name="PAYMENT_ID" } }; | ||
− | |||
description string? { @Column { name="DESCRIPTION" } } ; | description string? { @Column { name="DESCRIPTION" } } ; | ||
amount decimal(10,2) { @Column { name="AMOUNT" } } ; | amount decimal(10,2) { @Column { name="AMOUNT" } } ; | ||
− | |||
− | |||
− | |||
− | |||
− | |||
End | End | ||
</source> | </source> | ||
− | = ''' | + | = <br><br><br>'''Declaring variables''' = |
+ | |||
+ | You might declare a new payment record as follows: | ||
<source lang="java"> | <source lang="java"> | ||
− | + | mypayment PaymentRec; | |
− | + | mypayment.description = "shoes"; | |
− | + | mypayment.amount = 123.50; | |
− | + | </source> | |
− | </source> | + | |
+ | The variable declared next refers to database-connection details that are stored in an EGL deployment descriptor: | ||
+ | <source lang="java"> | ||
+ | ds SQLDataSource?{@resource {}}; | ||
+ | </source> | ||
+ | For a review of the EGL technology for connecting to a database, see | ||
+ | [[EDT:Resource Binding Databases|SQL database bindings]]. | ||
+ | |||
+ | = '''Adding a row''' = | ||
+ | |||
+ | The following function accepts a record and inserts the content into the database: | ||
+ | |||
+ | <source lang="java"> | ||
+ | function addPayment(newPayment PaymentRec in) | ||
+ | try | ||
+ | add newPayment to ds ; | ||
+ | onException(ex sqlException) | ||
+ | |||
+ | // invokes a custom exception handler | ||
+ | logException(ex); | ||
+ | end | ||
+ | end | ||
+ | </source> | ||
= '''Getting a row''' = | = '''Getting a row''' = |
Revision as of 20:09, 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) );
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 variable declared next refers to database-connection details that are stored in an EGL deployment descriptor:
ds SQLDataSource?{@resource {}};
For a review of the EGL technology for connecting 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.