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

Field Summary

Fields

Modifier and Type	Field and Description
MongoServices	mongoDB

Constructor Summary

Constructors

Constructor and Description
DatabaseServices()

Method Summary

Modifier and Type	Method and Description
static boolean	databaseSupportsGeneratedKeys(java.sql.DatabaseMetaData metadata)
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.
int	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.
MongoServices	getMongoDB()
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.
static int	processAutoIncrementColumns(java.sql.DatabaseMetaData metadata, java.sql.Statement statement)

Methods inherited from class java.lang.Object

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

Field Detail

mongoDB

public MongoServices mongoDB

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 int 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. Returns the number of rows that have been passed to the callback function.

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

Example 1: load Ebase table

 var count = 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

Returns:

the number of database records read

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 an INSERT statement the value of any auto-generated key is returned when a single record is inserted, for any other INSERT, UPDATE or DELETE statement the number of rows updated is returned, otherwise returns 0.

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

processAutoIncrementColumns

public static int processAutoIncrementColumns(java.sql.DatabaseMetaData metadata,
                                              java.sql.Statement statement)
                                       throws java.sql.SQLException

Throws:

java.sql.SQLException

databaseSupportsGeneratedKeys

public static boolean databaseSupportsGeneratedKeys(java.sql.DatabaseMetaData metadata)
                                             throws java.sql.SQLException

Throws:

java.sql.SQLException

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)

getMongoDB

public MongoServices getMongoDB()