com.ebasetech.xi.services

Class DatabaseServices

java.lang.Object

com.ebasetech.xi.services.DatabaseServices

All Implemented Interfaces:

java.io.Serializable

public class DatabaseServices
extends java.lang.Object
implements java.io.Serializable

See Also:

Serialized Form

Constructor Summary

Constructors

Constructor and Description
DatabaseServices()

Method Summary

Methods

Modifier and Type	Method and Description
int	executeGenericUpdateStatement(java.lang.String databaseConnectionName, java.lang.String updateStatement) Executes any kind SQL update statement - this can be UPDATE, INSERT, DELETE or a DDL statement CREATE, DROP, ALTER.
void	executeSelectStatement(java.lang.String databaseConnectionName, java.lang.String selectStatement, SelectStatementCallback callbackFunction) Executes a SQL select statement and passes each returned row to the specified callback function.
java.sql.Connection	getDatabaseConnection(java.lang.String databaseConnectionName) Returns a database connection.
java.lang.String	getDatabaseType(java.lang.String databaseConnectionName) Returns a string describing the database type for the database connection.
java.lang.String	makeJDBCDateString(java.util.Date date) Creates a SQL escape string for a date in the format {d 'yyyy-mm-dd'}.
java.lang.String	makeJDBCDateStringFromField(Field field) Creates a SQL escape string in the format {d 'yyyy-mm-dd'} from a form field of type Date, DateTime or Time.
java.lang.String	makeJDBCTimestampString(java.util.Date date) Creates a SQL escape string for a timestamp field in the format {ts 'yyyy-mm-dd hh:mm:ss.fff'}.
java.lang.String	makeJDBCTimestampStringFromField(Field field) Creates a SQL escape string in the format {ts 'yyyy-mm-dd hh:mm:ss.fff'} from a form field of type Date, DateTime or Time.
java.lang.String	makeJDBCTimeString(java.util.Date date) Creates a SQL escape string for a time field in the format {t 'hh:mm:ss'}.
java.lang.String	makeJDBCTimeStringFromField(Field field) Creates a SQL escape string in the format {t 'hh:mm:ss'} from a form field of type Date, DateTime or Time.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

DatabaseServices

public DatabaseServices()

Method Detail

getDatabaseConnection

public java.sql.Connection getDatabaseConnection(java.lang.String databaseConnectionName)
                                          throws com.ebasetech.xi.exceptions.FormRuntimeException

Returns a database connection.

Note that code should be enclosed in a try block, and that all database connections, result sets, and statements must be closed in a finally block, as shown in the example below. Failure to do this correctly can lead to connection pool leaks and eventually a hung system.

Javascript example:

 var con = system.getDatabaseConnection("CONN1");
 var stmt;
 var rs;
 try {
   stmt = con.prepareStatement("select * from tab1");
   rs = stmt.executeQuery();
   while (rs.next()) {
      var xx = rs.getString("col_name");
   }
 }
 finally {
   if (rs) rs.close();
   if (stmt) stmt.close();
   if (con) con.close();
 }
 

Parameters:

databaseConnectionName - the name of the Database Connection as configured in the Server Administration Application

Returns:

database connection

Throws:

com.ebasetech.xi.exceptions.FormRuntimeException - if the connection cannot be obtained for any reason

Since:

V5.2

getDatabaseType

public java.lang.String getDatabaseType(java.lang.String databaseConnectionName)
                                 throws com.ebasetech.xi.exceptions.FormRuntimeException

Returns a string describing the database type for the database connection. This is obtained using the DatabaseMetadata.getDatabaseProductName() method. Examples of returned strings:

• Apache Derby
• MySQL
• Microsoft SQL Server
• Oracle
• PostgreSQL

Parameters:

databaseConnectionName - the name of the Database Connection as configured in the Server Administration Application

Returns:

database type

Throws:

com.ebasetech.xi.exceptions.FormRuntimeException - if the connection cannot be obtained for any reason

Since:

V5.2

executeSelectStatement

public void executeSelectStatement(java.lang.String databaseConnectionName,
                          java.lang.String selectStatement,
                          SelectStatementCallback callbackFunction)
                            throws java.sql.SQLException

Executes a SQL select statement and passes each returned row to the specified callback function. The callback function should be specified with a single argument representing an object containing a key/value pair for each column in the row, see examples.

The function should return true to continue execution or false to terminate execution. If the return is omitted, execution continues.

Example 1: load Ebase table

 services.database.executeSelectStatement(
   "SAMPLES", 
   "select cat_name, cat_value, creation_date from categories", 
   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
   });
 

Example 2: load Ebase table - the table contains columns with the same names as the columns returned by the SQL statement. Note that this technique cannot be used with columns of type Date, Time or DateTime.

 var rowData = [];
 var tableData = {rows: rowData};
 services.database.executeSelectStatement("SAMPLES", "select * from tablexyz",
   function (columnData)
   {
     rowData.push(columnData);
     return true;
   });
 // load the table
 tables.sampleTable.loadFromJSON(JSON.stringify(tableData));
 // scroll to display the first row
 tables.sampleTable.control.scrollToTop();
 

Parameters:

databaseConnectionName - the name of the Database Connection as configured in the Server Administration Application

selectStatement - the SQL statement to execute

callbackFunction - callback function called with each row of data returned from the database

Throws:

java.sql.SQLException

Since:

V5.2

executeGenericUpdateStatement

public int executeGenericUpdateStatement(java.lang.String databaseConnectionName,
                                java.lang.String updateStatement)
                                  throws java.sql.SQLException

