Transaction report with multiple statements failed with error "Execute Parameterized Query failed"

Quality Engineer, Principal • Strategy

For some Gateways, when Parameterized Queries is turned on for transaction reports, if the report consists of multiple statements, the report may fail.

Description

Starting from Strategy version 2021 Update 4, all SQL executions triggered from transaction services reports and transaction-enabled documents will be secured through parameterized queries. However, due to different levels of support coming from Gateway drivers, some transaction reports, if consist of multiple statements, may fail. This article will illustrate 2 examples and introduce solutions and workarounds for users to continue to use transaction services with secured text inputs.

Case #1

A report designer created a transaction report executing a stored procedure with 2 parameters. Two SET statements are used to assign inputs to parameters.
When executed with Parameterized Queries, an error pops out and claims there is a wrong syntax in the queries.

Case #2

A report designer created a transaction report inserting 3 different inputs into 2 different tables with 2 individual SQL statements.

When executed with Parameterized Queries, an error pops out and claims there is a wrong syntax in the queries.

In both cases, the cause is that the Gateway driver is not able to map each parameter to its corresponding placeholders when they are in different statements.

Gateway Driver Support

Some gateways support multiple statements in one parameterized transaction on both JDBC and ODBC drivers, some only support ODBC drivers, some don't support either.
Here is a chart of some known Gateways and their ability to support on each driver implementation.

Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌

Workaround

If possible, the report designer can modify the transaction report to combine multiple statements into one. For example, in Case 1, it can be modified to the following to avoid multiple statements:

If #1 is not possible, the report designer can work with DBA to create a stored procedure that contains all the statements and call the stored procedure in the transaction report
If both approaches are not feasible, the admin can disable Parameterized Queries for the specific report by the following steps:

Go to the VLDB Properties at the transaction report,
Enable "Show Advanced Settings" and find "Parameterized Query Option"
Choose the first option.

Known limitation

KB485180: Transaction Report contains multi-statements failed for MySQL after Parameterized Queries are used
Error "Statement 'INSERT INTO PQTX_...' cannot be executed using current API" is returned when using multi-statement Transaction Services documents against Snowflake on JDBC driver

Comment

0 comments

Details

Knowledge Article

Published:

September 23, 2021

Last Updated:

October 8, 2021

Description

Gateway Driver Support

Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌
Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌	Dimension (may also be called Hierarchy)HierarchyHierarchy Level (mapped to a cube column)AttributeMember propertyAttribute formMember of Measures dimensionMetricCalculated member inMetric ODBCJDBCSQL Server✅✅Oracle ✅✅Redshift ✅❌PostgreSQL✅❌DB2❌❌Teradata✅❌SAP HANA ❌❌Snowflake ✅❌