note 56465 modified in features.file-upload by danbrown

I have been having issues with putting data in to an MSSQL database from an uploaded file. Trying to INSERT a file in excess of 25MB caused "Insufficient memory" errors from the SQL server I decided to chunk the data into the database rather than trying to spurt it all in at once. The memory management is much better and from Submit to in the DB takes about 1 second per MB. The machine has dual Pentium 3 933MHz and 2GB RAM. First things first I had to write a stored procedure. I saw no point in attempting to return a TEXTPTR(), which is required for the UPDATETEXT function to work, back to PHP so I didn't even bother. This is my very first stored procedure since this is really the first time I've developed solely for MSSQL. The important thing is that it's functional. Here's the code I used for my stored procedure. After writing this I need to execute it in a loop to get all the data in, in the correct order. I leave that bit to PHP of course. Please don't tell me I could have just written it all in the stored procedure using another language, this is PHP and MSSQL we're talking about. :) The zipfile column data type is TEXT, I tried IMAGE but it was problematic dealing with HEX data. ///////////// SP////////////////////// CREATE PROCEDURE dbo.dds_writeBlob @dataChunk AS TEXT, @refCode AS VARCHAR(50), @offSet AS INT AS DECLARE @dataPtr AS BINARY(16) SELECT @dataPtr=TEXTPTR(zipfile) FROM [dbo].[file] (UPDLOCK) WHERE [dbo].[file].ref = @refCode UPDATETEXT [dbo].[file].zipfile @dataPtr @offSet 0 @dataChunk GO //////////////////END SP///////////////// Then of course comes the PHP code to do the chunking. Firstly I have to convert the binary data to a type that can be accepted by TEXT data type. base64_encode() comes in handy for this purpose but of course I need it in chunks so I used chunk_split() and split it in to chunks of 256000 bytes by declaring the optional [chunklen]. I then explode() it in to a numerically indexed array using the newlines ("\r\n") that are inserted every [chunklen] by chunk_split. I can then loop through with a for(), passing the data chunks, in order, one at a time to the stored procedure. Here's the code: <?php $data = chunk_split(base64_encode(fread(fopen($file, "rb"), filesize($file))), 256000); $arrData = explode("\r\n", $data); unset($data); // Clear memory space $num = count($arrData); $offset = 0; for ($i=0;$i<$num;$i++) { $buffer = $arrData[$i]; $query = "EXEC [dds_writeBlob] '".$buffer."', '$reference', $offset"; @mssql_query($query) or die(mssql_get_last_message()); $offset = ($offset + strlen($buffer)); } ?> Of course we then need to extract the data from the database. This, thankfully, is a lot easier! Simply base64_decode() the data before outputting to a browser. <? $query = "SELECT zipfile, job_code FROM [file] WHERE ref = '$ref'"; @$result = mssql_query($query) or die('File Download: Failed to get file from the database.'); $file = mssql_fetch_assoc($result); header('Content-type: application/zip'); header('Content-Disposition: attachment; filename="job-'.trim($file['job_code']).'.zip"'); echo base64_decode($file['zipfile']); ?> The benefit of storing in base64_encoded format is the simplicity with which you can now send mime emails with the data attached as an alternative to having people download it. A simple chunk_split() on the SELECTed data will have it in the right format for mime mails. The draw back is the extra size required! Expect the base64_encoded data to be 1.33 times the size of the original data! --was-- I have been having issues with putting data in to an MSSQL database from an uploaded file. Trying to INSERT a file in excess of 25MB caused "Insufficient memory" errors from the SQL server I decided to chunk the data into the database rather than trying to spurt it all in at once. The memory management is much better and from Submit to in the DB takes about 1 second per MB. The machine has dual Pentium 3 933MHz and 2GB RAM. First things first I had to write a stored procedure. I saw no point in attempting to return a TEXTPTR(), which is required for the UPDATETEXT function to work, back to PHP so I didn't even bother. This is my very first stored procedure since this is really the first time I've developed solely for MSSQL. The important thing is that it's functional. Here's the code I used for my stored procedure. After writing this I need to execute it in a loop to get all the data in, in the correct order. I leave that bit to PHP of course. Please don't tell me I could have just written it all in the stored procedure using another language, this is PHP and MSSQL we're talking about. :) The zipfile column data type is TEXT, I tried IMAGE but it was problematic dealing with HEX data. ///////////// SP////////////////////// CREATE PROCEDURE dbo.dds_writeBlob @dataChunk AS TEXT, @refCode AS VARCHAR(50), @offSet AS INT AS DECLARE @dataPtr AS BINARY(16) SELECT @dataPtr=TEXTPTR(zipfile) FROM [dbo].[file] (UPDLOCK) WHERE [dbo].[file].ref = @refCode UPDATETEXT [dbo].[file].zipfile @dataPtr @offSet 0 @dataChunk GO //////////////////END SP///////////////// Then of course comes the PHP code to do the chunking. Firstly I have to convert the binary data to a type that can be accepted by TEXT data type. base64_encode() comes in handy for this purpose but of course I need it in chunks so I used chunk_split() and split it in to chunks of 256000 bytes by declaring the optional [chunklen]. I then explode() it in to a numerically indexed array using the newlines ("\r\n") that are inserted every [chunklen] by chunk_split. I can then loop through with a for(), passing the data chunks, in order, one at a time to the stored procedure. Here's the code: <? $data = chunk_split(base64_encode(fread(fopen($file, "rb"), filesize($file))), 256000); $arrData = explode("\r\n", $data); unset($data); // Clear memory space $num = count($arrData); $offset = 0; for ($i=0;$i<$num;$i++) { $buffer = $arrData[$i]; $query = "EXEC [dds_writeBlob] '".$buffer."', '$reference', $offset"; @mssql_query($query) or die(mssql_get_last_message()); $offset = ($offset + strlen($buffer)); } ?> Of course we then need to extract the data from the database. This, thankfully, is a lot easier! Simply base64_decode() the data before outputting to a browser. <? $query = "SELECT zipfile, job_code FROM [file] WHERE ref = '$ref'"; @$result = mssql_query($query) or die('File Download: Failed to get file from the database.'); $file = mssql_fetch_assoc($result); header('Content-type: application/zip'); header('Content-Disposition: attachment; filename="job-'.trim($file['job_code']).'.zip"'); echo base64_decode($file['zipfile']); ?> The benefit of storing in base64_encoded format is the simplicity with which you can now send mime emails with the data attached as an alternative to having people download it. A simple chunk_split() on the SELECTed data will have it in the right format for mime mails. The draw back is the extra size required! Expect the base64_encoded data to be 1.33 times the size of the original data! http://php.net/manual/en/features.file-upload.php