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

Nested Class Summary

Nested Classes

Modifier and Type	Interface and Description
static class	ISqlWorker.Null Null class that defines type information for null object

Field Summary

Fields

Modifier and Type	Field and Description
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_POSTGRESQL
static String	SERVERTYPE_POSTGRESQL_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

Modifier and Type	Method and Description
void	beginTran() Begins transaction for encapsulated connection and increments current transaction count
void	close()
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.
default int[]	executeBatchUpdate(String sql, List<Object[]> paramList, Object[] macros) Execute batch update
default int[]	executeBatchUpdate(String sql, List<Object[]> paramList, Object[] macros, boolean handleError) Execute batch update
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(IConnectionInfo 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() Deprecated. - since 10.3, use AutoCloseable
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_POSTGRESQL

static final int SERVERTYPE_POSTGRESQL

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

SERVERTYPE_POSTGRESQL_STR

static final String SERVERTYPE_POSTGRESQL_STR

See Also:

Constant Field Values

Method Detail

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 @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