C++

Forum

 
Connect SQLServer failed in c++

Ba ranh (33)
I connect to the database on VPS but it goes to the case 'SQL_ERROR', I don't know what am I missing or where is wrong, hope everyone helps.

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
 
#include <iostream>
#include <windows.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>

int Connect_SQL() {
	#define SQL_RESULT_LEN 240
	#define SQL_RETURN_CODE_LEN 1000
	#define MAX_STRING_LEN 255

	//define handles and variables
	SQLHANDLE sqlConnHandle;
	SQLHANDLE sqlStmtHandle;
	SQLHANDLE sqlEnvHandle;
	SQLWCHAR retconstring[SQL_RETURN_CODE_LEN];
	//initializations
	sqlConnHandle = NULL;
	sqlStmtHandle = NULL;
	//allocations
	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle))
		goto COMPLETED;
	if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
		goto COMPLETED;
	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle))
		goto COMPLETED;


	switch (
		SQLDriverConnect(sqlConnHandle,
		NULL,
		(SQLWCHAR*)L"DRIVER={SQL Server};SERVER = 103.151.23.147;DATABASE=test;UID=sa;PASSWORD=44448888;",
		SQL_NTS,
		retconstring,
		1024,
		NULL,
		SQL_DRIVER_NOPROMPT))
	{
	case SQL_SUCCESS:
		MessageBox(NULL, L"Successfully connected to SQL Server", L"", MB_OK);
		break;
	case SQL_SUCCESS_WITH_INFO:
		MessageBox(NULL, L"SQL_SUCCESS_WITH_INFO", L"", MB_OK);
		break;
	case SQL_INVALID_HANDLE:
		MessageBox(NULL, L"SQL_INVALID_HANDLE", L"", MB_OK);
		goto COMPLETED;
	case SQL_ERROR:
		MessageBox(NULL, L"SQL_ERROR", L"", MB_OK);
		goto COMPLETED;
	default:
		break;
	}
	//if there is a problem connecting then exit application
	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlConnHandle, &sqlStmtHandle))
		goto COMPLETED;
	//output
	MessageBox(NULL, L"Executing T-SQL query...", L"", MB_OK);
	//if there is a problem executing the query then exit application
	//else display query result
	if (SQL_SUCCESS != SQLExecDirect(sqlStmtHandle, (SQLWCHAR*)L"SELECT @@VERSION", SQL_NTS)) {
		MessageBox(NULL, L"Error querying SQL Server", L"", MB_OK);
		goto COMPLETED;
	}
	else {
		//declare output variable and pointer
		SQLCHAR sqlVersion[SQL_RESULT_LEN];
		SQLINTEGER ptrSqlVersion;
		while (SQLFetch(sqlStmtHandle) == SQL_SUCCESS) {
			SQLGetData(sqlStmtHandle, 1, SQL_CHAR, sqlVersion, SQL_RESULT_LEN, &ptrSqlVersion);
			//display query result
			MessageBox(NULL, L"Query Result", L"", MB_OK);
		}
	}
	//close connection and free resources
COMPLETED:
	SQLFreeHandle(SQL_HANDLE_STMT, sqlStmtHandle);
	SQLDisconnect(sqlConnHandle);
	SQLFreeHandle(SQL_HANDLE_DBC, sqlConnHandle);
	SQLFreeHandle(SQL_HANDLE_ENV, sqlEnvHandle);

	return 0;
}
Last edited on
salem c (3700)
> I connect to the database on VPS but it goes to the case 'SQL_ERROR',
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqldriverconnect-function?view=sql-server-ver15
Diagnostics

When SQLDriverConnect returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLGetDiagRec with an fHandleType of SQL_HANDLE_DBC and an hHandle of ConnectionHandle. The following table lists the SQLSTATE values commonly returned by SQLDriverConnect and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.


Implement the code to fetch and display all the appropriate diagnostic information.


> (SQLWCHAR*)L"DRIVER={SQL Server};SERVER = 103.151.23.147;DATABASE=test;UID=sa;PASSWORD=44448888;",
Be sure you have the right mix of ASCII/Unicode flags for your build.
Indiscriminate casting just silences the compiler, it doesn't mean you have a string in the right format.


> 1024,
You have a #define, use it.



freddie1 (1838)
I've posted here extensively over the years on ODBC. If you search you'll likely come up with my examples. To do ODBC you really need to learn to make maximum use of...

SQLGetDiagRec(...)

It does usually reveal in pretty awefully cryptic form what its unhappy with. Also, it really, really helps to make an ODBC Class to encapsulate and hide all the connection ugliness. Here's my ODBC class that shows SQLGetDiagRec(). Also, for whatever reason, I've never made a connection to SQLServer that returns SQL_SUCCESS. I don't know why that is. I always get SQL_SUCCESS_WITH_INFO, which is good enough. If you get that you've got a connection.

