Working
with Databases
Here is a brief overview of the Verj.io entities used to provide database support.
As an
alternative to using the entities above, you can execute a SQL statement
directly using the DatabaseServices
class in the Javascript scripting API – DatabaseServices is most easily accessed using services.database.xxxx. There are two main methods in DatabaseServices:
There are
also a number of additional methods to help with handling date and timestamp
database column types. All statements run within the context of a transaction
which is committed automatically when all scripts for the user request have
been executed or is rolled back if there is a failure.
The syntax
is:
executeSelectStatement(databaseConnectionName, sqlStatement,
callbackFunction)
The callback function accepts a single argument representing an
object containing a key/value pair for each column in the row. It should return
true or false to continue or stop processing of the result set.
e.g. to load the categories table from the categories database table in the SAMPLES database:
tables.categories.resetTable() // remove any existing data in the table
var stmt = "select cat_name, cat_value, creation_date from categories";
services.database.executeSelectStatement(
"SAMPLES",
stmt,
function (columnData)
{
tables.categories.insertRow();
tables.categories.categoryName.value = columnData.cat_name;
tables.categories.categoryValue.value = columnData.cat_value;
tables.categories.creationDate.value = columnData.creation_date;
return true; // continue
});
tables.categories.control.scrollToTop(); // scroll to the top of the table
The syntax
is:
executeGenericUpdateStatement(databaseConnectionName, sqlStatement)
This
normally returns the number of updated rows, except when the statement is an insert then the value of an
auto-incrementing column, if any, is returned.
Examples:
var stmt = "update categories where id = '"
+ fields.categoryId.value
+ "' set description = '"
+ fields.categoryDescription.value
+ "'";
services.database.executeGenericUpdateStatement("SAMPLES", stmt);
// example showing the return of an auto-incrementing column with an insert statement
var stmt = "insert requests(requestText, description) values('"
+ requestText
+ "', '"
+ description
+ "')";
var requestId = services.database.executeGenericUpdateStatement("SAMPLES", stmt);
Another
alternative is to use the Java JDBC API directly; as with DatabaseServices this option is
only available from server-side Javascript scripts. All statements are issued within
the context of a transaction and are automatically committed or rolled back.
For
example:
// use
the getDatabaseConnection method to get a connection
for any configured Database Connection (see Verj.io
Elements above)
var con = system.getDatabaseConnection("EBASE_SAMPLES");
var stmt;
var rs;
try
{
stmt = con.prepareStatement("select customer_id,
name from customer where credit_limit >
9999");
rs = stmt.executeQuery();
// create an array for the result
var
result = [];
// go through the result set and add each row
to the result array
while (rs.next())
{
// create an object for each row
var
row = {};
row.CUSTOMER_ID
= rs.getString("customer_id");
row.NAME = rs.getString("name");
result.push(row);
}
// build a table from the results so they can
be displayed
for each (var row in result)
{
tables.CUSTOMERS.insertRow();
tables.CUSTOMERS.CUSTOMER_ID.value
= row.CUSTOMER_ID;
tables.CUSTOMERS.NAME.value
= row.NAME;
}
}
finally
{
// it is very important to close any open
result sets, statements and connection in this manner
// otherwise there will be a resource leak.
if (rs) rs.close();
if (stmt) stmt.close();
if (con) con.close();
}