com.wm.app.b2b.server
Class DBConnection

java.lang.Object
  com.wm.app.b2b.server.DBConnection

Direct Known Subclasses:

JDBCConnection

public abstract class DBConnection
extends java.lang.Object

The DBConnection class is an abstract class that represents a connection to a remote database. An object of this class is returned when you connect to a database using DBConnectionManager.getConnection.

This class contains the methods for performing basic database operations (insert, update, and so on) on the database to which this object is connected. All of these methods use the abstract method execSQL to perform their work.

The lifetime of a DBConnection object is determined by parameters set in DBConnectionManager when a DBConnection object is instantiated.

Any subclasses that you derive from this class should implement the following methods:

• execSQL -- required.
• getTables -- Recommended in order to maintain compatibility with the webMethods Integration Server Administrator (which automatically generates flow services).
• getTableInfo -- Recommended in order to support higher-level database methods, which often need information for individual tables.
• startTransaction, commit, rollback -- Recommended for implementations that need to support transaction processing.

Constructor Summary

DBConnection()

Method Summary

Values	call(java.lang.String proc, Values data) Calls the specified SQL stored procedure.
Values	call(java.lang.String proc, Values[] sig, Values data, boolean paramsByOrder) Calls the specified SQL stored procedure with the specified signature.
Values	call(java.lang.String proc, Values data, boolean paramsByOrder) Calls the specified SQL stored procedure, fetching parameter values by name or order, as specified.
void	clearCatSchemDefaults() Resets (clears) the current default catalog and schema settings.
void	clearTransaction() Voids the current transaction without performing a rollback or a commit.
void	close() Closes this connection to the database.
void	commit() Permanently commits the database operations that have been performed since the last startTransaction and ends the transaction.
Values	delete(java.lang.String table, Values criteria) Removes all rows from the specified table that meet the given criteria.
Values	execSQL(java.lang.String sql) Executes the specified SQL statement and returns the results in a Values object.
Values	execSQL(java.lang.String sql, java.lang.Object[] params, int[] paramTypes) Executes the specified SQL statement using host variables from a specified Object [].
abstract Values	execSQL(java.lang.String sql, java.lang.Object[] params, int[] paramTypes, boolean processEscapes) Executes the specified SQL statement using host variables from a specified Object [] and optionally processes embedded escape sequences.
java.lang.String	getPassword() Returns the password for this DBConnection object.
Values	getProcInfo(java.lang.String proc) Returns the signature of a specified stored procedure.
Values	getProcInfo(java.lang.String catalog, java.lang.String schemaPattern, java.lang.String proc) Returns the signatures for the stored procedure matching the specified catalog, schema, and procedure name criteria.
Values	getProcs() Returns a list of stored procedures that exist in the database.
Values	getProcs(java.lang.String catalog, java.lang.String schemaPattern, java.lang.String procNamePattern) Returns a list of stored procedures matching the specified catalog, schema, and procedure name criteria.
Values	getTableInfo(java.lang.String table) Returns column information about a specific table.
Values	getTableInfo(java.lang.String catalog, java.lang.String schemaPattern, java.lang.String table, java.lang.String colNamePattern) Returns information (name, type, and so forth) about the columns in a table matching the specified catalog, schema, and column name criteria.
Values	getTables() Returns a list of all tables in the database to which this DBConnection object is connected.
Values	getTables(java.lang.String catalog, java.lang.String schemaPattern, java.lang.String tableNamePattern, java.lang.String[] tableTypes) Returns a list of tables matching the specified catalog, schema, table name, and table type criteria.
java.lang.String	getUrl() Returns the URL of the database to which this DBConnection object is connected.
java.lang.String	getUser() Returns the user name for this DBConnection object.
Values	insert(java.lang.String table, Values[] data, boolean rollbackOnFail) Inserts one or more rows in the specified table.
Values	insert(java.lang.String table, Values data, boolean rollbackOnFail) Inserts one row in the specified table.
Values	query(java.lang.String table, Values criteria) Executes the specified query and returns the set of rows matching the given criteria.
void	rollback() Reverses all database operations that have been performed since the last startTransaction and ends that transaction.
void	setCatSchemDefaults(java.lang.String catalog, java.lang.String schemaPattern) Sets the default catalog and schema values for methods that take catalog and schema parameters.
void	startTransaction() Starts a transaction.
Values	update(java.lang.String table, Values criteria, Values set) Updates all rows in the specified table that meet the given criteria.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

