C + + connect to SQL Server database through ODBC and add, delete, query and modify operations [book borrowing system as an example]

C + + connect to SQL Server database through ODBC and add, delete, query and modify operations [book borrowing system as an example]

Article catalog

preface

1, How to configure ODBC

2, How to set account and password for SQL Server

3, Code implementation of C + + connection to database and addition, deletion, query and modification operation

4, Source code

preface

Sophomore studied the principle and application of database and designed the course at the end of the semester. Because I was a freshman in C + +, I decided to write in C + +. Taking the book borrowing system as an example, the system is not very complete, but it can reflect how C + + adds, deletes, checks and modifies the database SQL Server. The following is my learning experience, which is only for learning reference.

1, How to configure ODBC

How to configure ODBC

2, How to set account and password for SQL Server

3, Code implementation of C + + connection to database and addition, deletion, query and modification operation

1. Programming environment: Visual Studio 2019

2. Basic principles

Everyone must have done C + + curriculum design. The data of C + + curriculum design is generally stored in the form of files. The course design of database stores data in the way of database. Using C++ code to implement the operation of the database is actually typing a SQL code (stored in string) in the C++ visual (visual studio), then calling the specific function to execute the SQL code, and finally returning the result to visual studio. The specific steps are as follows:

1. Step 1: apply for a handle.

ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

2. Step 2: define and splice the SQL statements you want to execute (take inserting operation code as an example). If there is a need for manual input, it is defined separately, and finally spliced into a complete SQL statement before execution. For example, if I want to insert a book, but I need to manually input the number, name and quantity of the book, then I need to define the number, name and quantity string of the cin Book separately. Finally, it is spliced into a complete SQL statement: insert into Books values ('001 ',' computer network ', 10).

    string str1 = "use JYXT";//The first SQL statement to execute
    string str2 = "insert into Books values ('";//The second SQL statement to execute
    cout << "Please enter the book number, book name and book quantity in turn" << endl;
    string Bno, Bname, Bsum;
    cin >> Bno >> Bname >> Bsum;
    string str3=str2 + Bno + "','" + Bname + "'," + Bsum + ")";

3. Step 3: call the function to execute the SQL statement (take inserting operation code as an example). If ret returns 0 or 1, the statement is executed successfully; otherwise, the execution fails. The reason for the failure may be that your SQL statement is wrong (remember to execute the sentence "use [database] first). It is recommended that you execute SQL Server first, and then splice it in visual studio. Note that it is an English symbol!!!

    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);//Execute the SQL statement str1 use JYXT
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);//Execute the SQL statement str3 insert into Books values ('Bno','Bname',Bsum)
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        cout << "Books on the shelf successfully!" << endl;
    }
    else {
        cout << "Failed to put books on the shelf!" << endl;
    }

4. Step 4: return the result after the SQL statement is executed (take the query operation code as an example). For example, if you want to query the basic information of a book, after calling the SQL statement, you need to store the data and then print it in the black box. The specific details are in the code comments (pay attention to the regular formation).

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLCHAR str1[10], str2[12], str3[10];//It is used to store the column information obtained from the database. You define several variables as long as you have several columns
        SQLINTEGER len_str1, len_str2, len_str3;//The string corresponds to the length. You can define several variables as long as you have several columns
        //printf("%s\t%s\t%s\n", "book number", "book name", "quantity");
        cout << "number" << "    " << "Book name" << "       " << "Number of books" << endl;
        while (SQLFetch(hstmt) != SQL_NO_DATA)
        {
            //You can call SQLGetData function several times for several columns. Pay attention to finding rules
            SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 10, &len_str1);   //Get the first column of data
            SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 12, &len_str2);   //Get the second column of data
            SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 10, &len_str3);   //Get the third column of data
            printf("%s\t%s\t%s\n", str1,str2, str3);
        }
    }

3. Definition of header file and global variable

#undef UNICODE
#undef _UNICODE
#include<Windows.h>
#include <sql.h> 
#include <sqlext.h> 
#include<sqltypes.h>
#include <iostream>
#include<string>
#include <iomanip>
using namespace std;

//Define global variables
SQLRETURN ret = NULL;//Return information
SQLHENV henv = NULL;//Environment Handles 
SQLHDBC hdbc = NULL;//connection handle
SQLHSTMT hstmt = NULL;//Statement Handle 

4. Database connection function

The specific code is as follows:

void connect()//Database connection function
{
    SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);//Application environment
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);//Setting environment
    SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);//Request database connection
    ret = SQLConnect(hdbc, (SQLTCHAR*)("SQL SERVER"), SQL_NTS, (SQLTCHAR*)("sa"), SQL_NTS, (SQLTCHAR*)("72580."), SQL_NTS);
    if ((ret == SQL_SUCCESS) || (ret == SQL_SUCCESS_WITH_INFO))
    {
        cout << "Database connection succeeded!" << endl;
    }
    else
    {
        cout << "Database connection failed!" << endl;
    }
}

You need to modify several parameters of SQLConnect function:

1. "SQL SERVER" should be changed to the ODBC data source name you set. If you don't know what the ODBC data source name is, see my article on configuring ODBC for details.

