PDF Images in SQL Server - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

PDF Images in SQL Server

Author

Topic

maybede
Starting Member

5 Posts

Posted - 2004-03-23 : 16:00:46

I am having trouble getting a PDF image out of SQL server. I am using C# to retrieve the PDF but it is giving me a "The file is damaged and could not be repaired." error message. The file is fine until I stream it into the Sql Server, after that it won't come out. I am using an Excel spreadsheet that has graphs embedded in it to create the PDF file and I wondered if anyone had ran into similar problems with PDF image fields, any thoughts would be appreciated. Thanks, I know this is a weird question but I am stumped.

Derick Mayberry
Senior Web Developer
Morgan Keegan & Co., Inc.
Memphis, TN 38133

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-23 : 19:52:29

Do you "have" to stream it into SQL Server. You should just store it to a directory and have a pointer in the database to it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

maybede
Starting Member

5 Posts

Posted - 2004-03-24 : 09:36:04

Actually yes, I work for a financial institution and some of the people here put out daily commentaries and things like that and they want them to go out over email to a huge group of people so instead of flooding the Exchange server with 650 copies of a 2mb PDF I send them links in an email and let them open it from there, this greatly reduces the network traffic greatly and gets the Exchange admins off my back. Thanks for the thoughts though, I could only wish it was that easy, by the way thats the way I was updating the intranets links.

Derick Mayberry
Senior Web Developer
Morgan Keegan & Co., Inc.
Memphis, TN 38133

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-24 : 09:46:38

Can you not just send them a link direct to documents placed on a safe publicly-accessible server?

Re the network traffic...surely with this method you are only bypassing the upfront network load. If all 650 recipients download/open the pdf, surely the traffic will rise to the same overall total....or are you likely to benefit from (some) network caching?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-24 : 09:57:59

Also, what do you mean by "stream into SQL Server". Aren't you just doing a standard insert? I'm not sure how you insert an image or doc into SQL Server.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

maybede
Starting Member

5 Posts

Posted - 2004-03-24 : 17:25:53

I am actually using the streaming object to open the PDF, creating a Byte Array, then adding that to the table. Its relatively easy, its bark is way worse than its bite.

