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

4 Replies

  • richie's avatar
    richie
    Community Hero
    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
      Contributor

      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

      • richie's avatar
        richie
        Community Hero
        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