Forum Discussion

troyyerJP's avatar
troyyerJP
Contributor
3 years ago
Solved

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 s...
  • richie's avatar
    3 years ago
    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
  • troyyerJP's avatar
    troyyerJP
    3 years ago

    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