Please post your Web Driver questions in official Web Driver forum

Friday, March 27, 2015

Prepared database statement and casting

Though database testing is not directly possible from selenium but if you have followed selenium database testing video then you know how you can use jdbc to perform database operation from within your java selenium tests. 

I came across an interesting problem today when I had to insert value in a database table. On the surface corresponding database insert code looks very easy - 

But its execution fails on line 16 with error - 

ERROR: column "user_id" is of type integer but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 163

The problem lies in the following line - 

queryBuilder.append("VALUES ('TRUE',?,2,'gift code [BoGJRhmTmImKJlrlFnBlZd+j2dBO3M9a]','50000','1060262','1');");

Herein user_id is represented by "?" and later replaced by real user_id in following line - 

updateCreditAccount.setString(1, UserModel.getUserId();

But user_id is of type integer in databse hence it needs to be cast from character to integer. This can be done by replacing "?" with "?::integer" in "VALUES" statement. Hence modified code looks as (Notice the change in line - 7) - 

And this works now :)
Fork me on GitHub