SQL/Java: My experience and learning during solving a problem

While working with a problem recently I felt the need of a bit deep dive into SQL and DB connection with regard to Java programming.

I had a problem which required me to look into DB connection process in order to automate a certain flow which included DB call at the end to validate certain expected results.

I was working in a test automation framework built quite long back with pretty old design patterns and principles.
It was no doubt tough to fix issues and understand the framework in terms of maintainability and coding practices. Also there was no documentation on the framework , so it lacked the readability too.

However when I started going through , somehow I managed to understand the framework and got a hang of it. But I had to solve a problem here.

Problem statement:

  1. User launches browser and navigates to a url
  2. User performs some operation where it includes uploading a xml file and the same file will create few records in the DB
  3. After record is created, it will generate a unique transaction id, lets name it transaction_id
  4. This id needs to be stored in a variable for further use
  5. The id is then passed to a DB query and as a result DB should return the record which got created when we uploaded the xml file.
  6. I used selenium and java to automate the file upload and to read values from webpage
  7. Then I triggered DB query to fetch the record to validate but it was returning me null.
  8. I tried the same doing manually and I was able to get
  9. I tried the same code using debug mode to understand where it is going wrong, surprisingly it worked well in debug mode.
  10. I also tried to hard code with existing id in query, it worked fine.
  11. I started feeling strange and thought a lot about the differences it would make.

My analysis and solution:

  1. I know that it works in debug mode and not in normal execution, So I started figuring out the possibilities.
  2. I tried to look into DB connection as sometimes due to improper Db clean up actions results might vary , so I went ahead and corrected DB closing connection code by adding
    DB Conn close, Resultset close, Statement close.
    Still I got same issue
  3. Then I realized that there was a delay when we create file in upload page and the record to appear in DB, 30 sec or so
  4. That is the reason while debug (due to step by step execution it anyways took time), hardcoding was working because it already existed in DB,
  5. I then added Thread.slepp for about 30 sec and the code started working.

My Takeaway is:

  1. to understand the flow clearly and try multiple times doing the same flow manually
  2. Whenever there is DB connection, be careful about making proper Open connection, close connection code or manage effectively DB conn pool.
  3. Understand the delay in DB operation as many time DB might fetch data from other DB sources which might delay the process