Forum Discussion

arasi_m's avatar
arasi_m
Occasional Contributor
6 months ago

How to build SQL query using strings whose value should be retrieved from TestCase property

Suppose i have the following properties defined in the Test Case Properties

FromTime = 2023-11-15 23:00:00

ToTime = 2023-11-16 23:00:00

 

I want to replace the time given in the below query with the above test case property variables. How to do that in ReadyAPI JDBC datasource ?

SELECT * FROM Table1 where TagId = 4314 and Time > '2023-11-15 23:00:00' and Time < '2023-11-16 23:00:00'

 

I am able to retrieve integer values like TagId and use it by adding 'Prepared Properties' within the JDBC datasource window. But if i add the string and use it inside the query, data is not fetched as the string is not interpreted properly.

 

Configuration
Data-Driven Testing
REST
Scripting

7 Replies

Sort By
  • mrdwprice's avatar
    mrdwprice
    Contributor
    6 months ago

    What data type does Table1.Time have in your database?
    is it DATETIME?

    • arasi_m's avatar
      arasi_m
      Occasional Contributor
      6 months ago

      Hello,

       

      In the database it is defined like this

       

      Time(datetime2(0),not null)

      • mrdwprice's avatar
        mrdwprice
        Contributor
        6 months ago

        Try using the CAST or CONVERT function

        SELECT * FROM Table1 WHERE TagId = 4314 AND Time > CAST('${#TestCase#FromTime}' AS datetime2)

  • TNeuschwanger's avatar
    TNeuschwanger
    Champion Level 2
    6 months ago

    Hello arasi_m 

     

    Just to add on a little to what mrdwprice stated previously...

     

    SELECT * FROM Table1 where TagId = ${#TestCase#TagId} and Time > '${#TestCase#FromTimeUTC}' and Time < '${#TestCase#ToTimeUTC}'

     

    A nice feature that ReadyAPI provides is to 'right-click' in most any data entry field and choose the "Get Data" menu item.  It allows you to gather the correct parameter replacement value from virtually anywhere in your project.  In this case, doing that and picking the values from your Test Case area will get you the properties you can use.

     

    Regards,

    Todd

    • arasi_m's avatar
      arasi_m
      Occasional Contributor
      6 months ago

      Thank you for the response. But i was facing the below error

      Can't get the Connection for specified properties; com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Conversion failed when converting date and/or time from character string.

      When i used the query like this
      SELECT * FROM Table1 where TagId = 4314 and Time > '${#TestCase#FromTime}'

       



  • mrdwprice's avatar
    mrdwprice
    Contributor
    6 months ago

    Perhaps you are missing the single quotation marks?

    SELECT * FROM Table1 where TagId = 4314 and Time > '${#Scope#Property-Name}' and Time < '${#Scope#Property-Name}'