SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 PDF Images in SQL Server
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

maybede
Starting Member

5 Posts

Posted - 03/23/2004 :  16:00:46  Show Profile
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

USA
4184 Posts

Posted - 03/23/2004 :  19:52:29  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
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.
Go to Top of Page

maybede
Starting Member

5 Posts

Posted - 03/24/2004 :  09:36:04  Show Profile
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 03/24/2004 :  09:46:38  Show Profile
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?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/24/2004 :  09:57:59  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
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.
Go to Top of Page

maybede
Starting Member

5 Posts

Posted - 03/24/2004 :  17:25:53  Show Profile
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 = "<b>* There was no Daily Commentary file to upload</b>";
}
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 = "<p><b>* The Daily Commentary was uploaded successfully.</b>";
}
}
catch (System.Exception ex)
{
txtMessage.Text = ex.Message.ToString();
}
try
{
if (UpFile2.ContentLength == 0)
{
txtMessage2.Text = "<b>* There was no Interest Rate Monitor file to upload</b>";
}
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 = "<p><b>* The Interest Rate Monitor was uploaded successfully.</b>";
}
}
catch (System.Exception ex)
{
txtMessage2.Text = ex.Message.ToString();
}
getBinder();
}

Derick Mayberry
Senior Web Developer
Morgan Keegan & Co., Inc.
Memphis, TN 38133
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 03/24/2004 :  17:51:11  Show Profile  Visit MichaelP's Homepage
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>

Edited by - MichaelP on 03/24/2004 17:52:39
Go to Top of Page

maybede
Starting Member

5 Posts

Posted - 03/25/2004 :  08:54:51  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000