search

Seddryck / NBi

NBi is a testing framework (add-on to NUnit) for Business Intelligence and Data Access. The main goal of this framework is to let users create tests with a declarative approach based on an Xml syntax. By the means of NBi, you don't need to develop C# or Java code to specify your tests! Either, you don't need Visual Studio or Eclipse to compile your test suite. Just create an Xml file and let the framework interpret it and play your tests. The framework is designed as an add-on of NUnit but with the possibility to port it easily to other testing frameworks.
http://www.nbi.io
Apache License 2.0
106 stars 37 forks source link

Unable to pass parameter to Sql query #634

Closed q3abhi closed 3 years ago

q3abhi commented 3 years ago

I am trying to pass the variable as a parameter to my sql query.

This is my test. I am passing the variable (DateKey) declared in assert section.

<test name="Cash Receipts - Current">`
  <system-under-test>`
    <execution>
      <query file="DaxQueries\CashReceipts\CashReceipts - Current.sql" />
    </execution>
  </system-under-test>
  <assert>
    <equalTo>
      <column  index="0" role="key" type="text" tolerance="1"/>
      <column  index="1" role="value" type="numeric"/>
      <column  index="2" role="value" type="numeric"/>
      <column  index="3" role="value" type="numeric"/>
      <column  index="4" role="value" type="numeric"/>
      <query file="SqlQueries\CashReceipts\CashReceipts - Current.sql">
        <parameter name="DateKey" sql-type="Int">20200331</parameter>
      </query>
    </equalTo>
  </assert>
</test>

This is my sql query defined in "CashReceipts - Current.sql" file. Using @DateKey to pass the value here.

SELECT
    r.AsOfDate AS [CalendarKey]
    ,SUM(r.YesterdayBalance) AS [Today]
    ,SUM(r.WeekBalance) AS [WeekToDate]
    ,SUM(r.MonthBalance) AS [MonthToDate]
    ,SUM(r.FYTDBalance) AS [YearToDate]
FROM
    wdw.fctReceivable r
INNER JOIN
    [wdw].[dimCalendar] c ON r.AsOfDate = c.CalendarKey
WHERE
    c.CalendarKey = @DateKey
GROUP BY
    r.AsOfDate

When I run the test, I get the following error :

NBi.NUnit.Runtime.TestSuite.Cash Receipts - Current:
System.AggregateException : One or more errors occurred.
  ----> System.Data.OleDb.OleDbException : Must declare the scalar variable "@DateKey".`

If I hard-code the value in sql query, test runs fine.

Seddryck commented 3 years ago

Preliminary comment: I do not recommend to use the execution and equalTo syntax but to switch to the new syntax result-set and equal-to. The old syntax that you're using is still supported but limited in terms of new features and will be deprecated.

Main answer: The issue is probably related to 

<execution>
      <query file="DaxQueries\CashReceipts\CashReceipts - Current.sql" />
</execution>

This query has no parameter defined.

q3abhi commented 3 years ago

@Seddryck

The parameter is defined in the "Assert" section as I am testing SSAS (System under test) and asserting it with sql.

<assert> <equalTo> <column index="0" role="key" type="text" tolerance="1"/> <column index="1" role="value" type="numeric"/> <column index="2" role="value" type="numeric"/> <column index="3" role="value" type="numeric"/> <column index="4" role="value" type="numeric"/> <query file="SqlQueries\CashReceipts\CashReceipts - Current.sql"> <parameter name="DateKey" sql-type="Int">20200331</parameter> </query> </equalTo> </assert>

Seddryck commented 3 years ago

Could you post your exact queries in the assert and system under test. I don’t see any reason why your query in system under test shouldn’t have a parameter but the query in assertion should.