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.

NM_CUSTOMDRAW and Listviews in Plain C

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.



Creating your own GUI with SQLite-OLE

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.

Microsoft Office and LibreOffice

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.

Happy New Year and SQLite-OLE

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.

Find duplicate lines in SQLite

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.

By jingo, now I’m German and British

Well, today has been a day that I expect most people do not experience.

Today I was given a piece of paper that says I am a Germany citizen. Along with about 60 people, I was at a small ceremony where we were given our citizenship certificates.

I know that I was not the only British citizen there. Whilst going through the relatively simple process I was told by the civil servant that there was a significant increase in the number of British people applying for German nationality. The civil servant said it was nearly all due to Brexit. I know that that was my reason for applying.

No, I will not be handing in my British passport, although I do believe that Brexit is fundamentally a stupid jingoistic thing to do.

I was born in a little village, St. Florence, in the south west corner of Wales, in the county of Pembrokeshire. Now, Pembrokeshire is a county that is split in to two. The north/east part is Welsh, whilst the south/west corner is called Little England beyond Wales.

There is a dividing line called the Landsker that runs though Pembrokeshire, apparently Landsker is a old Anglo-Saxon word that has a meaning related to land divisions.

Way back in history, when England’s kings and Queen Elizabeth I  were trying to impose their rule on Ireland, this south west corner of Pembrokeshire was used as a military base from which the English could nip over to Ireland. This part of Pembrokeshire had to be a safe base for the English, so ultimately the welshness of the area was, over many centuries, subdued. This got the south west corner of Pembrokeshire the name  Little England beyond Wales .

When I started primary school in 1959, there was no Welsh language teaching in the school. The school was built in the late 1800’s so it had things such as honour rolls of the village people who had died in the Great War of 1914-1918. These rolls, and all of the other paraphernalia and regalia, were in English, although the names were clearly Welsh: Jones, Lewis, Thomas, Williams,… But one should note that these are common throughout the UK, but Welsh names such as Blethyn, Prytherch, Iwan, Rhys did not appear on the rolls.

Indeed, the only time I heard Welsh in St. Florence was if my mum was talking to Mrs. Lewis the back.  One trace of welshness that we did have was a tendency to identify people by their job (hence the milkman Mr. Rowe was Rowe the milk) or by location (hence Mrs. Lewis the back who lived at the back of the village). Apart from Mrs. Lewis and my mum I do not recall ever hearing Welsh in the village.

The other time that I regularly encountered Welsh was when we visited my maternal grandmother who lived on the Welsh side of the Landsker. Granny Meaker could speak English but avoided it when possible especially when we came to visit. My mum and her mum would talk away in Welsh whilst my father and my brother/sisters and I would drink tea, eat Welsh cakes, and wait to go home. (My father was English, born in Birmingham.)

I believe that Granny Meaker hated my father on principle, and that she avoided English so she could avoid speaking to him. By all accounts my father did not want to speak to her either. Aren’t families fun!

When I was 11 and moved-up to the big school in Tenby there were one or two kids in the class who spoke Welsh, but they were bussed in from the
north, and were invariable farm children.

It was not until I went to university in Bristol that I was really confronted with an identity issue. To my English mates I was Welsh, but to the Welsh ones I was English.

Since then I have been critically aware that my nationality falls between two stools. I love the heritage of England (and the National Trust) and yet I feel the pain felt by many (the non-English British people and the Irish people) created by the English in the past.

Just for fun, check out Jane Austen and the speech made by the Baronet Sir Walter Elliot in the story “Persuasion”.

A widow Mrs Smith lodging in Westgate Buildings! A poor widow barely able to live, between thirty and forty; a mere Mrs Smith, an every-day Mrs Smith, of all people and all names in the world, to be the chosen friend of Miss Anne Elliot, and to be preferred by her to her own family connections among the nobility of England and Ireland! Mrs Smith! Such a name!

I find it strange and sad, and yet so very believable, that Scotland and Wales are not mentioned. However, this little speech strikes a chord with me. In a way it reminds me how my self identity is of little consequence.

I can stand-up and fight for Wales, or I can stand-up and fight for England. It is my choice.

But I find that I abhor divides. I consider reglious belief to be, 99%, depending on the self-identity that once assumes. Born in the UK, then probably protestant. Born in the Middle East, then possibly Islamic.

