SQL Call builder inputs

General Inputs

Input Name	Description
Name	Name for this builder call. The WebSphere Portlet Factory Designer displays this name in the builder call list. The name must begin with a letter or an underscore and contain only letters, digits, and underscores. This restriction allows the builder to use the Name input to build additional WebApp artifacts that implement the core of this builder runtime functionality.
Fetch DataSource Names	Click this button to fetch a list of JDBC DataSources defined on the application server supporting the WebSphere Portlet Factory Designer. The list is inserted as choices for the SQL DataSource input. If no DataSource names are listed in the SQL DataSource input, then WebSphere Portlet Factory Designer may not be properly configured to query an application server, the server might not be running, or there are no DataSources defined by the application server. Any valid datasource name can be typed into the SQL Datasource builder input. The Fetch Datasource Names button, however, only lists those datasources whose full names begin with jdbc/, jdbc/v4/, or jdbc/v5/. If you know that there is a valid datasource with a name that does not match any of these patterns, type the name in. If the name is correct, it should work. Depending on the performance of the application server, it might take several seconds to load the list of datasource names. This button is not displayed if the builder is configured to use an override SQL DataSource.
SQL DataSource	Open this list and select the JDBC DataSource to be used by the SQL Call when it executes the SQL statement you define later in this builder This input is displayed as a read-only prompt indicating the name of the override builder if the SQL Call builder is configured to use a custom SQL DataSource builder.

Database Explorer settings

Table 2. SQL Call builder Database Explorer settings inputs

Input Name	Description
Entity Type To Fetch	Select the type of entity to search for in the database: Tables Any table, view or alias Stored Procedures Any stored procedure or function
Catalog Pattern	Enter a name of the catalog to search for tables or stored procedures. Leave blank to match all catalogs. The following wildcards are supported: _ (underscore) Matches a single character % (percent) Matches zero or more characters Depending on the database and JDBC driver associated with the DataSource, this entry can be case sensitive.
Schema Pattern	Enter an SQL schema pattern to use while searching for tables or stored procedures. Typically this is the id of the database user that owns the entity you are trying to find. Leave this input blank to match all schemas. The following wildcards are supported: _ (underscore) Matches a single character % (percent) Matches zero or more characters Depending upon the database and JDBC driver associated with the DataSource this entry may be case sensitive.
Entity Pattern	Enter an entity name pattern (table or stored procedure name) to use while searching for tables or stored procedures. Leave blank to match all entities. The following wildcards are supported: _ (underscore) Matches a single character % (percent) Matches zero or more characters Depending upon the database and JDBC driver associated with the DataSource this entry may be case sensitive.
Fetch Entity Names	Click to fetch all of the entity names that match the catalog, schema, and entity patterns you specified. The names are inserted as choices for the Entity Name input. If no entity names are listed in the Entity Name input, WebSphere Portlet Factory Designer may not be properly configured to query an application server, the server may not be running, or there are no entity names that match the patterns you defined. Depending on the performance of the application server, it might take several seconds to load the list of entity names.
Entity Name	Select the name of the entity from the drop down list. The selected entity is used by the builder when you click Generate Sample SQL.
Statement Type	Select the type of table-based SQL statement to be generated when you click Generate Sample SQL. You can specify the following type of statements: Select To retrieve rows from the database table, view or alias. Insert To add a row to the database table, view or alias. Update To modify rows in the database table, view or alias. Delete To remove rows from the database table, view or alias. This input is displayed only if the Entity Type to Fetch input is set to Tables.
Generate Sample SQL	Click to have the builder generate a sample SQL statement in the Sample SQL input field and define positional parameters in the Sample Parameters input. If Entity Type to Fetch is set to Tables, the statement lists every column for the table. If Entity Type to Fetch is set to Stored Procedures, the statement takes the form of a JDBC stored procedure call where each input or output parameter is defined as a JDBC positional parameter.
Sample SQL	The sample SQL statement generated by the builder for the entity selected by the Entity Name input. The SQL displayed in this panel is editable. For example, you can edit a SELECT statement to return only the columns you need instead of all the columns defined for a table, view or alias. If you change the number of positional parameters make sure you also make appropriate changes to the Sample Parameters input before clicking Apply Sample SQL.
Sample Parameters	Sample definitions for the JDBC positional parameters defined in the Sample SQL input. Parameters are defined by: Position Numeric location of the ? that this parameter defines. In JDBC, position numbering starts at 1 which represents the first ? character in the Sample SQL input. Direction The direction of value assignment for the parameter. Set to Input if the parameter value is set only before the statement is executed. Set to Output if the parameter value is read only after the statement is executed. Set to Input/Output if the parameter value is both set before the statement is executed and read after its execution. Output and Input/Output are only valid for stored procedure calls. DB Type The type name for this parameter as known by the database. These types are database-specific. JDBC Type Cast All standard JDBC type names are supported. (Refer to JDBC documentation for specifics on each type available.) For more information, see Null inputs can cause problems. In addition, select Automatic to instruct the builder to use automatic JDBC type conversion when setting up parameters. Select Custom to use a custom method to set up a complex column type. This setting is handy when you work with driver-specific implementations of BLOB (binary large object) or CLOB (character large object) objects. Value or Set/Cast Method Specify one of the following: The value to be used at runtime for the positional parameter The name of a WebApp method to be called to set the parameter value at runtime The name of a WebApp method to be called to set the output type for a stored procedure parameter
Apply Sample SQL	Click to copy the sample SQL statement and parameters to the actual inputs of the builder for these values.
SQL Statement	Enter an SQL statement with optional positional parameter and indirect references. For example: SELECT * from EMPLOYEES where SALARY > ? Alternately, you can use this builder Database Explorer to locate and generate some sample SQL. You can edit that SQL as needed, and apply the edited SQL to this input. If you elect to override this input with an individual SQL Statement builder, this input is displayed as a read-only prompt that indicates the name of the builder being used to specify the statement.
Parameter Binding	Select one of the following parameter bindings to create variables associated with the statement specified in the SQL Statement input. Manual Manually binds values or WebApp variables to the positional parameters of the SQL statement. Automatic (Create Distinct Variables) Have the builder automatically create a distinct WebApp variable for each positional parameter in the statement. Automatic (Create XML variable) Have the builder create a single WebApp variable of type XML that contains values for all positional parameters in the statement. This choice is useful if you are profiling a statement in which the number of parameters can change, since only one variable is always generated regardless of the number of parameters involved. If you are using this builder in a Service Provider model that is to be consumed by a model that uses Page Automation builders that interact with the data, choose Automatic. Automatic generates a schema-typed variable and Page Automation builders require schema-typed variables. For more information, see Name generation for positional parameters.
Preserve Automatic Bindings	This input is available when the Parameter Binding input is set to one of the automatic options. When checked, this input causes the builder to preserve manual changes to the Parameters input. Normally the builder automatically keeps the Parameters input synchronized with the content of the SQL Statement and Name inputs. In this mode, the builder overwrites any manual changes you may have made. However, sometimes you may need to manually correct the automatic selection of parameter binding names. By checking this input, you indicate to the builder that you are taking responsibility for keeping the Parameter input synchronized with the other inputs. If you uncheck this input or you change the Parameter Binding input, the builder automatically synchronizes the Parameters. For more information, see Name generation for positional parameters.
Parameters	Parameters can include: Position Numeric location of the question mark character (?) that this parameter defines. In JDBC position numbering starts at 1 which represents the first ? character in the SQL Statement input. Direction The direction of value assignment for the parameter. Set to Input if the parameter value is set only before the statement is executed. Set to Output if the parameter value is read only after the statement is executed. Set to Input/Output if the parameter value is both set before the statement is executed and read after its execution. Output and Input/Output are only valid for stored procedure calls. JDBC Type Cast All standard JDBC type names are supported. (Refer to JDBC documentation for specifics on each type available.) For more information, see Null inputs can cause problems. In addition, select Automatic to instruct the builder to use automatic JDBC type conversion when setting up parameters. Select Custom to use a custom method to set up a complex column type. This setting is handy when you work with driver-specific implementations of BLOB (binary large object) or CLOB (character large object) objects. Value or Set/Cast Method One of the following: The value to be used at runtime for the positional parameter The name of a WebApp method to be called to set the parameter value at runtime The name of a WebApp method to be called to set the output type for a stored procedure parameter

