3 Replies - 4927 Views - Last Post: 04 November 2009 - 08:01 AM Rate Topic: -----

#1 Darkthal   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 04-November 09

Text in SQL as "image" datatype

Posted 04 November 2009 - 12:38 AM

There is an application, which is saving large HTML files to the database as an image datatype. I can not change that. What I have to do though is get content of this file.
I know that this data is selecter as byte[], but how to convert it to string? Moreover, how to convert it back and keep the charset (utf8)?

Database table definition (which I can not change):
CREATE TABLE [CDN].[BinaryData](
	[DAB_DABID] [int] IDENTITY(1,1) NOT NULL,
	[DAB_TwAID] [int] NOT NULL,
	[DAB_Value] [image] NULL,
	[DAB_Size] [int] NULL,
	[DAB_FileName] [varchar](254) COLLATE Polish_CI_AS NOT NULL,
	[DAB_Name] [varchar](128) COLLATE Polish_CI_AS NULL,
	[DAB_Extension] [varchar](4) COLLATE Polish_CI_AS NULL,
	[DAB_OpeZalID] [int] NULL,
	[DAB_StaZalId] [int] NULL,
	[DAB_TS_Zal] [datetime] NOT NULL,
	[DAB_OpeModID] [int] NULL,
	[DAB_StaModId] [int] NULL,
	[DAB_TS_Mod] [datetime] NOT NULL,
	[DaB_Typ] [int] NULL,
 CONSTRAINT [DAB_Primary] PRIMARY KEY CLUSTERED 
(
	[DAB_DABID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



When I try to dump the data to file, like I do with images (which by the way works fine) it gives me the file with wrong content.
Here is the file: Dumped file
To dump the file i used following method:
byte[] image = null;
            FileStream fs = null;
            string filename = Path.Combine(this._Path, "Description.html");

            if (MsSqlDataReader.Read())
            {
                image = (byte[])MsSqlDataReader["DAB_Wartosc"];
                int ArraySize = new int();
                ArraySize = image.GetUpperBound(0);
                fs = new FileStream(@filename, FileMode.OpenOrCreate, FileAccess.Write);
                fs.Write(image, 0, ArraySize);
                fs.Close();
            }
            MsSqlDataReader.Close();


Below are pretty useless informations, but I present them just for FYI:

Theese are samples that I already used:

return image.ToString(); 

Duuuh... It is not suprise, that it doesn't work, but I was desparate!


return System.Text.ASCIIEncoding.ASCII.GetString(image);

This returns strange data... Not even close.


return System.BitConverter.ToString(image);

Same here, but different content...


return System.Text.Encoding.GetEncoding("utf-8").GetString(image);

Same here, but different content...


Convert.ToBase64String(image);

Same here, but different content...


PS. image variable is really a byte[] containing selected data from database, which contains the large text file.

If something is not clear, please tell me, so I can explain my problem in more datailed way.

This post has been edited by Darkthal: 04 November 2009 - 03:14 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Text in SQL as "image" datatype

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7507
  • View blog
  • Posts: 15,558
  • Joined: 16-October 07

Re: Text in SQL as "image" datatype

Posted 04 November 2009 - 07:50 AM

It really shouldn't matter. Binary is binary. Maybe you're over thinking it.

Here's some simple code that works for me.

void BlobToStream(DbDataReader reader, Stream outStream) {
	int buffSize = 1024;
	byte[] buff = new byte[buffSize];
	long readPos = 0;
	BinaryWriter writer = new BinaryWriter(outStream);
	int bytesRead = (int)reader.GetBytes(0, readPos, buff, 0, buffSize);
	while (bytesRead > 0) {
		writer.Write(buff, 0, bytesRead);
		readPos += bytesRead;
		bytesRead = (int)reader.GetBytes(0, readPos, buff, 0, buffSize);
	}
	writer.Close();
}


void BlobToFile(DbConnection conn, string sql, string fileName) {
	DbCommand cmd = conn.CreateCommand();
	cmd.CommandText = sql;
	try {
		cmd.Connection.Open();
		using (DbDataReader reader = cmd.ExecuteReader()) {
			if (reader.Read()) {
				BlobToStream(reader, File.OpenWrite(fileName));
			}
		}
	} finally {
		cmd.Connection.Close();
	}
}



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#3 Momerath   User is offline

  • D.I.C Lover
  • member icon

Reputation: 1021
  • View blog
  • Posts: 2,463
  • Joined: 04-October 09

Re: Text in SQL as "image" datatype

Posted 04 November 2009 - 07:52 AM

If you do a select on the database, what does the image data look like?

You should also take a look at http://msdn.microsof...y/9d876whe.aspx
Was This Post Helpful? 0
  • +
  • -

#4 Darkthal   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 04-November 09

Re: Text in SQL as "image" datatype

Posted 04 November 2009 - 08:01 AM

View Postbaavgai, on 4 Nov, 2009 - 06:50 AM, said:

It really shouldn't matter. Binary is binary. Maybe you're over thinking it.

Here's some simple code that works for me.

void BlobToStream(DbDataReader reader, Stream outStream) {
	int buffSize = 1024;
	byte[] buff = new byte[buffSize];
	long readPos = 0;
	BinaryWriter writer = new BinaryWriter(outStream);
	int bytesRead = (int)reader.GetBytes(0, readPos, buff, 0, buffSize);
	while (bytesRead > 0) {
		writer.Write(buff, 0, bytesRead);
		readPos += bytesRead;
		bytesRead = (int)reader.GetBytes(0, readPos, buff, 0, buffSize);
	}
	writer.Close();
}


void BlobToFile(DbConnection conn, string sql, string fileName) {
	DbCommand cmd = conn.CreateCommand();
	cmd.CommandText = sql;
	try {
		cmd.Connection.Open();
		using (DbDataReader reader = cmd.ExecuteReader()) {
			if (reader.Read()) {
				BlobToStream(reader, File.OpenWrite(fileName));
			}
		}
	} finally {
		cmd.Connection.Close();
	}
}



Hope this helps.


Thank you for that. I will check it out tomorrow. I just figured out, that the application, that is adding the files to the database is somehow corrupting plain/text files. All other ones are imported without any problems. I iported text file in SQL Manager, using SQL Command:
UPDATE [CDN].[DaneBinarne] SET [DAB_Wartosc] = 
(SELECT * FROM OPENROWSET(BULK N'C:\Documents and Settings\Administrator\Moje dokumenty\DESC\LoremIpsum.txt', SINGLE_BLOB) AS [DAB_Wartosc])
WHERE [DAB_DABID] = 107

and I got the file dumped without any problems.
I even changed the file's extension before importing it with the application. The result was the same - corrupted file.
I just wrote to the company responsible for this software, and I'm wainting for their reply.

As I said before, I will check everything you guys wrote tomorrow and I will report the results here.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1