Reading Database BLOB with showing progress information

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

re: Reading Database BLOB with showing progress information

In article <1127836737.174161.124160@f14g2000cwb.googlegroups .com>,
"ssp" <ssp@planicsware.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...

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

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($stQuery);

$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($iFileSize/1024));
$oFileHandle = fopen('.dev/neu.exe','w');

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

fclose($oFileHandle);

?>


This works very good for me

Soeren Sproessig

re: Reading Database BLOB with showing progress information

On 28 Sep 2005 01:30:28 -0700, "ssp" <ssp@planicsware.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@databasix 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
================================================== =========================

re: Reading Database BLOB with showing progress information

On 28 Sep 2005 04:21:07 -0700, "ssp" <ssp@planicsware.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($stQuery);
>
>$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($iFileSize/1024));
>$oFileHandle = fopen('.dev/neu.exe','w');
>
>while (!isset($stRead) || $stRead!='') {
> $i++;
> $stQuery = sprintf("SELECT substring(PIF_FILE_BLOB,%d,%d) FROM PIF
>WHERE PIF_ID=2",$iPos,$iLength);
> $iPos+=$iLength;
> $stRead = $__DB->getOne($stQuery);
> fwrite($oFileHandle,$stRead);
> if ($i%$iStatus==0) echo "=";
>}
>echo "]";
>
>fclose($oFileHandle);
>
>?>
>
>
>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@databasix 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
================================================== =========================