DBConnection

public DBConnection()

Method Detail

getUrl

public final java.lang.String getUrl()

Returns the URL of the database to which this DBConnection object is connected.

Returns:

A String containing the database URL.

getUser

public final java.lang.String getUser()

Returns the user name for this DBConnection object.

Returns:

A String containing the user name that has been set for this DBConnection object.

getPassword

public final java.lang.String getPassword()

Returns the password for this DBConnection object.

Returns:

A String containing the password that has been set for this DBConnection object.

close

public void close()

Closes this connection to the database. After this method has been called, subsequent database operations performed on this DBConnection objects will return an error.

insert

public Values insert(java.lang.String table,
                     Values data,
                     boolean rollbackOnFail)

Inserts one row in the specified table.

Parameters:

table - A String specifying the table into which you want to insert a row.

data - A Values object containing the data that you want to insert. The keys in this object represent column names.

rollbackOnFail - A boolean specifying whether the change is to be rolled back if a failure occurs while inserting the row.

Set rollbackOnFail to…	To…
true	Undo change on failure
false	Do no transaction management operation on success or failure

insert

public Values insert(java.lang.String table,
                     Values[] data,
                     boolean rollbackOnFail)

Inserts one or more rows in the specified table.

Parameters:

table - A String specifying the table into which you want to insert rows.

data - A Values [] containing the data that you want inserted, where keys specify column names and values specify the data that is to be inserted into those columns. The first object in the array establishes the column names that this method will use for every member of the array, so the data structure of all members in the array must be identical.

rollbackOnFail - A boolean specifying whether the changes are to be rolled back if a failure occurs while inserting a row.

Set rollbackOnFail to…	To…
true	Undo change on failure
false	Do no transaction management operation on success or failure

Returns:

A Values object containing the following keys:

Key	Description
$updateCount	Number of rows this method successfully inserted.
$failCount	Number of rows this method failed to insert.
$errors	An array of IData objects, containing an entry for each error that occurred.

delete

public Values delete(java.lang.String table,
                     Values criteria)

Removes all rows from the specified table that meet the given criteria.

Note: If you do not specify any criteria, all rows are deleted from the table.

Parameters:

table - A String specifying the table from which you want to delete rows.

criteria - A Values object that specifies the criteria that must be met, where keys represent column names and values specify criteria.

Returns:

A Values object with one key, $updateCount, which contains the number of rows deleted or any errors encountered.

update

public Values update(java.lang.String table,
                     Values criteria,
                     Values set)

Updates all rows in the specified table that meet the given criteria.

Parameters:

table - A String specifying the table whose rows you want to update.

criteria - A Values object that specifies which rows are to be updated, where keys specify column names and values specify the criteria that a column must match.

set - A Values object containing the data that is to be written to rows matching criteria, where keys represent column names and values contain the data to be written into those columns.

Returns:

A Values object with one key, $updateCount, which contains the number of rows updated or any errors encountered.

query

public Values query(java.lang.String table,
                    Values criteria)

Executes the specified query and returns the set of rows matching the given criteria.

Parameters:

table - A String specifying the table that you want to query.

criteria - A Values object that specifies which rows are to be returned, where keys specify column names and values specify the criteria that a column must match.

Returns:

A Values object containing the following keys:

Key	Value
results	A com.wm.util.Table containing the results of the query.
$rowCount	Number of rows returned by the query.

call

public Values call(java.lang.String proc,
                   Values data)

Calls the specified SQL stored procedure. This feature is not supported generically -- you actually have to specify the SQL string to do the call. The JDBC version will use DatabaseMetaData to look up parameter info and make the call. Parameters are accessed by name.

Note: inout and out parameters are not supported at this time.

Parameters:

proc - A String specifying the name of the stored procedure.

data - A Values object specifying the parameter values for the procedure.

Returns:

A Values object containing the following keys:

Key	Value
results	A com.wm.util.Table containing the results of the first result set returned by the stored procedure.
$rowCount	Number of rows returned by the stored procedure.

Or any errors encountered.

call

public Values call(java.lang.String proc,
                   Values data,
                   boolean paramsByOrder)

Calls the specified SQL stored procedure, fetching parameter values by name or order, as specified. This feature is not supported generically -- you actually have to specify the SQL string to do the call. The JDBC version will use DatabaseMetaData to look up parameter info and make the call.

Note: inout and out parameters are not supported at this time.

Parameters:

proc - A String specifying the name of the stored procedure.

data - A Values object containing the parameter values that are to be passed to the procedure.

paramsByOrder - A boolean specifying whether parameter values are to be retrieved from data by order or name.

Set paramsByOrder to…	To…
true	Access parameters by order
false	Access parameters by name

Returns:

A Values object containing the following keys:

Key	Value
results	A com.wm.util.Table containing the results of the first result set returned by the stored procedure.
$rowCount	Number of rows returned by the stored procedure.

Or any errors encountered.

call

public Values call(java.lang.String proc,
                   Values[] sig,
                   Values data,
                   boolean paramsByOrder)

Calls the specified SQL stored procedure with the specified signature. This feature is not supported generically -- you actually have to specify the SQL string to do the call. Parameters can be retrieved by name or order from the supplied Values object.

Note: inout and out parameters are not supported at this time.

Parameters:

proc - A String specifying the name of the stored procedure.

sig - A Values [] in which each member contains the following keys:

Key	Value
name	The name of the parameter ("RETURN_VALUE" for the return val).
sqlType	The parameter's SQL type (e.g. "VARCHAR")
direction	The parameter's purpose, which must be one of the following: - in - out - inout - return value

Note: If you do not specify sig, the JDBC version will use DatabaseMetaData to retrieve parameter information and make the call.

data - A Values object containing the parameter values that are to be passed to the procedure.

paramsByOrder - A boolean specifying whether parameter values are to be retrieved from data by order or name.

Set paramsByOrder to…	To…
true	Access parameters by order
false	Access parameters by name

Returns:

A Values object containing the following keys:

Key	Value
results	A com.wm.util.Table containing the results of the first result set returned by the stored procedure.
$rowCount	Number of rows returned by the stored procedure.

Or any errors encountered.

execSQL

public Values execSQL(java.lang.String sql)

Executes the specified SQL statement and returns the results in a Values object.

Parameters:

sql - A String specifying the SQL statement that you want to execute. (No parsing or checking is performed on this string.)

Note: inout and out parameters are not supported at this time.

Returns:

A Values object containing the following keys:

Key	Value
results	A com.wm.util.Table containing the results of the first result set returned by the SQL statement (if the SQL statement produced a result set).
$rowCount	Number of rows returned by the SQL statement.

Or any errors encountered.

execSQL

public Values execSQL(java.lang.String sql,
                      java.lang.Object[] params,
                      int[] paramTypes)

Executes the specified SQL statement using host variables from a specified Object [].

Note: inout and out parameters are not supported at this time.

Parameters:

sql - A String specifying the SQL statement that you want to execute. (No parsing or checking is performed on this string.)

params - An Object [] containing the values that will be assigned to the host variables in the SQL statement.

paramTypes - A String [] specifying the SQL types in of the objects in params (using JDBC 1.2 type names; e.g., INTEGER, VARCHAR).

Returns:

A Values object containing the following keys:

Key	Value
results	A com.wm.util.Table containing the results of the first result set returned by the SQL statement (if the SQL statement produced a result set).
$rowCount	Number of rows returned by the SQL statement.

Or any errors encountered.

execSQL

