代码如下:
// QueryExecuteSQL.cpp : 定义控制台应用程序的入口点。
// Description : 使用odbc api对sql server进行查询,操作
// author : puppy peng
// date : May 4 -- May 5 ,2004
// compile : vs.net 2003 sql 2000
// verison : 1.2
//
// modification history : version 1.0: connect sucess May 5
// version 1. 1: connect query sucess May 6
// version 1.2 : beatiful, hehe:) May 6//******** All right solve to Zuiyue Soft Stdio 2004 *******#include "stdafx.h"
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>
#include <stdlib.h>
// global
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
RETCODE retcode;
// print use
long lProductID ;
SQLINTEGER pIndicators[2];void Init(); // Init
void ODBCConnect(char* szDSN, char* szUID,
char* szAuthStr); // connect to database
void ExecuteSQL(SQLCHAR* szStatement); // :) Execute SQL
void DisconnectFree(); // The end;int _tmain(int argc, _TCHAR* argv[])
{
printf("This is a test program for use ODBC AIP execute SQL!\n");
char szDSN[SQL_MAX_DSN_LENGTH+1] = "tempData", // My DSN
szUID[MAXNAME] = "", // My ID
szAuthStr[MAXNAME] = ""; // My password
// please modify the command here
SQLCHAR szStatement[] = "USE master SELECT ProductID FROM Products";
Init();
ODBCConnect(szDSN, szUID, szAuthStr);
ExecuteSQL(szStatement);
DisconnectFree();
return 0;
}
// Init(henv),好粗心呀!这里参数henv当然会导致循环
// 解决了就好!:) 17:23 May 6, 2004
void Init()
{
// Allocate the ODBC enviroement and save handle
// 分配环境句柄
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL,&henv);
if (retcode != SQL_SUCCESS)
printf("Cannot allocate env handle\n");
// Notify ODBC that tis is an ODBC 3.0 application
// 设置版本信息
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
if (retcode != SQL_SUCCESS)
printf("Cannot set ODBC version\n");}void ODBCConnect(char* szDSN, char* szUID,
char* szAuthStr)
{
// Allocate an ODBC connection handle and connect
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
retcode = SQLSetConnectAttr(hdbc,SQL_ATTR_LOGIN_TIMEOUT,(void*)5, 0);
if (retcode != SQL_SUCCESS)
printf("Cannot allocate connect handle\n");
// 连接到数据源
retcode = SQLConnect(hdbc,(UCHAR*)szDSN, SQL_NTS,
(UCHAR*)szUID, SQL_NTS,
(UCHAR*)szAuthStr, SQL_NTS);
if ( (retcode != SQL_SUCCESS) &&
(retcode!= SQL_SUCCESS_WITH_INFO))
{
// Connects failecd ,call SQLGetDiagRec errors.
printf("Error in connect to datasource\n");
// 处理诊断消息
SQLCHAR Sqlstate[6];
SQLINTEGER NativeError;
SQLCHAR ErrMsg[SQL_MAX_MESSAGE_LENGTH];
int nCountErr= 1; while(SQLGetDiagRec(SQL_HANDLE_DBC, hdbc,nCountErr,Sqlstate,
&NativeError, ErrMsg, sizeof(ErrMsg),NULL) != SQL_NO_DATA)
printf("Diag:%d, SQLSTATE:%s NativeError: %d ErrMsg:%s\n",
nCountErr++,Sqlstate, NativeError, ErrMsg);
}
else
{
// connects to SQL Server always return informational
// messages .
printf("Congratulation,connect successs,Please goto database to test \n");
}}
void ExecuteSQL(SQLCHAR* szStatement)
{
retcode = SQLAllocHandle(SQL_HANDLE_STMT ,hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,szStatement,SQL_NTS);
if ( (retcode != SQL_SUCCESS) &&
(retcode!= SQL_SUCCESS_WITH_INFO))
{
printf("执行SQL语句失败\n");
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
hstmt = SQL_NULL_HSTMT;
}
else
{
printf("*************************\n");
printf("********Congratulation***\n");
printf(" :) command run sucess !\n"); // Retrieve data from row set.
// retcode = SQLAllocHandle(SQL_HANDLE_STMT ,hdbc,&hstmt);
SQLBindCol(hstmt, 1, SQL_C_LONG, (SQLPOINTER)&lProductID,
sizeof(long),&pIndicators[0]);
// ********************************************
// 各位帮忙,这儿还是没有把lProductID打印,为什么?
// 经理没有追究,但是我终究要面对!
// May 6,2004
// *********************************************
while(SQLFetch(hstmt) == SQL_SUCCESS)
{
printf("ProductID: %d\n", lProductID);
}
}}void DisconnectFree()
{
// 断开,释放 disconnect and free
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);}
// QueryExecuteSQL.cpp : 定义控制台应用程序的入口点。
// Description : 使用odbc api对sql server进行查询,操作
// author : puppy peng
// date : May 4 -- May 5 ,2004
// compile : vs.net 2003 sql 2000
// verison : 1.2
//
// modification history : version 1.0: connect sucess May 5
// version 1. 1: connect query sucess May 6
// version 1.2 : beatiful, hehe:) May 6//******** All right solve to Zuiyue Soft Stdio 2004 *******#include "stdafx.h"
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>
#include <stdlib.h>
// global
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
RETCODE retcode;
// print use
long lProductID ;
SQLINTEGER pIndicators[2];void Init(); // Init
void ODBCConnect(char* szDSN, char* szUID,
char* szAuthStr); // connect to database
void ExecuteSQL(SQLCHAR* szStatement); // :) Execute SQL
void DisconnectFree(); // The end;int _tmain(int argc, _TCHAR* argv[])
{
printf("This is a test program for use ODBC AIP execute SQL!\n");
char szDSN[SQL_MAX_DSN_LENGTH+1] = "tempData", // My DSN
szUID[MAXNAME] = "", // My ID
szAuthStr[MAXNAME] = ""; // My password
// please modify the command here
SQLCHAR szStatement[] = "USE master SELECT ProductID FROM Products";
Init();
ODBCConnect(szDSN, szUID, szAuthStr);
ExecuteSQL(szStatement);
DisconnectFree();
return 0;
}
// Init(henv),好粗心呀!这里参数henv当然会导致循环
// 解决了就好!:) 17:23 May 6, 2004
void Init()
{
// Allocate the ODBC enviroement and save handle
// 分配环境句柄
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL,&henv);
if (retcode != SQL_SUCCESS)
printf("Cannot allocate env handle\n");
// Notify ODBC that tis is an ODBC 3.0 application
// 设置版本信息
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
if (retcode != SQL_SUCCESS)
printf("Cannot set ODBC version\n");}void ODBCConnect(char* szDSN, char* szUID,
char* szAuthStr)
{
// Allocate an ODBC connection handle and connect
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
retcode = SQLSetConnectAttr(hdbc,SQL_ATTR_LOGIN_TIMEOUT,(void*)5, 0);
if (retcode != SQL_SUCCESS)
printf("Cannot allocate connect handle\n");
// 连接到数据源
retcode = SQLConnect(hdbc,(UCHAR*)szDSN, SQL_NTS,
(UCHAR*)szUID, SQL_NTS,
(UCHAR*)szAuthStr, SQL_NTS);
if ( (retcode != SQL_SUCCESS) &&
(retcode!= SQL_SUCCESS_WITH_INFO))
{
// Connects failecd ,call SQLGetDiagRec errors.
printf("Error in connect to datasource\n");
// 处理诊断消息
SQLCHAR Sqlstate[6];
SQLINTEGER NativeError;
SQLCHAR ErrMsg[SQL_MAX_MESSAGE_LENGTH];
int nCountErr= 1; while(SQLGetDiagRec(SQL_HANDLE_DBC, hdbc,nCountErr,Sqlstate,
&NativeError, ErrMsg, sizeof(ErrMsg),NULL) != SQL_NO_DATA)
printf("Diag:%d, SQLSTATE:%s NativeError: %d ErrMsg:%s\n",
nCountErr++,Sqlstate, NativeError, ErrMsg);
}
else
{
// connects to SQL Server always return informational
// messages .
printf("Congratulation,connect successs,Please goto database to test \n");
}}
void ExecuteSQL(SQLCHAR* szStatement)
{
retcode = SQLAllocHandle(SQL_HANDLE_STMT ,hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,szStatement,SQL_NTS);
if ( (retcode != SQL_SUCCESS) &&
(retcode!= SQL_SUCCESS_WITH_INFO))
{
printf("执行SQL语句失败\n");
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
hstmt = SQL_NULL_HSTMT;
}
else
{
printf("*************************\n");
printf("********Congratulation***\n");
printf(" :) command run sucess !\n"); // Retrieve data from row set.
// retcode = SQLAllocHandle(SQL_HANDLE_STMT ,hdbc,&hstmt);
SQLBindCol(hstmt, 1, SQL_C_LONG, (SQLPOINTER)&lProductID,
sizeof(long),&pIndicators[0]);
// ********************************************
// 各位帮忙,这儿还是没有把lProductID打印,为什么?
// 经理没有追究,但是我终究要面对!
// May 6,2004
// *********************************************
while(SQLFetch(hstmt) == SQL_SUCCESS)
{
printf("ProductID: %d\n", lProductID);
}
}}void DisconnectFree()
{
// 断开,释放 disconnect and free
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);}
用的数据库不正确吧。
给你一个参考的例子,正确运行在SQLSerevr 2000下。
#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>
#define ID_LEN 7
#define NAME_LEN 50
#define PHONE_LEN 50
using namespace std;void main()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN retcode;
SQLCHAR szCustID[ID_LEN], szName[NAME_LEN], szPhone[PHONE_LEN];
SQLINTEGER sCustID, cbName, cbCustID,cbPhone;
/*Allocate environment handle */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set the ODBC version environment attribute */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Allocate connection handle */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set login timeout to 5 seconds. */
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void *)5, 0);
/* Connect to data source */
retcode = SQLConnect(hdbc, (SQLCHAR*) "mydsn", SQL_NTS,
(SQLCHAR*) "sa", SQL_NTS,
(SQLCHAR*) "zyl1998", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
/* Allocate statement handle */
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Process data */ retcode = SQLExecDirect(hstmt,
(SQLCHAR *) "SELECT OrderID, CustomerID, ShipName FROM Orders ORDER BY 2, 1, 3",
SQL_NTS);
if (retcode == SQL_SUCCESS) {
while (TRUE) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
//show_error();
cout << "Wrong!" << endl;
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
/* Get data for columns 1, 2, and 3 */
sCustID = 0;
//SQLGetData(hstmt, 1, SQL_C_CHAR, szCustID, ID_LEN, &cbCustID);
SQLGetData(hstmt, 1, SQL_C_ULONG, &sCustID, 0, &cbCustID);
SQLGetData(hstmt, 2, SQL_C_CHAR, szName, NAME_LEN, &cbName);
SQLGetData(hstmt, 3, SQL_C_CHAR, szPhone, PHONE_LEN, &cbPhone);
/* Print the row of data */
printf("%-5d %-*s %*s\n", sCustID, NAME_LEN-1, szName,
PHONE_LEN-1, szPhone);
} else {
break;
}
}
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
SQLDisconnect(hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}