SQL Data Access Parts are provided in the com.ibm.beans.samples.daps.sql package. These parts provide access to data available by JavaSoft's JDBC interface. JDBC access is manifested through the SQL language. The JDBC drivers pass the SQL text to a registered JDBC driver and return results. This package provides the following beans to access a database by JDBC:
You can use a combination of these beans to perform database operations as described in the following sections.
These beans use JavaBean bound properties and event connections to support data flow and communication. These values can be accessed and modified through Assembly Surface event wiring, scripting, property sheet editing, or some other comparable mechanism.
The successful property is always the last property set when executing a method. When using background execution (the executeInBackground property being set to true), the background thread execution has completed by the time that the successful property was set.
The Connection bean implements a connection to a JDBC driver. This connection is shared among the multiple DDL, InsertUpdateDelete, and Select SQL DAP beans. To use these beans, they require a JDBC connection to a database. The Connection Bean surfaces the JDBC connection's transaction support through the commit and the rollback methods.
The Connection Bean is not intended to assist the user in registering a JDBC driver nor to assist in constructing the JDBC URL to find a registered driver. The JDBC driver name is a Java class name and is driver specific. The JDBC URL syntax is defined as jdbc:subprotocol:subname. For example, to access a database through a JDBC-ODBC bridge, you use the URL:
jdbc:odbc:DB2
In this example, the subprotocol is odbc and the subname is the local ODBC data source name DB2.
The Connection bean includes bound property information pertaining to whether the database operations are to be performed in the background, the JDBC Driver Name, the JDBC Driver URL, the userid and password, the JDBC connection used by the JDBC beans, the autoCommit flag, the readOnly flag, the transaction isolation value, and the success of the method.
The Connection bean supports connect(), disconnect(), commit(), and rollback() methods. It does not have a user interface, and it is represented within the Assembly Surface by an icon. At runtime, this bean is invisible.
To use the Connection bean, do the following:
autoCommit | A flag that indicates whether autoCommit is performed after each statement. The connect() method uses this property. This property must be set prior to execution. The default is true. |
executeInBackground | A flag that indicates whether database operations are to be performed asynchronously in a background thread or synchronously in the current thread. The default is false. |
JDBCDriverName | The JDBC driver name is used to instantiate the JDBC Connection. This string identifies the database driver for the connection and is used by the connect() method. The driver name must be set before the execution of the connect() method. The default value is sun.jdbc.odbc.JdbcOdbcDriver. |
JDBCURL | The JDBC URL used to instantiate the JDBC Connection. This string identifies the database target for the connection. The connect() method uses this property. This property must be set prior to execution. The default is jdbc:odbc:DB2. |
password | The password of the userid using the database. The connect() method uses this string. This property must be set prior to execution. The default is an empty string. |
readOnly | A flag that indicates whether the database connection is in read- only mode. The connect() method uses this flag. This property must be set prior to execution. The default is false. |
transactionIsolation | The transaction isolation values. Possible values for this property
are:
The connect() method uses this value. This property must be set before execution. The default is REPEATABLE_READ. |
userid | The user ID of the person using the database. The connect() method uses this string. This property must be set prior to execution. The default is an empty string. |
exception | The exception for this method invocation. This is null if there is no exception. |
JDBCConnection | The JDBC java.sql.connection. The disconnect(), commit(), and rollback() methods use this property. The JDBCConnection property of later DDL, InsertUpdateDelete and Select beans should be bound to this property. |
successful | Indicates whether the method was successful. |
exception | The exception for this method invocation. This is null if there is no exception. |
successful | Indicates whether the method was successful. |
exception | The exception for this method invocation. This is null if there is no exception. |
JDBCConnection | This field is reset to null. |
successful | Indicates whether the method was successful. |
The DDL bean modifies the database schema through SQL DDL statements and includes bound property information. The DDL bean supports an execute() method. It does not have a user interface, and it is represented within the Assembly Surface by an icon. At runtime, this bean is invisible.
To use the DDL bean, do the following:
executeInBackground | A flag that indicates whether database operations are to be performed asynchronously in a background thread or synchronously in the current thread. The default is false. |
SQLText | The SQL DDL statement text for this statement. The execute() methods uses this value. This property must be set prior to execution. The default is an empty string. |
exception | The exception for this method invocation. This is null if there is no exception. |
successful | Indicates whether the method was successful. |
exception | The exception for this method invocation. This is null if there is no exception. |
successful | Indicates whether the method was successful. |
The InsertUpdateDelete bean supports addition, modification, and deletion operations on data within a database using the SQL Insert, Update, and Delete statements. The InsertUpdateDelete bean includes bound property information pertaining to database operations.
The InsertUpdateDelete bean supports the execute() method. This bean does not have an interface and is represented within the Assembly Surface by an icon. At runtime, the bean is invisible.
To use the InsertUpdateDelete bean, do the following:
executeInBackground | A flag that indicates whether database operations are to be performed asynchronously in a background thread or synchronously in the current thread. The default is false. |
inputs | Vector of input objects. The execute() method uses the Vector to set values for SQL parameter markers. This property must be set prior to execution. The default is a null. |
SQLText | The SQL Insert, Update, or Delete statement text. The execute() method uses this value. This property must be set prior to the execute() method. The default is an empty string. |
exception | The exception for this method invocation. This is null if there is no exception. |
numberRowsUpdated | The number of database rows affected by the SQL Insert, Update, or Delete statement. |
successful | Indicates whether the method was successful. |
exception | The exception for this method invocation. This is null if there is no exception. |
numberRowsUpdated | This field is reset to 0. |
successful | Indicates whether the method was successful. |
The Select bean implements a database query. It supports scrolling through the Select statement's result set. The Select bean includes bound property information. This bean supports the execute() method and the next() method. It does not have an interface, and it is represented within the Assembly Surface by an icon. At runtime, the bean is invisible.
To use the Select bean, do the following:
executeInBackground | A flag that indicates whether database operations are to be performed asynchronously in a background thread or synchronously in the current thread. The default is false. |
inputs | Vector of input objects. The execute() method uses the Vector to set values for SQL parameter markers. This property must be set prior to execution. The default is null. |
maxRowsReturned | The maximum number of rows that can be returned by a Select statement. The execute() method uses this field. This property must be set prior to execution. The default is 200. |
maxFieldSizeReturned | The maximum size of a single field in a returned row. The execute() method uses this field. This property must be set prior to execution. The default is 4096. |
SQLText | The SQL Select statement text. The execute() method uses this value. This property must be set prior to execution. The default is an empty string. |
columnNames | A Vector of strings representing the columns of this Select bean. |
columnLengths | A Vector of Integers representing the columns lengths of this Select bean. |
columnNullables | A Vector of Integers representing the columns nullable characteristics of this Select bean. |
columnTypes | A Vector of strings representing the columns types of this Select bean. |
exception | The exception for this method invocation. This is null if there is no exception. |
successful | Indicates whether the method was successful. |
exception | The exception for this method invocation. This is null if there is no exception. |
resultRow | A Vector of objects representing the last result row found. Java objects in the Vector are created as documented in JavaSoft's JDBC documentation. This is a logical conversion from the SQL type to a corresponding Java object type. |
rowFound | Indicates whether a row was found. |
successful | Indicates whether the method was successful. |
columnNames | This field is reset to null. |
columnLengths | This field is reset to null. |
columnNullables | This field is reset to null. |
columnTypes | This field is reset to null. |
exception | The exception for this method invocation. This is null if there is no exception. |
resultRow | This field is reset to null. |
rowFound | This field is reset to false. |
successful | Indicates whether the method was successful. |
The Viewer bean displays the objects of the Select bean's result Vector using the toString interface of the object. The Viewer bean supports the clear() method. The Viewer bean extends the java.awt.TextArea class to display the text version of all the objects contained within the output Vector object of the Select bean.
To use the Viewer bean, do the following: