# How Do I: Create a RowSet Control to Access a Database?

WebLogic Workshop comes with a built-in RowSet control that defines methods to read from and write to a relational database. A RowSet control uses a Java RowSet object, which holds records that were read from a database or that contain the data that needs to be updated. RowSet objects are for instance used to hold data that is displayed in a JSP using a grid (for more information, see Presenting Complex Data Sets in JSPs). When you define a RowSet control for a database, the methods and their SQL query expressions that provide access to the database are automatically created for you. Altering the auto-generated SQL has unpredicable results: in general you should avoid changing the SQL in the RowSet control.

Certain XA database drivers must support local transactions for the RowSet wizard to work correctly. To enable local transactions on your XA driver. (1) Open the WebLogic Server console. (If WebLogic Server is running, you can open the console by visiting http://localhost:7001/console.) (2) Logon to the console with the username/password: weblogic/weblogic. (3) On the left-hand navigation panel, navigate to [Your server] --> Services --> JDBC --> Connection Pools --> [Your SQLServer pool]. (4) On the right-hand content panel, select the Connections tab, click Advanced Options Show, at the bottom of the page, place a check next to SupportsLocalTransaction, and click Apply. (5) Restart WebLogic Server and begin the RowSet control wizard.

To Create a RowSet Control

1. Right-click the folder that should contain the RowSet control and select New-->Other File Types. The New File dialog appears.
2. In the upper-left pane, select Business Logic.
3. In the upper-right pane, select RowSet Control.
4. Click Create. The RowSet Control Wizard appears.
5. In the Name and Data Source dialog, enter a name for the RowSet control. Make sure that the control will have a JCX extension. Also, select the target database from the Data Source dropdown list. Click Next.
6. In the Select Methods dialog, select whether you want to create methods that provide full access to the table, or whether you only want query methods (or even no methods at all). In most cases you will want to select the option Query and update a database table. When you select this option, all the methods that are necessary to read, update, insert, and delete one or more records are created for you.
7. In the Select Methods dialog, also select the table that you want to access by selecting the appropriate schema and table. Click Next.
8. In the Select Columns dialog, select the columns that you want to use. The grid column determines whether the record field will be read from the database, and will be displayed in the overview table in a page flow (when you generate a page flow for the RowSet control). If you clear a field's grid column checkbox, the contents of this field will not be retrieved from the database during read operations. The read-only column determines whether you can update a value in this record field. However, it does not affect the ability to delete or insert records. In other words, if you select a column as read-only, you can insert a new record into the table and provide a value for this column, but you cannot update the value in this column at a later time.

   If you want full access to all the record fields, you want to use the default settings. Click Next.

   Note: To determine whether a record field is read-only, you can examine the RowSet's XML schema defined in the @common:define name="rowset-schemas" tag. Read-only elements will contain the attribute wld:ReadOnly="true". The XML schema parameterizes the RowSetMetaData interface. For more information on RowSet and RowSetMetaData, see the Java API documentation.
   Note: If you select multiple columns as the primary key columns, the generated SQL statement that inserts new rows into the database will be incomplete. The incomplete part of the SQL statement will be marked by "TODO". For example,
   SELECT TAXID,FIRSTNAME,LASTNAME,CURRENTLYBANKRUPT FROM WEBLOGIC.BANKRUPTCIES WHERE TAXID = ( TODO: Add sql to retrieve newly inserted row for user entered primary keys-WEBLOGIC.BANKRUPTCIES)
   Complete the SQL statement according to the syntax of your particular database.

9. In the Primary Key Source dialog, select the source of the primary keys.
   If your database application automatically generates primary keys for newly inserted records, use the default selection Automatically generated by the database. (Database applications typically generate primary keys by using a sequence table (Oracle) or an IDENTITY field in the database table (Pointbase, Sybase, SQLServer, etc.).
   Alternatively, you can also select SQL statement returning new keys and provide a SQL statement that will return a new primary key value, allow the user to enter a unique key by selecting Entered by user at runtime, or disable the insertion of new records by selecting Don't allow new record creation. In the last case the insertItems and getInserted methods will not be created for the RowSet control.

   Note: the option Entered by user at runtime allows users to specify the primary key value of records inserted into the database without following any set sequence if values. However, for integer primary key fields, there is one limitation on the insertion of new records: users may not insert new records with a primary key value that is less than the highest primary key value currently in the database. If the database currently has a primary key value of 1000, users may not insert a record with a primary key value of 999. In this case, the control will autoincrement a primary key value of 1001.

10. Click Create to create the RowSet control.

You can use the RowSet control in the same way as you would a Database control, including adding it to a custom control. You can also easily create a page flow to access the RowSet control. For more information, see How Do I: Create a Database Control Page Flow?

Related Topics

Presenting Complex Data Sets in JSPs

How Do I: Create a Database Control Page Flow?

Using Built-In Java Controls

Database Control