public abstract Values execSQL(java.lang.String sql,
                               java.lang.Object[] params,
                               int[] paramTypes,
                               boolean processEscapes)

Executes the specified SQL statement using host variables from a specified Object [] and optionally processes embedded escape sequences.

Parameters:

sql - A String specifying the SQL statement that you want to execute. (No parsing or checking of this string is performed.)

params - An Object [] containing the values that will be assigned to the host variables ('?'s) in the SQL statement.

paramTypes - A String [] specifying the SQL types in of the objects in param (using JDBC 1.2 type names; e.g., INTEGER, VARCHAR).

processEscapes - A boolean specifying whether SQL escapes in the SQL statement are to be processed.

Set processEscapes to…	To…
true	Process SQL escapes
false	Ignore SQL escapes

Returns:

A Values object containing the key $updateCount if the database returns a row count. Otherwise, it will contain the following keys:

Key	Value
results	A com.wm.util.Table containing the result set.
$rowCount	Number of rows in the result set.

Or any errors encountered.

getTables

public Values getTables()

Returns a list of all tables in the database to which this DBConnection object is connected.

Note: If catalog and schema defaults are set, they will automatically be applied to the search.

Returns:

A Values object containing an array of Values objects describing the tables in the database (the tables are not listed in any particular order).

See Also:

setCatSchemDefaults(java.lang.String, java.lang.String)

getTables

public Values getTables(java.lang.String catalog,
                        java.lang.String schemaPattern,
                        java.lang.String tableNamePattern,
                        java.lang.String[] tableTypes)

Returns a list of tables matching the specified catalog, schema, table name, and table type criteria.

Parameters:

catalog - A String specifying the catalog you want to search.

schemaPattern - A String specifying the schema whose tables you want information about. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

tableNamePattern - A String specifying the table you want information about. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

tableTypes - A String [] specifying the types of tables you want information about (e.g., TABLE, VIEW).

Returns:

A Values object containing an array of Values objects describing the tables in the database (the tables are not listed in any particular order).

getTableInfo

public Values getTableInfo(java.lang.String table)

Returns column information about a specific table.

Note: If catalog and schema defaults are set, they will automatically be applied to the search.

Parameters:

table - A String specifying the table about which you want information.

Returns:

A Values object containing an array of Values objects describing the columns in the table (the columns are not listed in any particular order).

See Also:

setCatSchemDefaults(java.lang.String, java.lang.String)

getTableInfo

public Values getTableInfo(java.lang.String catalog,
                           java.lang.String schemaPattern,
                           java.lang.String table,
                           java.lang.String colNamePattern)

Returns information (name, type, and so forth) about the columns in a table matching the specified catalog, schema, and column name criteria. (Check your database documentation for exactly what column information is returned by your database system.)

Parameters:

catalog - A String specifying the catalog that you want to search.

schemaPattern - A String specifying the schema whose tables you want information about. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

table - A String specifying the name of the table for which you want information. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

colNamePattern - A String specifying a column name. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

Returns:

A Values object containing a key for each column name and a Values object for each column in the specified table(s). Each column description has the following keys:

TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
COLUMN_NAME String => column name
DATA_TYPE short => SQL type from java.sql.Types
TYPE_NAME String => Data source dependent type name
COLUMN_SIZE int => column size. For char or date types this is the maximum number of characters, for numeric or decimal types this is precision.
BUFFER_LENGTH is not used.
DECIMAL_DIGITS int => the number of fractional digits
NUM_PREC_RADIX int => Radix (typically either 10 or 2)
NULLABLE int => is NULL allowed?
- columnNoNulls - might not allow NULL values
- columnNullable - definitely allows NULL values
- columnNullableUnknown - nullability unknown
REMARKS String => comment describing column (may be null)
COLUMN_DEF String => default value (may be null)
SQL_DATA_TYPE int => unused
SQL_DATETIME_SUB int => unused
CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column
ORDINAL_POSITION int => index of column in table (starting at 1)
IS_NULLABLE String => "NO" means column definitely does not allow NULL values; "YES" means the column might allow NULL values. An empty string means nobody knows.

