C++

Forum

 
How to make a general query function using ODBC

apfinger (9)
Hello, and thanks for reading! I'm a bit stumped on my current task after searching online and trying various ideas. I'm down to just a few lines of code that I'm stuck on. I hope someone can help!

Goal:
I'm trying to create a function that allows me to pass in a query and return whatever the query finds. For example, if I pass in SELECT Name FROM Race, I'll get back a list 1 column wide and as long as the number of records returned. If I pass in SELECT * FROM ContactInfo, I'll get back a list as wide as there are columns and as long as the number of records returned.

So far, I've established a connection to my database using ODBC and confirmed that my application is connected properly by hard coding some queries and viewing the results. Now, I'm trying to substitute generalizations in for the hard coded parts.

Environment:
MS Visual Studio 2010 C++ Express
MS SQL Server 2008 R2 Express

Problem:
SqlGetData has several parameters. Since this function doesn't explicitly know what data to expect, I have to make the function figure it out.
Here's the definition of SqlGetData according to msdn:
1
2
3
4
5
6
7
 
SQLRETURN SQLGetData(
      SQLHSTMT       StatementHandle,  (IN)
      SQLUSMALLINT   Col_or_Param_Num, (IN)
      SQLSMALLINT    TargetType,       (IN)
      SQLPOINTER     TargetValuePtr,   (OUT)
      SQLLEN         BufferLength,     (IN)
      SQLLEN *       StrLen_or_IndPtr);(OUT)


StatementHandle is of course the handle I created in the function.
Col_or_Param_Num comes from the index value of a for loop.
TargetType is retrieved from the SQLDescribeCol method.

TargetValuePtr, BufferLength, and StrLen_or_IndPtr are where I'm stumped.

I -think- TargetValuePtr is supposed to be a pointer to a C++ variable of my choosing to hold the data, while BufferLength is the allocated space for that variable, and StrLen_or_IndPtr is the returned actual data length. Can anyone confirm if I am understanding these terms correctly?

My idea was to ultimately have my function return a two dimensional vector of void pointers. Then, the calling functions, which supplied the query and thus know the expected parameters, can typecast and dereference the pointers.
I haven't set up the return type for my function yet, since I haven't gotten that far in the process.

Can I supply a rather generic pointer to TargetValuePtr for this purpose or must I supply a very exact item for this parameter?

I tried making a SQLPOINTER to pass into SqlGetData, but got null data returned. If I pass in an array of SQLCHAR, I get proper data returned.

Can anybody help? I hope I described the problem accurately. Below is my code. I need to run out the door, so I hope the formatting isn't atrocious.


1
2
3
4
5
6
7
 
static struct _DBRow_QueryList
{
	SQLCHAR			Name[51];
	SQLINTEGER		NameLength;
	SQLCHAR			Text[256];
	SQLINTEGER		TextLength;
} DBRow_QueryList;


1
2
3
4
5
6
7
 
static struct _DBRow_Race
{
    SQLCHAR			Name[51];
	SQLINTEGER		NameLength;
    SQLCHAR			Visible;
	SQLINTEGER		VisibleLength;
} DBRow_Race;


1
2
3
4
5
 
struct DBRow
{
public:
	std::vector<void *> Field;
};


1
2
3
4
5
 
struct DBTable
{
public:
	std::vector<DBRow> Row;
};


1
2
3
4
5
6
7
8
9
10
11
 
