Usage of a global variable to define a query parameter

[aniketh02]

I've seen that we can pass a static value to query parameter. is it possible to get the parameter value from a query? (query may execute on a specified connection string)

[Seddryck]

Not at the moment but it's planned to use global variables in more place than at the moment. Will add this to the backlog.

This item can be solved after #304

[Seddryck]

@aniketh02 To achieve this define a variable at the top of your test-suite. Then use this variable to define your parameters.

<testSuite name="Acceptance Testing: query equalTo with parameters" xmlns="http://NBi/TestSuite">
  <settings>
    <default apply-to="everywhere">
      <connectionString>...</connectionString>
    </default>
  </settings>
  <variables>
    <variable name="CurrencyCodeVariable">
      <query-scalar>
        <![CDATA[select top(1) CurrencyCode from [Sales].[Currency] where Name like '%Canad%']]>
      </query-scalar>
    </variable>
  </variables>
  <test name="Using a parameter referring to a variable">
    <system-under-test>
      <resultSet>
        <query>
          <![CDATA[select CurrencyCode, Name from [Sales].[Currency] where CurrencyCode=@CurrencyCode]]>
          <parameter name="@CurrencyCode">@CurrencyCodeVariable</parameter>
        </query>
      </resultSet>
    </system-under-test>
    <assert>
      <equalTo keys="all">
        <resultSet>
          <row>
            <cell>CAD</cell>
            <cell>Canadian Dollar</cell>
          </row>
        </resultSet>
      </equalTo>
    </assert>
  </test>
</testSuite>

Beta available at https://ci.appveyor.com/project/Seddryck/nbi/build/17.0-beta.105.build.212
Feedback welcome!

[aniketh02]

Hi Seddryck,

I have created a sample testcase to test global variable. I am getting "The variable named 'var1' is not defined" error.

I've defined variable at the top of the testsuite:

I am referring the same in the testcase.

[aniketh02]

Global variable support only scalar value? or can it support single column with more than one row (table-valued) ?

[Seddryck]

@aniketh02 Thx for reporting these issues

Humm, you test should work. Could you specify which version of NBi, you've tested? In the meantime, I'll try to reproduce.

Global variables are documented at http://www.nbi.io/docs/variable-define/ I fixed the issue in the ToC and now this page is visible.

At the moment, yes, NBi only supports scalar value. Variables containing a list or even a result-set, could be investigated but it will be in other work items.

[aniketh02]

I've tested 1.17.
I am unable to post code here so I have taken screenshot of it.

[Seddryck]

I can't reproduce your problem.

Could you be more precise than 1.17 (Is it the RC1? ) and specify the full version of the binaries? Could you retry with the last compilation: https://ci.appveyor.com/project/Seddryck/nbi/build/1.17.0-beta.109.build.224

PS: to post code put three ` to start and finish the paragraph of code

<?xml version="1.0" encoding="utf-8" ?>
<testSuite name="Acceptance Testing: ResultSet" xmlns="http://NBi/TestSuite"><settings>
    <default apply-to="system-under-test">
      <connectionString>Data Source=mhknbn2kdz.database.windows.net;Initial Catalog=AdventureWorks2012;User Id=sqlfamily;password=sqlf@m1ly</connectionString>
    </default>
    <default apply-to="variable">
      <connectionString>Data Source=mhknbn2kdz.database.windows.net;Initial Catalog=AdventureWorks2012;User Id=sqlfamily;password=sqlf@m1ly</connectionString>
    </default>
  </settings>
  <variables>
    <variable name="var1">
      <query-scalar>select 1963</query-scalar>
    </variable>
  </variables>
  <test name="Query with variable" uid="0107">
      <system-under-test>
        <resultSet>
          <query>
            select LoginID, JobTitle from [HumanResources].[Employee] where year([BirthDate])=@maxid
            <parameter name="maxid">@var1</parameter>
          </query>
        </resultSet>
      </system-under-test>
      <assert>
        <equalTo values-default-type="text">
          <resultSet>
            <row>
              <cell>adventure-works\ken0</cell>
              <cell>Chief Executive Officer</cell>
            </row>
            <row>
              <cell>adventure-works\belinda0</cell>
              <cell>Production Technician - WC45</cell>
            </row>
            <row>
              <cell>adventure-works\michael9</cell>
              <cell>Sales Representative</cell>
            </row>
          </resultSet>
        </equalTo>
      </assert>
    </test>
<testSuite>

[aniketh02]

I've tried global variable test on v1.17-RC (consumed it as a fresh setup in my machine)1. and also tried build from the following location but no luck. I am getting the same error. I have modified my test for easy testing, that does't require any table

https://ci.appveyor.com/project/Seddryck/nbi/build/1.17.0-beta.109.build.224/artifacts

<testSuite name="Acceptance Testing: ResultSet" xmlns="http://NBi/TestSuite">
  <settings>
  </settings>
  <variables>
    <variable name="var1">
      <query-scalar connectionString="Data Source=.;Initial Catalog=AdventureWorksDW2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"> 
         SELECT 4
      </query-scalar>
    </variable>
  </variables>
  <test name="global variable">    
    <system-under-test>
      <resultSet>
        <query connectionString="Data Source=.;Initial Catalog=AdventureWorksDW2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"> 		  		  
            <![CDATA[  SELECT @maxid  ]]>
            <parameter name="maxid">@var1</parameter>
	 </query>		  
       </resultSet>
     </system-under-test>
     <assert>
      <equalTo keys="all">
        <resultSet>
          <row>
             <cell>4</cell>
           </row>
        </resultSet>
      </equalTo>
    </assert>
  </test>  
</testSuite>

[Seddryck]

Thx @aniketh02 I will check this tomorrow evening. I'm really surprised because our tests are really similar.

[Seddryck]

Ok, I found the issue in your code. You're defining an OleDb connectionString (Provider = SQLNCLI11.1). The .Net framework doesn't support named parameters for OleDb commands: https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx

If you really want to use an OleDb connection string then use the correct notation for parameters: ?. Just replace the query in the system-under-test by:

<query connectionString="...">
  select ?
  <parameter name="maxid">@var1</parameter>
</query>

or use an SqlClient connection string (and then you'll be able to use a named parameter):

Data Source=.;Initial Catalog=AdventureWorksDW2014;Integrated Security=SSPI;Auto Translate=False;"> 

[aniketh02]

Hi @Seddryck I have used the following code with oledb connection string but it is giving the same error.
<query connectionString="..."> select ? <parameter name="maxid">@var1</parameter> </query>

Also tried with SqlClient connection string. Nbi is giving System.ArgumentException : Keyword not supported: 'auto translate' error so I've removed 'auto translate from connection string
`

	  <![CDATA[  SELECT @maxid  ]]>
	  <parameter name="maxid">@var1</parameter>
	  </query>`

and this time the error is same : NBi.Core.NBiException : The variable named 'var1' is not defined.

[Seddryck]

Cannot reproduce ... installing the correct version of NBi should fix your problem.