pyodbc access to Teradata (via web2py)

[Andrew]

Hello,
I'm using pyodbc to connect to a Teradata database in my normal
python
programs.
I am just starting to use web2py and experimenting with connecting
to
the same database. No luck so far.

The webbook says there is support for pyodbc but I cannot find any
examples of the connection string. The one that works in python
that I'm trying to adapt is:


db =
DAL('pyodbc:"DRIVER={Teradata};DBCNAME=MyDBTST;UID=username;PWD=password;QU­
IETMODE=YES;"')


Although pyodbc is a generic module, do I still need specfic Teradata
support within web2py, which I assume doesn't exist ?


Thanks
Andrew

[Massimo Di Pierro]

You need an Adapter. Let me look into this and send you one to try...

[DenesL]

web2py uses pyodbc to connect to MS SQL Server.

The connection string has the following format:
db=DAL("mssql://user:password@host/database")

If there are no differences in the SQL language betweeen MSSQL and
Teradata then you are in luck and the current adapter might just work.

[Andrew]

Thankyou Massimo and DenesL,

Denesl, I know MSSQL has its own python ODBC package (pymssql?) , so
I've never been sure if the "mssql:..." reference is really using
pyODBC, or another package. I was expecting to see pyODBC
explicitly referenced, as it is in a simple python scipt's connection
string. If what you say is true, then each database type that uses
pyODBC will need its own prefix type in the connection string. I
expect that's what Massimo refers to by an "Adaptor".

Massimo, looking forward to hearing from you.

Regards

Andrew

[Andrew]

Hi Denesl,
I was misinterpreting the list of databases in Chapter 6 of the web2py
book. I read it as a 2 column list, not realising that the pyodbc
reference was specifically relating to SQLServer and DB2. I would
say that Teradata SQL would be closer to DB2 than MSSQL. I'll give it
a try but there will be some dbms specific things that won't match.

[Andrew]

Hi Massimo,

Have you been able to look into creating a Teradata Adaptor ?

Thanks and Regards

Andrew

[Massimo Di Pierro]

I added but did not try. Get trunk and try something like:

db=DAL('teradata://DRIVER{Teradata};DBCNAME=10.10.10.10;
UID=test123;PWD=xxx;QUIETMODE=YES;')

Look into dal.py TeradataAdapter.__init__

I assumed the SQL is DB2 compatible but I am sure there are
exceptions.

[Andrew]

Thankyou Massimo,

I have been able to connect OK to a Teradata database based on the
change to dal.py. An important parameter to specify in the DAL for
Terdata is "DATABASE=xyz" to set the default database. This worked OK
as is.

There are a few differences between DB2 and Teradata though (as I am
learning):
Teradata does not have a IDENTITY_VAL_LOCAL() equivalent. For now,
I've added to the Teradata Adaptor:
def lastrowid(self,table):
return None
I am also experimenting with tables that are non auto increment
(following the legacy examples in the book) as these are more typical
in Teradata. Note: Teradata Identity columns do not increment by 1,
they will ensure uniqueness but due to the parallel architecture the
next number won't necessarily be 1 more than the last one.
I created a table with integer PK columns but the insert statement
generated by web2py is attempting to insert NULL values. I'm getting
lots of Rollbacks too.
As I've only just got this working and I'm new to web2py, I'll keep
experimenting with different combinations to see if I can pin it down.
Thanks for your help Massimo.

P.S. How do I get the shell to put in a line feed in the output
window, or is this normal. Would make it easier to read ?
P.P.S. If I want to run executesql statements, does the table I'm
referencing need to be defined in the model ?

[Andrew]

Hi Massimo,

I'm working on making a few changes to the TeradataAdaptor in dal.py,
but admittedly learning about how it all works as I go along. I hope
to give you some code sometime soon.

I have created a legacy table with a 2 column PK. I am attempting to
do an insert via the admin screen's "database administration" button,
however the generated insert statement tries to insert NULLS for the
PK columns. (Note that I was having trouble in this area with SQL
Server as well - Nothing was inserted, so I may have a more general
issue).

If you wouldn't mind having a look at this, but I'll keep working
through dal.py.

From the ticket:
Note: values present for ID1 and ID2 fields.
File D:\Python\web2py\gluon\sqlhtml.py in accepts at line 1194

Function argument list
(self=<gluon.sqlhtml.SQLFORM object>, request_vars=<Storage
{'_formname': 'AndrewTest3/create', '_f...D2': '6722', 'Name':
'DebugTest', 'ID1': '6656'}>, session=<Storage {'_formkey[AndrewTest3/
create]': '0f51d...ge\xc3\xb8\xcb\xbc|\xa1Y\x16\xabd\xfc\xcd\x81s'}>,
formname='AndrewTest3/create', keepvalues=False, onvalidation=None,
dbio=True, hideerror=False, detect_record_change=False)

Then I get "None" for ID1 and ID2 in dal.py

File D:\Python\web2py\gluon\dal.py in insert at line 4790

Function argument list
(self=<Table {'ALL': <gluon.dal.SQLALL object at 0x01C...rewTest3',
'_format': None, 'virtualfields': []}>, **fields={'ID1': None, 'ID2':
None, 'Name': 'DebugTest'})

File D:\Python\web2py\gluon\dal.py in insert at line 844

Function argument list
(self=<gluon.dal.TeradataAdapter object>, table=<Table {'ALL':
<gluon.dal.SQLALL object at 0x01C...rewTest3', '_format': None,
'virtualfields': []}>, fields=[(<gluon.dal.Field object>, None),
(<gluon.dal.Field object>, 'DebugTest'), (<gluon.dal.Field object>,
None)])

I've changed the ID fields between integer and string, but they both
return NULL.


P.S. this may becoming more of a developer issue than a user one.
P.P.S. Excellent work by the way. This is one of the most
professionally documented and well structured applications I've worked
with. It is a pleasure to be using it.

Thanks