SNOW-1020361 Unable to insert a UUID/byte array into an existing table

[madisonchamberlain]

I am unable to insert data into a table with a byte(16) column. If I run the following two queries through conn.Exec through the driver, I will always get the following error.

Function to generate a UUID byte array

func NewUuidBytes() []byte {
	u := uuid.New()
	return u[:]
}

Now I create a table with one BINARY(16) column:

ID_COL_NAME :+ "ID"
createTableQuery := fmt.Sprintf(
	"CREATE TABLE IF NOT EXISTS %s ("+
		"%s BINARY(16) NOT NULL PRIMARY KEY, "+ // 16-byte UUID
		")",
	tablePath,
	ID_COL_NAME,
)
_, err := conn.QueryContext(ctx, createTableQuery)

Now I try to insert a row...

AddRowQuery := fmt.Sprintf(
	"INSERT INTO "TESTDB"."TESTSCHEMA"."TEST_TABLE_1706289161310224000_1210730230567371133" (%s) VALUES (?)" 
		")",
	ID_COL_NAME,
)
AddRowArgs := NewUuidBytes()
_, err := conn.QueryContext(ctx, AddRowQuery, AddRowArgs...)

The add row will always fail with the following error 100115 (22000): The following string is not a legal hex-encoded value: '<[]uint8 Value>'

And if I have a BINARY column where I attempt to add a single BINARY value like []byte{byte(2)} that works fine.

[sfc-gh-dszmolka]

hi and thank you for raising this issue with us. I'm not entirely sure if it should work as originally attempted, but checking the Snowflake documentation for

• TO_BINARY
• Binary Input and Output

and especially the section for "Hexadecimal (“HEX”) Format Example", would it be feasible to try something like this:

• return uuid as string from NewUuidBytes
• INSERT INTO yourtable (ID_COL_NAME) SELECT TO_BINARY(HEX_ENCODE(newuuidstring), 'HEX');

can you please try something like this and let us know if it helps ?

[madisonchamberlain]

I did get this to work so thank you for the suggestion! I am curious whether that behavior should be supported without a cast

[sfc-gh-dszmolka]

nice; happy to hear you managed to get it working after all ! before answering the question, I'd like to test it with other drivers as well, need some time for that.

[sfc-gh-dszmolka]

this seems to work with the Python Connector simply with

[..cut for brevity]
myuuidbytes = uuid.uuid4().bytes
..
  cur.execute(
    "INSERT INTO testbinary(uuid) "
    "VALUES(%s)", (
        myuuidbytes
    ))

without any casting needed. I'm checking further internally if this is some limitation or needs to be done differently in gosnowflake. Thank you for bearing with us ! Will keep this thread posted.

[madisonchamberlain]

Ok thank you! And thanks again for the workaround, much appreciated!

[sfc-gh-pfus]

Hi @madisonchamberlain , I think the solution is mentioning correct type manually, can you verify if it works for you? https://github.com/snowflakedb/gosnowflake/blob/master/bindings_test.go#L164

[madisonchamberlain]

Hi @madisonchamberlain , I think the solution is mentioning correct type manually, can you verify if it works for you? https://github.com/snowflakedb/gosnowflake/blob/master/bindings_test.go#L164

Yes this works thank you! I didnt know we could manually specify the type and just pass one ? into the sql statement.