INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ODBC Parser problems....

ODBC Parser problems....

(OP)
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:

CODE

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:

CODE

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?

RE: ODBC Parser problems....

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...).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close