C ++数据库使用SQL ODBC从表中获取行数组
Refering to this link C++\SQL ODBC: Get row from table I don't understand the docs and have tried many variations to get something to work but it just outputs complete jibberish. Basically, how can I get data from my Microsoft Access Database into my C++ program. I would eventually want to get the entire table stored as arrays or vectors, that being, lets say there are 5 fields in my table, I want to store (in 5 different arrays or vectors) all the contents to the corresponding fields. But for now, how can I just have something like a table called "Example Table" and within it it has 1 field called "Names" and the type is a string or "short text" as Access calls it. How can I then load that into an array of strings or char*'s to use for my GUI or other parts of the program? Here is my current code:
#include <stdio.h>
#include <tchar.h>
#include <Windows.h>
#include <sqlext.h>
#include <iostream>
WCHAR szDSN[] = L"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\Users\\Name\\Desktop\\databaseExample\\databaseExample\\Data.accdb";
int _tmain(int argc, _TCHAR* argv[])
{
HENV hEnv;
HDBC hDbc;
/* ODBC API return status */
RETCODE rc;
int iConnStrLength2Ptr;
WCHAR szConnStrOut[256];
const WCHAR* query = L"select Names from Example_Table";
HSTMT hStmt;
/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);
/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);
/* Connect to the database */
rc = SQLDriverConnect(hDbc, NULL, (WCHAR*)szDSN,
SQL_NTS, (WCHAR*)szConnStrOut,
255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc))
{
wprintf(L"Successfully connected to database. Data source name: \n %s\n",
szConnStrOut);
/* Prepare SQL query */
wprintf(L"SQL query:\n %s\n", query);
rc = SQLAllocStmt(hDbc, &hStmt);
rc = SQLPrepare(hStmt, (SQLWCHAR*)query, SQL_NTS);
/* Excecute the query */
rc = SQLExecute(hStmt);
if (SQL_SUCCEEDED(rc))
{
wprintf(L"SQL Success\n");
}
else {
wprintf(L"SQL Failed\n");
}
}
else
{
wprintf(L"Couldn't connect to %s.\n", szDSN);
}
/* Disconnect and free up allocated handles */
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
getchar();
printf("hi\n");
return 0;
}
您需要使用 SQLGetData ，例如，如果您的列是文本，则传递SQL_C_WCHAR类型.以下是如何获取单个文本列的值并将其放入向量的示例:
You need to use SQLGetData, passing SQL_C_WCHAR type, for example, if your column is text. Here's example how to get values of the single text column and put them to vector:
#include <iostream>
#include <string>
#include <vector>
#include <Windows.h>
#include <sqlext.h>
WCHAR szDSN[] = L"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\Test\\users.accdb";
void DisplayError(SQLSMALLINT t, SQLHSTMT h) {
SQLWCHAR SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER NativeError;
SQLSMALLINT i, MsgLen;
SQLRETURN rc;
SQLLEN numRecs = 0;
SQLGetDiagField(t, h, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);
// Get the status records.
i = 1;
while (i <= numRecs && (rc = SQLGetDiagRec(t, h, i, SqlState, &NativeError,
Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
wprintf(L"Error %d: %s\n", NativeError, Msg);
i++;
}
}
std::vector<std::wstring> ExecuteSql(const WCHAR* sql) {
std::vector<std::wstring> results = {};
HENV hEnv = NULL;
HDBC hDbc = NULL;
HSTMT hStmt = NULL;
int iConnStrLength2Ptr;
WCHAR szConnStrOut[256];
SQLINTEGER rowCount = 0;
SQLSMALLINT fieldCount = 0;
SQLWCHAR buf[128];
SQLINTEGER ret;
/* ODBC API return status */
RETCODE rc;
/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);
/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);
/* Connect to the database */
rc = SQLDriverConnect(hDbc, NULL, (WCHAR*)szDSN,
SQL_NTS, (WCHAR*)szConnStrOut,
255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc))
{
/* Prepare SQL query */
rc = SQLAllocStmt(hDbc, &hStmt);
rc = SQLPrepare(hStmt, (SQLWCHAR*)sql, SQL_NTS);
/* Excecute the query */
rc = SQLExecute(hStmt);
if (SQL_SUCCEEDED(rc))
{
SQLNumResultCols(hStmt, &fieldCount);
if (fieldCount > 0)
{
/* Loop through the rows in the result set */
rc = SQLFetch(hStmt);
while (SQL_SUCCEEDED(rc))
{
//get data
rc = SQLGetData(hStmt, 1, SQL_C_WCHAR, buf, sizeof(buf), &ret);
if (SQL_SUCCEEDED(rc) == FALSE) {
wprintf(L"SQLGetData failed\n");
continue;
}
//convert data to string
std::wstring str;
if (ret <= 0) {
str = std::wstring(L"(null");
}
else {
str = std::wstring(buf);
}
results.push_back(str);
rc = SQLFetch(hStmt);
rowCount++;
};
rc = SQLFreeStmt(hStmt, SQL_DROP);
}
else
{
wprintf(L"Error: Number of fields in the result set is 0.\n");
}
}
else {
wprintf(L"SQL Failed\n");
DisplayError(SQL_HANDLE_STMT, hStmt);
}
}
else
{
wprintf(L"Couldn't connect to %s\n", szDSN);
DisplayError(SQL_HANDLE_DBC, hDbc);
}
/* Disconnect and free up allocated handles */
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
return results;
}
int main()
{
auto results = ExecuteSql(L"SELECT name FROM Users");
for (auto x : results) {
std::wcout << x << std::endl;
}
getchar();
return 0;
}