SQL Call Builder

In this topic ...

Quick Tips
Specifying Inputs
How this Builder Works
Using Database Explorer
Using Override Builders
Schema Generation Limitations
Related Topics ...

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

Use this Builder to call a SQL statement and, optionally, transform its results into XML.

This high-level Builder encapsulates into a single entity the functionality of several low-level SQL Builders (SQL DataSource, SQL Statement, SQL Transform to XML, etc.). This enables you to add sophisticated database access capabilities to a model with a single Builder.

How do I...

Connect to a database?

Build an SQL statement?

Cache query results?

Initiate a transaction and display results?

Quick Tips

Database Explorer - Use the Database Explorer to search for tables, views, and stored procedures that can be used as targets for the "SQL Statement" input.

Enhance SQL readability - Enter your SQL statements using multiple lines to make the SQL statement easier to read.
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

Null Inputs Can Cause Problems

If the JDBC Type Case of a positional parameter is set to "Automatic", a null input value for that parameter will likely cause an application error at runtime. The JDBC interface requires the specification of a JDBC type if explicitly setting a parameter to NULL, and a binding of "Automatic" will not provide the required type information at runtime. Therefore, you should either prevent null input values (by setting form fields to "required" or providing enumerated selection controls) or ensure that any positional parameter that might resolve to a null value is explicitly typed.

Specifying Inputs

The SQL Call 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.
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 s runtime functionality.
Fetch DataSource Names Press this button to fetch a list of JDBC DataSources defined on the application server supporting the Designer. The list will be inserted as choices for the "SQL DataSource" input. If no DataSource names are listed in the "SQL DataSource" input, then the 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, will only list 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. See the section entitled "Using Override Builders" for more information.
SQL DataSource Open this list and select the JDBC DataSource to be used by the SQL Call when it executes the SQL statement you will define later in this Builder
This input will be displayed as a read-only prompt indicating the name of the override Builder if the SQL Call Builder has been configured to use a custom SQL DataSource Builder. See Using Override Builders for more information.
Database Explorer Settings
Entity Type to Fetch What 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 might 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 Press to fetch all of the entity names that match the catalog, schema, and entity patterns you specified. The names will be inserted as choices for the "Entity Name" input.
If no entity names are listed in the "Entity Name" input, the 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.
Tip - Review the "Explorer Status" panel for any warning or error messages.
Entity Name Select the name of the entity from the drop down list. The selected entity will be used by the Builder when you press the "Generate Sample SQL" button.
Statement Type Select the type of table-based SQL statement to be generated when you press the "Generate Sample SQL" button. We 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 will be displayed only if the "Entity Type to Fetch" input is set to "Tables".
Generate Sample SQL Press 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 will list every column for the table.
If "Entity Type to Fetch" is set to "Stored Procedures", the statement will take the form of a JDBC stored procedure call where each input/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, we can edit a SELECT statement to return only the columns we 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 pressing the "Apply Sample SQL" button.
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 s value is set only before the statement is executed. Set to "Output" if the parameter s value is read only after the statement is executed. Set to "Input/Output" if the parameter s value is both set before the statement is executed and read after its execution.
Note that "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.) 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/Cast Method - Specify one of the following: the value to be used at runtime for the positional parameter; the name of a WebApp method that will be called to set the parameter s value at runtime; the name of a WebApp method that will be called to set the output type for a stored procedure parameter

Apply Sample SQL Press to copy the sample SQL statement and parameters to the Builder s actual inputs for these values.

SQL Statement Enter an SQL statement with optional positional parameter and indirect references. For example:
SELECT * from EMPLOYEES where SALARY | ?
Alternately, we can use this Builder's Database Explorer to locate and generate some sample SQL. We can then 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 will be displayed as a read-only prompt indicating the name of the Builder being used to specify the statement. See Using Override Builders for more information.
If you are using a SELECT statement, see Schema Generation Limitations below for limitations on generating an XML schema for the returned result set.

Parameter Binding Choose one of the following parameter bindings to create variables associated with this statement:

Manual - Select this if you want to manually bind values / WebApp variables to the positional parameters of the SQL statement.
Automatic (Create Distinct Variables) - Select this to have the Builder automatically create a distinct WebApp Variable for each positional parameter in the statement.
Automatic (Create XML variable) - Select this to 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 will always be generated regardless of the number of parameters involved.)

Here are some important points to keep in mind when making choices in this builder call input.

