EDN Admin
Well-known member
Hi, I am writing a tiny wrapper around the ODBC API defined at
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710154(v=vs.85).aspx, and I am currently trying it out and debugging it. I am having some problems with a query that is supposed to return results, but somehow doesnt (it does if I run it directly in SQL Server Management Studio).
Anyway, here is the code for my wrapper:// SqlWrite.cpp : Defines the exported functions for the DLL application.
//
#include "stdafx.h"
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <conio.h>
#include <tchar.h>
#include <stdlib.h>
#include <sal.h>
#include "interface.h"
#include "wininet.h"
#include <iostream>
#include <fstream>
#include <regex>
//#include "C:UsersaDocumentsvisual studio 2012Code SnippetsVisual C++My Code SnippetsSqlStrsqlstr.h"
/*******************************************/
/* Macro to call ODBC functions and */
/* report an error on failure. */
/* Takes handle, handle type, and stmt */
/*******************************************/
#define TRYODBC(h, ht, x) { RETCODE rc = x;
if (rc != SQL_SUCCESS)
{
HandleDiagnosticRecord (h, ht, rc);
}
if (rc == SQL_ERROR)
{
fwprintf(stderr, L"Error in " L#x L"n");
goto Exit;
}
}
#define DISPLAY_MAX 50 // Arbitrary limit on column width to display
#define SQL_QUERY_SIZE 1000 // Max. Num characters for SQL Query passed in.
/******************************************/
/* Forward references */
/******************************************/
void HandleDiagnosticRecord (SQLHANDLE hHandle,
SQLSMALLINT hType,
RETCODE RetCode);
void TryODBC(SQLHANDLESTR *h, int ht, SQLRETURN x)
{
RETCODE rc = x;
if (rc != SQL_SUCCESS)
{
HandleDiagnosticRecord (h, ht, rc);
}
if (rc == SQL_ERROR)
{
fwprintf(stderr, L"Error in %in", x);
SqlDisconnectDB(h);
}
}
extern "C" {
__declspec(dllexport) SQLHANDLESTR* __cdecl SqlConnectDB()
{
SQLHANDLESTR *hStr = new SQLHANDLESTR;
hStr->hEnv = NULL;
hStr->hDbc = NULL;
hStr->hStmt = NULL;
/*SQLHANDLESTR hStr;
hStr.hEnv = NULL;
hStr.hDbc = NULL;
hStr.hStmt = NULL;*/
WCHAR* pwszConnStr;
// Allocate an environment
if (SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hStr->hEnv) == SQL_ERROR)
{
fwprintf(stderr, L"Unable to allocate an environment handlen");
exit(-1);
}
// Register this as an application that expects 3.x behavior,
// you must register something if you use AllocHandle
TryODBC(hStr,
SQL_HANDLE_ENV,
SQLSetEnvAttr(hStr->hEnv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
0));
// Allocate a connection
TryODBC(hStr,
SQL_HANDLE_ENV,
SQLAllocHandle(SQL_HANDLE_DBC, hStr->hEnv, &hStr->hDbc));
pwszConnStr = L"";
// Connect to the driver. Use the connection string if supplied
// on the input, otherwise let the driver manager prompt for input.
TryODBC(hStr,
SQL_HANDLE_DBC,
SQLDriverConnect(hStr->hDbc,
GetDesktopWindow(),
pwszConnStr,
SQL_NTS,
NULL,
0,
NULL,
SQL_DRIVER_COMPLETE));
fwprintf(stderr, L"Connected!n");
TryODBC(hStr,
SQL_HANDLE_DBC,
SQLAllocHandle(SQL_HANDLE_STMT, hStr->hDbc, &hStr->hStmt));
return hStr;
}
}
extern "C" {
__declspec(dllexport) RETCODE __cdecl SqlExecS(SQLHANDLESTR* h, WCHAR* cmd)
{
RETCODE RetCode;
//wszInput = L"INSERT INTO soccer.dbo.Teams (TeamID, TeamName) VALUES (NEWID(), abc)";
RetCode = SQLExecDirect(h->hStmt,cmd, SQL_NTS);
return RetCode;
}
}
extern "C" {
__declspec(dllexport) SQLRETURN __cdecl SqlFetchDB(SQLHANDLESTR *h)
{
return SQLFetch(h->hStmt);
}
}
extern "C" {
__declspec(dllexport) SQLRETURN __cdecl SqlGetDataDB(SQLHANDLESTR *h, unsigned int colNum, int targetType, void *targetValuep, long bufferLength, long *strLen_or_IndPtr)
{
return SQLGetData(h->hStmt, colNum, targetType, targetValuep, bufferLength, strLen_or_IndPtr);
}
}
extern "C" {
__declspec(dllexport) void __cdecl SqlDisconnectDB(SQLHANDLESTR *hndl)
{
// Free ODBC handles and exit
if (hndl)
{
SQLFreeHandle(SQL_HANDLE_STMT, hndl);
}
if (hndl)
{
SQLDisconnect(hndl->hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hndl->hDbc);
}
if (hndl->hEnv)
{
SQLFreeHandle(SQL_HANDLE_ENV, hndl->hEnv);
}
wprintf(L"nDisconnected.");
}
}
void HandleDiagnosticRecord (SQLHANDLE hHandle,
SQLSMALLINT hType,
RETCODE RetCode)
{
SQLSMALLINT iRec = 0;
SQLINTEGER iError;
WCHAR wszMessage[1000];
WCHAR wszState[SQL_SQLSTATE_SIZE+1];
if (RetCode == SQL_INVALID_HANDLE)
{
fwprintf(stderr, L"Invalid handle!n");
return;
}
while (SQLGetDiagRec(hType,
hHandle,
++iRec,
wszState,
&iError,
wszMessage,
(SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
(SQLSMALLINT *)NULL) == SQL_SUCCESS)
{
// Hide data truncated..
if (wcsncmp(wszState, L"01004", 5))
{
fwprintf(stderr, L"[%5.5s] %s (%d)n", wszState, wszMessage, iError);
}
}
}
I have a SQLHANDLESTR struct as well, just to make handling of the API easier. It has three members,#include <sql.h>
#include <sqlext.h>
struct sqlstr{
SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
};
typedef sqlstr SQLHANDLESTR;
and, apart from the helper functions, I have five functions that I am using for wrapping:__declspec(dllexport) SQLHANDLESTR* __cdecl SqlConnectDB()
__declspec(dllexport) RETCODE __cdecl SqlExecS(SQLHANDLESTR* h, WCHAR* cmd)
__declspec(dllexport) SQLRETURN __cdecl SqlFetchDB(SQLHANDLESTR *h)
__declspec(dllexport) SQLRETURN __cdecl SqlGetDataDB(SQLHANDLESTR *h, int colNum, int targetType, void *targetValuep, long bufferLength, long *strLen_or_IndPtr)
__declspec(dllexport) void __cdecl SqlDisconnectDB(SQLHANDLESTR *hndl)
I am testing these functions like this:int WinMain(_In_ HINSTANCE hInstance,
_In_ HINSTANCE hPrevInstance,
_In_ LPSTR lpCmdLine,
_In_ int nCmdShow
)
{
SQLHANDLESTR * h = SqlConnectDB();
SQLRETURN retcode;
std::wstring comT = L"SELECT * FROM soccer.dbo.Teams WHERE TeamName = ";
std::wstring clubW = L"abc";
comT.append(clubW);
comT.append(L"");
std::string teamId;
std::string teamN;
SqlExecS(h,const_cast<WCHAR*>(comT.c_str()));
retcode = SqlFetchDB(h);
//if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)
//{
// show_error();
//}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
/* Get data for columns 1, 2, and 3 */
SqlGetDataDB(h, 1, SQL_C_CHAR, &teamId, 100, NULL);
SqlGetDataDB(h, 2, SQL_C_CHAR, &teamN, 100, NULL);
/* Print the row of data */
wprintf(L"id: %s, n: %sn", teamId,teamN);
}
SqlDisconnectDB(h);
return 1;
}
My soccer.dbo.Teams table is:TeamID TeamName
9CEC755C-D1BA-4F27-8EA3-01C3A561F365 abcA
D5C16EA9-A0A2-43E8-B862-3A7D120E60AC abc
472224B4-6D3C-43BB-87C1-58C7F2A6CF43 abc
9F9763A6-D22D-48C3-8BB3-7B7F9D4E4B02 abcA
66B341F6-2DDD-4AD3-BCFB-ACA0D52F5507 abc
So, when I run above querySELECT * FROM soccer.dbo.Teams WHERE TeamName = abc
I get an output of three rows in SQL Server Management Studio.
However, when run the query with the Wrapper, after executing SqlGetDataDB(h, 1, SQL_C_CHAR, &teamId, 100, NULL);
SqlGetDataDB(h, 2, SQL_C_CHAR, &teamN, 100, NULL);
teamId is "<Error reading characters of string.>"
and teamN is "" (empty).
It gets all the way to there, so it is able to connect to the DB and execute the query (SqlFetch), but then, something doesnt work. Dont know what yet
I copied a lot of the code WITHIN the wrappers from http://code.msdn.microsoft.com/ODBC-sample-191624ae#content.
Should I be using SQL_C_CHAR for the first column, if the type in the DB is a "uniqueidentifier"? I thought it would convert it, but I guess maybe it doesnt? Ill try some other types for now, but, if anyone has any insight, I would appreciate any and all help I can get!
Thanks!!
C
View the full article
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710154(v=vs.85).aspx, and I am currently trying it out and debugging it. I am having some problems with a query that is supposed to return results, but somehow doesnt (it does if I run it directly in SQL Server Management Studio).
Anyway, here is the code for my wrapper:// SqlWrite.cpp : Defines the exported functions for the DLL application.
//
#include "stdafx.h"
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <conio.h>
#include <tchar.h>
#include <stdlib.h>
#include <sal.h>
#include "interface.h"
#include "wininet.h"
#include <iostream>
#include <fstream>
#include <regex>
//#include "C:UsersaDocumentsvisual studio 2012Code SnippetsVisual C++My Code SnippetsSqlStrsqlstr.h"
/*******************************************/
/* Macro to call ODBC functions and */
/* report an error on failure. */
/* Takes handle, handle type, and stmt */
/*******************************************/
#define TRYODBC(h, ht, x) { RETCODE rc = x;
if (rc != SQL_SUCCESS)
{
HandleDiagnosticRecord (h, ht, rc);
}
if (rc == SQL_ERROR)
{
fwprintf(stderr, L"Error in " L#x L"n");
goto Exit;
}
}
#define DISPLAY_MAX 50 // Arbitrary limit on column width to display
#define SQL_QUERY_SIZE 1000 // Max. Num characters for SQL Query passed in.
/******************************************/
/* Forward references */
/******************************************/
void HandleDiagnosticRecord (SQLHANDLE hHandle,
SQLSMALLINT hType,
RETCODE RetCode);
void TryODBC(SQLHANDLESTR *h, int ht, SQLRETURN x)
{
RETCODE rc = x;
if (rc != SQL_SUCCESS)
{
HandleDiagnosticRecord (h, ht, rc);
}
if (rc == SQL_ERROR)
{
fwprintf(stderr, L"Error in %in", x);
SqlDisconnectDB(h);
}
}
extern "C" {
__declspec(dllexport) SQLHANDLESTR* __cdecl SqlConnectDB()
{
SQLHANDLESTR *hStr = new SQLHANDLESTR;
hStr->hEnv = NULL;
hStr->hDbc = NULL;
hStr->hStmt = NULL;
/*SQLHANDLESTR hStr;
hStr.hEnv = NULL;
hStr.hDbc = NULL;
hStr.hStmt = NULL;*/
WCHAR* pwszConnStr;
// Allocate an environment
if (SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hStr->hEnv) == SQL_ERROR)
{
fwprintf(stderr, L"Unable to allocate an environment handlen");
exit(-1);
}
// Register this as an application that expects 3.x behavior,
// you must register something if you use AllocHandle
TryODBC(hStr,
SQL_HANDLE_ENV,
SQLSetEnvAttr(hStr->hEnv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
0));
// Allocate a connection
TryODBC(hStr,
SQL_HANDLE_ENV,
SQLAllocHandle(SQL_HANDLE_DBC, hStr->hEnv, &hStr->hDbc));
pwszConnStr = L"";
// Connect to the driver. Use the connection string if supplied
// on the input, otherwise let the driver manager prompt for input.
TryODBC(hStr,
SQL_HANDLE_DBC,
SQLDriverConnect(hStr->hDbc,
GetDesktopWindow(),
pwszConnStr,
SQL_NTS,
NULL,
0,
NULL,
SQL_DRIVER_COMPLETE));
fwprintf(stderr, L"Connected!n");
TryODBC(hStr,
SQL_HANDLE_DBC,
SQLAllocHandle(SQL_HANDLE_STMT, hStr->hDbc, &hStr->hStmt));
return hStr;
}
}
extern "C" {
__declspec(dllexport) RETCODE __cdecl SqlExecS(SQLHANDLESTR* h, WCHAR* cmd)
{
RETCODE RetCode;
//wszInput = L"INSERT INTO soccer.dbo.Teams (TeamID, TeamName) VALUES (NEWID(), abc)";
RetCode = SQLExecDirect(h->hStmt,cmd, SQL_NTS);
return RetCode;
}
}
extern "C" {
__declspec(dllexport) SQLRETURN __cdecl SqlFetchDB(SQLHANDLESTR *h)
{
return SQLFetch(h->hStmt);
}
}
extern "C" {
__declspec(dllexport) SQLRETURN __cdecl SqlGetDataDB(SQLHANDLESTR *h, unsigned int colNum, int targetType, void *targetValuep, long bufferLength, long *strLen_or_IndPtr)
{
return SQLGetData(h->hStmt, colNum, targetType, targetValuep, bufferLength, strLen_or_IndPtr);
}
}
extern "C" {
__declspec(dllexport) void __cdecl SqlDisconnectDB(SQLHANDLESTR *hndl)
{
// Free ODBC handles and exit
if (hndl)
{
SQLFreeHandle(SQL_HANDLE_STMT, hndl);
}
if (hndl)
{
SQLDisconnect(hndl->hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hndl->hDbc);
}
if (hndl->hEnv)
{
SQLFreeHandle(SQL_HANDLE_ENV, hndl->hEnv);
}
wprintf(L"nDisconnected.");
}
}
void HandleDiagnosticRecord (SQLHANDLE hHandle,
SQLSMALLINT hType,
RETCODE RetCode)
{
SQLSMALLINT iRec = 0;
SQLINTEGER iError;
WCHAR wszMessage[1000];
WCHAR wszState[SQL_SQLSTATE_SIZE+1];
if (RetCode == SQL_INVALID_HANDLE)
{
fwprintf(stderr, L"Invalid handle!n");
return;
}
while (SQLGetDiagRec(hType,
hHandle,
++iRec,
wszState,
&iError,
wszMessage,
(SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
(SQLSMALLINT *)NULL) == SQL_SUCCESS)
{
// Hide data truncated..
if (wcsncmp(wszState, L"01004", 5))
{
fwprintf(stderr, L"[%5.5s] %s (%d)n", wszState, wszMessage, iError);
}
}
}
I have a SQLHANDLESTR struct as well, just to make handling of the API easier. It has three members,#include <sql.h>
#include <sqlext.h>
struct sqlstr{
SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
};
typedef sqlstr SQLHANDLESTR;
and, apart from the helper functions, I have five functions that I am using for wrapping:__declspec(dllexport) SQLHANDLESTR* __cdecl SqlConnectDB()
__declspec(dllexport) RETCODE __cdecl SqlExecS(SQLHANDLESTR* h, WCHAR* cmd)
__declspec(dllexport) SQLRETURN __cdecl SqlFetchDB(SQLHANDLESTR *h)
__declspec(dllexport) SQLRETURN __cdecl SqlGetDataDB(SQLHANDLESTR *h, int colNum, int targetType, void *targetValuep, long bufferLength, long *strLen_or_IndPtr)
__declspec(dllexport) void __cdecl SqlDisconnectDB(SQLHANDLESTR *hndl)
I am testing these functions like this:int WinMain(_In_ HINSTANCE hInstance,
_In_ HINSTANCE hPrevInstance,
_In_ LPSTR lpCmdLine,
_In_ int nCmdShow
)
{
SQLHANDLESTR * h = SqlConnectDB();
SQLRETURN retcode;
std::wstring comT = L"SELECT * FROM soccer.dbo.Teams WHERE TeamName = ";
std::wstring clubW = L"abc";
comT.append(clubW);
comT.append(L"");
std::string teamId;
std::string teamN;
SqlExecS(h,const_cast<WCHAR*>(comT.c_str()));
retcode = SqlFetchDB(h);
//if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)
//{
// show_error();
//}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
/* Get data for columns 1, 2, and 3 */
SqlGetDataDB(h, 1, SQL_C_CHAR, &teamId, 100, NULL);
SqlGetDataDB(h, 2, SQL_C_CHAR, &teamN, 100, NULL);
/* Print the row of data */
wprintf(L"id: %s, n: %sn", teamId,teamN);
}
SqlDisconnectDB(h);
return 1;
}
My soccer.dbo.Teams table is:TeamID TeamName
9CEC755C-D1BA-4F27-8EA3-01C3A561F365 abcA
D5C16EA9-A0A2-43E8-B862-3A7D120E60AC abc
472224B4-6D3C-43BB-87C1-58C7F2A6CF43 abc
9F9763A6-D22D-48C3-8BB3-7B7F9D4E4B02 abcA
66B341F6-2DDD-4AD3-BCFB-ACA0D52F5507 abc
So, when I run above querySELECT * FROM soccer.dbo.Teams WHERE TeamName = abc
I get an output of three rows in SQL Server Management Studio.
However, when run the query with the Wrapper, after executing SqlGetDataDB(h, 1, SQL_C_CHAR, &teamId, 100, NULL);
SqlGetDataDB(h, 2, SQL_C_CHAR, &teamN, 100, NULL);
teamId is "<Error reading characters of string.>"
and teamN is "" (empty).
It gets all the way to there, so it is able to connect to the DB and execute the query (SqlFetch), but then, something doesnt work. Dont know what yet
I copied a lot of the code WITHIN the wrappers from http://code.msdn.microsoft.com/ODBC-sample-191624ae#content.
Should I be using SQL_C_CHAR for the first column, if the type in the DB is a "uniqueidentifier"? I thought it would convert it, but I guess maybe it doesnt? Ill try some other types for now, but, if anyone has any insight, I would appreciate any and all help I can get!
Thanks!!
C
View the full article
