SQL - a tester's best friend?

If you work with applications which has a database, good skills in SQL are required in order to be an efficiant tester (unless you sitting next to a friendly DBA).

With SQL (and other database operations) you can:

1. Find, change, create or delete test data
2. Do backup and restore of databases
3. Plus a lot of other useful things, for example you learn much about the application through the database/data model

If you cannot perform 1 and 2 by yourself you will encounter serious bottlenecks in your testing progress.

So, what you need are:

1. Learn basic SQL (select)
2. Get proper account settings in your test environment(s) database(s) - read only
3. Learn advanced SQL (join, update, insert, store procedures, backup/restore, temp tables etc)
4. Get proper account settings in your test environment(s) database(s) - SA or at least anything close

Beware, you must prove yourself worthy for the DBA to achieve no. 4

I went from stage 1 to 4 in a few months just by self-study articles and other people's SQL. I am not considering myself being a skilled DBA but for my testing purposes I am doing quite OK. Also in automation knowing SQL is priceless in order to get your automation test case dynamic and testdata independant. Further more you can build a lot of AUT specfic test tools which will improve your testing dramaticly.

Over and out...

ps.

Here is a list of my favourite database engines which I have worked with in my testing over the years (ranked from a user friendly perspective):

1. Microsoft SQL Server 2005
2. Oracle (don't remember version no)
3. DB2 (don't remember version no)

VBScript: Reading text files

Until now I have used the FileSystemObject (FSO) when reading text files in automation but this week I wanted to read a utf-8 encoded file which did not work using FSO. I used my standard approach dealing with this kind of problem:

1. Collect information about the issue (mainly using Google)
2. Analyze information and design solution
3. Implement solution

So I found another COM-object which can be used reading files which seems to manage most of the Char Sets I normally deal with: Adodb.Stream

The result (getFileContent.vbs):

Function getTextFileContent (strFileName, strCharSet)
Const adTypeBinary = 1 'not used
Const adTypeText = 2
'Set default CharSet
If strCharSet = "" Then strCharSet = "ASCII"
' *** CharSets ***
'    Windows-1252
'    Windows-1257
'    UTF-16
'    UTF-8
'    UTF-7
'    ASCII
'    X-ANSI
'   iso-8859-2
Set objStreamFile = CreateObject("Adodb.Stream")
With objStreamFile
    .CharSet = strCharSet
    .Type= adTypeText
    .Open
    .LoadFromFile strFileName
    getTextFileContent = .readText
    .Close
End With
Set objStreamFile = Nothing
End Function

QTP: Debug class for Dictionary

As I mentioned earlier, I use the Dictionary object a lot in my test framework. However it requires some extra efforts when debugging in QTP compared to ordinary variables since your Keys and Items are not visable by default in Debug viewer's Variables tab. You either manually add Keys to the Watch tab or add extra code where your Dictionary Keys and Items are stored in an array:

arrKeys = objDictionary.Keys
arrValues = objDictionary.Items

The last couple of days I have tried to learn a bit about Classes in VBScript and I noticed that a class public variables are available in QTP's Debug Variables tab in alphabetic order. That made me come up with this crazy idea to maybe replace the use of Dictionary object with a custom class instead...but Classes in VBScript seems a bit limited according to my requirements. However, for debugging purposes it could be nice to create a "Class copy object" of a Dictionary:

'Create a dictionary object and add some parameters
Set objDictionary = CreateObject("Scripting.Dictionary")
objDictionary.Add "A_Parameter","47"
objDictionary.Add "Z_Parameter",""
objDictionary.Add "C_Parameter","11"
'Create a class out of the dictionary and a new debug object
Execute CreateClass("DebugDictionary", objDictionary)
Set objDictionaryDebug = New DebugDictionary
'Display A_Parameter value
Msgbox objDictionaryDebug.C_Parameter
Set objDictionaryDebug = Nothing
Set objDictionary = Nothing


Function CreateClass (strClassName, objDictionary)
'* Create Class string from Dictionary object
'* By Stefan Thelenius 2008-04-02
'* Usage example: Execute CreateClass("MyClass", objDictionary)
'Get keys and values into arrays
arrKeys = objDictionary.Keys
arrValues = objDictionary.Items
For i = 0 to UBound(arrKeys)
    'Create Public variables
    strClassPublicVar = strClassPublicVar & "Public " & arrKeys(i) & vbLf
    'Set default values to variables
    strClassPublicInit = strClassPublicInit & arrKeys(i) & "=""" & arrValues(i) & """" & vbLf
Next
'Add number of Keys as Count Int
strClassPublicVar = strClassPublicVar & "Public Count" & vbLf
strClassPublicInit = strClassPublicInit & "Count=" & UBound(arrKeys)+1 & vbLf
'Set Class and initializer
strClassStart = "Class " & strClassName & vbLf
strClassEnd = "End Class"
strClassInitStart = "Private Sub Class_Initialize" & vbLf
strClassInitEnd = "End Sub" & vbLf
'Return class string
CreateClass = strClassStart & strClassPublicVar & strClassInitStart & strClassPublicInit & _
    strClassInitEnd & strClassEnd
End Function

Above code example is available for download here: http://abouttesting.fileave.com/DictionaryClass.vbs

When using QTP the debug view looks like this: