C#连接mysql数据库,执行查询报错

发布网友 发布时间：2022-05-02 16:10

我来回答

共5个回答

懂视网 时间：2022-05-02 20:32

 1 /*
 2 C/C++连接MySQL数据库时，需要包含一个*.h的mysql头文件和一个mysql的lib文件
 3 1、初始化；
 4 2、连接数据库；
 5 3、执行sql查询语句；
 6 4、获取查询值；
 7 5、关闭
 8 */
 9 #include <stdio.h>
 10 #include <WinSock.h> 
 11 #include <mysql.h> 
 12 #include <Windows.h>
 13 #pragma comment(lib,"wsock32.lib")
 14 #pragma comment(lib,"libmysql.lib")
 15 
 16 MYSQL mysql;
 17 MYSQL_FIELD *fd; //字段列数组
 18 char field[32][32]; //存字段名二维数组
 19 MYSQL_RES *res; //行的一个查询结果集
 20 MYSQL_ROW column; //数据行的列
 21 char query[150]; //查询语句
 22 
 23 //函数声明
 24 bool ConnectDatabase(); 
 25 void FreeConnect();
 26 bool QueryDatabase(); 
 27 bool InsertData();
 28 bool ModifyData();
 29 bool DeleteData();
 30 
 31 int main(int argc, char **argv){
 32  ConnectDatabase();
 33  QueryDatabase();
 34  InsertData();
 35  QueryDatabase();
 36  ModifyData();
 37  QueryDatabase();
 38 //DeleteData();
 39 //QueryDatabase();
 40  FreeConnect();
 41 system("pause");
 42 return 0;
 43 }
 44 
 45 //连接数据库
 46 bool ConnectDatabase(){
 47 //Gets or initializes a MYSQL structure
 48 mysql_init(&mysql); 
 49 
 50 // Connects to a MySQL server
 51 const char host[] = "localhost";
 52 const char user[] = "root";
 53 const char passwd[] = "root";
 54 const char db[] = "employees";
 55 unsigned int port = 3306;
 56 const char *unix_socket = NULL;
 57 unsigned long client_flag = 0;
 58 
 59 //A MYSQL* connection handler if the connection was successful, 
 60 //NULL if the connection was unsuccessful. For a successful connection, 
 61 //the return value is the same as the value of the first parameter.
 62 if (mysql_real_connect(&mysql, host, user, passwd, db, port, unix_socket, client_flag)){
 63  printf("The connection was successful.
");
 64  return true;
 65  }
 66 else{
 67  //const char *mysql_error(MYSQL *mysql)
 68  //Returns the error message for the most recently invoked MySQL function
 69  //A null-terminated character string that describes the error. 
 70  //An empty string if no error occurred.
 71  printf("Error connecting to database:%s
", mysql_error(&mysql));
 72  return false;
 73  }
 74 }
 75 
 76 //释放资源
 77 //void mysql_free_result(MYSQL_RES *result)
 78 //Frees the memory allocated for a result set by mysql_store_result(), 
 79 //mysql_use_result(), mysql_list_dbs(), and so forth.
 80 //When you are done with a result set, you must free the memory it 
 81 //uses by calling mysql_free_result().
 82 //Do not attempt to access a result set after freeing it.
 83 
 84 //void mysql_close(MYSQL *mysql)
 85 //Closes a previously opened connection.mysql_close() also deallocates 
 86 //the connection handler pointed to by mysql if the handler was allocated automatically 
 87 //by mysql_init() or mysql_connect().
 88 void FreeConnect(){
 89  mysql_free_result(res);
 90 mysql_close(&mysql);
 91 }
 92 
 93 //查询数据
 94 bool QueryDatabase(){
 95 //将数据格式化输出到字符串
 96 sprintf_s(query, "select * from departments");
 97 //设置编码格式
 98 mysql_query(&mysql, "set names gbk"); 
 99 
100 //int mysql_query(MYSQL *mysql, const char *stmt_str)
101 //Executes an SQL query specified as a null-terminated string
102 //Executes the SQL statement pointed to by the null-terminated string stmt_str. 
103 //Normally, the string must consist of a single SQL statement without a terminating semicolon (;) or g. 
104 //If multiple-statement execution has been enabled, the string can contain several statements separated by semicolons.
105 //Return Values:Zero for success.Nonzero if an error occurred.
106 if (mysql_query(&mysql, query)){
107  printf("Query failed (%s)
", mysql_error(&mysql));
108  return false;
109  }
110 else{
111  printf("query success
");
112  }
113 
114 //MYSQL_RES *mysql_store_result(MYSQL *mysql)
115 //Retrieves a complete result set to the client
116 //mysql_store_result() reads the entire result of a query to the client, allocates a MYSQL_RES structure, and places the result into this structure.
117 //mysql_store_result() returns a null pointer if the statement did not return a result set(for example, if it was an INSERT statement).
118 //mysql_store_result() also returns a null pointer if reading of the result set failed.
119 //You can check whether an error occurred by checking whether mysql_error() returns a nonempty string.
120 //Return Values: A MYSQL_RES result structure with the results.NULL(0) if an error occurred.
121 res = mysql_store_result(&mysql);
122 if (!res){
123  printf("Couldn‘t get result from %s
", mysql_error(&mysql));
124  return false;
125  }
126 
127 //my_ulonglong mysql_affected_rows(MYSQL *mysql)
128 //It returns the number of rows changed, deleted, 
129 //or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. 
130 //For SELECT statements, returns the number of rows in the result set.
131 printf("number of dataline returned: %d
", mysql_affected_rows(&mysql));
132 
133 //获取字段的信息
134 //MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
135 //Returns the definition of one column of a result set as a MYSQL_FIELD structure.
136 //Call this function repeatedly to retrieve information about all columns in the result set.
137 
138 // 获取列数
139 int j = mysql_num_fields(res);
140 
141 char *str_field[32]; //存储字段信息
142 
143 //获取字段名
144 for (int i = 0; i < j; i++){ 
145  str_field[i] = mysql_fetch_field(res)->name;
146  }
147 
148 //打印字段
149 for (int i = 0; i < j; i++) 
150  printf("%10s	", str_field[i]);
151 printf("
");
152 
153 //打印查询结果
154 //MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
155 //Fetches the next row from the result set
156 while (column = mysql_fetch_row(res)){
157  printf("%10s	%10s
", column[0], column[1]);
158  }
159 return true;
160 }
161 
162 //插入数据
163 bool InsertData()
164 {
165 sprintf_s(query, "insert into departments values (‘dddd‘, ‘xxxxx‘);");
166 if (mysql_query(&mysql, query)) {
167  printf("Query failed (%s)
", mysql_error(&mysql));
168  return false;
169  }
170 else{
171  printf("Insert success
");
172  return true;
173  }
174 }
175 
176 //修改数据
177 bool ModifyData(){
178 sprintf_s(query, "update departments set dept_name=‘yyyyy‘ where dept_no=‘dddd‘");
179 if (mysql_query(&mysql, query)) {
180  printf("Query failed (%s)
", mysql_error(&mysql));
181  return false;
182  }
183 else{
184  printf("Insert success
");
185  return true;
186  }
187 }
188 
189 //删除数据
190 bool DeleteData()
191 {
192 sprintf_s(query, "delete from departments where dept_no=‘dddd‘;");
193 //char query[100];
194 //printf("please input the sql:
");
195 //gets_s(query); //手动输入sql语句
196 if (mysql_query(&mysql, query)) {
197  printf("Query failed (%s)
", mysql_error(&mysql));
198  return false;
199  }
200 else{
201  printf("Insert success
");
202  return true;
203  }
204 }

C/C++连接MySQL数据库执行查询（对employees进行查询）

标签：字符串   SQL_error   sep   center   get   exe   mat   locate   释放   

热心网友 时间：2022-05-02 17:40

热心网友 时间：2022-05-02 18:58

热心网友 时间：2022-05-02 20:32

热心网友 时间：2022-05-02 22:24