Result set handling

Table 3. SQL Call builder Result set handling inputs

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. Some databases may not support updatable result sets.
Scroll Type	Select 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 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.
Transform Result	Select the type of transform to be applied to the result set or stored procedure output. Choices are: None To prevent any transformation from being made. Complete XML Document When this choice is selected, the entire contents of the result set or stored procedure are transformed into a single block of XML and returned by the builder invocation method. Use this selection when you know that the size of the returned result set or stored procedure is not extremely large. Paged XML Data Retriever (Not valid for stored procedure calls) Select this to set up a variable in the WebApp that is backed by a data retriever LJO class. This class includes a method named getData() that queries the database, retrieves a subset of a result set, converts that information to XML, and returns it to the caller. A database query is performed each time this method is called and this can have performance implications. Use this selection when you are willing to trade off some performance for improved manageability of database connections and reduced memory impact from large result sets.
Row Count Calculation	Indicate how the number of rows are calculated for the pageable result set. The choices are: Automatically calculate count The total number of rows is determined by the size of the data set. This option is good for data sets of a relatively limited size that do not adversely impact performance. Leave count unspecified The total number of rows is not determined. In this case, you will not be able to provide an access link to the last row. Use custom count Select this option to provide an alternative method to calculate the number of rows, which is specified on the Row Count input. The Row Count Calculation input only appears when the Transform Result input is Paged XML Data Retriever.
Row Count	Provide an indirect reference to calculate the number of rows for the data set. This input is available only when Use custom count is selected for Row Count Calculation.
Top Element Name	This input is available when the Transform Result input is activated. By default the SQL Call builder returns a result with tag names RowSet and Row. Use this input to provide an alternate name for the RowSet tag. If you have more than one SQL Call builder in a model, you can do this to differentiate between the transform results returned by each builder.
Row Element Name	This input is displayed when the Transform Result input is activated. By default the SQL Call builder returns a result with tag names RowSet and Row. Use this input to provide an alternate name for the Row tag. If you have more than one SQL Call builder in a model, you can do this to differentiate between the transform results returned by each builder.
Schema Generation	Specify XML schema conditions. You can specify Omit Generation Step To prevent a schema from being generated From SQL Statement To specify a schema be generated based on the content of the SQL Statement input of this builder or the configured statement override builder. From Alternate SQL Statement To specify a schema be generated based on the content of the Alternate SQL Statement input. Use this if the SQL Statement contains an indirect reference. The SQL you provide here will be used as the basis for the schema, so make sure that it accurately reflects the form and content of the result set returned by the SQL actually executed. Use existing URL/File/Variable To specify that an existing schema is to be used. If you are using stored procedures, select Omit generation step.
Schema Location	This input is available when Use Existing URL/File/Variable is selected for the Schema Generation input. Indicates the URL, file path, or variable name where the existing schema is located.
Schema Import Rule	This input is available when Use Existing URL/File/Variable is selected for the Schema Generation input. The import rule determines when a schema is imported. You can choose: Import Only At Design Time Select this value if the schema will not change between design time and generation time, if the Schema Location input is not profiled, or if the schema is defined by the XML in a variable. Import On Every Regen Select this value if the schema can change between design time and generation time or if the Schema Location input is profiled.
Schema Regen Time	This input is available when From SQL Statement is selected as the Schema Generation input. Set one of the following options to control the generation behavior of the schema. On Designer Regen To generate the schema every time the model is regenerated in the WebSphere Portlet Factory Designer. On Every Regen To generate the schema on every model regeneration observed. Only When SQL Builder Changes Are Detected To generate the schema only when inputs that effect the schema content change. When a model that contains a SQL Call builder is opened or saved, the server is called to rerun the SQL query. Set this option to prevent unnecessary calls to the server and minimize the time to open and save the model.
Alternate SQL	This input is available when From Alternate SQL Statement is selected for the Schema Generation input. Enter a SQL statement to be used for generating the XML schema that defines the result set produced by executing the SQL Statement input.

