Working with a database

In this topic ... Connect to a database Build an SQL statement Execute an statement Cache query results Initiate a transaction and display results Optimize database operations Related Topics... Overview - Working with a Database How to use the SQL Call Builder How to use the SQL Datasource Builder How to use the Cache Control Builder

The Factory provides a variety of database tools from high-level to low-level. These tools allow you to connect to and interact with a database. In terms of functionality, the tools can be described as follows:

• Relational Database New Model Wizard - A wizard that walks you through the steps required to build a model that contains all the Builders necessary to retrieve, create, update, edit, and delete database entries. This wizard provides the best solution for most general database tasks and will also construct an SQL query and set up page displays for the data.

• SQL Call Builder - Use this is a "high-level" builder if we need to run a stored procedure on a database. In addition to providing basic connectivity, this builder includes a Database Explorer that will assist you in constructing an SQL query. This Builder also provides settings that allow you to control the formatting and translation of a returned result set.

• SQL Family of Builders - These Builders provide the core functionality required to access and use a database installed on a local or remote application server. The SQL Builders interact with a database through a Java Naming and Directory Interface (JNDI) datasource on the application server. The application server manages all aspects of database connections (pooling, credentials, time outs, etc.).

 

Connect to a database

There are several ways we can establish a connection to a JNDI datasource installed and running on an application server:

JNDI datasources must be correctly installed and configured on the application server to be accessible from a model.

• Create a Relational Database Model - Use the New Model Wizard to build a relational database model:

1. Start the New Model Wizard by selecting: File > New | Other | Factory Model.

2. Select a project in which the new model will be created.

3. In the Select Wizard page choose: Data Integration | Relational Database

4. Proceed through the wizard pages providing the requested information. When you click "Finish" a new model is created that provides access to the specified JNDI datasource.

• Add a SQL Call Builder to an existing model - Use this builder to establish a database connection and retrieve data:

1. Place an SQL Call Builder in a model.

2. Fetch datasource names and assign a datasource to the builder.

3. Use the Builder's Database Explorer to locate tables or stored procedures or to generate a sample SQL statement.

4. Use the Builder's ResultSet handling settings to format data returned by the query and transform results into XML for display

5. Use a Data Page Builder to display the data returned by the SQL Call builder.

• Add a SQL Datasource Builder to an existing model - Use this approach to add basic database connectivity to a model. You must add other builders to the model to implement an SQL query, format the data for display and display query results. When you configure this Builder you identify the JNDI datasource you want to access and the credentials required for access. If the server the datasource is on is remote, provide additional configuration items required by the server.

This builder provides low-level connectivity, and it's functionality is incorporated in both the Relation Database Model wizard and the SQL Call Builder. The builder also provides many additional configuration inputs. We can use these additional builder inputs to accomplish advanced connection tasks including:

• Connecting to a remote JNDI server

• Providing authentication information to the database

• Defining connection timeout parameters

• Firing database connection-related events that ca be handled in the WebApp

• Generating and logging database connection statistics and activities

Learn more about database connectivity review: Overview: Working with Data, How to use the SQL Datasource Builder, How to use the SQL Call Builder.

 

Build an SQL statement

The Designer provides several Builders we can use to work with SQL statements. These include:

• SQL Call Builder - This Builder provides a smart alternative to writing SQL code from scratch. To build an SQL statement using the Database Explorer feature of this builder:

1. Open the Database Explorer portion of the Builder's edit page.

2. Browse data tables and construct and generate sample SQL code.

3. Edit this sample code to create the exact statement we need.

The database Explorer supports the use of single-table queries as well as stored procedures. In addition, we can create Select, Insert, Update and Delete statements that incorporate positional parameters.

• SQL Statement - This Builder provides more basic functionality than the SQL Call Builder. The SQL Statement Builder is designed to work with a prepared or callable SQL statement that is ready for execution. The Builder allows you to specify to the statement values for positional parameters. We can also make various performance-enhancing settings to the resultset and execution throttles to tune the query to the database.

To use a prepared SQL statement in this builder:

1. Code a valid SQL statement in an editor such as Notepad.

2. Paste the text of the statement into the Builder or identify an indirect reference where the text is located, such as a Variable.

3. If the statement includes positional parameters (?), identify those parameters in the Builder's Statement Parameters tables.

Transforming Statement Results

This Builder also provides settings that allow for special-handling of columns via method-driven transforms. For example, we can use this feature to transform an output parameter to a record set.

