Package de.elo.ix.jscript.handler

Interface IDBConnectionHandler

All Superinterfaces:
de.elo.ix.db.schema.IDBConnection
public interface IDBConnectionHandler extends de.elo.ix.db.schema.IDBConnection
Internally used interface. Class DBConnection forwards method calls to an implementation of this interface. The implementation is only available when we are running as a IX event script or plugin.

  • Method Details

    • getDatabaseProductName

      String getDatabaseProductName() throws Exception
      Returns the name of the Database product.
      Returns:
      ...
      Throws:
      Exception

    • isOracle

      boolean isOracle() throws Exception
      Returns true, if connected to Oracle.
      Returns:
      ...
      Throws:
      Exception
      NamingException

    • isDB2

      boolean isDB2() throws Exception
      Returns true, if connected to DB2.
      Returns:
      ...
      Throws:
      NamingException
      Exception

    • isMssql

      boolean isMssql() throws Exception
      Returns true, if connected to Mssql.
      Returns:
      ...
      Throws:
      NamingException
      Exception

    • isPgsql

      boolean isPgsql() throws Exception
      Returns true, if connected to Mssql.
      Returns:
      ...
      Throws:
      NamingException
      Exception

    • open

      Connection open() throws Exception
      Open a database connection. The returned database connections must be closed with a call to its close function.
      Returns:
      Connection object
      Throws:
      Exception

    • query

      Object[][] query(String sqlcmd, Object params, int max, Object binding) throws Exception
      Execute a SELECT command.

      This function executes a SQL SELECT command and returns an array of result rows, whereby each row is an array of Java objects.

      Three kinds of query strings in the parameter sqlcmd are supported:

      • Simple query without placeholders:
        Example:
        db.query("select objid from objekte where objshort like 'it''s my string'", undefined, 0, undefined);
        This will execute the query "select objid from objekte where objshort like 'it''s my string'".
      • Query with MessageFormat placeholders:
        The placeholders are substituted by a java.text.MessageFormat object.
        Example:
        db.query("select {1} from {0} where {2} like {3}", ["objekte", "objid", "objshort", "'it''s my string'"], 0, undefined);
        This will execute the query "select objid from objekte where objshort like 'it''s my string'".
        Hints:Placeholders inside apostrophes - like in "... where objshort like '{0}' ..." - are NOT substituted.
      • Query with PreparedStatement placeholders:
        The placeholders are substituted by the JDBC driver.
        Example:
        db.query("select objid from objekte where objshort like ?", ["it's my string"], 0, undefined);
        Hint: Strings need not to be escaped.

      Databases provide column data for some column types in a non-serializable Java class. E.g. the ORACLE database return both integers and doubles as Java BigDecimal objects. Thus it is not possible to distinguish between the types and automatically convert to Integer or Double without knowlege about the underlying column type definition. To handle this circumstance, a JavaScript object can be supplied in the parameter binding that invokes the getter functions of the ResultSet object. It has to define a getResult function as shown in the example below. The first parameter of the getResult function is a IDBConnectionHandler.ResultSetWrapper

      The following JavaScript class is an example for the binding parameter. 

       
 function Binding() { 
   this.getResult = function(row, column) {
     // column starts with 1 !
     switch (column) {
       case 1: return row.getString(column);
       case 2: return row.getInteger(column);
       case 3: return row.getBytes(column);
       default: throw "Invalid column";
     }
   };
 }
 
  function dbread() {
    var db = new Packages.de.elo.ix.jscript.DBConnection( "jdbc/MyDB2", "TESTSCHEMA" );
    var args = ["Stuttgart", 59];
    var ret = db.query("select name, age, passportphoto from employees where branch like ? and age > ?", args, 0, new Binding());
    for (var i = 0; i < ret.length; i++) {
      log.info("name=" + ret[i][0] + ", age=" + ret[i][1] + ", photo.length=" + ret[i][2].length);
    }
    return ret;
  }
      Parameters:
      sqlcmd - SELECT command string.
      params - Optional. This parameters are bound if sqlcmd contains placeholders.
      max - Optional. Maximum number of rows to be read. If 0, all selected rows are read.
      binding - Optional. This parameter can hold a JavaScript object that extracts the data of a selected column from the ResultSet object.
      Returns:
      Java Object[] of rows. If more than one column is selected, each row element is an array of columns.
      Throws:
      Exception
      See Also:

    • query

      Object[][] query(String sqlcmd, Object params) throws Exception
      Execute a SELECT command. This function calls query(String, Object, int, Object).
      Parameters:
      sqlcmd - SELECT command.
      params - Optional. This parameters are bound if sqlcmd contains placeholders.
      Returns:
      Matrix of rows and columns.
      Throws:
      Exception
      See Also:

    • query

      Object[][] query(String sqlcmd) throws Exception
      Execute a SELECT command. This function calls query(String, Object, int, Object).
      Parameters:
      sqlcmd - SELECT command.
      Returns:
      Matrix of rows and columns.
      Throws:
      Exception
      See Also:

    • update

      Integer update(String sqlcmd, Object rows, Object binding) throws Exception
      Executes an update, delete oder insert Statement.
      • If the SQL command does not contain placeholders, the rows argument must be null.
      • If the SQL command contains MessageFormat placeholders like {0}, {1} etc. or the PreparedStatement placeholder ?, the rows parameter must not be null.
      • If rows is a single Object or an array of Objects, this objects replace the placeholders and one database row is updated.
      • If rows is an 2-dimensional array of Objects with the dimensions rows[rowcount][colcount], the SQL command contains colcount placeholders and rowcount database rows are updated.

      If the SQL command contains PreparedStatement placeholders, the function parameter binding can be supplied. A JavaScript object has to be passed that calls the setters of the internally used JDBC PreparedStatement object. This allows the programmer e.g. to store binary data. The binding object has to be defined analogous to the binding object in the query(String, Object, int, Object) function. Here is an example: 

       
 function Binding() {
   this.bindParam = function(row, col, obj) {
     switch (col) {
       case 1: return row.setString(col, obj);
       case 2: return row.setDouble(col, obj);
       case 3: return row.setInteger(col, obj);
       case 4: return row.setBytes(col, obj);
       default: throw "Invalid column";
     }
   }
 }
 
  function dbinsert() {
    var db = new Packages.de.elo.ix.jscript.DBConnection( "jdbc/MyDB2", "TESTSCHEMA" );
    var photos = [ ... bytes ... , ... bytes ... ]; 
    var args = [["Fritz", 54321.22, 38, photos[0]],["Maria", 65432.11, 43, photos[1]]];
    var ret = db.update("insert into employees (name, salary, age, passportphoto) values (?, ?, ?, ?)", args, new Binding());
    log.info("number of inserted rows=" + ret); // == 2
    return ret;
 }
      Parameters:
      sqlcmd - SQL command.
      rows - Optional. Data to be written or parameters if the sqlcmd contains placeholders.
      binding - Optional. This parameter can hold a JavaScript object that passes the data of a selected column into a PreparedStatement object.
      Returns:
      Number of modified rows.
      Throws:
      Exception

    • update

      Integer update(String sqlcmd) throws Exception
      Executes an update, delete or insert command. This function calls update(String, Object, Object)
      Parameters:
      sqlcmd - SQL command.
      Returns:
      Number of modified rows.
      Throws:
      Exception

    • update

      Integer update(String sqlcmd, Object rows) throws Exception
      Executes an update, delete or insert command. This function calls update(String, Object, Object)
      Parameters:
      sqlcmd - SQL command.
      rows - see update(String, Object, Object)
      Returns:
      Number of modified rows.
      Throws:
      Exception

    • escapeString

      String escapeString(String value)
      Return an escaped String value to be used in a SQL statement. Places an apostrophe before and after the given String and replaces every apostrophe in the string with two apostrophes.
      Parameters:
      value - String value to be escaped.
      Returns:
      Escaped String value.