SQL Datasource Prepared Properties using a Property that is a comma separated list
Hi,
I am using SQL Datasources (connecting to Postgres) in my ReadyAPI suite.
I am trying to use the Prepared Properties feature. There are somethings that are not working as I expected.
I have simplified most of the selects etc, but it is close enough to what I am attempting to run
Currently I have a Datasource Step using JDBC with postgres.
I have a select statement in the Data source such as this
Select First_name,last_name,customer_id from customer where customer_id in (30963905,33227339)
so now i want to use Prepared Properties instead of hardcoding the values in the SQL query. I create a Prepared Property in the Datasource step called customer_id. the value in it is 30963905 (valid customer in the database).
SQL statement is now updated as below
Select First_name,last_name,customer_id from customer where customer_id in (:customer_id)
when I run this, i get the following error
ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 2906 so the above is issue 1. To work around this I've changed my query as
Select First_name,last_name,customer_id from customer where cast(customer_id as varchar)
in (:customer_id)running a test, this returns 1 record. So is this the best way to do this or is there a better work around?
Let's still proceed further with my problem
Now with the prepared property, i've updated the value of customer_id. the new value in it is 30963905,33227339 (valid customers in the database)
now when running a test, this returns 0 records. so what am I doing wrong with the property?
I have tried the following permutations for the property value with all of them returning 0 rows back
30963905,33227339
30963905','33227339
30963905","33227339
'30963905','33227339'
"30963905","33227339"
Is there a way to see what is actually passed to the database server?
- Hey troyyerJP,
Ive always struggled usimg the properties from within the jdbc test step using the ":attribute" syntax as ive always found it flakey.
I really have no idea why casting to a varchar actually got you further ahead considering that properties are stored as strings anyway....so why would the cast be needed? Who knows!
Anyway, the way i got around the struggles you have at the moment is that is setup my tests to source properties from OUTSIDE the jdbc step (from a Properties test step, etc.) and then instead of that ":attribute" syntax i just replaced with '${PropertiesTestStep#attribute}' which worked everytime.
Could you perhaps alter your approach slightly or is it essential fpr some reason i cant think of to include properties on thr jdbc step itself rather than external to it?
Cheers,
Rich Hi richie ,
Thanks that suggestion has sorted my issues and I also didnt have to perform the cast to varchar. (on an aside I question the documented use of Prepared Properties in the ReadyAPI page https://support.smartbear.com/readyapi/docs/testing/data-driven/types/prepared-properties.html, as it states "you can use the prepared properties to add variable values to database queries." values being plural I would have assumed it should work the way I attempted it. anyways......)
Just documenting some quirks here with the process that richie mentioned.
Tl:dr version
to use a custom property in a Data Source query for selection using integer values, create the Property at any level in ReadyAPI and call it in your JDBC test step as
select * from customers where customer_id in ('${#TestSuite#customer_id}')(Note: for the Postgres JDBC SQL driver, I encountered an issue and the workaround is as below. for other drivers this workaround doesnt need to be done and you can comma separate the list)
- if the Property has one integer value, then enter the integer into the custom property as is without any quotes
- if the Property has greater than 1 integer value (that will be comma separated),
- end the first integer record with a single quote
- start the last integer with a single quote
- for integers that are not the first record, and not the last record, enclose the integer with single quotes. Example with multiple records:
33227339','30963905','31216310Full break down of me debugging this.
Added Property at Test Suite level, Property name customer_id with initial value of
33227339I have used the following parameter in the Postgres jdbc data source step as '${#TestSuite#customer_id}'
select * from customers where customer_id in ('${#TestSuite#customer_id}')worked and returned 1 record
Updated Test Suite Property customer_id with new value of
33227339,32286233rerunning the jdbc test step above gets an error org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "33227339,32286233"
Updated Test Suite Property customer_id with new value of
30963905','31216310this now works and returns the right records back in the Datasource.
When i took it to 3 records I had to do the following
33227339','30963905','31216310edited: with additional comments about the jdbc driver used
