oscilatingcretin February 2016

Differences between TdOleDb and Teradata .NET provider

In converting some of our old apps from the TdOleDb provider to the .NET provider, I've discovered a number of differences in the SQL that's supported. One of two examples I've come across are how TdOleDb allows this:

substring('abcd',2,2)

The .NET provider, however, throws an exception. You have to convert it to one of Teradata's two supported substring functions like this:

substr('abcd',2,2)
substring('abcd' from 2, 2)

Is there a way to identify all known differences in how the parser works between these two providers? We certainly plan on testing everything, but we could miss one or two SQL executions, so being able to search our apps for unsupported SQL would be a great help.

Edit

Here's the other example I found where the two providers behave differently. This is the old OleDb code that is executing a macro with CommandType.StoredProcedure:

cmdQuote = New OleDb.OleDbCommand
cmdQuote.Connection = conQuote

cmdQuote.CommandType = CommandType.StoredProcedure
cmdQuote.CommandText = "mydb.mymacro"

When I change all of the OleDb classes to those of the .NET provider, Teradata throws this exception:

[Teradata Database] [5494] 'mymacro' is not a stored procedure 

To get it to work with the .NET provider, I have to convert it to this:

cmdQuote = New TdCommand
cmdQuote.Connection = conQuote

cmdQuote.CommandText = "execute mydb.mymacro(?)"
cmdQuote.Parameters.Add(MyParam)

Answers


dnoeth February 2016

Seems like OleDB supports (at least some) ODBC-syntax, see

Scalar Functions

Post Status

Asked in February 2016
Viewed 3,193 times
Voted 5
Answered 1 times

Search




Leave an answer