getProcs

public Values getProcs()

Returns a list of stored procedures that exist in the database.

Note: If catalog and schema defaults are set, they will automatically be applied to the search.

Returns:

A Values object containing a key for each procedure name (the stored procedures are not listed in any particular order). Each key will have a Values object that describes the stored procedure and contains the following elements:

PROCEDURE_CAT String => procedure catalog (may be null)
PROCEDURE_SCHEM String => procedure schema (may be null)
PROCEDURE_NAME String => procedure name
COLUMN_NAME String => column/parameter name
COLUMN_TYPE Short => kind of column/parameter:
- procedureColumnUnknown - nobody knows
- procedureColumnIn - IN parameter
- procedureColumnInOut - INOUT parameter
- procedureColumnOut - OUT parameter
- procedureColumnReturn - procedure return value
- procedureColumnResult - result column in ResultSet
DATA_TYPE short => SQL type from java.sql.Types
TYPE_NAME String => SQL type name
PRECISION int => precision
LENGTH int => length in bytes of data
SCALE short => scale
RADIX short => radix
NULLABLE short => can it contain NULL?
- procedureNoNulls - does not allow NULL values
- procedureNullable - allows NULL values
- procedureNullableUnknown - nullability unknown
REMARKS String => comment describing parameter/column

getProcs

public Values getProcs(java.lang.String catalog,
                       java.lang.String schemaPattern,
                       java.lang.String procNamePattern)

Returns a list of stored procedures matching the specified catalog, schema, and procedure name criteria.

Parameters:

catalog - A String specifying the catalog you want to search.

schemaPattern - A String specifying the schema whose procedures you want information about. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

procNamePattern - A String specifying the name of the stored procedure about which you want information. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

Returns:

A Values object containing a key for each procedure (stored procedures are not listed in any particular order). Each key will have a Values object that describes the stored procedure and contains the following elements:

PROCEDURE_CAT String => procedure catalog (may be null)
PROCEDURE_SCHEM String => procedure schema (may be null)
PROCEDURE_NAM String => procedure name
- reserved for future use
- reserved for future use
- reserved for future use
REMARK String => explanatory comment on the procedure
PROCEDURE_TYP short => kind of procedure:
- procedureResultUnknown - May return a result
- procedureNoResult - Does not return a result
- procedureReturnsResult - Returns a result

getProcInfo

public Values getProcInfo(java.lang.String proc)

Returns the signature of a specified stored procedure.

Parameters:

proc - A String specifying the name of the stored procedure about which you want information.

Note: If catalog and schema defaults are set, they will automatically be applied to the search.

Returns:

A Values object containing a key for each procedure (stored procedures are not listed in any particular order). Each key will have a Values object that describes the stored procedure and contains the following elements:

PROCEDURE_CAT String => procedure catalog (may be null)
PROCEDURE_SCHEM String => procedure schema (may be null)
PROCEDURE_NAME String => procedure name
COLUMN_NAME String => column/parameter name
COLUMN_TYPE Short => kind of column/parameter:
- procedureColumnUnknown - nobody knows
- procedureColumnIn - IN parameter
- procedureColumnInOut - INOUT parameter
- procedureColumnOut - OUT parameter
- procedureColumnReturn - procedure return value
- procedureColumnResult - result column in ResultSet
DATA_TYPE short => SQL type from java.sql.Types
TYPE_NAME String => SQL type name
PRECISION int => precision
LENGTH int => length in bytes of data
SCALE short => scale
RADIX short => radix
NULLABLE short => can it contain NULL?
- procedureNoNulls - does not allow NULL values
- procedureNullable - allows NULL values
- procedureNullableUnknown - nullability unknown
REMARKS String => comment describing parameter/column

See Also:

setCatSchemDefaults(java.lang.String, java.lang.String)

getProcInfo

public Values getProcInfo(java.lang.String catalog,
                          java.lang.String schemaPattern,
                          java.lang.String proc)

Returns the signatures for the stored procedure matching the specified catalog, schema, and procedure name criteria.