If you are using this builder in a Service Provider model that will 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.
The parameter names generated when using Automatic are important because they appear in the Service Consumer models. Developers of consumer models will see these names in choosers and will need to associate values in their models with the various inputs to the service. As a result, it is a good idea to change the default generated names from values such as Parameter1Value, Parameter2Value, etc., to names that are more meaningful such as FirstName and LastName. Without these changes, it is difficult for the developer of the consumer model to determine which input is which especially in cases where the service creates new records and, therefore, has many inputs.
Once you have chosen Automatic, we can make changes to the names of the generated nodes. For example, consider a SQL statement that takes one argument. Choosing Automatic will generate a schema-typed variable reference with a value of ${Variables\<SQLcallBuilderName>Inputs\Parameter1Value}. A more useful name for this parameter might be RecordID, for example. A name like RecordID is more descriptive making it more meainingful to the developer of a consumer model. You can edit the generated parameter names manually. In this case you would change it to ${Variables\<SQLcallBuilderName>Inputs\RecordID}. Note: Once you have edited these values, do not choose Manual or Automatic (Create Distinct Variables) because your changes will get overwritten if you subsequently choose Automatic (Create XML Variable) again.

Parameters Parameters can include:

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 s value is only set before the statement is executed. Set to "Output" if the parameter s value is only read after the statement is executed. Set to "Input/Output" if the parameter s value is both set before the statement is executed and read after its execution.
Note that "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.) 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/Cast Method - One of the following: the value to be used at runtime for the positional parameter; the name of a WebApp method that will be called to set the parameter s value at runtime; the name of a WebApp method that will be called to set the output type for a stored procedure parameter.

Result Set Handling
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. Check the documentation for your target database for detailed information about the types of result sets supported.
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 will be transformed into a single block of XML and returned via the Builder s invocation method. Use this selection when you know the size of the returned result set or stored procedure will not be 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 method s 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.

If the resultset will contain BINARY or VARBINARY data types, click here for information about transforming these data types.
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 calcuate the number of rows, which is specified on the Row Count input.

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 displayed when the Transform Result input above 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.
You might want to do this if you have more than one SQL Call Builder in a model to differentiate between the transform results returned by each Builder.
Row Element Name This input is displayed when the Transform Result input above 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.
You might want to do this if you have more than one SQL Call Builder in a model to differentiate between the transform results returned by each Builder.
Schema Generation Specify XML schema conditions. We 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.

Schema Location Available when Use Existing URL/File/Variable is selected above. Indicates the URL, file path or variable name where the existing schema is located.
Schema Import Rule This input is displayed when "Use Existing URL/File/Variable is selected as the Schema Generation input.
The import rule determines when a schema is imported. We 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 choice. USe this input to control the generation behavior of the schema. We can choose:

On Designer regen - To generate the schema every time the model is regenerated in the 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's content change

Alternate SQL This input is displayed only when From Alternate SQL Statement above 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
Note: These methods are optional and not all will appear in the webApp. The Builder decides when they are needed based upon its inputs.
Invoke Name Enter a custom name that will be 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 executes 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 will be 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 executes is DELETE).
Enter a custom name that will be applied to this Builder-generated method.
Get text by Index Name Enter a custom name that will be 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 will be 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. Note that position numbers begin with one and may not be sequentially numbered.
Get XML by Index Name Enter a custom name that will be 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 will be 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
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 Events Enable this check box to fire all events (database, transform, etc.) related to this Builder's operation .
This feature is primarily useful for debugging purposes.
Builder Override Definition
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 the 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.

Input Name	Description
Name	Enter a name for this Builder call. The 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 s runtime functionality.
Fetch DataSource Names	Press this button to fetch a list of JDBC DataSources defined on the application server supporting the Designer. The list will be inserted as choices for the "SQL DataSource" input. If no DataSource names are listed in the "SQL DataSource" input, then the 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, will only list 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. See the section entitled "Using Override Builders" for more information.
SQL DataSource	Open this list and select the JDBC DataSource to be used by the SQL Call when it executes the SQL statement you will define later in this Builder This input will be displayed as a read-only prompt indicating the name of the override Builder if the SQL Call Builder has been configured to use a custom SQL DataSource Builder. See Using Override Builders for more information.
Database Explorer Settings
Entity Type to Fetch	What 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 might 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	Press to fetch all of the entity names that match the catalog, schema, and entity patterns you specified. The names will be inserted as choices for the "Entity Name" input. If no entity names are listed in the "Entity Name" input, the 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. Tip - Review the "Explorer Status" panel for any warning or error messages.
Entity Name	Select the name of the entity from the drop down list. The selected entity will be used by the Builder when you press the "Generate Sample SQL" button.
Statement Type	Select the type of table-based SQL statement to be generated when you press the "Generate Sample SQL" button. We 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 will be displayed only if the "Entity Type to Fetch" input is set to "Tables".
Generate Sample SQL	Press 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 will list every column for the table. If "Entity Type to Fetch" is set to "Stored Procedures", the statement will take the form of a JDBC stored procedure call where each input/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, we can edit a SELECT statement to return only the columns we 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 pressing the "Apply Sample SQL" button.
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 s value is set only before the statement is executed. Set to "Output" if the parameter s value is read only after the statement is executed. Set to "Input/Output" if the parameter s value is both set before the statement is executed and read after its execution. Note that "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.) 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/Cast Method - Specify one of the following: the value to be used at runtime for the positional parameter; the name of a WebApp method that will be called to set the parameter s value at runtime; the name of a WebApp method that will be called to set the output type for a stored procedure parameter
Apply Sample SQL	Press to copy the sample SQL statement and parameters to the Builder s actual inputs for these values.

