Forum Discussion

SriniMarva27's avatar
SriniMarva27
Occasional Contributor
7 years ago

How to use table name dynamically in SQL Query to populate Data Log of Data Source?

I am trying to create more dynamic data source test step which will have JDBC SQL query for which the schema, table names can be taken from test suite that is running.   For example, I have a test ...
  • mpartyka's avatar
    mpartyka
    7 years ago

    1) set up your 2 properties schemaName and tableName .. you can do this at this test case level, the testsuite level or the project level.  (Highlight the testCase/testSuite / project in the left panel.  If the properties window pane does not display below this panel, click View / Properties from the menu.  Click on the "Custom Properties" tab in the properties window pane.  Click the green plus sign to add the property, give it a name and a value.)

    2) once the properties are set up ... go back to the SQL Query window in your screen shot and right click.   This should bring up the 'Get Data' window.  Select the properties that you want to use in your SQL query.

  • mpartyka's avatar
    mpartyka
    7 years ago

    I think that you are missing the single quotes around the #TestCase#testCaseName ... without the quotes, I believe SQL thinks it is a column name.  


        WHERE DATA_SCENARIO LIKE CONCAT(A,'%')   - without the single quotes around A SQL is looking for column A

        WHERE DATA_SCENARIO LIKE CONCAT('A','%') - I think this is what you want ... so the two strings will be concatenated.

    so maybe this will work ?

        WHERE DATA_SCENARIO LIKE CONCAT(''',${#TestCase#testCaseName},''','%')

    Hope that helps.   Not a SQL expert!

mpartyka's avatar
mpartyka
Contributor
7 years ago

1) set up your 2 properties schemaName and tableName .. you can do this at this test case level, the testsuite level or the project level.  (Highlight the testCase/testSuite / project in the left panel.  If the properties window pane does not display below this panel, click View / Properties from the menu.  Click on the "Custom Properties" tab in the properties window pane.  Click the green plus sign to add the property, give it a name and a value.)

2) once the properties are set up ... go back to the SQL Query window in your screen shot and right click.   This should bring up the 'Get Data' window.  Select the properties that you want to use in your SQL query.

  • SriniMarva27's avatar
    SriniMarva27
    Occasional Contributor
    7 years ago

    Hi,

     

    Thank you for your reply. I am able to write dynamic SQL query 

     

    SELECT * FROM ${#TestSuite#schemaName}.${#TestSuite#tableName}
    WHERE DATA_SCENARIO LIKE ${#TestCase#testCaseName}

     

    But how do I include % in the above query. Could you please help me in writing it?

  • SriniMarva27's avatar
    SriniMarva27
    Occasional Contributor
    7 years ago

    SELECT * FROM ${#TestSuite#schemaName}.${#TestSuite#tableName}
    WHERE DATA_SCENARIO LIKE CONCAT(${#TestCase#testCaseName},'%')

     

    Could you please tell me what mistake am I making in the above SQL query?

    • mpartyka's avatar
      mpartyka
      Contributor
      7 years ago

      I think that you are missing the single quotes around the #TestCase#testCaseName ... without the quotes, I believe SQL thinks it is a column name.  


          WHERE DATA_SCENARIO LIKE CONCAT(A,'%')   - without the single quotes around A SQL is looking for column A

          WHERE DATA_SCENARIO LIKE CONCAT('A','%') - I think this is what you want ... so the two strings will be concatenated.

      so maybe this will work ?

          WHERE DATA_SCENARIO LIKE CONCAT(''',${#TestCase#testCaseName},''','%')

      Hope that helps.   Not a SQL expert!

      • SriniMarva27's avatar
        SriniMarva27
        Occasional Contributor
        7 years ago

        Thank you sir. 

         

        Is there a way to clear the data log of a data source during run time using Groovy code?