When using Strategy 2021 with parameterized query enabled, you may encounter the following five scenarios. The five defects do not share the same root cause, however, the JDBC driver can be used as an alternative to workaround the issues.
The following SQL clauses will fail or return unacceptable result sets with Data Direct SQL Server ODBC driver.
Error: You may encounter a drop temp table error when the temp table is created by parameterized query.
select a13.NSTATE_ID NSTATE_ID,
sum(a11.NSALES_AMT) WJXBFS1
into ##ZZTW1AHRPWMMD002
from NSALES a11
join LU_NCUSTOMER a12
on (a11.NCUSTOMER_ID = a12.NCUSTOMER_ID)
join LU_NCITY a13
on (a12.NCITY_ID = a13.NCITY_ID)
where ((a13.NSTATE_ID)
in (select ps21.NSTATE_ID
from ##ZZT2F7JRH5YMD000 ps21
join LU_NSTATE s22
on (ps21.NSTATE_ID = s22.NSTATE_ID)
where s22.NSTATE_NAME not like ?))
group by a13.NSTATE_ID
with parameters:
V%
Drop table ##ZZTW1AHRPWMMD002
select [a12].[REGION_ID] [REGION_ID],
max([a13].[REGION_NAME_SCH]) [REGION_NAME_SCH],
[a11].[CALL_CTR_ID] [CALL_CTR_ID],
max([a12].[CENTER_NAME]) [CENTER_NAME],
sum(([a11].[TOT_DOLLAR_SALES] - [a11].[TOT_COST])) [WJXBFS1],
CASE WHEN sum(([a11].[TOT_DOLLAR_SALES] - [a11].[TOT_COST])) < ? THEN '低' ELSE ? END [WJXBFS2]
from [city_ctr_sls] [a11]
join [lu_call_ctr] [a12]
on ([a11].[CALL_CTR_ID] = [a12].[CALL_CTR_ID])
join [lu_region] [a13]
on ([a12].[REGION_ID] = [a13].[REGION_ID])
group by [a12].[REGION_ID],
[a11].[CALL_CTR_ID]
with parameters:
200000
高东南,Atlanta,157962.503499997,?, ,Miami,178712.827399996,?, 南,New Orleans,504990.485600001,null, ,Memphis,301965.526399996,null,
Error: Data is not returned when incorrectly binding a SQL_C_CHAR column to SQL_C_WCHAR.
select [a14].[CATEGORY_ID] [CATEGORY_ID],
max([a15].[CATEGORY_DESC]) [CATEGORY_DESC],
[pa13].[SUBCAT_ID] [SUBCAT_ID],
max([a14].[SUBCAT_DESC]) [SUBCAT_DESC],
sum([a11].[TOT_COST]) [WJXBFS1]
from [item_mnth_sls] [a11]
join [lu_item] [a12]
on ([a11].[ITEM_ID] = [a12].[ITEM_ID])
join (select [a11].[SUBCAT_ID] [SUBCAT_ID],
max([a11].[ITEM_NAME]) [WJXBFS1]
from [lu_item] [a11]
join (select [a11].[ITEM_ID] [ITEM_ID]
from [lu_item] [a11]
where ([a11].[ITEM_NAME] > ?)
) [pa12]
on ([a11].[ITEM_ID] = [pa12].[ITEM_ID])
group by [a11].[SUBCAT_ID]
) [pa13]
on ([a12].[SUBCAT_ID] = [pa13].[SUBCAT_ID])
join [lu_subcateg] [a14]
on ([pa13].[SUBCAT_ID] = [a14].[SUBCAT_ID])
join [lu_category] [a15]
on ([a14].[CATEGORY_ID] = [a15].[CATEGORY_ID])
where [a12].[ITEM_NAME] = ?
group by [a14].[CATEGORY_ID],
[pa13].[SUBCAT_ID]
with parameters:
The
Adirondack StyleError: This is a specific issue with Azure Synapse Analytics and the follow error message displays as:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0xE7 has an invalid data length or metadata length.
select distinct [pa11].[COUNTRY_ID] [COUNTRY_ID],
[a14].[COUNTRY_NAME] [CUST_COUNTRY_DESC],
[pa11].[CUSTOMER_ID] [CUSTOMER_ID],
[a15].[CUST_LAST_NAME] [CUST_LAST_NAME],
[a15].[CUST_FIRST_NAME] [CUST_FIRST_NAME],
[pa11].[ITEM_ID] [ITEM_ID],
[a16].[ITEM_NAME] [ITEM_NAME],
[pa11].[WJXBFS1] [WJXBFS1],
[pa12].[WJXBFS1] [WJXBFS2],
[pa13].[WJXBFS1] [WJXBFS3]
from (select [a11].[ITEM_ID] [ITEM_ID],
[a11].[CUSTOMER_ID] [CUSTOMER_ID],
[a12].[COUNTRY_ID] [COUNTRY_ID],
sum(([a11].[QTY_SOLD] * (([a11].[UNIT_PRICE] - [a11].[DISCOUNT]) - [a11].[UNIT_COST]))) [WJXBFS1]
from [order_detail] [a11]
join [lu_employee] [a12]
on ([a11].[EMP_ID] = [a12].[EMP_ID])
join [lu_customer] [a13]
on ([a11].[CUSTOMER_ID] = [a13].[CUSTOMER_ID])
where [a13].[ADDRESS] between ? and ?
group by [a11].[ITEM_ID],
[a11].[CUSTOMER_ID],
[a12].[COUNTRY_ID]
) [pa11]
join (select [a11].[ITEM_ID] [ITEM_ID],
[a11].[CUSTOMER_ID] [CUSTOMER_ID],
[a12].[COUNTRY_ID] [COUNTRY_ID],
sum(([a11].[QTY_SOLD] * (([a11].[UNIT_PRICE] - [a11].[DISCOUNT]) - [a11].[UNIT_COST]))) [WJXBFS1]
from [order_detail] [a11]
join [lu_employee] [a12]
on ([a11].[EMP_ID] = [a12].[EMP_ID])
join [lu_country] [a13]
on ([a12].[COUNTRY_ID] = [a13].[COUNTRY_ID])
where [a13].[COUNTRY_NAME] = ?
group by [a11].[ITEM_ID],
[a11].[CUSTOMER_ID],
[a12].[COUNTRY_ID]
) [pa12]
on ([pa11].[COUNTRY_ID] = [pa12].[COUNTRY_ID] and
[pa11].[CUSTOMER_ID] = [pa12].[CUSTOMER_ID] and
[pa11].[ITEM_ID] = [pa12].[ITEM_ID])
join (select [a11].[ITEM_ID] [ITEM_ID],
[a11].[CUSTOMER_ID] [CUSTOMER_ID],
[a12].[COUNTRY_ID] [COUNTRY_ID],
sum([a11].[UNIT_COST]) [WJXBFS1]
from [order_detail] [a11]
join [lu_employee] [a12]
on ([a11].[EMP_ID] = [a12].[EMP_ID])
join [lu_item] [a13]
on ([a11].[ITEM_ID] = [a13].[ITEM_ID])
where [a13].[ITEM_NAME] in (?, ?, ?)
group by [a11].[ITEM_ID],
[a11].[CUSTOMER_ID],
[a12].[COUNTRY_ID]
) [pa13]
on ([pa11].[COUNTRY_ID] = [pa13].[COUNTRY_ID] and
[pa11].[CUSTOMER_ID] = [pa13].[CUSTOMER_ID] and
[pa11].[ITEM_ID] = [pa13].[ITEM_ID])
join [lu_country] [a14]
on ([pa11].[COUNTRY_ID] = [a14].[COUNTRY_ID])
join [lu_customer] [a15]
on ([pa11].[CUSTOMER_ID] = [a15].[CUSTOMER_ID])
join [lu_item] [a16]
on ([pa11].[ITEM_ID] = [a16].[ITEM_ID])Error: This is a specific test scenario with Rank() function in select statement and a nested query with prompt in inner query and the follow error message displays as:
Error: QueryEngine encountered error: OdbcPreparedStatement::GetParameterDataTypes() failed.
Error type: Odbc error. Odbc operation attempted: SQLDescribeParam..
select Rank() over(order by [a11].[CUST_FIRST_NAME] asc) [Rank1], [a11].[CUST_FIRST_NAME] [CUST_FIRST_NAME], [a11].[CUST_LAST_NAME] [CUST_LAST_NAME] from ( select [lu_customer].[CUST_FIRST_NAME] [CUST_FIRST_NAME], [lu_customer].[CUST_LAST_NAME] [CUST_LAST_NAME], [lu_customer].[CUSTOMER_ID] [CUSTOMER_ID] from [lu_customer] [lu_customer] where [lu_customer].[CUST_LAST_NAME] = ?Prompt ) [a11]
These defects are caused by our shipped ODBC SQL Server driver. We will fix them on next release.
Currently, there are 3 workarounds to solve the issue.
1. Change driver to use our shipped JDBC SQL Server driver.
2. Change driver to Microsoft SQL Server driver.
3. Disable Parameterized Query by editing the data instance.
