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

From Eclipsepedia

Jump to: navigation, search
(For More Information)
(Declaring variables)
 
(15 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
This page contains code snippets for database access.
 +
 
Much of the code on this page accesses a database table that has the following definition:<br>  
 
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 (  
Line 51: Line 53:
  
 
<source lang="java">
 
<source lang="java">
ds SQLDataSource?{@Resource {}};                       
+
ds SQLDataSource?{@Resource};                       
 
</source>
 
</source>
  
Line 67: Line 69:
 
       onException(ex sqlException)
 
       onException(ex sqlException)
  
         // invokes a custom exception handler
+
         // invokes a custom exception handler.
 
         logException(ex);         
 
         logException(ex);         
 
     end
 
     end
Line 73: Line 75:
 
</source>
 
</source>
  
Here is a statement variation that shows the SQL code you can customize:  
+
Here is a variation that shows the SQL code you can customize:  
 
<source lang="java">
 
<source lang="java">
 
   
 
   
Line 90: Line 92:
 
end
 
end
 
</source>
 
</source>
 +
 +
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 <code>paymentID</code> field of the record being added.
  
 
= '''Getting a row'''  =
 
= '''Getting a row'''  =
Line 103: Line 107:
 
   get aPayment from ds using(someId);                       
 
   get aPayment from ds using(someId);                       
  
   // alternative that shows the customizable SQL code
+
   // alternative that shows the customizable SQL code.
 
   get aPayment from ds using(someId) with #sql{             
 
   get aPayment from ds using(someId) with #sql{             
 
       SELECT *
 
       SELECT *
Line 110: Line 114:
 
   };
 
   };
  
   // future alternative that declares a dictionary
+
   // a future alternative (not yet scheduled)
   // and creates a key/value pair  
+
   // declares a dictionary and creates a key/value pair  
   // for each item of column data retrieved from the database
+
   // for each item of column data retrieved from the database.
 
   vals Dictionary;                       
 
   vals Dictionary;                       
 
   get vals from ds using(someId) with #sql{               
 
   get vals from ds using(someId) with #sql{               
Line 152: Line 156:
 
= '''Looping through an SQL result set'''  =
 
= '''Looping through an SQL result set'''  =
  
 +
The following code opens an SQL cursor and loops through the results.
 
<source lang="java">
 
<source lang="java">
function loopCust()
+
function loopPayment()
    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
+
  // declare a new data source
SELECT * FROM CUSTOMER
+
  ds SQLDataSource?{@resource{uri = "binding:myDB" }};     // .8 syntax
    };
+
  
    myCust Customer;
+
  // declares a new result set
 +
  rs SQLResultSet?;                                        
  
    //Loop through results and write out customer name
+
  // uses the specified SQL query to open that result set
    while(rs.getNext())
+
  open rs from ds with #sql{                               
        get myCust from rs;
+
      SELECT * FROM PAYMENT
        Syslib.writeStdOut ("Customer name: " + myCust.name);
+
  };
    end
+
  
end
+
  myPayment PaymentRec;
</source>
+
  
= For More Information =
+
  // 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
 +
</source>
 +
 
 +
= For more information =
  
 
For details on how to connect to a database, see [[EDT:Resource Binding Databases|SQL database bindings]].<br>  
 
For details on how to connect to a database, see [[EDT:Resource Binding Databases|SQL database bindings]].<br>  
  
 
For a keystroke-by-keystroke tutorial, see [[EDT:Tutorial: Access a database with EGL Rich UI|Access a database with EGL Rich UI]]. The third lesson shows how to retrieve Record type details from a database management system.
 
For a keystroke-by-keystroke tutorial, see [[EDT:Tutorial: Access a database with EGL Rich UI|Access a database with EGL Rich UI]]. The third lesson shows how to retrieve Record type details from a database management system.
 +
<br>
 +
<br><br>
 +
♦ [[EDT:Code_snippets|Code snippets main page]] <br>
 +
 +
 +
[[Category:EDT]]

Latest revision as of 21:27, 13 March 2012

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

[edit] 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.

[edit]


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

[edit]


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.

[edit] 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.

[edit] 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

[edit] 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

[edit] 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

[edit] 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