C++怎么使用ODBC查找之后怎么显示数据？

Question

 

C++使用ODBC连接至云端数据库，连接正常，可以增加数据，但是查找失败，返回值为-1

 

数据库：

#include <stdio.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <iostream>
#include <string>
#pragma comment(lib,"odbc32.lib")
#pragma warning(disable : 4996)
#pragma warning(disable : 6054)
using namespace std;

SQLRETURN ret;//返回信息
SQLHENV henv;//环境句柄
SQLHDBC hdbc;//连接句柄
SQLHSTMT hstmt;//语句句柄

//错误处理
void error(SQLRETURN err) {
	printf("结果输出：");
	switch (err) {
	case	SQL_SUCCESS:
		puts("****SQL_SUCCESS*****");
		break;
	case	SQL_SUCCESS_WITH_INFO:
		puts("SQL_SUCCESS_WITH_INFO");
		break;
	case	SQL_ERROR:
		puts("SQL_ERROR");
		break;
	case	SQL_INVALID_HANDLE:
		puts("SQL_INVALID_HANDLE");
		break;
	case	SQL_NO_DATA_FOUND:
		puts("SQL_NO_DATA_FOUND");
		break;
	case	SQL_NEED_DATA:
		puts("SQL_NEED_DATA");
		break;
	default:
		puts("err");
	}
}

//添加病人(姓名，性别，年龄，地址，费用)可以正常使用
int AddPaintent(string Patient, string Sex, int Age, string Adress, double Fee) {
	//申请环境句柄
	ret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
	//设置环境句柄
	ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
	//申请数据库连接句柄
	ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	//设置连接
	ret = SQLConnect(hdbc, (SQLCHAR*)"SJXM", SQL_NTS, (SQLCHAR*)"Admin", SQL_NTS, (SQLCHAR*)"YSDXrpa2021", SQL_NTS);
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)//链接成功
	{
		//申请SQL语句
		SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
		//使用指定数据库
		SQLCHAR ConnectDatabaseSql[] = "use Test;";
		//执行语句
		SQLExecDirect(hstmt, (SQLCHAR*)ConnectDatabaseSql, SQL_NTS);
		SQLCHAR SearchResult[1024];//定义SQL语句
		char SQLSearch[1024];
		char pa[20], se[5], ad[20];
		strcpy_s(pa, Patient.c_str());
		strcpy_s(se, Sex.c_str());
		strcpy_s(ad, Adress.c_str());
		//sql语句，分段式表、列名要用_替代空格，整体要用[]括起来
		sprintf_s(SQLSearch, "insert into Patient (Patient, Sex, Age, Adress, [MedcineFee]) values ('%s','%s','%d','%s','%lf');", pa, se, Age, ad, Fee);
		strcpy((char*)SearchResult, SQLSearch);//语句赋值
		//绑定语句准备执行
		SQLPrepare(hstmt, (SQLCHAR*)SearchResult, SQL_NTS);
		//执行语句
		ret = SQLExecute(hstmt);
		if (ret == SQL_SUCCESS_WITH_INFO || ret == SQL_SUCCESS)//执行成功
		{
			cout << "添加成功" << endl;
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);//释放语句
			SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接
			SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境
			return 1;//添加成功
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);//释放语句
			SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接
			SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境
			if (ret == SQL_NULL_DATA) {
				cout << "SQL_NULL_DATA" << endl;
			}
			else {
				cout << ret << endl;
			}
			return 2;//添加失败，错误：已有该成员
		}
	}
	else {
		cout << "连接失败：" << ret << endl;
		return 3;//添加失败，错误：连接错误
	}
	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接句柄
	SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境句柄
}

//查找病人(姓名),不知道怎么显示
void SearchPaintent(string Pt) {
	//申请环境句柄
	ret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
	//设置环境句柄
	ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
	//申请数据库连接句柄
	ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	//设置连接
	ret = SQLConnect(hdbc, (SQLCHAR*)"SJXM", SQL_NTS, (SQLCHAR*)"Admin", SQL_NTS, (SQLCHAR*)"YSDXrpa2021", SQL_NTS);
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)//连接数据库成功
	{
		SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请SQL语句
		SQLCHAR ConnectDatabaseSql[] = "use Test;";//使用指定数据库
		SQLExecDirect(hstmt, (SQLCHAR*)ConnectDatabaseSql, SQL_NTS);//执行语句
		SQLCHAR SearchResult[1024];//定义SQL语句
		char SQLSearch[1024], pa[20];
		strcpy_s(pa, Pt.c_str());
		cout << "sql语句设置完成：select * from Patient where Patient = " << pa << endl;
		sprintf_s(SQLSearch, "select * from Patient where Patient = '%s';", pa);
		//sprintf_s(SQLSearch, "select (Patient,Sex,Age,Adress,MedcineFee) from Patient where Patient = '%s';", pa);这个也不行
        strcpy((char*)SearchResult, SQLSearch);//语句赋值
		SQLPrepare(hstmt, (SQLCHAR*)SearchResult, SQL_NTS);//绑定语句准备执行
		ret = SQLExecute(hstmt);//执行搜索语句
		if (ret == SQL_SUCCESS_WITH_INFO || ret == SQL_SUCCESS)//搜索成功
		{
			SQLCHAR Patient[20], Sex[20], Adress[20];
			SQLPOINTER Age = 0;
			SQLPOINTER Medcine_Fee = 0;
			SQLLEN Patient_Len, Sex_Len, Age_Len, Adress_Len, Medcine_Len;
			int i = 0;
			while (SQLFetch(hstmt) != SQL_NO_DATA)
			{
				SQLGetData(hstmt, 1, SQL_C_CHAR, &Patient, 20, &Patient_Len);
				SQLGetData(hstmt, 2, SQL_C_CHAR, &Sex, 20, &Sex_Len);
				SQLGetData(hstmt, 3, SQL_C_SLONG, &Age, 20, &Age_Len);
				SQLGetData(hstmt, 4, SQL_C_CHAR, &Adress, 20, &Adress_Len);
				SQLGetData(hstmt, 5, SQL_C_DOUBLE, &Medcine_Fee, 20, &Medcine_Len);
				cout << &Patient << "  " << &Sex << "  " << &Age << "  " << &Adress << "  " << &Medcine_Fee << endl;
				i++;
			}
			if (i == 0)
				cout<<"没有查询到相关信息";
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);//释放语句
			SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接
			SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境
			
		}
		else {
			cout << "查询错误" << endl;
			cout << pa << endl;
			cout << ret << endl;
			if (ret == SQL_NULL_DATA) {
				cout << "SQL_NULL_DATA" << endl;
			}
			error(ret);
		}
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);//释放语句
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接
		SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境
		
	}
	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接
	SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境
	
}


int main() {
	string Patient = "123", Sex = "女", Adress = "罗德岛";
	int Age = 18;
	double Fee = 648;
	cout << "开始执行插入函数" << endl;
	AddPaintent(Patient, Sex, Age, Adress, Fee);
	cout << "开始搜索" << endl;
	SearchPaintent(Patient);
	return 0;
}

 希望能够通过这个函数传进来的string在数据库中查找对应的人然后在屏幕上显示这个人的全部信息，不传入姓名的话直接显示表中的全部数据。

Answer 1

程序语句的条件的值用单引号

where Patient=凯希尔

改为

where Patient='凯希尔'