Package com.webmethods.portal.service.sql

Interface ISqlWorker

All Superinterfaces:
AutoCloseable
public interface ISqlWorker extends AutoCloseable
ISqlWorker interface simplifies raw access and execution of SQL queries. Hides from the user all stuff related to managing connections, statements, etc.

This interface will usually provide functions for 99% of all tasks related to db operations. Many of the functions in this interface in addition to sql query string also take Object array of parameters and object array of macros. The parameters array contain input parameters for query or stored procedure. The actualy values are normal Java objects like String, Integer, Long, Boolean, etc. When parameter is NULL, then special constants of ISqlWorker interface should be used in place, which is guaranteed to work on any JDBC driver. It is also safe to just use Java null (depending on driver)

Here is an example of query strings that might be used:

  • UPDATE {0} SET {1} = ? - Having 2 macros and one input parameter
  • SELECT * FROM tblTest WHERE idThing = ? and idType = ? - Having 2 input parameters only
  • spListObjectTypes - call to stored procedure. Please note that when calling stored procedure you need to pass only stored procedure name. The actual sql statement will be compiled automatically depending on type of stored procedure, its parameters, type of the server: MS SQL or Oracle. Those calling stored procedures with output parameters also need array of output parameter types.

All ResultSet resultset objects returned are automatically closed by SqlWorker on release()

  • Field Details

  • Method Details

    • init

      void init(IConnectionInfo connInfo) throws SQLException
      Initializes SQL worker connection.
      Parameters:
      connInfo - to initialize SQL worker for
      Throws:
      SQLException

    • release

      @Deprecated void release()
      Deprecated.
      - since 10.3, use AutoCloseable
      Releases all resources aquired by SqlWorker, including database connection, result sets, prepares statements Will be executed once number of release() calls is equal to number of reinit() calls

    • beginTran

      void beginTran() throws SQLException
      Begins transaction for encapsulated connection and increments current transaction count
      Throws:
      SQLException

    • commitTran

      void commitTran() throws SQLException
      Commits currently active transaction for encapsulated connection. Commit is only executed if current transaction count == 1. Does nothing if there is no currently active transaction e.g. transaction count == 0
      Throws:
      SQLException

    • rollbackTran

      void rollbackTran() throws SQLException
      Rollbacks currently active transaction for encapsulated connection. Does nothing if there is no currently active transaction e.g. transaction count == 0
      Throws:
      SQLException

    • setExternalTran

      void setExternalTran(boolean isExternal)
      Sets transaction flag

    • isActiveTran

      boolean isActiveTran()
      If there is an active transaction
      Returns:

    • getConnection

      Connection getConnection()
      Returns encapsulated sql connection
      Returns:
      Connection object

    • getServerType

      int getServerType()
      Returns server type
      Returns:
      either SERVERTYPE_MSSQL or {@link @ISqlWorker#SERVERTYPE_ORACLE} server type

    • getBlob

      byte[] getBlob(ResultSet rs, int column) throws SQLException
      Returnes binary array for ResultSet column. Must be a Blob or compatible type to work properly
      Parameters:
      rs - is source ResultSet that contains blob type column
      column - is column number for blob field
      Returns:
      null if empty or field is not a Blob object
      Throws:
      SQLException

    • getClob

      String getClob(ResultSet rs, int column) throws SQLException
      Returnes String value for ResultSet clob column. Must be a Clob or compatible type to work properly.
      Parameters:
      rs - is source ResultSet that contains clob type column
      column - is column number for clob field
      Returns:
      value of Clob column or null if empty or field is not a Clob object
      Throws:
      SQLException

    • executeUpdate

      int executeUpdate(String sql) throws SQLException, ServerBusyException, DeadlockException
      Execute sql that doesn't return ResultSet or update statements
      Parameters:
      sql - is sql statement to be executed
      Returns:
      number of updated records
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeUpdate

      int executeUpdate(String sql, Object[] parameters, Object[] macros) throws SQLException, ServerBusyException, DeadlockException
      Execute statement that does not return result set or an update statement
      Parameters:
      sql - statement to be executed
      parameters - is an Object array of input parameters for query. May be null
      macros - is an Object array of macros to be used to format original query text. May be null
      Returns:
      number of updated records
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeBatchUpdate

      default int[] executeBatchUpdate(String sql, List<Object[]> paramList, Object[] macros) throws SQLException, ServerBusyException, DeadlockException
      Execute batch update
      Parameters:
      sql - - statement to be executed
      paramList - - is an Object array of input parameters for query. May be null
      macros - - is an Object array of macros to be used to format original query text. May be null
      Returns:
      number of updated records
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeBatchUpdate

      default int[] executeBatchUpdate(String sql, List<Object[]> paramList, Object[] macros, boolean handleError) throws SQLException, ServerBusyException, DeadlockException
      Execute batch update
      Parameters:
      sql - - statement to be executed
      paramList - - is an Object array of input parameters for query. May be null
      macros - - is an Object array of macros to be used to format original query text. May be null
      handleError - - whether an exception should be handled, provided it occurs during execution
      Returns:
      number of updated records
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeUpdate

      int executeUpdate(String sql, Object[] parameters, Object[] macros, boolean requestIdentity) throws SQLException, ServerBusyException, DeadlockException
      Execute statement that does not return result set or an update statement
      Parameters:
      sql - statement to be executed
      parameters - is an Object array of input parameters for query. May be null
      macros - is an Object array of macros to be used to format original query text. May be null
      requestIdentity - whether to request inserted identity value from the driver. If requested the last returned identity could be obtained using getLastInsertedIdentity() function
      Returns:
      number of updated records
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeUpdate

      int executeUpdate(String sql, Object[] parameters, Object[] macros, boolean requestIdentity, boolean handleError) throws SQLException, ServerBusyException, DeadlockException
      Execute statement that does not return result set or an update statement
      Parameters:
      sql - statement to be executed
      parameters - is an Object array of input parameters for query. May be null
      macros - is an Object array of macros to be used to format original query text. May be null
      requestIdentity - whether to request inserted identity value from the driver. If requested the last returned identity could be obtained using getLastInsertedIdentity() function
      Returns:
      number of updated records
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQuery

      ResultSet executeQuery(String sql, Object[] parameters, Object[] macros) throws SQLException, ServerBusyException, DeadlockException
      Execute sql formatted with parameters and macros that return ResultSet
      Parameters:
      sql - sql query to be executed
      parameters - is an Object array of input parameters for query. May be null
      macros - is an Object array of macros to be used to format original query text. May be null
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQuery

      ResultSet executeQuery(String sql) throws SQLException, ServerBusyException, DeadlockException
      Execute sql that return ResultSet
      Parameters:
      sql - sql query to be executed
      Returns:
      ResultSet produced from sql query
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • execute

      ResultSet execute(String sql, Object[] parameters, Object[] macros) throws SQLException, ServerBusyException, DeadlockException
      Execute sql formatted with parameters and macros that return ResultSet. Wraps Statement.execute method
      Parameters:
      sql - sql query to be executed
      parameters - is an Object array of input parameters for query. May be null
      macros - is an Object array of macros to be used to format original query text. May be null
      Returns:
      produced ResultSet
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQuery

      ResultSet executeQuery(String sql, Object[] parameters, Object[] macros, int resultsettype, int resultsetconcur) throws SQLException, ServerBusyException, DeadlockException
      Execute sql formatted with parameters and macros that return ResultSet
      Parameters:
      sql - sql query to be executed
      parameters - is an Object array of input parameters for query. May be null
      macros - is an Object array of macros to be used to format original query text. May be null
      resultsettype - see JDBC docs for details on this
      resultsetconcur - see JDBC docs for details on this
      Returns:
      produced ResultSet
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQuery

      ResultSet executeQuery(String sql, Object[] parameters, Object[] macros, int resultsettype, int resultsetconcur, int fetchSize) throws SQLException, ServerBusyException, DeadlockException
      Execute sql formatted with parameters and macros that return ResultSet
      Parameters:
      sql - sql query to be executed
      parameters - is an Object array of input parameters for query. May be null
      macros - is an Object array of macros to be used to format original query text. May be null
      resultsettype - see JDBC docs for details on this
      resultsetconcur - see JDBC docs for details on this
      fetchSize - desired fetch size
      Returns:
      produced ResultSet
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQueryProcResult

      ResultSet executeQueryProcResult(String strProc, Object[] parameters) throws SQLException, ServerBusyException, DeadlockException
      Execute stored procedure or package that returns result set
      Parameters:
      strProc - name of the stored procedure to execute
      parameters - is an Object array of input parameters for query. May be null
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQueryProcResult

      ResultSet executeQueryProcResult(String strProc, Object[] parameters, boolean assumePackageName) throws SQLException, ServerBusyException, DeadlockException
      Executes stored procedure by name, returning result set Important Oracle comment: Procedure that returns Cursor (aka ResultSet) must be declared within package and when called package name should be specified if assumePackageName = false, if assumePackageName = true, the package name is assumed equals to stored proc name. This flag is not used when database server is MS Sql Server
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQueryProcResultOut

      @Deprecated Object[] executeQueryProcResultOut(String strProc, Object[] parameters, int[] outParamTypes) throws SQLException, ServerBusyException, DeadlockException
      Deprecated.
      doesn't work in SQL
      Executes stored procedure by name, returning output params and result set Returns ResultSet and 0's element in the output Object[] array, all other output params returned starting from index 1
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeQueryProcOut

      Object[] executeQueryProcOut(String strProc, Object[] parameters, int[] outParamTypes) throws SQLException, ServerBusyException, DeadlockException
      Execute stored procedure that returns output parameters
      Parameters:
      strProc - name of the stored procedure to execute
      parameters - is an Object array of input parameters for query. May be null
      outParamTypes - array of output parameter types. See @Types interface for list of JDBC types
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeUpdateProc

      int executeUpdateProc(String strProc, Object[] parameters) throws SQLException, ServerBusyException, DeadlockException
      Execute stored procedure that doesn't return anything. Usually that could be update stored proc.
      Parameters:
      strProc - name of the stored procedure to execute
      parameters - is an Object array of input parameters for query. May be null
      Returns:
      number of affected records.
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeClobUpdate

      void executeClobUpdate(String tablename, String fieldname, String clobvalue, String keyfield, Object keyvalue) throws SQLException, ServerBusyException, DeadlockException
      Executes update for Clob type of field. Updates existing record, or creates new record if doesn't exists. This routine may be used for both MS SQL and Oracle databases
      Parameters:
      tablename - - table name with clob compatible field. For MS SQL Clob maps to text/ntext data types
      fieldname - - name of the clob field
      clobvalue - - value to write
      keyfield - of key field for the tablename. Usually this is PK
      keyvalue - for the key field
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • executeBlobUpdate

      void executeBlobUpdate(String tablename, String fieldname, byte[] blobvalue, String keyfield, Object keyvalue) throws SQLException, ServerBusyException, DeadlockException
      Executes update for Clob type of field. Updates existing record, or creates new record if doesn't exists. This routine may be used for both MS SQL and Oracle databases
      Parameters:
      tablename - - table name with clob compatible field. For MS SQL Blob maps to image data type
      fieldname - - name of the clob field
      blobvalue - - value to write to the blob field
      keyfield - of key field for the tablename. Usually this is PK
      keyvalue - for the key field
      Throws:
      SQLException
      ServerBusyException
      DeadlockException

    • isDeadlockCode

      @Deprecated boolean isDeadlockCode(int code)
      Deprecated.
      Use isDeadlockError(SQLException) instead
      Checks if error code is a deadlock code
      Parameters:
      code - error code
      Returns:
      true if code corresponds to a deadlock situation depending on DB

    • isDeadlockError

      boolean isDeadlockError(Exception e)
      Determines if SQLException reports deadlock error
      Parameters:
      e -
      Returns:

    • getBitsetObject

      Object getBitsetObject(int bitset)
      Returns a Java Object that translates a int value into the appropriate object type depending on the DB system the worker is using
      Parameters:
      bitset - 32-bit set
      Returns:
      correct object

    • getBitsetFromResult

      int getBitsetFromResult(ResultSet rs, int index) throws SQLException
      Returns a bitset from a resultset entry in given DB of worker. uses current row, caller must call index in order.
      Parameters:
      rs - current set
      index - field index
      Returns:
      32-bit bitset
      Throws:
      SQLException - if EOF or bad index

    • getBitsetFromObject

      int getBitsetFromObject(Object o)
      Returns a bitset from object translates based on DB
      Parameters:
      o - Integer in SQL, String in Oracle

    • getBitsetOutputType

      int getBitsetOutputType()
      Returns server storage type for bit set value. Those are Types.VARCHAR for Oracle and Types.INTEGER for SQL Server
      Returns:

    • getDatabaseRuntime

      IDatabaseRuntime getDatabaseRuntime()
      Returns current database runtime, may be null if there is no runtime associated

    • getLastInsertedIdentity

      int getLastInsertedIdentity()
      Returns last inserted identity

    • setDatabaseRuntime

      void setDatabaseRuntime(IDatabaseRuntime databaseRuntime)
      Sets database runtime for this sql worker instance

    • close

      void close()
      Specified by:
      close in interface AutoCloseable