In addition to SQL composition, the SQL Statement builder provides a way for you to apply a custom transform method to a column in a resultset. This allows you to control the XML formatting of returned data on a column-by-column basis. You might need to do this if the resultset contains complex JDBC column types, or if you want to apply other formatting to the resultset.

1. Add a SQL Transform to XML builder to the model to store your results in an XML variable.

2. Add a SQL Transaction builder to the model, specifying the name of your DataSource, SQL Statement, and SQL Transforms as a transaction step.

To learn more about working with SQL, review help for the How to use the SQL Call Builder and How to use the SQL Statement Builder.

 

Execute an SQL statement

You use the SQL Transaction builder to chain together other SQL builders in the model to create a transaction sequence. Once you have composed a query and provided transformation information, you can build an entire transaction. This builder can create either a short-lived or a long-lived SQL transaction. Such a transaction might identify a datasource, the SQL statement to execute, and a transform to apply to the resultset.

When running an SQL statement against a database, we can track statement-related database events in the model and also generate statistics and logs related to a statement execution. These options allow you to assess performance and maintain an audit trail of statement usage.

If the SQL statement returns data, we can use a Data Page builder to display it. See "Displaying Data with the Data Page" and the help for the Data Page builder for more information.

 

Cache query results

The Cache Control builder is the tool you use to cache the results of a database query. This Builder allows you to store the results of a model action, such as a method that initiates a database call. Results of the action are placed in the model's output cache and are available to multiple users having the same profile. In database operations this means that a single query can be used to service multiple users, providing those users have similar profiles. This minimizes unnecessary trips to the database and reduces system load.

This type of caching is appropriate for data that does not change too frequently (once per hour) and also data that is identical for a number of users. A typical example of data that might benefit from caching is employee data accessed by an HR department group.

To set up caching for a database query:

1. Place a Cache Control Builder in a model.

2. Configure the Builder by selecting the Action to Cache.

In most cases, the specific "Action to Cache" is the "executeForCaching" method on the <sqlCallName>Transform LJO. This LJO is added to the WebApp by the SQL Call Builder.

3. Tell the Builder the length of time to retain information in the cache (Refresh Interval input) and the trigger to use to refresh the cache (Keys input).

To learn more about caching, review: How to use the Cache Control Builder, How to Interpret Factory Statistics, and Factory API documentation for the com.bowstreet.util.cache class.

 

Initiate a transaction and display the results

A transaction is a combination of database related activities that culminates in the display of data returned from a database by a query. The Factory provides support for sophisticated database transactions in the form of the "SQL" family of builders. By combining SQL builders in various ways, we can create a complex transaction that runs an SQL statement against a particular JDBC datasource, transforms the resultset data returned, builds a schema from the data, and displays the formatted data on a page in your model.

The key to transaction management is the SQL Transaction builder. This builder works in conjunction with other SQL builders to create either a short-lived or a long-lived SQL transaction. Using this builder we can create a list of prepared statements or callable transactions that are execution-ready.

To implement a database transaction:

1. Make sure you have an SQL DataSource builder call in your model that points to the JNDI database you want to query.

2. Include an SQL Statement builder in the model to create a statement or stored procedure to run against the database.

3. Create an XML transform to format the returned resultset.

4. Use the SQL Transaction builder to link together various SQL builder artifacts in the model.

5. If desired, use an SQL Transform to XML Schema builder to generate a schema for the returned data.

6. Use the results of the transaction as input to a Data Page builder to create a display page/form that exactly matches the data.

Displaying Transaction Results

Usually you will want to feed the results of a transaction to a display mechanism in order to view the results. The Factory, the provides several display options, with the most being the Data Page builder. This builder can automatically create a form that either displays data and/or prompts the user to enter data.

The Data Page builder references a schema (possibly created by the SQL Transform to XML Schema builder) or a variable associated with a schema type, to determine the form fields to display. The Data Page builder can also create input forms based on an element type defined by a schema and an associated variable.

To learn more about building transactions, review help for these builders:  How to use the SQL Transaction Builder, How to use the SQL Transform to XML Builder, and How to use the SQL Transform to XML Schema Builder.

 

Optimize database operations

There are several techniques we can use to optimize your database operations. These include:

1. Use a Cache Control Builder to store relatively-static results of a query. If the data does not change more frequently than hourly, caching the database action is a good strategy.

2. Use settings available in the low-level SQL builders to maximize database efficiency. For example, The SQL Datasource Builder provides Timeout settings to manage connection and acquisition of a database. The SQL Statement Builder provides ResultSet Performance Hints settings and Execution Throttles that allow you to optimize resultset handling and control the amount of data being retrieved.