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

  1. I am trying to read and write binary data to a SQL Server database from Tcl.
  2. Reading works fine.
  3. Writing does not. My current attempt is using CONVERT(), and something
  4. is adding curly braces around the binary data.
  5.  
  6. In this transcript, I am doing an update command with two different data
  7. payloads. The payload with all lowercase letters works. The payload with
  8. punctuation characters does not. Curly braces are being added.
  9.  
  10. I get the same results if I try CONVERT() with style argument 2 and remove
  11. the leading 0x from the data payload.
  12.  
  13. Script started on 2024-08-15 09:04:43-04:00 [TERM="screen" TTY="/dev/pts/17" COLUMNS="80" LINES="24"]
  14. wooledg:~/queries/new$ cat query-helper-minimal
  15. # ~/.ebase.db contains arrays with database connection info, of the form:
  16. # array set production {
  17. # host {hostname.domain\something}
  18. # port {1433}
  19. # db {eBASE}
  20. # user {username}
  21. # pass {password}
  22. # }
  23. # array set testing {
  24. # ...
  25. # }
  26.  
  27. # Setting DB to one of these array names before sourcing this file lets
  28. # you pick a different database to connect to. The default is production.
  29.  
  30. source ~/.ebase.db
  31. if {! [info exists DB]} {set DB production}
  32. array set db [array get $DB]
  33.  
  34. package require tdbc::odbc
  35. set drv /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
  36. set conn "Driver=$drv;Server=$db(host);Port=$db(port);Database=$db(db);UID=$db(user);PWD=$db(pass)"
  37. tdbc::odbc::connection create db $conn
  38. wooledg:~/queries/new$ cat z1
  39. #!/usr/bin/tclsh
  40. set DB development
  41. source query-helper-minimal
  42.  
  43. set sql {
  44. update data.PdfConversions
  45. set InputData = CONVERT(varbinary(max), :data, 1)
  46. where Id = 1
  47. }
  48.  
  49. #set data 0x192122232425
  50. set data 0x656667686970
  51.  
  52. set d [dict create data $data]
  53. db allrows -as lists -- $sql $d
  54. wooledg:~/queries/new$ ./z1
  55. wooledg:~/queries/new$ cat z2
  56. #!/usr/bin/tclsh
  57. set DB development
  58. source query-helper-minimal
  59.  
  60. set sql {
  61. select InputData from data.PdfConversions where Id = 1
  62. }
  63.  
  64. fconfigure stdout -translation binary
  65. set data [lindex [db allrows -as lists -- $sql] 0]
  66. puts -nonewline $data
  67. wooledg:~/queries/new$ ./z2 | hd
  68. 00000000 65 66 67 68 69 70 |efghip|
  69. 00000006
  70. wooledg:~/queries/new$ vi z1
  71. [...]
  72. wooledg:~/queries/new$ cat z1
  73. #!/usr/bin/tclsh
  74. set DB development
  75. source query-helper-minimal
  76.  
  77. set sql {
  78. update data.PdfConversions
  79. set InputData = CONVERT(varbinary(max), :data, 1)
  80. where Id = 1
  81. }
  82.  
  83. set data 0x192122232425
  84. #set data 0x656667686970
  85.  
  86. set d [dict create data $data]
  87. db allrows -as lists -- $sql $d
  88. wooledg:~/queries/new$ ./z1
  89. wooledg:~/queries/new$ ./z2 | hd
  90. 00000000 7b 19 21 22 23 24 25 7d |{.!"#$%}|
  91. 00000008
  92. wooledg:~/queries/new$
  93. exit
  94.  
  95. Script done on 2024-08-15 09:05:46-04:00 [COMMAND_EXIT_CODE="0"]
  96.