Executes any kind SQL update statement - this can be UPDATE, INSERT, DELETE or a DDL statement CREATE, DROP, ALTER. For a non-DDL statement returns either the value of any auto-generated key resulting from an INSERT statement or the number of rows updated.

Example:

 var stmt = "update categories where id = '" 
    + fields.categoryId.value 
    + "' set description = '" 
    + fields.categoryDescription.value 
    + "'"; 
 services.database.executeGenericUpdateStatement("SAMPLES", stmt); 
 

Parameters:

databaseConnectionName - the name of the Database Connection as configured in the Server Administration Application

updateStatement - the SQL statement to execute

Returns:

if the statement is an insert that resulted in the creation of a row with an auto-generated key, the value of this column is returned; otherwise the number of updated rows is returned

Throws:

java.sql.SQLException

Since:

V5.2

makeJDBCDateString

public java.lang.String makeJDBCDateString(java.util.Date date)

Creates a SQL escape string for a date in the format {d 'yyyy-mm-dd'}. This can be used in conjunction with the executeGenericUpdateStatement(String, String) or executeSelectStatement(String, String, SelectStatementCallback) methods;

Example 1:

 var d1 = new Date();
 var stmt = "update orders set order_date = " 
    + services.database.makeJDBCDateString(d1) 
    + " where order_id = " + orderId; 
 services.database.executeGenericUpdateStatement("SAMPLES", stmt); 
 

Example 2:

 var dbs = services.database;
 var d2 = new Date(fields.orderDate.value);
 var stmt = "select * from orders where order_date >= "
    + dbs.makeJDBCDateString(d2); 
 dbs.executeSelectStatement("SAMPLES", stmt, function (columnData) {..}); 
 

Parameters:

date - date object

Since:

V5.2

See Also:

makeJDBCDateStringFromField(Field)

makeJDBCDateStringFromField

public java.lang.String makeJDBCDateStringFromField(Field field)

Creates a SQL escape string in the format {d 'yyyy-mm-dd'} from a form field of type Date, DateTime or Time. This can be used in conjunction with the executeGenericUpdateStatement(String, String) or executeSelectStatement(String, String, SelectStatementCallback) methods;

Example:

 var stmt = "update orders set order_date = "
    + services.database.makeJDBCDateStringFromField(fields.orderDate) 
    + " where order_id = " + orderId; 
 services.database.executeGenericUpdateStatement("SAMPLES", stmt); 
 

Parameters:

field - form field, table column or workflow process attribute

Since:

V5.2

See Also:

makeJDBCDateString(Date)

makeJDBCTimeStringFromField

public java.lang.String makeJDBCTimeStringFromField(Field field)

Creates a SQL escape string in the format {t 'hh:mm:ss'} from a form field of type Date, DateTime or Time. This can be used in conjunction with the executeGenericUpdateStatement(String, String) or executeSelectStatement(String, String, SelectStatementCallback) methods;

Example:

 var t2 = new Date(fields.orderTime.value);
 var stmt = "select * from orders where order_time = "
    + services.database.makeJDBCTimeStringFromField(fields.orderTime); 
 services.database.executeSelectStatement("SAMPLES", stmt, function (columnData) {..}); 
 

Parameters:

field - form field, table column or workflow process attribute

Since:

V5.2

See Also:

makeJDBCTimeString(Date)

makeJDBCTimestampStringFromField

public java.lang.String makeJDBCTimestampStringFromField(Field field)

Creates a SQL escape string in the format {ts 'yyyy-mm-dd hh:mm:ss.fff'} from a form field of type Date, DateTime or Time. An Ebase field of type DateTime is equivalent to a database timestamp column. This method can be used in conjunction with the executeGenericUpdateStatement(String, String) or executeSelectStatement(String, String, SelectStatementCallback) methods;

Example:

 var ts1 = new Date();
 var stmt = "update orders set order_timestamp = "
    + services.database.makeJDBCTimestampStringFromField(fields.orderTimestamp)
    + " where order_id = " + orderId; 
 services.database.executeGenericUpdateStatement("SAMPLES", stmt); 
 

Parameters:

field - form field, table column or workflow process attribute

Since:

V5.2

See Also:

makeJDBCTimestampString(Date)

makeJDBCTimeString

public java.lang.String makeJDBCTimeString(java.util.Date date)

Creates a SQL escape string for a time field in the format {t 'hh:mm:ss'}. This can be used in conjunction with the executeGenericUpdateStatement(String, String) or executeSelectStatement(String, String, SelectStatementCallback) methods;

Example:

 var t1 = new Date();
 var stmt = "update orders set order_time = "
    + services.database.makeJDBCTimeString(t1)
    + " where order_id = " + orderId; 
 services.database.executeGenericUpdateStatement("SAMPLES", stmt); 
 

Parameters:

date - date object

Since:

V5.2

See Also:

makeJDBCTimeStringFromField(Field)

makeJDBCTimestampString

public java.lang.String makeJDBCTimestampString(java.util.Date date)

Creates a SQL escape string for a timestamp field in the format {ts 'yyyy-mm-dd hh:mm:ss.fff'}. An Ebase field of type DateTime is equivalent to a database timestamp column. This method can be used in conjunction with the executeGenericUpdateStatement(String, String) or executeSelectStatement(String, String, SelectStatementCallback) methods;

Example:

 var ts1 = new Date();
 var stmt = "update orders set order_timestamp = " 
    + services.database.makeJDBCTimestampString(ts1)
    + " where order_id = " + orderId; 
 services.database.executeGenericUpdateStatement("SAMPLES", stmt); 
 

Parameters:

date - date object

Since:

V5.2

See Also:

makeJDBCTimestampStringFromField(Field)