2."sa" is changed to your SQL Server login.

3. Change "72580." to your SQL Server login password.

5. Insert

Here, take the above shelf Books as an example, that is, insert a record in the table Books. The specific implementation code is as follows:

void insert_book()
{
    // sql statement: insert into Books values ('001 ',' computer network ', 10)
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";//The first SQL statement to execute
    string str2 = "insert into Books values ('";//The second SQL statement to execute
    cout << "Please enter the book number, book name and book quantity in turn" << endl;
    string Bno, Bname, Bsum;
    cin >> Bno >> Bname >> Bsum;
    string str3=str2 + Bno + "','" + Bname + "'," + Bsum + ")";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        cout << "Books on the shelf successfully!" << endl;
    }
    else {
        cout << "Failed to put books on the shelf!" << endl;
    }
    free();
}

6. Delete

Take the following shelf Books as an example, that is, delete a record in the table Books. The specific implementation code is as follows:

void delete_book()
{
    //sql statement: delete Books where Bname = 'computer network'
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";
    string str2 = "delete Books where Bname='";
    cout << "Please enter the book name of the lower shelf" << endl;
    string Bname;
    cin >> Bname;
    string str3 = str2 + Bname + "'";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        cout << "Books off the shelf successfully!" << endl;
    }
    else {
        cout << "Books off the shelf failed!" << endl;
    }
    free();
}

7. Modify (update)

Take book borrowing as an example. The specific library borrowing function code is as follows:

void borrow_book()
{
    cout << "Please enter your student number" << endl;
    string Sno;
    cin >> Sno;
    cout << "Please enter the name of the book you want to borrow" << endl;
    string Bname;
    cin >> Bname;
    if (getbsum(Bname) > 0)//In order to judge whether books can be borrowed, a function getbsum(string bname) that can obtain the number of books bsum is customized here
    {
        ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
        string str1 = "use JYXT";
        string str2 = "update Books set Bsum-=1 where Bname='";
        string str3 = str2 + Bname + "'";
        string str4 = "declare @a char(6) select @a=Bno from Books where Bname= '";
        string str5 = str4 + Bname + "' insert into Record values ('" + Sno + "','Borrow books',getdate(),@a)";
        ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
        ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
        ret = SQLExecDirect(hstmt, (SQLCHAR*)str5.c_str(), SQL_NTS);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
            cout << "Book borrowing succeeded!" << endl;
        else
            cout << "Book borrowing failed!" << endl;
    }
    else
        cout << "This book is out of stock and cannot be borrowed" << endl;
    free();
}

8. Query (select)

Here, take finding book information as an example, and the specific implementation code is as follows:

void query_book()
{
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";
    string str2 = "select *from Books where Bname='";
    cout << "Please enter the title of the book" << endl;
    string Bname;
    cin >> Bname;
    string str3 = str2 + Bname + "'";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLCHAR str1[10], str2[12], str3[10];//It is used to store the column information obtained from the database. You define several variables as long as you have several columns
        SQLINTEGER len_str1, len_str2, len_str3;//The string corresponds to the length. You can define several variables as long as you have several columns
        //printf("%s\t%s\t%s\n", "book number", "book name", "quantity");
        cout << "number" << "    " << "Book name" << "       " << "Number of books" << endl;
        while (SQLFetch(hstmt) != SQL_NO_DATA)
        {
            //You can call SQLGetData function several times for several columns. Pay attention to finding rules
            SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 10, &len_str1);   //Get the first column of data
            SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 12, &len_str2);   //Get the second column of data
            SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 10, &len_str3);   //Get the third column of data
            printf("%s\t%s\t%s\n", str1,str2, str3);
        }
    }
    free();
}

4, Source code

#undef UNICODE
#undef _UNICODE
#include<Windows.h>
#include <sql.h> 
#include <sqlext.h> 
#include<sqltypes.h>
#include <iostream>
#include<string>
#include <iomanip>
using namespace std;

//Define global variables
SQLRETURN ret = NULL;//Return information
SQLHENV henv = NULL;//Environment Handles 
SQLHDBC hdbc = NULL;//connection handle
SQLHSTMT hstmt = NULL;//Statement Handle 

//Free up space
void free()
{
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);//Release statement
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//Release connection
    SQLFreeHandle(SQL_HANDLE_ENV, henv);//Release environment
}

//Connect to database
void connect()//Database connection function
{
    SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);//Application environment
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);//Setting environment
    SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);//Request database connection
    ret = SQLConnect(hdbc, (SQLTCHAR*)("SQL SERVER"), SQL_NTS, (SQLTCHAR*)("sa"), SQL_NTS, (SQLTCHAR*)("72580."), SQL_NTS);
    if ((ret == SQL_SUCCESS) || (ret == SQL_SUCCESS_WITH_INFO))
    {
        cout << "Database connection succeeded!" << endl;
    }
    else
    {
        cout << "Database connection failed!" << endl;
    }
}

