Another new version of my SQLite and OLE tool for Windows.
I have replaced my multiple monitor configuration on my PC, it was 1*27 inch and 1*32 inch monitors, by a single 42 inch 4k monitor from LG. To be precise a LG 43UD79-B 108.98 cm (42.51 inch).
Like a dog with two tails, I do not know which one to wag first.
The only change I needed to do was to get a deeper desk so that I can set the screen about 15 cms further away.
I have implemented quite a few changes to the SQLite-OLE application. A new version is now available for download.
Hmm, I discovered that my solution using NM_CUSTOMDRAW does not quite do what I wanted in a MS Win 10 environment. The issue is the Win 10 themes.
The windows and controls that I use are created using CreateWindow or CreateWindowEx. In a MS Win 10 environment I found that I found that the colour schemes that I tried to impose was not always followed.
If you call the API SetWindowThemeWindow , with the parameters: HWND of the target window / control, followed by 2 empty strings e.g.
SetWindowThemeWindow ( lWnd , _TEXT(“”),_TEXT(“”) ) ;
then the window/control will not follow the Windows Themes. I found it necessary to use this API for:
- the main Window
- Listviews so that I can set the colour of everyother row
- the header of ListViews so that I can change the colour of the font and the background colour.
I recently spent 4 days trying to set the colour of the headers of a Listview in my Windows program SQLite-OLE. It was a painful experience partially because I program in plain C using the bare Win API and not using MFC (Microsoft Foundation Classes).
Hmm, well it was all my own fault. If you do not read the documentation carefully, what can one expect.
The solution was ultimately simple.
Here are a few tips if you want to set the background colour of a listview’s cells or its column headers.
- When a Listview is displayed on the screen a number of WM_NOTIFY messages are sent. We need to intercept a subset of these WM_NOTIFY, specifically those indicating a NM_CUSTOMDRAW operation.
- WM_NOTIFY / NM_CUSTOMDRAW messages concerning the data cells of the Listview are sent to the WndProc of the Listview’s PARENT, in my case the Window containing the Listview. These messages are also sent for some other controls, so be sure to check that the (LPNMHDR)lParam)->hwndFrom identifies the wanted Listview’s HWND.
- If you intercept these (cell) messages you can set the colour of the cell, the colour of the cells’ text, and the background color of the text, simply by providing a new RGB value to the data structure pointed at by the message’s lParam.
- WM_NOTIFY / NM_CUSTOMDRAW messages concerning the header of the Listview are sent to the WndProc of the Listview: In my case I had to subclass the Listview in order to create a WndProc for the Listview that I could intercept.
- If you intercept these (header) messages you can set the colour of the header cell, the colour of the text, and the background color of the text. BUT you must use functions such as: FillRect, SetTextColor and SetBkColor.
Once I got this sorted out, the code was easy, and I can now modify the display of a Listview as shown below.
SQLite-OLE has now been updated, still less that 2.5 Mb, to include the ability for a user to create their own GUI and to interact with it via callbacks.
The capabilities are part of the underlying Forth-like interpreter, I only had to find a ways to expose these capabilities via a “Basic” like command line interpreter.
Well, I found a way to expose some of these (> 1000 Forth-like definition) to the SQLite-OLE command line interpreter.
I have done (for me) a phenomenal amount of number crunching at work. One average I am processing about 1.2 million lines of data a week, nearly all of it provided as Excel or as CSV files. Fortunately the SQLite-OLE application lets me write script files, so that it is easy to rerun the processing steps.
I am a great fan of the automation of repeated activities.
However when it comes to presenting the data / results to other people it is usually necessary for me to use Excel.
Now, I have been programming in VBA for Excel, Word and PowerPoint for more than 10 years (especially in PowerPoint). And I have been fortunate that I have a work laptop so that I can
play program at home. But I have often wondered if I should install LibreOffice (https://www.libreoffice.org/).
I was (until today) wondering how easy it would be to use the OLE interface of SQLite-OLE to control LibreOffice. Well now I know.
I started looking at the possibility last Sunday, and today (Thursday) I managed to get the LibreOffice WriterDemo going.
For anyone who is interested, you can find it on the SQLITE-OLE Scripts page.
I have spent the last two weeks trying to get a 30 minute video ready for YouTube about the SQLite-OLE application that is available from this web-site.
Each time I tried to record the video demonstrating SQLite-OLE I did something a bit different, and the demo crashed. Now with less than 7 hours to the New Year, I have got the video done. I feel as though I did something in 2017!
Here is the video:
Oops, sorry. Happy New Year.
Well it is good to know that Murphy’s Law is still going.
Two days ago I updated the downloadable SQLite-OLE application, and today I found a bug.
I regularly (3 or 4 times a week) have to deal with Excel files that have ~ 100 columns and more than 800,000 lines. Rather than do the analysis in Excel with macros, I transfer everything to the SQLite-OLE application. I find that far easier and far faster.
Recently I needed to identify the rows that were 100% duplicated across all columns . Now in SQL it is easy to remove duplicates, but it is a bit of a pain to find full row duplicates.
I will give an example:
create table family ( Position text , Age INT) insert into family values ( "Father", 42 ) insert into family values ( "Mother", 21 ) insert into family values ( "Daughter", 4 ) insert into family values ( "Son", 11 ) insert into family values ( "Mother", 21 ) insert into family values ( "Mother", 21 ) insert into family values ( "Son", 11 )
Removing duplicates is easy, you will find lots of examples in the Web. One easy way is to create a new table based on the old table using the distinct keyword:
create table Family2 as select distinct * from family
But finding duplicates is a bit more complicated.
Eventually I found the following which is fairly neat:
create temp table Family2 as select rowid as Ind from family delete from Family2 where Ind in ( select rowid from family group by Position, Age having count (*) = 1 )
This leaves Family2 holding the rowids into Family of all the duplicated rows!
The only trouble is that when there are lots of columns in Family then the expression
delete from Family2 where Ind in ( select rowid from family group by Position, Age having count (*) = 1 )
has to be extended to include all of the column names separated by commas. Frankly when there are lots of columns, then creating this column list is tedious, prone to error and horrible if you need to apply this to lots of different tables.
So I have extended the SQLite-OLE application’s (shell) commands to include a command that creates a comma separated list of the column names in a user specified table.
DBcolumnList ( <string expression = DB alias name> , <string expression = Table name> )
So in this example I would have used
create temp table Family2 as select rowid as Ind from family" dim queryString = " delete from Family2 where Ind in ( select rowid from family group by " queryString = queryString & DBcolumnList ( "main" , "family") having count (*) = 1 ) " sql = queryString Message = "Table temp.Family2 holds the rowids of the duplicate lines in table main.Family"
Yes, yes, I know. The red text is not SQLite syntax, but belongs to the SQLite-OLE application.
So I admit it, a little bit of advertising for SQLite-OLE, but on the other hand it did find the duplicates, and one bug has been removed! So there is a new version. For details, and the download link, please look in the SQLite-OLE web-page.
I have updated the downloadable Windows application that provides a shell around an SQLITE database engine, and that has the ability to link (using OLE concepts) to compatible servicer applications such as Microsoft Excel, Word, PowerPoint, Outlook,…
For details, and the download link, please look in the SQLITE-OLE web-page.