Introduction

In this post, I am going explain how I used Golang to simplify a daunting task at work.

Before I start, I assume you have basic knowledge in SQL like me :-)

Lets take a look at this query

SELECT [circuit_id],[unique_id],[unique_number],[created_user],
[created_date],[last_edited_user],[last_edited_date],[object_id]
FROM [<DB_NAME>].[dbo].[<TABLE_NAME>]
WHERE
unique_id='1111'
OR unique_id='2934'
OR unique_id='3150'
OR unique_id='3640'
***** thousands of unique_id *****

Obviously, its a simple query, provided you have a few number unique IDs. But unfortunately, the “god of few numbers” never blesses me in such a situation. Its always the other way round.

The QA team for a project I happen to part of always demands reports like these mostly with thousands of IDs in an excel file.

How I did it

• I would copy the coulmn with the unique IDs in the file and paste them in an editor
• Put the right strings/condition “OR unique_id=‘id_number’” around every unique id (here id_number) by way of using alt, command and arrow keys combo to position the cursor at serveral places at the same time. and type the condition.

Quite better than doing it one by one I guess. But bear in mind, my finger has to on the appropriate arrow key till I reach the end of the file and not to mention that these IDs come with various lengths so consistency of the cursor position is not guaranteed.

Cause to change my ways

So, out of fraustration and the resolution to make golang part of me, I decided to automate this laborous task to a large extent. I wrote a tiny program in Go to help me out.

Here is the program structure

|- main.go
|
|- uniqueIds.txt
|
|- queryFile.sql ( created by our code )

Here is main.go with explanations below.

Lets see what is happening here.

Inside of createQueryFile() is where all the action happens. On line 20, we create a file called queryFile.sql using the Create() method provided by os package. We also check for errors whiles doing so just incase anything goes wrong.

On line 26, we open the queryFile.sql file in append and read only mode and on line 31, we write the the beginning part of our sql query stored in the pQueryString variable declared in line 13 to the file.

On line 34, we open the file uniqueIds.txt in read write mode but defer its closure since we will be reading from it and we don’t want any premature closure till we are done reading its contents. This file contains all the unique IDs pulled from the excel file. We store it in the IDsFile variable and read its contents starting from line 42.

Since we want to read the contents of the file line by line, next is to scan the uniqueIds.txt file. We make it “scanable” using the NewScanner() method provided by the bufio package by passing the IDsFile variable as our argument since it is I/O capable. We then use the Scan() method which returns a boolean, – true if content exists and false otherwise, we pick the first unique id with the if condition on line 44 and append it to the queryFile.sql in line 45 using the q variable declared on line 26. This is because q returns a pointer the queryFile.sql file – thus its “mermory location” where we can alter its contents.

We move on at line 48 with an infinite loop to append the remaining IDs in a formated way (here OR unique_id='XXXX') with the help of of Fprintln from the fmt package. Remember Scan() returns a bool. So the loop breaks when it reaches the end of the file.

In doing all this, we also cease the opportunity to get rid of all white spaces surrounding our unique IDs in the uniqueIds.txt file using TrimSpace() method from the strings package.

Just like all SQL querries, we terminate ours with line 56.

We then invoke our function in line 60 inside the main function so that it gets executed anytime we run the program with the famous go run main.go command.

With this program, all I now have to do is provide the uniqueIds.txt file with IDs each on a separate line and I get back to learining Go ASAP. ;-)

Conclusion

To end this post, I would like stress on the fact that Go is easy to learn and small enough to fit into the programmers mind. I had fun doing this as I didn’t expect it would be this simple with. I am now ever prepared for my QA team.

I hope you find this useful and give Golang a try in solving those daunting tasks you run away from.

Thanks for your time. And oh did I mention everything was done using the standard libraby? SOLID Go