static struct ColumnInfo
{
public:
	SQLCHAR			ColumnName[32];
	SQLSMALLINT		ColumnNameLength;
	SQLSMALLINT		ColumnType;
	SQLUINTEGER		ColumnSize;
	SQLSMALLINT		DecimalDigits;
	SQLSMALLINT		Nullable;
	SQLPOINTER		TargetValue;
} _ColumnInfo;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
int DatabaseODBC::Query(std::string QueryName)
{
	// Check that the database connection is established
	if (SQLHandleDatabaseConnection == SQL_NULL_HDBC)
		return -1;

	SqlReturnCode = SQLAllocHandle(SQL_HANDLE_STMT, SQLHandleDatabaseConnection, &SQLHandleStatement);
	if(SqlReturnCode != SQL_SUCCESS && SqlReturnCode != SQL_SUCCESS_WITH_INFO)
		return -2;

	// Retrieve the actual query from the table of queries
	std::string GetQuery = "SELECT QueryText FROM QueryList WHERE QueryName LIKE '" + QueryName + "'";
	SqlReturnCode = SQLExecDirect(SQLHandleStatement, (SQLCHAR*)GetQuery.c_str(), SQL_NTS);
	if(SqlReturnCode != SQL_SUCCESS && SqlReturnCode != SQL_SUCCESS_WITH_INFO)
		return -3;

	std::string QueryToExecute = "";
	while (SQLFetch(SQLHandleStatement) == SQL_SUCCESS)
	{
		SqlReturnCode=SQLGetData(
								SQLHandleStatement,				//SQLHSTMT StatementHandle,
								1,								//SQLUSMALLINT ColumnNumber,
								SQL_C_CHAR,						//SQLSMALLINT TargetType,
								DBRow_QueryList.Text,			//SQLPOINTER TargetValuePtr,
								sizeof(DBRow_QueryList.Text),	//SQLINTEGER BufferLength,
								&DBRow_QueryList.TextLength);	//SQLINTEGER * StrLen_or_IndPtr
		QueryToExecute += (std::string)((char*)DBRow_QueryList.Text);
	}

	//free the statement handle so that it can be reused
	SQLFreeHandle(SQL_HANDLE_STMT, SQLHandleStatement);

	//allocate the statement handle again
	SqlReturnCode = SQLAllocHandle(SQL_HANDLE_STMT, SQLHandleDatabaseConnection, &SQLHandleStatement);
	if(SqlReturnCode != SQL_SUCCESS && SqlReturnCode != SQL_SUCCESS_WITH_INFO)
		return -2;

	//execute the query string retrieved from the QueryList table
	SqlReturnCode = SQLExecDirect(SQLHandleStatement, (SQLCHAR*)QueryToExecute.c_str(), SQL_NTS);
	if(SqlReturnCode != SQL_SUCCESS && SqlReturnCode != SQL_SUCCESS_WITH_INFO)
		return -3;

	SQLSMALLINT NumColumns;
	SqlReturnCode = SQLNumResultCols(SQLHandleStatement, &NumColumns);
	if(SqlReturnCode != SQL_SUCCESS && SqlReturnCode != SQL_SUCCESS_WITH_INFO)
		return -4;

	//Get specifications for each column in order to fetch the data
	std::vector<ColumnInfo> ColInfo;
	ColInfo.resize(NumColumns);
	for (SQLSMALLINT i=0; i<NumColumns; i++)
	{
			SQLDescribeCol (
					SQLHandleStatement,
					i+1,
					ColInfo[i].ColumnName,
					sizeof (ColInfo[i].ColumnName),
					&ColInfo[i].ColumnNameLength,
					&ColInfo[i].ColumnType,
					&ColInfo[i].ColumnSize,
					&ColInfo[i].DecimalDigits,
					&ColInfo[i].Nullable);
			//convert to SQL_CHAR if necessary so SqlGetData knows how to process
			switch (ColInfo[i].ColumnType)
			{
			case SQL_VARCHAR : ColInfo[i].ColumnType = SQL_CHAR; break;
			default : break;
			}
	}
	std::string Result = "";

	int IndexRow	= 0;
	while (SQLFetch(SQLHandleStatement) == SQL_SUCCESS)
	{
		for (SQLSMALLINT IndexColumn=0; IndexColumn < NumColumns; IndexColumn++)
		{
			SqlReturnCode=SQLGetData(
									SQLHandleStatement,						//SQLHSTMT StatementHandle,
									IndexColumn+1,							//SQLUSMALLINT ColumnNumber, starting at 1
									ColInfo[IndexColumn].ColumnType,		//SQLSMALLINT TargetType,
//****************************************************
//									DBRow_Race.Name,						//SQLPOINTER TargetValuePtr,
									_ColumnInfo.TargetValue,				//SQLPOINTER TargetValuePtr,
									sizeof(DBRow_Race.Name),	//SQLINTEGER BufferLength,
									&DBRow_Race.NameLength);//SQLINTEGER * StrLen_or_IndPtr
//****************************************************

//			Result is being used for now just to test my first case. In the end, data will be stored in a two dimensional array of void pointers for the other parts of the application to typecast and dereference
//			Result += (std::string)((char*)DBRow_Race.Name);
			Result += (std::string)((char*)_ColumnInfo.TargetValue);
		}
		IndexRow++;
	}

	//Release the query's handle from memory
	if (SQLHandleStatement != SQL_NULL_HSTMT)
		SQLFreeHandle(SQL_HANDLE_STMT, SQLHandleStatement);
	
	return 0;
}




