November 9th, 2011, 12:37 PM
[RESOLVED] ODBC: Retrieveing large data
I'm working on a set of classes to wrap some of the ODBC functionality (connection, statements and resultsets) on Win32 using VC++.
Connection and statements were pretty easy, the problem is with resultsets. The logic behind my implementation is:
- Create a Connection object make the connection.
- Create a Statement object, set the SQL and prepare it.
- Call Execute(...) from statement to get a ResultSet object with the first row of data.
- Call Fetch(...) on ResultSet object until it returns false (no more rows)
The Fetch(...) function takes the prepared statement, checks each column's type, nullability and size, creates a data structure to hold the values, binds the columns and calls SQLFetch(...).
Now, the data structure used to hold each column is a linked list of ColumnItem objects (type, name, size, isnullable, isnull and an union with short, long, float, double, etc). I have no problem with the simple data types such as INT, BIGINT, SHORT, etc. The problem is with VARCHAR, LONGVARCHAR, BINARY, LONGVARBINARY, that is, large variable data types.
ODBC provides some functions like SQLDescribeCol and SQLColAttribute to get the metadata of each column, but the reported size is the 'maximum size' not the actual size of the stored data, e.g. LONGVARCHAR can store up to 4GB of text but even if I'm only storing "Hello World" its reported size is 4GB. I've tried with SQLColAttribute and SQL_DESC_OCTET_LENGTH & SQL_DESC_DISPLAY_SIZE but none of them give me the required buffer size.
My first question is: Is there any way to know the required buffer size for these data types?
The current implementation caps both data types to 255 bytes/chars and if the coder wants more juice he/she must modify the m_nMaxBytes/m_nMaxChars member before fetching anything. Another option is using SQLGetData but I've heard that it may degrade performance, so my second question is: Is this a good approach or there's a better one?
An ideal solution would be getting the required buffer size directly, allocating, binding and fetching. But LONGVARCHAR columns report a 4GB size and the runtime says 'Are you crazy? I can't allocate that amount of memory' (at least on a x86 system) and, even if the allocation were sucesfull it would be a terrible waste of resources, so I'm a bit stuck.
I know that MFC does all this and you can even use RFX functions to bind data and so, but I'm trying to get rid of MFC because of its complexity (and I know it's not so hard to learn but it's very hard to find another MFC coder and so I end up doing all the coding), so MFC it's not an option.
Any hints/comments/solutions/tips will be greatly appreciated =)
P.S. Sorry for the english.
"A program is never less than 90% complete, and never more than 95% complete."
Tags for this Thread
Click Here to Expand Forum to Full Width
This is a Codeguru.com survey!