search

Teradata / PyTd

A Python Module to make it easy to script powerful interactions with Teradata Database in a DevOps friendly way.
MIT License
108 stars 43 forks source link

SQL with escape character for LIKE clause can fail with syntax error #103

Open ericsun2 opened 6 years ago

ericsun2 commented 6 years ago

As long as escape '!' or escape '\\' is following LIKE clause in ODBC mode, PyTd fails into the following error:

 SELECT DatabaseName, PermSpace, DBKind
 FROM DBC.DatabasesV WHERE DatabaseName LIKE 'DW!_%' escape '!'

ErrorCode = 3706 ErrorMessage = [42000] [Teradata][ODBC Teradata Driver][Teradata Database](-3706)Syntax error: expected something between the word 'TODO' and ':'.

It looks like a parsing error inside PyTd or ODBC code.

Simply remove the escape '!' part, the query will run (of course with 0 rows returned).

The following alternative works too, but LIKE '%!_test!_' escape '!' should be supported.

 SELECT DatabaseName, PermSpace, DBKind
 FROM DBC.DatabasesV WHERE substr(DatabaseName, 1, 3) = 'DW_'
padhia commented 6 years ago

What version of ODBC driver are you using? I had encountered a similar error #90 when using the new Simba drivers which since has been fixed in the later updates.