•Describe the symptoms of your problem carefully and clearly.
•Describe the research you did to try and understand the problem before you asked the question.
•Describe the diagnostic steps you took to try and pin down the problem yourself before you asked the question.
kbw (9488)
What is this meant to do?
 
 
    QueryToExecute += (std::string)((char*)DBRow_QueryList.Text);

apfinger (9)
Thanks for asking. In my rush yesterday, I did not clarify the first part of this function.

I pass into this function a string which is an alias to the actual query to execute. For example, I'd pass in "GetRaces". The first SqlGetData executes a query on a table QueryList, which has two important fields - a QueryName and Query Text field. In that table is a row with 'GetRaces' for QueryName and 'SELECT Name FROM Race' for QueryText.

1
2
3
4
5
6
7
 
static struct _DBRow_QueryList
{
	SQLCHAR			Name[51];
	SQLINTEGER		NameLength;
	SQLCHAR			Text[256];
	SQLINTEGER		TextLength;
} DBRow_QueryList;


I cast DBRow_QueryList.Text into a string simply for my own readability. When I finish functionality of this method, I intend to make it more efficient by removing all the unnecessary casting. For now, though, I for some reason can read my own code better when I see that "string" name.

This code snippet works perfectly fine, minus some error handling I'll add when the overall functionality is complete. At this point in the code, you can assume the string QueryToExecute has a valid SELECT statement in it.

Down lower in the code, I have two lines commented: "DBRow_Race.Name," and "Result += (std::string)((char*)DBRow_Race.Name);"

If I use those two lines instead of the lines directly below each, I can confirm SqlGetData works fine with QueryToExecute = "SELECT Name FROM Race". Result gets values stored in it which match the values in the Race table. (eg "HumanCaucasianLatinoAsian...")

However, with using those two lines, I cannot pass in some other SELECT statement, like SELECT * FROM Race, because DBRow_Race.Name may not be an appropriate data type for other fields. So it's my goal to generalize that item in the second SqlGetData call in this function.

When I compile and run the program as is, using SELECT Name FROM Race, but using _ColumnInfo.TargetValue instead of DBRow_Race.Name, I see a 0x00000000 in _ColumnInfo.TargetValue instead of the first value, "Human", and get an error when doing Result += (std::string)((char*)_ColumnInfo.TargetValue);

Microsoft Visual C++ Debug Library
Debug Assertion Failed!
.....
File:c:\program files\...\xstring
Line:930
Expression: invalid null pointer
For information on how your program can cause an assertion failure, see the Visual C++ documentation on asserts.

It seems clear to me that the assertion failed because SqlGetData did not know how to put the retrieved data into _ColumnInfo.TargetValue, but did not fail there because the data type was a valid SQLPOINTER with a buffer appropriately sized (sizeof(DBRow_Race.Name)). So the assertion failure happened only when trying to access that data source.

To reiterate my problem, I believe my problem is figuring out how to retrieve ambiguous data via SqlGetData.

If anyone is asking where the two dimensional vector of void pointers is, I'll answer now. I haven't implemented it yet. Its implementation will replace the "Result += ..." line, saving the retrieved data in the vector instead of a string. The Result string is simply being used for testing until I have the SqlGetData part figured out.

Thank you for asking, kbw, and thank you anyone else who takes the time to look at my problem.

I hope I didn't ramble too much, and hopefully clarified my problem better for others.
kbw (9488)
I asked about the statement because it's worrying. As a rule of thumb, you don't often need to cast in C++ as the language tends to "do the right thing". In the places you do cast, you need to really think about what you're doing, which is why C++ has it's own four cast types. You'll only confuse anyone trying to read your code with those spurious casts. You are also disabling the compilier's type checking, making your code susceptable to errors. You'll find that 
 
 
    QueryToExecute += DBRow_QueryList.Text;
