Tuesday, April 28, 2015

Merging Access MDB files

Recently I came across a task that seemed trivial, but turned out into an adventure on a second look. I needed to merge 33 MDB files having one or two tables with same structure each. I was disappointed that Access has no standard means to do so I also could not use Excel as the data could have been spoiled by its auto-type function (will they ever fix that?!).

So I started creating a program that would do the task. The fact that tables can be named differently (thanks for a silly decision of a person who created them in the first place) I faced unexpected complexity. The following query can be used to get the list of tables in Access MDB:

SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0

However it does not work due to permission schema if executed by a program. Running it under administrator does not work either (famous Microsoft integration of solutions is to be blamed). SO I needed to grant the required access with another query before above query starts working:

GRANT SELECT ON TABLE MSysObjects TO PUBLIC

Note: Just hold on for a second and think how stupid it seems. I can't do a query but I can run another one to grant the required access and voila it does the magic. So what is the point of restriction in the first place? Ah?...

This is the code that does the job:

var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";" +
@"Jet OLEDB:System Database=C:\Documents and Settings\\Application Data\\Microsoft\Access\System.MDW");

var cmdToExecute = new OleDbCommand();
cmdToExecute.Connection = conn;
cmdToExecute.CommandType = System.Data.CommandType.Text;
cmdToExecute.CommandText = "GRANT SELECT ON TABLE MSysObjects TO PUBLIC";

conn.Open();

// set permissions to read system object to get the list of tables
try
{
cmdToExecute.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
return;
}

Note: You must reference a schema file otherwise you will an exception during query execution.

And the second step - getting the list of tables:

cmdToExecute.CommandText = "SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0";
try
{
var reader = cmdToExecute.ExecuteReader();
while(reader.Read())
{                        
tables.Add(reader[0].ToString());
}
reader.Close();
}
catch()...

Then for each table selected I ran the query to read the content and added it to a StringBuilder object:


cmdToExecute.CommandText = "SELECT * FROM " + table;
reader = cmdToExecute.ExecuteReader();
while(reader.Read())
{
var sb = new StringBuilder();

for(int i=0; i
{
sb.Append(reader.GetValue(i).ToString());
sb.Append(delim);
}
sb.Append(file);
sb.Append(delim);
sb.Append(table);
sb.Append("\n");
}


This is pretty much it. The only other thing is that I faced some corruption within the files scanned and needed to use Access to fix the database before it starts working for those.

Feel free to copy and modify the code as you like. Hope it helps.

Full solution code is provided below:

static void Main(string[] args)
{
// read folder
var files = Directory.GetFiles(inDir, "*.mdb", SearchOption.TopDirectoryOnly);

// prepare to writing results into a file

using (var stream = new StreamWriter(outDir + @"\alldata.txt"))
{
// write the header
foreach(var header in headers)
{
stream.Write(header + delim);
}
stream.Write("\n");

// for each MDB file, read it and save content into CSV
foreach(var file in files)
{
Console.WriteLine(file);

var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";" +
@"Jet OLEDB:System Database=C:\Documents and Settings\\Application Data\\Microsoft\Access\System.MDW");

var cmdToExecute = new OleDbCommand();
cmdToExecute.Connection = conn;
cmdToExecute.CommandType = System.Data.CommandType.Text;
cmdToExecute.CommandText = "GRANT SELECT ON TABLE MSysObjects TO PUBLIC";

conn.Open();

// set permissions to read system object to get the list of tables
try
{
cmdToExecute.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
return;
}

var tables = new List();

// get data
cmdToExecute.CommandText = "SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0";
try
{
var reader = cmdToExecute.ExecuteReader();
while(reader.Read())
{                        
tables.Add(reader[0].ToString());
}
reader.Close();
// reading data for each table
foreach(var table in tables)
{
cmdToExecute.CommandText = "SELECT * FROM " + table;
reader = cmdToExecute.ExecuteReader();
while(reader.Read())
{
var sb = new StringBuilder();

for(int i=0; i
{
sb.Append(reader.GetValue(i).ToString());
sb.Append(delim);
}
sb.Append(file);
sb.Append(delim);
sb.Append(table);
sb.Append("\n");

stream.Write(sb.ToString());
}
}
}
catch(Exception e)
{
Console.WriteLine(e.Message);
return;
}

conn.Close();
}

}

Console.ReadKey();
}

Sunday, August 24, 2014

Agile or Traditional is not just your preference

Many of us have been through mainstream both types of development: traditional and Agile. I am not going to speculate on which one is best because no one but you can answer this question with due probability. The choice depends on many parameters that no one can advise you on. In short, Agile works best in the situation of volatile requirements whereas traditional shows better results backed by steady specifications.

Still many people try to use Agile in all cases, mostly because it is "more fun to do". Well, if it was about having fun we would probably spend our time riding a roller-coaster rather that sitting in our offices. So it is not about fun you get in the process, it is rather about the fun we get successfully launching the system, seeing it doing something useful and helpful. This is the kind of fun that I personally prefer.

The road to this goal itself is also important but as much as it does not compromise the goal itself. For example, choosing Agile for a mission-critical system may become fun in development but a huge frustration in the end. The devastating effect will be so big that you would not remember a minute of fun you have had in the process.

Agile is definitely attractive to the developers because it allows to jump into the coding not bothering doing boring routine work. It allows to skip the hardest efforts such as trying to think ahead, measure risks and probabilities. But it comes at the cost of missing important aspects of system design or requirements which will be hard to add on later, when the shortage is revealed. So be flexible. It is good to have more then one tool if conditions change.

Quick turnover cycles also attract stakeholders. They can see results quicker and try out the system earlier. It enables them making corrections if their understanding of the business processes was not optimal. Which in turns makes unsteady requirements even more volatile and Agile even more efficient. But this is only in theory. The reality is more complex as stakeholders fail to understand what it takes sometimes to convert a prototype into a production solution. It may not be easy to convince your boss that the system that he can see working is not ready to be released after few fixes. You must be prepared and keep delivering the message about what they see consistently. Otherwise you have a big chance to lose this battle and deal with evolving prototype kind of project with no time available for refactoring. I have been thought a project that took few weeks to be coded into Alfa but costed us six months to fix up and refactor. You have a good chance not to repeat our mistake.

All in all, Agile or not should not be your personal preference. It is not a religion or us against them. This is simply a tool that you should be using wisely. You have choices and whether you make them right will most likely define the success of your future project. So, choose insightfully and enjoy project success.



Friday, August 15, 2014

Need more automated tests? - No problem

Many want to add automated test harness to their tool-set. Some start. Few succeed. There are many reasons for this unfortunate trend to develop, but I want to focus on just one - automated tests are expensive on resources. You simply need to invest more time and efforts into making it happen or you are most likely set to fail.

Despite hiring more engineers to write tests looks like an obvious option, unless your are Google and do not really care about money, it is rarely something that you can sell to your bosses. So, the point is to make the best use of the resources you already have. All those people who are working hard running manual tests could have been used as an additional workforce. Not only would it increase the number of tests dramatically, it would probably increase their motivation and enthusiasm by doing really cool stuff and learning a bit of programming.

This is good that we advanced so far in thinking but there is another problem. What if those people do not have the required experience? Indeed, I would not expect them to start writing an effective and reliable locator method from the start. But as far as they know how to write a cycle - they are good enough. All you need is to re-balance the tasks so as to make the best use of either 1. highly technical personnel, guru and 2. people capable of doing some of coding.

In case when first group of engineers is involved in all stages of automation you probably waste their time and experience on dealing with tasks that can be performed by group 2. Moreover some tasks in automation may be even more suitable for people with manual testing experience, like creating new test scenarios. So it seems like creating test sequences is better to be done by people from the group less experienced in automation, but more savvy in manual test design and execution, knowing concepts of equivalent portioning and boundary conditions. Whereas technical aspects, such as locator engineering and test architecture design should be left in hands on gurus.

This will allow you automation champions to spend more time doing more complex tasks, leaving the simple stuff to their colleagues from manual testing wing.

In practice this schema can be implemented by doing test design with stubs created by automation team, followed by test case implementation by manual testing team. Tests do not work until automation team implements all the accessibility and validation functions. Until implemented the function should report themselves as not implemented.

I have successfully employed this schema more than once. Hopefully this will be of help for someone else out there.

Happy test automation!

Sunday, October 21, 2012

J-Meter, first impressions

Today I had a chance to take a look at J-Meter, a load tests tool, which most important feature is that it's free.

It took up to 10 minutes of my time to generate a simple HTTP request load test. In this sense, I would not call the tool UI intuitive. But once you've done your first test you should have no problems in the future. It just takes time to get used to unusual way of handling test components. Instead of enabling or using controllers and entities you simply add them to your test plan.

I liked the way you add more summary and reporting to your test. You may enable only those reporting ways that you prefer. However I found the charting slightly difficult to use and cheap-looking. But to me this is not a big problem as I usually value table output over charts.

Overall impression is that tool is capable of helping you out with simple load tests. But I would not recommend it for testing big and complex systems. You better look at commercial competitors.

In case when your financial restrictions prevail this is a good choice. But be prepared to spend more time for generating and supporting the tests than it would have been should you use a more advanced tool.

Automated testing (to be or not to be)

Many want to do automated testing. Not many start doing so. Few succeed. The reason why hides in the way we used to do our work and how we assess the performance. We prefer to see the results straight away, on the spot, now. Whereas it takes a while for automation to start paying back. The difference in time and efforts before it starts doing so can be recognized as a waste. In other words you would get an immediate result if you'd manually test your application instead of re-writing map files after UI change. Defects found and removed from the system is what is being scaled against investment in automation that will start paying back in months.

On the other hand, having automated tests saved many projects putting them back on track timely. The beauty of automation is that you so not need to make trade-offs, select regression tests carefully, adjust plans, and  procure more resources if you found that more testing is required. You just run them all (except for the situation when there are too many or your resources are limited).

The dilemma grows even bigger when you project size grows. For big projects this is even more difficult to find resources for automation, the lead time is even longer, however the outcome is usually more tangible. You should have  firm intention, high motivation, and be prepared for a challenge if you want your automation to start paying back one day. Below are simple recommendation for those who started on this path:

- Get commitment from the management, sell off the idea.
- Set right expectations in stakeholders (automation will start paying back when we reach X% of tests automated; usually about 30 if you start from the most important ones).
- Plan efforts for automation (don't expect people to spend their free time due to pure enthusiasm).
- Stick to the plans, treat automation goals as project goals, equal in the priority.
- Allow enough contingency buffers in your plans, so automation will not suffer just because something happened and your plan does not have any fat to cope with the situation.
- Get skilled hands to work on automation (test automation is more of programming that testing, so make sure people who do it are rather developers).
- Follow best practices in code engineering. It will save you massive efforts in maintenance. Make test code isolated from accessibility functions and GUI maps.
- Run your tests often not to allow them get outdated, so as it is not reasonable to invest in the updating. A test that sits n the shelf a year will unlikely work any more. Now imagine that you have got thousands shelf-ware tests.
- Use data-driven and control-driven approach.
- Involve more people by implementing pseudo-languages (a command driven test with a  language that anyone can easily use to create test sequence; such a sequence drives the test code responding to the commands and data parameters in it).
- Consistently deliver the message that automation is not an immediate ROI. Nonetheless it's got one of the biggest ROI in the software industry.

If you follow this you would save yourself tonnes of nerves.

Good luck!


Tuesday, October 2, 2012

Meteor approach

Yesterday I've finally fulfilled my wish and tried out Meteor. This is a new framework for developing web applications using JavaScript as the main language.

I just loved how easy it is to create a site and how few lines of code it requires. I also liked the way how it makes the changes immediately reflected in the running application.

But the most valuable tome is the ability to move the functionality between client and server with no efforts. There are actually three folders: one for server, one for client, and one for both. Moving stuff between them you change the architecture of your system. This may be very interesting for balancing complex systems load. For example, you have a client-server system which should support 1000 simultaneous users. You are kind of limited on the amount of horse power under the hood, the server is rather weak. So you may decide to move as much of computation as reasonable to the client side. In the traditional system it would require a careful planning and decent programming. In Meteor this is just a matter of Cut/Paste the Try. So you can easily adopt trial and error method.

Meteor is built on templates. Everything is defined as a template and it requires quite a bit of abstract thinking if you want to design a new system from scratch. be prepared to start thinking in different categories.

But Meteor is not only a framework. It's a Cloud. It takes just one command line call to deploy your application in a cloud. very easy and robust.

Meteor uses a non-relational database in the back-end. I hope they will implement connection to other database engines in the future.

Of the disadvantages I would mention using JavaScript, which is not object-oriented (despite some may argue, f.e. http://www.javascriptkit.com/javatutors/oopjs.shtml). Hopefully they will consider new replacement from Microsoft (TypeScript). Also there is only one database engine available yet (correct me if I am wrong). which also puts limitations on the system. We know that changing DB engine is not an option for many very complex systems. Allowing use of engines like Oracle, MS SQL Server, and DB2 would make that new framework more attractive to the enterprise software produces, including my company.

System is still in development, so don't rely on it if you need to make a commercial package. The documentation is scarce but you may find several examples and tutorial videos on the Internet. It is definitely good enough for evaluation and playing around with it. Believe me it's a pure fun :)

If you want to read more, just visit their site at http://meteor.com/main.

Friday, September 21, 2012

CTO - Rethinking the priorities

Do you know what is takes of a person to jump up from the quality assurance world into the leading production role? I do as after having more than 12 years of quality management experience I got an opportunity to head up the entire development operation.

This always looks easy when you are judging based on a limited understanding of the complexity f someone's else job. The best football players are always those sitting on the sofa. You have probably seen your boss acting and thought that you could do no worse. Actually you don't know until you try it out. I was thinking somewhat the same too.

Now I learned the difference and it's huge. I never knew how much of a company success depends on the personality of its leaders. Being a leader is the responsibility. The team is very sensitive to your own conditions. They see how you do and try to do the same. If you go lazy, the team does the same. If you are working hard the team catches up.

But the most complex thing was wear a new role on the team. Instead of quality perspective only I have got budget and business goals to drive my actions. I learned how to compromise quality when it is required. Yes, I did those obscene for which I hated CTOs with whom I worked before. This does not mean I am a bad person now (hope you don't think so:)). This only means that I understood and adopted a new role.

The moral of this story is that we should always be flexible and be prepared for a change. If we don't we will hardly be able to feast on the opportunities.

Good luck in changing the world through changing yourself!