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
-
mongoDB
-
-
Constructor Details
-
DatabaseServices
public DatabaseServices()
-
-
Method Details
-
getDatabaseConnection
public java.sql.Connection getDatabaseConnection(java.lang.String databaseConnectionName) throws com.ebasetech.xi.exceptions.FormRuntimeExceptionReturns 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 afinally
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.FormRuntimeExceptionReturns a string describing the database type for the database connection. This is obtained using theDatabaseMetadata.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.SQLExceptionExecutes 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 ApplicationselectStatement
- the SQL statement to executecallbackFunction
- 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.SQLExceptionExecutes 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 ApplicationupdateStatement
- 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 theexecuteGenericUpdateStatement(String, String)
orexecuteSelectStatement(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
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 theexecuteGenericUpdateStatement(String, String)
orexecuteSelectStatement(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
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 theexecuteGenericUpdateStatement(String, String)
orexecuteSelectStatement(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
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 theexecuteGenericUpdateStatement(String, String)
orexecuteSelectStatement(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 theexecuteGenericUpdateStatement(String, String)
orexecuteSelectStatement(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 theexecuteGenericUpdateStatement(String, String)
orexecuteSelectStatement(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
-