/*
 * 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

   mypayment PaymentRec;
mypayment.description = "shoes";
mypayment.amount = 123.50;

ds SQLDataSource?{@Resource};

function addPayment(newPayment PaymentRec in)
   try
      add newPayment to ds;
      onException(ex sqlException)
 
         // invokes a custom exception handler.
         logException(ex);        
    end
end

 
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

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

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

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