Overview: Working with a database
Related Topics ...
How to Use the SQL Call Builder
The Factory provides a variety of database tools from high-level to low-level. These tools allow you to connect to and interact a database. In terms of functionality, the tools can be described as follows:
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 low-level 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.).
JNDI datasources must be correctly installed and configured on the application server to be accessible from a model.
The SQL Builders
The SQL builders are used to implement database operations. This 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. We 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 that the 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 -- Encapsulates into a single builder call the functionality of several low-level SQL builders (SQL DataSource, SQL Statement, SQL Transform to XML, etc.). 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.
- 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, etc.
- SQL Transaction Creates either a short-lived or a long-lived SQL transaction. For example, using this builder we can create a list of prepared statements or callable transactions that are execution-ready.
- 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 the 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, we can mix-and-match DataSource builders with a Statement Builder to perform the same query on a variety of DataSources. Or, we can apply a single XML transform to a variety of result sets.