Solved

Saving a pdf file in to MSSQL database

Posted on 2013-06-03
5
744 Views
Last Modified: 2013-06-10
Hi,

I am using Zend_File_Transfer_Adapter_Http to upload a file to server and then use the file_get_contents to read the contents and encode using base64 to save to mssql database.

$tmpContnets = file_get_contents($filename, FILE_USE_INCLUDE_PATH);
$filecontent = base64_encode($tmpContnets);	
$newFileInfo['intDocSectionId'] = $fileSection;
$newFileInfo['intDocSize'] = $sizeOfFile;
$newFileInfo['strDocType'] = $filemimetype;
$newFileInfo['strDocName'] = $displayFileName;
$newFileInfo['strDocContents'] = $filecontent; 
$newFileInfo['datDocCreated'] = gmdate('Y-m-d H:i:s');
$newFileInfo['intDocCreatedBy'] = $user->userid;
$newFileInfo['strExt'] = $docext;

$newDoc->putNewDocument($newFileInfo)

Open in new window



to save I am using a class that extends Zend_Db_Table_Abstract.
class ABC_DbTable_Documents extends Zend_Db_Table_Abstract {
	protected $_schema = 'Accounts.dbo';
	protected $_name = 'tDocuments';
	protected $_primary = 'intDocId';

Open in new window



public function putNewDocument(array $newDocument) {
        
        try {
              $newRow = $this->createRow($newDocument);
              $newRow->save();
            return true;
        } catch (Exception $e){
            Log::error(get_class(), __LINE__, $e->getMessage(), true);
            return false;
        }
    }

Open in new window



The question is, when this code runs, it take supper long time to save the contents in to the database. The database is MSSQL. Document Content filed is set as varchar(MAX).

any idea what is happening here..

thanks
Peiris
0
Comment
Question by:Peiris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39217467
No idea; there is not enough information here to make a determination.  But I can give you some ideas.  Almost always when there is a slow script the problem is found in the I/O subsystem (which makes sense because disk I/O operations are orders of magnitude slower than in-memory processing).  So here is what I would do.  First try uploading a very, very small file, say 1K, and time the operation.  Then upload a 10K file and time the operation.  Did it take ten times longer?  If so the situation is probably caused by the speed of the internet connection.  Next, I would go into the code and remove the upload - just keeping the data base insertion.  Is it still slow without the upload?  If not, you can pretty much indict the speed of the internet connection.

If none of these things have any measurable effect on response time, then you might want to look at using YSlow to see if the overall script is just slow.  That happens sometimes with frameworks.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39217881
What version of SQL Server are you using? If it's 2012 take a look at the File Table feature, which is a way of keeping files under the thumb of the database management system without ever actually reading them in to tables.

hth

Mike
0
 
LVL 7

Expert Comment

by:Robert Saylor
ID: 39219213
I do not know MSSQL but I do know MySQL. In MySQL you do not store binary in a VARCHAR but you use a BLOB or a LONGBLOB. Might be the same for MSSQL. Hope this helps.
0
 

Accepted Solution

by:
Peiris earned 0 total points
ID: 39222519
I have found a way to work around with this. Instead using ZendFramework save methods, we use a stored procedure to save the info in to the MSSQL. This solve the issue. My guess is that when we use the Framework built in save methods, it try to check for security issues before it saving. Since its a base64 encoded files there are lot of text that need to be process before saving to the database (this may take long time). We are skipping that by directly binding a stored procedure to save the information.
0
 

Author Closing Comment

by:Peiris
ID: 39234090
I have found a solution.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php non-object 7 29
Doubt with angularJs with PHP 4 22
Query Task 8 23
ErrorKind in crystal reprot VB.Net 1 18
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question