Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EDT:Working with a database"

(→‎Following the typical pattern)
Line 50: Line 50:
 
End  
 
End  
 
</source>  
 
</source>  
 +
 +
= '''Adding a row'''  =
 +
 +
<source lang="java">
 +
function addCustomer{customer Customer in}
 +
    ds SQLDataSource?{@resource {}};
 +
    add customer to ds;
 +
end
 +
</source>
 +
 +
 +
  
 
= '''Getting a row'''  =
 
= '''Getting a row'''  =
Line 99: Line 111:
 
</source>  
 
</source>  
  
−
= '''Adding a row'''  =
 
−
 
−
<source lang="java">
 
−
function addCustomer{customer Customer in}
 
−
    ds SQLDataSource?{@resource {}};
 
−
    add customer to ds;
 
−
end
 
−
</source>
 
  
 
= '''Looping through an SQL result set'''  =
 
= '''Looping through an SQL result set'''  =

Revision as of 19:04, 12 February 2012

Much of the code on this page accesses a database table that is defined as follows:

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


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" } };
   category int { @Column { name="CATEGORY" } } ;
   description string? { @Column { name="DESCRIPTION" } } ;	
   amount decimal(10,2) { @Column { name="AMOUNT" } } ;
   fixedPayment boolean { @Column { name="FIXED_PAYMENT" } } ;
   dueDate date { @Column { name="DUE_DATE" } } ;		
   payeeName string { @Column { name="PAYEE_NAME" } } ;
   payeeAddress1 string { @Column { name="PAYEE_ADDRESS1" } } ;
   payeeAddress2 string { @Column { name="PAYEE_ADDRESS2" } } ;
End

Adding a row

function addCustomer{customer Customer in}
    ds SQLDataSource?{@resource {}};
    add customer to ds;
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.

Retrieved from "https://wiki.eclipse.org/index.php?title=EDT:Working_with_a_database&oldid=289749"

Back to the top