73.笔记 MySQL学习——C编写MySQL程序六交互式语句执行
本节实现一个简单的交互式语句执行客户端程序。
在connect2.c代码上进行加工。
代码如下：
l exec_stmt.c
#include <my_global.h>
#include <my_sys.h>
#include <m_string.h> /* for strdup() */
#include <mysql.h>
#include <my_getopt.h>
static char *opt_host_name = NULL; /* server host (default=localhost) */
static char *opt_user_name = NULL; /* username (default=login name) */
static char *opt_password = NULL; /* password (default=none) */
static unsigned int opt_port_num = 0; /* port number(use built-in value) */
static char *opt_socket_name = NULL; /* socket name (use built-in value) */
static char *opt_db_name = NULL; /* database name (default=none) */
static unsigned int opt_flags = 0; /* connection flags (none) */
static int ask_password = 0; /* whether to solicit password */
static MYSQL *conn; /* pointer to connectionhandler */
static const char *client_groups[] = {"client", NULL };
static struct my_option my_opts[] = /* option information structures */
{
{"help", '?', "Display this help and exit",
NULL, NULL,NULL,
GET_NO_ARG,NO_ARG, 0, 0, 0, 0, 0, 0},
{"host", 'h', "Host to connect to",
(uchar **)&opt_host_name, NULL, NULL,
GET_STR,REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"password", 'p', "Password",
(uchar **)&opt_password, NULL, NULL,
GET_STR,OPT_ARG, 0, 0, 0, 0, 0, 0},
{"port", 'P', "Port number",
(uchar **)&opt_port_num, NULL, NULL,
GET_UINT,REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"socket", 'S', "Socket path",
(uchar **)&opt_socket_name, NULL, NULL,
GET_STR,REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"user", 'u', "User name",
(uchar **)&opt_user_name, NULL, NULL,
GET_STR,REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{ NULL, 0,NULL, NULL, NULL, NULL, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0 }
};
/*
* Printdiagnostic message. If conn is non-NULL, print error information
* returned byserver.
*/
static void
print_error (MYSQL *conn, char *message)
{
fprintf(stderr, "%s\n", message);
if (conn !=NULL)
{
fprintf(stderr, "Error %u (%s): %s\n",
mysql_errno (conn), mysql_sqlstate (conn), mysql_error (conn));
}
}
static my_bool
get_one_option (int optid, const struct my_option*opt, char *argument)
{
switch(optid)
{
case '?':
my_print_help (my_opts); /* printhelp message */
exit (0);
case'p': /* password */
if(!argument) /* no value given;solicit it later */
ask_password = 1;
else /* copy password,overwrite original */
{
opt_password = strdup (argument);
if(opt_password == NULL)
{
print_error (NULL, "could not allocate password buffer");
exit(1);
}
while(*argument)
*argument++ = 'x';
ask_password = 0;
}
break;
}
return (0);
}
#include "process_result_set.c"
#include "process_statement.c"
int
main (int argc, char *argv[])
{
int opt_err;
MY_INIT(argv[0]);
load_defaults("my", client_groups, &argc, &argv);
if ((opt_err= handle_options (&argc, &argv, my_opts, get_one_option)))
exit(opt_err);
/* solicitpassword if necessary */
if(ask_password)
opt_password = get_tty_password (NULL);
/* getdatabase name if present on command line */
if (argc >0)
{
opt_db_name= argv[0];
--argc;++argv;
}
/* initializeclient library */
if(mysql_library_init (0, NULL, NULL))
{
print_error(NULL, "mysql_library_init() failed");
exit (1);
}
/* initializeconnection handler */
conn =mysql_init (NULL);
if (conn ==NULL)
{
print_error(NULL, "mysql_init() failed (probably out of memory)");
exit (1);
}
/* connect toserver */
if(mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,
opt_db_name, opt_port_num, opt_socket_name, opt_flags) == NULL)
{
print_error(conn, "mysql_real_connect() failed");
mysql_close(conn);
exit (1);
}
/* #@ _MAIN_LOOP_ */
while (1)
{
char buf[10000];
fprintf(stderr, "query> "); /* print prompt */
if (fgets(buf, sizeof (buf), stdin) == NULL) /* read statement */
break;
if (strcmp(buf, "quit\n") == 0 || strcmp (buf, "\\q\n") == 0)
break;
process_statement (conn, buf); /* execute it */
}
/* #@ _MAIN_LOOP_ */
/* disconnectfrom server, terminate client library */
mysql_close(conn);
mysql_library_end ();
exit (0);
}
l process_result_set.c
void
print_dashes (MYSQL_RES *res_set)
{
MYSQL_FIELD *field;
unsigned int i, j;
mysql_field_seek (res_set, 0);
fputc ('+',stdout);
for (i = 0; i< mysql_num_fields (res_set); i++)
{
field =mysql_fetch_field (res_set);
for (j = 0;j < field->max_length + 2; j++)
fputc('-', stdout);
fputc ('+',stdout);
}
fputc ('\n',stdout);
}
void
process_result_set (MYSQL *conn, MYSQL_RES *res_set)
{
MYSQL_ROW row;
/* #@ _COL_WID_CALC_VARS_ */
MYSQL_FIELD *field;
unsigned long col_len;
unsigned int i;
/* #@ _COL_WID_CALC_VARS_ */
/* #@ _COL_WID_CALCULATIONS_ */
/* determinecolumn display widths; requires result set to be */
/* generatedwith mysql_store_result(), not mysql_use_result() */
mysql_field_seek (res_set, 0);
for (i = 0; i< mysql_num_fields (res_set); i++)
{
field =mysql_fetch_field (res_set);
col_len =strlen (field->name);
if (col_len< field->max_length)
col_len =field->max_length;
if (col_len< 4 && !IS_NOT_NULL (field->flags))
col_len =4; /* 4 = length of the word"NULL" */
field->max_length = col_len; /* reset column info */
}
/* #@ _COL_WID_CALCULATIONS_ */
print_dashes(res_set);
fputc ('|',stdout);
mysql_field_seek (res_set, 0);
for (i = 0; i< mysql_num_fields (res_set); i++)
{
field =mysql_fetch_field (res_set);
/* #@ _PRINT_TITLE_ */
printf(" %-*s |", (int) field->max_length, field->name);
/* #@ _PRINT_TITLE_ */
}
fputc ('\n',stdout);
print_dashes(res_set);
while ((row =mysql_fetch_row (res_set)) != NULL)
{
mysql_field_seek (res_set, 0);
fputc ('|',stdout);
for (i = 0;i < mysql_num_fields (res_set); i++)
{
field =mysql_fetch_field (res_set);
/* #@ _PRINT_ROW_VAL_ */
if(row[i] == NULL) /* print the word"NULL" */
printf(" %-*s |", (int) field->max_length, "NULL");
else if(IS_NUM (field->type)) /* print valueright-justified */
printf(" %*s |", (int) field->max_length, row[i]);
else /* print value left-justified */
printf(" %-*s |", (int) field->max_length, row[i]);
/* #@ _PRINT_ROW_VAL_ */
}
fputc('\n', stdout);
}
print_dashes(res_set);
printf("Number of rows returned: %lu\n",
(unsigned long) mysql_num_rows (res_set));
}
l process_statement.c
void
process_statement (MYSQL *conn, char *stmt_str)
{
MYSQL_RES *res_set;
if(mysql_query (conn, stmt_str) != 0) /*the statement failed */
{
print_error(conn, "Could not execute statement");
return;
}
/* thestatement succeeded; determine whether it returned data */
res_set =mysql_store_result (conn);
if(res_set) /* a result set wasreturned */
{
/* processrows and free the result set */
process_result_set (conn, res_set);
mysql_free_result (res_set);
}
else /* no result set was returned */
{
/*
* does thelack of a result set mean that the statement didn't
* returnone, or that it should have but an error occurred?
*/
if(mysql_field_count (conn) == 0)
{
/*
*statement generated no result set (it was not a SELECT,
* SHOW,DESCRIBE, etc.); just report rows-affected value.
*/
printf("Number of rows affected: %lu\n",
(unsigned long) mysql_affected_rows (conn));
}
else /* an error occurred */
{
print_error (conn, "Could not retrieve result set");
}
}
}
代码解释
总共3个文件，如上。
exec_stmt.c调用另外两个文件，如下：
#include "process_result_set.c"
#include "process_statement.c"
在主循环中，其他部件主要辅助的小TIPS，主要的是那个WHILE循环。
在WHILE循环中一直输出
Query>
接受输入后进行执行。主要是调用process_statement.c文件中的
process_statement函数，该函数调用mysql_query函数，使用mysql_store_result处理结果集。
process_statement函数会调用process_result_set.c文件中的process_result_set函数来处理结果输出。
执行
# ./a.out -uroot
query> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
Number of rows returned: 4
query> use test;
Number of rows affected: 0
query> show tables;
+----------------+
| Tables_in_test |
+----------------+
+----------------+
Number of rows returned: 0