Portlet Factory, Version 6.1.2

 

The SQL Builders

The SQL builders are used to implement database operations. This powerful set of builders not only support JNDI database connectivity, but they also provide schema translation services that facilitate the conversion and display of ResultSet data. In addition, most SQL builders can be configured to fire database-related events. You can use these events to control and drive model actions.

SQL builders implement a JNDI-based approach to accessing DataSources, and support connections to both local and remote DataSources.

Note: IBM® WebSphere Portlet Factory relies on the application server to oversee all aspects all database management, including connection pooling and driver selection.

SQL builders also support the generation of database-related server statistics and logging. These statistics provide a way for you to analyze database performance issues. Logging information allows you to maintain audit trail of database operations, such as database changes and commits. The SQL builders include:

SQL Call

This builder provides a smart alternative to writing SQL code from scratch. It encapsulates the functionality of several low-level SQL builders (SQL DataSource, SQL Statement, SQL Transform to XML, and so on) into a single builder call. Use the SQL Call builder to get started interacting with a database. If you require the additional functionality of the low level SQL builders, use them as needed. The database explorer supports the use of single-table queries as well as stored procedures. In addition, you can create select, insert, update and delete statements that incorporate positional parameters.

SQL DataSource

Allows a model (and the other SQL builders) to obtain database connections from a local or remote JNDI-based DataSource. The DataSource is typically defined and managed by your application server and made available through a JNDI server provided as part of the application server's implementation.

SQL Statement

Creates Prepared or Callable SQL statements that are ready for execution. This builder manages all your DML (Data modeling Language) and DDL (Data Definition Language) operations. In this builder you write SQL-based database instructions such as stored procedure calls, inserts, deletes, selects, updates, create tables, create indices, and so on.

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. You can also make various performance-enhancing settings to the ResultSet and execution throttles to tune the query to the database.

Transforming Statement Results:

This builder also provides settings that allow for special-handling of columns via method-driven transforms. For example, you 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.

SQL Transaction

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, you 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, you can use a Data Page builder to display it.

SQL Transform to XML

Converts the result of an executed SQL statement into XML. The builder can create a full XML document or a paged version of an XML document. In the latter case, this output can be used by WebSphere Portlet Factory's Data Paging builder.

SQL Transform to XML Schema

Generates a complete XML schema as a result of the execution of one or more SQL statements. The schema created by this builder might serve as an input to another builder, such as a data page builder, at generation time.

SQL builders are designed to work in unison. For example, you can mix-and-match DataSource builders with a Statement builder to perform the same query on a variety of DataSources. Or, you can apply a single XML transform to a variety of result sets.

Parent topic: Working with a database

Library | Support |