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>E*ECSQLINCLUDESQLCA;〔1〕main(){E*ECSQLBEGINDECLARESECTION;〔2〕charfirstname[13];charuserid[9];charpasswd[19];E*ECSQLENDDECLARESECTION;E*ECSQLCONNECTTOsample;〔3〕E*ECSQLSELECTFIRSTNMEINTO:firstname〔4〕FROMemployeeWHERELASTNAME='JOHNSON';〔4〕printf("Firstname=%s\n",firstname);E*ECSQLCONNECTRESET;〔5〕return0;}. z.-上面是一個簡單的靜態(tài)嵌入SQL語句的應(yīng)用程序。它包括了嵌入SQL的主要局部：〔1〕中的include SQLCA語句定義并描述了SQLCA的構(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語句中，主變量前均有“：〞標志以示區(qū)別?！?〕在每次訪問數(shù)據(jù)庫之前必須做CONNECT操作，以連接到*一個數(shù)據(jù)庫上。這時，應(yīng)該保證數(shù)據(jù)庫實例已經(jīng)啟動?！?〕是一條選擇語句。它將表employee中的LASTNAME為“JOHNSON〞的行數(shù)據(jù)的FIRSTNAME查出，并將它放在firstname變量中。該語句返回一個結(jié)果?？梢酝ㄟ^游標返回多個結(jié)果。當(dāng)然，也可以包含update、insert和delete語句。〔5〕最后斷開數(shù)據(jù)庫的連接。從上例看出，每條嵌入式SQL語句都用E*ECSQL開場，說明它是一條SQL語句。這也是告訴預(yù)編譯器在E*ECSQL和“；〞之間是嵌入SQL語句。如果一條嵌入式SQL語句占用多行，在C程序中可以用續(xù)行符“\〞。C語言與SQLSERVER數(shù)據(jù)庫1.使用C語言來操作SQLSERVER數(shù)據(jù)庫,采用ODBC開放式數(shù)據(jù)庫連接進展數(shù)據(jù)的添加,修改,刪除,查詢等操作。step1:啟動SQLSERVER效勞,例如:HNHJ,開場菜單->運行->netstartmssqlserver. z.-step2:翻開企業(yè)管理器,建立數(shù)據(jù)庫test,在test庫中建立test表(avarchar(200),bvarchar(200))step3:建立系統(tǒng)DSN,開場菜單->運行->odbcad32,添加->SQLSERVER名稱：csql,效勞器：HNHJ使用用戶使用登錄ID和密碼的SQLSERVER驗證,登錄ID:sa,密碼:更改默認的數(shù)據(jù)庫為:test...測試數(shù)據(jù)源，測試成功，即DNS添加成功。2.cpp文件完整代碼//**************************save.cpp**************************C代碼*include<stdio.h>*include<string.h>*include<windows.h>*include<sql.h>*include<sqle*t.h>*include<sqltypes.h>*include<odbcss.h>SQLHENVhenv=SQL_NULL_HENV;SQLHDBChdbc1=SQL_NULL_HDBC;SQLHSTMThstmt1=SQL_NULL_HSTMT;. z.-/*cpp文件功能說明：1.數(shù)據(jù)庫操作中的添加，修改，刪除，主要表達在SQL語句上2.采用直接執(zhí)行方式和參數(shù)預(yù)編譯執(zhí)行方式兩種*/intmain(){RETCODEretcode;UCHARszDSN[SQL_MA*_DSN_LENGTH+1]="csql",szUID[MA*NAME]="sa",szAuthStr[MA*NAME]=";//SQL語句//直接SQL語句23.UCHARsql[37]="insertintotestvalues('aaa','100')";24.//預(yù)編譯SQL語句25.UCHARpre_sql[29]="insertintotestvalues(",")";//1.連接數(shù)據(jù)源//1.環(huán)境句柄retcode=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);retcode=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//2.連接句柄retcode=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc1);34. retcode=SQLConnect(hdbc1,szDSN,4,szUID,2,szAuthStr,0);35.//判斷連接是否成功. z.-36.if((retcode!=SQL_SUCCESS)&&(retcode!=SQL_SUCCESS_WITH_INFO)){printf("連接失敗!\n");}else{//2.創(chuàng)立并執(zhí)行一條或多條SQL語句/*1.分配一個語句句柄(statementhandle)2.創(chuàng)立SQL語句3.執(zhí)行語句4.銷毀語句*/retcode=SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);//第一種方式//直接執(zhí)行//添加操作//SQLE*ecDirect(hstmt1,sql,37);//第二種方式//綁定參數(shù)方式chara[200]="bbb";charb[200]="200";SQLINTEGERp=SQL_NTS;//1預(yù)編譯SQLPrepare(hstmt1,pre_sql,29);//第三個參數(shù)與數(shù)組大小一樣，而不是數(shù)據(jù)庫列一樣//2綁定參數(shù)值SQLBindParameter(hstmt1,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,200,0,&a,0,&p);. z.-SQLBindParameter(hstmt1,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,200,0,&b,0,&p);//3執(zhí)行SQLE*ecute(hstmt1);printf("操作成功!");//釋放語句句柄SQLCloseCursor(hstmt1);SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);}//3.斷開數(shù)據(jù)源/*1.斷開與數(shù)據(jù)源的連接.2.釋放連接句柄.3.釋放環(huán)境句柄(如果不再需要在這個環(huán)境中作更多連接)*/SQLDisconnect(hdbc1);SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);SQLFreeHandle(SQL_HANDLE_ENV,henv);return(0);}//**************************list.cpp**************************C代碼1.*include<stdio.h>. z.-*include<string.h>*include<windows.h>*include<sql.h>*include<sqle*t.h>*include<sqltypes.h>*include<odbcss.h>SQLHENVhenv=SQL_NULL_HENV;SQLHDBChdbc1=SQL_NULL_HDBC;SQLHSTMThstmt1=SQL_NULL_HSTMT;/*查詢SQLSERVER數(shù)據(jù)庫,1.條件查詢,2.直接查詢?nèi)?/intmain(){RETCODEretcode;UCHARszDSN[SQL_MA*_DSN_LENGTH+1]="csql",szUID[MA*NAME]="sa",szAuthStr[MA*NAME]=";19.UCHARsql1[39]="selectbfromtestwherea='aaa'";20.UCHARsql2[35]="selectbfromtestwherea="";21.UCHARsql3[19]="selectbfromtest";retcode=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);retcode=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,. z.-SQL_IS_INTEGER);retcode=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc1);//1.連接數(shù)據(jù)源28. retcode=SQLConnect(hdbc1,szDSN,4,szUID,2,szAuthStr,0);29.if((retcode!=SQL_SUCCESS)&&(retcode!=SQL_SUCCESS_WITH_INFO)){printf("連接失敗!");}else{//2.創(chuàng)立并執(zhí)行一條或多條SQL語句/*1.分配一個語句句柄(statementhandle)2.創(chuàng)立SQL語句3.執(zhí)行語句4.銷毀語句*/retcode=SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);//第一種方式/*//直接執(zhí)行SQLE*ecDirect(hstmt1,sql1,39);charlist[5];SQLBindCol(hstmt1,1,SQL_C_CHAR,list,5,0);SQLFetch(hstmt1);printf("%s\n",list);*/. z.-//第二種方式/*//綁定參數(shù)方式chara[200]="aaa";SQLINTEGERp=SQL_NTS;//1.預(yù)編譯SQLPrepare(hstmt1,sql2,35);//第三個參數(shù)與數(shù)組大小一樣，而不是數(shù)據(jù)庫列一樣//2.綁定參數(shù)值SQLBindParameter(hstmt1,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,200,0,&a,0,&p);//3.執(zhí)行SQLE*ecute(hstmt1);charlist[5];SQLBindCol(hstmt1,1,SQL_C_CHAR,list,5,0);SQLFetch(hstmt1);printf("%s\n",list);*///第三種方式全部輸出/*1.確認一個結(jié)果集是否可用。2.將結(jié)果集的列綁定在適當(dāng)?shù)淖兞可稀?.取得行*///3.檢查結(jié)果記錄(如果有的話)SQLE*ecDirect(hstmt1,sql3,19);. z.-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);//釋放語句句柄SQLCloseCursor(hstmt1);SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);}//4.斷開數(shù)據(jù)源/*1.斷開與數(shù)據(jù)源的連接.2.釋放連接句柄.3.釋放環(huán)境句柄(如果不再需要在這個環(huán)境中作更多連接)*/SQLDisconnect(hdbc1);SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);SQLFreeHandle(SQL_HANDLE_ENV,henv);return(0);}. z.-創(chuàng)立數(shù)據(jù)源的過程代碼:*include<stdlib.h>*inlcude<stdio.h>*include<windows.h>*include<sql.h>*include<sqle*t.h>*include<Sqltypes.h>*defineSNO_LEN30*defineNAME_LEN50*defineDEPART_LEN100*defineSSE*_LEN5intmain(){/*Step1定義句柄和變量*///以king開頭的表示的是連接KingbaseES的變量//以server開頭的表示的是連接SQLSERVER的變量SQLHENV kinghenv,serverhenv;SQLHDBC kinghdbc,serverhdbc;SQLHSTMTkinghstmt,serverhstmt;SQLRETURNret;SQLCHAR sName[NAME_LEN],sDepart[DEPART_LEN],sSe*[SSE*_LEN],sSno[SNO_LEN];SQLINTEGERsAge;SQLINTEGRRcbAge=0,cbSno=SQL_NTS,cbSe*=SQL_NTS,cbName=SQL_NTS,cbDepart=SQL_NTS;. z.-/*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;//連接失敗時返回錯誤值;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兩種方式執(zhí)行語句*//*預(yù)編譯帶有參數(shù)的語句*/ret=SQLPrepare(sercerhstmt,"INSERTINTOSTUDENT(SNO,SNAME,SSE*,SAGE,SDEPT)VALUES(",",",",")",SQL_NTS);if(ret==SQL_SUCCESS||ret==SQL_SUCCESS_WITH_INFO). z.-{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,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,2,0,sSe*,0,&cbSe*);ret=SQLBindParameter(serverhstmt,4,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&sAge,0,&cbAge);ret=SQLBindParameter(serverhstmt,5,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,DEPART_LEN,0,sDepart,0,&cbDepart);}/*執(zhí)行SQL語句*/ret=SQLE*ecDirect(kinghstmt,"SELECT*FROMSTUDENT",SQL