MySQL BLOB: random access via DBI ?

I suspect that I know the answer already but ..

does DBD::mysql (or the MySQL API for that matter) support random
access to BLOB types ? i.e. the ability to select/insert
a BLOB via a serious of small queries, as opposed to the
memory intensive approach of selecting/inserting the total
contents of the column in one query.

I've found nothing useful via Google, or the MySQL site
though what I can see in the mysql API documentation leads
me to believe that this isn't supported at all.

--
Regards

Stephen Collyer
Netspinner Ltd

Re: MySQL BLOB: random access via DBI ?

> does DBD::mysql (or the MySQL API for that matter) support random
> access to BLOB types ? i.e. the ability to select/insert
> a BLOB via a serious of small queries, as opposed to the
> memory intensive approach of selecting/inserting the total
> contents of the column in one query.

It's unlikely that any scenario would allow you to select/insert via
a serious of small queries. Some approaches might allow you to
do it via a series of small queries.

RE: MySQL BLOB: random access via DBI ?

On 25-Jul-2006 Stephen Collyer wrote:
> I suspect that I know the answer already but ..
>
> does DBD::mysql (or the MySQL API for that matter) support random
> access to BLOB types ? i.e. the ability to select/insert
> a BLOB via a serious of small queries, as opposed to the
> memory intensive approach of selecting/inserting the total
> contents of the column in one query.
>
> I've found nothing useful via Google, or the MySQL site
> though what I can see in the mysql API documentation leads
> me to believe that this isn't supported at all.
>
> --
> Regards
>
> Stephen Collyer
> Netspinner Ltd

Ignoring the "series of small queries" bit of your question as I'm not sure what
that would imply. If you just wanted to do the insert/select in smaller chunks:

ODBC API does allow the retrieval or insertion of large objects piecemeal but
it is not random. To retrieve in 1k chunks you use (vastly simplified)

SQLPrepare(select blobfield from table);
SQLExecute
SQLFetch
while (SQLGetData(col=1, buffer, 1k) != SQL_NO_DATA);

(I think DBD::ODBC may even do internally this on large fields - can't
remember).

To insert in 1k chunks (again vastly simplified):

SQLPrepare(insert into table (blobfield) values(?))
SQLBindParam(1, DATA_AT_EXEC);
SQLExecute;
while (still some to send)
SQLPutData(buffer, 1k);

These approaches do allow some apps to avoid having the entire large field in
memory since you can read/insert a little at a time e.g. read 1k chunks from a
file and push them to the db avoiding needing to read the entire file.

I don't know how this ends up in the myodbc driver (and hence mysql client
libs) but it may be something similar.

As far as I know there is no interface to this in DBD::mysql or DBI.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

RE: MySQL BLOB: random access via DBI ?

Looking at the MySQL API I'd have to agree that there's no obvious way of
doing this efficiently. I don't see anything equivalent to, for example,
Oracle's "piecewise LOB fetch" functionality, or other ways of "streaming"
the BLOB rather than fetching/updating all in one go.

For fetching I suppose you could hack it together with SUBSTR (but is it
valid to use that on binary data?). I doubt it'd be very friendly to the
server, although it would reduce the client memory issues.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

-----Original Message-----
From: Stephen Collyer [mailto:scollyer@netspinner.co.uk]
Sent: 25 July 2006 14:25
To: dbi-users@perl.org
Subject: MySQL BLOB: random access via DBI ?

I suspect that I know the answer already but ..

does DBD::mysql (or the MySQL API for that matter) support random access to
BLOB types ? i.e. the ability to select/insert a BLOB via a serious of small
queries, as opposed to the memory intensive approach of selecting/inserting
the total contents of the column in one query.

I've found nothing useful via Google, or the MySQL site though what I can
see in the mysql API documentation leads me to believe that this isn't
supported at all.

--
Regards

Stephen Collyer
Netspinner Ltd

Re: MySQL BLOB: random access via DBI ?

Andy Hassall wrote:
> Looking at the MySQL API I'd have to agree that there's no obvious way of
> doing this efficiently. I don't see anything equivalent to, for example,
> Oracle's "piecewise LOB fetch" functionality, or other ways of "streaming"
> the BLOB rather than fetching/updating all in one go.

Right. I suspect I'm on a wild goose chase. Still, it makes me wonder
how people get large BLOBs in and out of MySQL - if you've got 500MB
in the DB, do you really want to be tying up that much memory in the
server each time you fetch or insert it ?

(And another problem that comes to mind, though it's off-topic
for this list, is what to do about max_allowed_packet; I
believe this has to be set on both client and server to accommodate
the full size of the query, which is potentially a problem if
you have no control over the server).

> For fetching I suppose you could hack it together with SUBSTR (but is it
> valid to use that on binary data?). I doubt it'd be very friendly to the
> server, although it would reduce the client memory issues.

Are you referring to Perl's substr() or MySQL's SUBSTRING() ? Either
way, I'm not sure that it would make much difference. This is from
the "How mysql uses memory" page of the mysql docs:

>For each table having BLOB columns, a buffer is enlarged dynamically
>to read in larger BLOB values. If you scan a table, a buffer as large
>as the largest BLOB value is allocated.

From that, I get the impression that mysql is pretty naive in terms of
memory minimization w.r.t BLOBs.

--
Regards

Stephen Collyer
Netspinner Ltd

RE: MySQL BLOB: random access via DBI ?

Stephen Collyer wrote:
>Andy Hassall wrote:
>> For fetching I suppose you could hack it together with SUBSTR (but is
>> it valid to use that on binary data?). I doubt it'd be very friendly
>> to the server, although it would reduce the client memory issues.
>
>Are you referring to Perl's substr() or MySQL's SUBSTRING() ? Either way,
I'm not sure that >it would make much difference. This is from the "How
mysql uses memory" page of the mysql
>docs:
>
>>For each table having BLOB columns, a buffer is enlarged dynamically
>>to read in larger BLOB values. If you scan a table, a buffer as large
>>as the largest BLOB >value is allocated.
>
> From that, I get the impression that mysql is pretty naive in terms of
memory
>minimization w.r.t BLOBs.

I was thinking MySQL's substr (alias of substring), but from what you say
above, it won't help.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool