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 suite named CQ_SMOKE_TEST where CQ is the schema name, CQ_SMOKE_TEST is the table name and the SQL query in Data Source should be dynamically created as follows

 

SELECT * FROM <SCHEMA NAME>.<TABLE_NAME>

 

How do I achieve this?

  • 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!

  • 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

      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

      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

        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!