Method Names

These methods are optional and not all appear in the WebApp. The builder decides when they are needed based upon its inputs.

Table 4. SQL Call builder Method Names inputs

Input Name	Description
Invoke Name	Enter a custom name that is applied to this builder-generated method. This is the name of the WebApp method that must be called to execute the SQL statement defined by this builder. If the builder is configured to transform a result set or stored procedure output, then the method will return either an XML value or a paged data retriever. Otherwise the method will not return a value (such as when the SQL executed is DELETE). You might want to use a custom name so that methods generated by this builder adhere to a naming convention you have established.
Invoke With Params Name	Enter a custom name that is applied to this builder-generated method. This is the name of the WebApp method that allows a caller to override the positional parameter bindings, if any, defined for the builder. Calling this method causes the provided method parameters to be bound to the SQL statement's positional parameters before execution. If the builder is configured to transform a result set or stored procedure output, then the method will return either an XML value or a paged data retriever. Otherwise the method will not return a value (such as when the SQL executed is DELETE).
Get Text By Index Name	Enter a custom name that is applied to this builder-generated method. This is the name of the WebApp method that can be used to extract textual stored procedure output using a zero-based index. When a stored procedure's output is transformed into XML all of the output parameters appear as elements in the transformed XML. This method allows you to access the text of those output values by index just as you would access the elements of a Java array by index.
Get Text By Position Name	Enter a custom name that is applied to this builder-generated method.This is the name of the WebApp method that can be used to extract textual stored procedure output using parameter positions. When a stored procedure's output is transformed into XML all of the output parameters appear as elements in the transformed XML and their position in the SQL statement is remembered. This method allows you to access the values of those output parameters by specifying their position in the parameter list. Position numbers begin with one and may not be sequentially numbered.
Get XML By Index Name	Enter a custom name that is applied to this builder-generated method. This is the name of the WebApp method that performs the same function as Get Text by Index Name but instead returns the XML of the output parameter rather just its text.
Get XML By Position Name	Enter a custom name that is applied to this builder-generated method. This is the name of the WebApp method that performs the same function as Get Text by Position Name but instead returns the XML of the output parameter rather just its text.

Events, Statistics and Logging

Table 5. SQL Call builder Events, Statistics and Logging

Input Name	Description
Log SQL Statements	Enable this input 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 input to log any SQL warnings that are generated as a result of preparing the SQL statement for execution.
Log Server Stats	Enable this input to generate server statistics for this builder instance.
Fire Events	Enable this input to fire all events (database, transform, and so on) related to this builder's operation . This feature is primarily useful for debugging purposes.

Builder Override Definition

Input Name	Description
SQL DataSource	Use this list to select an existing SQL DataSource builder to be used to override this builder's internal SQL DataSource input.
SQL Statement	Use this list to select an existing SQL Statement builder to be used to override this builder's internal SQL Statement input.
SQL Transform	Use this list to select an existing SQL Transform builder to be used to override this builder's internal SQL Transform.

Null inputs can cause problems