Attempt to configure default driver for unixODBC

[TallTed]

based on discussion at https://stackoverflow.com/questions/57084677/how-do-i-configure-a-default-driver-for-unixodbc/57102585

[theory]

Sadly none of those options work, either. I don't get it. When I specify Driver=Snowflake Driveror Driver=Default it works fine. But omit the Driver param and it complains that no default driver is specified. I'm mystified.

[theory]

Oh, and Driver=Default works only when its Driver key in odbcinst.ini is Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so.

[TallTed]

When I specify Driver=Snowflake Driveror Driver=Default it works fine. But omit the Driver param and it complains that no default driver is specified. I'm mystified.

I'm not sure what you mean by the above.

When you specify or omit the Driver param where?

You may find it helpful to review some of the ODBC Specification docs on SQLConnect(), SQLDriverConnect(), and (just informationally) SQLBrowseConnect().

That may help you confirm whether you've actually hit a bug in UnixODBC. (You might also try a different ODBC driver manager, like iODBC [maintained and supported by my employer].)

[theory]

I think this might be an issue with DBD::ODBC; this issue seems particularly strange. Like somehow SQLDriverConnect fails to load the default driver, and I can't tell why, because DBD::ODBC ignores it, tries SQLConnect, and when that fails, shows me its error.

[TallTed]

Ah, I hadn't realized you were using DBD::ODBC. I thought you were developing your own ODBC-compliant tool. Yes, this might indeed be that issue -- which should properly be considered and treated as a bug in DBD::ODBC; I don't know why the UnixODBC wouldn't have reported and helped resolve it as such.

As important as the configuration files would be how you're asking DBD::ODBC to connect -- what command are you issuing, with what arguments, exactly what ODBC connect string? Every detail counts, here.

The next phase in analysis and troubleshooting would be ODBC Tracing, to see just what calls are being issued by DBD::ODBC to the driver manager (UnixODBC), and what the driver manager is returning for them. Driver tracing can also be helpful, as it shows what calls the driver manager (UnixODBC) is issuing to the driver (Snowflake), and what the driver is returning for them. These logs would confirm whether DBD::ODBC is indeed issuing SQLDriverConnect() followed by SQLConnect()

[theory]

Doesn't look like that error-swelling issue the problem:

