1.0.158 (This build has changes to SqlQuery,Prg, SqlQuery.Dll, DCLIPX.Dll ) 1. Added the ability to open ADS ADT files on a free table connection. ADT free files must not be bound to a data-dictionary. Both DBF and ADT files may exist in a data-dictionary. NOTE: ADT files should allow NULL values in fields for indexes and filters to work properly if they use the Empty() function. 2. Added EXACT filter option on browse filtered columns. 1.0.157 (This build has changes to SqlQuery.Prg, SqlQuery.Dll) 1. Altering a field name in "Table Properties" did not work. This is fixed. 2. Added new option to list tables that exist in data folder which are not listed in an ADS data dictionary. 3. Rebuilding ADS indexes in "Index Properties" would error if the connection was free tables instead of a dictionary. This is fixed. 4. Linked windows could fail if the linked field name was also a SQL verb. This is fixed. 5. Added the ability in "Table Properties" to reindex all tables. This uses SQL stored procedures for the fastest performance. 6. Added a "Select Index Tag" to the browse right-click menu. 1.0.156 (This build has changes to SqlQuery.Prg, SqlQuery.Dll) 1. Added new setting to [SYSTEM] group in SqlQuery.Ini. CacheColumns= is used to set the Cache Columns for browsing. The /CC: command line parameter will override this setting. 2. Fixed bug in the "Edit Record" routine that failed to save memo fields. 1.0.155 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL, ; DCLIP1.DLL, DCSTD.CH) 1. Fixed a tagging problem when altering fields in "table properties." 2. Added a new BROWSE SQL command to DCSTD.CH. This calls a new function named DC_BrowseSqlQuery() which is used by Xdot.exe and DC_Dot(). Use this in place of the BROWSE command to use the browsing system of SqlQuery. 1.0.154 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed a runtime error in table properties when altering fields. 2. Added a new "search" feature to the menu of the browse window. This will create a new cursor that will include all the records in which any field contains a match to an entered value. 3. The command line /PR: option would sometimes fail and exit before displaying the browse. This has been fixed. 1.0.153 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed problem with Editing a record not able to edit a logical field. 2. The browse window now displays the amount of time (in seconds) that it took to execute the last SQL query. 1.0.152 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed a runtime error when displaying Table Properties on a free connection. 2. Fixed multiple problems with PGDBE implementation (PostGreSql). 3. Fixed a runtime error when adding new fields. 4. Fixed a divide by zero runtime when printing less than 100 records. 5. Added AdsCacheOpenCursors(0) to insure that cursors are closed after closing browse windows (ADS only). 6. Fixed problem of download of update not working. 7. Removed Setting of Scope when filtering an ISAM work area. This led to undesirable results. Now only dbSetFilter() will be used if no SQL connection is available. 1.0.151 (This build has changes to SqlQuery.Prg, SqlQuery.Dll) 1. Added a "Test files in this Data-Dictionary" menu selection to the Tables tree when using an ADS Data-Dictionary. This will attempt to open each table in the Data-Dictionary and report as "Success" or "Open Error". Those tables which cannot be opened will be automatically tagged for removal if so desired. 1.0.150 (This build has changes to SqlQuery.Prg, Sqlquery.Dll, DCLIPX.DLL) 1. The Treeview of ADS tables (dictionary connection only) will now display The ICON_TABLE_LOCKED icon if the table is encrypted. 2. Right-click menu (Ads dictionary connection) now will allow a table to be encrypted or decrypted. 1.0.149 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed a bug in DC_ChDir() (dclipx.dll) that doesn't work with a UNC path. Connections to free tables starting with \\ would not work. 2. Improved the user interface of the "ADS Create Data-Dictionary" routine. 3. DC_DbGather() will now set a new UUID when adding a new record, if the UUID field exists and is empty. 1.0.148 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Table Properties (field list browse) now allows field list to be sorted in any order (name, position, type, length) 2. The PrintBrowse() method of DC_XbpBrowseFiltered() was given a few parameters to control the user experience during printing. ::PrintBrowse( [], [], [], ; [] ) is the title at the top of the report. (default .T.) will lock the browse during the report, otherwise the user will see each record being highlighted as it is being printed. (default .T.) will display DONE for 2 seconds after report is completed. (default .T.) will display a progress bar and Cancel button to abort the print job. 1.0.147 (This build has changes to SqlQuery.Prg, SqlQuery.Dll) 1. You can now execute linked browse windows, change the colors and then save the new colors by clicking on the SAVE button in the lower right corner. 1.0.146 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Added a new access/assign method to DC_DacSession() - :IsSqlExecute() This is a thread-safe Get-Set method that is used to determine if DC_DbGather() had to use SQL to update or insert a record (based on UUID). The flag can be tested by oDacSession:isSqlExecute. if .TRUE. is returned, then the SQL cursor should be reloaded into the browse to see the changed data. 2. Added ability to set the browse even/odd row colors. Right-click the browse for a menu to set the colors. Colors for linked browse windows can be set and restored to make it easier for users to know which is the parent window and which is the child window. 3. After linking 2 browse windows in a One-2-Many configuration, the SAVE button in the lower right corner of the browse is used to save the configuration to the SqlQuery scripts dictionary. 4. Changed the index format for SqlQuery.Cdx (used by FoxCdx) to make it compatible with ADSDBE. This allows the SqlQuery.Dbf to be viewed, filtered, and maintained by the SqlQuery browse/edit system rather than requiring special code. To view the Scripts catalog system click on File -> Browse SqlQuery.Dbf (Dictionary) File. Added UUID to SqlQuery.DBF. 5. Added record navigation to the edit record screen. 1.0.145 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, SqlStart.Prg, ; SqlQuery.Exe, DCLIPX.DLL) 1. Added ability to create UUID Index tags for ADS databases. 1.0.144 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Improved "Create New Connection" dialog. 2. Added "Edit Connection" and "Delete Connection" to the connection branch of the tree view. These will update SqlQuery.ini. 3. CTRL-End key now builds ALL cached columns. 1.0.143 (This build has changes to SqlQuery.Prg, SqlQuery.Dll) 1. Removed the first 2 frozen columns of the Data Browse. This caused all the export routines to fail. This may be permanent or just temporary until I can figure out a better fix. 1.0.142 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed a bug that sometimes would not show the right-click filter dialog in some applications. 2. Added a new iVar to the DC_XbpColumnFiltered() class. ::isColumnConfigEnabled (Default is FALSE) is used to enable the 2nd toolbar with the "Add/Move/Insert" column buttons. To enable this feature in your code do the following: DCBROWSECOL SUBCLASS 'DC_XbpColumnFiltered()' ; EVAL {||::isColumnConfigEnabled := .T.} 3. Some of the _DC*.PRG source files for DCLIPX.DLL are now included in SqlQuery20.Zip. These are the files that often get changed with SqlQuery updates. 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: - Make connection to in /sf or /sqlfile: - Load SQL from and execute /ss or /sqlscript: - Load and Execute SQL Script /st or /sqltable: - Execute SELECT * from /sq or /sqlstatement: - Execute /sq or /sql: - Execute /pr or /proc: - Run procedure /vw or /view: - Execute View /cs or /cmdscript: - Load and Execute COMMAND Script /it or /isamtable: - Open table and browse /is or /isamscript: - Load and Execute ISAM Script /ini: - Load . default is SqlQuery.Ini /cc: - Cache columns greater than 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 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: - Cache columns greater than /sqlfile: - Load SQL from and execute /sqlscript: - Load and Execute SQL Script /sqltable: - Execute SELECT * from /sqlstatement: - Execute /sql: - Execute /proc: - Run procedure /view: - Execute View /cmdscript: - Load and Execute COMMAND Script /isamtable: - Open table and browse /isamscript: - Load and Execute ISAM Script /ini: - Load . default is SqlQuery.Ini /conn: - *REQUIRED* Make connection to in ex 1: SqlQuery /conn:ADS_15 /sqlfile:arreport.sql /cc:40 ex 2: SqlQuery /conn:ADS_1 /isamtable: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 - . This must be an object of the DC_DacSession() class. IF 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 clause to the DCBROWSE command. This will insure that columns which have a position greater than 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.128 (This build has changes to DCLIPX.DLL) 1. Fixed a regression in DC_XbpColumnFiltered() that caused some apps to display their browse colors incorrectly. Still working on a solution that will work for all applications. Color change was caused by column tagging feature that can allow columns to be tagged for exporting or printing. Tagged columns are displayed in a different color. 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 - a pointer to a DC_XbpPushButtonXPConfig() object. This will create a sort button in the browse header. SQLSESSION - 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 - 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. Changes to SQLQUERY.PRG: 1. SQLQUERY.INI now allows a WindowScaleFactor= clause in the [SYSTEM] section. This will increase the size of windows by a factor of . 1.0.125 (This build has changes to SqlQuery.prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed problem with DC_AdsStatement() not setting the Locking Type correctly when DC_XbpColumnFiltered() builds a new SQL statement. It now defaults to the LOCKMODE= setting in the connection string. If that doesn't exist, it defaults to DC_AdsLockModeDefault(). 2. Improved the refreshing of the tree branches when adding, editing or deleting SQL scripts, Command scripts, ISAM scripts. 3. Fixed problem where linked browse windows failed to execute when loading from scripts. 1.0.124 (This build has changes to SqlQuery,prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed runtime error in Export to XML. 2. Increased number of total connections to 90. Ads (30), ODBC (30), PG (30) 3. Added support for Microsoft Access ODBC driver. It is not possible to acquire Schema information via a SQL statement so the connection now looks for files with names .Tables, .Views and .Columns. Look for example in Sample Programs. 4. ADS Table properties now has an option to update the comments in the browse window. This is a faster method of putting a comment on every table in the tree. 1.0.123 (This build has changes to SqlQuery,prg, SqlQuery.Dll) 1. Bug fixes. 2. Command scripts can now use the BROWSE command with extra options to control external windows. 3. A set of Sample Data is now available. Create a new folder named SampleData under your SqlQuery folder where SqlQuery.exe resides. Unzip SampleData.Zip into that new folder. It will create some sub directories. It has several examples of Views, Procedures, SQL Scripts, Command Scripts, and ISAM Scripts. Download it here: http://bb.donnay-software.com/SqlQuery/SampleData.zip To run SqlQuery with the Sample data: SqlQuery .\SampleData\SqlQuery.Ini 1.0.122 (This build has changes to SqlQuery.prg, SqlQuery.Dll, DCLIPX.DLL) 1. General improvements to interface. 2. Bug fixes 3. Added new features to command scripts. Can now execute SQL statements in a command script. Also included RMChart.Ocx and RMChart.dll so graphing can be implemented in command scripts. 1.0.121 (This build has changes to SqlQuery.prg, SqlQuery.Dll, DCLIPX.DLL) 1. Added ability to create, save, and restore ISAM browses. Field picker can now modify a field to an expression or can add a new column with a custom expression. Right click in the Selected Items browse. Example: Right(SSN,4) AS Last4 1.0.120 (This build has changes to SqlQuery.prg, SqlQuery.Dll) 1. Added ability to create and delete views. 1.0.119 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. Fixed problem with how items were displayed in the View branch. 2. Fixed error caused by DC_AdsStatement() when SQL statement did not create a cursor. 3. Show 'Please wait...' in Tree message window when executing a SQL statement. 4. SQL Procedures with SELECT statements now allow DC_XbpBrowseFiltered() to add column filters to the cursor in the browse. 1.0.118 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) 1. New tree branch structure: a. Views - VIEWS exist in Connection source data-dictionary. Can be created in connection source data manager or in SqlQuery. Can be executed in connection source data manager, in SqlQuery or in any ADS/ODBC client program. b. Procedures - PROCEDURES exist in Connection source data-dictionary. Can be created in connection source data manager or in SqlQuery. Can be executed in connection source data manager, in SqlQuery or in any ADS.ODBC client program. c. SQL Scripts - SQL SCRIPTS exist in SqlQuery.Dbf data-dictionary. Can be created and executed only in SqlQuery or in Xbase++ programs that use SqlQuery.dll. d. Command Scripts - COMMAND SCRIPTS exist in Sqluery.Dbf data-dictionary. Can be created and executed only in Sqlquery or in Xbase++ programs that use SqlQuery.dll. 2. Fixed a runtime error in DC_XbpColumnFiltered() when clearing a logical field in the right mouse button screen. 3. Fixed a bug in DC_XbpColumnFiltered() where it didn't set the SQL Query correctly when using a logical field in the right mouse button screen. 1.0.117 (This build has changes to SqlQuery.Prg, SqlQuery.Dll) 1. Added new entry to SqlQuery.ini [SYSTEM] : Administrator=Yes This is equivalent to SET SQLQUERY_ISADMIN=Y and allows commands and procedures to be created and executed. 2. When editing a record, changes will not be allowed if the cursor is a static cursor. 1.0.116 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, SqlQuery.Cdx) 1. Added new features to File Menu: a. Edit SqlQuery.ini file. b. Restart SqlQuery.Exe c. Browse SqlQuery.Dbf Dictionary 2. Commands in Command window can now be saved to SqlQuery.Dbf Dictionary. 3. New tree view branch - Scripts. This is for command scripts. NOTE: Must delete SQLQUERY.CDX file to rebuild indexes. 1.0.115 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL, SqlQuery.Exe, DCLIP1.DLL, SqlQuery.CH) 1. Fixed some bugs when creating a new connection. 2. Fixed error caused by closing Tree Window. Now cannot close window. It is recommended that you add the following to your SQLQUERY.INI file: [SYSTEM] BrowseWindowsOnDesktop=Yes 3. Added a Command window that is available only with ADMIN privileges. Set SQLQUERY_ISADMIN=Y This uses the preprocessor and macro compiler to allow you to run your own commands. This is handy for database operations that are not possible or impractical in SQL. Commands are entered into COMMANDS.CH. 1.0.114 (This build has changes to SqlQuery.Prg, SqlQuery.Dll, SqlQuery.Exe, DCLIPX.DLL) 1. Fixed a bug in the ADS SQL implementation where a static cursor is created but then an error is created when adding an additional WHERE clause and re-running the query. Also, improved the error dialog window. 2. Added new functions DC_AdsDebugFile(), DC_AdsDebugMode() to help debug ADS SQL issues. Writes to a log file. 1.0.113 (this build has changes to SqlQuery.Prg, SqlQuery.dll) 1. Improved record editing screen and stored procedures support. 2. Can now set Session Properties for an ODBC connection. 1.0.112 (this build has changes to SqlQuery.Prg, SqlQuery.dll, DCLIPX.DLL) 1. Added support for Stored Procedures. Right-Click menu provides for stored procedures to be created, deleted and executed. 1.0.111 (this build has changes to SqlQuery.Prg, SqlQuery.dll) 1. Fixed a bug when running SQL queries that create a STATIC cursor with ADS. A runtime error would occur if a file named QUERY.DBF exists in the same folder as SqlQuery.Exe. 1.0.110 (this build has changes to SqlQuery.Prg, SqlQuery.dll, DCLIPX.DLL) 1. Exporting now includes option to Export to a JSON file. This writes out JSON (JavaScript Object Notation) code. A subset of data can be exported out this way because only the columns that are in the browse will be exported and only the rows that are viewed or tagged will be exported as data. 2. Exporting now includes option to Export to a HTML file. This writes out HTML code for view in a web browser. A subset of data can be exported out this way because only the columns that are in the browse will be exported and only the rows that are viewed or tagged will be exported as data. 3. Added ability to create a new Table using SQL script. 4. Now can export tables to Database, Excel, CSV, XML, SQL, Json, HTML without the need to browse first. Select a table in the tree, click on Export then choose the export type. All fields and all records will be exported. 1.0.109 (this build has changes to SqlQuery.Prg, SqlQuery.dll, DCLIPX.DLL) 1. Fixed a regression bug in build 108 that caused expanding the treeview to be very slow when building the index data for the entire tree. Now the index data is not built until a table name is clicked in the tree. 2. Exporting now includes option to Export to a SQL file. This writes out SQL which will create the table and insert all the data. A subset of data can be exported out this way because only the columns that are in the browse will be exported and only the rows that are viewed or tagged will be exported as data. 3. Improved speed of loading Data Dictionary information into the tree view. Field lists and Index lists are now loaded and displayed only on demand. 1.0.108 (this build has changes to SqlQuery.Prg, SqlQuery.dll) 1. Any statement can be entered in the SQL Query Window, other than just SELECT statements if the :lIsAdmin flag is set. This can be done in your code if you are using the SqlQuery() class or with an environment variable: SET SQLQUERY_ISADMIN=Y 2. Index files are now displayed in Properties window of a Table on an ODBC connection. 3. Index files can now be created from Properties window of a Table on an ODBC connection. 4. Exporting now includes option to Export to XML file. A subset of data can be exported out this way because only the columns that are in the browse will be exported and only the rows that are viewed or tagged will be exported as data. 1.0.107 (this build has changes to SqlQuery.Prg, SqlQuery.dll, DCLIPX.DLL) 1. Added the ability to edit a record. Press ENTER when in a data browse. 2. Added ability to add a new index tag to an ADS database. Select table in tree, click on Properties - Select Index Tab - Add New Index. This is accomplished via a SQL script so it is not possible to corrupt the index file even if info is entered incorrectly. 3. Added ability to drop an index tag from an ADS database. Select table in tree, click on Properties - Select Index Tab - Delete Selected Index. These features are accomplished via a SQL script so it is not possible to corrupt the data file or crash the program even if info is entered incorrectly. 4. Added ability to add or drop fields from a database. Select table in tree, click on Properties - Select Fields Tab. Tag fields with the SPACE BAR then click on "Delete TAGGED fields". When adding fields, any field name that ends in a digit can be replicated nn times. example: PHONE01, PHONE02, ... PHONEnn Also added ability to alter the name and type of fields. These features are accomplished via a SQL script so it is not possible to corrupt the data file or crash the program even if info is entered incorrectly. 5. Fixed a bug in DC_AdsStatement() that fails on some complicated SELECT statements that may create a STATIC cursor (such as JOINS). To prevent STATIC cursor errors, make sure that your connection also includes a pointer to the directory with the tables. Ex. ServerFree=c:\myfiles 1.0.106 (this build has changes to SqlQuery.Prg, SqlQuery.dll, AdsMgmt.exe, adsmg.dll, dccolors.ch) 1. Added "Advantage Utilities" to the UTIL menu. This runs the Ads Management Console (by Alaska Software) to monitor Advantage Server connections, table usage, users, etc. 2. Fixed a regression bug that caused an error when connecting to an ODBC source. 3. Added the ability to import a DBF file into the ODBC database when using an ODBC connection. 4. Added the ability to edit a record. Press ENTER when in a data browse. 1.0.105 (this build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) ------- 1. Fixed a bug with the Export routines missing the last record in the browse. 2. The ODBCDBE and PGDBE Dbes are no longer loaded at startup. They are only loaded when a connection is made. 3. Fixed a bug in DC_Array2Excel() that fails to correctly show the progress. 1.0.104 (this build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) ------- 1. Fixed bugs. 2. Disconnecting will now close any browse windows associated with that connection. 3. When connecting to an ADS dictionary via ODBC, the table comment now appears in the tree view. 4. Data Browse window background color can now be set for each connection. Example: [ODBC_4] Name=Advantage ODBC (BhsTest) Driver={Advantage StreamlineSQL ODBC} Database=C:\bhs\bhsmed.add BrowseWindowBGColor={220,220,220} // light gray 5. Colors can now be entered into the SqlQuery.ini file as a color name that is defined in DCCOLORS.CH. Click on Help -> Browse Color Definitions to see the list of available colors. Example: BrowseWindowBGColor=DC_LIGHTGOLDENROD 1.0.103 (this build has changes to SqlQuery.Prg, SqlQuery.Dll) ------- 1. Fixed bugs. 2. Added ability to recreate ADS indexes. This is important if connecting to .CDX files that were created with DBFCDX or FOXCDX. Those indexes do not perform well under ADS. Recreating them with ADS will improve performance and they will still work well when opening with DBFCDX or FOXCDX. To rebuild indexes, right click for menu on a Table in the tree view, then click on "Properties". All indexes will be shown. Click on 'Rebuild this Index File'. This will use a SQL script to rebuild the indexes. You must have a user id of "ADSSYS" in your SqlQuery.Ini file to enable this feature. This will work with an ADSDBE connection or an ODBCDBE connection that uses the ADS ODBC driver. 1.0.102 (this build has changes to SqlQuery.Prg, SqlQuery.Dll, DCLIPX.DLL) ------- 1. Fixed bugs. 2. Treeview now shows all connections on startup but will not connect to the server until the item is double-clicked or "Connect" is selected from the right-click menu. Items that ARE NOT connected will have a GRAY icon. Items that ARE connected will have a GREEN icon. 3. A connection can be disconnected by clicking "Disconnect" from the right-click menu. 4. On ADS connections, One-to-many links between windows can now allow child windows to be ISAM browses instead of SQL cursor browses. This improves performance when the child database is very large. If the child window is ISAM it will find a compatible index and set the scope on the child window. If a compatible index cannot be found, the browse will be rebuilt using a SQL statement and a WHERE clause to display the child records. 5. Right-clicking a column header will allow a record to be located by clicking on the LOCATE button. This will find the first record that matches the user entry. If the browse is ISAM, it will seek for the record by first finding a compatible index (ADS only). If it is SQL or a compatible index can not be found, then the Xbase++ dbLocate() will be used instead. 6. Left-clicking a column header will sort the browse by using the ORDER BY clause of the SQL statement if the browse window is SQL. If the browse window is ISAM, left-clicking will try to find a compatible index (ADS only) for that column and will select the index tag and refresh the browse. 7. Field lists can now be picked for both SQL browses and ISAM browses. Those field lists will be saved in Linked views and restored when executing the view. 8. Windows can be linked together regardless of whether the parent or child windows are SQL or ISAM or mixed. You can choose the technique that gives the best performance. 1.0.101 ------- 1. Fixed bugs. 2. Added Automatic check for update on server. 3. Adding new views and connections now refreshes Tree View. 4. Can now delete a view. 5. Can now reindex a tag when using ADS. 6. The browse window real estate usage has been improved. Use the splitbar to resize the "Cell contents window". 7. Improvements to the field picker for creating SQL SELECT statements. 8. When linking 2 windows, if the child database is ADS and is over 1,000,000 records, an attempt will be made to search for an index matching the key field and then set a scope rather than rebuild the SELECT statement with a WHERE clause. This improves one-to-many performance dramatically. If the scope cannot be set, then it will fall back to the default method of adding to the WHERE clause. 9. A parent window can now be linked to up to 2 child windows and saved to the Views. 10. Data Windows can be optionally created on the AppDeskTop(). The default is the drawingArea of the main window. When using multiple monitors and many data windows it is recommended to have them on the AppDeskTop(). Use a setting in SqlQuery.Ini: [SYSTEM] BrowseWindowsOnDesktop=Yes 11. Can now configure colors for Data Browse windows in SqlQuery.ini using RGB or HTML hex (example: #FFCA3D) [SYSTEM] TreeFGColor={10,10,10} TreeBGColor={192,192,192} BrowseFGColorOdd={0,0,0} BrowseBGColorOdd={230,123,133} BrowseFGColorEven={0,0,0} BrowseBGColorEven={110,150,193} 1.0.100 Initial Build ---------------------- SqlQuery.Exe is a tool written entirely in Xbase++ using eXpress++. 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 treeview 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(). 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. The SqlQuery.exe and SqlQuery.dll included in this distribution were compiled in Xbase++ 2.0. If you want an Xbase++ 1.9 version, then just run PBUILD SqlQuery.Xpj with your 1.9 installation. The PostGreSQL connections will not work with Xbase++ 1.9. You will also need to rename DCLIPX.DLL.19 to DCLIPX.DLL. You can also download the 1.9 here: http://bb.donnay-software.com/sqlquery/sqlquery19.zip 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. It also does One-To-Many relations between windows. No special indexes are required. Everything is done using SQL statements to send messages between windows. When an item is double-clicked in a parent window, a message is sent to the child window (even though it runs in another thread) to re-execute the SQL for that window with and addition to the WHERE clause. This addition passes the value in the key field of the parent window to the key field of the child window. This works with even with no indexes and it works with any kind of connection (ODBC,ADS,PG). Setting a link between 2 windows is simple: 1. Click on the LINK button of the parent window. A sub-window will appear with a browse of the parent fields. 2. Click on any child window. The fields of the child window will appear in the sub-window. 3. Pair the parent field name to the child field name by clicking in both browses. 4. Click OK. Test the link by double-clicking parent items to see the child window get refreshed with related items. This One-To-Many can be saved to the VIEW dictionary by simply clicking on LINK again and selecting Save to Views. The select statements, field relations and the window coordinates will be saved. 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: [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')}