//Number of books obtained
int getbsum(string bname)
{
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string sql = "use JYXT";
    string sql2 = "select bsum from Books where Bname='";
    string str = sql2 + bname + "'";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)sql.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        int a=0;
        string str1;
        SQLINTEGER len_str1;
        while (SQLFetch(hstmt) != SQL_NO_DATA)
        {
            SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLCHAR*)str1.c_str(), 10, &len_str1);   //Get the first column of data
            a = atoi(str1.c_str());
        }
        free();
        return a;
    }
}

//Book borrowing (deleted)
void borrow_book()
{
    cout << "Please enter your student number" << endl;
    string Sno;
    cin >> Sno;
    cout << "Please enter the name of the book you want to borrow" << endl;
    string Bname;
    cin >> Bname;
    if (getbsum(Bname) > 0)
    {
        ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
        string str1 = "use JYXT";
        string str2 = "update Books set Bsum-=1 where Bname='";
        string str3 = str2 + Bname + "'";
        string str4 = "declare @a char(6) select @a=Bno from Books where Bname= '";
        string str5 = str4 + Bname + "' insert into Record values ('" + Sno + "','Borrow books',getdate(),@a)";
        ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
        ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
        ret = SQLExecDirect(hstmt, (SQLCHAR*)str5.c_str(), SQL_NTS);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
            cout << "Book borrowing succeeded!" << endl;
        else
            cout << "Book borrowing failed!" << endl;
    }
    else
        cout << "This book is out of stock and cannot be borrowed" << endl;
    free();
}

//Book return (increase)
void return_book()
{
    cout << "Please enter your student number" << endl;
    string Sno;
    cin >> Sno;
    cout << "Please enter the name of the book you want to return" << endl;
    string Bname;
    cin >> Bname;
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";
    string str2 = "update Books set Bsum+=1 where Bname='";
    string str3 = str2 + Bname + "'";
    string str4 = "declare @a char(6) select @a=Bno from Books where Bname= '";
    string str5 = str4 + Bname + "' insert into Record values ('" + Sno + "','Return the book',getdate(),@a)";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str5.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        cout << "Return the book successfully!" << endl;
    else
        cout << "Failed to return the book!" << endl;
    free();
}

//Query student borrowing information (search)
void print_br()
{
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";
    string str2 = "SELECT*FROM Record where Sno='";
    cout << "Please enter student number" << endl;
    string Sno;
    cin >> Sno;
    string str3 = str2 + Sno + "'";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLCHAR str1[10], str2[12], str3[20], str4[20];
        SQLINTEGER len_str1, len_str2, len_str3, len_str4;
        //printf("%s\t%s\t%s\n", "book number", "book name", "quantity");
        cout << "Student number" << "    " << "operation" << "    " << "Operation time" << "                Book number"<<endl;
        while (SQLFetch(hstmt) != SQL_NO_DATA)
        {
            SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 10, &len_str1);   //Get the first column of data
            SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 12, &len_str2);
            SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 20, &len_str3);
            SQLGetData(hstmt, 4, SQL_C_CHAR, str4, 20, &len_str4);
            printf("%s\t%s\t%s\t%s\n", str1, str2, str3, str4);
        }
    }
    free();
}

//Query book information (find)
void query_book()
{
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";
    string str2 = "select *from Books where Bname='";
    cout << "Please enter the title of the book" << endl;
    string Bname;
    cin >> Bname;
    string str3 = str2 + Bname + "'";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLCHAR str1[10], str2[12], str3[10];
        SQLINTEGER len_str1, len_str2, len_str3;
        //printf("%s\t%s\t%s\n", "book number", "book name", "quantity");
        cout << "number" << "    " << "Book name" << "       " << "Number of books" << endl;
        while (SQLFetch(hstmt) != SQL_NO_DATA)
        {
            SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 10, &len_str1);   //Get the first column of data
            SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 12, &len_str2);
            SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 10, &len_str3);

            printf("%s\t%s\t%s\n", str1,str2, str3);
        }
    }
    free();
}

//Books on shelves
void insert_book()
{
    //insert into Books values ('006 ',' computer network ', 10)
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";
    string str2 = "insert into Books values ('";
    cout << "Please enter the book number, book name and book quantity in turn" << endl;
    string Bno, Bname, Bsum;
    cin >> Bno >> Bname >> Bsum;
    string str3=str2 + Bno + "','" + Bname + "'," + Bsum + ")";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        cout << "Books on the shelf successfully!" << endl;
    }
    else {
        cout << "Failed to put books on the shelf!" << endl;
    }
    free();
}

//Off shelf books
void delete_book()
{
    //delete Books where Bname = 'computer network'
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//Request handle
    string str1 = "use JYXT";
    string str2 = "delete Books where Bname='";
    cout << "Please enter the book name of the lower shelf" << endl;
    string Bname;
    cin >> Bname;
    string str3 = str2 + Bname + "'";
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
    ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        cout << "Books off the shelf successfully!" << endl;
    }
    else {
        cout << "Books off the shelf failed!" << endl;
    }
    free();
}

int main()
{
    return 0;
}

Tags: C++ Database SQL Server SQL

Posted on Wed, 08 Sep 2021 18:02:49 -0400 by quicknik