[C Language] Use ODBC to connect to the Microsoft SQL Server database

Because I recently used C to connect to SQL Server, but I found that there is not much information on the Internet, so I collected the information and experiences I found over the past two days and kept it for myself.

Use the C language to connect the SQL server through ODBC (Open Database interconnection), which can be divided into two steps: 1. Configure the local ODBC environment; 2. Code ...... = _ =

[Configure the local environment first]
1. Start sqlserver service, for example, hnhj, Start Menu-> Run-> Net start MSSQLServer

2. Change the SQL Server logon mode to SQL Server Authentication login.

Step: Go to your database, right-click on the server, and choose Properties> Security> SQL Server and Windows Authentication mode. Click OK.

3. Open the Enterprise Manager, create a database, and create a table in the database.

My database name is CCCs. Create a city table

4. Create a system DSN. Choose Start> RUN> odbcad32.

Click "add"-> "SQL Server"-> "data source name" (remember it by name. It will be useful later. My name is CCCs)-> select "SQL Server" (select the local name instead of "local) -> use the user's logon ID and password for SQL Server Authentication-> logon ID: SA, password: (null)-> change the default database to: CCCS-> test data source, the test is successful, that is, the DNS is successfully added.

[Key functions of C language]

1. sqlbindcol () functionThere are six parameters:

SQLRETURN SQLBindCol(SQLHSTMT            StatementHandle,SQLUSMALLINT     ColumnNumber,SQLSMALLINT       TargetType,SQLPOINTER        TargetValuePtr,SQLINTEGER        BufferLength,SQLLEN *              StrLen_or_Ind);

The first parameter is the handle,The second parameter is the number of columns in the target table.(Unsigned short), the third is the target type, and the fourth is the string variable that stores the database feedback information (city, latitude, etc,The fifth parameter is the length of the fourth parameter.(We recommend that you use strlen (string) to test the length.) The sixth is what the buffer zone is. Set it to 0.

2.Sqlexecdirect () functionThere are three parameters:

SQLRETURN SQLExecDirect(     SQLHSTMT     StatementHandle,     SQLCHAR *    StatementText,     SQLINTEGER   TextLength);

The first parameter is the handle, the second parameter is the string variable of the SQL statement stored in the database, and the third function is the length of the second parameter (strlen (string) is recommended) length of test ).

[Source code]

The header file functions. H is attached after the main code CCCS-insert.cpp and CCCS-select.cpp

CCCS-insert.cpp]

# Include "functions. H "sqlhenv henv = SQL _null_henv; sqlhdbc hdbc1 = SQL _null_hdbc; sqlhstmt hstmt1 = SQL _null_hstmt;/* CPP file Function Description: 1. adding, modifying, and deleting database operations are mainly reflected in SQL statements. 2. direct execution and parameter pre-compilation */INT main () {retcode; uchar szdsn [SQL _max_dsn_length + 1] = "CCCS "; // database name ucharszuid [maxname] = "sa"; // username ucharszauthstr [maxname] = ""; // password charsql [60] = "\ 0 "; // The SQL statement used for inserting the variable charsqlh1 [26] = "insert into C Ity values ('"; // concatenation string charsqlh2 [4] ="', '"; charsqlh3 [3] = "')"; // uchar pre_ SQL [31] = "insert into city values (?,?,?) "; // Pre-compiled SQL statement citymsg * citymsg; // city information // SQL statement // 1. connect to the data source // 1. environment handle retcode = sqlallochandle (SQL _handle_env, null, & henv); retcode = sqlsetenvattr (henv, marker, (sqlpointer) SQL _ov_odbc3, SQL _is_integer); // 2. connection handle retcode = sqlallochandle (SQL _handle_dbc, henv, & hdbc1); retcode = sqlconnect (hdbc1, szdsn, 4, szuid, 2, szauthstr, 0 ); // determine whether the connection is successful if (retcode! = SQL _success) & (retcode! = SQL _success_with_info) {printf ("connection failed! \ N ");} else {// 2. create and execute one or more SQL statements/* 1. allocate a statement handle (statement handle) 2. create SQL statement 3. execute Statement 4. destroy statement */retcode = sqlallochandle (SQL _handle_stmt, hdbc1, & hstmt1); // The first method // directly execute // Add operation // open the file citymsg = getcitymsg (); citymsg = citymsg-> next; while (citymsg-> next! = NULL) {// concatenate string strcpy (SQL, sqlh1); strcat (SQL, citymsg-> City); strcat (SQL, sqlh2); strcat (SQL, citymsg-> LAT); strcat (SQL, sqlh2); strcat (SQL, citymsg-> Lon); strcat (SQL, sqlh3 ); // execute the SQL statement // sqlexecdirect (hstmt1, (uchar *) SQL, 50); // test switch (sqlexecdirect (hstmt1, (uchar *) SQL, strlen (SQL) {Case SQL _success_with_info: {printf ("SQL _success_with_info \ n"); break;} case SQL _success: {printf ("SQL _success \ n"); break;} Case SQL _error: {printf ("SQL _error \ n"); break;} default: printf ("else return \ n ");} // printf ("% s \ n", SQL) after the test ends; // test // re-initialize the SQL statement to store the variable strcpy (SQL, sqlh1 ); // The linked list points to the next node citymsg = citymsg-> next;} // The second method // The parameter binding method/* char a [200] = "BBB "; char B [200] = "200"; char C [200] = "200"; sqlinteger P = SQL _cnt; // 1 pre-compiled sqlprepare (hstmt1, pre_ SQL, 31 ); // The third parameter is the same as the array size, instead of the same database column. // 2 bind the parameter value sqlbindparameter (hstmt1, 1, SQL _param_input, SQL _c_char, SQL _char, & A, 0, & P); sqlbindparameter (hstmt1, 2, SQL _param_input, SQL _c_char, SQL _char, & B, 0, & P ); sqlbindparameter (hstmt1, 2, SQL _param_input, SQL _c_char, SQL _char, & C, 0, & P); // 3. Execute sqlexecute (hstmt1); */printf ("operation successful! "); // Release statement handle sqlclosecursor (hstmt1); sqlfreehandle (SQL _handle_stmt, hstmt1);} // 3. disconnect the data source/* 1. disconnect from the data source. 2. release the connection handle. 3. release the environment handle (if more connections are no longer needed in this environment) */sqldisconnect (hdbc1); sqlfreehandle (SQL _handle_dbc, hdbc1); sqlfreehandle (SQL _handle_env, henv); getchar (); return 0 ;}

