com.ibm.broker.plugin

Class MbSQLStatement

java.lang.Object

com.ibm.broker.plugin.MbSQLStatement

public class MbSQLStatement
extends java.lang.Object

MbSQLStatement provides support for accessing an external ODBC Database. An object of this class is created using one of the createSQLStatement() methods of MbNode.

Example usage:

  public void evaluate(MbMessageAssembly assembly, MbInputTerminal inTerm) throws MbException
  {
    MbMessage newMsg = new MbMessage(assembly.getMessage());
    MbMessageAssembly newAssembly = new MbMessageAssembly(assembly, newMsg);

    String table = assembly.getMessage().getRootElement().getLastChild().getFirstChild().getName();

    MbSQLStatement state = createSQLStatement( "dbName", 
                                               "SET OutputRoot.XML.integer[] = PASSTHRU('SELECT * FROM " + table + "');" );

    state.setThrowExceptionOnDatabaseError(false);
    state.setTreatWarningsAsErrors(true);

    state.select( assembly, newAssembly );

    int sqlCode = state.getSQLCode();
    if(sqlCode != 0)
      {
        // Do error handling here

        System.out.println("sqlCode = " + sqlCode);
        System.out.println("sqlNativeError = " + state.getSQLNativeError());
        System.out.println("sqlState = " + state.getSQLState());
        System.out.println("sqlErrorText = " + state.getSQLErrorText());
      }

    getOutputTerminal("out").propagate(assembly);
  }
 

Database access using JDBC is not supported.

See Also:

"ESQL Reference Manual"

Field Summary

Fields

Modifier and Type	Field and Description
static int	SQL_TRANSACTION_AUTO Transaction type.
static int	SQL_TRANSACTION_COMMIT Transaction type.

Method Summary

Modifier and Type	Method and Description
void	clearStatement() Clears the statement.
void	execute(MbMessageAssembly assembly) Executes the SQL expression associated with this object.
java.lang.String	getDataSourceName() Gets the ODBC data source name associated with this SQL statement object.
int	getSQLCode() Returns the SQLCode state variable associated with the last select() or execute() call.
java.lang.String	getSQLErrorText() Returns the SQLErrorText state variable associated with the last select() or execute() call.
int	getSQLNativeError() Returns the SQLNativeError state variable associated with the last select() or execute() call.
java.lang.String	getSQLState() Returns the SQLState state variable associated with the last select() or execute() call.
java.lang.String	getStatement() Gets the SQL expression associated with this SQL statement object.
boolean	getThrowExceptionOnDatabaseError()
int	getTransactionType() Gets the transaction type of this SQL statement object.
boolean	getTreatWarningsAsErrors()
void	select(MbMessageAssembly assemblyIn, MbMessageAssembly assemblyOut) Executes the SQL expression associated with this object.
void	setThrowExceptionOnDatabaseError(boolean throwExceptions) Sets the value of the 'throwExceptionOnDatabaseError' attribute.
void	setTreatWarningsAsErrors(boolean warningsAsErrors) Sets the value of the 'treatWarningAsErrors' attribute.
java.lang.String	toString()

Methods inherited from class java.lang.Object

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

Field Detail

SQL_TRANSACTION_AUTO

public static final int SQL_TRANSACTION_AUTO

Transaction type. Specifies that a database commit or rollback will be performed at the completion of the message flow.

See Also:

Constant Field Values

SQL_TRANSACTION_COMMIT

public static final int SQL_TRANSACTION_COMMIT

Transaction type. Specifies that a commit will be performed after execution of the statement.

See Also:

Constant Field Values

Method Detail

getTransactionType

public int getTransactionType()
                       throws MbException

Gets the transaction type of this SQL statement object. Will be one of:

• SQL_TRANSACTION_AUTO
• SQL_TRANSACTION_COMMIT

The former value specifies that a database commit will be performed at the completion of the message flow (that is, as a fully globally coordinated or partially globally coordinated transaction). The latter value specifies that a commit will be performed after execution of the statement, and within the execute or select method (that is, the message flow is partially broker coordinated).

Returns:

The transaction type

Throws:

MbException

getDataSourceName

public java.lang.String getDataSourceName()
                                   throws MbException

Gets the ODBC data source name associated with this SQL statement object.

Returns:

The data source name

Throws:

MbException

getStatement

public java.lang.String getStatement()
                              throws MbException

Gets the SQL expression associated with this SQL statement object.

Returns:

The SQL expression as a String.

Throws:

MbException

clearStatement

public void clearStatement()
                    throws MbException

Clears the statement. The removes all of the resources associated with this SQL statement object. It should be called when the object is no longer required, prior to garbage collection. Any attempt to use this object after it has been cleared will result in a NullPointerException being thrown.

Throws:

MbException

execute

public void execute(MbMessageAssembly assembly)
             throws MbException

Executes the SQL expression associated with this object. This method is to be used when the expression does not return data, for example when a PASSTHRU function is used.

If a database error occurs during the execution of this statement, the behavior is determined by the value of the 'throwExceptionOnDatabaseError' attribute. If set to true (the default value), an exception gets thrown which can be caught if run within a try/catch block. If set to true using setThrowExceptionOnDatabaseError() then no exception is thrown, but the SQLCode must be examined determine status. The four database state values are available using getSQLCode(), getSQLState(), getSQLNativeError(), and getSQLErrorText()

Parameters:

assembly - The MbMessageAssembly in which any data referenced by the SQL expression is held.

Throws:

MbException

select

public void select(MbMessageAssembly assemblyIn,
                   MbMessageAssembly assemblyOut)
            throws MbException

Executes the SQL expression associated with this object. If the expression returns data, then it is written into the message assembly object specified by the assemblyOut parameter.

If a database error occurs during the execution of this statement, the behavior is determined by the value of the 'throwExceptionOnDatabaseError' attribute. If set to true (the default value), an exception gets thrown which can be caught if run within a try/catch block. If set to true using setThrowExceptionOnDatabaseError() then no exception is thrown, but the SQLCode must be examined determine status. The four database state values are available using getSQLCode(), getSQLState(), getSQLNativeError(), and getSQLErrorText()

Parameters:

assemblyIn - The MbMessageAssembly in which any data referenced by the SQL expression is held.

assemblyOut - The MbMessageAssembly into which any data returned by the SQL expression will be written.

Throws:

MbException

toString

public java.lang.String toString()

Overrides:

toString in class java.lang.Object

setThrowExceptionOnDatabaseError

public void setThrowExceptionOnDatabaseError(boolean throwExceptions)

Sets the value of the 'throwExceptionOnDatabaseError' attribute. This attribute determines the behavior of the broker when it encounters a database error. If set to true, an exception is thrown which can be caught and handled by the user-defined node if desired. If set to false, no exception is thrown, but the SQLCode must be examined determine status. The four database state values are available using getSQLCode(), getSQLState(), getSQLNativeError(), and getSQLErrorText().

By default, this attribute is set to 'true'.

Parameters:

throwExceptions - The boolean value to be set.

getThrowExceptionOnDatabaseError

public boolean getThrowExceptionOnDatabaseError()

Returns:

The current value of the 'throwExceptionOnDatabaseError' attribute.

setTreatWarningsAsErrors

public void setTreatWarningsAsErrors(boolean warningsAsErrors)

Sets the value of the 'treatWarningAsErrors' attribute. If set to true, warnings generated by the database call will be handled in the same manner as errors. By default, this attribute is set to 'false'.

Parameters:

warningsAsErrors - The boolean value to be set.

getTreatWarningsAsErrors

public boolean getTreatWarningsAsErrors()

Returns:

The current value of the 'treatWarningAsErrors' attribute.

getSQLCode

public int getSQLCode()

Returns the SQLCode state variable associated with the last select() or execute() call. This method only returns a valid value if 'throwExceptionOnDatabaseError' was set to false prior to the select() or execute() call. This must be called to determine the success/failure status of the database call.

Returns:

The SQLCode integer value.

getSQLNativeError

public int getSQLNativeError()

Returns the SQLNativeError state variable associated with the last select() or execute() call. This method only returns a valid value if 'throwExceptionOnDatabaseError' was set to false prior to the select() or execute() call.

Returns:

The SQLNativeError integer value.

getSQLState

public java.lang.String getSQLState()

Returns the SQLState state variable associated with the last select() or execute() call. This method only returns a valid value if 'throwExceptionOnDatabaseError' was set to false prior to the select() or execute() call.

Returns:

The SQLState string.

getSQLErrorText

public java.lang.String getSQLErrorText()

Returns the SQLErrorText state variable associated with the last select() or execute() call. This method only returns a valid value if 'throwExceptionOnDatabaseError' was set to false prior to the select() or execute() call.

Returns:

The SQLErrorText string.