MSSQL Server problem writing VARBINARY data.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

MSSQL Server problem writing VARBINARY data.

Author

Topic

LED555
Starting Member

5 Posts

Posted - 2009-09-25 : 18:57:18

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
2a2a0d0a202050726f63657373696e67
20536c6f74203d2031200d0a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a
2a2a2a2a2a2a2a2a2a2a2a2a2a2a0d0a
0d0a70617373776f726420414454524f

text data out (which is correct):
0x
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A0D0A202050726F63657373696E67
20536C6F74203D2031200D0A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A
2A2A2A2A2A2A2A2A2A2A2A2A2A2A0D0A
0D0A70617373776F726420414454524F

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-26 : 03:50:09

How are you inserting it?

--
Gail Shaw
SQL Server MVP

LED555
Starting Member

5 Posts

Posted - 2009-09-26 : 13:41:34

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
Starting Member

5 Posts

Posted - 2009-09-26 : 14:00:56

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
Starting Member

5 Posts

Posted - 2009-09-26 : 17:04:27

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?

LED555
Starting Member

5 Posts

Posted - 2009-09-26 : 17:31:58

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:

[CODE]package require tclodbc

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

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

####################################################################
#
# 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][/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources