cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Datasource Prepared Properties using a Property that is a comma separated list

SOLVED
troyyerJP
Occasional Contributor

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)

troyyerJP_0-1624175520065.png

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? 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
richie
Community Hero

Re: 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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

troyyerJP
Occasional Contributor

Re: SQL Datasource Prepared Properties using a Property that is a comma separated list

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','31216310

 

 

Full break down of me debugging this.

Added Property at Test Suite level, Property name customer_id with initial value of 

 

 

33227339

 

 

I 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,32286233

 

 

rerunning 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','31216310

 

 

this 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','31216310

 

edited: with additional comments about the jdbc driver used

View solution in original post

4 REPLIES 4
richie
Community Hero

Re: 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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

troyyerJP
Occasional Contributor

Re: SQL Datasource Prepared Properties using a Property that is a comma separated list

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','31216310

 

 

Full break down of me debugging this.

Added Property at Test Suite level, Property name customer_id with initial value of 

 

 

33227339

 

 

I 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,32286233

 

 

rerunning 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','31216310

 

 

this 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','31216310

 

edited: with additional comments about the jdbc driver used

View solution in original post

richie
Community Hero

Re: SQL Datasource Prepared Properties using a Property that is a comma separated list

Hey @troyyerJP,

Glad i could help.

What version of ReadyAPI are you using cos i have multiple paramterised integers in my properties and i didnt have to separate them out with apostrophes......thats just weird!

Admittedly, i typically create sqlserver/oracle/mySql RDBMS connections. Just wondering if thats a driver issue, rather than a ReadyAPI issue.

Ta

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
troyyerJP
Occasional Contributor

Re: SQL Datasource Prepared Properties using a Property that is a comma separated list

@richie , I'm currently using ReadyAPI 3.8.0

I might just update my previous comment to be more explicit about which JDBC sql (Postres being the sql environment I am running my current project in) so as not to confuse anyone coming here in the future. I'll have to connect up to our MS SQL environment and try it out as well  

New Here?
Join us and watch the welcome video:
Top Kudoed Authors