Failed to retrieve data from database - DataDirect Paradox

[Former Member]

Crystal Reports 12.1.0.892

Windows XP SP3

DataDirect ODBC Driver for Paradox 5.30.00.23

Paradox 5 database

When I run the following query against two tables:

SELECT `Ticket1a`.`PatronID`, `PHONELIN`.`PhoneNumber`

FROM `PHONELIN` `PHONELIN` INNER JOIN `Ticket1a` `Ticket1a` ON `PHONELIN`.`PatronID`=`Ticket1a`.`PatronID`

I get the following error:

Failed to retrieve data from the database

Details: 42000: DataDirect ODBC Paradox driver Extra characters at end of query: INNER. Database VendorCode: 3902

The SQL tracing log can be found at http://188.grandtheater.org/sql.log

Can anyone tell me what is going on here and how I can get around it?

TIA

[Former Member]

I think something wrong in the syntax even though it is correct the drivers shows some error at INNER.

What happens if you try using this syntax?

SELECT `Ticket1a`.`PatronID`, `PHONELIN`.`PhoneNumber`

FROM `PHONELIN` `PHONELIN` , `Ticket1a` `Ticket1a` where `PHONELIN`.`PatronID`=`Ticket1a`.`PatronID`

Regards,

Raghavendra

[Former Member]

I can make the original query work if I remove the grave accent from everything. But Crystal Reports and the DataDirect driver will not let me alter the SQL statement. When I contacted DataDirect about it, they said it was a Crystal problem.

[Former Member]

Try using "Add command" instead of using tables in the report and you cna modify the query and run the report.

Regards,

Raghavendra

[Former Member]

I tried the Add Command option and entered the SQL query as follows:

select ticket1a.patronid, phonelin.phonenumber

from ticket1a ticket1a inner join phonelin phonelin on ticket1a.patronid=phonelin.patronid

and still got the same error as before. Using the following SQL query:

select ticket1a.patronid, phonelin.phonenumber

from ticket1a, phonelin

where ticket1a.patronid = phonelin.patronid

I was able to get results using the DataDirect driver.

If I use the Microsoft Paradox driver (which is very slow. 15 minutes to run this query) and use the following SQL query:

select ticket1a.patronid, phonelin.phonenumber

from ticket1a ticket1a inner join phonelin phonelin on ticket1a.patronid=phonelin.patronid

I will not get the error and I will get results.

Any reasons why the DataDirect driver is producing errors?

[Former Member]

I think the inner join is not supported in Paradox database. Try running the same query at the data base level and check the correct syntax.

Regards,

Raghavendra

[Former Member]

Inner join is supported. I was able to run it using the MS driver.