Teradata support

[benilovj]

It's possible to use a Teradata Express VM image to set up a test instance.

[evenv]

Hi Jake,

First of all, awesome that you're doing active work on this project - it's enormously useful and has even greater potential. Second - I see that you're working on Teradata support which is perfect given that I'm currently working on a Teradata project and would love to setup DbFit for testing. Do you have any idea when the Teradata driver will be ready?

[benilovj]

Hi Even,

Thanks for the encouraging words!

Regarding the Teradata driver, as far as I know, the version I have checked in is fully featured; it's just repackaging of the work that @MMatten and @lifeisstillgood did (in fact it looks like they've since put it on GitHub). My problem is that I still haven't set up a version of Teradata locally to test against, so I can't confirm that it works with the latest FitNesse.

I could really use your help with this; if I provide you with the appropriate jars, could you help me test and provide feedback?

[evenv]

Hi again,

If you can supply the jars I would be more than happy to do some testing :)

[benilovj]

Hi Even,

Great!

To get up and running:

• grab the latest RC:
  https://s3.amazonaws.com/dbfit/dbfit-complete-2.0.0-RC2.zip
• grab the teradata jar:
  https://s3.amazonaws.com/dbfit/dbfit-teradata-2.0.0-RC2.jar
• place the dbfit-teradata jar into the lib folder
• download the teradata jdbc driver, place it into the lib folder
• start fitnesse as normal

The following should connect to your database:

!|dbfit.TeradataTest|
|Connect|host|username|password|database|

And the following could serve as a smoketest:

|Execute|Create table Test_DBFit(name varchar(50), luckyNumber numeric);ET|

|Insert|Test_DBFit|
|name|luckyNumber|
|pera|1|
|nuja|2|
|nnn|3|

|Query|Select * from Test_DBFit|
|name|lucky Number|
|pera|1|
|nuja|2|
|nnn|3|

|Execute|Drop table Test_DBFit;ET|

please let me know whether that seems to work OK.

thanks in advance!
Jake

On 28 February 2013 16:56, Even Vinge notifications@github.com wrote:

Hi again,

If you can supply the jars I would be more than happy to do some testing :)

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/7#issuecomment-14243662
.

[evenv]

Great, I've now set up my environment like you specificed. (I had to specify classpath lib/*.jar to get the fixture to load, but that's no problem.

I'm running into an issue connecting to the database however. From looking at TeradataEnvironment.java it seems like it's using the wrong authorization mechanism - the default is TD and we're using LDAP. According to http://terahelp.blogspot.no/2009/08/connecting-to-database-using-teradata.html this is a simple parameter you can add to the JDBC connection string. I tried specifying the database parameter as "databasename,LOGMECH=LDAP" and then it connected perfectly.

[evenv]

Running the smoketest with the logmech "hack", I am able to create the table OK, but I get the following error when trying to insert rows:

[benilovj]

I'm running into an issue connecting to the database however. From looking
at TeradataEnvironment.java it seems like it's using the wrong
authorization mechanism - the default is TD and we're using LDAP. According
to
http://terahelp.blogspot.no/2009/08/connecting-to-database-using-teradata.htmlthis is a simple parameter you can add to the JDBC connection string. I
tried specifying the database parameter as "databasename,LOGMECH=LDAP" and
then it connected perfectly.

Right, thanks for the feedback. I'll try to find a way to incorporate this into the code and/or documentation (tracked through #20).

[benilovj]

Running the smoketest with the logmech "hack", I am able to create the
table OK,

that's very encouraging!

but I get the following error when trying to insert rows:

Generally, "cannot retrieve list of columns" means that the user that you
are connecting with doesn't have access to read the metadata tables, my
understanding is that in Teradata this is the "dbc.columns" table. Could
this be what's causing the problem?

[evenv]

Hm, I can select from dbc.columns so doesn't seem to be that...

[benilovj]

ok, if that's the case, then I'll have to have a closer look and
potentially build a version that produces more debug info. Thanks again for
testing this out!

On 1 March 2013 13:02, Even Vinge notifications@github.com wrote:

Hm, I can select from dbc.columns so doesn't seem to be that...

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/7#issuecomment-14288170
.

[evenv]

My pleasure, let me know if there is anything else I can test out.

[MMatten]

You can try specifying .

instead of . What does that produce?

[MMatten]

tags! I meant try

db.table

instead of

table

[benilovj]

@MMatten what is your suspicion? That the table isn't being created in the USER database?

@evenv, what the code is doing under the covers is trying to execute the following query:

SELECT columnname, 
       CASE 
         WHEN c.columntype IN ( 'CF' ) THEN 'CHAR' 
         WHEN c.columntype IN ( 'CV' ) THEN 'VARCHAR' 
         WHEN c.columntype IN ( 'CO' ) THEN 'CLOB' 
         WHEN c.columntype IN ( 'I8' ) THEN 'BIGINT' 
         WHEN c.columntype IN ( 'I' ) THEN 'INTEGER' 
         WHEN c.columntype IN ( 'I2' ) THEN 'SMALLINT' 
         WHEN c.columntype IN ( 'I1' ) THEN 'BYTEINT' 
         WHEN c.columntype IN ( 'D' ) THEN 'DECIMAL' 
         WHEN c.columntype IN ( 'F' ) THEN 'DOUBLE' 
         WHEN c.columntype IN ( 'DA' ) THEN 'DATE' 
         WHEN c.columntype IN ( 'TS' ) THEN 'TIMESTAMP' 
         WHEN c.columntype IN ( 'TI' ) THEN 'TIME' 
         WHEN c.columntype IN ( 'BF' ) THEN 'BINARY' 
         WHEN c.columntype IN ( 'BV' ) THEN 'VARBINARY' 
         WHEN c.columntype IN ( 'PD' ) THEN 'PERIOD(DATE)' 
         WHEN c.columntype IN ( 'PT' ) THEN 'PERIOD(TIME)' 
         WHEN c.columntype IN ( 'PS' ) THEN 'PERIOD(TIMESTAMP)' 
         WHEN c.columntype IN ( 'PM' ) THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)' 
       END  AS columntype, 
       columnlength, 
       'IN' AS paramdirection 
FROM   dbc.columns c 
WHERE  c.databasename = user 
       AND c.tablename = 'Test_DBFit' 
ORDER  BY c.columnid 

To aid debugging, would you be able to run that query by hand after running the test which creates the Test_DBFit table? If the query result is empty, does changing the 'user' to the actual database return results? Thanks!

[MMatten]

I've got no obvious suspects but what is the default database for the user being used? That would determine the DB where the table gets created. It would default to the user. I think the query against the DBC views is returning no rows.
If the default DB for the user is not the user/DB then that would happen.

Mark

[MMatten]

It might make sense to change "WHERE c.databasename = user" to "WHERE c.databasename = database". As "database" is the current default database for the user session.

[benilovj]

Should this change happen for both getAllProcedureParameters() and getAllColumns()?

[MMatten]

I believe it should.

Any feedback from Even on whether the default database for the user being different to the user itself is the culprit?

[benilovj]

I believe it should.

Thanks, I'll modify this.

Any feedback from Even on whether the default database for the user being different to the user itself is the culprit?

not yet.

[MMatten]

I think this issue should now be resolved and ready to close.

[javornikolov]

Resolved by #307