Welcome Guest, Not a member yet? Register   Sign In
Blob from MS SQL DB only returning 64k
#1

[eluser]Unknown[/eluser]
I've got CI working with a client's MS SQL DB, using freetds from a linux server. The only issues I've got is that when I try to get a PDF from the db - which is stored in a Blob on the SQL server, I only get back 64k of the file. I'm assuming that there's some sort of 64k part-limit - and that I need to grab it in chunks... but I can't find a document detailing the process. Can someone point me in the right direction? Code snippet (with all the checks for empty and such removed for clarity):

$query = $this->db->get_where('books','id='.$id);
$row = $query->row();
$this->output
->set_content_type('pdf')
->set_output($row->File_Blob);
#2

[eluser]Unknown[/eluser]
SOLVED - so I thought I should share.

I tried a variety of things to fix this:

changed the 'text size' in the freetds.conf file to 10000000 (roughly 10MB) to accomodate the largest PDFs we have. This bombed horribly as it then tried to allocate 10MB for every retrieved cell from the database. Even if you were only selecting 10 rows of 5 columns at a time - you'd need to allocate 500MB of memory. Not good.

Commented out text size in freetds.conf and modified mssql.textsize in php.ini - same issue as above

Tried to use php's ini_set('mssql.textsize',10000000) just before making the query for the single cell I needed - had no effect

Then I discovered that the mssql.textsize in the php.ini file is utilized at the time the connection is instantiated - so what had to happen was:

Code:
$textset = $this->db->query("SET TEXTSIZE 10000000");
$this->db->select('File_Blob');
$query = $this->db->get_where(‘books’,‘id=’.$id);
$row = $query->row();
$this->output
  ->set_content_type(‘pdf’)
  ->set_output($row->File_Blob);

The query to 'SET TEXTSIZE' lets me pull the whole file - and using the $this->db>select allows me to only grab the one needed cell (thereby going as easy as possible on the memory).

Cheers!
M@




Theme © iAndrew 2016 - Forum software by © MyBB