Can anyone please help me with a working SQL Query format with parameters in the where clause? I have tried using the format we use in Java (JDBC) and few other trial and errors reading about Groovy SQL format, but no queries working in Katalon!
An example in Java for which I am looking a corresponding format in Katalon (Groovy).
String query = “select emp_id, **name **from **employee **where emp_id = '”+employeeID+ "’ "; //employeeID is a parameter passed from Excel (Data File)
your String looks good. What error do you get? Do you run it against MS SQL Server? How do you run DB query?
I get this error:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name ‘A568632’.
//Here, A568632 is actually the value of the parameter in where clause of the query being passed from a Data File (Excel). I don’t know how Katalon identifies it as a column name! Thats why I doubt the format of the query.
I am running it against SQL Server. I have tried it in two ways: 1. Directly from within a test script 2.Calling a custom keyword into my test script. Both generating the same error as above.
But this is SQLServerException, it is returned from SQL Server, not from Katalon or Selenium. Looks like the query is invalid. Can you debug your test and find out what query is sent to SQL Server?
Indeed, the issue was of the query format because I was using single quotes in queries like we use in each Katalon statement. I have now replaced the single quotes with double quotes and modified the query exactly as this format:
String query = “select emp_id, **name **from **employee **where **emp_id **= '”+employeeID+ "’ ";
So yesteray when I was using this format with double quotes, it was actually working, but I was not sure if its really working because the KeywordUtil.markPassed and KeywordUtil.markFailed which I am using to validate the database result was producing false positives in test report due to some reason.
You can use both single and double quotes for String in Groovy - but only limitation for single quotes in this case is that you have to escape single quotes in query itself - like this:
String query = 'select emp_id, name from employee where emp_id = \'' +employeeID+ '\'';
I am confused now - so does it work or not? How can it produce false-positives?
I was looking for exactly this format since last few days which you have now provided.
It worked for me in the same format we use in Selenium/Java, the reason of false-positives was not the query, it was due to another reason which I have mentioned in above post, but that confused me that the SQL query was not working.
I am glad that I could help you. Is there anything other you need help with in this case?
That was adequately helpful Marek, thank you!
could you please help me with this if you can… so i have a script connecting to the sql database. the connection works fine but when i try to execute the script i get an error:
So i created a Global Variable which is a username.
CustomKeywords.‘mydatabaseConnection.FicaMe.execute’("UPDATE [Trading].[dbo].[Users] SET UserStatusID = 2, ficaRegistered = 1 WHERE [Login] = "+ GlobalVariable.username )
This is the error i get :
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name ‘MyUsername_1534249786’.
and note that MyUsername is the username which i created as a global variable.