Portlet Factory, Version 6.1.2
SQL Statement builder
Use the SQL Statement builder to create Prepared or Callable SQL statements that are ready for execution.
This builder manages all your Data Modeling Language (DML) and Data Definition Language (DDL) operations. In this builder, you write SQL-based database instructions. For example, you can create stored procedure calls, inserts, deletes, selects, updates, create tables, and create indices.
The following setting groups are available:
- Required
- A builder name and SQL statement are required for this builder. The SQL you enter must be valid SQL syntax for the database you are targeting and can include any valid SQL character.
- Statement Parameters
- (optional) If the statement you enter has positional parameters indicated by question mark (?) characters, you can provide values for those characters using these settings. When the statement is prepared for execution, the parameters you enter here are picked, defined, and added to the statement. Input parameters typically specify a value used in a query or stored procedure call. Output parameters are used to support the calling of stored procedures and the retrieval of data from their output parameters.
- Result Set Custom Data Transforms
- (optional) These settings are used when a result set contains complex JDBC column types such as ARRAY, BLOB, and STRUCT or you want to apply some custom processing to a column as part of the result set transformation.
For example, if a result set column contains all uppercase characters, you might create a method to lowercase the column contents as part of the result set transformation into XML.
- Result Set Performance Hints
- (optional) These settings allow the builder to create a result set tuned for a particular usage pattern. For example, you might tune a result set for read-only use or a specific fetch direction.
- Execution Throttles
- (optional) These settings allow you to place limits on a statement execution. For example, you might want to do this to constrain the number of rows returned by a query. Using a constraint such as this can prevent overload of network, database, or application resources.
- Events, Statistics and Logging
- (optional) These settings enable you to collect database-related server statistics and work with database-related events.
Quick Tips
- Enhance SQL readability
- Enter your SQL statements using multiple lines to make SQL easier to read.
- Use Automatic JDBC Type Cast
- When you do not know the type of a positional parameter, select Automatic to allow JDBC to determine the type for you.
- 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
- Database Syntax Issues
- Some databases let you create mixed-case table or column names. Typically, this practice is not wise and can lead to problems. In addition, table and column names should never be declared with quotation ("") marks. The naming of data entities in the database should not be influenced by the presentation and display of that data.
Parent topic: Builder help
- About using the builder call editor
The builder call editor allows you to specify all the input values to a builder call.
- SQL Statement builder inputs
The SQL Statement builder takes the inputs described in this table.
- About tracking database events
The SQL Statement builder can fire this database-related event in the model.
- About writing custom JDBC set/type-cast methods for stored procedure output parameters
If you specify a "Custom" JDBC type-cast for an output parameter of the SQL statement, include in your model a method that will perform the type-casting of the parameter for the builder.
- About writing custom set methods for input parameters
If you specify a "Custom" JDBC type-cast for an input parameter of the SQL statement, then you will need to include in your model a method that will perform a type-cast and value definition of the parameter for the builder.
- Writing ResultSet Column and Output Parameter Transform methods
This topic describes the parameters that a custom method must take.
- Working with a database
IBM® WebSphere Portlet Factory provides a variety of database tools from high-level to low-level.
Library | Support |