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
