Working with Databases

Documentation home

 

Verj.io Database Entities

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

 

 

 

 

 

 

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:

 

 

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

}