SqlQuery was inspired by work I was doing with Richard Covington and Bobby Drakos on their applications back in early December, 2018."Making software is like making babies. It takes 9 months and you don't get it any faster by putting more men on the job."
It is now 6 months later and we have just entered the 3rd trimester.
This started out as simply a tool or utility program, but it soon became apparent that many of the features of this application are also desirable to be embedded in existing Xbase++ applications, especially the new capabilities of the DCBROWSE system and some SQL support functions. Also, the command-line interface opens many new possibilities for seamless integration.
Soon I will be created a series of YouTube videos to create a better understanding of the value of this for for eXpress++ users.
http://bb.donnay-software.com/sqlquery/sqlquery20.zip
http://bb.donnay-software.com/sqlquery/sqlquery19.zip
http://bb.donnay-software.com/sqlquery/builds.txt
Here is an update of the past month of work:
Code: Select all
1.0.141 (This build has changes to SqlStart.Prg, SqlQuery.Exe)
1. Created an alternate 'shorthand' version of the command line interface to
make it easier for application integration:
Command line Options:
/co or /conn:<group name> - Make connection to <group name> in <cIniFile>
/sf or /sqlfile:<cSqlFile> - Load SQL from <cSqlFile> and execute
/ss or /sqlscript:<cTitle> - Load and Execute SQL Script <cTitle>
/st or /sqltable:<cTable> - Execute SELECT * from <cTable>
/sq or /sqlstatement:<cStatement> - Execute <cStatement>
/sq or /sql:<cStatement> - Execute <cStatement>
/pr or /proc:<cProc> - Run procedure <cProc>
/vw or /view:<cView> - Execute View <cView>
/cs or /cmdscript:<cCommand> - Load and Execute COMMAND Script <cCommand>
/it or /isamtable:<cDbfFile> - Open table <cDbfFile> and browse
/is or /isamscript:<cTitle> - Load and Execute ISAM Script <cTitle>
/ini:<cIniFile> - Load <inifile>. default is SqlQuery.Ini
/cc:<n> - Cache columns greater than <n>
ex 1: SqlQuery /co:ADS_15 /sf:arreport.sql /cc:40
ex 2: SqlQuery /co:ADS_1 /it:mn_cor.dbf
ex 3: SqlQuery /co:ADS_15 /ss:"2018 Sales"
ex 4: SqlQuery /co:ADS_1 /pr:"MedallionDrivers"
1.0.140 (This build has changes to SqlStart.Prg, SqlQuery.Exe, SqlQuery.Prg,
SqlQuery.Dll, DCDIALOG.CH, DCLIPX.DLL)
1. Removed the "More Cols" button from SqlQuery browse. Configuration of
the browse should be done with the hot-keys defined in build 1.0.139.
Also, configuration can be done with the button toolbar on the filter
dialog (right-click in browse header).
2. The browse filtering system now combines the use of both the WHERE clause
of the SQL statement and the SET FILTER / SET SCOPE clauses of the work
area. The SET FILTER and/or SET SCOPE is used only when WHERE is not
possible. This is true when creating an ISAM browse in which there is no
SQLSESSION <dacSession> clause in the DCBROWSE statement. It also can
occur if the SQL statement is so complex that rebuilding the WHERE clause
is impractical or too difficult.
3. SqlQuery now supports a command-line interface so that features of the
system can be called seamlessly from other applications.
Command line Options:
/cc:<n> - Cache columns greater than <n>
/sqlfile:<cSqlFile> - Load SQL from <cSqlFile> and execute
/sqlscript:<cTitle> - Load and Execute SQL Script <cTitle>
/sqltable:<cTable> - Execute SELECT * from <cTable>
/sqlstatement:<cStatement> - Execute <cStatement>
/sql:<cStatement> - Execute <cStatement>
/proc:<cProc> - Run procedure <cProc>
/view:<cView> - Execute View <cView>
/cmdscript:<cCommand> - Load and Execute COMMAND Script <cCommand>
/isamtable:<cDbfFile> - Open table <cDbfFile> and browse
/isamscript:<cTitle> - Load and Execute ISAM Script <cTitle>
/ini:<cIniFile> - Load <inifile>. default is SqlQuery.Ini
/conn:<group name> - *REQUIRED* Make connection to <group name> in <cIniFile>
ex 1: SqlQuery /conn:ADS_15 /sqlfile:arreport.sql /cc:40
ex 2: SqlQuery /conn:ADS_1 /isam:mn_cor.dbf
ex 3: SqlQuery /conn:ADS_15 /sqlscript:"2018 Sales"
ex 4: SqlQuery /conn:ADS_1 /proc:"MedallionDrivers"
1.0.139 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL)
1. Fixed bugs in the browse column configuration system.
1.0.138 (This build has changes to SqlQuery.Prg, SqlQuery.Dll)
1. Added 4 Hotkeys to browse system to allow changing column configuration
without the need to right click the heading.
Ctrl-Right = Move Column RIGHT
Ctrl-Left = Move Column LEFT
Ctrl-Delete = Delete Column
Ctrl-Insert = Insert Column
1.0.137 (This build has changes to DCLIPX.DLL)
1. Added 3 more buttons to the Column Filtering dialog:
< Move - Moves the column to the left in the browse.
Move > - Moves the column to the right in the browse.
Choose - Chooses a list of columns to browse.
1.0.136 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL, ;
DCDIALOG.CH)
1. The DCBROWSE command column caching now supports 2 parameters:
@..DCBROWSE .. CACHECOLUMNS [AUTOADD]
The AutoAdd clause enables the automatic column adding capability when
the end of the browse is reached with the keyboard or scrollbar.
2. The Column Filtering dialog that pops up with Right-Button mouse, there
are 3 new buttons on the dialog:
Add - Adds a new column to the browse from a picklist of available
columns.
Insert - Inserts a new column in the browse from a picklist of available
columns.
Delete - Deletes the current column from the browse.
1.0.135 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL, ;
DCDIALOG.CH)
1. Fixed a regression bug that caused an error when editing a record from the
browse.
2. Left-frozen columns in a browse are no longer shown in the "choose
columns" dialog when building a browse configuration.
1.0.134 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL)
1. Added a new field (TABLE) and a new index tag (TABLE) to SqlQuery.Dbf.
This is for storing browse configurations that were created with the
oBrowse:chooseCachedColumn() method. The SqlQuery.Dbf/.Cdx files will
automatically be updated when running this build.
2. Browse columns configurations can now be saved and restored. After
choosing the columns to browse, the user is prompted to save the
configuration for that table. Multiple configurations may be saved for
each table. To restore a configuration select "Browse this Table (Saved
Columns)" from the table tree menu. If more than one configuration has
been saved for a table, a browse of the titles for each configuration
will allow choosing the correct configuration.
1.0.133 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL)
1. Column caching (::chooseCachedColumn() method) has been improved to allow
deleting, moving, and adding columns.
2. Improved the field/column picking dialog.
3. Select "Browse this Table (Pick Columns)" from the table menu to start
with a blank browse. This will allow the user to choose the columns to
browse and their order.
1.0.132 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL)
1. Fixed a bug in DCLIPX.DLL that cause "More Columns" screen to popup if
the last cached column was reached with the right arrow key.
2. Added UUID field create/fill features to a single table in the tree.
1.0.131 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL, ;
DCLIP1.DLL)
1. Removed the need to have a custom handler to handle scrolling to the next
cached browse column. This is now handled automatically by the
DC_XbpBrowse class.
2. Added the column caching to the Browse feature of XDot.exe and DC_Dot().
Now the first 20 columns only will be displayed. This will improve
performance when browsing a database with a lot of fields.
3. Editing a record in SqlQuery will now automatically use SQL UPDATE or SQL
INSERT if a lock cannot be obtained. Some SQL SELECT statements return a
cursor that is STATIC or not updateable. This will cause a lock error in
DC_DbGather(). If the record cannot be updated with a lock, then it will
be updated or inserted using SQL. SQL UPDATE requires that the current
work area contain a field named UUID Char(36) and that the field must not
be empty. SQL INSERT requires that the current work area contain a field
named UUID Char(36) and DC_DbGather() will automatically fill in the
field using UUIDToChar(UUIDCreate()).
4. SqlQuery now supports the ability to add UUID Char(36) fields to all
tables in a connection. Right Click on "Tables", tag the tables you wish
to add the UUID field and then click "Start Update". If the connection
uses a data dictionary, then the data dictionary will be updated also.
5. SqlQuery now supports the ability to fill in all empty UUID fields in all
tables in a connection with UUIDToChar(UUIDCreate()). Right Click on
"Tables", tag the tables you wish to update and then click "Start
Update".
6. DCLIPX.DLL now contains the following new functions and classes:
a. DC_DacSession(). This is a class that inherits from DacSession(). It
contains some new iVars that are required for DC_DbGather() to do
updates and inserts via SQL. The class has an iVar named
"SqlExecuteBlock". This is a code block that receives SQL statements
and executes the statement. This should contain a call to your custom
SQL execute function.
b. DC_GetSqlStatement(). This returns the SQL SELECT statement for the
current work area. If no SQL cursor exists, then it will return "".
c. DC_GetSqlTableName(). This returns the name of the table that is
associated with the SQL statement for the current work area. This is
needed by DC_DbRecord2SqlUpdate() and DC_DbRecord2SqlInsert() when
creating the SQL UPDATE and SQL INSERT statements from a record
object.
d. DC_DbRecord2SqlUpdate(). This returns a SQL UPDATE statement from a
record object. The record object must contain a field named UUID
Char(36) and the field must not be empty. This is required to insure
that the correct record is updated.
e. DC_DbRecord2SqlInsert(). This returns a SQL INSERT statement from a
record object. If the record object contains a field name UUID
Char(36) its value will be set to UUIDToChar(UUIDCreate()) if it is
empty.
6. DC_DbGather() now accepts a new parameter - <oDacSession>. This must be
an object of the DC_DacSession() class. IF <oDacSession> contains a
field named "SqlExecuteBlock" this code block will be evaluated to
execute the SQL UPDATE or SQL INSERT command in the event that a lock
cannot be obtained on the SQL cursor.
1.0.130 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL,
DCDIALOG.CH )
1. Added a new CACHECOLUMN clause to the DCBROWSECOL command. This will
not add the column to the browse until a new method of the DCBROWSE
class is called - oBrowse:chooseCachedColumn(GetList). This can be
called from a pushbutton or hotkey. Another new method is
oBrowse:NextCachedColumn(). This can be called from a custom handler to
automatically add the next cached column when using the keyboard or
scrollbar to move past the last visible column.
This new feature is used in SqlQuery to improve the performance of SQL
SELECT * browse windows that have a lot of fields. SqlQuery will build
columns only for the first 20 fields and a button will be displayed to
choose additional columns to add to the browse.
2. Added a new CACHECOLUMNS <nColumns> clause to the DCBROWSE command. This
will insure that columns which have a position greater than <nColumns>
will be cached.
1.0.129 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL)
1. Filtering system now has a "Tag" button in the Right-Click dialog.
This will toggle the ::isTagged field of the column and display a tagged
column in a different color. Columns that have the ::isTagged field
set to TRUE will be detected by the print and excel routines and give
the user the option of printing only tagged columns.
2. Fixed bugs in the print routine of the Browse. Also moved the print code
from SqlQuery.Dll to DCLIPX.DLL so that the printing can be done in your
application browse windows - Just call oBrowse:PrintBrowse().
3. Moved the Export to Excel code from SqlQuery.Dll to DCLIPX.DLL so that
the export can be done in your application browse windows - Just call
oBrowse:browse2Excel()
4. Added an iVar to the DC_XbpBrowse class to set the color that is to be
used for column tagging. This may be an array of 2 colors or a code
block that returns an array of 2 colors.
Ex: DCBROWSE .. ;
EVAL {|o|o:columnTagColor := {GRA_CLR_WHITE, GRA_CLR_DARKPINK}}
1.0.127 (This build has changes to SqlQuery.Prg, Sqlquery.Dll, DCLIPX.DLL)
1. Fixed a few runtime errors in SqlQuery.Prg.
2. Set the ODBCSSN_INDEX_AUTOOPEN property of the ::session object to TRUE.
This fixes an anomoly in Xbase++ browse navigation that can cause a
dbSkip(-1) to fail and put the workarea at Eof() when using the ODBCDBE.
3. Transferred some of the capabilities of SqlQuery.Dll to the
DC_XbpBrowseFiltered and DC_XbpColumnFiltered classes in DCLIPX.DLL.
This makes it simpler to add these features to existing browse systems in
your eXpress++ applications. Now can add column tagging, exporting and
printing into applications using DCBROWSE.
1.0.126 (This build has changes to SqlQuery.prg, SqlQuery.Dll, DCLIPX.DLL,
DCDIALOG.CH)
Changes to eXpress++ code:
1. Filtering system now has a "Total" button in the Right-Click dialog.
This will be enabled for all columns that are numeric. Clicking the
button will total all rows for that column and display the total in a
message box. The totaling algorithm will respect any filters that have
been set and only total visible rows.
2. DCBROWSE has the following parameters that support the SQL filtering
system:
SUBCLASS 'DC_XbpBrowseFiltered()' - Subclass may also be a class that
inherits from DC_XbpBrowseFiltered()
for customization of browse.
SORTCONFIG <oSortConfig> - a pointer to a DC_XbpPushButtonXPConfig()
object. This will create a sort button in the
browse header.
SQLSESSION <oSession> - a pointer to the DacSession object that connects
to the SQL data source.
3. DCBROWSECOL has the following parameters that support the SQL filtering
system:
SUBCLASS 'DC_XbpColumnFiltered()' - Subclass may also be a class that
inherits from DC_XbpColumnFiltered()
for customization of columns.
SQLFIELD <cFieldName> - The name of the field of the database that
corresponds to the browse column. This may be
more than 1 concatenated field names:
Example: SQLFIELD '[CASH]+[CREDIT]'
SQLSORT - This will create a SORT button which will select either an open
index for sorting the column or will generate an ORDER BY
clause to add to the SQL statement.Here is a Sample program that I created for Steve Darby. Compile and run it to see how it works.
It uses a SQL statement as a template which is then modified when the user enters the start date
and month of the report. After the months and years are embedded in the SQL, it is executed by
SqlQuery.Exe via the command-line interface. The user can then print or export the browse or
use the filtering/sorting system of the browse.
Code: Select all
#INCLUDE "dcdialog.CH"
FUNCTION Main()
LOCAL cSql, nStartMonth, nStartYear, GetList[0], cSqlSave, lSqlConverted := .f., ;
oSqlQuery, oConnection
TEXT INTO cSql WRAP
select bcomp, company,
sum(iif(month(invdate)=%1m% and year(invDate)=%1y%,invtotal,0)) as %1c%_Amt_%1y%,
sum(iif(month(invdate)=%1m% and year(invDate)=%1y%,1,0)) as %1c%_Trans_%1y%,
sum(iif(month(invdate)=%2m% and year(invDate)=%2y%,invtotal,0)) as %2c%_Amt_%2y%,
sum(iif(month(invdate)=%2m% and year(invDate)=%2y%,1,0)) as %2c%_Trans_%2y%,
sum(iif(month(invdate)=%3m% and year(invDate)=%3y%,invtotal,0)) as %3c%_Amt_%3y%,
sum(iif(month(invdate)=%3m% and year(invDate)=%3y%,1,0)) as %3c%_Trans_%3y%,
sum(iif(month(invdate)=%4m% and year(invDate)=%4y%,invtotal,0)) as %4c%_Amt_%4y%,
sum(iif(month(invdate)=%4m% and year(invDate)=%4y%,1,0)) as %4c%_Trans_%4y%,
sum(iif(month(invdate)=%5m% and year(invDate)=%5y%,invtotal,0)) as %5c%_Amt_%5y%,
sum(iif(month(invdate)=%5m% and year(invDate)=%5y%,1,0)) as %5c%_Trans_%5y%,
sum(iif(month(invdate)=%6m% and year(invDate)=%6y%,invtotal,0)) as %6c%_Amt_%6y%,
sum(iif(month(invdate)=%6m% and year(invDate)=%6y%,1,0)) as %6c%_Trans_%6y%,
sum(iif(month(invdate)=%7m% and year(invDate)=%7y%,invtotal,0)) as %7c%_Amt_%7y%,
sum(iif(month(invdate)=%7m% and year(invDate)=%7y%,1,0)) as %7c%_Trans_%7y%,
sum(iif(month(invdate)=%8m% and year(invDate)=%8y%,invtotal,0)) as %8c%_Amt_%8y%,
sum(iif(month(invdate)=%8m% and year(invDate)=%8y%,1,0)) as %8c%_Trans_%8y%,
sum(iif(month(invdate)=%9m% and year(invDate)=%9y%,invtotal,0)) as %9c%_Amt_%9y%,
sum(iif(month(invdate)=%9m% and year(invDate)=%9y%,1,0)) as %9c%_Trans_%9y%,
sum(iif(month(invdate)=%10m% and year(invDate)=%10y%,invtotal,0)) as %10c%_Amt_%10y%,
sum(iif(month(invdate)=%10m% and year(invDate)=%10y%,1,0)) as %10c%_Trans_%10y%,
sum(iif(month(invdate)=%11m% and year(invDate)=%11y%,invtotal,0)) as %11c%_Amt_%11y%,
sum(iif(month(invdate)=%11m% and year(invDate)=%11y%,1,0)) as %11c%_Trans_%11y%,
sum(iif(month(invdate)=%12m% and year(invDate)=%12y%,invtotal,0)) as %12c%_Amt_%12y%,
sum(iif(month(invdate)=%12m% and year(invDate)=%12y%,1,0)) as %12c%_Trans_%12y%,
sum(iif(month(invdate)=%13m% and year(invDate)=%13y%,invtotal,0)) as %13c%_Amt_%13y%,
sum(iif(month(invdate)=%13m% and year(invDate)=%13y%,1,0)) as %13c%_Trans_%13y%,
Count(invtotal) as Total_Trans,
sum(invtotal) as Total_Amt
from ar where (year(invdate)=%startyear% and month(invdate)>=%startmonth%) or
(year(invdate)=%endyear% and month(invdate)<=%endmonth%)
group by bcomp, Company order by Total_Amt
ENDTEXT
cSqlSave := cSql
nStartMonth := 1
nStartYear := Year(Date()) - 1
@ 0,0 DCSAY 'Start Month' GET nStartMonth PICTURE '99' SAYSIZE 10 SAYRIGHTBOTTOM
@ 1,0 DCSAY 'Start Year' GET nStartYear PICTURE '9999' SAYSIZE 10 SAYRIGHTBOTTOM
@ 3,0 DCSAY 'SQL statement:' SAYSIZE 0
@ 4,0 DCMULTILINE cSql SIZE 150,30 FONT '11.Lucida Console'
@ 35,0 DCPUSHBUTTON CAPTION 'Create SQL' SIZE 10,1.2 ;
ACTION {||cSql := CreateSql(cSqlSave,nStartMonth,nStartYear), ;
lSqlConverted := .t., ;
DC_GetRefresh(GetList)}
@ DCGUI_ROW, DCGUI_COL + 10 DCPUSHBUTTON SIZE 10,1.2 CAPTION 'Execute SQL' ;
ACTION {||ExecuteSQL(cSql,@oSqlQuery,@oConnection)} WHEN {||lSqlConverted}
DCREAD GUI FIT TITLE 'Create SQL for Sales Resport'
RETURN cSQL
* ------------
PROC appsys ; RETURN
* ------------
STATIC FUNCTION CreateSql( cSql, nStartMonth, nStartYear )
LOCAL nMonth, nYear, i, aMonths, cReplace, cWith
aMonths := { ;
'Jan', ;
'Feb', ;
'Mar', ;
'Apr', ;
'May', ;
'Jun', ;
'Jul', ;
'Aug', ;
'Sep', ;
'Oct', ;
'Nov', ;
'Dec' }
nMonth := nStartMonth
nYear := nStartYear
cReplace := '%startyear%'
cWith := Alltrim(Str(nYear))
cSql := Strtran(cSql,cReplace,cWith)
cReplace := '%startmonth%'
cWith := Alltrim(Str(nMonth))
cSql := Strtran(cSql,cReplace,cWith)
FOR i := 1 TO 13
cReplace := '%' + Alltrim(Str(i)) + 'm%'
cWith := Alltrim(Str(nMonth))
cSql := Strtran(cSql,cReplace,cWith)
cReplace := '%' + Alltrim(Str(i)) + 'c%'
cWith := aMonths[nMonth]
cSql := Strtran(cSql,cReplace,cWith)
cReplace := '%' + Alltrim(Str(i)) + 'y%'
cWith := Alltrim(Str(nYear))
cSql := Strtran(cSql,cReplace,cWith)
IF i == 13
EXIT
ENDIF
nMonth++
IF nMonth > 12
nMonth := 1
nYear++
ENDIF
NEXT
cReplace := '%endyear%'
cWith := Alltrim(Str(nYear))
cSql := Strtran(cSql,cReplace,cWith)
cReplace := '%endmonth%'
cWith := Alltrim(Str(nMonth))
cSql := Strtran(cSql,cReplace,cWith)
RETURN cSql
* -----------
STATIC FUNCTION ExecuteSql( cSql, oSqlQuery, oConnection )
MemoWrit('SalesQuery.Sql',cSql)
RunShell('/CONN:ADS_15 /SQLFILE:C:\Darby\SalesQuery.Sql','C:\SqlQuery\SqlQuery.exe',.t.,.t.)
RETURN .t.