Problem using Informix ODBC for Crystal report

Hi everyone, We have installed Informix IDS 11.50 FC3 on HP-UX-11.23 and CSDK 3.50 TC7 on Windows 7. We have some Crystal reports developed with Oracle sometime back. We are porting them to Informix. The Crystal Report used is of version Crystal Reports 8.5. The reports use Informix ODBC DSN to connect to the server. The reports with single table query work fine. When the report has multiple tables with joins, it gives ODBC err: [Informx][Informix ODBC Driver][Informix] A syntax error has occured. I enabled SQL trace on the IDS to check what SQL statement is executed and if there is any error. I could see the SQL is executed successfully and returned one row (sql_actualrows on syssqltrace is 1 and sql_sqlerror is 0). Any help will be really appreciated. Thanks in advance.

On Wed, Sep 15, 2010 at 2:05 PM, SARVESH SWAMY <swami_sarvesh@yahoo.com>wrote: > Hi everyone, > We have installed Informix IDS 11.50 FC3 on HP-UX-11.23 and CSDK 3.50 TC7 > on > Windows 7. We have some Crystal reports developed with Oracle sometime > back. > We are porting them to Informix. The Crystal Report used is of version > Crystal > Reports 8.5. The reports use Informix ODBC DSN to connect to the server. > The > reports with single table query work fine. When the report has multiple > tables > with joins, it gives ODBC err: [Informx][Informix ODBC Driver][Informix] A > syntax error has occured. > > I enabled SQL trace on the IDS to check what SQL statement is executed and > if > there is any error. I could see the SQL is executed successfully and > returned > one row (sql_actualrows on syssqltrace is 1 and sql_sqlerror is 0). > > Any help will be really appreciated. > > Thanks in advance. > That's a very strange situation... My first idea would be that you didn't catch the right SQL, but that would mean doubting you which is not my intention. So, in this scenario what I would recommend is: 1- Make sure that you got the right SQL 2- Try to run that SQL using the Informix ODBC driver with another tool (any query tool that uses ODBC or as a last resort something like MS tools (Excel for example) 3- Activate the TRACE (option on the DSN configuration) and collect info and open a PMR with IBM Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016364ecd6012da5104904d0415

Hi Fernando, Thanks for the responce. I found out the problem. First thing is the SQL trace at IDS is not logging the failed query. The report had a sub report that had a different query, which I did not notice. The subreport has a query something like below select ORDERDTL.itm_num from ORDERDTL ORDERDTL, ITEM ITEM WHERE ORDERDTL.itm_num = ITEM.itm_num and ITEM.itm_num = 'XYZ' It shows error for table ITEM with alias ITEM. I am not sure if ITEM is a key word in IDS. I have a table called LANGUAGE, I get same error for that table. I removed the alias for ITEM table, it started working. Thanks again. Sarvesh

On Thu, Sep 16, 2010 at 7:33 AM, SARVESH SWAMY <swami_sarvesh@yahoo.com>wrote: > Hi Fernando, > > Thanks for the responce. I found out the problem. > First thing is the SQL trace at IDS is not logging the failed query. > The report had a sub report that had a different query, which I did not > notice. > > The subreport has a query something like below > > select ORDERDTL.itm_num from ORDERDTL ORDERDTL, ITEM ITEM > WHERE ORDERDTL.itm_num = ITEM.itm_num and ITEM.itm_num = 'XYZ'> > It shows error for table ITEM with alias ITEM. I am not sure if ITEM is a > key > word in IDS. I have a table called LANGUAGE, I get same error for that > table. > I removed the alias for ITEM table, it started working. > > Thanks again. > > Sarvesh > ITEM and LANGUAGE are both reserved words in Informix 11: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids _sqs_1818.htm Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00c09f899b690e11de04905d2323