SQL Object Properties

Evaluate SQL

true or false.

Determines whether the SQL statement will be subject to iWay Functional Language evaluation.

When set to true, iSM attempts to evaluate the statement, in which case, care must be taken to escape characters that are significant to iWay (for example, single quotes).

The default value is false. This prevents ambiguity and protects against side effects.

Output Format

row, column, or field.

Specifies how to construct the output XML document for the answer set (required, but meaningful for SELECT only).

Use JNDI

True, False, Edit, Create variable, Create XPath statement, Load IFL expression

If value is set to true, a connection is established from a data source, accessed via JNDI

JNDI Name

Edit, Create variable, Create XPath statement, Load IFL expression

JNDI Name for the requested Data Source. Required field if using JNDI.

JNDI Factory

Edit, Create variable, Create XPath statement, Load IFL expression

JNDI initial context factory class. Default is blank.

Transaction Isolation Level

asis, readUncommitted, readCommitted, repeatableRead, or serializable.

Sets the isolation level for this connection. For information on locking, see your database documentation.

Pool Connections

true or false.

When set to true, all connections are pooled by the URL. This enables the first connection to the URL to be shared by subsequent uses of the SQL Agent that use the same URL.

The true value is appropriate for SELECT, but may not be appropriate for SQL statements that require commit.

The default value is false.

Attempt Read Only

true or false.

If set to true, for SELECT statements sets JDBC 'read only' flag. Some drivers cannot handle this optimization and may report security failures.

The default value is false.

Connection Properties

String literal or iWay Functional Language expression.

These are additional vender‑specific properties to be added to the JDBC URL.

Need Commit

true or false.

Set if the SQL executed modifies the data table(s). In this case, pooling performed in response to the Pool Connections setting is on the worker level. This makes the connection serial, enabling the system to maintain proper commit scopes. This property affects only pools. SQL statements other than SELECT are always committed.

Note: If configured, this service participates in iWay server transaction management.

The default value is false.

Timeout

Describes the number of seconds the service will wait for an operation to complete.

0 - no time out check

If a value is set, it alerts a driver to attempt to stop the connection after the defined period.

Max Rows

0 (zero) or any positive integer.

Maximum number of rows to be returned for a select DML statement.

To return all rows, use zero. If the statement returns more rows than the maximum, the remainder will not be part of the response document. These rows can, however, be appended as sibling documents as explained in the Make Siblings property.

Make Siblings

true or false.

Indicates whether or not to make sibling documents.

A sibling is a complete XML document that is related to another XML document. In the case of the SQL Agent, each sibling carries the max rows number of response rows. Specialized agents and components are ‘sibling-aware’ and can work with sibling documents (for example, IterSibSplit and emitters). Although many agents carry the siblings forward and most protocols can properly deal with siblings, it is not guaranteed that all will do so.

When siblings are used, we recommend that the SQL agent be placed immediately before an object that can process sibling documents.

The default value is false.

Max Siblings

A positive integer.

If Make Siblings is set to true, then this is the Maximum number of siblings created in one invocation of the SQL Object.

Emit No Empty

true or false.

If set to true, empty documents are not emitted.

The default value is false.

Issuance Strategy

construct or bind.

Choose whether to issue data through SQL construction performed by iSM, or through SQL bind (prepare/execute).

Reply Node Name

String literal or iWay Functional Language expression.

If entered, this represents the XML node (that is, tag) that will contain the response from the issued SQL statement.

Base 64 if Needed

true or false.

When set to true, all fields are checked to see if they need base64 encoding.

Note: The true setting has a negative impact on performance.

xLOB Handling

none, external, inline, String literal or iWay Functional Language expression

Default is none

Defines how blobs and clobs are to be treated

Want Generated Keys

True, false, String literal or iWay Functional Language expression

If set to true, generated keys on a non-bound INSERT statement are returned, if available, from the data base in the iway.genkey’ register.

Output Document

result, input, String literal or iWay Functional Language expression

Determines if an operation does not return a result set, if the agent should return the standard result document or if it should return only its input.

If input is selected, the response node from the result document that would otherwise have been returned will be stored in a special register named “iway.sqlstatus”.

If the operation is not successful, an error or result document will always be returned.

Call at EOS

True, false, String literal or iWay Functional Language expression

Determines if when using a streaming preparser in a channel, a last call is made after the last document.

User ID

String literal or iWay Functional Language expression (SREG, and so on).

The user ID to connect to the RDBMS.

Password

String literal or iWay Functional Language expression (SREG, and so on).

The password to connect to the RDBMS.