Testing table to json conversion - importing json in blob back to SQL

When comes to conversions, one of the best ways to verify it is converting back and see if you get the data as expected. Importing JSON to MSSQL from a local disk has become trivial, and MS documentation describe it well. But in practice there is a limited use of loading data from a local disk of the server. So I decided to upload it to a Azure blob container.

Loading from blob storage was a challenge, mainly due to different documents describing parts of it in different ways. Some have changed over time, and other requirements are for different purposes. Once you upload data to blob, this is all you need to do.

1. Create SAS (Shared Access Signature) from Azure
2. Create credential in SQL using SAS
3. Create external data source using credential
4. Import it using BULK and OPEN JSON to a new table

These are some points to remember - I spent long time to figure out some of them

1. Create a SAS from Azure - this is not the storage key, but a token created using the key. You can time limit tokens and put some other restrictions too, which are cool.
2. When copying SAS, omit the first question mark. It is documented everywhere but don't know why it is there in the first place - probably for easy construction of  REST urls...
3. You have to create a DATABASE SCOPED credential. If not, get a cryptic error message saying credential doesn't exist or you don't have permission. 
4. You might have to create a master key unless it was already done for something else.
5. Identity of the credential should be SHARED ACCESS SIGNATURE
6. You can use any (valid) name for the credential. Some documents ask to use the storage location for the name but it's for a different process, not to load from blob.
7. When looking for the file, it uses location in data source + path and file from load statement. So you can use the storage root in location and give the file as 'container/file' or use the container in the location itself and give only the file name when loading.

When put all of them together, if the json file in blob is called orders.json and you import it to a SQL table called orderJson, SQL looks like,

CREATE DATABASE SCOPED CREDENTIAL MyContainerKey

WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sv=xxxxxxxxxxxxxxxxxxx'

CREATE EXTERNAL DATA SOURCE blobdata
WITH ( TYPE = BLOB_STORAGE,

LOCATION = 'https://myblobstorage.blob.core.windows.net',
CREDENTIAL= MyContainerKey)

select * into orderJson
FROM OPENROWSET ( BULK 'mycontainer/orders.json', DATA_SOURCE='blobdata', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn) With (

[OrderID] [int],
[CustomerID] [nchar](5),
[EmployeeID] [int],
[OrderDate] varchar(30),
[RequiredDate] varchar(30),
[ShippedDate] varchar(30),
[ShipVia] [int],
[Freight] [money],
[ShipName] [nvarchar](40),
[ShipAddress] [nvarchar](60),
[ShipCity] [nvarchar](15),
[ShipRegion] [nvarchar](15),
[ShipPostalCode] [nvarchar](10),
[ShipCountry] [nvarchar](15)

) as orderdata

This should be relatively fast,  if it takes minutes, usually the reason is url for location is wrong.

Extractng Google sites tables to Excel

In past few months I had to use tables in Google sites to gather issues and other data, replacing local spread sheets already in place. Though this is far from ideal issue tracking systems, it is absolutely great at what it is promising; fast set-up; easy to share and collaborate; good and clean UI . They worked great.

When I had to extract data, there was no easy way - at least I didn't find any. This little VBScript routine saved the day. I had to take short cuts to save time (to avoid all the authentication issues, I first open the site in IE and then run this. It does not use the same instance but new instance already have access to the site. And gave a 4 second sleep to complete the page load instead of waiting for an event).

Note that ~ is used as the delimiter to allow commas in data and non-breakable spaces in the table are replaced by regular spaces.

set FSO = CreateObject("Scripting.FileSystemObject")
Set IE = WScript.CreateObject("InternetExplorer.Application", "IE_")
IE.Visible = True

  recIndex=1
  set outFile = FSO.CreateTextFile(".\outfile.csv")
  IE.Navigate "https://sites.google.com/site/<mysite/mypage>"
  wscript.sleep 4000
  tmpSTR = IE.Document.DocumentElement.innerhtml

  set objTable = IE.Document.GetElementByID("goog-ws-list-table")
  set colRow = objTable.GetElementsByTagName("tr")
  For each objRow in colRow
    set colFields = objRow.GetElementsByTagName("TD")
    If Not isnull(colFields) then recIndex = recIndex + 1
    outfile.Write recIndex
    For each objField in colFields
       outfile.Write "~ " & trim(replace(objField.InnerHTML,"&nbsp;",chr(32),1,-1)) 
    Next
    outFile.Writeline
  Next
