ODBC Parser problems....

[WILLIEWANKA]

Hey everyone, we're experiencing some strange behavior between an application and Teradata. Essentially, we are submitting a query, but the query is being rewritten. We have run some checks to see if the query is being rewritten by the application, or by Teradata.


This is what we are submitting:

sel 
a.column1, 
cast( case 
when a.column2 < 5000
then u.column3
when a.column2 >= 5000
then s.column4
end as decimal(7,5) 
) as column5 
from 
container1.table1 a 
inner join 
container2.table2 t 
on 
t.column1 = a.column1 
LeFt OuteR JoIn 
container1.table3 s 
on 
s.orig_acct_num = a.column1 
LeFt OuteR JoIn 
container1.table4 u 
on 
u.orig_acct_num = a.column1 
order by 1

This is what is stored in the DBC query log:

SELECT 
a.column1 , 
CAST( column2 AS DEC ( 7 , 5 ) ) AS column5 
FROM 
container1.table1 a 
INNER JOIN 
container2.table2 t 
ON t.column1 = a.column1 
LEFT OUTER JOIN container1.table3t s 
ON s.orig_acct_num = a.column1 
LEFT OUTER JOIN container1.table4 u 
ON u.orig_acct_num = a.column1 
ORDER BY 1

As you can see, all the keywords are uppercase instead of lowercase or mixedcase. I've also used a Teradata specific function (add_months) and the case was changed to ADD_MONTHS, so we know the query is being rewritten by something that knows Teradata functions. I have looked through the ODBC documentation, and have found two options that might affect this.

SQLWithCommentsOrParenthesis

= [Yes | No]
Default = Yes
When this option is Yes, the ODBC Driver for
Teradata prepares SQL statements enclosed in
parenthesis or preceded by comments.
When this option is No, these statements are
prepared by Teradata


NoScan

= [ Yes | No ] Default = No
This option is used to enable/disable parsing
of SQL statements by the driver.
In Windows, this option is called
"DisableParsing".
When NoScan is Yes - the p a r s e r in the driver
is bypassed and the SQL statement is sent
directly to Teradata.
This option should not be set when the SQL
statement contains ODBC-specific syntax.
Setting this option while using ODBC-specific
syntax in the SQL statement results in
Teradata RDBMS reporting errors.
When NoScan is No - the p****r in the driver
is not bypassed and SQL statements are sent to
the p a r s e r.



Another thing that is stumping me, is the fact that I can run the query through SQL Assistant, and not encounter the same problem. I can't find any documentation on how the ODBC P a r s e r works.

Has anyone experienced anything like this before? Does anyone know where I can find more documentation on the ODBC P a r s e r for Teradata?

 

[ispaleny]

Teradata ODBC interface bypasses CLI, that's why it is the strangest Teradata interface.

It is used to extend Teradata SQL in several ways, but it criples Teradata in several ways too.

As an example, just try to run:
select 1--1 command

With ODBC parsing enabled, it results in
one row with number 2 named "command"

without it, it results in
one row with number 1 named "1".

In SQL Assistant, you can switch ODBC parser off in Options menu ( something like ODBC Extensions...).