Working with Databases

Documentation home

Verj.io Database Entities

Here is a brief overview of the Verj.io entities used to provide database support.

• Database Connection: as the name implies this represents a connection to a database system. The other entities shown below are each linked to a Database Connection. Database Connections are created on the server using the Server Administration Application, click here for details.

• Database Resource: is used to execute SQL statements. Resource fields are mapped to form fields or tables using resource field mappings which are used to transfer data between the resource and a form. SQL statements are executed as a result of an FPL script command such as fetch, fetchtable, update, updatetable, delete, insert or an API method such as DatabaseResource.fetch(), Table.fetchTable() etc. Database Resources are located within the IT Elements/External Resources section of the designer tree.

• Stored Procedure Resource: similar to a Database Resource, but used to execute a stored procedure. Resource fields are mapped to form fields to transfer data between the resource and a form. Executed as a result of the FPL script command exec or the API method StoredProcedureResource.exec(). Stored Procedure Resources are located within the IT Elements/External Resources section of the designer tree.

• Dynamic List: is used to execute a single select SQL statement to build a list displayed to the end user. The SQL is normally executed automatically by the system as it constructs the HTML output for each page, though options exist to control this. List fields are mapped to form fields using dynamic list mappings. Dynamic Lists are located in the Lists section of the designer tree.

• XML Resource with Stored Procedure Adapter: is used to execute a stored procedure passing an XML request document and receiving an XML response document. Resource fields are mapped to form fields or tables using resource field mappings which are used to transfer data between the resource and a form. Executed as a result of the FPL script command call or the API method XmlResourceBase.call() to the XML Resource. XML Resources are located within the IT Elements/External Resources section of the studio tree.

Direct access from a script

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:

• executeSelectStatement() – this can be used to issue any SELECT statement. Each row is fed back to a callback function.
• executeGenericUpdateStatement – this can be used to issue any update statement e.g. INSERT, UPDATE, CREATE, DROP, ALTER

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.

Select statement

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

Update statement

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);

Direct access using JDBC

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();

}