Unable to connect to SQLite using SQLiteODBC on MacBook Air M1

[Promisey]

Unable to connect to SQLite using SQLiteODBC on MacBook Air M1

[HughWilliams]

I presume you installed the Home Brew macOS SQLite 0.9998 ODBC driver ?

Which we have done on both Silicon M1 and Intel machines, both of which work with both the unixODBC isql and iODBC odbctest client applications:

~/.odbc.ini

ODBC Data Sources]
...
sqlite		= SQLite3

[sqlite]
Driver = SQLite3

~/.odbcinst.ini

[ODBC Drivers]
...
SQLite3 = Installed

[SQLite3]
Driver          = /opt/homebrew/lib/libsqlite3odbc.so 

$ file /opt/homebrew/lib/libsqlite3odbc.so
/opt/homebrew/lib/libsqlite3odbc.so: Mach-O 64-bit dynamically linked shared library arm64
$ iodbctest sqlite
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0815.0106
Driver: 0.9998 (sqlite3odbc.so)
SQL>create table t1 (c1 int)
Statement executed. 0 rows affected.
SQL>insert into t1 values (1)
Statement executed. 1 rows affected.
SQL>select * from t1 
c1         
-----------
1          
 result set 1 returned 1 rows.
SQL>quit
Have a nice day.
$ isql sqlite
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Now I note your driver file name is /opt/homebrew/lib/libsqlite3odbc.dylib whereas in our test the file name is /opt/homebrew/lib/libsqlite3odbc.so ie .so rather than .dylib the latter being the extension used on the Intel machine. Thus might the driver you are using be Intel rather than M1 , which can be checked by running the command:

file /opt/homebrew/lib/libsqlite3odbc.dylib

[Promisey]

Thank you for your reply and the detailed information provided.
I did indeed follow the instructions on the provided webpage and used the Brew command to install the SQLite 0.9998 ODBC driver.
And I have followed your suggestions and updated my configuration files, then attempted to reconnect, but unfortunately, the issue persists. Below are my updated configuration files and the error I encounter when executing the iodbctest sqlite command:

[mysqlitedb]
Description=My SQLite sample database
Driver=SQLite3
Database=/Users/fox/Documents/TablePlus/Redail.sqlite3

[ODBC Data Sources]
sqlite		= SQLite3

[sqlite]
Driver = SQLite3

~/.odbcinst.ini:

[ODBC Drivers]
Amazon Redshift              = Installed
Simba Spark ODBC Driver      = Installed
Simba SQL Server ODBC Driver = Installed
SQLite3 = Installed

[Amazon Redshift]
Description = Amazon Redshift ODBC Driver
Driver      = /opt/amazon/redshift/lib/libamazonredshiftodbc.dylib

[Simba Spark ODBC Driver]
Driver = /Library/simba/spark/lib/libsparkodbc_sbu.dylib

[Simba SQL Server ODBC Driver]
Driver = /Library/simba/sqlserverodbc/lib/libsqlserverodbc_sbu.dylib

[SQLite3]
Driver = /opt/homebrew/lib/libsqlite3odbc.so

When executing the iodbctest sqlite command, I encounter the following error:

iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.1521.0607
1: SQLDriverConnect = [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) SQLSTATE=IM002
1: ODBC_Connect = [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) SQLSTATE=IM002

Additionally, I would like to add that when using iODBC Administrator, the driver list does include SQLite3. However, when attempting to add a new User DSN, an error occurs stating, "An error occurred when trying to add the DSN: Request failed."

I noticed that there might be an issue with the driver file path "Driver = /opt/homebrew/lib/libsqlite3odbc.so" as it appears to be an alias pointing to the original file "libsqlite3odbc-0.9998.dylib". I'm not sure if this is the root cause, but I suspect the issue might lie here.

I would greatly appreciate any further suggestions or ideas that you may have. Thank you once again for your assistance!

[smalinin]

@Promisey

If you want to use isql and unixodbc, the content in .odbc.ini must be looks like(ie looks to value for Driver) :

[sqlite]
Driver = /opt/homebrew/lib/libsqlite3odbc.so 
Database=/Users/uid/mytest.sqlite

These settings will works also with iODBC, but iodbctest must be exec with command:

iodbctest DSN=sqlite

If you want to use .odbc.ini with your settings:

[sqlite]
Driver = SQLite3 
Database=/Users/sergei/2/favicons.sqlite

It will work ONLY with iODBC and it will not work with UnixODBC.
But iodbctest exec command must be:

iodbctest DSN=sqlite

[TallTed]