If you want to use this code in any way you would need to replace my instances of 'String' with the C++ String Class 'string' or wstring. I have my own String Class so you need to change that, for example, instances of String::lpStr() need to be changed to wstring::c_str(), etc.

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
 
//CSql.h
#if !defined(CSQL_H)
#define CSQL_H
#include  <sqltypes.h>
#include  <odbcinst.h>
#include  <sql.h>
#include  <sqlext.h>

class SQL
{
 public:
 SQL();
 ~SQL();
 void MakeConnectionString(void);
 void ODBCConnect(void);
 void ODBCDisconnect(void);

 public:
 String            strConnectionString;
 String            strDatabase;
 String            strDriver;
 String            strServer;
 String            strDBQ;
 TCHAR             szCnOut[512];
 short int         iBytes;
 SWORD             swStrLen;
 SQLHENV           hEnvr;
 SQLHDBC           hConn;
 SQLINTEGER        iNativeErrPtr;
 SQLSMALLINT       iTextLenPtr;
 SQLTCHAR          szErrMsg[512];
 SQLTCHAR          szErrCode[8];
 unsigned int      blnConnected;
};

#endif


Nothing real fancy, but here is the header for my ODBC Class. You could easily make your own. The two databases I routinely dealt with were MS Access and SQL Server.

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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
//CSql.cpp
#ifndef   UNICODE
#define   UNICODE
#endif
#ifndef   _UNICODE
#define   _UNICODE
#endif
//#define   MyDebug
#include  <windows.h>
#include  <tchar.h>
#include  <stdio.h>
#include  <odbcinst.h>
#include  <SqlTypes.h>
#include  <sql.h>
#include  <sqlext.h>
#include  "Strings.h"
#include  "CSql.h"

#if defined(MyDebug)
extern FILE* fp;
#endif


SQL::SQL() //Constructor
{
 ZeroMemory(szCnOut, 512);
 ZeroMemory(szErrMsg,512);
}


SQL::~SQL()
{
 //Sql Destructor
}


void SQL::MakeConnectionString(void)
{
 #ifdef MyDebug
 fprintf(fp,"  Entering SQL::MakeConnectionString()\n");
 #endif
 if(strDriver==(TCHAR*)_T("SQL Server"))
 {
    if(strDBQ==(TCHAR*)_T(""))
    {
       strConnectionString=(TCHAR*)_T("DRIVER=");
       strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("SERVER=")+strServer+(TCHAR*)_T(";");
    }
    else
    {
       strConnectionString=(TCHAR*)_T("DRIVER=");
       strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("SERVER=")+strServer+(TCHAR*)_T(";")+ \
       (TCHAR*)_T("DATABASE=") + strDatabase + (TCHAR*)_T(";") + (TCHAR*)_T("DBQ=") + strDBQ + (TCHAR*)_T(";");
    }
 }
 else if(strDriver==(TCHAR*)_T("Microsoft Access Driver (*.mdb)"))
 {
    strConnectionString=(TCHAR*)_T("DRIVER=");
    strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("DBQ=")+strDBQ+(TCHAR*)_T(";");
 }
 else if(strDriver==(TCHAR*)_T("Microsoft Excel Driver (*.xls)"))
 {
    strConnectionString=(TCHAR*)_T("DRIVER=");
    strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("DBQ=")+strDBQ+(TCHAR*)_T(";");
 }
 else
 {
    #ifdef MyDebug
    fprintf(fp,"  Got In Else!\n");
    fwprintf(fp,L"  this->strConnectionString.lpStr() = %s\n",this->strConnectionString.lpStr());
    #endif
    if(this->strConnectionString.InStr(L"SQL Server",true,true))
    {
       this->strDriver=L"SQL Server";
       return;
    }
    if(this->strConnectionString.InStr(L"Microsoft Access Driver (*.mdb)",true,true))
    {
       this->strDriver=L"Microsoft Access Driver (*.mdb)";
       return;
    }
    if(this->strConnectionString.InStr(L"Microsoft Excel Driver (*.xls)",true,true))
    {
       this->strDriver=L"Microsoft Excel Driver (*.xls)";
       return;
    }
 }
 #ifdef MyDebug
 fprintf(fp,"  Leaving SQL::MakeConnectionString()\n");
 #endif
}


void SQL::ODBCConnect(void)
{
 TCHAR szCnIn[512];
 UINT iResult;

 MakeConnectionString();
 SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
 SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
 _tcscpy(szCnIn,strConnectionString.lpStr());
 iResult=SQLDriverConnect(hConn,NULL,(SQLTCHAR*)szCnIn,(SQLSMALLINT)_tcslen(szCnIn),(SQLTCHAR*)szCnOut,512,&swStrLen,SQL_DRIVER_NOPROMPT);
 if(iResult==0 || iResult==1)
 {
    blnConnected=TRUE;
    this->strConnectionString=szCnOut;
 }
 else
 {
    SQLGetDiagRec(SQL_HANDLE_DBC,hConn,1,szErrCode,&iNativeErrPtr,szErrMsg,512,&iTextLenPtr);
    blnConnected=FALSE;
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn);
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
}


