I am trying to run a complex query that accepts an argument from calling keyword.
The query is like 891 characters long consisting of some declarations, temp table with joins and lastly a select query joining the output of temp data and a few more tables as a final output.
I tried using that query as a single line and also multple lines (using three dots or 3 quotes) all fails to run it. I used Database Library. Execute Sql String and Database Library.Query keywords. Tried to put that query in a script file and the run using Execute Sql Script and they all fails.
Now I though of putting it in stored procedure and calling it via DatabaseLibrary.Execute Sql String and it doesn’t return anything. Calling via Call Stored Procedure keyword gives another error Keyword failed: AttributeError: ‘pyodbc.Cursor’ object has no attribute ‘callproc’
I am using pyodbc to connect to database and Database Library keyword to perform databse operations.
Any guidance on this scenarios is welcomed.
We have our complex static sql queries in a sql file which we then use as an argument in the keyword (execute sql query). Is that an option for your situation?
There is no execute sql query in Database Library. I only see Execute Sql String or Execute Sql Script as keywords. Plus we need to get to output of final Select query joining with temp table and another table as an output to be captured and later on this populates the values in the next keywords.
specifically the variable you’re passing to procedure_params, I’ll suggest you try procedure_params=${params} (note the $ not @
But I’ll also suggest also using Log to check the content of @{params} on the line above, because your list appears to have an = element as a separate values and I’m not sure that’s what you intended?
Here’s an example from the documentation for Call Stored Procedure, you can see the create the list with @ and then pass it in with $:
Calling the procedure in Robot Framework:
@{params}= Create List Jerry OUTPUT
# Second parameter value can be anything, it will be replaced anyway
${param values} ${result sets}= Call Stored Procedure get_second_name ${params}
# ${param values} = ['Jerry', 'Schneider']
# ${result sets} = []
Other than those 2 issues you syntax looks to me like what the documentation calls for.
Thanks for sharing the insight on calling a stored procedure. Apparently, I cannot use a Stored procedure, function or anything that can change DB schema. Now I need to work around in a way where I can use temporary table, with joins and use final select joining temp table with another table and capture its output in robot framework. Any suggestions on these?
I would first use a SQL client app appropriate for your database to figure out the sql statements you need and note them down as comments in your robot file
Then under each comment use Execute Sql String or Query (depending on if your expecting result data back from the statement or not, e.g. something like Create Table #MyTempTable I would probably use Execute Sql String, but something like SELECT * FROM #MyTempTable I would use Query
Then I would modify the values in the SQL statements with RF Variables where appropriate
You didn’t mention which database server you’re using and each database engine has it’s own unique variations on the SQL syntax.
But as a high level overview you’ll needs something like this:
# SQL to create temp table
Execute Sql String Create Table MyTempTable (
... EmployeeID int
... );
# SQL to put data into temp table
Execute Sql String Insert Into MyTempTable
... Select EmployeeID from [EmployeeMaster] Where EmployeeID between 1 and 100
# SQL to query what you need using the temp table
${result}= Query SELECT * FROM MyTempTable
... JOIN [EmployeeMaster] ON MyTempTable.EmployeeID = [EmployeeMaster].EmployeeID
... JOIN [AnotherTable] ON MyTempTable.EmployeeID = [AnotherTable].EmployeeID
# SQL to remove the temp table when you're done
Execute Sql String Drop Table MyTempTable
# test the results
List Should Contain Value ${result}[${result_row}] expected value
Make sure you drop the temp table before doing your assertions on the results as in case an assertions fails, or otherwise put the Drop table in the test teardown (you don’t want the DBA getting upset because you’re leaving all these temp tables open in memory.)
