martin2k
ForumsVB6GamesSoftwareDirectoryHTMLArticlesBlogContact

August 2013

Blog

2017

Current Month

July

March

February

2014

August

May

January

2013

November

October

September

August

July

June

April

March

February

January

2012

December

October

September

August

2010

June

2009

August

May

February

2008

November

October

July

January

2007

December

November

October

Subscribe Subscribe

 

Saturday 10th August 2013

[Top]

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.

Microsoft SQL Server Management Studio
Microsoft SQL Server Management Studio

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:

Microsoft ActiveX Data Objects 2.8 Library
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:

Dim MyConnObj As New ADODB.Connection
Dim MyRecSet As New ADODB.Recordset

The procedure that migrated the data resembled the following:

Private Sub Command1_Click()
Dim a As Integer, s As String, i As Integer, str As String, FN As String, pad As String

a = CF.GetValue("Apps")

For i = 1 To a
    FN = CF.GetValue("FN" & i)

    str = "insert into Apps (FN, Cat, Name, IsLive, Added, Author, Version, IconURL, Desc80) values ("
    str = str & "'" & Replace(FN, "'", "''") & "',"
    str = str & "'" & CF.GetValue("Cat" & i) & "',"
    str = str & "'" & Replace(CF.GetValue("Name" & i), "'", "''") & "',"
    str = str & CF.GetValue("Live" & i) & ","
    str = str & CF.GetValue("Added" & i) & ","

    pad = LoadPAD(FN & ".xml")

    str = str & "'" & Replace(getXML("Company_Name", pad), "'", "''") & "',"
    str = str & "'" & Replace(getXML("Program_Version", pad), "'", "''") & "',"
    str = str & "'" & Replace(getXML("Application_Icon_URL", pad), "'", "''") & "',"
    str = str & "'" & Replace(getXML("Char_Desc_80", pad), "'", "''") & "')"

    MyConnObj.Execute str
Next i
End Sub

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

[Top]

VBDepend

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.

VBDepend comes with 60 code metrics.  Some of them are related to your code organization (the number of classes, the number of methods declared in a class...),
some of them are related to code quality (complexity, percentage of comments, number of parameters, cohesion of classes, stability of Projects...),
some of them are related to the structure of code (which types are the most used, depth of inheritance...).

VBDepend supports the Code Query Language (CQL) (specification available here) for maximum flexibility.
Basically, VBDepend considers your code as a database and you can write some CQL statements to query and check some assertions on this database.
As a consequence, CQL is similar to SQL and supports the SELECT TOP FROM WHERE ORDER BY pattern.
For example the following CQL query matches all public methods that have more than 30 lines of code:

SELECT METHODS  WHERE NbLinesOfCode >  30  AND IsPublic


VBDepend could be downloaded from http://www.vbdepend.com/VBDependDownload.aspx and purchased from http://www.vbdepend.com/Purchase.aspx.

 


blog comments powered by Disqus
Martin Allen 1999 - 2013.  Last updated Saturday 10 August 2013 12:33:07 AM.
Powered by Blog2k