Portlet Factory, Version 6.1.2

---

 

SQL Call builder

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.

 

Key inputs

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:

• Name

• SQL Data source

• SQLStatement

• 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

• Inputs

• Results

 

Samples

A complete sample shows you how to use IBM® WebSphere Portlet Factory to create a service provider that uses data from a relational database.

http://www.lotus.com/ldd/pfwiki.nsf/dx/ibm-creating-a-sql-service

 

More about this builder

Database Explorer

Use the Database Explorer if you need assistance in creating an SQL statement.

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.

Enhance SQL readability

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

Instead use:

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.

• About using the builder call editor

  The builder call editor allows you to specify all the input values to a builder call.

• SQL Call builder inputs

  This topic describes the inputs for the SQL Call builder.

• Using override builders

  The SQL Call builder leverages many of the low-level SQL builders during its generation phase.

• Schema generation limitations with SELECT statements

  When using the SQL Call builder to execute a SELECT statement, there are two scenarios in which the SELECT statement as entered in the SQL Statement input cannot be used to generate an XML schema for the returned result set.

• About working with BINARY and VARBINARY results

  Some database columns are stored as binary fields even though the data they represent is simple strings.

• Working with a database

  IBM WebSphere Portlet Factory provides a variety of database tools from high-level to low-level.

• SQL DataSource builder

  The SQL DataSource builder allows your model (and the other SQL builders) to obtain database connections from a local or remote JNDI-based DataSource.

Parent topic: Builder help

---

Library | Support |