does the right thing.

SQLGetData() returns the data for a column. Of course, the question is what type is the data? You clearly can't determine that at compile time. It has to be discovered at runtime. You can check what type the column is with SQLDescribeCol().

Depending on what type you know to expect determines what you pass to SQLGetData's. This table maps ODBC to C types for you.
http://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx

So, you call SQLDescribeCol() once for each column per query and remember the column types. Then you pass the appropriate SQL data type and C data type to SQLGetData() for each column in each row.

Last edited on
apfinger (9)
Thanks for the tip on casting. I wasn't aware of some of that information you just gave to me.

With regards to my SQLGetData() problem, I did determine the column type using SQLDescribeCol() and saved that in into the vector ColInfo<ColumnInfo>.

Now that you mention the ODBC to C type mapping, which I've looked at a few times now, I've come up with a crude means of getting the job accomplished. Unfortunately, I don't have time today or tonight to test it, but I do appreciate the help so far and will chime in later whether or not it works.

Off the top of my head, I suppose I could use a switch statement to call SqlGetData(), passing in appropriate column parameters based on the SQL type.

1
2
3
4
5
6
7
 
switch (ColInfo[IndexColumn].ColumnType)
{
  case SQL_CHAR: SqlGetData(...); break;
  case SQL_INTEGER: SqlGetData(...); break;
  case SQL_SMALLINT: SqlGetData(...); break;
...
}


My initial thought had been to simply call SqlGetData once, using some slick way of passing in very generic parameter information. This switch idea may be good enough for now, though, until I familiarize myself with ODBC programming a bit more.

I do appreciate your help so far. I suppose I can handle duplicating a function call a dozen times, if it gets the task done. I was just loathing the idea of duplicating the bulk of this code for every single query my application may ever apply.

By the way, if my immediate thought is not a very good idea, feel free to smack the back of my head.
Last edited on
kbw (9488)
The idea's fine, go for it.
exiledAussie (131)
A while ago I wrapped ODBC to let a user enter a generic query, and I remember using specialized columns, all decendants of a column base class, which did most of the work, utilising virtual functions like 'Get_Bound_Variable()' etc, helping to cut out a lot of replication, though I also remember having to pass a void* to the client function and relying on it to know the type of column it was pointing to when retrieving data.
Last edited on
apfinger (9)
About a week later than I intended to get back to this, but wanted to say that using a switch statement seems to be working.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
static struct ColumnType
{
public:
// Fix Later				//SQL_UNKNOWN_TYPE		//0
SQLCHAR		_SqlChar[8000];	//SQL_CHAR				//1
// Fix Later				//SQL_NUMERIC			//2
// Fix Later				//SQL_DECIMAL			//3
SQLINTEGER	 _SqlInteger;	//SQL_INTEGER			//4
// Fix Later				//SQL_SMALLINT			//5
// Fix Later				//SQL_FLOAT				//6
// Fix Later				//SQL_REAL				//7
// Fix Later				//SQL_DOUBLE			//8
// Fix Later				//SQL_DATETIME			//9
// Fix Later				//SQL_VARCHAR			//12
// Fix Later				//SQL_TYPE_DATE			//91
// Fix Later				//SQL_TYPE_TIME			//92
// Fix Later				//SQL_TYPE_TIMESTAMP	//93
SQLINTEGER	_ObjectLength;
} _ColumnType;


As you can see, I've only tested SQLINTEGER and SQLCHAR, using the max char/varchar length desbribed http://msdn.microsoft.com/en-us/library/ms176089.aspx

1
2
3
4
5
6
7
8
9
10
11
12
 
			case SQL_CHAR :				//1
				{
					SqlReturnCode=SQLGetData(
						SQLHandleStatement,						//SQLHSTMT StatementHandle,
						IndexColumn+1,							//SQLUSMALLINT ColumnNumber, starting at 1
						ColInfo[IndexColumn].ColumnType,		//SQLSMALLINT TargetType,
						&_ColumnType._SqlChar,					//SQLPOINTER TargetValuePtr,
						sizeof(_ColumnType._SqlChar),			//SQLINTEGER BufferLength,
						&_ColumnType._ObjectLength);			//SQLINTEGER * StrLen_or_IndPtr
//					Result is being used for now just to test my first case. In the end, data will be stored in a two dimensional array of void pointers for the other parts of the application to typecast and dereference
					Result += (std::string)((char*)_ColumnType._SqlChar);
				} break;


