ODBC connection from SAS

[gravesti]

I'm trying to use DuckDB via ODBC in SAS 9.4.
I get this incomprehensible message. Any ideas?
Nothing in my system is in Chinese.

ERROR: CLI prepare error: ODBC_DuckDB->PrepareStmt
Parser Error: syntax error at or near "䕓䕌呃⨠䘠佒⁍剉卉"
LINE 1: 
       䕓䕌呃⨠䘠佒⁍剉卉 ††††††††
        ^

The full odbctrace is here: https://gist.github.com/gravesti/b7fba9755712ce223b7e2284aedc0453

[kksaohun]

Hello! I'm trying to do the same, at this very moment :)

I have a bit more success, I managed to connect DuckDB to SAS, but only when running the SAS workspace (directly on Linux) with:

/sas/SASHome/SASFoundation/9.4/sas -nodms -encoding wlatin2

And I would need it in UTF-8.

What is your session encoding? What does

proc options option=encoding; run;

says for you?

---

More info about the setup that works for me:

odbcinst.ini:

[ODBC]
Trace = yes
TraceFile = /tmp/odbctrace

[DuckDB Driver]
Driver = /home/username/odbc.unixodbc/duckdb_odbc-linux-amd64/libduckdb_odbc.so
UsageCount  = 1

odbc.ini:

[DuckDB]
Driver=DuckDB Driver
Database=/tmp/duck2.db

The LIBNAME in SAS:

libname kk odbc noprompt="dsn=DuckDB;Database=/tmp/duck2.db;access_mode=read_only" autocommit=yes;

Then SQL:

proc sql;
select * from kk.foo;
quit;

But it works ONLY if proc options option=encoding; run; shows ENCODING=WLATIN2, and NOT when ENCODING=UTF-8.

[kkovacs]

Hi @gravesti , have you had any success with the above? Can you post the output of proc options option=encoding; run;?

[gravesti]

Thanks for the nudge. I have finally had success now using the current stable driver (https://github.com/duckdb/duckdb/releases/download/v1.1.0/duckdb_odbc-linux-amd64.zip).
I can confirm it works with the encoding set to wlatin2. 👍

[kkovacs]

That's great news! Thanks for the feedback.

Now I just somehow must get it to work with UTF-8, because in my language, accents are not optional... :)

[gravesti]

Is writing tables to the duckDB lib in SAS working for you? Currently I can get the error below. I've tried setting access_mode = READ_WRITE in odbc.ini.

ERROR: CLI error trying to establish connection: [unixODBC][Driver 
       Manager]Data source name not found and no default driver specified

@kkovacs @kksaohun

[kksaohun]

Hello @gravesti

you're right, I'm getting the same error. This does NOT work:

libname kk odbc noprompt="Driver=DuckDB Driver;Database=/tmp/duck2.db" autocommit=yes;

proc sql;
insert into kk.foo values (45);
quit;

proc sql;
select * from kk.foo;
quit;

The output is below -- it is a very stange way to fail, because the connection CLEARLY works, since the SELECT statement runs:

NOTE: Libref KK was successfully assigned as follows:
      Engine:        ODBC
      Physical Name:
ERROR: CLI error trying to establish connection: [unixODBC][Driver
       Manager]Data source name not found, and no default driver specified
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


                                The SAS System                               1
                                               16:14 Friday, December 13, 2024

                                          id
                                 -----------
                                          42
                                          43
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds

But some good news: it apparently works if we use the other, more convoluted way of SAS ODBC connection. This PROC SQL works:

proc sql;

connect to odbc as kk (required="Driver={DuckDB Driver};Database=/tmp/duck2.db");

select * from connection to kk (
        insert into foo values (45)
);

disconnect from kk;

quit;

It did insert the "45":

$ ../duckdb /tmp/duck2.db
v1.1.2 f680b7d08f
Enter ".help" for usage hints.
D select * from foo;
┌───────┐
│  id   │
│ int32 │
├───────┤
│    42 │
│    43 │
│    45 │
└───────┘

The one good news is that since this is the "SQL-pass-through" type of ODBC calling in SAS, at least SAS won't try to interpret the SQL -- which means we can use DuckDB's friendly dialect to write the SQL, which would not be possible with LIBNAME ODBC.

Hope this helps somehow! :)

Edit: added clarification re: SQL pass-through

[gravesti]

Thanks @kksaohun for the tip.

So what you wrote does work for me.

Creating tables also seems to work like this:

libname kk3 odbc noprompt="dsn={DuckDB};Database=/home/gravesti/duckdb-odbc/data/data2.db;" autocommit=yes;
proc sql; CONNECT USING kk3 as con; 
execute(
    CREATE TABLE mytable (i DOUBLE, b DOUBLE);
) con ;
proc print data=kk3.mytable; run;

(Occasionally i get NOTE: The SAS System stopped processing this step because of errors. but it has actually worked.)

What I can't do yet is write a table from a SAS library to the duckdb connection. In passthrough I seem to only be able to use the tables from that connection. So I can't do create table mytable2 select * from work.oldtable

[mtslaugh]

In passthrough I seem to only be able to use the tables from that connection.

@gravesti, have you tried using the connection=global option in the libname statement? Ordinarily, SAS uses separate a separate connection for passthrough, but DuckDB only supports a single read/write connection. Connection=global should force SAS to use a single connection for everything.