@Promisey — Please let us know if the info above was sufficient to get you rolling, or if you need further assistance. If you do need more help, please let us know whether the SQLLite driver is the only one that's not working for you — that is, please confirm whether you're able to use any or all of the other ODBC Drivers listed in the odbcinst.ini.

[Promisey]

@TallTed
Deer Contributor,
First of all, thank you very much for your response and help! I'm a newbie and I hope my questions can get your attention and help. Following the suggestions of the previous two seniors, I tried to modify the configuration files as follows:

～/.odbcinst.ini

[ODBC Drivers]
PostgreSQL Unicode           = Installed
Simba Spark ODBC Driver      = Installed
Simba SQL Server ODBC Driver = Installed
SQLite3 = Installed

[PostgreSQL Unicode]
Description = PostgreSQL ODBC driver
Driver      = /usr/local/lib/psqlodbcw.so

[Simba Spark ODBC Driver]
Driver = /Library/simba/spark/lib/libsparkodbc_sbu.dylib

[Simba SQL Server ODBC Driver]
Driver = /Library/simba/sqlserverodbc/lib/libsqlserverodbc_sbu.dylib

[SQLite3]
Driver = /opt/homebrew/lib/libsqlite3odbc.so

～/.odbc.ini

[sqlite]
Description=My SQLite sample database
Driver=/opt/homebrew/lib/libsqlite3odbc.so
Database=/Users/fox/Documents/TablePlus/Redail.sqlite3

After executing the iodbctest DSN=sqlite command, it seems to be successful, with the output showing as follows:

However, I found that I must use the ATTACH DATABASE command in the SQL command line to reconnect to the specified database file. This makes me feel that the database file path specified in the odbc.ini file does not seem to work. Is this normal?

Then, I tried to use the isql -v sqlite command but encountered the following error:

[01000][unixODBC][Driver Manager]Can't open lib '/opt/homebrew/lib/libsqlite3odbc.so' : file not found
[ISQL]ERROR: Could not SQLConnect

In addition, in the iODBC Administrator64, the driver list indeed added the SQLite3 option, but the User DSN list and System DSN list are still empty. When I tried to add a new DSN, the following error occurred:

An error occurred when trying to add the DSN : Request failed

I also noticed that both of these files are aliases, pointing to ~/Library/ODBC/odbc.ini and ~/Library/ODBC/odbcinst.ini. The content of the target files is as above. I'm not sure if this information is useful.

Thank you once again for your attention and help. I hope to resolve these issues. Looking forward to your reply!
Sincerely,
Promisey

[Promisey]

Dear Developer,

I would like to add some additional information regarding my issue. My ultimate goal is to connect to a SQLite database on a MacBook M1 using Tableau Desktop. Currently, in the ODBC page within Tableau, the DSN list is empty and there is no SQLite3 option in the driver list.

I appreciate anyone's assistance and look forward to any reply!

Sincerely

[HughWilliams]

You have been able to make a connection with the iodbctest program based on your output above, so the SQLite ODBC driver does appear to be working.

It also appears you have updated the SQLite ODBC driver as it now has a ".so" file extension rather than ".dylib" ? I still would like to see file output of the driver ie

file /opt/homebrew/lib/libsqlite3odbc.so

I don't know what you mean by the ATTACH DATABASE command, which I assume is a SQLite specific command you are running in iodbctest to connect to the correct database ?

In iodbctest what is return when the iodbctest tables command is run before and after running the ATTACH DATABASE command to see what tables are available in both cases ?

If you want the SQLite driver to be visible in the iODBC Administrator UI then you need to specify a setup dialog name to use by pointing to the driver shared library itself in the odbcinst.ini file ie

[ODBC Drivers]
...
SQLite3 = Installed
...

[SQLite3]
Driver = /opt/homebrew/lib/libsqlite3odbc.so
Setup = /opt/homebrew/lib/libsqlite3odbc.so

[Promisey]

Dear maintainers and contributors,

First of all, I'd like to apologize if my previous response caused any confusion. I'm a novice in this area, and I'm sincerely seeking your help.

Here's the output of the command file /opt/homebrew/lib/libsqlite3odbc.so:

/opt/homebrew/lib/libsqlite3odbc.so: Mach-O 64-bit dynamically linked shared library arm64

This file is a symlink to /opt/homebrew/Cellar/sqliteodbc/0.9998/lib/libsqlite3odbc-0.9998.dylib.

When I run the iodbctest DSN=sqlite command and enter the tables SQL command, I see the following:

As shown in the image, it seems to be an empty database file, not the SQLite database path I configured in ~/.odbc.ini.

