C语言连接SQL数据库例子.doc

《C语言连接SQL数据库例子.doc》由会员分享，可在线阅读，更多相关《C语言连接SQL数据库例子.doc（29页珍藏版）》请在三一文库上搜索。

1、连接至U SAMPLE 数据库，查询 LASTNAME 为JOHNSON 的FIRSTNAME 信息#include<stdio.h>#include<stdlib.h>#include<string.h>#include"util.h"#include<sqlca.h>EXECSQL INCLUDEmain()EXECSQL BEGINDECLARESQLCA; ( 1)SECTION;( 2)SQL的主要部分:SQLCA用于应用程序和数据库之间的SECTION之间定义了宿主变量。宿主charfirstname13;char

2、userid9;charpasswd19;EXECSQL CONNECTSQLTO sample;（3）EXECSELECTFIRSTNMEINTO:firstname（4）FROMemployeeWHERELASTNAME='JOHNSON'(4)printf("Firstname=%sn",firstnameEXECSQL CONNECTRESET;( 5)SECTION;）；0；returnEXEC SQL END DECLARE上面是一个简单的静态嵌入 SQL语句的应用程序。它包括了嵌入（1）中的includeSQLCA语句定义并描述了 SQLCA的

3、结构。通讯，其中的SQLCODE返回SQL语句执行后的结果状态。(2)在 BEGIN DECLARE SECTION 和 END DECLARE变量可被SQL语句引用，也可以被 C语言语句引用。它用于将程序中的数据通过SQL语句传给数据库管理器，或从数 据库管理器接收查询的结果。在 SQL语句中，主变量前均有 ：”标志以示区别。（3 ）在每次访问数据库之前必须做CONNECT操作，以连接到某一个数据库上。这时，应该保证数据库实例已经启动。（4 ）是一条选择语句。它将表 employee 中的LASTNAME 为JOHNSON ”的行数据的 FIRSTNAME查出，并将它放在 firstname

4、 变量中。该语句返回一个结果。可以通过游标返回多个结果。当然，也可以 包含 update、insert 和 delete 语句。（5）最后断开数据库的连接。从上例看出，每条嵌入式 SQL语句都用EXEC SQL开始，表明它是一条 SQL语句。这也是告诉预编 译器在EXEC SQL和“ ”之间是嵌入SQL语句。如果一条嵌入式 SQL语句占用多行，在 C程序中可以 用续行符“”c 语言用 vc 连接 sql server 20002009/11/02 09:34 sql server w1提取单条记录#import "C:Program Files'Common FilesSys

5、temADOmsado15.dll" no_n amespace,re name("EOF","adoEOF"), named_guidsCoI ni tialize(NULL);Source=*.mdb"/_bstr_t varSource="Data Source=myServerAddress;l nitialCatalog=myDataBase;User ld=myUser name;Password=myPassword;" _Conn ectio nPtr m_pC onn ectio n(_uuido

6、f(C onn ectio n);m_pC onn ectio n->Ope n(varSource,"","",adModeU nkno w);_RecordsetPtr m_pSet(_uuid(Recordset);try m_pSet->Ope n(%1,m_pCo nn ectio n.Getl nterfacePtr()adOpe nDyn amic,adLockPessimistic,adCmdText);catch(_com_error *e)AfxMessageBox(e->ErrorMessage();return;

7、_varia nt_t var;CStri ng %2=""long fldc=m_pSet->GetFields()->GetCou nt();long i=0;try m_pSet->MoveFirst();if(!m_pSet->adoEOF) for(i=O;i<fldc;i+)var=m_pSet->GetCollect(lo ng)i);var.Cha ngeType(VT_BSTR);%2+=var.bstrVal;%2+=""m_pSet->MoveNext();catch(_com_error

8、*e)AfxMessageBox(e->ErrorMessage();delete e;m_pSet->MoveFirst();CoU nin itialize(NULL);2单值比较#import "C:Program Files'Common FilesSystemADOmsado15.dll" no_n amespace,re name("EOF","adoEOF"), named_guidsCoI ni tialize(NULL);Source=*.mdb"_bstr_t varSource=&q

9、uot;Data Source=myServerAddress;l nitialCatalog=myDataBase;User Id=myUser name;Password=myPassword;" _Conn ectio nPtr m_pC onn ectio n(_uuidof(C onn ectio n);m_pC onn ectio n->Ope n(varSource,"","",adModeU nkno w);_RecordsetPtr m_pSet(_uuid(Recordset);try m_pSet->Ope n

10、(%1,m_pCo nn ectio n.Getl nterfacePtr()adOpe nDyn amic,adLockPessimistic,adCmdText);catch(_com_error *e)AfxMessageBox(e->ErrorMessage();return;_varia nt_t var;try m_pSet->MoveFirst();if(!m_pSet->adoEOF)var=m_pSet->GetCollect(lo ng)0);var.Cha ngeType(VT2);int %3=var.i ntVal;if(%3=%4)%5m_p

11、Set->MoveNext();catch(_com_error *e)AfxMessageBox(e->ErrorMessage();delete e;m_pSet->MoveFirst();CoU nin itialize(NULL);3.显示表格#import "C:Program Files'Common FilesSystemADOmsado15.dll" no_n amespace,re name("EOF","adoEOF"), named_guidsCoI ni tialize(NULL);S

12、ource=*.mdb"_bstr_t varSource="Data Source=myServerAddress;I nitialCatalog=myDataBase;User ld=myUser name;Password=myPassword;"_Conn ectio nPtr m_pC onn ectio n(_uuidof(C onn ectio n);m_pC onn ectio n->Ope n(varSource,"","",adModeU nkno w);/打开属性为默认(adModeRead(只读

13、)，adModeWrite( 可写), adModeReadWrite(可读写)等)_RecordsetPtr m_pSet(_uuid(Recordset);try HRESULT hr=m_pSet->Ope n( %1,m_pCo nn ectio n.Getl ntefacePtr(), adOpe nDyn amic,adLockPessimistic,adCmdText);catch(_com_error *e)AfxMessageBox(e->ErrorMessage();if(SUCCESSED(hr)/表打开成功FieldsPtr p_fields=m_pSet-

14、>Fields;FieldPtr p_field;_varia nt_t var_i ndex;LPCSTR field_name;int in dex=0;_bstr_t bstr_field _n ame;int coun tfields=p_fields->GetCou nt();CStri ng *Colu mn=new CStri ngco un tfields;CListCtrl *pList=(CListCtrl*)GetDlgltem(%1);/IDC_LIST_TABLEDATAVERIFY(pList);pList->DeleteAllltems();fo

15、r(i ndex=O;i ndexvco un tfields;i ndex+)var_i ndex.vt=VT4;var_i ndex .IV al=i ndex;p_field=p_fields->ltemvar_i ndex;bstr_field_ name=p_field->GetName();field_name=(LPCSTR)bstr_field_name;Colu mnin dex=field_ name;int Colu mn Width=Colu mnin dex.GetLe ngth()*15;pList->I nsertColum n(in dex,f

16、ield_name,LVCFMT_CENTER,Colum nWidth);int i=0;_bstr_t vCol;pList->SetTextBkColor(RGB(122,200,122);pList->SetTextColor(RGB(0,0,200);while(!m_pSet->adoEOF) pList->I nsert(i,atoi(i); for(i nt j=0;j<co un tfields;j+)vCol=m_pSet->GetCollect(lo ng)j); pList->SetltemText(i,j,vCol);m_pS

17、et->MoveNext();i+;CoU nin itialize(NULL);C语言与SQL SERVER数据库1使用C语言来操作SQL SERVER数据库，采用ODBC开放式数据库连接进行数 据的添加，修改，删除,查询等操作。step1:启动SQLSERVER服务，例如:HNHJ,开始菜单-> 运行->net start mssqlserverstep2:打开企业管理器，建立数据库test,在test库中建立test表(avarchar(200),b varchar(200)step3:建立系统DSN,开始菜单-> 运行->odbcad32,添力卩->

18、SQL SERVER名称：csql,服务器：HNHJ使用用户使用登录ID和密码的SQLSERVER验证登录ID:sa,密码:更改默认的数据库为:test测试数据源，测试成功，即 DNS添加成功。2.cpp文件完整代码#C代1. #i nclude<stdio.h>2. #i nclude<stri ng.h>3. #i nclude<win dows.h>4. #i nclude<sql.h>5. #i nclude<sqlext.h>6. #i nclude<sqltypes.h>7. #i nclude<odbc

19、ss.h>9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.sqlhenv henv = sql_null_henv; sqlhdbc hdbc1 = sql_ null_hdbc; sqlhstmt hstmt1 = sql_ null_hstmt;/*cpp文件功能说明：1. 数据库操作中的添加，修改，删除，主要体现在SQL语句上2. 采用直接执行方式和参数预编译执行方式两种*/int main()RETCODE retcode;UCHAR szDSNSQL_MAX_DSN_LENGTH+1 = &

20、quot;csql", szUIDMAXNAME ="sa",szAuthStrMAXNAME =""/SQL语句/直接SQL语句UCHAR sql37 = "in sert in to test values('aaa','100')"/预编译SQL语句UCHAR pre_sql29 = "in sert in to test values(?,?)"1.连接数据源1.环境句柄retcoderetcode = SQLAllocHa ndle(SQL_HANDLE_ENV,

21、 NULL, &henv);=SQLSetE nvAttr(he nv,SQL_ATTR_ODBC_VERSION,32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.(SQLP0INTER)SQL_0V_0DBC3,SQL_IS_INTEGER);2.连接句柄retcode = SQLAIIocHa ndle(SQL_HANDLE_DBC,he nv, & hdbc1);retcode = SQLConnect(hdbc1,szDSN, 4, szUID, 2, szAuthStr, 0);/判断连接是否成功

22、if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) printf(” 连接失败!n"); else 2.创建并执行一条或多条SQL语句/*1. 分配一个语句句柄 (stateme nt han dle)2. 创建SQL语句3. 执行语句4. 销毁语句*/retcode = SQLAllocHa ndle(SQL_HANDLE_STMT,hdbc1, & hstmt1);/第一种方式/直接执行/添加操作52. /SQLExecDirect (hstmt1,sql,37);53

23、.53. /第二种方式54. /绑定参数方式55. char a200="bbb"56. char b200="200"57. SQLINTEGER p = SQL_NTS;58. /1预编译59. SQLPrepare(hstmt1,pre_sql,29); /第三个参数与数组大小相同，而不是数据库列相同60. /2绑定参数值61. SQLBi ndParameter(hstmt1,1,SQL_PARAMN PUT,SQL_C_CHAR,SQL_CHAR,200,0, &a,0, &p);62. SQLBi ndParameter(hs

24、tmt1,2,SQL_PARAMN PUT,SQL_C_CHAR,SQL_CHAR,200,0, &b,0, &p);63. /3执行64. SQLExecute(hstmt1);66.65. printf("操作成功!");66. /释放语句句柄67. SQLCloseCursor(hstmt1);68. SQLFreeHa ndle(SQL_HANDLE_STMT, hstmt1);71.69. 70. 113.断开数据源71. /*72. 1.断开与数据源的连接73. 2.释放连接句柄.74. 3.释放环境句柄(如果不再需要在这个环境中作更多连接)75

25、. */76. SQLDisco nn ect(hdbcl);77. SQLFreeHa ndle(SQL_HANDLE_DBC,hdbc1);78. SQLFreeHa ndle(SQL_HANDLE_ENV,henv);79. return(O);80. #C代1. #i nclude2. #i nclude3. #i nclude4. #i nclude5. #in clude6. #i nclude7. #in clude8.<stdio.h><stri ng.h> <win dows.h> <sql.h> <sqlext.h>

26、; <sqltypes.h> <odbcss.h>9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.SQLHENV henv = SQL_NULL_HENV;SQLHDBC hdbc1 = SQL_NULL_HDBC;SQLHSTMT hstmt1 = SQL_NULL_HSTMT;/*查询SQLSERVER数据库,1.条件查询,2.直接查询全部*/int main()RETCODE retcode;UCHAR szDSNSQL_MAX_DSN_LENGTH+1 = "csql&q

27、uot;, szUIDMAXNAME ="sa",szAuthStrMAXNAME =""UCHARsql139="selectb fromtest where a = 'aaa'"UCHARsql235="selectb fromtest where a = ?"UCHARsql319="selectb fromtest"retcode = SQLAllocHa ndle(SQL_HANDLE_ENV, NULL, &henv);retcode = SQLSetE nv

28、Attr(he nv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);retcode = SQLAllocHa ndle(SQL_HANDLE_DBC,he nv, & hdbc1);1.连接数据源2, szAuthStr, 0);SQL_SUCCESS_WIThdbc1, &hstmt1);31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.retcode = SQLConnect(hdbc1,szDSN, 4, szUID,if

29、( (retcode != SQL_SUCCESS) && (retcode !=HNFO) ) printf(”连接失败!"); else 2.创建并执行一条或多条SQL语句/*1. 分配一个语句句柄 (stateme nt han dle)2. 创建SQL语句3. 执行语句4. 销毁语句*/retcode = SQLAllocHa ndle(SQL_HANDLE_STMT,/第一种方式/*/直接执行SQLExecDirect (hstmt1,sql1,39);char list5;SQLBindCol(hstmt1,1, SQL_C_CHAR, list, 5,

30、0);SQLFetch(hstmt1);prin tf("%sn",list);51. *52.52. II第二种方式53. I*54. II绑定参数方式55. char a200="aaa"56. SQLINTEGER p = SQL_NTS;57. II1.预编译58. SQLPrepare(hstmt1,sql2,35); II第三个参数与数组大小相同， 而不是数据库列相同59. II2.绑定参数值60. SQLBi ndParameter(hstmt1,1,SQL_PARAMN PUT,SQL_C_CHAR,SQL_CHAR,200,0, &am

31、p;a,0, &p);61. II3.执行62. SQLExecute(hstmtl);63. char list5;64. SQLBindCol(hstmt1,1,SQL_C_CHAR,list,5,0);65. SQLFetch(hstmtl);66. prin tf("%sn",list);67. *I69.68. II第三种方式全部输出69. I*72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.1. 确认一个结果集是否可用。2. 将结果集的列绑定在适当的变量上。3

32、. 取得行*/3.检查结果记录(如果有的话)SQLExecDirect (hstmt1,sql3,19);char list5;SQLBindCol(hstmt1, 1, SQL_C_CHAR, list, 5, 0);doretcode = SQLFetch(hstmt1);if(retcode = SQL_NO_DATA)break;prin tf("%sn",list);while(1);/释放语句句柄SQLCloseCursor (hstmt1);SQLFreeHa ndle (SQL_HANDLE_STMT, hstmt1);4.断开数据源/*96.1.断开与数据

33、源的连接.97.2.释放连接句柄.98.3.释放环境句柄（如果不再需要在这个环境中作更多连接）99.*/100.SQLDisco nn ect(hdbc1);101.SQLFreeHa ndle(SQL_HANDLE_DBC,hdbc1);102.SQLFreeHa ndle(SQL_HANDLE_ENV,he nv);103.return(O);104.创建数据源的过程代码#in clude<stdlib.h>#in lcude<stdio.h>#in clude<wi ndows.h>#in clude<sql.h>#in clude<

34、sqlext.h>#in clude<Sqltypes.h>#defi ne SNO_LEN 30#defi ne NAME_LEN 50#defi ne DEPART_LEN 100#define SSEX_LEN 5int main()/*Step 1定义句柄和变量*/以king开头的表示的是连接 KingbaseES的变量/以server开头的表示的是连接 SQLSERVER的变量SQLHENVkin ghe nv,serverhe nv;SQLHDBCkin ghdbc,serverhdbc;SQLHSTMTkin ghstmt,serverhstmt;SQLRETU

35、RNret;SQLCHARsNameNAME_LEN,sDepartDEPART_LEN,sSexSSEX_LEN,sS no SNO_LEN;SQLINTEGER sAge;SQLINTEGRR cbAge=O,cbS no=SQL_NTS,cbSex=SQL_NTS,cbName=SQL_NTS,cbDepart=SQL_NTS;/*Step 2初始化环境*/ret=SQLAIIocHa ndle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&ki nghe nv);ret=SQLAIIocHa ndle(SQL_HANDLE_ENV,SQL_NULL_HANDL

36、E, &setverhe nv); ret=SQLSetE nvAttr(ki nghe nv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0); ret=SQLSetE nvAttr(serverhe nv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3, 0);/*Step 3建立连接*/ret=SQLAllocHa ndle(SQL_HANDLE_DBC,ki nghe nv,& ki nghdbc);ret=SQLAllocHa ndle(SQL_HANDLE_DBC,serverhe nv,&

37、amp; serverhdbc);ret=SQLCo nn ect(k in ghdbc,"Ki ngbaseESODBC",SQL_NTS,"SYSTEM",SQL_NTS,"MANAGER",SQL_NTS);if(!SQL_SUCCEEDED(ret)return -1;/连接失败时返回错误值；ret=SQLCo nn ect(serverhdbc,"SQLServer",SQL_NTS,"sa",SQL_NTS,"sa",SQL_NTS);if(!SQL_SUCCEE

38、DED(ret)return -1;/连接失败时返回错误值；/*Step 4初始化语句句柄*/ret=SQLAllocHa ndle(SQL_HANDLE_STMT,ki nghdbc,&ki nghstmt); ret=SQLSetStmtAttr(k in ghstmt,SQL_ATTR_ROW_BIND_TYPE(SQLPOINTER)SQL_BIND_BY_COLUMN,SQL_IS_INTEGER);ret=SQLAllocHa ndle(SQL_HANDLE_STMT,serverhdbc, &serverhstmt);/*Step 5两种方式执行语句*/*预编译带

39、有参数的语句 */ret=SQLPrepare(sercerhstmt,"INSERTINTO STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(?,?,?,?,?)",SQL_NTS);if(ret=SQL_SUCCESS|ret=SQL_SUCCESS_WITH_INFO)ret=SQLBi ndParameter(serverhstmt,1,SQL_PARAMN PUT,SQL_C_CHAR,SQL_CHAR,SNO_LEN,0,sS no,0, &cbSNO);ret=SQLBi ndParameter(serverhstmt

40、,2,SQL_PARAMN PUT,SQL_C_CHAR,SQL_CHAR,SNAME_LEN,O,sName,O,&cbName);ret=SQLBi ndParameter(serverhstmt,3,SQL_PARAMN PUT,SQL_C_CHAR,SQL_CHAR,2,0,sSex,0,&cbSex);ret=SQLBi ndParameter(serverhstmt,4,SQL_PARAMN PUT,SQL_C_LONG,SQL_INTEGER,0,0, &sAge,0,&cbAge);ret=SQLBi ndParameter(serverhstm

41、t,5,SQL_PARAMN PUT,SQL_C_CHAR,SQL_CHAR,DEPART_LEN,0,sDepart,0,&cbDepart);/*执行SQL语句*/ret=SQLExecDirect(kinghstmt,"SELECT* FROM STUDENT",SQL_NTS);if(ret=SQL_SUCCESS|ret=SQL_SUCCESS_WITH_INFO)ret=SQLBi ndCol(k in ghstmt,1,SQL_C_CHAR,sS no,SNO_LEN,&cbSno);ret=SQLBi ndCol(ki nghstmt,2,S

42、QL_C_CHAR,sName,NAME_LEN, &cbName);ret=SQLB in dCol(ki nghstmt,3,SQL_C_CHAR,sSex,SSEX_LEN,&cbSex);ret=SQLBi ndCol(kin ghstmt,4,SQL_C_L0NG, &sAge,0,&cbAge);ret=SQLB in dCol(ki nghstmt,5,SQL_C_CHAR,sDepart,DEPART_LEN, &cbDepart);/*Step 6处理结果集并执行预编译后的语句*/while(ret=SQLFetch(ki nghstm

43、t)!=SQL_NO_DATA_FOUND)if(ret=SQL_ERROR)prin tf("Fetcherror'n");else ret=SQLExecute(serverhstmt);/*Step 7中止处理*/SQLFreeHa ndle(SQL_HANDLE_STMT,k in ghstmt);SQLDisco nn ect(ki nghdbc);SQLFreeHa ndle(SQL_HANDLE_DBC,ki nghdbc);SQLFreeHa ndle(SQL_HANDLE_ENV,ki nghe nv);SQLFreeHa ndle(SQL_HANDLE_STMT,serverhstmt);SQLDisc onn ect(serverhdbc);SQLFreeHa nlde(SQL_HANDLE_DBC<serverhdbc);SQLFreeHa nl de(SQL_HANDLE_ENV,serverhe nv);return 0;感谢下载!欢迎您的下载，资料仅供参考