In this lesson, you add functions that access rows in a relational database. Add the functions in order, before the final end statement in SQLService.egl.
The addPayment() function adds a new row to the database.
To code the function:
function addPayment(newPayment paymentRec inOut)
add newPayment;
end

import records.paymentRec;
The
reference is now resolved. You will use this feature often, whether
by selecting the menu item or by pressing Ctrl-Shift-O.


The getAllPayments function reads all of the records from the table and stores them in an array.
To code the function:
function getAllPayments() returns (paymentRec[])
paymentArray paymentRec[];
get paymentArray;
return (paymentArray);
end
The EGL get statement generates
an SQL SELECT statement to retrieve a result set. When the target
of the get statement is a dynamic array
of records, EGL retrieves all matching rows from the result set and
inserts each successive row into the next array element.The editPayment function replaces an existing row in the database with an edited version. The function assumes that the user previously read the row from the database.
To code the function:
function editPayment(chgPayment paymentRec inOut)
replace chgPayment nocursor;
end
The EGL replace statement
generates an SQL UPDATE statement. The deletePayment function deletes the specified record from the table.
To code the function:
function deletePayment(delPayment paymentRec inOut)
try
delete delPayment nocursor;
onException(exception SQLException)
if(SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)
throw exception;
end
end
end
The EGL delete statement generates
an SQL DELETE statement. If no rows are present, the Derby database
returns an SQLState value of "02000", and the EGL runtime code throws
an exception that the function catches: that is, processes
in some onException logic. When a function catches but ignores an exception, processing continues without interruption. That rule applies to the preceding logic, when the value of SQLState is "02000". When a function uses the throw statement to throw an exception, the exception stays active. That rule also applies to the preceding logic, when the value of SQLState is other than "02000".
At run time, if a service does not handle an exception, the service requester receives an exception of type ServiceInvocationException. Incidentally, if the service cannot be accessed, the requester receives an exception of type ServiceInvocationException or ServiceBindingException, depending on the details of the error.
The createDefaultTable function creates a set of data for testing your completed application.
To code the function:
function createDefaultTable() returns (paymentRec[])
try
execute #sql{
delete from PAYMENT
};
onException(exception SQLException)
if (SQLLib.sqlData.sqlState != "02000") // sqlState is of type CHAR(5)
throw exception;
end
end;
ispDate DATE = dateTimeLib.dateValueFromGregorian(20110405);
addPayment(new paymentRec{category = 1, description = "Apartment",
amount = 880, fixedPayment = YES});
addPayment(new paymentRec{category = 2, description = "Groceries",
amount = 450, fixedPayment = NO});
addPayment(new paymentRec{category = 5, description = "ISP",
amount = 19.99, fixedPayment = YES, dueDate = ispDate });
return (getAllPayments());
end
In the next lesson, you will create a widget to hold the table of expense data.