com.webmethods.portal.service.sql
Interface ISqlWorker


public interface ISqlWorker

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:

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


Nested Class Summary
static class ISqlWorker.Null
          Null class that defines type information for null object
 
Field Summary
static Object BOOLEAN_NULL
          Identifies NULL Boolean
static Object BYTE_NULL
          Identifies NULL Byte
static Object CHAR_NULL
          Identifies NULL Char
static Object INTEGER_NULL
          Identifies NULL Integer
static Object LONG_NULL
          Identifies NULL Long
static Object NULL
          Identifies default NULL - will assume VARCHAR type
static Object NUMERIC_NULL
          Identifies NULL Numeric (all floats)
static int SERVERTYPE_ANY
          Supported database server types
static int SERVERTYPE_CUSTOM
           
static String SERVERTYPE_CUSTOM_STR
           
static int SERVERTYPE_DB2
           
static String SERVERTYPE_DB2_STR
           
static int SERVERTYPE_DERBY
           
static String SERVERTYPE_DERBY_STR
           
static int SERVERTYPE_INFORMIX
           
static String SERVERTYPE_INFORMIX_STR
           
static int SERVERTYPE_MSSQL
           
static String SERVERTYPE_MSSQL_STR
           
static int SERVERTYPE_MYSQL
           
static String SERVERTYPE_MYSQL_STR
           
static int SERVERTYPE_ODBC
           
static String SERVERTYPE_ODBC_STR
           
static int SERVERTYPE_ORACLE
           
static String SERVERTYPE_ORACLE_STR
           
static int SERVERTYPE_SYBASE
           
static String SERVERTYPE_SYBASE_STR
           
static Object SHORT_NULL
          Identifies NULL Short
static Object VARCHAR_NULL
          Identifies NULL VARCHAR type
 
Method Summary
 void beginTran()
          Begins transaction for encapsulated connection and increments current transaction count
 void commitTran()
          Commits currently active transaction for encapsulated connection.
 ResultSet execute(String sql, Object[] parameters, Object[] macros)
          Execute sql formatted with parameters and macros that return ResultSet.
 void executeBlobUpdate(String tablename, String fieldname, byte[] blobvalue, String keyfield, Object keyvalue)
          Executes update for Clob type of field.
 void executeClobUpdate(String tablename, String fieldname, String clobvalue, String keyfield, Object keyvalue)
          Executes update for Clob type of field.
 ResultSet executeQuery(String sql)
          Execute sql that return ResultSet
 ResultSet executeQuery(String sql, Object[] parameters, Object[] macros)
          Execute sql formatted with parameters and macros that return ResultSet
 ResultSet executeQuery(String sql, Object[] parameters, Object[] macros, int resultsettype, int resultsetconcur)
          Execute sql formatted with parameters and macros that return ResultSet
 ResultSet executeQuery(String sql, Object[] parameters, Object[] macros, int resultsettype, int resultsetconcur, int fetchSize)
          Execute sql formatted with parameters and macros that return ResultSet
 Object[] executeQueryProcOut(String strProc, Object[] parameters, int[] outParamTypes)
          Execute stored procedure that returns output parameters
 ResultSet executeQueryProcResult(String strProc, Object[] parameters)
          Execute stored procedure or package that returns result set
 ResultSet executeQueryProcResult(String strProc, Object[] parameters, boolean assumePackageName)
          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.
 Object[] executeQueryProcResultOut(String strProc, Object[] parameters, int[] outParamTypes)
          Deprecated. doesn't work in SQL
 int executeUpdate(String sql)
          Execute sql that doesn't return ResultSet or update statements
 int executeUpdate(String sql, Object[] parameters, Object[] macros)
          Execute statement that does not return result set or an update statement
 int executeUpdate(String sql, Object[] parameters, Object[] macros, boolean requestIdentity)
          Execute statement that does not return result set or an update statement
 int executeUpdate(String sql, Object[] parameters, Object[] macros, boolean requestIdentity, boolean handleError)
          Execute statement that does not return result set or an update statement
 int executeUpdateProc(String strProc, Object[] parameters)
          Execute stored procedure that doesn't return anything.
 int getBitsetFromObject(Object o)
          Returns a bitset from object translates based on DB
 int getBitsetFromResult(ResultSet rs, int index)
          Returns a bitset from a resultset entry in given DB of worker.
 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
 int getBitsetOutputType()
          Returns server storage type for bit set value.
 byte[] getBlob(ResultSet rs, int column)
          Returnes binary array for ResultSet column.
 String getClob(ResultSet rs, int column)
          Returnes String value for ResultSet clob column.
 Connection getConnection()
          Returns encapsulated sql connection
 IDatabaseRuntime getDatabaseRuntime()
          Returns current database runtime, may be null if there is no runtime associated
 int getLastInsertedIdentity()
          Returns last inserted identity
 int getServerType()
          Returns server type
 void init(ConnectionInfo connInfo)
          Initializes sql worker connection.
 boolean isActiveTran()
          If there is an active transaction
 boolean isDeadlockCode(int code)
          Deprecated. Use #isDeadlockError(SQLException) instead
 boolean isDeadlockError(Exception e)
          Determines if SQLException reports deadlock error
 void release()
          Releases all resources aquired by SqlWorker, including database connection, result sets, prepares statements
 void rollbackTran()
          Rollbacks currently active transaction for encapsulated connection.
 void setDatabaseRuntime(IDatabaseRuntime databaseRuntime)
          Sets database runtime for this sql worker instance
 void setExternalTran(boolean isExternal)
          Sets transaction flag
 

