Database vendor code -201 when connecting to Informix database

[javedi]

Hello

I've been rattling my brains over this and now am out of ideas.

When adding one (the same) table from an Informix database Crystal returns an error showing.

"Failed to retrieve data from the database.
Details: 42000:[Informix][Informix ODBC Driver][Informix]A syntax error has occured. [Database vendor code: -201]

What is more bizzare is that this happens only to some tables, other return data fine.

Any idea's would be welcomed.

Javedi
Using Crystal Reports R11-R2.

 

[kray4660]

I do not know the answer, but we had a propriety DB once and it would return an odd error if I linked more than three tables. The only thing I can suggest is re-installing the ODBC driver and checking for updates.

 

[javedi]

Thanks Kray,

I've updated the driver to no success.

Javedi

 

[Turkbear]

Hi,
Can you post the SQL code produced by CR - Perhaps it is not fully Informix compatible ( syntax can change between versions
of CR and of Informix ( at least for IDS from 9 to 10), apparently)

Are you using a command object with SQL you created or just 'standard' pick and choose from tables and fields? How about any inserted SQL expressions?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[javedi]

Hi Turkbear,

I've tried both. If I type SQL in the Command option to select * from the 'resource' table it works. But when I use this Command to link to with other tables it returns more errors.

I did look at the SQL syntax for Informix and found that it was the same for a table that had no problems and the tables that return this error.

With problem;
SELECT resource.resourcename
FROM db_cra:informix.resource resource

without;
SELECT agentconnectiondetail.sessionseqnum
FROM db_cra:informix.agentconnectiondetail agentconnectiondetail

This version of CR was recommended by the db vendor.

Thanks,
Javed

 

[Turkbear]

Hi,
One more thing - can you post the SQL created after the joins that cause the problem are created?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[javedi]

Hi

There's no joins as i'm only using one table. If that is not what you meant then you'll have to elaborate.

Thanks,
Javedi

 

[Turkbear]

Hi,
Then I am confused by this:

If I type SQL in the Command option to select * from the 'resource' table it works. But when I use this Command to link to with other tables it returns more errors

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[javedi]

Sorry for confusion Turkbear,

If I use the Command option to select * from the resource table the data is returned without error, but not when using the resource table directly.

I looked at the option of using the Command option but did not use this as it was causing other errors.

The error I am trying to fix is the -201 code.

Javedi

 

[Turkbear]

Hi,
OK..When you use the resource table directly and CR throws the
syntax error, can you post the SQL that CR created?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[javedi]

Hi

It is;

SELECT resource.resourcename
FROM db_cra:informix.resource resource

copied from Database -> Show SQL.

But other tables that use the same syntax work.

Any idea's?

Javedi

 

[Turkbear]

Hi,
Odd...Looks identical to the one that works, but MUST be different in some way to trigger a syntax error (which deals with improperly formed queries) rather than some other error ( like 'field not found').

Maybe, as an experiment, try this:
Create the report but, instead of selecting from the table, use a Command instead and write the SQL yourself - see if that works - it may give a clue.

Also, try that query directly on the database using whatever user tool Informix supplies ( I don't use Informix).





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[javedi]

Hi

If i use the Command with SQL for the single table, data returns error free.

If i write SQL for selecting fields from more than one table, Command returns the same error -201.

I'm writing in SQL and not ESQL, if this makes a difference?

Can multiple fields from tables be used in Command option?

Thanks,
Javedi

 

[Turkbear]

Hi,
You had said:

no joins as i'm only using one table

but now you indicate that the4 problem only happens when you join tables

If i use the Command with SQL for the single table, data returns error free.

If i write SQL for selecting fields from more than one table, Command returns the same error -201.

Please post the sql you wrote when using more than one table.
(The one that triggers the error)






To Paraphrase:"The Help you get is proportional to the Help you give.."