TM1 Forum

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.

does the spec. of the tablename help?

select * from db_table where db_table.id = 1;

Regards, M.

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.

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,

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

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

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 = ""

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.