SQL Datasource Prepared Properties using a Property that is a comma separated list
- 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
