This article was brought to you by the guys from VDF-GUIdance.
For more DataFlex targeted articles see http://www.vdf-guidance.com


Query tool

by Bob Worsley

Summary

This is a SQL Query tool that only does queries and doesn't allow database updates. It's currently installable for MSSQL, ODBC, MySQL and PostGres
Size: 1600 KB Download
Date Created: 13/09/2020
Date Updated: 21/09/2020
Author: Bob Worsley


Introduction



SQLQueries is a DataFlex 19.1 workspace that gives you the possibility to run queries against a SQL database.

Currently it supports the database drivers from both connectivity kit providers in the market (red. DAW and Mertech). The default database is MSSQL via DAWs driver, but it also works with PostgreSQL and MySQL using Mertech's drivers. On top of that you can also use ÖDBC.

The connectivity kit dependencies are not supplied with the workspace as that could cause version conflicts with whatever driver version you are using.
For this reason, for Mertech there's a binary called "TestConnection.exe" so that this works without the dependencies (mertech.inc etc..)

First Run



On the first run, the application will present you with a configuration dialog to setup the database connection and a user that will use the SQLQueries program.
In our example here we are choosing DAWs MSSQL driver and the connection string as is used in our demo test.

Note that the user here is NOTHING to do with your SQL backend. It is the user of the SQLQueries program. In order to protect the database it has its own user/password logic.

In our example we have the user "Roger Waters" which then can login with user ID "Roger" and a secret password. I'm not here to tell you that it is "PinkFloyd123!" :)

These login credentials are saved in a dataflex file called "User".. if you want to get this setup screen again.. then empty the user file.

Login



Start the program again.

This time you are presented with a login dialog.
Here we enter the login ID we used created ("Roger") and the password that went with it (the super secret one).

Build a query



Now you can build a query either manually or by using the helper dialogs for selecting the table names and column names.


add the rest of your query ...




and press the "Execute" to run your query against the database.

As you can see, the program shows you the result of the SQL database as queried via your standard SQL Query.

There's more you can do.
  • you can export the query results to csv
  • Queries can be saved for later use

Updates



* 2020-09-16 I fixed a minor bug with setup.dg where I had to initialize the oDriver comboform because when first bringing it up it could show the MySQL driver.

The second thing is showing errors when the user types in some incorrect SQL syntax. I do check the main table to see that it's correct, but if a finger fumble spelled "select" as "sselect" or whatever, I would get the big ugly DF error box and setting verbose_state to false did nothing. It turns out that you can disable the error handler in the two SQLSelect functions and the SQL error from the back end comes through quite nicely for both drivers. and tells the real story.

Also added the Scintilla control for editing the SQL

Download



You can download the workspace here: