duckdb: "Invalid keyword" error in SQLDriverConnect when connecting via Power Query SDK

What happens?

When connecting to DuckDB from Power Query SDK using the latest build on main, I cannot see any tables in the Odbc.DataSource that is returned by the query. I have tested this with the latest release (0.10.0) as well and confirmed that this did return the tables when running the test script.

To Reproduce

Download latest nightly build for Windows
Install the duckdb_odbc driver
Create a test database using the duckdb.exe from the nightly build, for example, .\duckdb.exe test.db, and create a table, create table numbers as select * from generate_series(1,10) g(x)
Connect to the test database with Power Query SDK, for example, using this SqlODBC sample with the following Power Query script:

let
    Database = "\path\to\my\test.db",
    OdbcDatasource = SqlODBC.Contents(Database)
in
    OdbcDatasource{[Item="numbers"]}[Data]

Make sure to set HierarchicalNavigation = false in the SqlODBC.pq file.

This returns the following error:

The key didn't match any rows in the table

wheras with the 0.10 release, it returns a table.

When looking at the trace logs, the following line shows up in the logs for the nightly build that is not in the 0.10 build:

Microsoft.Mashu 6ce4-5210	EXIT  SQLDriverConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HDBC                0x000001C52CE6A750
		HWND                0x0000000000000000
		WCHAR *             0x00007FFD478FC3D0 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FFD478FC3D0 <Invalid buffer length!> [-3]
		SWORD                       -3 
		SWORD *             0x0000000000000000
		UWORD                        0 <SQL_DRIVER_NOPROMPT>

		DIAG [01S09] O (0)

Where 01S09 is an “Invalid keyword” error in SQLDriverConnect. See docs.

Here are the trace logs for the main build and the 0.10.0 build, respectively: ODBC_TRACE_main_build_VSC.log ODBC_TRACE_010_VSC.log

OS:

Windows 10

DuckDB Version:

Latest main (0.10.1-dev596)

DuckDB Client:

ODBC

Full Name:

Guen Prawiroatmodjo

Affiliation:

MotherDuck

Have you tried this on the latest nightly build?

I have tested with a nightly build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

Yes, I have

About this issue

Original URL
State: closed
Created 4 months ago
Comments: 22 (12 by maintainers)

Most upvoted comments

@guenp Thanks a lot for the hint. Just tried it but the connector doesn’t show up. Placed it correctly in the expected folder. Also installed 0.10.0 odbc driver. Adjusted the security config in Power BI. I work in Windows. You linked the linux odbc drivers in your Repo…

dewulf on Mar 27, 2024

@dewulf, there are a bunch of workarounds implemented in this Power Query connector, that might help you get the data in UTF-8. Note that this only works with duckdb_odbc 0.10.0 and there are some known bugs

guenp on Mar 27, 2024

@kmatt, thanks again for sharing repro steps. I think I was able to partially fix your issue, but I need to do some more investigation on the root cause before I can share a sustainable fix. Basically, it looks like the ODBC driver is returning the wrong column types to Power BI, which makes it unable to parse the column which causes the error you saw (This query does not have any columns with the supported data types.). I tested this by creating a simple table with two INT8 columns and running your repro steps. After some inspection it looks like ODBC returned the columns as type 11 which is TIMESTAMP (see ODBC spec). I was able to “fix” this by converting the type to TINYINT (type -6). Note that this is not possible to do via Power BI directly and can only be done by writing a custom connector and overriding SQLColumns (see docs).

Loading the data via “Blank Query”:

After loading the data and creating plots:

Sorry I can’t be more helpful, but I’ll keep you posted on my progress and will share the code with you once it’s ready! Note that I ran the above on 0.10.0, to make this work in 0.10.1 we still need to fix this “Invalid keyword” error (which will hopefully be an easy fix).

guenp on Mar 4, 2024

@kmatt could your issue be related to this one: https://github.com/duckdb/duckdb/issues/6528?

maiadegraaf on Mar 1, 2024

@kmatt yes, it’s the latest nightly build that is published here: https://duckdb.org/docs/installation/?version=main I just abbreviated it to 0.10.1-dev, but the full name of the version I used was v0.10.1-dev585 25720d7112

I added to this issue because of https://github.com/duckdb/duckdb/issues/10907#issuecomment-1970280569 but I can create a new issue if it should be tracked separately.

Sorry for the confusion, I added that for context on why I used HierarchicalNavigation=false in the Power Query script. We could definitely open an issue for that but it may become moot given that we have this regression on the latest main (my guess is that pushing a fix for that will require fixing this issue first)

guenp on Mar 1, 2024

@guenp - Example repro for a DuckDB with tables and data in the database:

Get data -> Blank query
Advanced Editor
Enter Power Query query, with a DSN as below, or without a DSN as above:

let
    Source = Odbc.DataSource("dsn=DuckTest", [HierarchicalNavigation=false]),
    testdata = Source{[Item="testtbl",Schema="main",Catalog="testdb"]}[Data]
in
    testdata

I will see data in Power Query, but Close & Apply fails with the above errors.

If I connect to a memory database, and query files in the file system, the ODBC connector dialog can be used, but this does not address DuckDB files with table data within. The only way I have been able to access DuckDB tables is via Power query, and table names specified. No browsing of DuckDB tables seems to work for me.

kmatt on Mar 1, 2024

Hi @guenp, thanks for your issue. I recently reworked the way the driver reads the connection string. I suspect that SQLDriverConnectW returned SQL_SUCCESS_WITH_INFO since HierarchicalNavigation is not a valid DuckDB config. Previously only the database path and the DSN was read out of the connection string so everything else was just ignored, now a SQL_SUCCESS_WITH_INFO is returned when an unknown keyword is encountered as per the docs. Is it possible to print out the diagnostic records to confirm HierarchicalNavigation is what’s causing this? Also could you let me know if this is still a problem if HierarchicalNavigation is removed?

maiadegraaf on Feb 29, 2024