Use the SQL Call builder to access a relational database using SQL statements and stored procedures.
This builder is typically used in a service provider model to implement a service operation. Use this builder to access a data store, or create view and form pages based on data from a specific back end system.
This high-level builder encapsulates the functionality of several low-level SQL builders (SQL DataSource, SQL Statement, and SQL Transform to XML). This encapsulation enables you to add sophisticated database access capabilities to a model with a single builder.
Note that you need to feed the data from the SQL Call builder into another builder, such as Data Page or View & Form builder. You can get the benefit of service provider/consumer model by putting the SQL Call builder in the provider, and feed the data to a builder (like View & Form builder) in the consumer.
In a typical usage scenario, the SQL Call Builder could be used to create a service provider model that accesses a relational database. A typical provider model would contain the following builders.
- A Service Definition builder, often with testing support enabled for easy testing of database functionality.
- One or more SQL Call builders, to retrieve or store data from the database.
- One or more Service Operation builders, corresponding to the SQL Call builders, so that each operation is implemented with a corresponding SQL Call.
In most common applications of this builder, use the Database Explorer to builder your SQL statement, and use these inputs to complete your service provider:
- SQL Data source
- Transform result
- Parameter Binding - automatic is recommend in most applications
- Results set - the default settings are recommend in most applications
Key objects this builder creates
When used to create a service provider that uses data from a relational database, the key objects this builder creates are:
- Data service
A complete sample shows you how to use IBM® WebSphere® Portlet Factory to create a service provider that uses data from a relational database.
More about this builder
Use the Database Explorer if you need assistance in creating an SQL statement. Enhance SQL readability
You can browse entities in the database and construct the SQL statement as you are browsing. This is particularly useful if you are not an expert in using SQL. As you specify various inputs to the Explorer, you build a statement that can then be edited.
Enter your SQL statements using multiple lines to make the SQL statement easier to read.Avoid Escape Characters
Do not use an escape character in front of quotes in SQL statements. For example, do not use:
select count(* ) \\" Count\\ " from sometable
select count(*) "Count" from mytable
How this builder works
This builder allows your model 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 implementation. The DataSource created by the application server can provide a set of additional features beyond simply handing out JDBC connections for access to a database. Some of those features might include connection pooling, stale connection handling, and connection time outs.
Authentication and credentials
This builder is designed to use the authentication credentials in force on the JNDI connection. Thus, requests from this builder to the DataSource are passed directly through the JNDI connection. This builder does not provide inputs for user-specific credentials. However, if user-specific credentials are required, you can use an SQL DataSource builder in the model to override the connection functionality provided by this builder.
Parent topic: Builder Reference: wpf7
About using the builder call editor: wpf7
Working with a database: wpf7
SQL DataSource builder: wpf7