Saturday 10th August 2013
SQL in VB6
I have noticed recently that the martin2k Software app I created to generate content for martin2k Software was getting slower on a daily basis. The main reason for this is that it uses my Configuration File ActiveX control, which is essentially based on VB6 array functionality. As you can imagine, constantly searching through an array of over 50,000 elements is going to cause delays.
Fortunately, within my job, I have been getting skilled up on working with databases, which included learning Structured Query Language (SQL) and have so far written a few scripts. I've been impressed by the speed in which SQL is able to manipulate large amounts of data and thought that this would be the ideal solution for martin2k Software, which now has over 10,000 apps.
So my research began to get see if I could use it with VB6. I looked into the price of Microsoft SQL Server 2008 R2 and unfortunately this is totally out of reach for me at around £500. Eventually I found that there was a free version of this software, which is called Microsoft SQL Server 2008 R2 Express. This can be downloaded here - Microsoft SQL Server 2008 R2 RTM - Express with Management Tools. It is possible to get version 2012, but I preferred to use something I was going to be familiar with. Apparently the 2008 R2 version is simpler to install than 2008, but if you don't know fully what you are doing, there can still be some confusing parts to it. I have found a YouTube video, which gives some help: How to Install the Microsoft SQL Server 2008 R2 Express Edition.
To start, I used Microsoft SQL Server Management Studio (SSMS) to create a database with one table to keep things simple (my app doesn't need more than one). Having designed my table, I then created anther database named 'test' with the exact same table. The reason for this is that once both tables are populated with data, I can experiment with the test one without worrying about causing any permanent damage.
The data for my app was stored in a text file in a similar format to an INI. I had to get details of the more than 10,000 apps from this file into the database. This process is called data migration. To do this, I wrote a one off subroutine in martin2k Software, which was activated by a CommandButton. To connect to SQL Server with VB6, I added a reference to the Microsoft ActiveX Data Objects 2.8 Library:
I then created two global variables - one for the connection and one for to work with a recordset:
The procedure that migrated the data resembled the following:
The above procedure basically works by firstly getting the number of apps from the loaded configuration file data into the variable 'a'. After this, a SQL insert statement is built up using entries from the configuration file and data from the PAD file related to this entry. I surrounded string values with apostrophes as required by SQL. Also, if there was the chance that a string might contain an apostrophe, which would cause an error in the statement, I added code to replace these with two apostrophes to 'escape' this character. At the end of each value, I added a comma (except for the last one where I added a closed bracket to end the statement.
LoadPAD loads the entire contents of a PAD file into a variable. This is then used as an argument in getXML, which returns a string value of the setting as specified in the first argument. Finally, the line 'MyConnObj.Execute str' executes the statement.
Some of the select statements I have used within martin2k Software are:
select COUNT(*) as E from Apps where FN = 'app filename'
This should return 1 if the FN or filename exists in the database once or 0 if it doesn't. I then use an update statement if the app exists or an insert statement to add the app to the database if it doesn't.
select top 1 Added from Apps order by Added desc
Returns 1 value indicating the most recently added app
select FN from Apps where IsLive = 1
Returns all live (i.e. on the website) filenames of apps from the table
The difference this has made to my app is phenomenal. It takes a few minutes to process 100 apps rather than over half an hour. The slowest part is downloading the PAD file from the web. I'll definitely be using SQL in future apps - perhaps even with more than one table!
Saturday 3rd August 2013
I have been asked by VBDepend to include some information about their product on my blog and this can be seen below:
VBDepend is a tool that simplifies managing VB6 code base. Architects and developers can analyze code structure, specify design rules, do effective code reviews and master evolution by comparing different versions of the code.