protected void load_DailyCommentary(Object sender, EventArgs e)
{
FileLength=0;
FileLength2=0;

string filekey = Request.QueryString["key"];
string status = Request.QueryString["status"];
System.Web.HttpPostedFile UpFile2 = UP_FILE2.PostedFile;
System.Web.HttpPostedFile UpFile = UP_FILE.PostedFile;

try
{
if (UpFile.ContentLength == 0)
{
txtMessage.Text = "* There was no Daily Commentary file to upload";
}
else
{
FileInfo fi = new FileInfo(UpFile.FileName);
FileLength = UpFile.ContentLength;
System.Byte[] FileByteArray = new System.Byte[FileLength];
System.IO.Stream StreamObject = UpFile.InputStream;
StreamObject.Read(FileByteArray,0,FileLength);
System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;Data Source=***;UID=***;PWD=***;Database=***");
System.String SqlCmd = "INSERT INTO FI_retailtrading (PDF, webfilename, mimetype, lastmod, filesize, category, title) VALUES (?, ?, ?, ?, ?, ?, ?)";

System.Data.OleDb.OleDbCommand OleDbCmdObj = new System.Data.OleDb.OleDbCommand(SqlCmd, Con);

OleDbCmdObj.Parameters.Add("@PDF", System.Data.OleDb.OleDbType.Binary, FileLength).Value = FileByteArray; OleDbCmdObj.Parameters.Add("@webfilename", System.Data.OleDb.OleDbType.VarChar,50).Value = fi.Name;
OleDbCmdObj.Parameters.Add("@mimetype", System.Data.OleDb.OleDbType.VarChar,100).Value = UpFile.ContentType;
OleDbCmdObj.Parameters.Add("@lastmod", System.Data.OleDb.OleDbType.Date,8).Value = DateTime.Now;
OleDbCmdObj.Parameters.Add("@filesize", System.Data.OleDb.OleDbType.Integer,4).Value = UpFile.ContentLength;
OleDbCmdObj.Parameters.Add("@category", System.Data.OleDb.OleDbType.VarChar,100).Value = "Daily Comments";
OleDbCmdObj.Parameters.Add("@title", System.Data.OleDb.OleDbType.VarChar,100).Value = "Kevin Giddis Daily Commentary";

Con.Open();
OleDbCmdObj.ExecuteNonQuery();
Con.Close();
txtMessage.Text = "* The Daily Commentary was uploaded successfully.";
}
}
catch (System.Exception ex)
{
txtMessage.Text = ex.Message.ToString();
}
try
{
if (UpFile2.ContentLength == 0)
{
txtMessage2.Text = "* There was no Interest Rate Monitor file to upload";
}
else
{

FileLength2 = UpFile2.ContentLength;
FileInfo fi2 = new FileInfo(UpFile2.FileName);

System.Byte[] FileByteArray2 = new System.Byte[FileLength2];
System.IO.Stream StreamObject2 = UpFile2.InputStream;
StreamObject2.Read(FileByteArray2,0,FileLength2);
System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;Data Source=***;UID=***;PWD=***;Database=***");
System.String SqlCmd = "INSERT INTO FI_retailtrading (PDF, webfilename, mimetype, lastmod, filesize, category, title) VALUES (?, ?, ?, ?, ?, ?, ?)";

System.Data.OleDb.OleDbCommand OleDbCmdObj2 = new System.Data.OleDb.OleDbCommand(SqlCmd, Con);

OleDbCmdObj2.Parameters.Add("@PDF", System.Data.OleDb.OleDbType.Binary, FileLength).Value = FileByteArray2; OleDbCmdObj2.Parameters.Add("@webfilename", System.Data.OleDb.OleDbType.VarChar,50).Value = fi2.Name;
OleDbCmdObj2.Parameters.Add("@mimetype", System.Data.OleDb.OleDbType.VarChar,100).Value = UpFile2.ContentType;
OleDbCmdObj2.Parameters.Add("@lastmod", System.Data.OleDb.OleDbType.Date,8).Value = DateTime.Now;
OleDbCmdObj2.Parameters.Add("@filesize", System.Data.OleDb.OleDbType.Integer,4).Value = UpFile2.ContentLength;
OleDbCmdObj2.Parameters.Add("@category", System.Data.OleDb.OleDbType.VarChar,100).Value = "Interest Rates";
OleDbCmdObj2.Parameters.Add("@title", System.Data.OleDb.OleDbType.VarChar,100).Value = "Kevin Giddis Interest Rate Market Monitor";

Con.Open();
OleDbCmdObj2.ExecuteNonQuery();
Con.Close();
txtMessage2.Text = "* The Interest Rate Monitor was uploaded successfully.";
}
}
catch (System.Exception ex)
{
txtMessage2.Text = ex.Message.ToString();
}
getBinder();
}

Derick Mayberry
Senior Web Developer
Morgan Keegan & Co., Inc.
Memphis, TN 38133

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-24 : 17:51:11

You did a read from 0 to the length of the file, that should work.
~~I thought you needed to call StreamObject.ReadAll() to getl all of the bytes? Maybe I'm wrong.~~

What you need to do to test is take that ByteArray and write that out to a file. If you can open that file, then it's a problem with your SQL. If you can't read that file, it's a problem with the Stream / ByteArray.

Let us know how the writing to file goes.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

maybede
Starting Member

5 Posts

Posted - 2004-03-25 : 08:54:51

According to the MSDN there is not a method called ReadAll() for the Stream Class or the StreamReader Class. I am going to write my ByteArray to a file and see if that works and maybe that will give me some clues.

Derick Mayberry
Senior Web Developer
Morgan Keegan & Co., Inc.
Memphis, TN 38133

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources