SQL Statement builder inputs

Statement Parameters

Table 2. SQL Statement builder Statement Parameters inputs

Input Name	Description
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. Manual 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: Position (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: Position (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 3. SQL Statement builder Result Set Custom Data Transforms inputs

Input Name

Description

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: Column (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 4. SQL Statement builder Result Set Performance Hints

Input Name	Description
Concurrency	Select a concurrency mode for the Result Set generated by this statement. Modes are: Read Only 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: Forward Traditional result set type. Forward cursor movement only. Insensitive Result set not sensitive to changes in underlying database Sensitive 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: Forward 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.

Execution Throttles

Table 5. SQL Statement builder Execution Throttles

Input Name	Description
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 6. SQL Statement builder Events, Statistics and Logging

Input Name	Description
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.

Avoid Null Inputs

If the type of a positional parameter is set to Automatic, a null input will likely 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.