Posted to tcl by greycat at Thu Aug 15 13:39:55 GMT 2024view pretty

I am trying to read and write binary data to a SQL Server database from Tcl.
Reading works fine.
Writing does not.  My current attempt is using CONVERT(), and something
is adding curly braces around the binary data.

In this transcript, I am doing an update command with two different data
payloads.  The payload with all lowercase letters works.  The payload with
punctuation characters does not.  Curly braces are being added.

I get the same results if I try CONVERT() with style argument 2 and remove
the leading 0x from the data payload.

Script started on 2024-08-15 09:04:43-04:00 [TERM="screen" TTY="/dev/pts/17" COLUMNS="80" LINES="24"]
wooledg:~/queries/new$ cat query-helper-minimal
# ~/.ebase.db contains arrays with database connection info, of the form:
# array set production {
#     host {hostname.domain\something}
#     port {1433}
#     db   {eBASE}
#     user {username}
#     pass {password}
# }
# array set testing {
# ...
# }

# Setting DB to one of these array names before sourcing this file lets
# you pick a different database to connect to.  The default is production.

source ~/.ebase.db
if {! [info exists DB]} {set DB production}
array set db [array get $DB]

package require tdbc::odbc
set drv /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
set conn "Driver=$drv;Server=$db(host);Port=$db(port);Database=$db(db);UID=$db(user);PWD=$db(pass)"
tdbc::odbc::connection create db $conn
wooledg:~/queries/new$ cat z1
#!/usr/bin/tclsh
set DB development
source query-helper-minimal

set sql {
    update data.PdfConversions
    set InputData = CONVERT(varbinary(max), :data, 1)
    where Id = 1
}

#set data 0x192122232425
set data 0x656667686970

set d [dict create data $data]
db allrows -as lists -- $sql $d
wooledg:~/queries/new$ ./z1
wooledg:~/queries/new$ cat z2
#!/usr/bin/tclsh
set DB development
source query-helper-minimal

set sql {
    select InputData from data.PdfConversions where Id = 1
}

fconfigure stdout -translation binary
set data [lindex [db allrows -as lists -- $sql] 0]
puts -nonewline $data
wooledg:~/queries/new$ ./z2 | hd
00000000  65 66 67 68 69 70                                 |efghip|
00000006
wooledg:~/queries/new$ vi z1
[...]
wooledg:~/queries/new$ cat z1
#!/usr/bin/tclsh
set DB development
source query-helper-minimal

set sql {
    update data.PdfConversions
    set InputData = CONVERT(varbinary(max), :data, 1)
    where Id = 1
}

set data 0x192122232425
#set data 0x656667686970

set d [dict create data $data]
db allrows -as lists -- $sql $d
wooledg:~/queries/new$ ./z1
wooledg:~/queries/new$ ./z2 | hd
00000000  7b 19 21 22 23 24 25 7d                           |{.!"#$%}|
00000008
wooledg:~/queries/new$ 
exit

Script done on 2024-08-15 09:05:46-04:00 [COMMAND_EXIT_CODE="0"]