com.wm.app.b2b.server

Class JDBCConnection

java.lang.Object

com.wm.app.b2b.server.DBConnection

com.wm.app.b2b.server.JDBCConnection

public class JDBCConnection
extends DBConnection

The JDBCConnection class contains methods that you use to interact with a JDBC database. It extends DBConnection.

The first time this class is initialized, it loads the default JDBC driver (sun.jdbc.odbc.JdbcOdbcDriver unless otherwise specified in the webMethods property, watt.server.jdbc.defaultDriver) and any other drivers that are specified by the webMethods property watt.server.jdbc.driverList.

Constructor Summary

Constructors

Constructor and Description
JDBCConnection() Deprecated. Use JDBCConnection(String, String, String)
JDBCConnection(java.lang.String url, java.lang.String user, java.lang.String pass) Constructs a JDBCConnection and connects it to the specified database using the default JDBC driver.
JDBCConnection(java.lang.String url, java.lang.String user, java.lang.String pass, java.lang.String driver) Constructs a JDBCConnection and connects it to the specified database using the specified JDBC driver.

Method Summary

Modifier and Type	Method and Description
Values	call(java.lang.String proc, Values[] sig, Values data, boolean paramsByOrder) Calls the specified SQL stored procedure.
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	connect(java.lang.String dbUrl, java.lang.String dbUser, java.lang.String dbPass, java.lang.String dbDriver) Deprecated. Use one of the JDBCConnection constructors.
Values	execSQL(java.lang.String sql, java.lang.Object[] paramValues, int[] paramTypes, boolean processEscapes) Executes the specified SQL statement using host variables from a specified Object [] and optionally processes embedded escape sequences.
static java.lang.String[]	getAvailableDrivers() Returns a list of JDBC drivers that are currently loaded.
java.lang.String	getDriverName() Returns the name of the JDBC driver that this JDBCConnection is currently using (e.g., oracle.jdbc.driver.OracleDriver).
Values	getProcInfo(java.lang.String catalog, java.lang.String schemaPattern, java.lang.String proc) Returns information about a stored procedure that matches the specified catalog and schema criteria.
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 catalog, java.lang.String schemaPattern, java.lang.String tableNamePattern, java.lang.String colNamePattern) Returns information (name, type, null value setting, and so forth) about the columns in a table matching the specified catalog, schema, and column name criteria.
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.
static Values	handleSQLException(java.sql.SQLException e, java.lang.String loc) Converts an SQL Exception into a Values object.
static void	init() Initializes this JDBCConnection and loads the default JDBC driver and any other drivers that are configured to be preloaded if they are not loaded already.
boolean	isConnected() Indicates whether this JDBCConnection is currently connected to a database.
static boolean	loadDriver(java.lang.String driver) Loads the specified driver.
void	rollback() Reverses all database operations that have been performed since the last startTransaction and ends that transaction.
void	startTransaction() Starts a transaction.
static void	toValues(java.sql.ResultSet set, Values results) Converts pending results into a Values object.

Methods inherited from class com.wm.app.b2b.server.DBConnection

call, call, clearCatSchemDefaults, delete, execSQL, execSQL, getPassword, getProcInfo, getProcs, getTableInfo, getTables, getUrl, getUser, insert, insert, query, setCatSchemDefaults, update

Methods inherited from class java.lang.Object

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

Constructor Detail

JDBCConnection

public JDBCConnection()

Deprecated. Use JDBCConnection(String, String, String)

Constructs a single JDBCConnection that can be used to connect to multiple databases.

JDBCConnection

public JDBCConnection(java.lang.String url,
                      java.lang.String user,
                      java.lang.String pass)
               throws DBConnectionException

Constructs a JDBCConnection and connects it to the specified database using the default JDBC driver. The default driver is sun.jdbc.odbc.JdbcOdbcDriver unless otherwise specified in the webMethods server property, watt.server.jdbc.defaultDriver.

Parameters:

url - A String specifying the URL of the database to which you want to connect.

user - A String specifying the user name that is to be used to connect to the database.

pass - A String specifying the password that is to be used to connect to the database.

Throws:

DBConnectionException - If a connection cannot be established.

JDBCConnection

public JDBCConnection(java.lang.String url,
                      java.lang.String user,
                      java.lang.String pass,
                      java.lang.String driver)
               throws DBConnectionException

