Sequel::DatabaseError (ArgumentError: string contains null byte) when running SQL with JSON interpolation through Snowflake #2209
Replies: 2 comments 3 replies
-
|
The exception is being raised by the ruby-odbc gem (the If that raises |
Beta Was this translation helpful? Give feedback.
-
|
Looks like you're right. Running that piece of code, I still get the same error, which means that the error comes from |
Beta Was this translation helpful? Give feedback.
-
|
If you are willing to use JRuby, you could try the jdbc adapter with the JDBC Snowflake driver. I think JDBC and ODBC are the only options currently available for connecting to Snowflake from Ruby. |
Beta Was this translation helpful? Give feedback.
-
|
Gotcha. I'm afraid JRuby isn't an option for now. I'm going to try my luck with the |
Beta Was this translation helpful? Give feedback.
-
|
The error you're seeing comes from the driver, not the DB, but it does remind me of this issue we have seen in WebhookDB: https://github.com/webhookdb/webhookdb/blob/2a57b5e/lib/webhookdb/replicator/base.rb#L711 PG's json column can handle JSON with an encoded/escaped null character, because it stores the json string verbatim, but jsonb needs to parse the json, and it finds a null-terminated string in the parsed json. I would expect Snowflake's Object type works the same. But again, your error comes from the driver, while the linked issue comes from the database, but at least it's something to think about. pry(main)> s = JSON.generate({a: "abc\u0000"})
pry(main)> DB[:jsontest].insert(j: s)
=> nil
pry(main)> DB[:jsontest].insert(jb: s)
Sequel::DatabaseError: PG::UntranslatableCharacter: ERROR: unsupported Unicode escape sequence
LINE 1: INSERT INTO "jsontest" ("jb") VALUES ('{"a":"abc\u0000"}') R...
^
DETAIL: \u0000 cannot be converted to text.
CONTEXT: JSON data, line 1: {"a":"abc\u0000... |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
I'm on a Mac M3 Pro on Sonoma 14.4.
Ruby version: 2.7.6
unixODBC: 2.3.12 (installed through homebrew)
I have set up my ODBC drivers properly and can run regular queries, but I encounter the same error every time when I try to run SQL with a string that has a JSON string interpolated in it.
When running
odbcinst -j:My
odbc.ini:and
odbcinst.ini:and
simba.snowflake.ini:I also have installed the
ruby-odbcgem using the following command:gem install ruby-odbc -- --with-odbc-dir=/opt/homebrew/Cellar/unixodbc/2.3.12I initialized the database using this:
This is the function I'm trying to run.
mail.ingest_threadis a stored procedure that I have in Snowflake which I want to call from Rails:I passed in a JSON for the thread argument and an ActiveRecord object for the user argument which will just be a string that looks like "123" in the function. The JSON is this:
As a JSON string:
I made sure that the JSON string doesn't contain any null bytes, but when I run the function it gave me this:
Backtrace:
I've tried running normal queries and found no issue, but when trying to call this stored procedure in Snowflake with a JSON string as the argument, I run into this problem. Can anyone help me with this issue? Would like to hear other people's thoughts before submitting this as a bug. Thank you in advance.
Beta Was this translation helpful? Give feedback.
All reactions