How to execute SQL queries on a DBF file

The main feature of DBF Commander Professional is the ability to execute SQL queries.

The SQL Panel is located at the bottom of the main window (by default). It consists of two tabs: SQL and Messages (see the pictures).

sql dbf     sql message

The Messages tab displays information about the success of query execution, as well as error messages (if any).

The SQL text area will contain all the queries you type. To execute a single query, select the SQL text and click the Execute button (or press the F5 key). If you want to run several queries at once, separate them from each other with an empty line (in case of the UNION clause, there is no need to separate the queries, since it's a single query):

sql union clause

sql error

As you can see on the picture above, you can use comments in the SQL text.

Select all the queries that you want to execute, and press the F5 key (or click the Execute button). After the execution of queries is complete, the resulting table will be opened (if any).

You can also save an SQL text from the SQL Panel to a text file, as well as load it later to SQL Panel using the appropriate tool buttons.

If any SQL query execution finishes when DBF Commander Professional is minimized, the application's icon will start flashing in the Taskbar to inform you about that.

If you run the "SELECT ..." command without the "... INTO TABLE ..." clause, a temporary DBF file named "Query.dbf" will be created in the current user's Application Data directory (for example, "C:\Documents and Settings\username\Application Data\DBF Commander Professional\Query.dbf"). You can also use the "... INTO CURSOR ..." clause to save the result of the query execution to a custom-named, temporary DBF file (cursor).
If you close the application, the temporary files will be deleted.

If you use a relative path to the DBF file in the query text, please note that the DBF table will be looked for in the directory from which the current DBF file was opened. If there are no open files or no file was found at the specified path, the "File does not exist" error will occur. Therefore, it's better to use a full path in SQL queries. To insert the current file's full name into the SQL query text, click the Insert Current Filename button or press Ctrl+F5.

For more information on the SQL syntax, detailed description of core SQL statements, and query examples, please see the following topics:

In addition to the common SQL syntax, DBF Commander supports many other Visual FoxPro OLE DB Provider commands like VAL, STR, CTOD, YEAR, BETWEEN, etc. For more information on these functions, see this MSDN topic.

 

SQL Tools

 

Insert Current Filename (Ctrl+F5)

If at least one DBF file is open and the SQL Panel is visible, this command will insert the current DBF file's full file name at the SQL Panel cursor position.