Victor Alekseev
Reputation: 31
"ArgumentError: string contains null byte" when connecting to Snowflake through ODBC from Ruby
I work on Mac M1 (Version 11.6 (20G165)), where installed the next software:
- https://sfc-repo.snowflakecomputing.com/odbc/mac64/latest/index.html
- iODBC-SDK-3.52.15-macOS11.dmg from http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
- UnixODBC manager, installed as brew install unixodbc freetds
- ruby for x86_64 architecture
The content of /opt/snowflake/snowflakeodbc/lib/universal/simba.snowflake.ini file:
[Driver]
DriverManagerEncoding=UTF-16
DriverLocale=en-US
ErrorMessagesPath=/opt/snowflake/snowflakeodbc/ErrorMessages
LogLevel=0
LogPath=/tmp
CURLVerboseMode=true
ODBCInstLib=/usr/local/Cellar/unixodbc/2.3.11/lib/libodbcinst.dylib
CABundleFile=/opt/snowflake/snowflakeodbc/lib/universal/cacert.pem
ODBCInstLib=libodbcinst.dylib
The content of ~/./.odbcinst.ini
[ODBC]
Trace=yes
TraceFile=odbc.log
[ODBC Drivers]
Snowflake = Installed
[Snowflake]
Driver=/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib
The content of ~/.odbc.ini
[ODBC Data Sources]
Snowflake=Snowflake
Snowflake1=Snowflake
[Snowflake]
Description=SnowflakeUserDB
Driver=Snowflake
Locale=en-US
SERVER=xxxxx.snowflakecomputing.com
WAREHOUSE=DEV_COMPUTE
PORT=443
SSL=on
Database=W_DEV_DB
UID = XXX_DEV
ACCOUNT=xxxxx
TRACING=6
Schema = PUBLIC
The result of ruby -v:
ruby 2.4.10p364 (2020-03-31 revision 67879) [x86_64-darwin20]
The next files has architecture x86_64 (tested with lipo -info):
- /opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib
- /usr/local/Cellar/unixodbc/2.3.11/bin/isql
- /usr/local/Cellar/unixodbc/2.3.11/lib/libodbcinst.dylib
- /usr/local/Cellar/unixodbc/2.3.11/bin/odbcinst
The result of /usr/local/Cellar/unixodbc/2.3.11/bin/odbcinst -q -s
[Snowflake]
The result of `/usr/local/Cellar/unixodbc/2.3.11/bin/odbcinst -j'
unixODBC 2.3.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/krocodl/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
The result of /usr/local/Cellar/unixodbc/2.3.11/bin/isql -v Snowflake username password
+---------------------------------------+
| Connected!
But unfortunately the minimal test program, got from https://community.snowflake.com/s/article/connecting-to-snowflake-using-the-obdc-driver--ruby-on-rails-5, does not work with the next error:
An error occurred
Error code: #<Sequel::DatabaseConnectionError: ArgumentError: string contains null byte>
The similar sample, based on DBI.connect("DBI:ODBC:Snowflake","username","password") does not work with the same error.
In the ~/sql.log file there are the next errors:
[000000.000202]
ruby 20058EE00 ENTER SQLConnect
SQLHDBC 0x7f7fc05c95d0
SQLCHAR * 0x7f7fc117dcb0
| Snowflake |
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0x7f7fc117dc88
| username |
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0x7fff78089d44
| **** |
SQLSMALLINT -3 (SQL_NTS)
[000000.122933]
ruby 20058EE00 EXIT SQLConnect with return code -1 (SQL_ERROR)
SQLHDBC 0x7f7fc05c95d0
SQLCHAR * 0x7f7fc117dcb0
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0x7f7fc117dc88
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0x7fff78089d44
SQLSMALLINT -3 (SQL_NTS)
[000000.123422]
ruby 20058EE00 ENTER SQLError
SQLHENV 0x0 (SQL_NULL_HANDLE)
SQLHDBC 0x7f7fc05c95d0
SQLHSTMT 0x0 (SQL_NULL_HANDLE)
SQLCHAR * 0x304a56d85
SQLINTEGER * 0x304a56d7c
SQLCHAR * 0x304a56fd0
SQLINTEGER 511
SQLSMALLINT * 0x304a56d7a
[000000.124001]
ruby 20058EE00 EXIT SQLError with return code 0 (SQL_SUCCESS)
SQLHENV 0x0 (SQL_NULL_HANDLE)
SQLHDBC 0x7f7fc05c95d0
SQLHSTMT 0x0 (SQL_NULL_HANDLE)
SQLCHAR * 0x304a56d85
| (empty string) |
SQLINTEGER * 0x304a56d7c (10380)
SQLCHAR * 0x304a56fd0
| o
SQLINTEGER 511
SQLSMALLINT * 0x304a56d7a (103)
[000000.124058]
ruby 20058EE00 ENTER SQLError
SQLHENV 0x0 (SQL_NULL_HANDLE)
SQLHDBC 0x7f7fc05c95d0
SQLHSTMT 0x0 (SQL_NULL_HANDLE)
SQLCHAR * 0x304a56d85
SQLINTEGER * 0x304a56d7c
SQLCHAR * 0x304a56fd0
SQLINTEGER 511
SQLSMALLINT * 0x304a56d7a
[000000.124251]
ruby 20058EE00 EXIT SQLError with return code 100 (SQL_NO_DATA_FOUND)
SQLHENV 0x0 (SQL_NULL_HANDLE)
SQLHDBC 0x7f7fc05c95d0
SQLHSTMT 0x0 (SQL_NULL_HANDLE)
SQLCHAR * 0x304a56d85
SQLINTEGER * 0x304a56d7c
SQLCHAR * 0x304a56fd0
SQLINTEGER 511
SQLSMALLINT * 0x304a56d7a
[000000.126147]
ruby 20058EE00 ENTER SQLFreeEnv
SQLHENV 0x7f7fc07bd000
[000000.126166]
ruby 20058EE00 EXIT SQLFreeEnv with return code -1 (SQL_ERROR)
SQLHENV 0x7f7fc07bd000
The exception backtrace:
[0] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/adapters/odbc.rb:29:in `initialize'"
[1] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/adapters/odbc.rb:29:in `connect'"
[2] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/adapters/odbc.rb:29:in `connect'"
[3] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/connection_pool.rb:122:in `make_new'"
[4] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/connection_pool/threaded.rb:209:in `assign_connection'"
[5] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/connection_pool/threaded.rb:139:in `acquire'"
[6] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/connection_pool/threaded.rb:91:in `hold'"
[7] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/database/connecting.rb:252:in `synchronize'"
[8] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/database/connecting.rb:278:in `test_connection'"
[9] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/database/misc.rb:175:in `initialize'"
[10] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/database/connecting.rb:57:in `new'"
[11] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/database/connecting.rb:57:in `connect'"
[12] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/core.rb:124:in `connect'"
[13] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/core.rb:412:in `adapter_method'"
[14] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/sequel-5.57.0/lib/sequel/core.rb:419:in `block (2 levels) in def_adapter_method'"
[15] = "/Users/krocodl/Projects/samples/test.rb:42:in `<top (required)>'"
[16] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/ruby-debug-ide-2.3.3/lib/ruby-debug-ide.rb:95:in `debug_load'"
[17] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/ruby-debug-ide-2.3.3/lib/ruby-debug-ide.rb:95:in `debug_program'"
[18] = "/Users/krocodl/.asdf/installs/ruby/2.4.10/lib/ruby/gems/2.4.0/gems/ruby-debug-ide-2.3.3/bin/rdebug-ide:190:in `<main>'"
Does anyone else have any ideas on how to make the test example work????
P.S. Reported as https://github.com/jeremyevans/sequel/issues/1888
Upvotes: 2
Views: 480
Answers (1)
Victor Alekseev
Reputation: 31
When we install ruby-odbc gem, we need to configure it with the reference to the previously installed iODBC-SDK by performing the next operation:
gem install ruby-odbc -- --with-odbc-dir=/usr/local/Cellar/unixodbc/2.3.11
Upvotes: 1
Related Questions
- Snowflake ODBC driver (64 bit) error while setting up DSN
- Sequel::DatabaseConnectionError: ODBC::Error: 01000 (0) [unixODBC][Driver Manager]Can't open lib 'libSnowflake.dylib'
- connect to snowflake via odbc driver on mac m1
- Issue Installing and Configuring the ODBC Driver for macOS
- snowsql not able to take arguments in mac
- Snowflake Ruby odbc Assertion failure: GEOGRAPHY_type_unsupported
- Connecting R to Snowflake through ODBC
- Sequel Rails ODBC with Snowflake returns '\x1A' for special characters
- Can't connect to snowflake via unixODBC. Error: [S1000][unixODBC][Snowflake][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function
- Getting error messages while connecting to snowflake database though ODBC