C语言连接SQL数据库

连接到SAMPLE数据库，查询LASTNAME为JOHNSON的FIRSTNAME信息。#include<stdio.h>#include<stdlib.h>#include<string.h>#include"util.h"#include<sqlca.h>EXECSQLINCLUDESQLCA;(1)main(){EXECSQLBEGINDECLARESECTION;(2)charfirstname[13];charuserid[9];charpasswd[19];EXECSQLENDDECLARESECTION;EXECSQLCONNECTTOsample;(3)EXECSQLSELECTFIRSTNMEINTO:firstname(4)FROMemployeeWHERELASTNAME='JOHNSON';(4)printf("Firstname=%s\n",firstname);EXECSQLCONNECTRESET;(5)return0;}上面是一个简单的静态嵌入SQL语句的应用程序。它包括了嵌入SQL的主要部分：中的includeSQLCA语句定义并描述了SQLCA的结构。SQLCA用于应用程序和数据库之间的通讯，其中的SQLCODE返回SQL语句执行后的结果状态。在BEGINDECLARESECTION和ENDDECLARESECTION之间定义了宿主变量。宿主变量可被SQL语句引用，也可以被C语言语句引用。它用于将程序中的数据通过SQL语句传给数据库管理器，或从数据库管理器接收查询的结果。在SQL语句中，主变量前均有“：”标志以示区别。在每次访问数据库之前必须做CONNECT操作，以连接到某一个数据库上。这时，应该保证数据库实例已经启动。是一条选择语句。它将表employee中的LASTNAME为“JOHNSON”的行数据的FIRSTNAME查出，并将它放在firstname变量中。该语句返回一个结果。可以通过游标返回多个结果。当然，也可以包含update、insert和delete语句。最后断开数据库的连接。从上例看出，每条嵌入式SQL语句都用EXECSQL开始，表明它是一条SQL语句。这也是告诉预编译器在EXECSQL和“；”之间是嵌入SQL语句。如果一条嵌入式SQL语句占用多行，在C程序中可以用续行符、'\”。c语言用vc连接sqlserver20002009/11/0209:34[sqlserver].Q1.提取单条记录//#import"C:\ProgramFiles\CommonFiles\System\ADO\msado15.dll"\//no_namespace,rename(〃EOF〃，〃adoEOF〃)，named_guidsCoInitialize(NULL);_bstr_tvarSource=〃Provider二Microsoft.Jet.OLEDB.4.0;DataSource=*.mdb〃;//_bstr_tvarSource=〃DataSource二myServerAddress;InitialCatalog二myDataBase;UserId=myUsername;Password=myPassword;"_ConnectionPtrm_pConnection(_uuidof(Connection));m_pConnection->Open(varSource,〃〃，〃〃，adModeUnknow);_RecordsetPtrm_pSet(_uuid(Recordset));try(m_pSet->Open(%%1,m_pConnection.GetInterfacePtr()adOpenDynamic,adLockPessimistic,adCmdText);}catch(_com_error*e)((AfxMessageBox(e->ErrorMessage());return;}_variant_tvar;CString%%2二〃〃;longfldc=m_pSet->GetFields()->GetCount();longi=0;try(m_pSet->MoveFirst();if(!m_pSet->adoEOF)(for(i=0;i<fldc;i++)(var=m_pSet->GetCollect((long)i);var.ChangeType(VT_BSTR);%%2+=var.bstrVal;%%2+二〃〃;}//m_pSet->MoveNext();}}catch(_com_error*e)(AfxMessageBox(e->ErrorMessage());deletee;}//m_pSet->MoveFirst();CoUninitialize(NULL);单值比较//#import"C:\ProgramFiles\CommonFiles\System\ADO\msado15.dll"\//no_namespace,rename(〃EOF〃，〃adoEOF〃)，named_guidsCoInitialize(NULL);_bstr_tvarSource="Provider二Microsoft.Jet.OLEDB.4.0;DataSource=*.mdb”;//_bstr_tvarSource="DataSource二myServerAddress;InitialCatalog二myDataBase;UserId=myUsername;Password=myPassword;"_ConnectionPtrm_pConnection(_uuidof(Connection));m_pConnection->Open(varSource,"","",adModeUnknow);_RecordsetPtrm_pSet(_uuid(Recordset));try(m_pSet->Open(%%1,m_pConnection.GetInterfacePtr()adOpenDynamic,adLockPessimistic,adCmdText);}catch(_com_error*e)((AfxMessageBox(e->ErrorMessage());return;}_variant_tvar;try(m_pSet->MoveFirst();if(!m_pSet->adoEOF)(var=m_pSet->GetCollect((long)0);var.ChangeType(VT_I2);int%%3二Val;if(%%3==%%4)(%%5}//m_pSet->MoveNext();}catch(_com_error*e)(AfxMessageBox(e->ErrorMessage());deletee;}//m_pSet->MoveFirst();CoUninitialize(NULL);显示表格//#import"C:\ProgramFiles\CommonFiles\System\ADO\msado15.dll"\//no_namespace,rename("EOF","adoEOF"),named_guidsCoInitialize(NULL);_bstr_tvarSource="Provider二Microsoft.Jet.OLEDB.4.0;DataSource=*.mdb”;//_bstr_tvarSource="DataSource二myServerAddress;InitialCatalog二myDataBase;UserId=myUsername;Password=myPassword;"_ConnectionPtrm_pConnection(_uuidof(Connection));m_pConnection->Open(varSource,"","",adModeUnknow);//打开属性为默认(adModeRead(只读)，adModeWrite(可写)，adModeReadWrite(可读写)等)_RecordsetPtrm_pSet(_uuid(Recordset));try(HRESULThr=m_pSet->Open(%%1,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockPessimistic,adCmdText);}catch(_com_error*e)(AfxMessageBox(e->ErrorMessage());}if(SUCCESSED(hr))(//表打开成功}FieldsPtrp_fields=m_pSet->Fields;FieldPtrp_field;_variant_tvar_index;LPCSTRfield_name;intindex=0;_bstr_tbstr_field_name;intcountfields=p_fields->GetCount();CString*Column=newCString[countfields];CListCtrl*pList=(CListCtrl*)GetDlgItem(%%1);//IDC_LIST_TABLEDATAVERIFY(pList);pList->DeleteAllItems();for(index=0;index<countfields;index++)(var_index.vt=VT_I4;var_index.IVal=index;p_field=p_fields->Item[var_index];bstr_field_name=p_field->GetName();field_name=(LPCSTR)bstr_field_name;Column[index]=field_name;intColumnWidth=Column[index].GetLength()*15;pList->InsertColumn(index,field_name,LVCFMT_CENTER,ColumnWidth);}inti=0;_bstr_tvCol;//pList->SetTextBkColor(RGB(122,200,122));//pList->SetTextColor(RGB(0,0,200));while(!m_pSet->adoEOF)(pList->Insert(i,atoi(i));for(intj=0;j<countfields;j++)(vCol=m_pSet->GetCollect((long)j);pList->SetItemText(i,j,vCol);}m_pSet->MoveNext();i++;}CoUninitialize(NULL);C语言与SQLSERVER数据库使用C语言来操作SQLSERVER数据库，采用ODBC开放式数据库连接进行数据的添加，修改，删除，查询等操作。_step1:启动SQLSERVER服务，例如:HNHJ,开始菜单->运行->netstartmssqlserverstep2:打开企业管理器，建立数据库test,在test库中建立test表(avarchar(200),bvarchar(200))step3:建立系统DSN，开始菜单->运行->odbcad32,添加->SQLSERVER名称：csql,服务器：HNHJ使用用户使用登录ID和密码的SQLSERVER验证，登录ID:sa,密码：更改默认的数据库为:test...测试数据源，测试成功，即DNS添加成功。cpp文件完整代码//##########################save.cpp##########################C代码口洱;#include<stdio.h>#include<string.h>#include<windows.h>#include<sql.h>#include<sqlext.h>#include<sqltypes.h>#include<odbcss.h>8.SQLHENVhenv=SQL_NULL_HENV;SQLHDBChdbc1=SQL_NULL_HDBC;SQLHSTMThstmt1=SQL_NULL_HSTMT;12./*cpp文件功能说明：1.数据库操作中的添加，修改，删除，主要体现在SQL语句上2.采用直接执行方式和参数预编译执行方式两种*/intmain()(RETCODEretcode;UCHARszDSN[SQL_MAX_DSN_LENGTH+1]="csql”,szUID[MAXNAME]="sa",szAuthStr[MAXNAME]="”;//SQL语句//直接SQL语句UCHARsql[37]="insertintotestvalues('aaa','100')";//预编译SQL语句UCHARpre_sql[29]="insertintotestvalues(?,?)";//1.连接数据源//1.环境句柄retcode=SQLAllocHandle(SQL_HANDLE_ENVNULL,&henv);retcode=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//2.连接句柄retcode=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc1);retcode=SQLConnect(hdbc1,szDSN,4,szUID,2,szAuthStr,0);//判断连接是否成功if((retcode!=SQL_SUCCESS)&&(retcode!=SQL_SUCCESS_WITH_INFO))(printf("连接失败!\n");}else(//2.创建并执行一条或多条SQL语句/*1.分配一个语句句柄(statementhandle)2.创建SQL语句3.执行语句4.销毁语句*/retcode=SQLAllocHandle(SQL_HANDLE_STMT,hdbcl,&hstmt1);//第一种方式〃直接执行//添加操作//SQLExecDirect(hstmt1,sql,37);53.〃第二种方式〃绑定参数方式chara[200]="bbb”;charb[200]="200”;SQLINTEGERp=SQL_NTS;//1预编译SQLPrepare(hstmt1,pre_sql,29);//第三个参数与数组大小相同，而不是数据库列相同//2绑定参数值SQLBindParameter(hstmt1,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,200,0,&a,0,&p);SQLBindParameter(hstmt1,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,200,0,&b,0,&p);//3执行SQLExecute(hstmt1);66.printf("操作成功!”)；〃释放语句句柄SQLCloseCursor(hstmt1);SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);71.TOC\o"1-5"\h\z}//3.断开数据源/*1.断开与数据源的连接.2.释放连接句柄.3.释放环境句柄(如果不再需要在这个环境中作更多连接)*/SQLDisconnect(hdbc1);SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);SQLFreeHandle(SQL_HANDLE_ENVhenv);return(0);}//##########################list.cpp##########################C代码口洱;#include<stdio.h>#include<string.h>#include<windows.h>#include<sql.h>#include<sqlext.h>#include<sqltypes.h>#include<odbcss.h>8.SQLHENVhenv=SQL_NULL_HENV;SQLHDBChdbc1=SQL_NULL_HDBC;SQLHSTMThstmt1=SQL_NULL_HSTMT;12./*查询SQLSERVER数据库,1.条件查询,2.直接查询全部*/intmain()(RETCODEretcode;UCHARszDSN[SQL_MAX_DSN_LENGTH+1]="csql”,szUID[MAXNAME]="sa",szAuthStr[MAXNAME]="”;UCHARsql1[39]="selectbfromtestwherea='aaa'";UCHARsql2[35]="selectbfromtestwherea=?";UCHARsql3[19]="selectbfromtest";24.retcode=SQLAllocHandle(SQL_HANDLE_ENVNULL,&henv);retcode=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);retcode=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc1);//1,连接数据源retcode=SQLConnect(hdbc1,szDSN,4,szUID,2,szAuthStr,0);if((retcode!=SQL_SUCCESS)&&(retcode!=SQL_SUCCESS_WITH_INFO))(printf("连接失败!”)；}else{//2.创建并执行一条或多条SQL语句/*1.分配一个语句句柄(statementhandle)2.创建SQL语句3.执行语句4.销毁语句*/retcode=SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);//第一种方式/*〃直接执行SQLExecDirect(hstmt1,sql1,39);charlist[5];SQLBindCol(hstmt1,1,SQL_C_CHAR,list,5,0);SQLFetch(hstmt1);printf("%s\n",list);*/52.〃第二种方式/*〃绑定参数方式chara[200]="aaa”;SQLINTEGERp=SQL_NTS;//1.预编译SQLPrepare(hstmt1,sql2,35);//第三个参数与数组大小相同，而不是数据库列相同//2.绑定参数值SQLBindParameter(hstmt1,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,200,0,&a,0,&p);//3.执行SQLExecute(hstmt1);charlist[5];SQLBindCol(hstmt1,1,SQL_C_CHAR,list,5,0);SQLFetch(hstmt1);printf("%s\n",list);TOC\o"1-5"\h\z*/69.〃第三种方式全部输出/*1.确认一个结果集是否可用。2.将结果集的列绑定在适当的变量上。3.取得行*///3.检查结果记录(如果有的话)SQLExecDirect(hstmt1,sql3,19);charlist[5];SQLBindCol(hstmt1,1,SQL_C_CHAR,list,5,0);do{retcode=SQLFetch(hstmt1);if(retcode==SQL_NO_DATA){break;}printf("%s\n",list);}while(1);87.〃释放语句句柄SQLCloseCursor(hstmtl);SQLFreeHandle(SQL_HANDLE_STMT,hstmtl);91.TOC\o"1-5"\h\z}93.//4.断开数据源/*1,断开与数据源的连接.2,释放连接句柄.3,释放环境句柄(如果不再需要在这个环境中作更多连接)*/SQLDisconnect(hdbcl);SQLFreeHandle(SQL_HANDLE_DBC,hdbcl);SQLFreeHandle(SQL_HANDLE_ENVhenv);return(0);}创建数据源的过程代码：#include<stdlib.h>#inlcude<stdio.h>#include<windows.h>#include<sql.h>#include<sqlext.h>#include<Sqltypes.h>#defineSNO_LEN30#defineNAME_LEN50#defineDEPART_LEN100#defineSSEX_LEN5intmain(){/*Step1定义句柄和变量*/〃以king开头的表示的是连接KingbaseES的变量〃以server开头的表示的是连接SQLSERVER的变量SQLHENVkinghenv,serverhenv;SQLHDBCkinghdbc,serverhdbc;SQLHSTMTkinghstmt,serverhstmt;SQLRETURNret;SQLCHARsName[NAME_LEN],sDepart[DEPART_LEN],sSex[SSEX_LEN],sSno[SNO_LEN];SQLINTEGERsAge;SQLINTEGRRcbAge=0,cbSno=SQL_NTS,cbSex=SQL_NTS,cbName=SQL_NTS,cbDepart=SQL_NTS;/*Step2初始化环境*/ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&kinghenv);ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&setverhenv);ret=SQLSetEnvAttr(kinghenv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);ret=SQLSetEnvAttr(serverhenv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);/*Step3建立连接*/ret=SQLAllocHandle(SQL_HANDLE_DBC,kinghenv,&kinghdbc);ret=SQLAllocHandle(SQL_HANDLE_DBC,serverhenv,&serverhdbc);ret=SQLConnect(kinghdbc,"KingbaseESODBC”,SQL_NTS,"SYSTEM”,SQL_NTS,"MANAGER”,SQL_NTS);if(!SQL_SUCCEEDED(ret))return-1;〃连接失败时返回错误值；ret=SQLConnect(serverhdbc,"SQLServer”,SQL_NTS,"sa”,SQL_NTS,"sa”,SQL_NTS);if(!SQL_SUCCEEDED(ret))return-1;〃连接失败时返回错误值；/*Step4初始化语句句柄*/ret=SQLAllocHandle(SQL_HANDLE_STMT,kinghdbc,&kinghstmt);ret=SQLSetStmtAttr(kinghstmt,SQL_ATTR_ROW_BIND_TYPE(SQLPOINTER)SQL_BIND_BY_COLUMN,SQL_IS_INTEGER);ret=SQLAllocHandle(SQL_HANDLE_STMT,serverhdbc,&serverhstmt);/*Step5两种方式执行语句*//*预编译带有参数的语句*/ret=SQLPrepare(sercerhstmt,"INSERTINTOSTUDENT(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(?,?,?,?,?)”,SQL_NTS);if(ret==SQL_SUCCESS||ret==SQL_SUCCESS_WITH_INFO){ret=SQLBindParameter(serverhstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,SNO_LEN,0,sSno,0,&cbSNO);ret=SQLBindParameter(serverhstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,SNAME_LEN,0,sName,0,&cbName);ret=SQLBindParameter(serverhstmt,