Ask a Question

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

SOLVED
SriniMarva27
Occasional Contributor

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
mpartyka
Contributor

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

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.

View solution in original post

mpartyka
Contributor

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

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!

View solution in original post

5 REPLIES 5
mpartyka
Contributor

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

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.

View solution in original post

SriniMarva27
Occasional Contributor

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

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
Occasional Contributor

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

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
Contributor

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

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!

View solution in original post

SriniMarva27
Occasional Contributor

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

Thank you sir. 

 

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

cancel
Showing results for 
Search instead for 
Did you mean: