如何在MSSQL中将varbinary数据类型转换回ascii(How to convert varbinary data type back to ascii in MSSQL)

如何在MSSQL中将varbinary数据类型转换回ascii(How to convert varbinary data type back to ascii in MSSQL)

我试图从MS SQL中的图像日期字段中获取RTF数据。 不那么容易。

问题是，当我对数据字段进行直接二进制转储时，它不是RTF格式。 让我解释一下发生了什么。 当我使用Wordpad创建RTF文件，并将该数据写入varbinary（max）并重新转换它时，结果是jiberish。

将RTF数据放入MS SQL的代码：

exec master..sp_configure 'show advanced options', 1; GO RECONFIGURE; GO exec master..sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @objStream INT DECLARE @imageBinary VARBINARY(MAX) DECLARE @filePath VARCHAR(8000) select @imageBinary=Report from Mytable WHERE EncounterID=7 select @filePath='c:\temp\report.rtf' EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT EXEC sp_OASetProperty @objStream, 'Type', 1 EXEC sp_OAMethod @objStream, 'Open' EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2 EXEC sp_OAMethod @objStream, 'Close' EXEC sp_OADestroy @objStream

在二进制文件中，此文件的第一部分如下所示：0x7B 5C 72 74 66 31 5C 61 6E 73 69 5C 61 6E 73 69）（ascii {\ rtf1 \ ansi \ ansi）但是，varbinary字段如下所示：0xB0 04 01 00 0E 00 00 00 00 00 00 00 00 00 09 00

当我将数据从数据库中取出时（通过使用上述过程的反向），它不是可识别的RTF文件。 所以，不知何故，MS正以一种我无法识别的方式转换它。 如果我能弄清楚如何将其转换回ascii文本，那么我可以继续我的应用程序。

I am trying to get RTF data out of a image date field in MS SQL. Not so easy.

The issue is that when I do a straight binary dump of the data field it is not in RTF format. Let me explain what is going on. When I create a RTF file with Wordpad, and write that data to a varbinary(max), and reconvert it, the result is jiberish.

Code to put RTF data into MS SQL:

exec master..sp_configure 'show advanced options', 1; GO RECONFIGURE; GO exec master..sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @objStream INT DECLARE @imageBinary VARBINARY(MAX) DECLARE @filePath VARCHAR(8000) select @imageBinary=Report from Mytable WHERE EncounterID=7 select @filePath='c:\temp\report.rtf' EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT EXEC sp_OASetProperty @objStream, 'Type', 1 EXEC sp_OAMethod @objStream, 'Open' EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2 EXEC sp_OAMethod @objStream, 'Close' EXEC sp_OADestroy @objStream

In binary, the first part of this file goes like this 0x7B 5C 72 74 66 31 5C 61 6E 73 69 5C 61 6E 73 69) (ascii {\rtf1\ansi\ansi ) However, the varbinary field looks like this: 0xB0 04 01 00 0E 00 00 00 00 00 00 00 00 00 09 00

And when i take that data out of the database (by using the reverse of the procedure above), it is not a recognizable RTF file. So, somehow MS is converting it in a way i can't recognize. If I can figure out how to convert it back to ascii text then I can continue with my application.

最满意答案

以下适用于我。 我怀疑问题必须是如何将文件保存到数据库。

CREATE TABLE #BlobTest ( blob varbinary(max) ) INSERT INTO #BlobTest (blob) SELECT BulkColumn FROM Openrowset( Bulk 'C:\testing.rtf', SINGLE_BLOB) AS blob GO exec master..sp_configure 'show advanced options', 1; GO RECONFIGURE; GO exec master..sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @objStream INT DECLARE @imageBinary VARBINARY(MAX) DECLARE @filePath VARCHAR(8000) select @imageBinary=blob from #BlobTest select @filePath='c:\report.rtf' EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT EXEC sp_OASetProperty @objStream, 'Type', 1 EXEC sp_OAMethod @objStream, 'Open' EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2 EXEC sp_OAMethod @objStream, 'Close' EXEC sp_OADestroy @objStream

The following works for me. I suspect the issue must be how you are saving the file to the database.

CREATE TABLE #BlobTest ( blob varbinary(max) ) INSERT INTO #BlobTest (blob) SELECT BulkColumn FROM Openrowset( Bulk 'C:\testing.rtf', SINGLE_BLOB) AS blob GO exec master..sp_configure 'show advanced options', 1; GO RECONFIGURE; GO exec master..sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @objStream INT DECLARE @imageBinary VARBINARY(MAX) DECLARE @filePath VARCHAR(8000) select @imageBinary=blob from #BlobTest select @filePath='c:\report.rtf' EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT EXEC sp_OASetProperty @objStream, 'Type', 1 EXEC sp_OAMethod @objStream, 'Open' EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2 EXEC sp_OAMethod @objStream, 'Close' EXEC sp_OADestroy @objStream