One Star

Default DB issues using Generic ODBC to Sybase

I'm trying to set up a generic ODBC connection to a Sybase DB. The ODBC connection requires a default database to be defined, but unless I specify this as either master or temdb - I get a "general error" when I test the DB connection
in Talend (it works fine for the ODBC test).
I could leave the default as tempdb or master - but when I go to Talend and try and create a schema under the connection - it's looking in the wrong database for the tables - and there's no way of pointing the connection at the right DB.

Dougie McGibbon
7 REPLIES
One Star

Re: Default DB issues using Generic ODBC to Sybase

Hi.
I had the same problem connecting to MS SQLServer using ODBC. I managed to fix it by using a full path to the table in my sql.
SELECT COLUMN1, COLUMN2 FROM ..TABLE
I don't know if it will work with Sybase, but give it a try.
Philip
One Star

Re: Default DB issues using Generic ODBC to Sybase

Yes, that will work, but the ETL I'm building will be used against numerous different DBs with the same data set - so hardcoding the name of the DB in the ETL means I'll need to rewrite the 60odd DBinput components every time I try to get data from a different DB. It also means that if I use a select from multiple tables as the DB input - I can't create a schema for it because it can't directly reference the tables I'm selecting from.
The ODBC connection should pass through the default database connection to Talend and show up in the DB Connection metadata - but it's not being used.

Dougie McGibbon
One Star

Re: Default DB issues using Generic ODBC to Sybase

Hi.
I just tried something and it worked. As your first step, use a tPerl component and set a variable, without the "my" so that the scope is wider than where you declare it.
$MyDB = '.';
In your SQL statement, using double quotes, code as follows:
"select COLUMN1, COLUMN2 from ${MyDB}.TABLE"
This should make your code more generic.
Hope this helps
Philip Deetlefs
Employee

Re: Default DB issues using Generic ODBC to Sybase

Hi,
I'm trying to reproduce this problem. Could you please tell me more about your conf ( OS, TOS, Sybase ASE version, ODBC driver version ).
I run with XP, TOS 1.1.1r1413, ASE 15 and Sybase ODBC driver 4.20.00.67 and I don't have any problem with default database definition.
Best Regards.
One Star

Re: Default DB issues using Generic ODBC to Sybase

Hi.
I have also tried something else that worked. I created an environment variable with value '.', inluding the single quotes. I then used the following code in a tPerl component: $MyDB = $ENV{MyDB}; In this way, you only have to change the value in one place to point your programs to different data bases. So there is no hardcoding involved anymore.
Philip
One Star

Re: Default DB issues using Generic ODBC to Sybase

OS is Windows XP pro SP2. The ODBC driver is the Sybase ASE ODBC driver version 15.00.00.130. ASE DB software is 15.0.1 (running on Windows 2003 Standardx64 Sp1). TOS Software is 1.1.2.r2022 (updated this morning).
I create a System Datasource using the ASE driver. The sybase login I'm using has it's own default DB (and has SA role so it's not a Sybase security issue). ODBC Connection test using the ODBC Data Source Administrator works ok.
Create a Metadata DB connection using the Generic ODBC DB type and the CHECK test will always return a General Error - unless I set the ODBC connection default DB to either Master or Tempdb (leaving it blank doesn't work either).
Dougie McGibbon
Employee

Re: Default DB issues using Generic ODBC to Sybase

Hi,
With Talend Open Studio v2.0.0M1, native Sybase connection is available.
Go on download page to check out this new version.
Regards.