Field Detail

VARCHAR_NULL

static final Object VARCHAR_NULL
Identifies NULL VARCHAR type


INTEGER_NULL

static final Object INTEGER_NULL
Identifies NULL Integer


LONG_NULL

static final Object LONG_NULL
Identifies NULL Long


SHORT_NULL

static final Object SHORT_NULL
Identifies NULL Short


BYTE_NULL

static final Object BYTE_NULL
Identifies NULL Byte


BOOLEAN_NULL

static final Object BOOLEAN_NULL
Identifies NULL Boolean


CHAR_NULL

static final Object CHAR_NULL
Identifies NULL Char


NUMERIC_NULL

static final Object NUMERIC_NULL
Identifies NULL Numeric (all floats)


NULL

static final Object NULL
Identifies default NULL - will assume VARCHAR type


SERVERTYPE_ANY

static final int SERVERTYPE_ANY
Supported database server types

See Also:
Constant Field Values

SERVERTYPE_MSSQL

static final int SERVERTYPE_MSSQL
See Also:
Constant Field Values

SERVERTYPE_ORACLE

static final int SERVERTYPE_ORACLE
See Also:
Constant Field Values

SERVERTYPE_ODBC

static final int SERVERTYPE_ODBC
See Also:
Constant Field Values

SERVERTYPE_DB2

static final int SERVERTYPE_DB2
See Also:
Constant Field Values

SERVERTYPE_INFORMIX

static final int SERVERTYPE_INFORMIX
See Also:
Constant Field Values

SERVERTYPE_SYBASE

static final int SERVERTYPE_SYBASE
See Also:
Constant Field Values

SERVERTYPE_CUSTOM

static final int SERVERTYPE_CUSTOM
See Also:
Constant Field Values

SERVERTYPE_MYSQL

static final int SERVERTYPE_MYSQL
See Also:
Constant Field Values

SERVERTYPE_DERBY

static final int SERVERTYPE_DERBY
See Also:
Constant Field Values

SERVERTYPE_MSSQL_STR

static final String SERVERTYPE_MSSQL_STR
See Also:
Constant Field Values

SERVERTYPE_ORACLE_STR

static final String SERVERTYPE_ORACLE_STR
See Also:
Constant Field Values

SERVERTYPE_MYSQL_STR

static final String SERVERTYPE_MYSQL_STR
See Also:
Constant Field Values

SERVERTYPE_ODBC_STR

static final String SERVERTYPE_ODBC_STR
See Also:
Constant Field Values

SERVERTYPE_DB2_STR

static final String SERVERTYPE_DB2_STR
See Also:
Constant Field Values

SERVERTYPE_INFORMIX_STR

static final String SERVERTYPE_INFORMIX_STR
See Also:
Constant Field Values

SERVERTYPE_SYBASE_STR

static final String SERVERTYPE_SYBASE_STR
See Also:
Constant Field Values

SERVERTYPE_CUSTOM_STR

static final String SERVERTYPE_CUSTOM_STR
See Also:
Constant Field Values

SERVERTYPE_DERBY_STR

static final String SERVERTYPE_DERBY_STR
See Also:
Constant Field Values
Method Detail

init

void init(ConnectionInfo connInfo)
          throws SQLException
Initializes sql worker connection.

Parameters:
connInfo - to initialize Sql worker for
Throws:
SQLException

release

void release()
Releases all resources aquired by SqlWorker, including database connection, result sets, prepares statements


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 ISqlWorker.SERVERTYPE_MSSQL or @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

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 ISqlWorker.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 ISqlWorker.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

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

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