CCCS-select.cpp]

/* China city coord systemselect */# include "functions. H "// define the query method macro # defineselect_all0 # define >_|||# define # define henv = SQL _null_henv; sqlhdbc hdbc1 = SQL _null_hdbc; sqlhstmt hstmt1 = bytes; /*************************************** ********************* * ********************************/void selectall (Retcode, char * SQL, char * sqlh1, char * initialize) {/* 1. check whether a result set is available. 2. Bind the columns of the result set to appropriate variables. 3. get the line */citymsg; getchar (); // The carriage return character stored on the choose interface to prevent the output of the number of lines strcpy (SQL, sqlh1) twice before the first page turning ); // concatenate the SQL statement string sqlexecdirect (hstmt1, (uchar *) SQL, strlen (SQL); // send the Select All statement to the database // sqlbindcol (hstmt1, 1, SQL _c_char, list, 5, 0); // This function sends the statement sqlbindcol (hstmt1, 1, SQL _c_char, citymsg to the database. city, 10, 0); // This function is the database feedback information function sqlbindcol (hstmt1, 2, SQL _c_char, citymsg. lon, 11, 0); // The second parameter is sqlbindcol (hstmt1, 3, SQL _c_char, Citymsg. lat, 11, 0); // The Fifth parameter is the length of the returned string do {retcode = sqlfetch (hstmt1); If (retcode = SQL _no_data) {break ;} printf ("% S % s \ n", citymsg. city, citymsg. lon, citymsg. LAT); static int n = 1; // flip counter n ++; If (N % 20 = 0) // 20 lines per page {printf ("\ n [Page % d]", N/20); // page number getchar (); // press enter to change pages} while (1); strcpy (SQL, initialize); // reinitialize the string getchar (); // end flag, press ENTER }/*********************************** *************** * ***************************************/ Void selectbylat (char * sqlh1, char * SQL, char * initialize) {char sqlh5 [10] = "\ 0"; citymsg; getchar (); printf ("Enter the latitude to query: \ n "); printf (" Example: 34.17 \ n "); printf (" north latitude "); gets (sqlh5); strcpy (SQL, sqlh1); strcat (SQL, "where latitude = 'North latitude"); strcat (SQL, sqlh5); strcat (SQL, "'"); puts (SQL ); // display the SQL statements sqlexecdirect (hstmt1, (uchar *) SQL, strlen (SQL); sqlbindcol (hstmt1, 1, SQL _c _ Char, citymsg. city, 10, 0); // This function is the database feedback information function sqlbindcol (hstmt1, 2, SQL _c_char, citymsg. lon, 11, 0); // The second parameter is the column number sqlbindcol (hstmt1, 3, SQL _c_char, citymsg in the target table. lat, 11, 0); // The Fifth parameter is the length of the returned string sqlfetch (hstmt1); printf ("% S % s \ n", citymsg. city, citymsg. lon, citymsg. LAT); strcpy (SQL, initialize); // reinitialize the string getchar (); // end mark, press ENTER}/* to query sqlserver database, 1. conditional query, 2. directly query all */INT main () {retcode; uchar szdsn [SQL _max_dsn _ Length + 1] = "CCCS"; // database name ucharszuid [maxname] = "sa"; // user name ucharszauthstr [maxname] = ""; // password charsql [57] = "\ 0"; // The storage variable charinitialize [2] = "\ 0" of the SQL statement used for insertion "; // initialization variable charsqlh1 [20] = "select * from city"; // concatenate string retcode = sqlallochandle (SQL _handle_env, null, & henv); retcode = sqlsetenvattr (henv, SQL _attr_odbc_version, (sqlpointer) SQL _ov_odbc3, SQL _is_integer); retcode = sqlallochandle (SQL _handle_dbc, Henv, & hdbc1); // 1. Connect to the data source retcode = sqlconnect (hdbc1, szdsn, 4, szuid, 2, szauthstr, 0); If (retcode! = SQL _success) & (retcode! = SQL _success_with_info) {printf ("connection failed! ");} Else {// 2. create and execute one or more SQL statements/* 1. allocate a statement handle (statement handle) 2. create SQL statement 3. execute Statement 4. destroy statement */retcode = sqlallochandle (SQL _handle_stmt, hdbc1, & hstmt1); // The first method/* // directly execute sqlexecdirect (hstmt1, (uchar *) SQL, strlen (SQL); char list [5]; sqlbindcol (hstmt1, 1, SQL _c_char, list, 5, 0); sqlfetch (hstmt1 ); printf ("% s \ n", list); * // method 2/* // bind the parameter method char a [200] = "AAA "; sqlinteger P = SQL _cnt; // 1. pre-compiled sqlprepare (hstmt1, sql2, 35); // The third parameter is the same as the array size, instead of the same database column. // 2. bind the parameter value sqlbindparameter (hstmt1, 1, SQL _param_input, SQL _c_char, SQL _char, 200,0, & A, 0, & P); // 3. execute sqlexecute (hstmt1); char list [5]; sqlbindcol (hstmt1, 1, SQL _c_char, list, 5, 0); sqlfetch (hstmt1 ); printf ("% s \ n", list); */INT choose; // The storage variable while (1) {printf ("input query method: \ n "); printf (" 0. query all \ n "); printf (" 1. exact latitude QUERY \ n "); printf (" 2. latitude range query \ n "); printf (" \ n "); scanf (" % d ", & choose); Switch (choose) {Case select_all: {// all outputs selectall (retcode, SQL, sqlh1, initialize); break;} case select_lat_exact: {// query selectbylat (sqlh1, SQL, initialize) by specific latitude ); break;} case select_lat_scope: {// query by latitude range }}// release statement handle sqlclosecursor (hstmt1); sqlfreehandle (SQL _handle_stmt, hstmt1);} // 4. disconnect the data source/* 1. disconnect from the data source. 2. release the connection handle. 3. release the environment handle (if more connections are no longer needed in this environment) */sqldisconnect (hdbc1); sqlfreehandle (SQL _handle_dbc, hdbc1); sqlfreehandle (SQL _handle_env, henv); getchar (); getchar (); Return (0 );}

[Header file functions. H]

# Include <stdio. h> # include <string. h> # include <windows. h> # include <SQL. h> # include <sqlext. h> # include <sqltypes. h> # include <odbcss. h> // Type Def struct citymsg {charcity [50]; // city citycharlon [50]; // longitude longitudecharlat [50]; // latitude latitudestruct citymsg * Next; // next node} citymsg; // function declaration citymsg * nextnood (citymsg *); // construct the linked list function citymsg * getcitymsg (); // obtain the city and coordinate functions // construct the linked list citymsg * nextnood (citymsg * oldcm) {// Tail plug method citymsg * newcm; newcm = (citymsg *) malloc (sizeof (citymsg); // create a space for the new node newcm-> next = NULL; // initialize the new node oldcm-> next = newcm; return newcm; // return the new node} // obtain the city and coordinates citymsg * getcitymsg () {citymsg * head, // header pointer * citymsg; // file * FP, which stores the city and coordinates; // file pointer-> head = (citymsg *), which stores the city and coordinates *) malloc (sizeof (citymsg); // create a space for the header pointer citymsg = (citymsg *) malloc (sizeof (citymsg )); // The first node opens the space head-> next = citymsg; // initialize the header pointer citymsg-> next = N Ull; // initialize the first node fp = fopen ("citycoord.txt", "RT"); // try to open the file if (FP = NULL) {// if it cannot be opened, feedback Information and exit printf ("cannot open this file, press any key to exit. \ n "); getchar (); exit (1) ;}while (! Feof (FP) {// read City and coordinate information cyclically until fscanf (FP, "% S % s", citymsg-> city, citymsg-> Lon, citymsg-> LAT); // read the city and coordinate citymsg = nextnood (citymsg); // open and direct to the next node} return head ;}

[References]

C language and SQL Server database

How to change the SQL Server 2008 login verification method

Sqlexecdirect function-SQL Server msdn