Reading Database BLOB with showing progress information

**Michael Vilain** · Sep 27 '05, 07:25 PM

Re: Reading Database BLOB with showing progress information

In article <1127836737.174 161.124160@f14g 2000cwb.googleg roups.com>,
"ssp" <ssp@planicswar e.de> wrote:
[color=blue]
> Dear all,
>
> I'm dealing with a very tricky problem and can't seem to find the
> answer with google. the problem is: i want to store huge data
> (binaries) inside a mysql databases blob - to later store those data to
> a file somewhere else (from where i can connect the database).
>
> problem comes into play, when I try to output a "progress" of the
> reading of this data (kind of "xx bytes of XXX bytes already
> transmitted").
>
> Does PHP / PDO /PEAR:DB has those Buffered Read capabilities for Table
> Columns ?
>
> Has anybody done this before ?
>
> Many thanks in advance....
> Soeren[/color]

Don't think you'll be able to do this, AFAIK. Leastways, if you do, it
won't be with php, which is strictly server-side.

With php, the best I think you can do is report the total bytes to of
the object to be transfered in the headers and the browser will report
file transfer progress if it's supports that. IE and Safari do this.
But having php talk to the browser and continuously report back periodic
progress isn't possible.

You might start looking into writing a java-based client-server pair
that can do the handshaking, file transfer, and report on progress.

--
DeeDee, don't press that button! DeeDee! NO! Dee...

**ssp** · Sep 28 '05, 08:35 AM

Re: Reading Database BLOB with showing progress information

thanks for your answer.

the problem is: i'm writing a kind of multiplatform php-cli application
and do not have a browser - so reporting the transfered bytes is a very
handy feature...do you have any other ideas?

many thanks

**ssp** · Sep 28 '05, 11:25 AM

Solution (was: Re: Reading Database BLOB with showing progress information)

Dear all,

finally I found a solution (or better our sysadmin told me to read
mysql manual user comments in detail;):

the trick is: mysql trades BLOBs as kind of huge strings. so one can
use LENGTH on a column to get its content-length and then read out
using SUBSTRING sequentially.

snippet (assuming that $__DB = PEAR:DB Connection is correctly
initiated) :
<?
$stQuery = "SELECT LENGTH(PIF_FILE _BLOB) FROM PIF WHERE PIF_ID=2";
$iFileSize = $__DB->getOne($stQuer y);

$i=0;
$iPos = 1; //Startposition
$iLength = 4096; //Readbuffer size
$iTotal = ($iFileSize/$iLength); //how many queries ?
$iStatus = ceil($iTotal/20); //after 20 queries print out a char

//open Output File Handle
printf("Lade neu.exe [%d kB] [",ceil($iFileSi ze/1024));
$oFileHandle = fopen('.dev/neu.exe','w');

while (!isset($stRead ) || $stRead!='') {
$i++;
$stQuery = sprintf("SELECT substring(PIF_F ILE_BLOB,%d,%d) FROM PIF
WHERE PIF_ID=2",$iPos ,$iLength);
$iPos+=$iLength ;
$stRead = $__DB->getOne($stQuer y);
fwrite($oFileHa ndle,$stRead);
if ($i%$iStatus==0 ) echo "=";
}
echo "]";

fclose($oFileHa ndle);

?>

This works very good for me

Soeren Sproessig

**Gary L. Burnore** · Sep 28 '05, 11:35 AM

Re: Reading Database BLOB with showing progress information

On 28 Sep 2005 01:30:28 -0700, "ssp" <ssp@planicswar e.de> wrote:
[color=blue]
>thanks for your answer.
>
>the problem is: i'm writing a kind of multiplatform php-cli application
>and do not have a browser - so reporting the transfered bytes is a very
>handy feature...do you have any other ideas?[/color]

Incorporate a browser.
--
gburnore@databa six dot com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº Ý³Þ³ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº Ý³Þ³ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
DataBasix | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº Ý³Þ³ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ 3 4 1 4 2 Ý³Þ³ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
=============== =============== =============== =============== ===============
Want one? GET one! http://signup.databasix.com
=============== =============== =============== =============== ===============

**Gary L. Burnore** · Sep 28 '05, 11:45 AM

Re: Solution (was: Re: Reading Database BLOB with showing progress information)

On 28 Sep 2005 04:21:07 -0700, "ssp" <ssp@planicswar e.de> wrote:
[color=blue]
>Dear all,
>
>finally I found a solution (or better our sysadmin told me to read
>mysql manual user comments in detail;):
>
>the trick is: mysql trades BLOBs as kind of huge strings. so one can
>use LENGTH on a column to get its content-length and then read out
>using SUBSTRING sequentially.
>
>snippet (assuming that $__DB = PEAR:DB Connection is correctly
>initiated) :
><?
>$stQuery = "SELECT LENGTH(PIF_FILE _BLOB) FROM PIF WHERE PIF_ID=2";
>$iFileSize = $__DB->getOne($stQuer y);
>
>$i=0;
>$iPos = 1; //Startposition
>$iLength = 4096; //Readbuffer size
>$iTotal = ($iFileSize/$iLength); //how many queries ?
>$iStatus = ceil($iTotal/20); //after 20 queries print out a char
>
>//open Output File Handle
>printf("Lade neu.exe [%d kB] [",ceil($iFileSi ze/1024));
>$oFileHandle = fopen('.dev/neu.exe','w');
>
>while (!isset($stRead ) || $stRead!='') {
> $i++;
> $stQuery = sprintf("SELECT substring(PIF_F ILE_BLOB,%d,%d) FROM PIF
>WHERE PIF_ID=2",$iPos ,$iLength);
> $iPos+=$iLength ;
> $stRead = $__DB->getOne($stQuer y);
> fwrite($oFileHa ndle,$stRead);
> if ($i%$iStatus==0 ) echo "=";
>}
>echo "]";
>
>fclose($oFileH andle);
>
>?>
>
>
>This works very good for me[/color]

I've seen this sort of thing before. Adding some time to the actual
process to show progress makes the user believe it's going "faster"
than if they see nothing happening.
--
gburnore@databa six dot com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº Ý³Þ³ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº Ý³Þ³ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
DataBasix | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº Ý³Þ³ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ 3 4 1 4 2 Ý³Þ³ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
=============== =============== =============== =============== ===============
Want one? GET one! http://signup.databasix.com
=============== =============== =============== =============== ===============

Reading Database BLOB with showing progress information

Reading Database BLOB with showing progress information

Comment

Comment

Comment

Comment

Comment