Microsoft KB Archive/62874

INF: Unloading Large Image Data Files from SQL Server

PSS ID Number: Q62874 Article last modified on 03-31-1994

1.00 OS/2

Question
Given that it is possible to store rows of image data larger than 64K in SQL Server [using dbwitetext and dbmoretext], how can you retrieve this data from within an application program?

Response
This question often arises about reading text/image data greater than 64K. DB-LIBRARY (DB-Lib), as well as the operating system, cannot allocate a block of data larger than 64K (at least, not without using huge pointers, which dblib does not use). To read a text/image column larger than 64K, you must read the column a single piece at a time, from a single row at a time. The following is an example of how this is done: size = min(1024L,length_of_text_column); buffer = malloc((int)size); offset = 0L; while(offset < length_of_text_column) { // The length of the column is determined by the following query // (it returns a DBINT): // select datalength(textcolumn) from texttable where // some_column_value = some_identifying_value_for_the_row_you_want // You send the following commands down to the server with the // following command: dbcmd(dbproc,“declare @val varbinary(30)”); // Please note that the text pointer only points to the last row in the // text column for the select clause rows. You should word your select // to return only one row at a time; the brackets in the command // listed below are used to indicate that the WHERE clause is optional. // Be sure to fill in the WHERE clause to obtain the row desired. dbcmd(dbproc,“select @val = textptr(columnname) from tablename [where clause]”); dbfcmd(dbproc,“readtext table.column @val %ld %ld”,offset,size); dbsqlexec(dbproc); // execute the command while(dbresults(dbproc)!=NO_MORE_RESULTS) // Eat the text pointer, not needed. { if(DBROWS(dbproc)); // Check for the returned text pointer. { dbnextrow(dbproc); // Discard the returned text pointer. break; // Get out of the loop. } } dbresults(dbproc); // Results of text returned. dbbind(dbproc,1,CHARBIND,(DBINT)0,buffer) // Bind text, buffer MUST be >= readtext size parameter. dbnextrow(dbproc); // Text is bound now for that piece for the // current row. dbnextrow(dbproc); // Used to end the row processing. // Now do it all over, but set the offset to the next block, which is // past the size bytes of the first. offset += size; // For next loop. if(offset + size > length_of_text_column) size = length_of_text_column - offset; } In the Software/Data Library is a file named IMAGE.EXE that demonstrates how you can load and unload large image files into and out of SQL Server. IMAGE.EXE can be found in the Microsoft Software Library on GEnie, CompuServe, and Microsoft OnLine by searching on the word IMAGE.EXE, the Q number of this article, or S12606. IMAGE.EXE is a compressed, self- extracting file. After you download the file, run it to extract the file(s) it contains.

= Additional reference words: 1.00 dblib =

Copyright Microsoft Corporation 1994.