C語言連接SQL數(shù)據(jù)庫

連接到SAMPLE數(shù)據(jù)庫，查詢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;}上面是一個(gè)簡(jiǎn)單的靜態(tài)嵌入SQL語句的應(yīng)用程序。它包括了嵌入SQL的主要部分：中的includeSQLCA語句定義并描述了SQLCA的結(jié)構(gòu)。SQLCA用于應(yīng)用程序和數(shù)據(jù)庫之間的通訊，其中的SQLCODE返回SQL語句執(zhí)行后的結(jié)果狀態(tài)。在BEGINDECLARESECTION和ENDDECLARESECTION之間定義了宿主變量。宿主變量可被SQL語句引用，也可以被C語言語句引用。它用于將程序中的數(shù)據(jù)通過SQL語句傳給數(shù)據(jù)庫管理器，或從數(shù)據(jù)庫管理器接收查詢的結(jié)果。在SQL語句中，主變量前均有“：”標(biāo)志以示區(qū)別。在每次訪問數(shù)據(jù)庫之前必須做CONNECT操作，以連接到某一個(gè)數(shù)據(jù)庫上。這時(shí)，應(yīng)該保證數(shù)據(jù)庫實(shí)例已經(jīng)啟動(dòng)。是一條選擇語句。它將表employee中的LASTNAME為“JOHNSON”的行數(shù)據(jù)的FIRSTNAME查出，并將它放在firstname變量中。該語句返回一個(gè)結(jié)果。可以通過游標(biāo)返回多個(gè)結(jié)果。當(dāng)然，也可以包含update、insert和delete語句。最后斷開數(shù)據(jù)庫的連接。從上例看出，每條嵌入式SQL語句都用EXECSQL開始，表明它是一條SQL語句。這也是告訴預(yù)編譯器在EXECSQL和“；”之間是嵌入SQL語句。如果一條嵌入式SQL語句占用多行，在C程序中可以用續(xù)行符、'\”。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);//打開屬性為默認(rèn)(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數(shù)據(jù)庫使用C語言來操作SQLSERVER數(shù)據(jù)庫，采用ODBC開放式數(shù)據(jù)庫連接進(jìn)行數(shù)據(jù)的添加，修改，刪除，查詢等操作。_step1:啟動(dòng)SQLSERVER服務(wù)，例如:HNHJ,開始菜單->運(yùn)行->netstartmssqlserverstep2:打開企業(yè)管理器，建立數(shù)據(jù)庫test,在test庫中建立test表(avarchar(200),bvarchar(200))step3:建立系統(tǒng)DSN，開始菜單->運(yùn)行->odbcad32,添加->SQLSERVER名稱：csql,服務(wù)器：HNHJ使用用戶使用登錄ID和密碼的SQLSERVER驗(yàn)證，登錄ID:sa,密碼：更改默認(rèn)的數(shù)據(jù)庫為:test...測(cè)試數(shù)據(jù)源，測(cè)試成功，即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.數(shù)據(jù)庫操作中的添加，修改，刪除，主要體現(xiàn)在SQL語句上2.采用直接執(zhí)行方式和參數(shù)預(yù)編譯執(zhí)行方式兩種*/intmain()(RETCODEretcode;UCHARszDSN[SQL_MAX_DSN_LENGTH+1]="csql”,szUID[MAXNAME]="sa",szAuthStr[MAXNAME]="”;//SQL語句//直接SQL語句UCHARsql[37]="insertintotestvalues('aaa','100')";//預(yù)編譯SQL語句UCHARpre_sql[29]="insertintotestvalues(?,?)";//1.連接數(shù)據(jù)源//1.環(huán)境句柄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.創(chuàng)建并執(zhí)行一條或多條SQL語句/*1.分配一個(gè)語句句柄(statementhandle)2.創(chuàng)建SQL語句3.執(zhí)行語句4.銷毀語句*/retcode=SQLAllocHandle(SQL_HANDLE_STMT,hdbcl,&hstmt1);//第一種方式〃直接執(zhí)行//添加操作//SQLExecDirect(hstmt1,sql,37);53.〃第二種方式〃綁定參數(shù)方式chara[200]="bbb”;charb[200]="200”;SQLINTEGERp=SQL_NTS;//1預(yù)編譯SQLPrepare(hstmt1,pre_sql,29);//第三個(gè)參數(shù)與數(shù)組大小相同，而不是數(shù)據(jù)庫列相同//2綁定參數(shù)值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執(zhí)行SQLExecute(hstmt1);66.printf("操作成功!”)；〃釋放語句句柄SQLCloseCursor(hstmt1);SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);71.TOC\o"1-5"\h\z}//3.斷開數(shù)據(jù)源/*1.斷開與數(shù)據(jù)源的連接.2.釋放連接句柄.3.釋放環(huán)境句柄(如果不再需要在這個(gè)環(huán)境中作更多連接)*/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數(shù)據(jù)庫,1.條件查詢,2.直接查詢?nèi)?/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,連接數(shù)據(jù)源retcode=SQLConnect(hdbc1,szDSN,4,szUID,2,szAuthStr,0);if((retcode!=SQL_SUCCESS)&&(retcode!=SQL_SUCCESS_WITH_INFO))(printf("連接失敗!”)；}else{//2.創(chuàng)建并執(zhí)行一條或多條SQL語句/*1.分配一個(gè)語句句柄(statementhandle)2.創(chuàng)建SQL語句3.執(zhí)行語句4.銷毀語句*/retcode=SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);//第一種方式/*〃直接執(zhí)行SQLExecDirect(hstmt1,sql1,39);charlist[5];SQLBindCol(hstmt1,1,SQL_C_CHAR,list,5,0);SQLFetch(hstmt1);printf("%s\n",list);*/52.〃第二種方式/*〃綁定參數(shù)方式chara[200]="aaa”;SQLINTEGERp=SQL_NTS;//1.預(yù)編譯SQLPrepare(hstmt1,sql2,35);//第三個(gè)參數(shù)與數(shù)組大小相同，而不是數(shù)據(jù)庫列相同//2.綁定參數(shù)值SQLBindParameter(hstmt1,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,200,0,&a,0,&p);//3.執(zhí)行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.確認(rèn)一個(gè)結(jié)果集是否可用。2.將結(jié)果集的列綁定在適當(dāng)?shù)淖兞可稀?.取得行*///3.檢查結(jié)果記錄(如果有的話)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.斷開數(shù)據(jù)源/*1,斷開與數(shù)據(jù)源的連接.2,釋放連接句柄.3,釋放環(huán)境句柄(如果不再需要在這個(gè)環(huán)境中作更多連接)*/SQLDisconnect(hdbcl);SQLFreeHandle(SQL_HANDLE_DBC,hdbcl);SQLFreeHandle(SQL_HANDLE_ENVhenv);return(0);}創(chuàng)建數(shù)據(jù)源的過程代碼：#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初始化環(huán)境*/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;〃連接失敗時(shí)返回錯(cuò)誤值；ret=SQLConnect(serverhdbc,"SQLServer”,SQL_NTS,"sa”,SQL_NTS,"sa”,SQL_NTS);if(!SQL_SUCCEEDED(ret))return-1;〃連接失敗時(shí)返回錯(cuò)誤值；/*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兩種方式執(zhí)行語句*//*預(yù)編譯帶有參數(shù)的語句*/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,