<> use c Language pair sql server Add, delete, modify and query
as everyone knows , since sql server
2000 in the future , No longer support c Embedded language operation of language , Therefore, it is necessary to use odbc Connect to operate . When I finished the experiment, I couldn't find a more complete operation code , Took a lot of detours , Here I share with you some of my summary .
environment
windows 10
sql server 2019
vs2019
be careful ： Please use a multi byte character set
* ODBC connect
I will not repeat this part in detail , It's easy to search in the forum .
* Connect database #include<stdio.h> #include<windows.h> #include<sql.h> #include
<sqlext.h> #include<sqltypes.h> void Connect(SQLRETURN ret, SQLHENV& henv,
SQLHDBC& hdbc, SQLHSTMT& hstmt)// Handle to set connection parameters { const char* SY1 = "DOST"; unsigned
char* SY = (unsigned char*)SY1; const char* db21 = "sa";// database ODBC Connected account unsigned
char* db2 = (unsigned char*)db21; const char* pass1 = "12345678";// database ODBC Password for connection
unsigned char* pass = (unsigned char*)pass1; ret = SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE, &henv);// Request environment handle ret = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, SQL_IS_INTEGER); ret =
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);// Request database connection handle ret = SQLConnect(hdbc,
SY, SQL_NTS, db2, SQL_NTS, pass, SQL_NTS); /*db2 The configuration is ODBC Data source name , Here, you can modify it according to your own configuration */ if
(!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)) { printf(" Failed to connect to database !\n");
return; } ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);// Used to allocate handles }
3. Add function
void insert(SQLRETURN ret, SQLHENV& henv, SQLHDBC& hdbc, SQLHSTMT& hstmt) { int
i= 0,j = 0,k = 0; unsigned char tablename[60]; do { printf(" Please enter the serial number of the table you want to insert data into \n")
; printf("1. Student list S 2. Class Schedule Card C 3. Student transcript SC\n"); scanf("%d", &i); switch (i) { case 1:
strcpy((char*)tablename, "S(sclass,sno,sname,ssex,Sdept,sage) values
(??????)");printf(
" Please according to S(sclass,sno,sname,ssex,Sdept,sage) Input data in sequence , Carriage return or space between data \n"); break;
// It's obvious that dynamic is used here sql Way of , The place of the question mark is the place we need to enter later case 2:strcpy((char*)tablename, "C
values (????)"); printf(" Please according to C(cno,cname,cpno,ccredit) Input data in sequence , Carriage return or space between data \n");
break; case 3:strcpy((char*)tablename, "SC values (????)"); printf(
" Please according to SC(sclass,sno,cno,grade) Input data in sequence , Carriage return or space between data \n"); break; default:printf(
" Input error , Re-enter \n"); } } while (i < 1 || i>3); char s[5][10], s1[10] = "", s2[10] =
"", s3[10] = "", s4[10] = "", s5[10] = ""; int num; /*long conlumnlen;*/
SQLAllocStmt(hdbc, &hstmt); SQLCHAR sql2[100] = "insert into ";
// it is to be noted that ,sqlchar Actually unsigned char, This cannot be operated directly in string operation statements , Need to be forced to char Type before operation strcat((
char*)sql2, (char*)tablename); //printf("%s\n", sql2); SQLPrepare(hstmt, (
SQLCHAR*)sql2, strlen((char*)sql2));// This is right sql Statement proceed prepare operation , This step helps prevent injection , Improve security if (i
== 1) j = 5; else if (i == 2|| i==3) j = 3; for (k = 0; k < j; k++) { scanf("%s"
, s[k]); SQLBindParameter(hstmt, k+1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10,
0, &s[k], 100, NULL);
//sqlbindparameter The function operation is used to bind the position of the question mark just now , be careful , Upper “k+1” The value of is the question mark sql Position in statement , The order must be consistent , And if sql Modify after binding , The binding here will go wrong .
} scanf("%d", &num); SQLBindParameter(hstmt, k+1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 8, 0, &num, sizeof(int), NULL); SQLExecute(hstmt); if (ret ==
SQL_SUCCESS|| ret == SQL_SUCCESS_WITH_INFO) { printf(" Operation successful !\n"); } else { UCHAR
errmsg[100]; printf(" operation failed !\n"); SQLError(henv, hdbc, hstmt, NULL, NULL, errmsg,
sizeof(errmsg), NULL); printf("%s", errmsg); } }
4. Delete function
void deleteD(SQLRETURN ret, SQLHENV& henv, SQLHDBC& hdbc, SQLHSTMT& hstmt) {
int i = 0; unsigned char tablename[60]; do { printf(" Please enter the serial number of the table you want to delete data \n"); printf
("1. Student list S 2. Class Schedule Card C 3. Student transcript SC\n"); scanf("%d", &i); switch (i) { case 1:strcpy((char
*)tablename, "S where sclass = ? and sno= ? "); printf(" Please enter class number and student number , Space between \n");
break; case 2:strcpy((char*)tablename, "C where cno= ? "); printf(" Please enter the course number \n");
break; case 3:strcpy((char*)tablename, "SC where sclass =? and sno=? and cno=? "
); printf(" Please enter class number, student number and course number , Space between \n"); break; default:printf(" Input error , Re-enter \n"); } }
while (i < 1 || i>3); unsigned char s[3][10]; SQLCHAR sql[120] = "delete from ";
/*long conlumnlen;*/ SQLAllocStmt(hdbc, &hstmt); strcat((char*)sql, (char*)
tablename); SQLPrepare(hstmt, (SQLCHAR*)sql, strlen((char*)sql)); if (i == 2) {
scanf("%s", s[0]); SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 10, 0, &s[0], 100, NULL); } else if (i == 1) { scanf("%s", s[0]);
scanf("%s", s[1]); SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 10, 0, &s[0], 100, NULL); SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 10, 0, &s[1], 100, NULL); } else if (i == 3) { scanf("%s",
s[0]); scanf("%s", s[1]); scanf("%s", s[2]); SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &s[0], 100, NULL);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &s[1],
100, NULL); SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10
, 0, &s[2], 100, NULL); } SQLExecute(hstmt);//sql implement if (ret == SQL_SUCCESS ||
ret== SQL_SUCCESS_WITH_INFO) { printf(" Operation successful !\n"); } else { UCHAR errmsg[100];
printf(" operation failed !\n"); SQLError(henv, hdbc, hstmt, NULL, NULL, errmsg, sizeof(errmsg
), NULL); printf("%s", errmsg); } }
Obviously, the delete operation is very similar to the add operation just now , It's just sql The sentence is different , Whether it is sql Statement connection ,sqlprepare still sqlexecute The order is basically the same , Dynamic is also used sql Way of .
5. Modify function
void edit(SQLRETURN ret, SQLHENV& henv, SQLHDBC& hdbc, SQLHSTMT& hstmt) { int i
= 0; unsigned char tablename1[60],tablename2[60]; unsigned char s[3][10]; do {
printf(" Please enter the serial number of the table you want to modify the data \n"); printf("1. Student list S 2. Class Schedule Card C 3. Student transcript SC\n"); scanf("%d", &i
); switch (i) { case 1:strcpy((char*)tablename1, "S set "); strcpy((char*)
tablename2, "where sclass=? and sno=?");break; case 2:strcpy((char*)tablename1,
"C set "); strcpy((char*)tablename2, "where cno=? ");break; case 3:strcpy((char*
)tablename1, "SC set "); strcpy((char*)tablename2, "where sclass=? and sno=?
and cno=?"); break; default:printf(" Input error , Re-enter \n"); } } while (i < 1 || i>3);
unsigned char s1[10]; char part[14][10]; strcpy(part[0], "sage=? "); strcpy(part
[1], "sclass=? "); strcpy(part[2], "sno=? "); strcpy(part[3], "sname=? ");
strcpy(part[4], "ssex=? "); strcpy(part[5], "Sdept=? "); strcpy(part[6], "cno=?
"); strcpy(part[7], "cname=? "); strcpy(part[8], "cpno=? "); strcpy(part[9],
"ccredit=? "); strcpy(part[10], "grade=? "); int j = 0,num; do { printf(
" Select the modified element name \n"); printf("1.sage 2.sclass 3.sno 4.sname 5.ssex 6.Sdept 7.cno
8.cname 9.cpno 10.ccredit 11.grade\n"); scanf("%d", &j); } while (j < 1 || j>11)
; SQLCHAR sql[100] = "update "; /*long conlumnlen;*/ SQLAllocStmt(hdbc, &hstmt);
strcat((char*)sql, (char*)tablename1); strcat((char*)sql, part[j-1]); strcat((
char*)sql, (char*)tablename2); printf("%s\n", sql); SQLPrepare(hstmt, (SQLCHAR*)
sql, strlen((char*)sql)); if (i == 2) { printf(" Please enter the course number ："); scanf("%s", s[0]);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &s[0],
100, NULL); } else if (i == 1) { printf(" Please enter the class number ："); scanf("%s", s[0]); scanf(
"%s", s[1]); SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
10, 0, &s[0], 100, NULL); SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR
, SQL_CHAR, 10, 0, &s[1], 100, NULL); } else if (i == 3) { printf(" Please enter class number and course number ："
); scanf("%s", s[0]); scanf("%s", s[1]); scanf("%s", s[2]); SQLBindParameter(
hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &s[0], 100, NULL);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &s[1],
100, NULL); SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10
, 0, &s[2], 100, NULL); } printf(" Enter the modified element value ："); if (j == 1||j==11) { scanf("%d", &
num); SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 8, 0
, &num, sizeof(int), NULL); } else { scanf("%s", s1); SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &s, 100, NULL); } ret= SQLExecute
(hstmt); if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { printf(
" Operation successful !\n"); } else { UCHAR errmsg[100]; printf(" operation failed !\n"); SQLError(henv, hdbc,
hstmt, NULL, NULL, errmsg, sizeof(errmsg), NULL); printf("%s", errmsg); } }
This is also consistent with the method used above .
6. Query function
The query function is divided into precise query and fuzzy query , Dynamic query can be used for precise query sql Way of , But due to dynamic sql of ? After being passed in, it will be directly recognized as an element , Therefore, fuzzy query can only be completed by string connection .
void Search(SQLRETURN ret, SQLHENV& henv, SQLHDBC& hdbc, SQLHSTMT& hstmt) {
SQLAllocStmt(hdbc, &hstmt); unsigned char sno[10], sclass[10]; printf(" Please enter class
Student number ：( Separated by spaces )"); scanf("%s %s", sclass, sno); int i; printf(
" Please enter the function you want to query ：1. Query your student information 2. Check all your grades \n"); scanf("%d", &i); SQLCHAR sql[150]; if(i==
1) strcpy((char*)sql,"select * from S where sclass= ? and sno= ? "); else if(i==
2) strcpy((char*)sql, "select a.sname,b.*,c.cname from S a, SC b, C c where
b.sclass = ? and b.sno= ? and a.sno=b.sno and a.sclass=b.sclass and c.cno=b.cno"
); SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &
sclass, 100, NULL); SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 10, 0, &sno, 100, NULL); char S[6][10]; long conlumnlen; int snum=0;
if (i == 1) { SQLPrepare(hstmt, (SQLCHAR*)sql, strlen((char*)sql)); SQLBindCol(
hstmt, 1, SQL_CHAR, S[0], 10, &conlumnlen); SQLBindCol(hstmt, 2, SQL_CHAR, S[1],
10, &conlumnlen); SQLBindCol(hstmt, 3, SQL_CHAR, S[2], 10, &conlumnlen);
SQLBindCol(hstmt, 4, SQL_CHAR, S[3], 10, &conlumnlen); SQLBindCol(hstmt, 6,
SQL_CHAR, S[4], 10, &conlumnlen); SQLBindCol(hstmt, 5, SQL_INTEGER, &snum,
sizeof(int), &conlumnlen); printf("sclass\tsno\tsname\tssex\tSdept\tsage\n");
SQLExecute(hstmt); } else if(i==2) { SQLPrepare(hstmt, (SQLCHAR*)sql, strlen((
char*)sql)); SQLBindCol(hstmt, 1, SQL_CHAR, S[0], 10, &conlumnlen); SQLBindCol(
hstmt, 2, SQL_CHAR, S[1], 10, &conlumnlen); SQLBindCol(hstmt, 3, SQL_CHAR, S[2],
10, &conlumnlen); SQLBindCol(hstmt, 4, SQL_CHAR, S[3], 10, &conlumnlen);
SQLBindCol(hstmt, 6, SQL_CHAR, S[4], 10, &conlumnlen); SQLBindCol(hstmt, 5,
SQL_INTEGER, &snum, sizeof(int), &conlumnlen); printf(
"sname\tsclass\tsno\tcname\tcno\tgrade\n"); SQLExecute(hstmt); } ret = SQLFetch(
hstmt); if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { printf(
" Operation successful !\n"); while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { if (i
== 1) printf("%s%s%s%s%s%d\n", S[0], S[1], S[2], S[3], S[4], snum); else printf(
"%s%s%s%s%s%d\n", S[0], S[1], S[2], S[4], S[3], snum); ret = SQLFetch(hstmt); }
} else { UCHAR errmsg[100]; printf(" operation failed !\n"); SQLError(henv, hdbc, hstmt, NULL,
NULL, errmsg, sizeof(errmsg), NULL); printf("%s", errmsg); } }
Up there sqlbindcol function , It is used for binding after query c Variables defined in language , Embedded sql The same principle , But the operation is more direct . alike , The binding order also needs to match the columns you query in the database .
This paper focuses on the connection operation of fuzzy query , Due to direct implementation sql Statement mode , The template of fuzzy query is below .
select ··· from ··· like %···%
Therefore, our operation code is ：
char input[100]; char sno[10] = "", sname[10] = "", ssex[4] = "", sage[4] = "",
saddr[20] = ""; printf(" Enter the address to query the corresponding data \n"); scanf("%s", input); long conlumnlen;
SQLAllocStmt(hdbc, &hstmt); SQLCHAR sql2[100] = "select * from dbo.S1 where
SADDR like '%";// Note that there must be no space after the percent sign here , Otherwise, it's almost impossible for you to see the difference strcat((char*)sql2, input);
strcat((char*)sql2, "%'"); SQLPrepare(hstmt, (SQLCHAR*)sql2, strlen((char*)sql2)
); /*ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
100, 0, &input, 100, NULL);*/ SQLBindCol(hstmt, 1, SQL_CHAR, sno, 10, &
conlumnlen); SQLBindCol(hstmt, 2, SQL_CHAR, sname, 10, &conlumnlen); SQLBindCol(
hstmt, 3, SQL_CHAR, ssex, 4, &conlumnlen); SQLBindCol(hstmt, 4, SQL_CHAR, sage,
4, &conlumnlen); SQLBindCol(hstmt, 5, SQL_CHAR, saddr, 20, &conlumnlen);
SQLExecute(hstmt); ret = SQLFetch(hstmt); if (ret == SQL_SUCCESS || ret ==
SQL_SUCCESS_WITH_INFO) { printf(" Operation successful !\n"); while (ret == SQL_SUCCESS || ret ==
SQL_SUCCESS_WITH_INFO) { printf("%s\t%s\t%s\t%s\t%s\n", sno, sname, ssex, sage,
saddr); ret = SQLFetch(hstmt); } } else { UCHAR errmsg[100]; printf(" operation failed !\n");
SQLError(henv, hdbc, hstmt, NULL, NULL, errmsg, sizeof(errmsg), NULL); printf(
"%s", errmsg); } SQLFreeConnect(hdbc); SQLFreeEnv(henv);
Using such a string connection has disadvantages , May be sql Injection attack .sqlfetch Is a cursor , Used to output all results , I believe everyone should have learned , No analysis .
SQLCHAR sql2[100] = "select * from dbo.S1 where SADDR like '%";
// Note that there must be no space after the percent sign here , Otherwise you can hardly see the difference strcat((char*)sql2, input); strcat((char*)
sql2, "%'");
7. summary
Now the language is so rich , Using this underlying function to connect to the database is a little troublesome , So many library functions don't use this . But if it's in school , It is good for future work and study to start to understand in the lower aspects .
8. Reflections
clear as daylight , This article is very loose and casual in terms of language and structure , So if there are any errors , Welcome to leave a message , I'll make changes .
ps： This is my database experiment assignment , If the latecomers have reference , Never go to the same school as me , Miss Zhang Miao will see it , After all, few lazy people even PB I'm too lazy to do it
Technology