SQL Statement builder inputsAdded by IBM on June 28, 2011 | Version 1 (Original)
|This topic describes the inputs for the SQL Statement builder.
This topic describes the inputs for the SQL Statement builder.
- Dynamic SQL IN Clauses
- Statement Parameters
- Result Set Custom Data Transforms
- Result Set Performance Hints
- Execution Throttles
- Events, Statistics and Logging
- Avoid null inputs
Table 1. General inputs
|Name||Required. Enter a name for this builder call. The WebSphere® Portlet Factory Designer displays this name in the builder call list.|
|SQL Statement||Required. Enter an SQL statement with optional positional parameters. The SQL you enter is not validated by the builder for correct syntax, so be sure to use legal SQL syntax for your target database when creating statements. |
SELECT * from EMPLOYEES where SALARY > ?
Note: Input parameters that are bound to SQL statements via the ? notation, can only be bound to elements in the where clause of an SQL statement.
Dynamic SQL IN Clauses
Use this input to add simple SQL IN clauses to a SQL statement. Use a SQL IN clause to specify multiple values in a WHERE
clause. For example, the SQL statement
SELECT * FROM EMPLOYEE WHERE LASTNAME IN ('JONES', 'SMITH')
will return records where the LASTNAME column contains JONES or SMITH. You can also use the SQL IN clauses in combination with other WHERE clause operators, for example:
Table 2. Dynamic SQL IN Clauses inputs
SELECT * FROM EMPLOYEE WHERE FIRSTNAME = ? AND LASTNAME IN ('JONES' , 'SMITH')
|Column Name||Required. Use the Column Name input to specify the column name in which that the values will be searched. If you do not specify a value, then a regen error will occur at design time.|
|Values||The Values input is used to specify values that will be matched in the column. The value of this input can be a
- String (hardcoded or indirect ref to a string)
- IXml variable
A delimited string can be used for this input. If the string type is used, the Values Delimiter input is used to parse the values from the string.Collection
A reference to a java.util.Collection that contains the values.IXml
An IXml variable. When an IXml variable is used, the builder expects IXml in the following structure:
The names of the elements RowSet and Row do not matter, only the structure. The builder uses the child nodes of RowSet as the values.
|Connector ||If multiple SQL IN clauses are specified, each IN clause must be separated by a connector. The connector can be either AND or OR. A regen error will occur if multiple SQL IN clauses are specified and a connector is not specified. The connector is not required for the last SQL IN clause, since there is not a subsequent IN clause. If it is specified, it is ignored. |
|Values Delimiter ||If the hard coded value or indirect value specified in the Values input is a String, then the delimiter specified in this input will be used to as the delimiter between values. The comma (,) and pipe (|) are selectable or you can specify your own delimiter. If the value specified in the Value input is an IXml variable or a java.util.Collection then this input is ignored.|
Table 3. Statement Parameters inputs
|Input Parameter Binding||Use this input to control the creation of variables used to store information related to any positional input parameter in the SQL statement. By manipulating the content of these variables you can dynamically drive a statement's positional input values.|
Enable to prevent the creation of positional parameter variables. You might want to use Manual binding when you have existing variables that you want to bind to statement parameters or when you want to supply non-variable values.Auto-create Distinct variables
Enable to have builder automatically create a variable for each positional parameter in the SQL statement. You might want to do this in order to access parameter values by pointing at a variable. In the WebApp, variable names appear as: BuilderName_arginput1, BuilderName_arginput2, and so on.Auto-create XML Variable
Enable to create a single positional parameter variable of type XML. You might want to use this variable to define all positional parameters based on an XML schema. This is useful when profiling is involved. However, accessing an individual value node within the XML structure requires you to use X-path references. In the WebApp, variable name appears as: BuilderNameXmlInputs.
|Input Parameters||Use these for all SELECT operations or stored procedure calls. Input parameters allow for substitution into the SQL statement in place of fields denoted by '?' characters. The following information is required for an Input parameter:|
(required) Numeric location of the "?" within the statement. Position numbering starts at "1" which represents the first "?" character.JDBC Type Cast
(required) All standard JDBC type names are supported. (Refer to JDBC documentation for specifics on each type available.) In addition, Automatic can be selected to instruct the builder to use automatic JDBC type conversion when setting up parameters. Custom can be selected to use a custom method to set up a complex column type. This setting is handy when working with driver-specific implementations of BLOB (binary large object) or CLOB (character large object) objects.Value or Set Method
(required) When using a "Custom" JDBC type cast, use this setting to provide the name of a method to be used for setting up the positional parameter. The builder will call this method and pass to it the prepared statement it has built. The method is then responsible for setting the positional parameter in the prepared statement.Sample Data
(optional) Use this setting to define the sample data that is picked up by the SQL Transform to XML Schema builder. Data you enter here is used to generate a sample row that is used to create an XML schema defining the content of the actual result set that will be produced by the SQL statement.
|Output Parameters||Output parameter definitions are substituted into the callable SQL statement in place of fields denoted by '?' characters. The following information is required for an output parameter: |
(required) Numeric location of the "?" within the callable statement that corresponds to this output parameter. Position numbering starts at "1" which represents the first "?" character.JDBC Type Cast
(required) JDBC type expected to be returned from the stored procedure call. All standard JDBC type names are supported. (Refer to JDBC documentation for specifics on each type available.) In addition, Custom can be selected to use a custom method to define the type of the output parameter This setting is handy when defining driver-specific structures. For example, if a "person" STRUCT is returned, you can use this setting to define that STRUCT as a type specific to your database.Cast Method
(optional) When using custom JDBC type casting, use this setting to select the name of a method to be used to set the output parameter's type. The builder will call this method and pass to it the prepared statement it has built. The method is then responsible for setting the type required.Transform Method
(optional) Use this setting to define a method that will be used to provide special handling of the data returned for this output parameter.Note: The Output Parameters input displays only when Input Parameter Binding is set to Manual.
Result Set Custom Data Transforms
Table 4. Result Set Custom Data Transforms inputs
|Custom Transforms||If the SQL statement produces a result set, then this input allows you to apply custom transforms to the columns in the result set.|
Enter a column position and transformation method to use on that column location. The following settings are available:
(required) Column position within the result set that contains data to be transformed. Position is denoted by a number between 1 and n.Transform Method
(required) Name of method to be called by the SQL Transform to XML builder that will provide special handling of data located in the column specified.
Result Set Performance Hints
Table 5. Result Set Performance Hints inputs
|Concurrency||Select a concurrency mode for the Result Set generated by this statement. Modes are: |
Underlying database result set is read-only.Updatable
Underlying database result set will support data changes.
Note: Some databases may not support updatable result sets. Check the documentation for your target database for detailed information about the types of result sets supported.
|Scroll Type||The type of scrolling used by the result set. Types include: |
Traditional result set type. Forward cursor movement only.Insensitive
Result set not sensitive to changes in underlying databaseSensitive
Result set is sensitive to changes in underlying database.Note: Some databases may not support all of these scroll types. Check the documentation for your target database for detailed information about the types of result sets supported.
|Fetch Direction||The direction in which rows of the Result Set are fetched. Directions include:|
Records retrieved in a forward direction from first to last row.Reverse
Records retrieved in a backward direction from last to first row.Unknown
Fetch direction is not known.
| Fetch Size||The number of rows to be fetched concurrently from the result set when additional rows are requested. |
Table 6. Execution Throttles inputs
|Max Field Size||Specify the maximum number of characters returned in a field or the maximum number of bytes in a column of type BINARY, VARBINARY, LONGVARBINARY, CHAR, VARCHAR, and LONGVARCHAR |
(Excess data will be silently discarded)
|Max Rows||Specify the maximum number of rows for any result set created from this statement. (Excess rows will be silently dropped.) A value of zero indicates that all rows are to be returned. |
Note: This setting limits the number of records in the result set. It can appear that all records are returned when in fact the database might contain additional matches over and above the number specified here.
|Execution Timeout||Specify the maximum time (in seconds) that this statement will be allowed to execute before being terminated. |
Note: When establishing this setting, be sure to consider database loading, network throughput, and other variables to insure that adequate time is allotted for execution even under "worst case" conditions.
Events, Statistics and Logging
These logs are stored in the WEB-INF/logs
directory of your deployment server.
Table 7. Events, Statistics and Logging inputs
|Log SQL Statements||Enable this box to log all of the SQL statements prepared for execution by the builder. The log message will include the actual values used for each positional parameter in the SQL.|
|Log SQL Warnings||Enable this box to log any SQL warnings that are generated as a result of preparing the SQL statement for execution.|
|Log Server Stats||Enable this box to generate server statistics for this builder instance.|
|Fire Create Events||Enable this box to fire an event when a statement is created and prepared for execution.|
Table 8. Advanced inputs
|Generated Key Column||Explicitly specify the name of the column that contains the automatically generated key to be returned from an INSERT operation. An Oracle database needs this column name to work successfully, but other databases can also use this approach. |
|Allow Dynamic SQL||Enable this input to use indirect references in the SQL Statement input. This is not recommended. To guard against SQL injection exploits, you should fully validate the values of the indirect references used in the SQL Statement.|
|Input Parameters are Search Criteria||Set this input to indicate that the builder has a SELECT statement that behaves as a search operation. The input parameters for the statement require special handling. (For example, the builder performs blank string suppression on the search criteria.) The input is clear by default.|
Avoid null inputs
If the type of a positional parameter is set to Automatic
, a null input can cause an application error. The JDBC interface requires the specification of a JDBC type if setting a parameter to NULL
, and a binding of Automatic will not provide type information at runtime. Therefore, you should either prevent null inputs (for example, setting form fields to "required" or providing enumerated selection controls) or ensure that any positional parameter that might be passed a null reference is typed with a specific JDBC type.
Parent topic: SQL Statement builder