TM19.4 TI: MySQL ODBC Fetching Records Issue

[shallabhkhera]

I'm facing a problem fetching records via select statement from MySQL Ver 4 Database from TI.
I have installed MySQL ODBC connector ver 3.5( or even ver 5.1) for connecting to the Mysql4(for MYSQL5) server.
It is connecting fine with the ODBC connection.
With TM1 I can execute the describe statements and admin statements like

show tables;
desc db_table;


BUT
select * from db_table where id = 1;

is giving an error "SQL Statement failed". In message log as well error="" string is empty

Select statements are not working on any table. Are you aware of any issue like this?
Has anyone faced any issues connecting TI to MySQL via ODBC.

[Marcus Scherer]

does the spec. of the tablename help?

select * from db_table where db_table.id = 1;

Regards, M.

[shallabhkhera]

No it didn't help, I think its a TI bug as there are no issues with Oracle database.

Its funny actually that desc db_table; works and select statement doesn't.

[Steve Rowe]

Hi,

Perhaps if you post the full script of your TI it may help. I've not worked with MySQL but with Oracle and M$SQL and had no problems with them.

Cheers,

[shallabhkhera]

Hi Steve,

That is the complete script, as I just started doing reports for MySQL. From Oracle I also didn't face any issue.

select * from file where file.FileID = 1;


is the complete query. I was just trying to test whether I can do everything possible with TI as of MySQL DB. but I got stuck here in select statement query only.

Shallabh

[Steve Rowe]

I see so that is in the data source definition?

One possibility is to check that the query returns any rows? Do you get anything better without the Where clause?
Like I said I'm not familiar with MySQL but is the trailing ";" required, this might be a problem since it is also TI end of line syntax, but if you have used it in your other queries then I guess it is OK.
The only other thing I can think of is that the ODBC provider is bugged somehow, does the ODBC provider work if you run the same query from Excel for example?

HTH

[shallabhkhera]

I tried the MySQLBrowser before which was running fine. Then I also made an ODBC link via Excel that also ran fine.

Issue is no error shown by the TM1 server in the server log

5560 DEBUG 2009-09-17 10:44:13.458 TM1.SQLAPI Doing SQLConnectW( "mamdb", "root" )
5560 ERROR 2009-09-17 10:44:13.551 TM1.SQLAPI SQLExecDirect() fails, error = ""
5560 DEBUG 2009-09-17 10:44:23.520 TM1.SQLAPI Doing SQLConnectW( "mamdb", "root" )
5560 INFO 2009-09-17 10:44:23.661 TM1.SQLAPI Checking Driver Capabilities for database "mamdb"
5560 INFO 2009-09-17 10:44:23.661 TM1.SQLAPI Driver supports SQLFetchScroll
5560 DEBUG 2009-09-17 10:44:28.786 TM1.SQLAPI Doing SQLConnectW( "mamdb", "root" )
5560 ERROR 2009-09-17 10:44:28.880 TM1.SQLAPI SQLExecDirect() fails, error = ""
5560 DEBUG 2009-09-17 10:45:27.459 TM1.SQLAPI Doing SQLConnectW( "mamdb", "root" )
5560 ERROR 2009-09-17 10:45:27.553 TM1.SQLAPI SQLExecDirect() fails, error = ""
5560 DEBUG 2009-09-17 10:46:19.945 TM1.SQLAPI Doing SQLConnectW( "mamdb", "root" )
5560 ERROR 2009-09-17 10:46:20.023 TM1.SQLAPI SQLExecDirect() fails, error = ""

[lotsaram]

You have definitely tried with MS Query or another query tool using a DSN with the same ODBC driver and it worked? This woudl point to something specific to TM1 as opposed to just a dud ODBC driver.

Still this is quite unusual, might be worthwhile searching for an alternative driver and/or logging a support call.