outfile.Close
MsgBox "Done"

Setting current date and other dynamic values in SoapUI properties

One less known but very powerful and interesting features of SoapUI is the ability to use scripting inside property values. During property substitutions where ever they are used, in request or in side assertions, script is executed and the value is substituted. They take the general form of

${=(script)}

For example, if you want to generate a random number in a property, create a property in the TestCase, say Random and set the value to

${=(int)Math.random()*1000}
(Since Math.random gives a random value between 0 and 1, the value of this expression is going to be a random number between 1 and 1000)

In the request, if you specify

${#TestCase#Random}
It is going to be a random number between 1 and 1000.

Taking a more complex (and probably useful) example, following is going to generate the current date in form of DD-MM-YY.

${=Calendar.instance.get(Calendar.DATE)+"-"+(int)(Calendar.instance.get(Calendar.MONTH)+1)+"-"+Calendar.instance.get(Calendar.YEAR)}

Note that months are starting from 0, so you have to add one to the month.

Or, if the date time string is desirable,

${=Calendar.instance.getTime()}

Running mavenized JUnit Tests from command line

It has been a while again - I was doing lot of work but habit of blogging faded away. Fortunately I started writing things down, specially simple commands that are essential as a tester... Hopefully I will rapidly publish them.

I wasn't a fan of mavenizing java - at least at the start- but it has changed. The thing with these tools is the relative complexity for smaller scale projects. As a tester, I feel this more since we need small and quick JUnit or Java test programs often.

 If the project is mavenized, it is impossible to build the java path manually. You can export the classpath using dependency plugin, and set the classpath. Go to the project folder and give the command,

mvn dependency:build-classpath -Dmdep.outputFile=cp.txt

This will create the file cp.txt with all the dependencies. Now add the output folder of your project, this is usually,

%ProjectDir%\output\classes in a mavenized project. Don’t forget to add the semicolon!

Now open the text file from notepad, press Ctrl+A and then Ctrl+C to copy everything. Go to command prompt and type

Set CLASSPATH=

Without pressing Enter, right click the mouse and select paste. That will copy all the contents of the file in to the command. Now press enter and the classpath is set.

To verify, type

echo %CLASSPATH%

This should print all the contents you had in cp.txt. Now run the junit tests with command,

java org.junit.runner.JUnitCore MyTestClass


Alternatively, you can use the command line switch -classpath too but the command line is going to be very long!

java -classpath (content of cp.txt) org.junit.runner.JUnitCore MyTestClass

Why not make SoapUI step names more than descriptive?

I was continuing my journey with SoapUI. One of the challenges was to develop a test infrastructure with a low maintenance overhead. I am doing contracting work once it is over, in-house staff should be able to maintain it.

I was using Groovy script only when it is absolutely necessary, and setting up the custom content type was such task. Content type was read from a property, but I thought setting the property manually for every content type is not going to be reliable. It is easy to lose or change it without ever noticing it.

SoapUI suggest to use descriptive names for test steps. This is a very practical and useful suggestion. But can we go one step further? This is a technique I started using even in manual testing environments. You can include various data about the entity in different fields. The name can contain some useful data. If it is a database table, use values from a numeric column when testing a text column, so that you can verify them easily.

So, in this case I thought of including the content type in the name itself, and reading it from step name. There was an additional advantage of not having to modify the script in anyway when copying it to a new test. Just change the name. The following script read the name and use the string preceded by "-" to set the content type.

Re-usable Groovy scripts for SoapUI

For couple of days I could not login to blog - but with kind of frequency I was blogging in last year, it should not make any difference.

Though I stared to use SoapUI with some reluctance (mainly due to the fact that the project I am working is a REST system and I thought SoapUI's support for REST cannot be that great) I made good progress and became more and more comfortable with it.

One test I had in mind was verifying response headers. Using the Groovy script was the most straight forward method to do that. However, in all the examples I found, Groovy script was referring to the step by name to access the response data. This meant editing the script every time I used it in a different place. SoapUI pro has a scripting library but I was using the free version.

Then I thought the best way is to write the script to get data from the previous step, regardless where it is. In this way, I don't need to change the script every time I use, but just to place it write below the step where I need to test the headers. This was the script I came up with.

Adding Winpe boot to Windows 7 boot menu

I spent good part of today preparing a new test machine. One thing I always wanted was to have a native WinPE boot in the test machine. This was possible with vista but not with previous versions of Widows. For pre-Vista systems, I had to mess around with bootmanager/ntldr to get this to happen. I used a Windows 7 system today, but don't see any reason why it would not work with Vista.

In test environments, it is useful to have a WinPE boot option in windows boot menu. Booting to WinPE would allow you to look at the filesystem in case if the computer is not booting and more importantly, it will help easy refreshing of the system by restoring a fresh image. Having a PXE server in your network too, would equally be useful in terms of booting to preOS environments.

I had a WinPE build, created using the latest version of WAIK (Windows Automated Installation Kit) . If you don't have WinPE, search for AIK , which is a free download from Microsoft. It is not difficult to build WinPE using WAIK, and lot of help in the net!

This is all I had to do.

Copy boot.sdi file

If you install Windows 7 using the default settings, it would have created a small partition to hold boot files. This is usually 200MB, and no drive letter assigned. If you don't have this small partition, skip step fist and last step. All files will be in C: in that case.

• Start diskmanager and assign a drive letter to the boot partition
• Boot files are in the "\boot" folder in that partition. This is a hidden folder, but from a CMD window, you can go to the folder with 'CD \boot' command.
• Files in the boot are not hidden. It has most of the boot files necessary but not boot.sdi file. Copy this to the \boot folder from WinPE build's boot folder.
• Now we are done with the hidden partition, go to diskmanager and remove the driver letter for that partition. Otherwise it will continue to mount it all the time.

Copy the .Wim file

The boot image or WinPE, WinPE.wim file can be found in the WinPE built created from AIK. Copy the .wim file from the WinPE build to a convenient location in the system. You may have enough space in the boot partition (it is 200MB but there are no many files), but try to avoid it. You can create a new folder in drive C: and use it to hold your .wim as well as .vhd in time to come! Say your Windows partition is C: and the name of the .wim file is winpe.wim, and copied it to C:\Images.

Add an entry to BCD

Usually, the {ramdiskoptions} entry is not created when installing Windows. Just make sure if it is there or not with the following command.

bcdedit /enum {ramdiskoptions}

If it says "There are no matching objects or the store is empty", that indicates {ramdskoptions} entry has not been created. Create it with the following commands.

bcdedit /create {ramdiskoptions}

bcdedit /set {ramdiskoptions} ramdisksdidevice boot

bcdedit /set {ramdiskoptions} ramdisksdipath \boot\boot.sdi

Now, create the actual entry with following commands.

bcdedit /create /d "WinPE boot" /application osloader

After successfully creating the entry, this will display the {GUID} of the entry in format {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}. Use that value in the following commands.

bcdedit /set {GUID} device ramdisk=[C:]\Images\winpe.wim,{ramdiskoptions}

bcdedit /set {GUID} osdevice ramdisk=[C:]\Images\winpe.wim,{ramdiskoptions}

bcdedit /set {GUID} systemroot \Windows

bcdedit /set {GUID} detecthal Yes

bcdedit /set {GUID} WinPE Yes

Finally, add it to the current boot options list with

bcdedit /displayorder {GUID} /addlast

Reboot the system and select WInPE boot to boot to WinPE

Surprisingly, you can boot the same machine to 32 bit WinPE as well as 64bit (if the machine is 64bit.) just by using the appropriate WinPE.wim. They can be in the same menu, no other files need change.

Notes:

• In boot menu, if "Ramdisk Options" is displayed instead of the description of WinPE, remove the description of the {ramdiskoptions} with the following command.

bcdedit /deletevalue {ramdiskoptions} description

• If booting to winpe fails and says "A required device is not accessible", check if you have copied the boot.sdi file to the correct location.