Regarding the "ATTACH DATABASE" command, the full command I used is:

ATTACH DATABASE '/Users/fox/Documents/TablePlus/Redail.sqlite3' AS 'db'

My concern is that I must use this command to connect to the specified database, which makes me wonder if the database path specified in ~/.odbc.ini is not working. As a reminder, I used the iodbctest DSN=sqlite command, and the SQLite database path has already been defined in ~/.odbc.ini. Please refer to my previous reply for the contents of this file.

Additionally, after configuring the setup in the odbcinst.ini file, the iODBC Administrator UI still does not display the configuration. When I try to add a new DSN, it shows an error, as demonstrated in this screen recording:

2023-04-26_10-05-44.mp4

The iODBC Administrator UI even crashes and exits, as shown at the end of the following screen recording:

2023-04-26_10-01-33.mp4

I genuinely appreciate your patience in responding to my questions. My ultimate goal is to connect to an SQLite database file using Tableau through ODBC. I am grateful for any assistance anyone can provide.

Best regards!

[HughWilliams]

Looking at the screen recordings the iODBC Administrator being run is for Intel 64 , whereas you are running on an M1 (arm64) machine with an M1 (arm64) SQLite ODBC Driver.

As per this OpenLink ODBC Driver and iODBC usage on Apple Silicon M1 machines post, on an M1 machine you should be running the /Applications/iODBC/iODBC\ Administrator.app/Contents/MacOS/iODBC\ Administrator iODBC Administrator rather than /Applications/iODBC/iODBC\ Administrator64.app/Contents/MacOS/iODBC\ Administrator64 which is the original Intel 64 only iODBC Administrator.

Note you also need to ensure the Tableau application is an M1 (arm64) binary given your SQLite driver is an M1 (arm64) binary. As if Tableau is an Intel 64 binary then you will need a SQLite Intel 64 ODBC driver to work with it.

[Promisey]

I apologize for the confusion earlier as I mistakenly used the wrong UI software. However, I am experiencing the same issue with iODBC Administrator (Apple Silicon) as shown in the attached screen recording:

2023-04-28_13-35-38.mp4

Additionally, I have installed the M1 (arm64) supported version of Tableau Desktop. I followed the official website's instructions and filled out the necessary information to obtain the trial version. The application opens and runs correctly on my machine. However, I have not been able to find a way to connect to my SQLite database, as previously mentioned.

Thank you for your time and understanding.

Kind regards.

[HughWilliams]

Can you provide the results of running the following command from a terminal session started as the user (fox ???) the iODBC Admin UI runs as on the M1 machine:

ls -l ~/.odbc.ini 
ls -l ~/.odbcinst.ini 
ls -l ~/Library/ODBC/odbc.ini
ls -l ~/Library/ODBC/odbcinst.ini 
ls -l /Library/ODBC/odbc.ini
ls -l /Library/ODBC/odbcinst.ini

[Promisey]

Yes, my username is "fox" and following your instructions to execute the command, I received the following output:

Thank you in advance for your help and support.
Sincerely.

[HughWilliams]

OK, so the permissions on the .odbc.ini file will allow it to be written to and is what I wanted to confirm, along with the other related ODBC config files.

Can you please confirm the version of iODBC Administrator and components, which is available in the About tab of the UI ?

[Promisey]

[pkleef]

Development has found an issue with a setting on the macOS UI for the generic setup dialog which caused key/value pairs not to be saved.

[pkleef]

Please download and install the latest version of the iODBC Driver Manager v3.52.16 for macOS 11 and newer which fixes the UI issues you reported.

https://github.com/openlink/iODBC/releases/download/v3.52.16/iODBC-SDK-3.52.16-macOS11.dmg

[Promisey]

Thank you for your update. Unfortunately, it seems that the issue still persists after the upgrade as shown in the attached screen recording:
https://user-images.githubusercontent.com/15836013/236084758-bc779725-20b5-48e4-ac7b-672b58be5187.mp4

And I'm not sure what the cause might be. I followed your provided link for downloading and directly installed the upgrade without uninstalling the original application or rebooting my device.

I appreciate any further assistance or suggestions you may have to help resolve this issue.

Sincerely

[HughWilliams]

What version of macOS are you running, as the "Request failed" error writing the DSN implies a possible permissions error and so even though the ~/.odbc.ini has unix file permissions to be written to there maybe some higher level Apple entitlement requirement preventing access ?

