This article is the English version of Snowflake ODBC with Ruby で日本語を扱うTips ~ ruby-odbc と共に ~
As of 2025/03, Snowflake does not have a Ruby SDK.
Therefore, when using a Ruby application as a client, it is necessary to implement a REST API or ODBC connection.
This time, we had to investigate errors in ODBC connections, so here is a summary of the contents.
Environment
Errors that were occurring
Condition 1) The number of characters in the string column definition is small (e.g. VARCHAR(4))
Condition 2) Japanese strings are stored.
The following two types of errors occurred in the case of a table called `Sequel::DatabaseError
Sequel::DatabaseError: ArgumentError: negative string size (or size too big)
→ Error in data acquisition process.
JSON::GeneratorError: source sequence is illegal/malformed utf-8
→ No error occurs in the data acquisition process itself, but an error occurs when the object containing the acquired byte sequence is JSON serialized.
Investigation
We will follow the location where the error occurs. This line was traceable in the former Ruby stack trace.
https://github.com/jeremyevans/sequel/blob/5.89.0/lib/sequel/adapters/odbc.rb#L95
Debugging it, I found that s is an object of ODBC::Statement 1. The each method is defined in the C extension part of ruby-odbc.
https://github.com/larskanis/ruby-odbc/blob/38d1431b7b8589e54079d93817db03a3846afbf7/ext/odbc.c#L6785
The code for the latest Ruby 3.2-compatible v0.999992 Gem is maintained by Christian Werner, but the code is not committed to GitHub, so for the sake of explanation, I'll check the code with the previous version by Lars Kanis. 2
If you follow the code of stmt_each, you will find a function called do_fetch. This function has about 500 lines and is very hard to read...
https://github.com/larskanis/ruby-odbc/blob/38d1431b7b8589e54079d93817db03a3846afbf7/ext/odbc.c#L5921
To get the results, call the SQLGetData function.
https://learn.microsoft.com/ja-jp/sql/odbc/reference/syntax/sqlgetdata-function?view=sql-server-ver16
According to ChatGPT, the specification seems to be as follows
In the case of the SQLGetData function, the data itself is passed as an address (pointer) of a buffer allocated in advance, and the acquired data is written to that buffer inside the function. The length of the data and the null flag are also stored in the pointer (StrLen_or_IndPtr) that is passed in advance.
The size of the buffer to be allocated in advance is set to S.
The preallocated buffer size is the column metadata obtained by the SQLColAttribute function.
https://github.com/larskanis/ruby-odbc/blob/38d1431b7b8589e54079d93817db03a3846afbf7/ext/odbc.c#L3184
The following conditions can be summarized for string columns.
| ODBC driver return type |
ruby-odbc UNICODE flag |
type |
size |
| SQL_LONGVARCHAR |
ON (UNICODE=true) |
SQL_C_WCHAR |
SQL_NO_TOTAL |
| SQL_LONGVARCHAR |
OFF (UNICODE=false) |
SQL_C_CHAR |
SQL_NO_TOTAL |
| SQL_LONGVARCHAR |
ON (UNICODE=true) |
SQL_C_WCHAR |
number of characters × sizeof(SQLWCHAR) + sizeof(SQLWCHAR)(NULL-terminated) |
| non SQL_LONGVARCHAR |
OFF (UNICODE=false) |
SQL_C_CHAR |
number of characters + 1 (for null terminated) |
To set UNICODE to true, you apparently need to build for UTF8 and call it with require 'odbc_utf8'. 3
This has suggested that if the byte size of the data is large relative to the number of characters in the column, the fourth pattern may result in an error because the buffer size allocated is smaller than actually needed.
In light of the above, we will look again at the implementation within do_fetch.
https://github.com/larskanis/ruby-odbc/blob/38d1431b7b8589e54079d93817db03a3846afbf7/ext/odbc.c#L6169
The variable curlen is initialized with the size obtained earlier.
https://github.com/larskanis/ruby-odbc/blob/38d1431b7b8589e54079d93817db03a3846afbf7/ext/odbc.c#L6361
If type == SQL_C_CHAR, we call rb_tainted_str_new (moved to rb_str_new in Ruby 3.2) with the character size we just got and generate a string.
https://github.com/larskanis/ruby-odbc/blob/38d1431b7b8589e54079d93817db03a3846afbf7/ext/odbc.c#L6361
If you look at the definition of the rb_tainted_str_new function in Ruby's string.c, you will see that internally the str_new0 function is called, and in it, if len < 0 (if curlen is a negative value), the following error message is generated
https://github.com/ruby/ruby/blob/v3_1_6/string.c#L881-L883
Since curlen is also specified as an argument to SQLGetData, it is likely that under some condition the call to SQLGetData causes curlen to be negative and an error to occur. (I ran out of steam before I could continue further investigation...)
Solution
- The right way: use the UTF-8 version of ruby-odbc (
odbc_utf8).
Untested, but you may need to be creative when using it with Sequel.- Only adding
require "odbc_utf8" before Sequel worked.
- Workaround: In Snowflake, specify
MapToLongVarchar parameter 4 in Snowflake ODBC driver as configuration/connection parameter so that the column type is mapped to SQL_LONGVARCHAR.
Conclusion
Most of this research was code reading of ruby-odbc. With little information available in Japanese, this was a good opportunity to deepen my understanding of the internal workings of the ODBC binding in Ruby.
I hope it will be helpful for those who face similar challenges.