# DBI_TRACE=DBD sqitch status 'db:snowflake://x/y'
# On database db:snowflake://x.snowflakecomputing.com/y
non-Unicode login6_sv
dbd_db_login6
    SQLDriverConnect 'Server=x.snowflakecomputing.com;Port=443;Database=y;UID=root', 'root', 'xxxx'
    SQLDriverConnect failed:
    !!dbd_error2(err_rc=-1, what=db_login/SQLConnect, handles=(55b0cfdf8790,55b0cfdf8d90,0)
    !SQLError(55b0cfdf8790,55b0cfdf8d90,0) = (IM002, 0, [unixODBC][Driver Manager]Data source name not found, and no default driver specified)
         DESTROY for DBI::db=HASH(0x55b0cfdf57b0) ignored - handle not initialised
non-Unicode login6_sv
dbd_db_login6
    SQLDriverConnect 'Server=x.snowflakecomputing.com;Port=443;Database=y;UID=root', 'root', 'xxxx'
    SQLDriverConnect failed:
    !!dbd_error2(err_rc=-1, what=db_login/SQLConnect, handles=(55b0cfd66350,55b0cfe00a00,0)
    !SQLError(55b0cfd66350,55b0cfe00a00,0) = (IM002, 0, [unixODBC][Driver Manager]Data source name not found, and no default driver specified)
         DESTROY for DBI::db=HASH(0x55b0cfd61848) ignored - handle not initialised
[unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002)

It only calls SQLDriverConnect (but twice? weird).

[TallTed]

The DBD::ODBC docs where you found the error discussion say that it will use SQLDriverConnect() if the connection string ... contains "UID=xxx" or "PWD=xxx".

This line of your trace -- which appears to be from DBD::ODBC, not from UnixODBC -- shows a redundancy in the SQLDriverConnect() call --

SQLDriverConnect 'Server=x.snowflakecomputing.com;Port=443;Database=y;UID=root', 'root', 'xxxx'

UID=root should not be needed in the connection string, as it's passed in the following argument. Not knowing exactly what you've done to reach this point, I can't tell you exactly what to change, but the current goal would be to get something like the following in your DBD::ODBC trace --

SQLConnect 'Server=x.snowflakecomputing.com;Port=443;Database=y', 'root', 'xxxx'

[TallTed]

Note that the [ODBC Data Sources] stanza maps Data Source Names, which are stanzas in odbc.ini, as keywords, to Driver Names, which are stanzas in odbcinst.ini, as values.

Descriptions of the DSNs go in their individual stanzas, as values of the Description keyword.

[theory]

The DSN I'm passing is dbi:ODBC:Server=iovationanalysis.snowflakecomputing.com;Port=443;Database=test_sqitch;warehouse=sqitch. Username and password are passed separately to DBI->connect. Looks like DBD::ODBC is adding UID to the connection string it passes to SQLDriverConnect.

I've opened a DBD::ODBC bug report to see if @mjegh has any ideas.

[theory]

Here's the trace:

[ODBC][38][1564067201.124055][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x5561b381ea50
[ODBC][38][1564067201.126133][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x5561b381ea50
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = -6
[ODBC][38][1564067201.128151][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][38][1564067201.129719][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x5561b381ea50
[ODBC][38][1564067201.131221][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x5561b381f360
[ODBC][38][1564067201.134096][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x5561b381f360
            Window Hdl = (nil)
            Str In = [Server=iovationanalysis.snowflakecomputing.com;Port=443;Database=test_sqitch;warehouse=sqitch;UID=test_sqitch;PWD=*****************][length = 131]
            Str Out = 0x7ffd21e5dad0
            Str Out Max = 512
            Str Out Ptr = 0x7ffd21e5d8ce
            Completion = 0
[ODBC][38][1564067201.135641][SQLDriverConnect.c][1239]Error: IM002
[ODBC][38][1564067201.138041][SQLError.c][434]
        Entry:
            Connection = 0x5561b381f360
            SQLState = 0x7ffd21e5d44a
            Native = 0x7ffd21e5d444
            Message Text = 0x7ffd21e5d450
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd21e5d442
[ODBC][38][1564067201.139846][SQLError.c][471]
        Exit:[SQL_SUCCESS]
            SQLState = IM002
            Native = 0x7ffd21e5d444 -> 0
            Message Text = [[unixODBC][Driver Manager]Data source name not found, and no default driver specified]
[ODBC][38][1564067201.142230][SQLError.c][434]
        Entry:
            Connection = 0x5561b381f360
            SQLState = 0x7ffd21e5d44a
            Native = 0x7ffd21e5d444
            Message Text = 0x7ffd21e5d450
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd21e5d442
[ODBC][38][1564067201.144730][SQLError.c][471]
        Exit:[SQL_NO_DATA]
[ODBC][38][1564067201.146900][SQLError.c][514]
        Entry:
            Environment = 0x5561b381ea50
            SQLState = 0x7ffd21e5d44a
            Native = 0x7ffd21e5d444
            Message Text = 0x7ffd21e5d450
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd21e5d442
[ODBC][38][1564067201.148639][SQLError.c][551]
        Exit:[SQL_NO_DATA]
[ODBC][38][1564067201.150154][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x5561b381f360
[ODBC][38][1564067201.152204][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][38][1564067201.153622][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x5561b381ea50
[ODBC][38][1564067201.158975][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x5561b378f5e0
[ODBC][38][1564067201.160716][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x5561b378f5e0
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = -6
[ODBC][38][1564067201.164241][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][38][1564067201.166175][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x5561b378f5e0
[ODBC][38][1564067201.168122][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x5561b38253f0
[ODBC][38][1564067201.170939][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x5561b38253f0
            Window Hdl = (nil)
            Str In = [Server=iovationanalysis.snowflakecomputing.com;Port=443;Database=test_sqitch;warehouse=sqitch;UID=test_sqitch;PWD=*****************][length = 131]
            Str Out = 0x7ffd21e5dad0
            Str Out Max = 512
            Str Out Ptr = 0x7ffd21e5d8ce
            Completion = 0
[ODBC][38][1564067201.173891][SQLDriverConnect.c][1239]Error: IM002
[ODBC][38][1564067201.176773][SQLError.c][434]
        Entry:
            Connection = 0x5561b38253f0
            SQLState = 0x7ffd21e5d44a
            Native = 0x7ffd21e5d444
            Message Text = 0x7ffd21e5d450
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd21e5d442
[ODBC][38][1564067201.179981][SQLError.c][471]
        Exit:[SQL_SUCCESS]
            SQLState = IM002
            Native = 0x7ffd21e5d444 -> 0
            Message Text = [[unixODBC][Driver Manager]Data source name not found, and no default driver specified]
[ODBC][38][1564067201.185889][SQLError.c][434]
        Entry:
            Connection = 0x5561b38253f0
            SQLState = 0x7ffd21e5d44a
            Native = 0x7ffd21e5d444
            Message Text = 0x7ffd21e5d450
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd21e5d442
[ODBC][38][1564067201.188864][SQLError.c][471]
        Exit:[SQL_NO_DATA]
[ODBC][38][1564067201.191278][SQLError.c][514]
        Entry:
            Environment = 0x5561b378f5e0
            SQLState = 0x7ffd21e5d44a
            Native = 0x7ffd21e5d444
            Message Text = 0x7ffd21e5d450
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd21e5d442
[ODBC][38][1564067201.193614][SQLError.c][551]
        Exit:[SQL_NO_DATA]
[ODBC][38][1564067201.195081][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x5561b38253f0
[ODBC][38][1564067201.196486][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][38][1564067201.198000][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x5561b378f5e0

[theory]

Contrast when I add Driver=Snowflake to the DSN:

[ODBC][45][1564067441.431656][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x5566e08f1550
[ODBC][45][1564067441.433357][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x5566e08f1550
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = -6
[ODBC][45][1564067441.435268][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][45][1564067441.436985][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x5566e08f1550
[ODBC][45][1564067441.438350][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x5566e08f1e60
[ODBC][45][1564067441.440368][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x5566e08f1e60
            Window Hdl = (nil)
            Str In = [Server=iovationanalysis.snowflakecomputing.com;Port=443;Database=test_sqitch;warehouse=sqitch;Driver=Snowflake;UID=test_sqitch;P...][length = 148]
            Str Out = 0x7ffd604e5a10
            Str Out Max = 512
            Str Out Ptr = 0x7ffd604e580e
            Completion = 0
        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

        DIAG [28000] IP 208.252.28.90 is not allowed to access Snowflake.  Contact your local security administrator.

[ODBC][45][1564067442.314303][SQLDriverConnect.c][1479]
        Exit:[SQL_ERROR]
[ODBC][45][1564067442.322440][SQLError.c][434]
        Entry:
            Connection = 0x5566e08f1e60
            SQLState = 0x7ffd604e538a
            Native = 0x7ffd604e5384
            Message Text = 0x7ffd604e5390
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd604e5382
[ODBC][45][1564067442.325782][SQLError.c][471]
        Exit:[SQL_SUCCESS]
            SQLState = 28000
            Native = 0x7ffd604e5384 -> 390420
            Message Text = [IP 208.252.28.90 is not allowed to access Snowflake.  Contact your local security administrator.]
[ODBC][45][1564067442.328577][SQLError.c][434]
        Entry:
            Connection = 0x5566e08f1e60
            SQLState = 0x7ffd604e538a
            Native = 0x7ffd604e5384
            Message Text = 0x7ffd604e5390
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd604e5382
[ODBC][45][1564067442.330785][SQLError.c][471]
        Exit:[SQL_NO_DATA]
[ODBC][45][1564067442.332572][SQLError.c][514]
        Entry:
            Environment = 0x5566e08f1550
            SQLState = 0x7ffd604e538a
            Native = 0x7ffd604e5384
            Message Text = 0x7ffd604e5390
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd604e5382
[ODBC][45][1564067442.334324][SQLError.c][551]
        Exit:[SQL_NO_DATA]
[ODBC][45][1564067442.336525][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x5566e08f1e60
[ODBC][45][1564067442.338421][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][45][1564067442.340063][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x5566e08f1550
[ODBC][45][1564067442.344186][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x5566e08f1550
[ODBC][45][1564067442.346488][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x5566e08f1550
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = -6
[ODBC][45][1564067442.348407][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][45][1564067442.350184][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x5566e08f1550
[ODBC][45][1564067442.351549][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x5566e0a67920
[ODBC][45][1564067442.354768][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x5566e0a67920
            Window Hdl = (nil)
            Str In = [Server=iovationanalysis.snowflakecomputing.com;Port=443;Database=test_sqitch;warehouse=sqitch;Driver=Snowflake;UID=test_sqitch;P...][length = 148]
            Str Out = 0x7ffd604e5a10
            Str Out Max = 512
            Str Out Ptr = 0x7ffd604e580e
            Completion = 0
        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

        DIAG [28000] IP 208.252.28.90 is not allowed to access Snowflake.  Contact your local security administrator.

[ODBC][45][1564067443.589997][SQLDriverConnect.c][1479]
        Exit:[SQL_ERROR]
[ODBC][45][1564067443.594840][SQLError.c][434]
        Entry:
            Connection = 0x5566e0a67920
            SQLState = 0x7ffd604e538a
            Native = 0x7ffd604e5384
            Message Text = 0x7ffd604e5390
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd604e5382
[ODBC][45][1564067443.597749][SQLError.c][471]
        Exit:[SQL_SUCCESS]
            SQLState = 28000
            Native = 0x7ffd604e5384 -> 390420
            Message Text = [IP 208.252.28.90 is not allowed to access Snowflake.  Contact your local security administrator.]
[ODBC][45][1564067443.601043][SQLError.c][434]
        Entry:
            Connection = 0x5566e0a67920
            SQLState = 0x7ffd604e538a
            Native = 0x7ffd604e5384
            Message Text = 0x7ffd604e5390
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd604e5382
[ODBC][45][1564067443.603356][SQLError.c][471]
        Exit:[SQL_NO_DATA]
[ODBC][45][1564067443.606606][SQLError.c][514]
        Entry:
            Environment = 0x5566e08f1550
            SQLState = 0x7ffd604e538a
            Native = 0x7ffd604e5384
            Message Text = 0x7ffd604e5390
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd604e5382
[ODBC][45][1564067443.608477][SQLError.c][551]
        Exit:[SQL_NO_DATA]
[ODBC][45][1564067443.610728][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x5566e0a67920
[ODBC][45][1564067443.614531][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][45][1564067443.618594][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x5566e08f1550

Not seeing much of a hint in these traces as to why it's not finding the default. :-(

[mjegh]

I haven't had a chance to look at this properly but you need to bear a few things in mind. (1) there are 2 ways to connect, SQLConnect (the old way which only accepts a DSN, username, password) and SQLDriverConnect (which takes a full connection string). In SQLDriverConnect you have to give it either DRIVER=something or DSN=something or something else I've forgotten. (2) DBI doesn't allow any specification of what method DBD::ODBC should use, so D::O has to guess want is best. (3) D:O tries hard not to break old code and so when it added SQLDriverConnect (for more flexibility) there had to be some way of it deciding whether to use SQLConnect or SQLDriverConnect (I believe it looks for DRIVER= or DSN= but I'd have to check again). (4) SQLDriverConnect returns a string which is supposed to be what is required to pass back into another SQLDriverConnect to connect to the same database/system (people rely on this) there are IIFC, some complications if the UID/PWD are omitted. D:O supports passing the connection string back to Perl for this purpose. (5) UID/PWD are only defined for SQLDriverConnect, they are meaningless in SQLConnect e.g., if you pass UID=xxx as the username to SQLConnect it won't work. Hence, if your connection string includes UID/PWD D:O assumes you want to use SQLDriverConnect. (6) ODBC drivers are funny things (I know, I've written a few). All you NEED to pass to SQLDriverConnect as far as the ODBC driver manager is concerned is ENOUGH for it to identify the driver and then everything in the connection string is passed to the driver.

There is probably loads I've forgotten about this as I haven't had much reason to change D:O significantly in a lot of years and I have to admit I've not yet read all of the above comments properly. This is more of a head up that I've briefly seen this issue, here are pointers and I'll try and come back to it.

I believe there are sections in the FAQ about this. I'll try and come back to this over the weekend if I can. As for the password issue being visible (the original issue you reported in D:O, I will look again at that as well.

Hope this helps a little for now.

[mjegh]

You might find https://www.easysoft.com/developer/interfaces/odbc/linux.html and the internal links to Perl useful (written a long time ago but problably mostly still holds true). I have worked on unixODBC and my colleague still looks after it so I'm sure we can get to the bottom of the problem. As this issue is not in DBD::ODBC (it is in sqitchers) if this is a different issue to the password appearing in the trace, e.g., a connection problem, either post as example of what you pass to DBI->connect, contents of odbc.ini and odbcinst.ini files and post it on github in DBD::ODBC or mail it to me and I'll take a look. I'm having a hard time following this issue because it is an attempt to fix something not clearly enough defined for me.

[TallTed]

@theory - Given all of the above... It seems worth asking why you want to do this (rely on the Default driver and/or DSN), and considering whether the current level of effort is worth it.

[mjegh]

ah, your problem is you want to use the unixODBC defined default driver in the odbcinst.ini? via DBD::ODBC?

[mjegh]

I thought I'd written something about this in https://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html but it doesn't mention default driver so that isn't going to help you. I'll come back to you on that.

[mjegh]

Here is a quick (and I mean quick, summary based on a quick look at DBD::ODBC code). As far as DBD::ODBC is concerned (and there is a lot of history in this some before my time and some to keep backward compatibility):

1. You pass the string dbi:ODBC:something to DBI, and it passes something to DBD::ODBC
2. IF the string is longer than SQL_MAX_DSN_LENGTH your string will be passed to SQLDriverConnect.
3. IF you string contains DSN= or DRIVER= your string will be passed to SQLDriverConnect
4. rightly or wrongly, if you pass a username or password to DBI->connect and DBD::ODBC elects to call SQLDriverConnect your username/password will be appended to the connection string as UID=username;PWD=password. Actually, I could argue rightly, as what DBD::ODBC is trying to do is mangle DBI sematics to ODBC but I could argue it the other way around.
5. If you pass a dbname to DBI->connect same happens as in 4.
6. DBD::ODBC then calls SQLDriverConnect with the string it has be given and the additions it has added from arguments to DBI->connect.
7. If this fails it falls back on passing your DBI->connect string, username and password as the DSN, username and password to SQLConnect for backwards compatibility.

After that, it is up to unixODBC or MS driver manager or iODBC what happens and after that up to your ODBC driver. So if you take the strings passed to SQLDriverConnect/SQLConnect by DBD::ODBC you should be able to work out what the driver manager does and if those strings look ok, the issue is probably in your ODBC config e.g. odbc.ini, odbcinst.ini.

[mjegh]

I just checked with Nick Gorham (of unixODBC) and he says a driver = /path/to/my/driver in the odbcinst.ini (not in a driver section) will be used as the DEFAULT driver. e.g.,

Driver=/usr/local/easysoft/sqlserver/lib/libessqlsrv.so

[anotherdriver] Driver=/path/to/another/named/driver

[theory]

ah, your problem is you want to use the unixODBC defined default driver in the odbcinst.ini? via DBD::ODBC?

Yes, because I’m making a Docker image that’s solely for using Sqitch with Snowflake. There is no other driver in the image (aside from whatever unixODBC includes), and users will use the image only for Snowflake deployments. So it seems silly for all of the URLs to include Driver=Snowflake. It should never be any other driver.

I’ll give that non-sectional Driver spec a try.

[theory]

I changed /etc/odbcinst.ini to

Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so

[snowflake]
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so

Sadly that did not work.

[mjegh]

Ok, can you past odbcinst.ini (if different from above), what you passed to DBI->connect and what the error was please.

[theory]

odbcinst.ini:

[ODBC Drivers]
Snowflake=Installed

[Default]
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so

[Snowflake]
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so

DBI->connect:

dbi:ODBC:Server=iovationanalysis.snowflakecomputing.com;Port=443;Database=test_sqitch;warehouse=sqitch

Error:

[unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002)

[mjegh]

ok, can you change either the connection string to include DRIVER=Default OR simply add to the top of the odbcinst.ini file:

Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so

[mjegh]

if one doesn't work, try the other - sorry should have mentioned this.

[TallTed]

@mjegh --

I just checked with Nick Gorham (of unixODBC) and he says a driver = /path/to/my/driver in the odbcinst.ini (not in a driver section) will be used as the DEFAULT driver. e.g.,

Driver=/usr/local/easysoft/sqlserver/lib/libessqlsrv.so

[anotherdriver]
Driver=/path/to/another/named/driver

That makes no sense, and violates the ODBC spec. (Lest there be confusion, when they refer to the "Default subkey" in the Windows Registry, that equates to the [Default] section header in the odbcinst.ini textfile.)

The structure of this odbcinst.ini file typically starts with an [ODBC Drivers] section (though this section could be anywhere in the file), into which a subsequent bare Driver=/path/to/driver would fall (because the section ends with the next [section header] line; blank lines have no meaning in this structure).

The only way Nick's suggestion might work is if the first line of the odbcinst.ini file was this bare Driver=/path/to/driver line, which was then followed by the [ODBC Drivers] and other sections -- but this still does not conform to the spec.

[TallTed]

@theory --

So it seems silly for all of the URLs to include Driver=Snowflake. It should never be any other driver.

This is a lot of work to try to save a few dozens or even hundreds of characters in some strings/config-files.

As "silly" as it may be to specify Driver=Snowflake (or Driver=Default) every time, that is the way the ODBC ecosystem was designed to, and does, work.

[theory]

if one doesn't work, try the other - sorry should have mentioned this.

Sorry, one of the other what?

[mjegh]

Change the connection string to add DRIVER=Default (based on your current odbcinst.ini config) and IF that doesn't work (and I don't see why not) ADD

Driver=/usr/local/easysoft/sqlserver/lib/libessqlsrv.so

to your odbcinst.ini file and do not specify DRIVER=Default in the connection string.

[theory]

As "silly" as it may be to specify Driver=Snowflake (or Driver=Default) every time, that is the way the ODBC ecosystem was designed to, and does, work.

Perhaps so, and if this can't be made to work, that's fine. I'm just surprised to find that when it complains about no default driver there is no way to specify one that I can find. Seems like a bug.

[mjegh]

I feel your frustration and understand it - I've had similar issues myself in the past. I'm happy to perservere if you are for now.

[theory]

Yes, it works if I specify Driver=Default in the DSN and specify the path to the driver. But that's not the special default behavior suggested by the docs and the error message. For example, if I specify, as the spec suggest:

[ODBC Drivers]
Snowflake=Installed

[Default]
Driver=Snowflake

[Snowflake]
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so

It fails because it can't load the library Snowflake. IOW, it's not the default, it's just another driver spec that happens to be named "Default".

[mjegh]

ah, ok, that is the first time you've mentioned that scenario (that I have seen). I'm happy to pass that over to Nick and get a comment from him but when I asked him before he said the default driver is Driver=/path/to/my/driver without a driver section and I believe that is not HIS choice, but what Microsoft decided in the first place (bear in mind their settings are in the registry not a config file). I'll ask him now before he goes home.

[TallTed]

@mjegh -

I believe that is not HIS choice, but what Microsoft decided in the first place (bear in mind their settings are in the registry not a config file)

As I said above... Microsoft uses a Registry subkey of "Default" which maps/equates to a [Default] section in the INI file. Older versions of Windows had both text files and Registry entries, and even older versions of Windows had only the text files, where you could see this in action.

[mjegh]

@TallTed You could be right, I'm not completely sure right now but one thing I know for sure is what works and given the purpose of the change in question there isn't going to be another solution in the near future given it is a docker image and reliant on packaged packages. I was working on the basis of helping someone out and solving an issue and I think we got there, whether it is on "specification" is a whole new ball park. If you or David wants to persue the "this isn't to specification" then by means go ahead.

[theory]

Bare driver not under a key does not work:

/repo# cat /etc/odbcinst.ini 
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so
/repo# sqitch status 'db:snowflake://test_sqitch@iovationanalysis/test_sqitch?warehouse=sqitch'
# On database db:snowflake://test_sqitch:@iovationanalysis.snowflakecomputing.com/test_sqitch?warehouse=sqitch
[unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002)

[mjegh]

@theory, you have to put DRIVER=Default in I belive. Whether that is technically correct or not.

[theory]

# cat /etc/odbcinst.ini 
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so
# sqitch status 'db:snowflake://test_sqitch@iovationanalysis/test_sqitch?warehouse=sqitch;Driver=Default'
# On database db:snowflake://test_sqitch@iovationanalysis.snowflakecomputing.com/test_sqitch?warehouse=sqitch;Driver=DEFAULT
[unixODBC][Driver Manager]Can't open lib 'DEFAULT' : file not found (SQL-01000)

[mjegh]

Appologies:

[DEFAULT]

Driver=/path/to/driver.so

and then I think you need DRIVER=DEFAULT in the dbi connection string to force DBD::ODBC to use SQLDriverConnect but that may not be necessary.

[mjegh]

@TallTed

The structure of this odbcinst.ini file typically starts with an [ODBC Drivers] section

I spoke with Nick (of unixODBC) and his basic comment was "thats a hangover from the windows 3.1 and Openlink implementation. unixODBC doesnt need that list of the drivers in the file, it just looks at the drivers in the file. May not be perfect for some value of perfect but there you go."

He also said "Generally, I would suggest never using default anything, its opening a world of confusion and pain, especially when you have user and system odbcinst.ini and odbc.ini on some platforms and 32 and 64 bit versions and possibly shared ini files to add to the mix."

I hope that clears something up. Of course, unixODBC is OS so if you don't like it....

[theory]

Yes, I have tried that, and it works, but that's not setting a default. That's just another driver that happens to be named "default". I don't get that that's what unixODBC means when it says "no default driver specified."

[TallTed]

the windows 3.1 and Openlink implementation

a/k/a the standard-compliant implementations. Microsoft wrote and maintains the ODBC standard, with which iODBC (which I guess would be what Nick meant by the "OpenLink implementation") complies.

Non-compliant odbcinst.ini (i.e., lacking the [ODBC Drivers] stanza) are still handled by iODBC, because many tools and users violate the standard when editing this file, but the violation is clear.

[theory]

Well maybe I should try iODBC. I agree about the world of pain for the general case, but this is essentially shipping a database app, not a driver management system.

[TallTed]

@theory - You may not find much difference with iODBC in the mix, because of the ODBC API calls being issued by DBD::ODBC, and the arguments it's applying. I would strongly recommend that you just use the Driver=Snowflake connection string formulation, as the minimal space savings you will achieve by leaving it out do have the potential to introduce confusion down the line, for your users if not for you.

[theory]

@TallTed I wish there were standard driver names, then I could just make a default in my app. But no, someone could change the name to "SNWFLK" or something and it wouldn't work. I could maybe patch Sqitch in this Docker image to assign a Driver to the DSN if it doesn't have one, since inside the image there is only one, anyway.

[TallTed]

@theory - Are you not putting the driver in the image, along with your app, and the driver manager? And building these config files? The name in the odbcinst.ini is arbitrary; the most important piece in the driver's description/registration there is the Driver=/path/to/library mapping... If the name change you suggest is a real problem, it's no less a problem than would be someone declaring a different [Default] driver.

[theory]

Yes, but the URLs are used outside the image, passed to it.

[TallTed]

Yes, but the URLs are used outside the image, passed to it.

All the more reason to include the driver name you specify in what should be include in the URLs used with your tool.

The more pieces you add about how this thing (which I've never used) is meant to work, the more I think you're currently working to solve the wrong problems -- including at least some problems which are likely to never exist -- by trying to make the [Default] driver key work in ways it was never really meant to.

[theory]

I think I've been confused. I've been assuming it was possible to set up a default driver in the ODBC configuration. The docs @TallTed linked to in his SO answer seem to suggest as much. But through all this discussion, I'm suspecting that I may have been misunderstanding the error:

Data source name not found, and no default driver specified

I had been assuming that this meant that no default driver was configured on the host. But I think now perhaps, what it actually means is "Cannot find the DSN in the URL, and there was no Driver param to fall back on, either". IOW, the Driver param specifies the default driver if the DSN can't be found. That interpretation is consonant with the findings here, I think. It's a little disappointing, but I think it could be fixed by an improved error message, or at least some sort of documentation update to explain what it means.

[theory]

@mjegh Can you confirm the above interpretation with Nick Gorham? Thanks!

[mjegh]

I'll try when I see him next week.