1

I am trying to define ODBC data source for Power BI Destop and use parameters in it. So - I have ODBC DSN for the Firebird (defined in the "ODBC Data Source Administrator (64-bit)") and I can successfully import full tables from this ODBC in the Power BI Destop.

Now I am trying to use custom SQL statement with the parameters for the definition of the data source for Power BI Destop.

I am doing: 'Get data from another source' and select 'Other - ODBC' and I enter the following SQL statement in 'Advanced options - SQL statement (optional)':

select s.sale_id, s.sale_date, sl.good_id, sl.price, sl.amount
  from sale_lines sl
    left join sales s on (sl.sale_id=s.sale_id)
  where s.sale_date>=:from_date and
        s.sale_date<=:to_date

I have also tried to use @form_date, ?from_date formats for the definition of the parameters. But in all cases I am getting different error messages:

Details: "ODBC: ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -206
Column unknown
FROM_DATE
At line 4, column 26"

Details: "ODBC: ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, column 25
@"

Details: "ODBC: ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, column 26
from_date"

So, my question is - how to defined parameters for the SQL statement that I am building for the definition of ODBC data source for the Power BI Destop. I am using Firebird 2.1/3.0/4.0.

TomR
  • 2,696
  • 6
  • 34
  • 87
  • This https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters can be good lead to the possible answer - dynamic M query parameters. – TomR Sep 07 '21 at 08:05
  • 1
    Not a direct answer, but generally speaking, you should not use parameters at all. Power BI doesn't work that way. Leave Power BI to select and filter the data for you. You should simply add the required tables in the model and build the logic there. – Andrey Nikolov Sep 07 '21 at 08:22
  • I understand it, but my tables are with data for many years and takes GBs of space. And there is no DirectQuery option for (Firebird) ODBC. – TomR Sep 07 '21 at 08:41
  • 1
    About ODBC parameters: https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/statement-parameters – user13964273 Sep 07 '21 at 10:57

1 Answers1

2

There is no named parameters in Firebird itself.

If your database access library does not implement them as an extra layer over standard SQL - then use unnamed ? parameters and populate them by parameter number/index rather than by a non-existing name.

Like select a, b from tablename where (c = ?) or (d < ?).

Arioch 'The
  • 15,799
  • 35
  • 62
  • Yes, PowerBI accepts this, but gives error message 'Details: "ODBC: ERROR [07002] [ODBC Firebird Driver]COUNT field incorrect"'. So - obvisously - this is the right way to specify the parameters and so, the question is answered. But, unfortunately, the main problem is still unsolved due to further issues inside ODBC driver that somewhow tries to use COUNT etc. – TomR Sep 07 '21 at 13:01
  • 1
    @TomR is your Firebird version at least 2.5 ? 2.5 has TraceAPI and you can use tools like FBProfiler.sf.net to see which queries actually come to the server itself. Because your queries above have no aggregation functions, neither count or anything. I suspect it is not ODBC but PowerBI instead that tries to calculate "scroll bar length" albeit that is not always accurate value. Also, does PowerBI utilize other interfaces than ODBC ? like .Net Provider, or JDBC or OLE DB (commercial ibprovider.com) ? – Arioch 'The Sep 07 '21 at 15:18
  • The error means that number of your placeholders doesn't match number of assigned parameters. – user13964273 Sep 07 '21 at 21:48