Storing binary image data in sql server
BMaxwell
I'm using the code below to read the contents of a PNG file and store
it in a Sql Server 2000 image column. The result is that the UTF8
representation gets stored in the database. I can read it back out
and create the file again, but I need to store the bytes in the
original representation. Could someone help me figure out what I'm
missing?
Thanks in advance,
Brad
set nFileID [ open $szPngFileName {RDONLY} ]
fconfigure $nFileID -encoding binary -translation binary
set szImage [ read $nFileID ]
close $nFileID
set szSQL "UPDATE MyTable "
append szSQL "SET Status = 2, "
append szSQL "LabelImage = ? "
append szSQL "WHERE Key = '$szFileName'"
puts $szSQL
db $szSQL {{LONGVARBINARY}} [ list $szImage ]
Benjamin Riefenstahl
BMaxwell writes:
> I'm using the code below to read the contents of a PNG file and
> store it in a Sql Server 2000 image column. The result is that the
> UTF8 representation gets stored in the database. [...]
>
> set nFileID [ open $szPngFileName {RDONLY} ]
> fconfigure $nFileID -encoding binary -translation binary
>
> set szImage [ read $nFileID ]
> close $nFileID
>
> set szSQL "UPDATE MyTable "
> append szSQL "SET Status = 2, "
> append szSQL "LabelImage = ? "
> append szSQL "WHERE Key = '$szFileName'"
> puts $szSQL
>
> db $szSQL {{LONGVARBINARY}} [ list $szImage ]
I guess your DB package (you don't say which it is, TclODBC maybe?)
either doesn't handle binary data at all, or it doesn't recognise that
LONGVARBINARY is a binary data field. In the second case you may just
be missing some instruction to tell the DB wrapper about this.
benny
Dave Bigelow
Dave
BMaxwell
Oops, I meant to mention that it is indeed tclodbc (ver 2.3). This is
pretty much following the sample code from the blob.tcl sample
included with the package. If I retrieve the data and write the file
out again, it's actually correct. But I want to access the data in
the database with other non-tcl applications and the UTF-8 confuses
them. Is there a way I could just transmit and store the original
bytes?
Thanks,
Brad
Roy Nurmi
correctly.
What it does currently, is that it does pass the tcl string data as it is to
the database, but the catch here is that even after:
fconfigure $nFileID -encoding binary -translation binary
the "binary" data in tcl memory is actually UTF-8 encoded, unless you
convert it using Tcl_GetByteArrayFromObj(). In my original example this has
worked just because the data is read in the same way as it is written, so
tcl application works fine doing it both ways, but an external application
gets the data mangled.
This will be fixed in some time (pretty easy task), but with current binary
version there is no workaround.
-- Roy Nurmi
"BMaxwell" <bcma...@yahoo.com> wrote in message
news:e563afce.04102...@posting.google.com...
mys...@hotmail.com
When reading from a blob field in a sybase table, there is something that gets mangled. I write the (tif image) data to a file, but the image is not viewable.
If i export the data from sybase isql to a file, as a hex string (0x49492a0008...) i can open that frmo tcl, and convert it the correct way to a viewable tif file.
I have tried with both tclodbc 2.3 and 2.5
(Using Active state 8.6 and tdbc didnt Work for me either, as it looks like tdbc in activestate is bugged and not documented correct. When trying to load tdbc::odbc, an error is appearing about TclOO not being loadable. Loading TclOO manually gives error that command "tdbc::odbc::connection" allready exist.
When doing a tdbc::odbc::connection create dbhandle, no connection string is accepted due to wrong number of arguments.)
(Sybtcl won't load either, so ill probably have to transfer data manually to my harddisk using a horseshoe magnet...)
Harald Oehlmann
> (Using Active state 8.6 and tdbc didnt Work for me either, as it looks like tdbc in activestate is bugged and not documented correct. When trying to load tdbc::odbc, an error is appearing about TclOO not being loadable. Loading TclOO manually gives error that command "tdbc::odbc::connection" allready exist.
>
> When doing a tdbc::odbc::connection create dbhandle, no connection string is accepted due to wrong number of arguments.)
It was correct, that older versions had those issues, but not tcl8.6.0...
About the connect string, this is sometimes quite difficult.
On wiki.tcl.tk/tdbc, there are some examples.
I pseronally use:
tdbc::odbc::connection create db "FILEDSN=[file nativename [file normalize odbc.dsn]]"
for a DSN file, or
tdbc::odbc::connection create db "DSN=connection;UID=user;PWD=password"
-Harald