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

From Eclipsepedia

Jump to: navigation, search
(Declaring variables)
 
(45 intermediate revisions by 2 users not shown)
Line 1: Line 1:
Much of the code on this page accesses a database table that is defined as follows:<br>  
+
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>  
 
<pre>CREATE TABLE PAYMENT (  
 
<pre>CREATE TABLE PAYMENT (  
 
   PAYMENT_ID INT  
 
   PAYMENT_ID INT  
Line 5: Line 7:
 
   GENERATED ALWAYS AS IDENTITY  
 
   GENERATED ALWAYS AS IDENTITY  
 
       (START WITH 1, INCREMENT BY 1),
 
       (START WITH 1, INCREMENT BY 1),
  CATEGORY INT,
 
 
   DESCRIPTION CHAR(30),  
 
   DESCRIPTION CHAR(30),  
 
   AMOUNT DECIMAL(10,2),  
 
   AMOUNT DECIMAL(10,2),  
  FIXED_PAYMENT SMALLINT,
+
</pre>  
  DUE_DATE DATE,
+
  PAYEE_NAME CHAR(30),
+
  PAYEE_ADDRESS1 CHAR(30),
+
  PAYEE_ADDRESS2 CHAR(30)  );</pre>  
+
 
<br>  
 
<br>  
  
Line 19: Line 16:
 
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.&nbsp; <br>  
+
*Define a Record, Handler, or external type and include annotations. For a Record type, you might retrieve details automatically from a database management system.&nbsp; <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.  
*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.
+
*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.
  
 
= <br><br><br>'''Defining a Record type'''  =
 
= <br><br><br>'''Defining a Record type'''  =
Line 40: Line 37:
 
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" } };
  category int { @Column { name="CATEGORY" } } ;
 
 
   description string? { @Column { name="DESCRIPTION" } } ;
 
   description string? { @Column { name="DESCRIPTION" } } ;
 
   amount decimal(10,2) { @Column { name="AMOUNT" } } ;
 
   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  
 
End  
 
</source>  
 
</source>  
  
= '''Getting a row'''  =
+
= <br><br><br>'''Declaring variables'''  =
 +
 
 +
You might declare a new payment record as follows:
 +
<source lang="java">  mypayment PaymentRec;
 +
mypayment.description = "shoes";
 +
mypayment.amount = 123.50;
 +
</source>
 +
 
 +
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:
  
 
<source lang="java">
 
<source lang="java">
function getCustomer(someId String in} returns (Customer)
+
ds SQLDataSource?{@Resource};                       
    ds SQLDataSource?{@resource {}};                      // declares a data source that will use binding name "ds" (since a name is not specified)
+
</source>
  
    aCust Customer;                                        // declares and empty customer record
+
For details on how to connect to a database, see
    get aCust from ds using(someId);                      // gets the record in the table that has a key value of someID and populates the customer record
+
[[EDT:Resource Binding Databases|SQL database bindings]].
  
    get aCust from ds using(someId) with #sql{            // alternative approach for getting a single record (allows for customizing the SQL)
+
= '''Adding a row'''  =
SELECT *
+
FROM CUSTOMER
+
WHERE id = ?
+
    };
+
  
    vals Dictionary;                                      // declares a new dictionary
+
The following function uses the previous data-source declaration, accepts a record, and inserts the content of that record into the database:
    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);
+
<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
 
end
</source>  
+
</source>
  
= '''Getting multiple rows with one EGL statement'''  =
+
Here is a variation that shows the SQL code you can customize:
 +
<source lang="java">
 +
 +
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
 +
</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'''  =
  
 +
The following code shows alternative ways to get a record:
 
<source lang="java">
 
<source lang="java">
function getCustomer(} returns (Customer[])
+
function getPayment(someId Int in} returns (PaymentRec)
    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
+
  aPayment PaymentRec;                                  
    get custs from ds;                                    // populates the array with a Customer record for each row in the result set
+
  
    get custs from ds with #sql {
+
  // retrieves a table row that has a key value of someID
      select * from customer where state = 'CO'
+
  // and places the content of that row in the customer record.
    };                                                    // populates the array, but with a limited set of Customer records
+
  get aPayment from ds using(someId);                     
  
    state String = "CO";
+
  // alternative that shows the customizable SQL code.
    get custs from ds using(state) with #sql {
+
  get aPayment from ds using(someId) with #sql{            
      select * from customer where state = ?
+
      SELECT *
    };                                                     // parameterized version of the previous example
+
      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
 
end
</source>  
+
</source>
  
= '''Adding a row'''  =
+
= '''Getting multiple rows with one statement'''  =
  
 +
The following code shows alternative ways to get a list of records:
 
<source lang="java">
 
<source lang="java">
function addCustomer{customer Customer in}
+
function getPayments(} returns (PaymentRec[])
     ds SQLDataSource?{@resource {}};
+
 
     add customer to ds;
+
  // 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
 
end
</source>  
+
</source>
  
 
= '''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 define a Record type to match details that are stored in 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