0

I have a MySQL database with a mediumblob column. I want to get binary data from the blob field into a C++ stringstream using ODBC on Windows.

I first call SQLDescribeCol and it indicates that it is of type SQL_LONGVARBINARY.

I then make a call to SQLGetData as follows:

SQLLEN indicator;
SQLCHAR SqlChar[8000];
SQLGetData(m_sqlstatementhandle, i, SQL_CHAR, &SqlChar, sizeof(SqlChar), (SQLLEN*)&indicator);

I then go on and write the data into a stringstream:

stringstream ss;
ss.write((char*)&SqlChar, indicator);

This does give me the blob data, but it is stored in SqlChar as a HEX string.

My program expects the data in the stringstream to be stored as binary. Now I could convert the HEX string to binary first and then write it to the stringstream, but that doesn't feel right to me. I would really like to get it as binary straight away from SQLGetData.

So a couple of questions:

  1. Am I using SQLGetData correctly for a blob type?
  2. Is a SQLCHAR array the right container to write into?
  3. Is there a way to get the data as binary directly from MySQL via ODBC?

Thanks.

Improve this question
2
  • but it is stored in SqlChar as a HEX string. -- How do you know the data is really a hex string? Maybe it is your debugger showing you the data as a hex string, and not that the data is actually a hex string. Also, you should check your return codes when calling ODBC functions. What is the return value after calling SQLGetData? Commented Mar 14, 2016 at 19:31
  • @PaulMcKenzie Thanks for your comment. The above is just some minimal code to make the question clear. I do check return codes in my actual code. As it turns out the fix was pretty simple once I found it. Commented Mar 14, 2016 at 19:46

2 Answers 2

Reset to default
1

Supplying SQL_BINARY as argument is wrong (even if it works here because SQL_BINARY and SQL_C_BINARY seems to be defined to the same value). From the doc of SQLGetData, about the 3rd argument TargetType:

[Input] The type identifier of the C data type of the *TargetValuePtr buffer. For a list of valid C data types and type identifiers, see the C Data Types section in Appendix D: Data Types.

See: https://msdn.microsoft.com/en-us/library/ms715441%28v=vs.85%29.aspx

The input identifier needs to identify the C-type of the buffer used. The c-type identifier are all named something like SQL_C_foobar.

So, you should either pass SQL_C_BINARY as argument (if you would like to tell the driver to get plain binary data) or SQL_C_CHAR (if you would like to tell the driver to convert the data to some "character data"). Note that both, SQL_C_CHAR and SQL_CHAR, are defined to 1.

Some more explanations about your case that the driver converted the data to a "string": As you can see from the list of C Data Types, the buffer-type SQLCHAR can be used for multiple "C type identifiers": If you use a SQLCHAR buffer, and pass SQL_C_CHAR as InputType, the driver "knows" that the buffer is of type SQLCHAR and that you would like to get the data as "character data". So, in your case, MySql will convert the binary data to something human-readable, like a hex-string. On the other hand, if you pass SQL_C_BINARY as InputType, the driver "knows" that the buffer is of type SQLCHAR and that you are interested in pure binary values, so there is no need for the driver to convert the binary data to a human-readable string, and it will just fill the buffer with the plain binary data.

See here for the list of c-types and their corresponding buffer types: https://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx

As a general note: Watch out whether the documentation speaks about the "Sql C Type" or the "Sql Type". The first is used to identify the C-type of a buffer, the other is used to identify the logical database type of a column.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Good explanation as to why it was giving me hex data.
0

Do you ever spend all day trying to figure something out, and then finally find the answer 20 minutes after posting your question? I do.

I noticed on this question that the poster uses SQL_C_BINARY, which is the same as SQL_BINARY. So when I call SQLGetData as follows I get what I expect.

SQLGetData(m_sqlstatementhandle, i, SQL_C_BINARY, &SqlChar, sizeof(SqlChar), (SQLLEN*)&indicator);

So it boiled down to supplying the wrong target type.

Edit: Edited to reflect correct information from @erg's post.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.