What's the best way to read in an entire LOB using ODBC?

Question

Reading in an entire LOB whose size you don't know beforehand (without a max allocation + copy) should be a fairly common problem, but finding good documentation and/or examples on the "right" way to do this has proved utterly maddening for me.

I wrestled with SQLBindCol but couldn't see any good way to make it work. SQLDescribeCol and SQLColAttribute return column metadata that seemed to be a default or an upper bound on the column size and not the current LOB's actual size. In the end, I settled on using the following:

1) Put any / all LOB columns as the highest numbered columns in your SELECT statement

2) SQLPrepare the statement

3) SQLBindCol any earlier non-LOB columns that you want

4) SQLExecute the statement

5) SQLFetch a result row

6) SQLGetData on your LOB column with a buffer of size 0 just to query its actual size

7) Allocate a buffer just big enough to hold your LOB

8) SQLGetData again on your LOB column with your correctly sized allocated buffer this time

9) Repeat Steps 6-8 for each later LOB column

10) Repeat Steps 5-9 for any more rows in your result set

11) SQLCloseCursor when you are done with your result set

This seems to work for me, but also seems rather involved.

Are the calls to SQLGetData going back to the server or just processing the results already sent to the client?

Are there any gotchas where the server and/or client will refuse to process very large objects this way (e.g. - some size threshold is exceeded so they generate an error instead)?

Most importantly, is there a better way to do this?

Thanks!

Answer 1

I see several improvements to be done.

1. If you need to allocate a buffer then you should do it once for all the records and columns. So, you could use the technique suggested by @RickJames, improved with a MAX like this:

   SELECT MAX(LENGTH(blob1)) AS max1, MAX(LENGTH(blob2)) AS max2, ...

You could use max1 and max2 to upfront allocate the buffers, or maybe only the largest one for all columns.

2. The length of the buffer returned at 1. might be too large for your application. You could decide at runtime how large the buffer would be. Anyway, SQLGetData is designed to be called multiple times for each column. Just by calling it again, with the same column number, it will fetch the next chunk. The count of available bytes will be saved where StrLen_or_IndPtr (the last argument) points. And this count will decrease after each call with the amount of bytes fetched.

And certainly there will be roundtrips to the server for each call because the purpose of all this is to prevent the driver from fetching more than the application can handle.

3. The trick with passing NULL as buffer pointer in order to get the length is prohibited in this case, check SQLGetData on Microsoft's Docs.

However, you could allocate a minimal buffer, say 8 bytes, pass it and its length. The function will return the count of bytes written, 7 in our case because the function add a null char, and will put at StrLen_or_IndPtr the count of remaining bytes. But you probably won't need this if you allocate the buffer as explained above.

Note: The LOBs need to be at the end of the select list and must be fetched in that order precisely.

Answer 2

SQLGetData

SQLGetData get the result of already fetched result. For example, if you have SQLFetch the first row of your table, SQLData will send you back the first row. It is used if you don't know if you can SQLBindCol the result.

But the way it is handle depends on your driver and is not describe in the standards. If your database is a SQL database, cursor cannot go backward, so the result may be still in the memory.

Large object query

The server may refuse to process large object according to the server standard and your ODBC Driver standard. It is not described in the ODBC standard.