使用C ++连接到MySQL服务器 [英] Connecting to a MySQL server using C++
问题描述
我正在尝试通过Visual C ++ 2008 Express Edition上的MySQL ODBC 5.1驱动程序使用C ++连接到MySQL服务器.
I'm attempting to connect to a MySQL server using C++ with the MySQL ODBC 5.1 Driver on Visual C++ 2008 Express Edition.
我正在遵循MSDN上的这些说明:
I'm following these instructions from MSDN:
- SQLConnect
- SQLGetData
- SQLFetch
唯一的区别是我必须将所有SQLCHAR转换为SQLWCHAR，以匹配函数参数，希望不会影响连接字符串.
The only difference is that I have to convert all the SQLCHAR to SQLWCHAR, to match the function params, hopefully that doesn't affect the connection string.
每次连接时，我都会得到SQL_ERROR作为返回值.
所以我假设连接字符串或连接语句出了问题.
Every time I connect I get SQL_ERROR as the return value.
So I'm assuming there's something wrong with the connection string or the connection statement.
我尝试过
DNS=TestConnection; UID=user; PSW=password
和
SERVER=localhost; DRIVER={MySQL ODBC 5.1 Driver}; PORT=3306; UID=user; PSW=password; DATABASE=dbo;
以及其他类似的连接字符串.
SERVER=localhost; DRIVER={MySQL ODBC 5.1 Driver}; PORT=3306; UID=user; PSW=password; DATABASE=dbo;
and other similar connection strings.
名为TestConnection的DNS与后面的连接字符串具有相同的信息.
The DNS that's called TestConnection has the same info as the latter connection string.
模式为dbo，并具有一个名为testfire的表，具有以下列规范:
The schema is dbo, and have one table called testfire with the following column specs:
TEST_ID( INT(11), PRIMARY, AUTO INCREMENT)
TEST_STRING( VARCHAR(50) )
TEST_INTEGER( INT(11) )
TEST_FLOAT( FLOAT )
TEST_DATE( DATETIME )
3行:
ID STRING INT FLOAT DATE
------------------------------------------------------
| 1 | Test 1 | 1 | 0.1 | 2001-01-01 00:00:00 |
| 2 | Test 2 | 2 | 0.2 | 2002-01-01 00:00:00 |
| 3 | Test 3 | 3 | 0.3 | 2003-01-01 00:00:00 |
------------------------------------------------------
我试图使用Excel连接来检索数据，主要是为了查看驱动程序是否正常工作. Excel成功地检索了数据，没有问题，因此名为TestConnection的DNS有效，凭据也有效.
I've attempted to retrieve the data using an Excel connection, mostly to see if the driver works. Excel successfully retrieved the data without problem, so the DNS named TestConnection is valid, and so are the credentials.
- 我在做什么错?
- 我应该改变什么?
- 是否将转换为
MYSQLWCHAR *弄乱了连接字符串? - 是否存在一种不同的，也许更好和更有效的方法? (也许除了类封装之外，这就是我在测试成功之后要做的事情)
- What am I doing wrong?
- What should I change?
- Is it the conversion to
MYSQLWCHAR *that messes up the connection string? - Is there a different, perhaps better and more efficient approach? (except perhaps class encapsulation, that's what I'm going to do after the test is successful)
哦，编译器没有给出任何错误或警告，代码已编译并运行，没有任何问题.
Oh, and the compiler doesn't give any errors or warnings, the code is compiled and runs without any problems.
因此，这是测试代码，该代码返回查询执行错误":
So, here's the test code, which returns "Query execution error":
#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
using namespace std;
int main(){
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN retcode;
HWND desktopHandle = GetDesktopWindow();
SQLWCHAR OutConnStr[255];
SQLSMALLINT OutConnStrLen;
SQLWCHAR szDNS[2048] ={0};
// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
// Set the ODBC version environment attribute
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
// Allocate connection handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
// Set login timeout to 5 seconds
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
// Connect to data source
retcode = SQLDriverConnect(
hdbc,
desktopHandle,
(SQLWCHAR*)"driver=MySQL Server",
_countof("driver=MySQL Server"),
OutConnStr,
255,
&OutConnStrLen,
SQL_DRIVER_PROMPT );
// Allocate statement handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
// Process data
retcode = SQLExecDirect(hstmt, (SQLWCHAR*)"SELECT TEST_STRING, TEST_INTEGER, TEST_FLOAT FROM dbo.testfire", SQL_NTS);
if (retcode == SQL_SUCCESS) {
SQLINTEGER sTestInt, cbTestStr, cbTestInt, cbTestFloat;
SQLFLOAT dTestFloat;
SQLCHAR szTestStr[200];
while (TRUE) {
cout<<"Inside loop";
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
cout<<"An error occurred";
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
SQLGetData(hstmt, 1, SQL_C_CHAR, szTestStr, 200, &cbTestStr);
SQLGetData(hstmt, 2, SQL_C_ULONG, &sTestInt, 0, &cbTestInt);
SQLGetData(hstmt, 3, SQL_C_FLOAT, &dTestFloat, 0,&cbTestFloat);
/* Print the row of data */
cout<<szTestStr<<endl;
cout<<sTestInt<<endl;
cout<<dTestFloat<<endl;
} else {
break;
}
}
}else{
cout<<"Query execution error."<<endl;
SQLWCHAR SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER NativeError;
SQLSMALLINT i, MsgLen;
SQLRETURN rc2;
// Get the status records.
i = 1;
while ((rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, SqlState, &NativeError,
Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
cout<<SqlState<<endl;
cout<<NativeError<<endl;
cout<<Msg<<endl;
cout<<MsgLen<<endl;
i++;
}
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
SQLDisconnect(hdbc);
}else{
cout<<"Connection error."<<endl;
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
system("pause");
return 0;
}
更新
使用Mat提供的文档中的SQLDriverConnect正确参数对代码进行更新(并发布)后，连接将起作用.如何在不提示输入DNS名称的情况下做同样的事情?将窗口句柄设置为null并...?
After updating the code (and post) using the correct arguments for the SQLDriverConnect from the documentation provided by Mat (see comments below), the connection works. How can I do the same thing without having to prompt for the DNS name? Put window handle as null and...?
现在，它在SQLExecDirect(hstmt, (SQLWCHAR*)"SELECT TEST_STRING, TEST_INTEGER, TEST_FLOAT FROM dbo.testfire", SQL_NTS)处失败，但是查询是正确的，那是什么问题?
Now it fails at the SQLExecDirect(hstmt, (SQLWCHAR*)"SELECT TEST_STRING, TEST_INTEGER, TEST_FLOAT FROM dbo.testfire", SQL_NTS), but the query is correct, so, what's the problem?
返回的确切错误消息是:
The exact error message returned is:
Sql State: 42000
Native Error: 1064
Message:
Message Length: 211
42000: Syntax error or access violation
*StatementText contained an SQL statement that was not preparable or contained a syntax error.
The user did not have permission to execute the SQL statement contained in *StatementText.
那么...是什么意思? 我怎么没有权限? 那怎么会产生语法错误，这显然是有效的查询?
So... what does that mean? How can I not have permission? How can that generate a syntax error, it's clearly a valid query?
推荐答案
在Mat的一点帮助下，我能够找出问题所在，但是由于他没有给出答案，我必须回答它，以便可以将这些问题共享给有相同问题的人员，也可以将其标记为已回答.
With a little help from Mat, I was able to figure out what the problem was, but since he didn't give it in a form of an answer, I'll have to answer it so it can be shared for those who have the same problem, and also to mark as answered.
所以，我的问题是我无法连接到数据库.正如Mat所建议的，我应该使用称为SQLGetDiagRec的扩展错误信息，并根据文档修复参数.花了一点时间来学习SQLGetDiagRec函数的工作原理，但是一旦我设法将wchar_t转换为char *，我就能够看到它所产生的错误.
So, my problem was that I couldn't connect to the database. As Mat suggested, I should use the extended error info, known as SQLGetDiagRec and also fix the arguments according to the documentation. Took me a moment to learn how the SQLGetDiagRec function works, but once I managed to convert the wchar_t to char * I was able to see the error it was generating.
连接尝试给了我错误Data source not found and no default driver specified.这给了我一个提示，表明我要么写了错误的连接字符串，要么以某种方式误解了文本字符串.
The connection attempt gave me the error Data source not found and no default driver specified. That gave me a clue, indicating I either wrote the incorrect connection string or that the text string was somehow misinterpreted or mangled.
做一些在网上搜索给了我一个深刻的印象，即字符串被误解了，要修复它，我必须将其设置为文字字符串.果然，在字符串前面加一个L即可解决问题！
Doing some searching on the net gave me the insight that the string was misinterpreted, and to fix it I had to make it a literal string. Surely enough, putting an L in front of the string solved it!
retcode = SQLDriverConnect(hdbc, 0,
(SQLWCHAR*)L"DSN=TestConnection;SERVER=localhost;UID=user;PWD=password;DRIVER=MySQL Server;",
_countof(L"DSN=TestConnection;SERVER=localhost;UID=user;PWD=password;DRIVER=MySQL Server;"),
OutConnStr, 255, &OutConnStrLen, SQL_DRIVER_COMPLETE);
与此同时，我学习了如何摆脱提示，在纠正最初的问题后，很容易找出提示.为窗口句柄指定null，将驱动程序完成设置为SQL_DRIVER_COMPLETE，并确保在连接字符串中添加了所有需要的信息.
At the same time, I learned how to get rid of the prompt, which was quite easy to figure out after correcting the initial problem. Specify null for the window handle, set driver completion to SQL_DRIVER_COMPLETE and make sure you add all the information needed in the connection string.
因此，我对SQLExecDirect的查询遇到的下一个问题是给出错误消息Syntax error or access violation.问题显然与连接字符串相同.果然
So, the next problem I had with the query with SQLExecDirect was giving an error saying Syntax error or access violation. The problem was obviously the same as with the connection string. Surely enough
retcode = SQLExecDirect(hstmt, (SQLWCHAR*)L"SELECT TEST_STRING, TEST_INTEGER, TEST_FLOAT FROM dbo.testfire", SQL_NTS);
像护身符一样工作.
这是完整的，功能齐全的代码:
Here's the code in its entirety, fully functional:
#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
#include <string>
using namespace std;
int main(){
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN retcode;
SQLWCHAR OutConnStr[255];
SQLSMALLINT OutConnStrLen;
// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
// Set the ODBC version environment attribute
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
// Allocate connection handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
// Set login timeout to 5 seconds
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
// Connect to data source
retcode = SQLDriverConnect(
hdbc,
0,
(SQLWCHAR*)L"DSN=TestConnection;SERVER=localhost;UID=root;PWD=never140;DRIVER=MySQL Server;",
_countof(L"DSN=TestConnection;SERVER=localhost;UID=root;PWD=never140;DRIVER=MySQL Server;"),
OutConnStr,
255,
&OutConnStrLen,
SQL_DRIVER_COMPLETE );
// Allocate statement handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
// Process data
retcode = SQLExecDirect(hstmt, (SQLWCHAR*)L"SELECT TEST_STRING, TEST_INTEGER, TEST_FLOAT FROM dbo.testfire", SQL_NTS);
if (retcode == SQL_SUCCESS) {
SQLINTEGER sTestInt, cbTestStr, cbTestInt, cbTestFloat, iCount = 1;
SQLFLOAT dTestFloat;
SQLCHAR szTestStr[200];
while (TRUE) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
cout<<"An error occurred";
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
SQLGetData(hstmt, 1, SQL_C_CHAR, szTestStr, 200, &cbTestStr);
SQLGetData(hstmt, 2, SQL_C_ULONG, &sTestInt, 0, &cbTestInt);
SQLGetData(hstmt, 3, SQL_C_DOUBLE, &dTestFloat, 0,&cbTestFloat);
/* Print the row of data */
cout<<"Row "<<iCount<<":"<<endl;
cout<<szTestStr<<endl;
cout<<sTestInt<<endl;
cout<<dTestFloat<<endl;
iCount++;
} else {
break;
}
}
}else{
cout<<"Query execution error."<<endl;
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
}else{
cout<<"Connection error"<<endl;
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
system("pause");
return 0;
}
只是展示一下，即使是最微小的事情也可以使一切失败.
Just goes to show, even the tiniest thing can make everything fail.
感谢Mat的帮助.
这篇关于使用C ++连接到MySQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