This insight suggests most strongly to me that religion and nationality are just other names for jingoism, or tribalism.

It is probably no surprise that I do not follow any football team, or rugby team, or indeed any other team. I disagree with the idea of us and them.
I do not mind a game, I do not mind people being in teams, but I do object to the tribalistic attitude that so often comes from teams.

As a general rule I believe that together is better than apart.
Now, to bring this blog to a close:

  • I saw the EU as a step toward together and away from apart.
  • I see Brexit as just another team playing at jingoism and the old mine is bigger than yours.

One of the drivers for me taking on Germany nationality was to support together, and one of the reasons why I will not give up my British nationality is I will not promote apart.

PSTN and Switching-Routing

I have noticed over the last 20 years a tendency by some people to consider PSTN to mean the Public Telephone Switched Network. It should come as no surprise to people who know me, that I take a different position.

Let us take a step back  to the first 100 hundred years of the telephone. Independent of who has the right to be called the inventor of the telephone, Alexander Graham Bell certainly brought the telephone to the public.

Each call across those telephone networks would consist of a set-up phase, a connected phase and a clear-down phase. Typically, at least in the days of the manual operator, the calling party would identify the called party by one of two means:

  1. A description of the end subscriber, such as Mr. Sherlock Holmes, 221B Baker Street, London
  2. The identity of the end point on the telephone network, such as London, Whitehall, 1212 i.e. the telephone exchange and the “local number” on that exchange. (Whitehall 1212 was, from about 1934 till the 1960s, the  London Metropolitan Police station known as Scotland Yard.)

In the first case the operator would do a check in the telephone directory to convert the subscriber identity to an end point identity. Once the end point identity was known the operator would select a transport path that would extend the call in the appropriate direction. This decision, based on the context of the call (target destination ), took in to account the resources available to the operator, and the decision was and still is called routing.

Every telephone call over the PSTN is routed.  Sorry, but I see no room for discussion on this.

Once the call set-up has reached the called party, and the called party answers the call, then the call set-up phase ends and the call connection phase starts. In the call connection phase there is an identifiable semi-permanent route (often called a path) between the caller and called party. That route/path took the form of a galvanic connection between the parties (in the analogue telephone system).

In the implementation of the telephone service that started in the mid 1980s the galvanic connection was replaced by a sequence of logical transformations on digital signals in time and space

It was common, even in the analogue telephone days, to refer to the infrastructure that handled the routing decision and that interconnected the transport paths as a telephone switch.

Now we must step back to the 1970’s and 1980’s. In the early 1970’s the basics of the Internetworking Protocol were established by Bob Kahn and Vincent Cerf. The resulting telecommunications networks, based on the Internetworking Protocol, led to the establishment of the Internet as we know it today:

a set of interconnected infrastructure that has a shared end-point addressing scheme and a shared understanding of the structure and interpretation of data packets that the infrastructure transports.

Now we enter the period in which the fledgling Internet caught the attention of financially orientated concerns.
One of the topics that was turned in to an issue, was whether companies who provided public access to the Internet were subject to the regulations normally applicable to providers of telecommunications services.

One argument against the applicability of the telecommunications regulations to the Internet hinged on questionable logic:

  • The PSTN is subject to regulations.
  • PSTN “stands” for the Public Switched Telephone Network.
  • The Internet is routed, and has nothing to do with “switched”.
  • The Internet should therefore not be subject to telecommunications regulations.

I think that an appropriate phrase to some up this logic, and its conclusion, is horse feathers.

Hmmm, this was  a wonderful application of the now common  alternative facts, and yes, it was spearheaded by companies based in the USA.

It was of course, totally ignored by such non-regulation preferring companies, that the Internet in the last 20 or so years makes extensive (i.e. massive) use of  switching in the MPLS cores and in the Ethernet access networks.

I have said it before, and I will say it again.

Wishy washy words lead to wishy washy thinking”.

If you want to muddy the waters of your opposition, start using wishy washy words, obfuscate the issue.

To quote that author whom I have already referred to:

Terry Pratchett – ‘A lie can run round the world before the truth has got its boots on.’

If you really want to understand something, then consider this: the tools of comprehension are words and sentences. If you use the wrong tools do not be surprised if the results are sub-optimal.