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 in the next sections access a database table that is defined | + | Much of the code in the next sections access a database table that is defined here:<br> |
CREATE TABLE PAYMENT ( | CREATE TABLE PAYMENT ( | ||
PAYMENT_ID INT | PAYMENT_ID INT | ||
| − | + | PRIMARY KEY NOT NULL | |
| − | + | GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), | |
CATEGORY INT, | CATEGORY INT, | ||
DESCRIPTION CHAR(30), | DESCRIPTION CHAR(30), | ||
| Line 15: | Line 15: | ||
The sections:<br> | The sections:<br> | ||
| + | |||
| + | * [[#Following_the_typical_pattern|Following the typical pattern ]] | ||
| + | * [[#Defining_a_Record_type|Defining a Record type]] | ||
| + | * [[#Getting_a_row|Getting a row]] | ||
| + | * [[#Getting_multiple_rows_with_one_EGL_statement|Getting multiple rows with one EGL statement]] | ||
<br> | <br> | ||
| Line 22: | Line 27: | ||
Here is the typical pattern for interacting with a relational database: | 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. <br> | + | *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. <br> |
*Declare a variable that is based on the defined type. Your code will copy data between the database and that variable.<br> | *Declare a variable that is based on the defined type. Your code will copy data between the database and that variable.<br> | ||
*Declare variables to represent the SQL data sources. Each data source is a connection or a result set. | *Declare variables to represent the SQL data sources. Each data source is a connection or a result set. | ||
Revision as of 16:43, 10 February 2012
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.
- 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.
Note: To retrieve details for a Record type from a DBMS, create an EGL project and do as follows:
- In Project Explorer, right-click.
- Click New > Record and begin using the the New EGL Record wizard.
- On the first wizard page, selecting the Records from SQL Database template.
- Complete the steps required by the wizard.
Defining a record
// 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