SQL Statement	Enter an SQL statement with optional positional parameter and indirect references. For example: SELECT * from EMPLOYEES where SALARY \| ? Alternately, we can use this Builder's Database Explorer to locate and generate some sample SQL. We can then 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 will be displayed as a read-only prompt indicating the name of the Builder being used to specify the statement. See Using Override Builders for more information. If you are using a SELECT statement, see Schema Generation Limitations below for limitations on generating an XML schema for the returned result set.

Parameter Binding	Choose one of the following parameter bindings to create variables associated with this statement: Manual - Select this if you want to manually bind values / WebApp variables to the positional parameters of the SQL statement. Automatic (Create Distinct Variables) - Select this to have the Builder automatically create a distinct WebApp Variable for each positional parameter in the statement. Automatic (Create XML variable) - Select this to 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 will always be generated regardless of the number of parameters involved.) Here are some important points to keep in mind when making choices in this builder call input. If you are using this builder in a Service Provider model that will 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. The parameter names generated when using Automatic are important because they appear in the Service Consumer models. Developers of consumer models will see these names in choosers and will need to associate values in their models with the various inputs to the service. As a result, it is a good idea to change the default generated names from values such as Parameter1Value, Parameter2Value, etc., to names that are more meaningful such as FirstName and LastName. Without these changes, it is difficult for the developer of the consumer model to determine which input is which especially in cases where the service creates new records and, therefore, has many inputs. Once you have chosen Automatic, we can make changes to the names of the generated nodes. For example, consider a SQL statement that takes one argument. Choosing Automatic will generate a schema-typed variable reference with a value of ${Variables\<SQLcallBuilderName>Inputs\Parameter1Value}. A more useful name for this parameter might be RecordID, for example. A name like RecordID is more descriptive making it more meainingful to the developer of a consumer model. You can edit the generated parameter names manually. In this case you would change it to ${Variables\<SQLcallBuilderName>Inputs\RecordID}. Note: Once you have edited these values, do not choose Manual or Automatic (Create Distinct Variables) because your changes will get overwritten if you subsequently choose Automatic (Create XML Variable) again.
Parameters	Parameters can include: 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 s value is only set before the statement is executed. Set to "Output" if the parameter s value is only read after the statement is executed. Set to "Input/Output" if the parameter s value is both set before the statement is executed and read after its execution. Note that "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.) 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/Cast Method - One of the following: the value to be used at runtime for the positional parameter; the name of a WebApp method that will be called to set the parameter s value at runtime; the name of a WebApp method that will be called to set the output type for a stored procedure parameter.
Result Set Handling
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. Check the documentation for your target database for detailed information about the types of result sets supported.
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 will be transformed into a single block of XML and returned via the Builder s invocation method. Use this selection when you know the size of the returned result set or stored procedure will not be 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 method s 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. If the resultset will contain BINARY or VARBINARY data types, click here for information about transforming these data types.
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 calcuate the number of rows, which is specified on the Row Count input.
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 displayed when the Transform Result input above 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. You might want to do this if you have more than one SQL Call Builder in a model to differentiate between the transform results returned by each Builder.
Row Element Name	This input is displayed when the Transform Result input above 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. You might want to do this if you have more than one SQL Call Builder in a model to differentiate between the transform results returned by each Builder.
Schema Generation	Specify XML schema conditions. We 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.
Schema Location	Available when Use Existing URL/File/Variable is selected above. Indicates the URL, file path or variable name where the existing schema is located.
Schema Import Rule	This input is displayed when "Use Existing URL/File/Variable is selected as the Schema Generation input. The import rule determines when a schema is imported. We 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 choice. USe this input to control the generation behavior of the schema. We can choose: On Designer regen - To generate the schema every time the model is regenerated in the 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's content change
Alternate SQL	This input is displayed only when From Alternate SQL Statement above 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
Note: These methods are optional and not all will appear in the webApp. The Builder decides when they are needed based upon its inputs.
Invoke Name	Enter a custom name that will be 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 executes 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 will be 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 executes is DELETE). Enter a custom name that will be applied to this Builder-generated method.
Get text by Index Name	Enter a custom name that will be 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 will be 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. Note that position numbers begin with one and may not be sequentially numbered.
Get XML by Index Name	Enter a custom name that will be 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 will be 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
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 Events	Enable this check box to fire all events (database, transform, etc.) related to this Builder's operation . This feature is primarily useful for debugging purposes.
Builder Override Definition
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 the 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.

