Defines an SQL transaction.

An SQL transaction allows Convertigo to execute a request on an SQL database, which is accessed by the parent SQL connector.
An SQL transaction is always associated with an SQL query, or several SQL queries, defined in the Query property. The query/queries can be dynamically configured using the transaction’s variables (see Query property description and Variable objects documentation).
The resultset is output in transaction’s XML response in an sql_output element. The data organization in the sql_output element depends on the Output mode property.
In case of multiple queries, the several queries are sequentially executed, using auto-commit mode defined in Auto-commit property. Each query’s resultset is output in transaction’s XML response in sql_output elements. The data organization in sql_output elements depends on the Output mode property and is the same for all queries of the transaction.

Property Type Category Description
Accessibility Accessibility standard Defines the transaction/sequence accessibility.
This property can take the following values:

• Public: The transaction/sequence is runnable from everyone and everywhere, visible in the Test Platform and is also exposed in the SOAP WSDL as a web service method.
• Hidden: The transaction/sequence is runnable but only from people who know the execution URL, not visible in the Test Platform nor exposed in the SOAP WSDL.
• Private: The transaction/sequence is only runnable from within the Convertigo engine (Call Transaction/(Call Sequence steps), is not visible in the Test Platform and cannot be requested as SOAP web service method. This value is used for tests, unfinished transactions/sequences or functionalities not to be exposed. Private transactions/sequences remain runnable in the Studio, for the developer to be able to test its developments.

Note: In the Test Platform:

• The administrator user (authenticated in Administration Console or Test Platform) can see and run all transactions / sequences, no matter what their accessibility is.
• The test user (authenticated in the Test Platform or in case of anonymous access) can see and run public transactions/sequences and run hidden ones if he knows their execution URL.
Comment String standard Describes the object comment to include in the documentation report.
This property generally contains an explanation about the object.
Default XML column tag name String standard Defines the default column tag name to output in XML response, depending on the Output mode property.
This property allows to configure a tag name for each column of result, depending on the Output mode property.
Default value is column.
Default XML row tag name String standard Defines the default row tag name to output in XML response, depending on the Output mode property.
This property allows to configure a tag name for each line of result, depending on the Output mode property.
Default value is row.
Query String standard Defines the SQL query/queries to execute on the target database.
The Query property allows to define an SQL query or several SQL queries to be executed on the target database. Any SQL query must be written in accordance with the target database tables and available functions and keywords (depending on the parent SQL connector configuration).
The SQL query/queries can be parameterized with the transaction’s variables, to be dynamically configured at runtime with variable values. To use a transaction variable in an SQL query, use one of the following syntaxes in the query:

• {variable_name}: the simple-brace notation - variable name surrounded by curly braces - protects the SQL query from SQL injections, i.e. only the first value represented by the variable is used, discarding any further content (SQL injection). It works so that the variable only contains a value. This notation can only be used inside the WHERE clause. If you need to use a variable anywhere else inside the SQL query, you should use the double-brace notation.
• : the double-brace notation - variable name surrounded by double curly braces - does not protect the SQL query from SQL injections, i.e. the variable content can contain any content. If the variable value contains a piece of SQL query, it will not prevent the SQL query execution. This may be useful when a whole WHERE clause is computed outside the transaction (at sequence level for example) and passed as a variable to the SQL transaction. It should also be used when variables need to be used in the query outside of the WHERE clause.

In the case of multiple SQL queries, they must be separated by semicolons ‘;’.
Notes:

• In the case of multiple SQL queries, you cannot use the Optional max number of results property in this case.
Response timeout long standard Defines the response maximum waiting time (in seconds).
Maximum time (in seconds) for a transaction/sequence to run. When specified time is reached, the transaction/sequence ends and returns a timeout error. If requested through the SOAP interface, the error is returned as a SOAP exception.
Add statistics to response boolean expert Defines whether some statistics of execution of the transaction/sequence should be added as data in the transaction/sequence’s response.
If this property is set to true, the transaction/sequence response will be enhanced with the statistics data of its execution (total time for the request, time spent waiting for the mainframe, etc.).
Note: This property has nothing to do with the general property of the Convertigo engine Insert statistics in the generated document that can be edited in the Configuration page of the Administration Console.
Authenticated context required boolean expert Defines whether an authenticated context is required to execute the transaction/sequence.
If this property is set to true, the context of execution of the transaction/sequence must have been authenticated. Otherwise, the transaction/sequence is not executed. Default value is false for a standard access to transactions/sequences.
Notes:

• When a context is authenticated, all the contexts in the same HTTP session are also authenticated. For more information about context and HTTP session, see Context general presentation paragraph in JavaScript Objects APIs chapter.
• When executing a transaction/sequence from stub (__stub variable passed to true in entry), this property is ignored. Indeed, executing from stub is for testing purposes and should not require any authentication: the context would never be authenticated as the transaction/sequence setting the context as authenticated could also be executed from stub.
Authenticated user as cache key boolean expert Defines whether the authenticated user should be used as cache key.
When the cache is enabled (Response lifetime setting filled with a time-to-live), the Authenticated user as cache key property allows to specify to use the authenticated user ID from context/session as an additional key to the cache.
It would have as effect that two different identified users cannot retrieve the cached response of the other for the same request. Default value is false: the authenticated user is not used as cache key.
Auto-commit int expert Defines the commit mode, to be automatic or not.
The Auto-commit property can take one of the following values:

• enabled, after each query: auto-commit is done after executing each query from the Query property,
• enabled, once at the end: auto-commit is done after executing all queries from the Query property,
• disabled, manual commit: the developer should program himself the commits to the database thanks to COMMIT statement.

Default value is enabled, after each query, enabling auto-commit after each query execution.
Notes:

• Not all databases support “grouped transactions” and “rollback”. To use this property, be sure that your target database supports these features. Otherwise, the auto-commit is always applied.
Call the biller boolean expert Defines whether the billing management module should be called for each generated XML document.
If this property is set to true, the applicable billing management module, defined thanks to the connector’s billing class name property, is invoqued. This parameter should never be changed (Convertigo private use only).
Character set String expert Defines the character set used for operations on the generated XML document (default: UTF-8).
Generate JSON type boolean expert Add “type” attributes to respect JSON types for the .json requester.
This property will add “type” and “originalKeyName” attributes on XML response. The FLAT_ELEMENT is the recommended mode, other modes will just add the array support for rows.
Include certificate group boolean expert Includes the certificate group into the cache key.
If set to true, the certificate group is added to the cache key which is used to determine whether the transaction’s response should be pulled from the cache or not.
A transaction’s cached response is pulled from the cache when all cache key values are corresponding to a stored cache entry.
Optional max number of results String expert Defines the maximum number of results returned by the SQL query.
Setting this property automatically adds a LIMIT xx at the end of the query before its execution on the database. It allows limiting automatically the number of results of every execution of one query.
Notes:

• Beware that this property should be used only when the target SQL database allows the LIMIT keyword in the queries. To know which type of database is accessed by the transaction, refer to the Driver property of parent SQL connector.
• Beware that this property cannot be used when the Query property contains several queries.
Output mode int expert Defines how the resulting XML is generated from each SQL query result.
The Output mode property allows to change the structure of generated XML for a same query result. It can be set to one of the following values:

• RAW: generates a row element for each result line, selected columns are added as attributes named after the column names. Note that as the columns are XML attributes, they are sorted by alphabetical order under the row element.
• AUTO: generates a mix of complex elements and elements with attributes (interesting for multi-table requests).
• ELEMENT: generates XML elements named after table names, selected columns are added as child XML elements named after column name. In case of multi-table requests, one row is a complex element which depth depends on the number of selected tables. Note that as the columns are XML elements, they are sorted in the order requested in the query and as the resultset has returned data.
• ELEMENT_WITH_ATTRIBUTES: generates a structure similar to the ELEMENT output mode but with row and column tag names. Each element (row or column) contains a name attribute with the actual name of the selected table or column (useful when table or column names contain symbols not allowed in XML tag names, or start by a number, etc.).
• FLAT_ELEMENT: generates a structure similar to the RAW output mode but with elements instead of attributes. Each result line generates a row element, selected columns are added as child XML elements named after the column names. Note that as the columns are XML elements, they are sorted in the order requested in the query and as the resultset has returned data.
Response client cache boolean expert Defines whether the transaction/sequence response should be cached by the client.
If set to false, the response XML is sent to the client along with HTTP headers forcing the client browser not to store it in its local cache. This is the default value, since dynamic responses are usually preferred. If set to true, the XML response is sent normally.
Response lifetime String expert Defines the response time-to-live (in seconds) in cache, i.e. the time during which the cached response remains valid or time interval for its renewal. This property enables the cache when filled, disables the cache when left empty.
The Response lifetime property allows to specify the cache settings for the transaction/sequence’s response. It can be set to the following values:

• <empty>: Disables the cache for the transaction/sequence. The response will not be cached and each request will execute the complete transaction. It is the default value.
• absolute,<time in secs>: Enables the cache for the transaction/sequence. The response will be cached for the time specified in seconds. If an other request with the same parameters occurs within this time, the response will be returned from the cache.
• daily,hh:mm:ss: Enables the cache for the transaction/sequence. The response will be cached until hh:mm:ss of the current day is reached. If an other request with the same parameters occurs before this time, the response will be returned from the cache. A new day starts at 00:00:00.
• weekly,hh:mm:ss,w: Enables the cache for the transaction/sequence. The response will be cached until hh:mm:ss of the wth day of week is reached. For Sunday w = 1, for Monday w = 2 … and for Saturday w = 7. If an other request with the same parameters occurs before this time, the response will be returned from the cache. A new day starts at 00:00:00.
• monthly,hh:mm:ss,d: Enables the cache for the transaction/sequence. The response will be cached until hh:mm:ss of the dth day of month is reached. If an other request with the same parameters occurs before this time, the response will be returned from the cache. A new day starts at 00:00:00.

Notes:

• The Response lifetime property editor proposes a Generator tool that can help you configure the Response lifetime setting.
• The Variable objects contain the Cache key property that allows to specify to use this variable as a key to the cache or not. See Variable objects documentation for more information.
Secure connection required boolean expert Defines whether the transaction/sequence should be called through a secured connection (e.g. HTTPS).
Depending on the requester, if this property is set to true, the transaction/sequence must be accessed through a secure connection (e.g. HTTPS in case of HTTP access). Default value is false for a standard access to transactions/sequences.
Style sheet int expert Defines how the XML returned by the transaction has to be processed by XSLT.
This property can take the following values:

• None: Do not process with XSLT. Usual setting for web services (SOAP or REST) where plain XML data is to be returned.
• From transaction: Use the XSL style sheet attached to the transaction. When used, make sure a style sheet object is added to the transaction.
• From last detected screen class: Use XSL style sheet attached to the last detected screen class (in case of a transaction with screen classes).

Transactions using sheets from last detected screen class are mainly used in Web Clipping or Legacy Publishing projects.
XML grouping boolean expert Defines whether the resulting XML should be grouped by elements.
Default value is false. Setting it to true enables the grouping of XML elements in the transaction’s XML response. The behavior can be different depending on the Output mode property value.