I just copy the above for each SQL data type, changing TargetValuePtr and BufferLength according to the data type.

Thanks for the help! Now, on to saving the results into void pointers, returning a table of these void pointers, and typecasting / dereferencing by the calling methods.

And, of course, eliminating all of the unnecessary casting pointed out by kbw.
kbw (9488)
That's a step in the right direction. The step further is to think further about the data structure.

You have defined a struct that aggregates all the data types and it can be pretty large, but you only use one field at a time. The data is either an integer or a string or a date and so on, so you only need to be one type at any time, that's where a union comes in.

So using your definitions:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
struct ColumnType
{
    DWORD type;
    DWORD length;

    union ColumnDataType
    {
    // Fix Later            //SQL_UNKNOWN_TYPE      //0
    SQLCHAR SqlChar[8000];  //SQL_CHAR              //1
    // Fix Later            //SQL_NUMERIC           //2
    // Fix Later            //SQL_DECIMAL           //3
    SQLINTEGER SqlInteger;  //SQL_INTEGER           //4
    // Fix Later            //SQL_SMALLINT          //5
    // Fix Later            //SQL_FLOAT             //6
    // Fix Later            //SQL_REAL              //7
    // Fix Later            //SQL_DOUBLE            //8
    // Fix Later            //SQL_DATETIME          //9
    // Fix Later            //SQL_VARCHAR           //12
    // Fix Later            //SQL_TYPE_DATE         //91
    // Fix Later            //SQL_TYPE_TIME         //92
    // Fix Later            //SQL_TYPE_TIMESTAMP    //93
    } data;
};


This is a standard procedural pattern for such matters. COM even has a standard data structure called VARIANT that's similar (more elaborate of course).

There is a more OO method, but you probably need to understand this first.
apfinger (9)
Oh, I like that! Why wasn't this nifty keyword taught in my college courses?! I'm going to give this a shot.

I had posted another thread regarding my table of void pointers here http://www.cplusplus.com/forum/general/36840/

If I can use the union keyword to give me an object that can hold any of the data, I can eliminate the danger of void* and just use a table of ColumnType (probably rename it CellType or something). With this plan, I shouldn't need to worry so much about memory leaks.

Once I get something functionality using this suggestion, I'll take a peek at VARIANT and then maybe poke around with some inheritance to see what will better suit my needs.

Thanks again for the help!
apfinger (9)
So far, using the union to let me store one of several different data types seems to be working. Thanks a ton! I'm not sure I could have Googled a question that would have hinted at using union.

Once I get the files cleaned up a bit and a few more data types and queries tested, I may look into the other ideas(VARIANT and OO). I'm not a fan of allocating space for SqlChar[8000] on each CellType, but it'll do for the moment. Once I start optimizing the code, I'll probably take exiledAussie's idea of having a base CellType virtual class with varying classes for each type inheriting from it.

For now, this eliminates (I think) my memory leak concern in the other thread, too. Two birds with one stone!
exiledAussie (131)
Does the use of unions here lead to massive inefficiencies? I have not used unions, but I assume (perhaps wrongly) that the given code would assign 8000 bytes (the char[8000]) for an int.
jsmith (5804)
Personally I would consider using a boost::variant instead of a union (though it leads to a different design). union is as "efficient" as it gets, the drawback being that you cannot store any objects that have constructors in a union. (C++0x relaxes only a very small part of that restriction).
jsmith (5804)
Personally I would consider using a boost::variant instead of a union (though it leads to a different design). union is as "efficient" as it gets, the drawback being that you cannot store any objects that have constructors in a union. (C++0x relaxes only a very small part of that restriction).
kbw (9488)
I'm not suggesting the final solution be done using a VARIANT. I wanted the OP to understand how to do it in C first. There's no point running of and using some clever Boost mechanism or building a general query mechanism involving a value hierarchy if you don't understand how it works.

He can begin to look at that stuff now, but couldn't at the initial post.
Topic archived. No new replies allowed.