Also to move forward in terms of connecting to your target tableau client application, if you manually configure the SQLite ODBC DSN in the ~/.odbc.ini user DSN file is the DSN visible in tableau and are you able to connect ?

[Promisey]

I appreciate your response and would like to provide you with the requested information. I'm using the following system:

As mentioned earlier, I've followed your guidance and entered the necessary information in the configuration file, and it remains unchanged.

Regarding the ODBC options in Tableau, I couldn't find any related information for SQLite in the driver list or the DSN list.

Please let me know if you need any additional information or if there's anything else I can do to help resolve this issue. Thank you for your assistance!

Best regards.

[HughWilliams]

Actually looking again at the file permissions of the ODBC files you provided previously, although the ~/.odbc.ini & ~/.odbcinst.ini sym-linked files are owned by and have write permissions for the fox user, the physical ~/Library/ODBC/odbc.ini and ~/Library/ODBC/odbc.ini files they are sym-linked to are owned by root which with no write permissions for the fox , hence they cannot be written to by the fox user, see:

So you need to change the owner of the physical ~/Library/ODBC/odbc.ini and ~/Library/ODBC/odbc.ini files to be that of the fox user you are running as ie

-rw- -r- -r- root staff  /Users/home/fox/Library/ODBC/odbc.ini  

should be:

-rw- -r- -r- fox staff  /Users/home/fox/Library/ODBC/odbc.ini 

[Promisey]

I'm glad to see that there has been some progress. I was able to add a new DSN using the iODBC administrator UI after executing the following two commands to modify file permissions:

sudo chown fox:staff ~/Library/ODBC/odbc.ini
sudo chown fox:staff ~/Library/ODBC/odbcinst.ini

Additionally, I was able to connect to the specified database file using the iodbctest DSN="**" command.

However, I encountered an issue when trying to connect to the newly created DSN using Tableau's ODBC option. The error message is as follows:

I also noticed that the SQLite3 option is missing from the list of drivers in Tableau. I suspect this might be related to permissions too, but I'm not sure what to do next.

I tried to delete the SQLite driver and reconfigure it using the iODBC administrator UI, but I encountered another issue, as demonstrated in the following screen recording:
https://user-images.githubusercontent.com/15836013/236367757-3763ecbf-11f9-40bb-b42b-65064b6a1685.mp4

I would sincerely appreciate any guidance or suggestions on how to resolve these issues.

Thank you!

[HughWilliams]

As indicated in #86 (comment) you needed to ensure the tableau application is an arm64 binary to work with the arm64 only SQLite ODBC driver binary home-brew provides you with, which you indicated was the case ? But as per your screenshot Tableau is an x86_64 Intel binary hence the error in your screenshot.

Thus you need a x86_64 Intel SQLite ODBC Driver binary to work with your Tableau is an x86_64 Intel binary application which is running under Rosetta 2 on the machine.

Or as our iODBC Administrator and ODBC Drivers are built, a universal binary SQLite ODBC Driver (with arm64 and x86_64 modules included in the single binary) should be used which would then work dynamically against arm64 or x86_64 ODBC applications. But for some reason homebrew do not build universal binaries, which they should as it is not a difficult process as they do build both separately and just need to bundled them together ???

In fact @pkleef built a SQLite ODBC Driver universal binary for macOS (arm64/M1 or x86_64/Intel) and should enable connection to your Tableau x86_64 application.

$ file ./libsqlite3odbc.so
./libsqlite3odbc.so: Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit dynamically linked shared library x86_64] [arm64]
./libsqlite3odbc.so (for architecture x86_64):	Mach-O 64-bit dynamically linked shared library x86_64
./libsqlite3odbc.so (for architecture arm64):	Mach-O 64-bit dynamically linked shared library arm64

[Promisey]

I would like to sincerely thank you for your guidance. Indeed, the Tableau version I downloaded is for Intel 64-bit and runs through Rosetta 2. I just found out that they don't support ARM yet, and I didn't realize this before. I apologize for any confusion.

Since Tableau does not officially support ARM, it seems that the only option is to use ODBC Intel 64bit which can be running on macOS. I appreciate the information you provided about the SQLite ODBC Driver universal binary for macOS (arm64/M1 or x86_64/Intel). However, I noticed that the download link seems to be problematic and I am unable to download the file directly. Could you please provide the correct file address?

Thank you very much for your assistance.

[TallTed]

@Promisey — If your browser isn't cooperating, you should be able to download the Universal2 libsqlite3odbc.so library via Terminal.app, by executing the following command —

curl -kO http://download3.openlinksw.com.s3.amazonaws.com/support/sqlite/libsqlite3odbc.so