Parameters:

catalog - A String specifying the catalog that you want to search.

schemaPattern - A String specifying the schema whose stored procedures you want information about. If the database supports pattern matching on schemas, you may specify a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

proc - A String specifying the name of the stored procedure whose signature you want.

Returns:

A Values object containing a key for each procedure (stored procedures are not listed in any particular order). Each key will have a Values object that describes the stored procedure and contains the following elements:

PROCEDURE_CAT String => procedure catalog (may be null)
PROCEDURE_SCHEM String => procedure schema (may be null)
PROCEDURE_NAME String => procedure name
COLUMN_NAME String => column/parameter name
COLUMN_TYPE Short => kind of column/parameter:
procedureColumnUnknown - nobody knows
procedureColumnIn - IN parameter
procedureColumnInOut - INOUT parameter
procedureColumnOut - OUT parameter
procedureColumnReturn - procedure return value
procedureColumnResult - result column in ResultSet
DATA_TYPE short => SQL type from java.sql.Types
TYPE_NAME String => SQL type name
PRECISION int => precision
LENGTH int => length in bytes of data
SCALE short => scale
RADIX short => radix
NULLABLE short => can it contain NULL?
procedureNoNulls - does not allow NULL values
procedureNullable - allows NULL values
procedureNullableUnknown - nullability unknown
REMARKS String => comment describing parameter/column

setCatSchemDefaults

public void setCatSchemDefaults(java.lang.String catalog,
                                java.lang.String schemaPattern)

Sets the default catalog and schema values for methods that take catalog and schema parameters. These default settings are used when catalog and schema are not explicitly specified by the method itself. We strongly recommend that you use the catalog and schema settings when you are operating against large, complex databases to limit the areas of the database that need to be searched.

Parameters:

catalog - A String specifying the name of the database catalog to which the method will be applied.

schemaPattern - A String specifying the schema to which the method will be applied. If your database system supports pattern matching on schemas, you may use a pattern-matching string, where '%' matches one or more characters and a '_' matches one character.

See Also:

clearCatSchemDefaults()

clearCatSchemDefaults

public void clearCatSchemDefaults()

Resets (clears) the current default catalog and schema settings.

See Also:

setCatSchemDefaults(java.lang.String, java.lang.String)

startTransaction

public void startTransaction()
                      throws DBException

Starts a transaction. By default, individual SQL operations are atomic transactions that are committed as soon as they are completed. By using startTransaction, you can build a transaction made up of multiple SQL operations. Database operations performed after you call startTransaction are not committed until you call the commit method. This action permanently commits the changes (they can no longer be rolled back) and explicitly ends the transaction. A transaction can also be ended by performing a rollback, which reverses all changes that were made up to the previous startTransaction.

Throws:

DBException - If the database returns an error (most likely, because it doesn't support transaction processing).

See Also:

commit(), rollback(), clearTransaction()

commit

public void commit()
            throws DBException

Permanently commits the database operations that have been performed since the last startTransaction and ends the transaction. Once a set of operations have been committed, they can no longer be rolled back.

Throws:

DBException - If the database returns an error. This can happen if the database doesn't support transaction processing or if a transaction was never started.

See Also:

rollback(), startTransaction()

rollback

public void rollback()
              throws DBException

Reverses all database operations that have been performed since the last startTransaction and ends that transaction.

Throws:

DBException - If the database returns an error. This can happen if the database doesn't support transaction processing or if a transaction was never started.

See Also:

commit(), startTransaction()

clearTransaction

public void clearTransaction()
                      throws DBException

Voids the current transaction without performing a rollback or a commit. Simply reverts to standard, atomic SQL behavior, which means each operation is committed as soon as it is executed. This method is not one that you use for routine transaction processing. It is only provided to accommodate the very unusual case where, for whatever reason, you need to end a transaction without rolling it back or committing it.

Throws:

DBException - If the database returns an error. This can happen if the database doesn't support transaction processing or if a transaction was never started.

See Also:

startTransaction(), commit(), rollback()