PS:今天上午，非常郁闷，有很多简单基础的问题搞得我有些迷茫，哎，代码几天不写就忘。目前又不当COO，还是得用心记代码哦！
先建一个表
============
go
/*==============================================================*/
/* Table: Student */
/*==============================================================*/
create table Student (
stuid int not null,
name varchar(20) not null,
sex char(2) not null,
class varchar(8) not null
)
go
===========
头文件
=============
#ifndef _ODBC_SQL_H_
#define _ODBC_SQL_H_
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#include <vector>
#include <iostream>
using namespace std;
#define MAXBUFLEN 255
#define MaxNameLen 20
#define M_OK 0
struct student
{
int istuid;
char szname[20];
char szsex[2];
char szclass[8];
};
class OdbcDBLinker
{
public :
~OdbcDBLinker();
static OdbcDBLinker* Instance();
int ConnectDB(char* szDSN,char* szUID,char* szAuthStr);
int CloseLink();
int SelectTable(SQLCHAR *szSqlStmt,student RetMSG);
int insertdata(student& stu);
protected:
int init();
private:
OdbcDBLinker();
SQLHENV henv ;//定义环境句柄
SQLHDBC hdbc ;//定义数据库连接句柄
SQLHSTMT hstmt ;//定义语句句柄
// //Data Source Name must be of type User DNS or System DNS
// char* szDSN ;//DSN
// char* szUID ;//log name
// char* szAuthStr ;//passward
static int hstmtcount;
static OdbcDBLinker* m_odbcdbliker;
};
#endif
==============
CPP
================
#include "OdbcSql.h"
OdbcDBLinker* OdbcDBLinker::m_odbcdbliker = NULL;
int OdbcDBLinker::hstmtcount = 0;
OdbcDBLinker::OdbcDBLinker()
{
}
OdbcDBLinker::~OdbcDBLinker()
{
if(m_odbcdbliker!=NULL)
{
delete m_odbcdbliker;
}
}
OdbcDBLinker* OdbcDBLinker::Instance()
{
if(m_odbcdbliker==NULL)
{
m_odbcdbliker= new OdbcDBLinker();
}
return m_odbcdbliker;
}
int OdbcDBLinker::init()
{
int iRetcode = M_OK;
henv = SQL_NULL_HENV;//定义环境句柄
hdbc = SQL_NULL_HDBC;//定义数据库连接句柄
hstmt = SQL_NULL_HSTMT;//定义语句句柄
SQLRETURN retcode = 0;
int iwaittime = 5;
SQLPOINTER rgbValue;
rgbValue = &iwaittime;
iRetcode = SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//连接环境句柄
if(iRetcode<0)
{
printf("Error\n");
}
iRetcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if(iRetcode<0)
{
printf("Error\n");
}
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); //设置连接句柄
if(iRetcode<0)
{
printf("Error\n");
}
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)(rgbValue), 0);
return iRetcode;
}
int OdbcDBLinker::ConnectDB(char* szDSN,char* szUID,char* szAuthStr)
{
int iRetcode = M_OK;
iRetcode= init();
if (iRetcode == SQL_SUCCESS || iRetcode ==SQL_SUCCESS_WITH_INFO)
{
printf("数据源连接成功，可操作数据库\n");
iRetcode = SQLConnect(hdbc,
(SQLCHAR*)szDSN,
(SWORD)strlen(szDSN),
(SQLCHAR*) szUID,
(SWORD)strlen(szUID),
(SQLCHAR*) szAuthStr,
(SWORD)strlen(szAuthStr));
}
else
{
printf("数据源连接失败\n");
}
if(iRetcode<0)
{
printf("数据库登岸失败，请确认用户名和密码是不是正确\n");
}
// iRetcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); //设置语句句柄
// if(iRetcode<0)
// {
// printf("Error\n");
// }
return iRetcode;
}
int OdbcDBLinker::CloseLink()
{
int iRetcode = M_OK;
//SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return iRetcode;
}
int OdbcDBLinker::SelectTable(SQLCHAR * szSqlStmt,student RetMSG)
{
//这里查询当前只要hdbc不释放，查询的结果是一批一批的。
//这个函数调用2次则执行第一次查询的结果是不会再被查询出的，就是
int iRetcode = M_OK;
int iBindIdx = 1;
SQLINTEGER columnLen = 0;//数据库定义中该属性列的长度
int inorg = 0;//绑定的参数
SQLINTEGER cinorg = 0; //这个是必须的，为什么不知道
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT, 0, 0, &siIntOrg, 0, &cbIntOrg);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, &llCustCode, 0, &cbCustCode);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(szTransCode) - 1, 0, szTransCode, sizeof(szTransCode), &cbTransCode);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(szCurrency) - 1, 0, szCurrency, sizeof(szCurrency), &cbCurrency);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(szStkex) - 1, 0, szStkex, sizeof(szStkex), &cbStkex);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(szStkbd) - 1, 0, szStkbd, sizeof(szStkbd), &cbStkbd);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(szProductId) - 1, 0, szProductId, sizeof(szProductId), &cbProductId);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(szFtsInstrId) - 1, 0, szFtsInstrId, sizeof(szFtsInstrId), &cbFtsInstrId);
// siSqlRetCode |= SQLBindParameter(sqlhStmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(szDirection) - 1, 0, szDirection, sizeof(szDirection), &cbDirection);
if(hstmtcount == 0)
{
iRetcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); //设置语句句柄
if(iRetcode<0)
{
printf("Error\n");
}
hstmtcount++;
}
iRetcode = SQLPrepare(hstmt, szSqlStmt, SQL_NTS);
//iRetcode = SQLBindParameter(hstmt, iBindIdx++, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &inorg, 0, &cinorg);
//SQLCHAR* szSqlStmt=(SQLCHAR*)szSqlStmt1;
//printf("%d\n",hstmt);
// if(iRetcode<0)
// {
// printf("Error\n");
// }
// 执行语句
inorg = 0;
//iRetcode = SQLExecute(szSqlStmt);
iRetcode = SQLExecute(hstmt);
//iRetcode = SQLExecDirect(hstmt,(SQLCHAR*)"SELECT * FROM SYS_DD_ITEM WHERE INT_ORG = 8888", SQL_NTS);
int istudentid;
char szname[20] = {0};
char szsex[20]={0};
char szclass[20]={0};
iBindIdx = 1;
// iRetcode |= SQLBindCol(sqlhStmt, iBindIdx++, SQL_C_SLONG, (SQLPOINTER)&iSettDate, 0, &cbSettDate);
// iRetcode = SQLFetch(sqlhStmt);
iRetcode = SQLBindCol(hstmt, iBindIdx++, SQL_C_SLONG,(SQLPOINTER)&istudentid, sizeof(istudentid), &columnLen);
iRetcode = SQLBindCol(hstmt, iBindIdx++, SQL_C_CHAR,(SQLPOINTER)szname, sizeof(szname), &columnLen);
iRetcode = SQLBindCol(hstmt, iBindIdx++, SQL_C_CHAR,(SQLPOINTER)szsex, sizeof(szsex), &columnLen);
iRetcode = SQLBindCol(hstmt, iBindIdx++, SQL_C_CHAR,(SQLPOINTER)szclass, sizeof(szclass), &columnLen);
int i = 0;
while ( (iRetcode = SQLFetch(hstmt) ) != SQL_NO_DATA)
{
// if(columnLen>0)
// printf("szdd_id = %s szdd_name = %s\n", szdd_id,szdd_name);
// else
// printf("szdd_name = NULL city = NULL\n");
// Sleep(100);
RetMSG.istuid = istudentid;
strcpy(RetMSG.szclass,szclass);
strcpy(RetMSG.szname,szname);
strcpy(RetMSG.szsex,szsex);
printf("%s\n",RetMSG.szname);
i++;
if(i ==1 )
{break;}
}
// if (hstmt != SQL_NULL_HANDLE)
// {
// SQLCancel(hstmt);
// SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
// hstmt = SQL_NULL_HANDLE;
// hstmtcount = 0;
//
return iRetcode;
}
int OdbcDBLinker::insertdata(student& stu)
{
int iRetcode = 0;
SQLCancel(hstmt);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
hstmt = SQL_NULL_HANDLE;
hstmtcount = 0;
printf("%s\n",stu.szclass);
iRetcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); //设置语句句柄
if(iRetcode<0)
{
printf("Error\n");
}
printf("%d\n",hstmt);
SQLCHAR szSqlStmt[1024]= {0};
_snprintf((char*)szSqlStmt,sizeof(szSqlStmt),"INSERT INTO Student (stuid,name,sex,class)VALUES(%d,'%s','%s','%s')",stu.istuid,stu.szclass,stu.szname,stu.szsex
);
//printf("%s",(char*)szSqlStmt);
iRetcode = SQLPrepare(hstmt, szSqlStmt, SQL_NTS);
iRetcode = SQLExecute(hstmt);
//iRetcode = SQLExecDirect(hstmt,szSqlStmt,SQL_NTS);
return 0;
}
=================
测试文件
==============
#include "OdbcSql.h"
int main()
{
OdbcDBLinker* myDblink = OdbcDBLinker::Instance();
student ast ={0};
//vector<student> mast;
myDblink->ConnectDB("abc","ftssett","123");
SQLCHAR szSqlStmt[100]={0};
_snprintf((char*)szSqlStmt,sizeof(szSqlStmt) - 1,"SELECT * FROM Student ");
myDblink->SelectTable(szSqlStmt,ast);
//printf("%d\n",ast.istuid);
memset(&ast,0,sizeof(student));
myDblink->SelectTable(szSqlStmt,ast);
memset(&ast,0,sizeof(student));
myDblink->SelectTable(szSqlStmt,ast);
//myDblink->CloseLink();
// myDblink->ConnectDB("abc","ftssett","123");
student myast ={88,"2","q","q"};
myDblink->insertdata(myast);
return 0;
}
文章结束给大家分享下程序员的一些笑话语录：
IBM和波音777
波音777是有史以来第一架完全在电脑虚拟现实中设计制造的飞机，所用的设备完全由IBM公司所提供。试飞前，波音公司的总裁非常热情的邀请IBM的技术主管去参加试飞，可那位主管却说道:“啊，非常荣幸，可惜那天是我妻子的生日，So..”..
波音公司的总载一听就生气了:“胆小鬼，我还没告诉你试飞的日期呢!”