[RESOLVED] ODBC: Retrieveing large data
Hi everyone.
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.
Re: ODBC: Retrieveing large data
Quote:
Originally Posted by
bioHzrdmX
Hi everyone.
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(...).
ODBC has basically two methods of retrieving data. The one you described is limited in that you are binding variables to the columns before you fetch the data. Once you do that you are limited to how much data you can get (the size of the bound fields). This is described fully in the ODBC documentation.
Quote:
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.
You must not bind the variables if you want all of the data. You must fetch the data, and the call SQLGetData() to get the information you seek. Then you resize or readjust your buffers with the information, and then call SQLGetData() a second time to retrieve the data.
So in a nutshell, your "bind and fetch" method will not work. You need to "fetch/query/retrieve" and that is done by SQLFetch()/SQLGetData()/SQLGetData(), the second call to SQLGetData() being the one that actually retrieves the data with the size info that was gotten from the first call to SQLGetData().
Regards,
Paul McKenzie
Re: ODBC: Retrieveing large data
This thread also may have to do with the bind and fetch method being used, limiting the data being retrieved:
http://www.codeguru.com/forum/showthread.php?t=518156
Regards,
Paul McKenzie
Re: ODBC: Retrieveing large data
Thanks Paul,
I've read the ODBC section of the WinSDK back and forth and never got this clear, so thanks again for your reply.
So it leads to another (and the last one, I promise) question: Fetch/GetData for ALL the fields or Bind/Fetch for fixed-length fields and GetData later for variable-length fields?
I want to get a 'resource/network friendly' approach (if possible).
Re: ODBC: Retrieveing large data
Quote:
Originally Posted by
bioHzrdmX
So it leads to another (and the last one, I promise) question: Fetch/GetData for ALL the fields or Bind/Fetch for fixed-length fields and GetData later for variable-length fields?
Personally, I've never gotten the mixture to work successfully for one result set. So you could write your wrapper to do both methods, but each being exclusive of the other (the result set must either entirely use the Bind/Fetch, or it must use the Fetch/GetData method).
What I've done is in the Fetch/GetData is to always get character data, regardless of the field type (SQL_C_TCHAR in the SQLGetData call). The field type will tell me how to convert it to the correct type(integer, float, etc.).
I would suggest doing this with a simple test case. Don't bind your variables, call SQLFetch(), and then SQLGetData() for each of the columns. You don't need to retrieve anything, just observe how you get the information for each field.
Regards,
Paul McKenzie
Re: ODBC: Retrieveing large data
Thanks Paul, I will try with your method.
So it goes like this: get everything as TCHAR, store and then cast/parse as required. I guess this is the way in which MFC does it.
Again, thanks for your replies =)
Re: ODBC: Retrieveing large data
Quote:
Originally Posted by
bioHzrdmX
Thanks Paul, I will try with your method.
So it goes like this: get everything as TCHAR, store and then cast/parse as required. I guess this is the way in which MFC does it.
Again, thanks for your replies =)
No problem. It's one of those things that stumps a lot of programmers when it comes to ODBC, and that is how to retrieve all the data in a large column, but be memory efficient at the same time (like in your example, you have a 4GB column defined, but the data in the column is only a few bytes).
Just to add, the SQLGetData() last parameter is the address of a variable you provide. On return, this is set to the length of the data. This last parameter is the key to how much to resize your char buffers, and then make the second call to SQLGetData() to actually retrieve the data.
http://msdn.microsoft.com/en-us/libr...=vs.85%29.aspx
Regards,
Paul McKenzie