void SQL::ODBCDisconnect(void)
{
 if(blnConnected==TRUE)
 {
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn);
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
    blnConnected=FALSE;
 }
}
Last edited on
freddie1 (1838)
Here's an old program I dug up that I believe shows SQLGetDiagRec() in its simplest form. What I did was I modified the code so as to make an attempt to connect to a Microsoft Access Database that doesn't exist at the path specified in the code, which triggered a call to my error handler which calls SQLGetDiagRec()....

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
 
// cl Test06.cpp odbc32.lib
#include <windows.h>
#include <cstring>
#include <cstdio>
#include <sqlext.h>

int main(void)
{
 SQLSMALLINT swStrLen=0;
 SQLRETURN iReturn=0;
 char szCnOut[512];
 char szCnIn[512];
 int blnReturn=0;
 SQLHENV hEnvr;
 SQLHDBC hConn;

 iReturn=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
 {
    printf("SQLAllocHandle(hEnvr) Succeeded!\n");
    iReturn=SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
    if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
    {
       printf("SQLSetEnvAttr(hEnvr) Succeeded!\n");
       iReturn=SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
       if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
       {
          printf("SQLAllocHandle(hConn) Succeeded!\n");
          memset(szCnIn, 0,512);
          memset(szCnOut,0,512);
          strcpy(szCnIn,"DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\\Tallies\\DBFiles\\TestData.mdb;");
          iReturn=SQLDriverConnect(hConn,NULL,(SQLCHAR*)szCnIn,(SQLSMALLINT)strlen(szCnIn),(SQLCHAR*)szCnOut,512,&swStrLen,SQL_DRIVER_NOPROMPT);
          if(iReturn==SQL_SUCCESS || iReturn==SQL_SUCCESS_WITH_INFO)
          {
             printf("SQLDriverConnect() Succeeded Wonderfully!\n");
             printf("szCnIn  = %s\n",szCnIn);
             printf("szCnOut = %s\n",szCnOut);
             SQLDisconnect(hConn);
          }
          else
          {
             SQLCHAR szSQLState[16];
             SQLCHAR szMessage[512];
             SQLINTEGER iNativeErrorPointer=0; 
             SQLSMALLINT iTextLengthPtr=0;
             int iCtr=1;
             printf("SQLDriverConnect() Failed Miserably!\n");
             printf("szCnIn              = %s\n",szCnIn);
             printf("szCnOut             = %s\n",szCnOut);
             while(SQLGetDiagRec(SQL_HANDLE_DBC,hConn,iCtr,szSQLState,&iNativeErrorPointer,szMessage,512,&iTextLengthPtr)==SQL_SUCCESS)
             { 
                printf("DiagRecNum          = %d\n",iCtr);         
                printf("szSQLState          = %s\n",szSQLState);
                printf("iNativeErrorPointer = %d\n",iNativeErrorPointer);
                printf("szMessage           = %s\n",szMessage);
                printf("iTextLengthPtr      = %d\n",iTextLengthPtr);
                iCtr++;
             };   
          }
          SQLFreeHandle(SQL_HANDLE_DBC,hConn);
       }
       else
       {
          printf("SQLAllocHandle(hConn) Failed!\n");
       }
    }
    else
    {
       printf("SQLSetEnvAttr(hEnvr) Failed!\n");
    }
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
 else
 {
    printf("SQLAllocHandle(hEnvr) Failed!\n");
 }
 getchar();

 return 0;
}


Here is the command line build session and invocation of the exe of the modified code with the bad database path which causes the output from SQLGetDiagRec()...

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
 

/*
C:\Code\VStudio\VC++9\ODBC>cl Test06.cpp odbc32.lib
Microsoft (R) C/C++ Optimizing Compiler Version 15.00.21022.08 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

Test06.cpp
Microsoft (R) Incremental Linker Version 9.00.21022.08
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:Test06.exe
Test06.obj
odbc32.lib

C:\Code\VStudio\VC++9\ODBC>Test06
SQLAllocHandle(hEnvr) Succeeded!
SQLSetEnvAttr(hEnvr) Succeeded!
SQLAllocHandle(hConn) Succeeded!
SQLDriverConnect() Failed Miserably!
szCnIn              = DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\Tallies\DBFiles\TestData.mdb;
szCnOut             =
DiagRecNum          = 1
szSQLState          = IM002
iNativeErrorPointer = 0
szMessage           = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
iTextLengthPtr      = 91

C:\Code\VStudio\VC++9\ODBC> */
Topic archived. No new replies allowed.