The Query tool allows a user to make multiple connections to ADS, ODBC or PostGreSQL data sources.
You don't need to know anything about SQL to use this application to query all of your data tables.
A tree view window displays the tables, fields, views and index info for each data connection.
It allows you to browse data in ISAM or SQL SELECT mode.
You can pick fields (columns) to browse then filter the data using a new feature built into the
eXpress++ DCBROWSE system that takes advantage of SQL WHERE clauses for fast filtering
and ORDER BY for sorting.
You can tag rows and columns for printing or exporting to Excel.
You can execute Views that are already in the data source or create your own views using SQL
commands and save them to the SqlQuery view dictionary.
These features can also be built into your own applications and work with workareas or arrays
of data objects.
You do not need to use ADSDBE, ODBCDBE or PGDBE in your applications to use this SQL capability
in your applications if you are using .DBF/.CDX files. You only need the free ADS client DLLs which
are in the runtime folder of the Xbase++ Professional Version. (Ace32.dll,Axcws32.dll,AdsLoc32.dll)
You do not need to create an Ads Data-Dictionary to use this capability because SQL queries can be
made against a set of free tables. However, if you want to create a data-dictionary you don't need
the Ads Architect to do this. It can be created in SqlQuery.Exe.
Here is a testimonial:
"SF had a result they could not find from CRL on 12/11/18. I used SqlQuery
and located the record easily. It worked as expected and I was able to see
that it was marked done which is why it wasn't showing in drreview.exe. But
it wasn't showing in drmove.exe as being moved.
I did all of this using SqlQuery. Really good tool!"
Richard Covington - Behavioral Health Systems, Inc.
SqlQuery.Dll can be used in your own applications.
There is no documentation at this time but the source code is included.
There are 2 new classes that are used as sub-classes of the DC_XbpBrowse() system.
They will be documented in eXpress++ build 267 and there will be plenty of samples.
Integrating this feature into your own browses requires only adding a few lines of code
to your DCBROWSE commands. The name of the sub-classes are DC_XbpBrowseFiltered()
and DC_XbpColumnFiltered(). Look in the SqlQuery.Prg file to see how they are used.
I am providing this to all eXpress++ users who have the Xbase++ Professional Subscription
because I want some more beta-testers before releasing build 267.
If you want to use SqlQuery.exe with PostGreSql you can use a PGDBE connection or an
ODBCDBE connection. For ODBC, I recommend that you install the following free
ODBC driver: Devart ODBC Driver for PostgreSQL
It is recommended that you create a new folder named SqlQuery and unzip SqlQuery.zip
into that folder.
http://bb.donnay-software.com/sqlquery/sqlquery.zip
This file will be updated on a regular basis as new features and bug fixes are added.
Run SqlQuery.Exe.
It will load SqlQuery.Ini and connect to all of your data sources.
This has been tested with the following data sources:
ODBCDBE - Sql Server
ODBCDBE - MySql ODBC 8.0 Unicode Driver
ODBCDBE - Advantage StreamlineSQL ODBC
ODBCDBE - Devart ODBC Driver for PostGreSql
ODBCDBE - PostgreSQL Unicode
ADSDBE - Connection to Data Dictionary (DBF/CDX/ADT) (Remote and Local)
ADSDBE - Connection to Free Tables (DBF/CDX) (Remote and Local)
PGDBE - Connection to PostGreSQL server
I anticipate that release by April 2019.
As of now this is a query tool only.
It does not allow SQL statements that can change your data.
If you use Advantage Architect or Sql Server Manager for queries, you will find this to
be much more useful. Right-click a browse column header to set a range for that column.
Left-click a browse column header to sort on that column.
If you need help getting started, make a posting on the forum or Skype me at roger.donnay.
This is an example of how to setup connections to data sources in SqlQuery.Ini:
Code: Select all
[SYSTEM]
TreeFont=10.Lucida Console
ShowBrowseCautionMessages=No
TreeFGColor=1
TreeBGColor=0
[ODBC_1]
Name=MyFireRules (BillingFD)
ConnectString=
Driver={SQL Server}
ServerDict=DESKTOP-O5IFT5O\SQLEXPRESS
Database=BillingFD
UID={||GetEnv('BILLINGFD_UID')}
PWD={||GetEnv('BILLINGFD_PWD')}
ConnectTimeOut=15
[ODBC_2]
Name=Behavioral Health (HippaSuites)
ConnectString=
Driver={SQL Server}
ServerDict=DESKTOP-O5IFT5O\SQLEXPRESS
Database=HippaSuites
UID=
PWD=
ConnectTimeOut=15
[ODBC_3]
Name=MySql (Donnay Web Forum)
ConnectString=FileDSN=c:\SqlQuery\MySql.dsn
ConnectTimeOut=15
[ODBC_4]
Name=Advantage ODBC (BhsTest)
Driver={Advantage StreamlineSQL ODBC}
ConnectTimeOut=15
UID=AdsSys
Database=C:\bhs\bhsmed.add
ConnectString=ServerTypes=2
[ODBC_5]
Name=Advantage ODBC (Medallion)
Driver={Advantage StreamlineSQL ODBC}
ConnectTimeOut=15
UID=AdsSys
Database=C:\medalion\medalion.add
ConnectString=ServerTypes=2
[ADS_1]
Name=Medallion (Medalion.add)
Database=C:\Medalion\medalion.add
ServerFree=C:\Medalion\
AdsLockingType=1
UID=AdsSys
[ADS_2]
Name=Medallion (Free Tables)
ServerFree=c:\medalion
AdsLockingType=1
[ADS_3]
Name=BHS (BhsTest.add)
UID=AdsSys
Database=C:\bhs\bhstest.add
ServerFree=C:\bhs\
AdsLockingType=0
[ADS_4]
Name=BHSServices (c:\bhsservices)
ServerFree=C:\bhsservices\
AdsLockingType=0
[ADS_5]
Name=Sample Program (SqlTest1)
ServerFree=c:\expd20\samples\adssql\files\data\
AdsLockingType=0
[ADS_6]
Name=Meter Shop
ServerFree=c:\meter
AdsLockingType=1
[ADS_7]
Name=DD-787 Ship's Roster
ServerFree=y:\wamp64\www\dd787
AdsLockingType=0
[ADS_8]
Name=Drugs (Free)
ServerFree=c:\drugs
AdsLockingType=0
[ADS_9]
Name=SQL Test (Ads Free)
ServerFree=c:\expd20\samples\adssql\files\data
AdsLockingType=0
[ADS_10]
Type=ADS
Name=Jack SQL
Database=c:\test\jacksql\RecreaNet.add
ServerFree=c:\test\jacksql\
UID=
PWD=
ConnectTimeOut=15
AdsLockingType=0
AdsTableType=2
[ADS_11]
Name=Meter (SqlQuery.add Dictionary)
UID=AdsSys
Database=C:\meter\sqlquery.add
ServerFree=C:\meter\
AdsLockingType=1
[ADS_12]
Database=c:\drugs\SqlQuery.Add
Name=c:\drugs\SqlQuery.Add
UID=AdsSys
AdsLockingType=0
AdsTableType=2
[PG_1]
Name=Northwind (PostGreSql)
ConnectString=
ServerDict=localhost
Database=northwind
UID=postgres
PWD={||GetEnv('POSTGRESQL_PASSWORD')}
[PG_2]
Name=Medallion (PostGreSql)
ConnectString=
ServerDict=localhost
Database=medallion
UID=postgres
PWD={||GetEnv('POSTGRESQL_PASSWORD')}- The is a field picker to help create quick queries. 16 different data sources are shown in the tree view.
- SqlQuery1.JPG (178.42 KiB) Viewed 29112 times
- This is a SELECT statement saved as a VIEW to be executed at any time.
- SqlQuery2.JPG (196.15 KiB) Viewed 29112 times
- This is the resultant browse of a pre-defined view - DTR End of Year Report
- SqlQuery3.JPG (284.87 KiB) Viewed 29112 times
- This screen shows how columns can be tagged for exclusion, exporting or printing.
- SqlQuery4.JPG (283.71 KiB) Viewed 29112 times
- This screen shows how 3 columns were filtered to reduce down to only 42 rows.
- SqlQuery5.JPG (262.71 KiB) Viewed 29112 times
