Page 1 of 1

The 3rd trimester

Posted: Thu May 30, 2019 5:31 am
by rdonnay
When I spoke at conferences, I often started out by saying
"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."
SqlQuery was inspired by work I was doing with Richard Covington and Bobby Drakos on their applications back in early December, 2018.

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.