SQL Statement Builder

SQL Statement Builder

In this topic ...

Quick Tips
Specifying Inputs
Writing Custom Set Methods For Input Parameters
Writing Custom JDBC Set/Type-Cast Methods For Output Parameters
Writing ResultSet Column and Output Parameter Transform Methods
Tracking Database Events
Related Topics ...

Overview: Working with Data
How do I...Work with a Database?

Use this Builder to create Prepared or Callable SQL statements that are ready for execution. This Builder manages all your DML (Data Modeling Language) and DDL (Data Definition Language) operations. In this Builder you write SQL-based database instructions. For example, we can create stored procedure calls, inserts, deletes, selects, updates, create tables, create indices, etc.
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, we can provide values for those characters using these settings. When the statement is prepared for execution, the parameters you enter here will be 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 s 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 s 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's 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.

How do I...

Connect to a database?

Build an SQL Statement?

Execute an SQL Statement?

Initiate a database transaction and display the results?

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, such as Db2, let you create mixed-case table or column names. In most cases 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.

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 (e.g. 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.

Specifying Inputs

The SQL Statement Builder takes the inputs described in the table below. For help on inputs common to many or all Builders such as those in the Properties and HTML Attributes input groups, see "Using the Builder Call Editor."

Input Name Description
Name Enter a name for this Builder call. The Designer displays this name in the Builder Call List.
SQL Statement 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.
Example:
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 a SQL statement.
Statement Parameters
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, etc.
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 Use these settings only when calling a stored procedure. 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, we 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.

Result Set Custom Data Transforms
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
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
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 "worse case" conditions.
Events, Statistics and Logging
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 Prepare Events Enable this box to fire an event when a statement is created and prepared for execution.

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 ll need to include in your model a method that will perform a type-cast and value definition of the parameter for the Builder. These custom methods must take the following parameters and return void:

BuilderName - This string parameter will contain the name of the SQL Statement Builder that is calling the method to perform a custom type-cast and value definition. We can define one method for each custom type-cast you have defined or we can bundle all of your type-casts together and use this input to identify which specific type-cast to apply when called by the Builder.

statement This object parameter is the actual JDBC PreparedStatement or CallableStatement that was created by the Builder and is being prepared for execution. The method is responsible for setting the input type of the positional parameter indicated by the position parameter. It is also responsible for defining the input value to be used by the statement when it s executed.
position This integer parameter identifies the positional parameter in the SQL statement for which the type-cast is to be performed.

The following Java code sample is the body of a custom set method for an input parameter. In this example we do not need to differentiate between SQL Statement Builders, so we ignore the BuilderName input. We cast the statement parameter into a JDBC PreparedStatement and then proceed to set the value and type of the statement s positional parameter denoted by the position parameter of the method.
  try
  {
     PreparedStatement ps = (PreparedStatement) statement;
     if( ps != null ) ps.setString( position, "SYS" );
  }
  catch( SQLException e )
  {
     e.printStackTrace();
  }

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. Such a custom method must take the following parameters and return void:

BuilderName - A string parameter that contains the name of the SQL Statement Builder that is calling the method to perform a custom type-cast. We can define one method for each custom type-cast you have defined, or we can bundle all of your type-casts together and use this input to identify which specific type-cast to apply when called by the Builder.

statement An object parameter that is the actual JDBC PreparedStatement or CallableStatement that was created by the Builder and is being prepared for execution. The method is responsible for setting the output type of the positional parameter indicated by the position parameter.
position This integer parameter identifies the positional parameter in the SQL statement for which the type-cast is to be performed.
operation -- This new string parameter is used to specify whether the method is being called to "cast" an output parameter of an stored procedure call, or to "set" the output parameter's value.

The following Java code sample is the body of a custom type-cast method for an output parameter. In this example we do not need to differentiate between SQL Statement Builders, so we ignore the BuilderName input. The statement parameter is cast into a JDBC CallableStatement. Then the type of the statement s positional parameter denoted by the position parameter of the method is set:
{
  try
  {
    CallableStatement stpCall = (CallableStatement) statement;
    if( stpCall != null )
    {
      if( "set".equals( operation ) )
      {
        stpCall.setInt( position, 100 );
      }
      else if( "cast".equals( operation ) )
      {
        stpCall.registerOutParameter( position, Types.NUMERIC );
      }
    }
  }
  catch( SQLException e )
  {
    throw new WebAppRuntimeException( e );
  }
}

Writing ResultSet Column and Output Parameter Transform Methods

If you define a custom transform for a result set column or output parameter of the SQL statement, include in your model a method that will take the value of the column or parameter and transform it into and XML element. These custom methods must take the following parameters and return an IXml object:

BuilderName - A string parameter that contains the name of the SQL Statement Builder that is calling the method to perform a custom transform on a result set column.
columnNumber - An integer parameter that identifies either the SQL statement s result set column or the output parameter that is to be transformed by the method. We can define one method for each column / output parameter that you will transform manually or we can bundle all of your transforms together and use this input to identify which column / output parameter is being transformed by the Builder.
columnValue This object parameter is the actual result set column value or output parameter that this custom method is to transform into XML. Note that the value will be null if the column / output parameter contain a null value.
elementName A string parameter that provides the name of the XML element containing the transformed data.
createVerboseXml A boolean parameter that tells the method whether or not it s allowed to create XML that includes any special element attributes meaningful to this particular transformation. In most cases this parameter can be safely ignored.

The following Java code sample is the body of a custom transform method. In this example we expect the value to be a string (VARCHAR or equivalent) that is converted to lowercase. Since we do not need to differentiate between SQL Statement Builders and result set columns/ output parameters, we ignore the BuilderName and columnNumber inputs. We cast the columnValue parameter into a string and then proceed to transform the value into an XML element with a name provided by the elementName parameter of the method.
String s = (String) columnValue;
IXml element = XmlUtil.create( elementName );
element.setText( s.toLowerCase() );
return element;

Tracking Database Events

This Builder can fire the following database-related event in the model:

Create Event - This event provides a way for your model to manipulate a prepared statement before it is handed off to other Builders in the model for execution.

When the Builder is configured to fire events, it will create and register in the model an event named SqlStatementBuilderEvent. This name then becomes available as an option for the "Event Name" input of an Event Handler Builder. When the Builder fires this event at run-time, it passes three parameters that can be used by an event handler action to manipulate the JDBC prepared statement just created and prepared for execution. Those parameters are:

BuilderName - This string parameter will contain the name of the SQL Statement Builder that fired the create event. Since all Statement Builders in a model use the SqlStatementBuilderEvent event name, this input allows you to discriminate among all of the Statement Builders when handling events.
eventType - This string parameter indicates what type of event was fired. Only one value is currently available, and that is "Create".
statement - An object parameter that is the actual JDBC prepared statement (or callable statement for stored procedure calls) that was created and prepared for execution by the Builder.

Input Name	Description
Name	Enter a name for this Builder call. The Designer displays this name in the Builder Call List.
SQL Statement	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. Example: 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 a SQL statement.
Statement Parameters
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, etc. 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	Use these settings only when calling a stored procedure. 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, we 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.
Result Set Custom Data Transforms
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
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
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 "worse case" conditions.
Events, Statistics and Logging
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 Prepare Events	Enable this box to fire an event when a statement is created and prepared for execution.