Devart Forums

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.

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

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

Instead of "TOP" use "LIMIT":
becomes

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

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.

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

Hi,

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

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