C Language +odbc+sql Connection

First step: Configure ODBC.

①, locate ODBC in the Control Panel, or search for ODBC on the Control Panel.

②, click on the ODBC Add button, select SQL Server, this is a new data source to create SQL Server dialog box, I name Phonesql name, select the SQL login server, then click Next, select Login method: Network login, user input login, Optional (I choose user Input Login to: User name sa password 123456 for example).

③, select the database test (I built the test in SQL), and then always click Next. Last point test data source. Finally the test is displayed successfully.

So ODBC is established, and the next task is to connect to the database.

Step two: Build the database tables and add data.

    CREATE TABLE [dbo]. [Worker] (    [num] [int] NOT NULL,    [name] [Char] (a) NOT null,    [sex] [char] (TEN) NOT null,    [age] [ INT] NOT NULL,    [Shenfennum] [char] (+) NOT null, [Xueli] [char] (TEN) NOT null, [Mianmao] [char] (TEN) not NULL,[Mima] [char] (a) NOT null, [Quanxian] [char] (TEN) NOT null, [born] [char] (NO) null) on [PRIM ARY]

Step Three: Use VC + + to access the database.
①, referencing header file

#include <stdio.h>  #include <windows.h>  #include <sql.h>  #include <sqlext.h >  #include <sqltypes.h>  

Explanation: 1. Sqlinteger defines an integer variable that is equivalent to an int in the C language.

2. SQLCHAR defines a character variable that is equivalent to char in the C language.
3. Ret=sqlconnect (HDBC, (sqlchar*) "phonesql", Sql_nts, (sqlchar*) "sa", Sql_nts, (sqlchar*) " 123456", sql_nts);//Connect to Database

                               Phonesql is the ODBC data source established, SA is the username, 123456 is the password
        The code is as follows:

#include <stdio.h> #include <windows.h> #include <sql.h> #include <sqlext.h> #include < sqltypes.h> #include <string.h>sqlinteger num1,age1; SQLCHAR NAME1[20],SEX1[10],SHENFENNUM1[20],XUELI1[10],MIANMAO1[10],MIMA1[20],QUANXIAN1[10],BORN1[20]; Sqlinteger Len_num1,len_name1,len_sex1,len_shenfennum1,len_xueli1,len_age1,len_mianmao1,len_born1,len_mima1,len  _quanxian1;void Fuzhi () {sqlreturn ret; Sqlhenv henv;//sqlhandle henv sqlhdbc hdbc;//sqlhandle hdbc sqlhstmt hstmt;//sqlhandle hstmt ret=SQLAllocHandle (SQL_HAN DLE_ENV,NULL,&AMP;HENV);//Application Environment handle RET=SQLSETENVATTR (Henv,sql_attr_odbc_version, (sqlpointer) SQL_OV_ODBC3,SQL_IS_ INTEGER);//Set Environment Properties Ret=sqlallochandle (SQL_HANDLE_DBC,HENV,&AMP;HDBC);//Request database connection handle Ret=sqlconnect (HDBC, (sqlchar*) " Phonesql ", Sql_nts, (sqlchar*)" sa ", Sql_nts, (sqlchar*)" 123456 ", sql_nts);//Connect database if (ret==sql_success | | ret==sql_ Success_with_info) {ret=sqlallochandle (sql_handle_stmt,hdbc,&hstmt);//apply SQL statement handle SQLCHAR sql[]= "select *from WorkER "; ret=sqlexecdirect (hstmt,sql,sql_nts);//Execute SQL statement directly if (ret==sql_success | | ret==sql_success_with_info) {int i=0; while (SQLFetch (hstmt)!=sql_no_data) {//Traversal result set/***************************************** database integer assignment ****************** /sqlgetdata (HSTMT,1,SQL_C_ULONG,&AMP;NUM1,0,&AMP;LEN_NUM1);/************************************* /sqlgetdata (HSTMT,2,SQL_C_CHAR,NAME1,20,&AMP;LEN_NAME1); SQLGetData (HSTMT,3,SQL_C_CHAR,SEX1,10,&AMP;LEN_SEX1); SQLGetData (HSTMT,4,SQL_C_ULONG,&AMP;AGE1,0,&AMP;LEN_AGE1); SQLGetData (HSTMT,5,SQL_C_CHAR,SHENFENNUM1,20,&AMP;LEN_SHENFENNUM1); SQLGetData (HSTMT,6,SQL_C_CHAR,XUELI1,10,&AMP;LEN_XUELI1); SQLGetData (HSTMT,7,SQL_C_CHAR,MIANMAO1,10,&AMP;LEN_MIANMAO1); SQLGetData (HSTMT,8,SQL_C_CHAR,MIMA1,20,&AMP;LEN_MIMA1); SQLGetData (HSTMT,9,SQL_C_CHAR,QUANXIAN1,10,&AMP;LEN_QUANXIAN1); SQLGetData (HSTMT,10,SQL_C_CHAR,BORN1,10,&AMP;LEN_BORN1); printf ("%d%s%s%d%s '%s%s%s%s%s\n", Num1,name1,sex1,age1,shenfennum1,xueli1,miANMAO1,MIMA1,QUANXIAN1,BORN1);}  Sqlsmallint Number_column; Ret=sqlnumresultcols (hstmt,&number_column);//query result set number of columns if (ret==sql_success | | ret==sql_success_with_info) printf  ("result set%d columns \ n", number_column); else printf ("The number of query result set columns failed!")  \ n ");  Sqlinteger Number_row; Ret=sqlrowcount (Hstmt,&number_row);//Query the number of rows affected if (ret==sql_success | | ret==sql_success_with_info) printf ("Result set common  %d records \ n ", Number_row); else printf ("The number of query result set records failed!")  \ n "); Sqlfreehandle (sql_handle_stmt,hstmt);//release statement handle}else printf ("Query database operation failed!  \ n ");     SQLDisconnect (HDBC);//Disconnect the connection to the database} else printf ("Connection database failed!\n"); Sqlfreehandle (SQL_HANDLE_DBC,HDBC);//release connection handle Sqlfreehandle (sql_handle_env,henv);//release environment handle}/**********************  /int Main () {Fuzhi (); return 0;   }