Constructs a JDBCConnection and connects it to the specified database using the specified JDBC driver. The driver will automatically be loaded if it is not loaded already.

Parameters:

url - A String specifying the URL of the database to which you want to connect.

user - A String specifying the user name that is to be used to connect to the database.

pass - A String specifying the password that is to be used to connect to the database.

driver - A String specifying the name of the JDBC driver that this JDBCConnection will use to access the database (e.g., oracle.jdbc.driver.OracleDriver.

Throws:

DBConnectionException - If a connection cannot be established.

Method Detail

init

public static void init()

Initializes this JDBCConnection and loads the default JDBC driver and any other drivers that are configured to be preloaded if they are not loaded already.

loadDriver

public static boolean loadDriver(java.lang.String driver)

Loads the specified driver.

Parameters:

driver - A String specifying the name of the JDBC driver you want to load (e.g., oracle.jdbc.driver.OracleDriver).

Returns:

true if driver loaded successfully; false otherwise.

getAvailableDrivers

public static final java.lang.String[] getAvailableDrivers()

Returns a list of JDBC drivers that are currently loaded.

Returns:

a String [] containing the names of the JDBC drivers that have been loaded on the webMethods Integration Server.

isConnected

public boolean isConnected()

Indicates whether this JDBCConnection is currently connected to a database.

Returns:

true if this JDBCConnection is connected to a database; false otherwise.

getDriverName

public java.lang.String getDriverName()

Returns the name of the JDBC driver that this JDBCConnection is currently using (e.g., oracle.jdbc.driver.OracleDriver).

Returns:

A Stringcontaining the name of the JDBC driver.

connect

public Values connect(java.lang.String dbUrl,
                      java.lang.String dbUser,
                      java.lang.String dbPass,
                      java.lang.String dbDriver)

Deprecated. Use one of the JDBCConnection constructors.

Connects this JDBCConnection object to a specified database.

Parameters:

dbUrl - A String specifying the URL of the database to which you want to connect.

dbUser - A String specifying the user name that is to be used to connect to the database.

dbPass - A String specifying the password that is to be used to connect to the database.

dbDriver - A String specifying the name of the JDBC driver that is to be used to access the database (e.g., oracle.jdbc.driver.OracleDriver).

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.

Overrides:

close in class DBConnection

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 last startTransaction.

Overrides:

startTransaction in class DBConnection

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.

Overrides:

commit in class DBConnection

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.

Overrides:

rollback in class DBConnection

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

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.

Overrides:

clearTransaction in class DBConnection

See Also:

startTransaction(), commit(), rollback()

call

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

Calls the specified SQL stored procedure. It uses the JDBC method DatabaseMetaData to get the arguments for the procedure, and then uses the values you specify to fill those parameters.

Overrides:

call in class DBConnection

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 that contains the first ResultSet or update count from the procedure. (Currently, out and inout params are not returned).

execSQL

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

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

Specified by:

execSQL in class DBConnection

Parameters:

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

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

paramTypes - A String [] specifying the SQL data types in of the objects in paramValues (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(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.

Overrides:

getTables in class DBConnection

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 key names corresponding to the names of tables in the database. The value of each key will have JDBC-specific information about the table.

getTableInfo

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

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

Overrides:

getTableInfo in class DBConnection

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.

tableNamePattern - 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). (The columns are not listed in any particular order.) 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(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.

Overrides:

getProcs in class DBConnection

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 whose keys represent the names of the stored procedures that exist in the database (the stored procedures are not listed in any particular order).

getProcInfo

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

Returns information about a stored procedure that matches the specified catalog and schema criteria.

Overrides:

getProcInfo in class DBConnection

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 information you want to retrieve.

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

toValues

public static void toValues(java.sql.ResultSet set,
                            Values results)
                     throws java.sql.SQLException

Converts pending results into a Values object. All columns of all result sets are returned.

Parameters:

set - The Java ResultSet that you want to convert.

results - The Values object into which the entries generated from the Java ResultSet object are put.

Throws:

java.sql.SQLException

handleSQLException

public static Values handleSQLException(java.sql.SQLException e,
                                        java.lang.String loc)

Converts an SQL Exception into a Values object.

Parameters:

e - The SQLException that you want to convert.

loc - A String that you want to use to identify this particular SQLException.

Returns:

a Values object containing a key named "location" whose value is the string specified in loc. Also contains the values returned by Service.throwError.