MSSQL Server problem writing VARBINARY data.

[LED555]

I'm trying to INSERT a zip file into an MSSQL table. The table structure is such:

CREATE TABLE [dbo].[zips](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[timestamp] [varchar](50) NULL,
	[inserted] [varchar](50) NULL,
	[filename] [varchar](50) NULL,
	[raw_file] [varbinary](max) NULL
) ON [PRIMARY]

...nothing too fancy.

When I insert a text file, everything is well and I'm able to retrieve the text content successfully without any problems, but when I insert a binary file such as zip, the binary data gets modified.

My guess is that MSSQL somehow replaces the NON-PRINTABLE characters it gets to something default.

The zip data I'm trying to insert is:
0x504b0304140000080800715f303b607f...

but what ends up in the MSSQL table is (which is wrong):
0x504B030414C080C0800808C080715F303B607F...

notice how the 00's get replaced with C0 80

text data in:
0x
2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a
2a2a0d0a202050726f63657373696e672
0536c6f74203d2031200d0a2a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a0d0a0d
0a70617373776f726420414454524f

text data out (which is correct):
0x
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A0D0A202050726F63657373696E672
0536C6F74203D2031200D0A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A0D0A0D
0A70617373776F726420414454524F

Any help will be greatly appreciated. I have zero experience with SQL, and I'm really struggling with this. Thank you!

[sknake]

How are you trying to insert the data? Typically binary inserts are done with applications and not directly from TSQL, and inside of the applications you use ADO.NET/Native SQL parameters. You set the parameter to a byte[] array and execute the query.

This all depends on the language you are using though. What language are you developing your application in?

[LED555]

I'm using TCL and their ODBC package. They provided a VARBINARY example, and this is what I'm using.

So I open the file I want to upload, in binary mode, and just upload all the bytes using the INSERT SQL command... it seems to work ok, except for this byte replacements I notice once the data is uploaded.

Every other type of data I upload is perfect... it's just this VARBINARY.

I know that TCL is doing the right thing, because when I do a hex dump of my data I'm trying to upload, it's exactly what I'm expecting to see compared to other hex editors.

Is it something to do with my SQL Server setup?... I have zero experience with that. Collation, some NULL character settings?

Thank you.

[LED555]

my TCL code:

package require tclodbc

####################################################################
#
# Connect to SQL database, named "test"
#

database db "DRIVER=SQL Server;DBQ=test;USR=root;PWD=root"

####################################################################
#
# Read binary data from a zip file
#
# "test.zip" is < 1kB
#

set in [open "C:\\test.zip"]
fconfigure $in -translation binary
set data [read $in]
close $in

####################################################################
#
# Store binary data to a database table "zips"
#
#
#	CREATE TABLE [dbo].[zips](
#		[id] [int] IDENTITY(1,1) NOT NULL,
#		[timestamp] [varchar](50) NULL,
#		[inserted] [varchar](50) NULL,
#		filename] [varchar](50) NULL,
#		[raw_file] [varbinary](max) NULL
#	) ON [PRIMARY]
#

db "INSERT INTO zips (timestamp, inserted, filename, raw_file) \
VALUES ('09/26/09','09/26/09','test.zip',?)" {{VARBINARY}} [list $data]

[LED555]

So I looked up the ASCII meaning of 0x03 and 0x80 that my SQL Server replaces my 0x00 with. 0x03 means "End of text" and 0x80 has no meaning, it's just a character (C with an accent mark below it).

Does this ring a bell to anyone?

[sknake]

I have no idea man. I have never even thought of using TCL to connect with SQL Server and it has been years since i've done anything with the language :(

[LED555]

Thank you SKNAKE for giving it a thought, I appreciate it,

...but, I have another piece of the puzzle that may shed some light on my problem:

I uploaded the same zip file into an MS Access Database, and was able to retrieve it perfectly. The code is the same, except where I'm writing the data. The ODBC driver is the same, so I don't think my problem is on my application side... here it is:

package require tclodbc

####################################################################
#
# Connect to SQL database, named "test"
#

database db [B]"DRIVER=Microsoft Access Driver (*.mdb);DBQ=test.mdb"[/B]

####################################################################
#
# Read binary data from a zip file
#
# "test.zip" is < 1kB
#

set in [open "C:\\test.zip"]
fconfigure $in -translation binary
set data [read $in]
close $in

####################################################################
#
# Store binary data to a database table "zips"
#
#
#	CREATE TABLE [dbo].[zips](
#		[id] [int] IDENTITY(1,1) NOT NULL,
#		[timestamp] [varchar](50) NULL,
#		[inserted] [varchar](50) NULL,
#		filename] [varchar](50) NULL,
#		[raw_file] [varbinary](max) NULL
#	) ON [PRIMARY]
#

db "INSERT INTO zips (timestamp, inserted, filename, raw_file) VALUES ('09/26/09','09/26/09','test.zip',?)" {{VARBINARY}} [list $data]