The TOP statement reports a syntax error when UniDAC queries the dbf file

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Wilson Alex
Posts: 16
Joined: Fri 13 Dec 2019 16:39

The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Wilson Alex » Wed 13 Oct 2021 02:51

Hi,

I am trying to use UniDAC to query the data in the Visual FoxPro database. I refer to the instructions in this official document (https://blog.devart.com/delphi-component-for-dbf.html) to connect the dbf data table file, and everything is normal.

But when I use the TOP n function to query the first record in the table that is larger than a certain field, it keeps reporting "near "1"1: syntax error".
https://sm.ms/image/A9G4pHiml8vEKaF

But I use Microsoft Visual FoxPro 9 SP2 to execute the same SQL statement in the command line window and it can be executed successfully.
https://sm.ms/image/sVNEABow6dDTFPJ

I want to know where the problem is and how to deal with it.

Wilson Alex
Posts: 16
Joined: Fri 13 Dec 2019 16:39

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Wilson Alex » Wed 13 Oct 2021 14:48

Hi MaximG, Could you give me some help? I'm a little anxious.

frickler
Posts: 37
Joined: Wed 04 Apr 2018 08:30

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by frickler » Mon 18 Oct 2021 11:30

UniDAC uses SQL engine of SQLite for this. So you are limited to SQLite compatible SQL.

Instead of "TOP" use "LIMIT":

Code: Select all

SELECT TOP 10 id, name FROM foo ORDER BY id
becomes

Code: Select all

SELECT id, name FROM foo ORDER BY id LIMIT 10

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Stellar » Tue 19 Oct 2021 12:55

Hi,

Devart ODBC Driver for xBase is designed with using SQLite engine, therefore to compose queries using our drivers, you can use the syntax used in SQLIte: sqlite.org/lang.html
In most cases, our driver supports the SQLite syntax. To limit the number of rows returned for a query, pelase use the LIMIT clause.

For example:
SELECT Name FROM TestTable LIMIT 1

Should you have any questions, do not hesitate to ask!
Best regards,
Sergey

Wilson Alex
Posts: 16
Joined: Fri 13 Dec 2019 16:39

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Wilson Alex » Thu 04 Nov 2021 07:44

Hi frickler, Stellar.
Thank you, I had to use Microsoft Visual FoxPro OLEDB Provider to complete the development of related functions at that time, and I will try to develop with UniDAC again.
I have a question. When using the TUniQuery component to query a dbf table(file name: 20210916.dbf), is the dbf file name after the FROM statement? Must be quoted and not include the extension?
If I don’t do this, I will always prompt: near "20210916": syntax eror.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Stellar » Tue 09 Nov 2021 07:46

Hi,

Thank you for your reply.

You don't have to specify the file extension in SQL statements when querying your data with UniDAC.

[Table1.DBF]
UniQuery1.SQL.Text := 'SELECT * FROM Table1';

Kindly note that for the proper work of UniDAC the names of DBF tables must not start with numbers.
Please try to rename the table files (* .DBF, * .CDX, * .FPT, etc.), for instance:

"20210916.dbf" -> "T20210916.dbf".
"20210916.cdx" -> "T20210916.cdx".
"20210916.fpt" -> "T20210916.fpt".

Please, let us know if any other questions arise!
Sergey
Devart Team

Wilson Alex
Posts: 16
Joined: Fri 13 Dec 2019 16:39

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Wilson Alex » Mon 15 Nov 2021 02:35

Hi,

Thank you very much for your patience, You saved my life.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Stellar » Fri 03 Dec 2021 10:55

Hi,

Thank you for your reply! I was happy to assist you!
In case you have any further questions about our products, please feel free to contact us any time!

Sergey
Devart Team

Post Reply