The fourth step: assign the obtained data to the structure in C language, make the operation data more convenient (also can directly use through ODBC change data, personal feel Trouble).
     ①, define the structure body.

struct Worker{int num;//work number char name[20];//name char sex[10];//sex int age;//age char shenfennum[20];//ID number char xueli[10];// Education Char mianmao[10];//political face char mima[20];//password char quanxian[10];//permission set char born[20];//Birth date}gong[1000];

②, assign value.

                gong[i].num=num1;gong[i].age=age1;strcpy ((char *) gong[i].name, (char *) name1); strcpy ((char *) gong[i].sex, (char *) SEX1); strcpy ((char *) gong[i].shenfennum, (char *) shenfennum1), strcpy ((char *) Gong[i].xueli, (char *) xueli1); strcpy (( char *) Gong[i].mianmao, (char *) mianmao1), strcpy ((char *) Gong[i].mima, (char *) MIMA1); strcpy ((char *) Gong[i].quanxian , (char *) quanxian1); strcpy ((char *) Gong[i].born, (char *) BORN1);

Note: The char data type in SQL is filled with the appropriate space when the content is insufficient for its own length. Therefore, when the value is assigned, the space symbol will be assigned to the variable inside, resulting in incorrect post processing data. When SQL builds a table: modifies the char type to a varchar type, or you can write a function to remove the extra whitespace. The code is as follows:

void Trim (char *str) {int i,j,k; char tmpstr[maxstrsize];//Can be dynamically assigned, here with static general conditions can meet I=strlen (str); for (k=j=0;j<i;j++ {if (str[j]!= ') {tmpstr[k]=str[j];k++;}} tmpstr[k]= ' + '; strcpy (STR,TMPSTR); }

The overall code is as follows:

#include <stdio.h> #include <windows.h> #include <sql.h> #include <sqlext.h> #include < sqltypes.h> #include <string.h> #define Maxstrsize Sqlinteger num1,age1; SQLCHAR NAME1[20],SEX1[10],SHENFENNUM1[20],XUELI1[10],MIANMAO1[10],MIMA1[20],QUANXIAN1[10],BORN1[20]; Sqlinteger Len_num1,len_name1,len_sex1,len_shenfennum1,len_xueli1,len_age1,len_mianmao1,len_born1,len_mima1,len _quanxian1;struct worker{int num;//Work number char name[20];//name char sex[10];//sex int age;//age char shenfennum[20];//ID number Char xueli[10];//Education Char mianmao[10];//political face char mima[20];//password char quanxian[10];//permission set char born[20];//Birth date}gong[1000]; void Trim (char *str); void Fuzhi () {sqlreturn ret; Sqlhenv henv;//sqlhandle henv sqlhdbc hdbc;//sqlhandle hdbc sqlhstmt hstmt;//sqlhandle hstmt ret=SQLAllocHandle (SQL_ HANDLE_ENV,NULL,&AMP;HENV);//Application Environment handle RET=SQLSETENVATTR (Henv,sql_attr_odbc_version, (sqlpointer) SQL_OV_ODBC3,SQL_ Is_integer);//Set Environment Properties Ret=sqlallochandle (SQL_HANDLE_DBC,HENV,&AMP;HDBC);//Request database connection handle Ret=sqlConnect (HDBC, (sqlchar*) "Phonesql", Sql_nts, (sqlchar*) "sa", Sql_nts, (sqlchar*) "123456", sql_nts);//Connect database if (ret== sql_success | | Ret==sql_success_with_info) {ret=sqlallochandle (sql_handle_stmt,hdbc,&hstmt);//apply SQL statement handle SQLCHAR sql[]= " Select *from worker "; Ret=sqlexecdirect (hstmt,sql,sql_nts);//Execute SQL statement directly if (ret==sql_success | | ret==sql_success_with_ INFO) {int i=0;while (SQLFetch (hstmt)!=sql_no_data) {//Traverse result set/***************************************** Database integer Assignment * * * * /sqlgetdata (HSTMT,1,SQL_C_ULONG,&AMP;NUM1,0,&AMP;LEN_NUM1);/************************ /sqlgetdata (Hstmt,2,sql_c_char,name1,20,&len_ NAME1); SQLGetData (HSTMT,3,SQL_C_CHAR,SEX1,10,&AMP;LEN_SEX1); SQLGetData (HSTMT,4,SQL_C_ULONG,&AMP;AGE1,0,&AMP;LEN_AGE1); SQLGetData (HSTMT,5,SQL_C_CHAR,SHENFENNUM1,20,&AMP;LEN_SHENFENNUM1); SQLGetData (HSTMT,6,SQL_C_CHAR,XUELI1,10,&AMP;LEN_XUELI1); SQLGetData (HSTMT,7,SQL_C_CHAR,MIANMAO1,10,&AMP;LEN_MIANMAO1); SQLGetData (HSTMT,8,SQL_C_CHAR,MIMA1,20,&AMP;LEN_MIMA1); SQLGetData (HSTMT,9,SQL_C_CHAR,QUANXIAN1,10,&AMP;LEN_QUANXIAN1); SQLGetData (HSTMT,10,SQL_C_CHAR,BORN1,10,&AMP;LEN_BORN1); gong[i].num=num1;gong[i].age=age1;strcpy ((char *) gong[i].name, (char *) name1); strcpy ((char *) gong[i].sex, (char *) SEX1); strcpy ((char *) gong[i].shenfennum, (char *) shenfennum1), strcpy ((char *) Gong[i].xueli, (char *) xueli1); strcpy (( char *) Gong[i].mianmao, (char *) mianmao1), strcpy ((char *) Gong[i].mima, (char *) MIMA1); strcpy ((char *) Gong[i].quanxian , (char *) quanxian1), strcpy ((char *) Gong[i].born, (char *) born1); trim (gong[i].name);//delete the database assignment to the space character in the string trim (Gong[i] . Sex), Trim (gong[i].shenfennum), Trim (Gong[i].xueli), Trim (Gong[i].mianmao), trim (Gong[i].mima); Trim (Gong[i]. Quanxian); Trim (Gong[i].born);p rintf ("\n*********************** structure data test%d data *****************************\n", i+ 1);p rintf ("\n\n**************************************************************************\n");p rintf ("Work No.:%d Name: %s Gender:%s ID number:%s \ n Education:%s ", gong[i].num,gong[i].naMe,gong[i].sex,gong[i].shenfennum,gong[i].xueli);p rintf ("Political face:%s password%s, permissions:%s Birth date%s", Gong[i].mianmao,gong[i].mima , Gong[i].quanxian,gong[i].born);p rintf ("Age:%d years \ n", Gong[i].age);p rintf ("************************************* \ n "); i++;} Sqlsmallint Number_column; Ret=sqlnumresultcols (hstmt,&number_column);//query result set number of columns if (ret==sql_success | | ret==sql_success_with_info) printf ("result set%d columns \ n", number_column); else printf ("The number of query result set columns failed!") \ n "); Sqlinteger Number_row; Ret=sqlrowcount (Hstmt,&number_row);//Query the number of rows affected if (ret==sql_success | | ret==sql_success_with_info) printf ("Result set common %d records \ n ", Number_row); else printf ("The number of query result set records failed!") \ n "); Sqlfreehandle (sql_handle_stmt,hstmt);//release statement handle}else printf ("Query database operation failed! \ n "); SQLDisconnect (HDBC);//Disconnect the connection to the database} else printf ("Connection database failed!\n"); Sqlfreehandle (SQL_HANDLE_DBC,HDBC);//release connection handle Sqlfreehandle (sql_handle_env,henv);//release environment handle}void trim (char *str) {int I , J,k; Char Tmpstr[maxstrsize]; Can be dynamically allocated, here with static general conditions can meet I=strlen (str); for (K=J=0;J&Lt;i;j++) {if (str[j]!= ') {tmpstr[k]=str[j];k++;}} tmpstr[k]= ' + '; strcpy (STR,TMPSTR); }/********************************************************/int Main () {Fuzhi (); return 0;}

　　

The results appear as follows:

C Language +odbc+sql Connection