Package 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 Description
    MongoServices mongoDB  

  • Constructor Summary

    Constructors
    Constructor Description
    DatabaseServices()  

  • Method Summary

    Modifier and Type Method 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 Details

  • Constructor Details

    • DatabaseServices

      public DatabaseServices()

  • Method Details

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