How this Builder Works

This Builder allows your model to obtain database connections from a local or remote JNDI-based DataSource. The DataSource is typically defined and managed by your application server and made available through a JNDI server provided as part of the application server s implementation. The DataSource created by the application server will usually provide a set of additional features beyond simply handing out JDBC connections for access to a database. Some of those features might include connection pooling, stale connection handling, and connection time outs.

Authentication and Credentials

This Builder is designed to use the authentication credentials in force on the JNDI connection. Thus requests from this Builder to the datasource are passed directly through the JNDI connection. This Builder does not provide inputs for user-specific credentials. However, If user-specific credentials are required, we can use a SQL DataSource Builder in the model to override the connection functionality provided by this Builder.

Using the Database Explorer

Use the Database Explorer if we need assistance in creating an SQL statement. We can browse entities in the database and construct the SQL statement on-the-fly. This is particularly useful if you are not an expert in using SQL. As you specify various inputs to the Explorer, you build a statement that can then be edited.

Using Override Builders

The SQL Call Builder leverages many of the low-level SQL Builders during its generation phase. These "internal" SQL Builders are configured by SQL Call to use input values that are appropriate for the majority of users. However, there may be scenarios where a user needs to apply a fine grain of control over how these "internal" Builders function at runtime. To make this fine grain of control possible the SQL Call Builder allows the user to override the "internal" SQL DataSource, SQL Statement, and/or SQL Transform Builders used by SQL Call during generation.

Under the group heading "Builder Override Definition" there are three inputs that list all of the SQL DataSource, SQL Statement, and SQL Transform Builders present in the model. Choosing a Builder name for one of these inputs causes the SQL Call Builder to use the selected Builder at generation and runtime rather than it s "internal" Builder. For example, if we needed the SQL DataSource to behave differently than the "internal" DataSource, then you would add a SQL DataSource Builder call to your model, configure it appropriately, when select it as the override for the "SQL DataSource" in the SQL Call Builder call. SQL Call will thereafter use the SQL DataSource Builder call that you added to the model.

Schema Generation Limitations with SELECT Statements

When using the SQL Call Builder to execute a SELECT statement, there are two scenarios in which the SELECT statement as entered in the "SQL Statement" input cannot be used to generate an XML schema for the returned result set. The first scenario involves "SQL Statement" inputs that contain one or more indirect references. Because the Builder generates an XML schema for the result set at model regeneration time, the Builder needs to know the final form of the SELECT statement at regeneration. However, an indirect reference in the "SQL Statement" input implies that some part of the SELECT statement won't be determined until runtime. As a result, the SQL Call Builder does not allow a "SQL Statement" input with indirect references to be used for schema generation. We can work around this limitation by selecting "From Alternate SQL Statement" as the value of the "Schema Generation" input. Then enter into the "Alternate SQL" input a copy of your SELECT statement that does not contain any indirect references. When configured in this way, the SQL Call Builder will use the content of the "Alternate SQL" input to generated the XML schema at regen time and use the content of "SQL Statement" at model execution (runtime).

The second scenario involves "SQL Statement" inputs that use positional parameters that correspond to the JDBC column types of ARRAY, BLOB, CLOB, DATE, DISTINCT, JAVA_OBJECT, OTHER, REF, and STRUCT. At regeneration time the SQL Call Builder generates an XML schema for the SELECT's result set by generating and examining a small result set that has the same columns as the result set that will be generated at execution. The Builder attempts to create sample values for each positional parameter before generating this sample result set. However, it's not possible for the Builder to create sample values for the types listed. The solution to this problem is to select "From Alternate SQL Statement" as the value of the "Schema Generation" input. Then enter into the "Alternate SQL" input a copy of your SELECT statement that does not contain any positional parameters.

Working with BINARY and VARBINARY Results

Some database columns are stored as binary fields even though the data they represent is simple strings. BINARY and VARBINARY are SQL types that SQL Transform will automatically attempt to convert into strings. This approach may or may not work, depending upon the content of the columns and the actual data. If you expect BINARY or VARBINARY results to be returned, keep in mind that these results will be converted into strings. If that's not appropriate for the data involved, you will need to add a custom transform for those columns.