数据库 数据库编程二

//头文件
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <errno.h>
#include <mysql/mysql.h>
#include <termios.h>

#ifdef __cplusplus
extern "C"
{
#endif
//设置退格键不回显
int set_Backkey();

//获取用户SQL语句，判断用户操作，不区分大小写
void read_user_print(MYSQL *pmysql,MYSQL *connection);

#ifdef __cplusplus
}
#endif

//辅助方法实现
#include "pub.h"

//设置退格键不回显
int set_Backkey()
{
    struct termios term;
    memset(&term, 0, sizeof(term));
    /*获取当前系统的termios设置*/
    if (tcgetattr(STDIN_FILENO, &term) == -1)
    {
        printf("tcgetattr() failed! error message:%s\n", strerror(errno));
        return -1;
    }
    /*设置tremios的擦除字符功能为退格键*/
    term.c_cc[VERASE] = '\b';
    if (tcsetattr(STDIN_FILENO, TCSANOW, &term) == -1)
    {
        printf("tcsetattr() failed! error message:%s\n", strerror(errno));
        return -1;
    }
    return 0;
}

//获取用户SQL语句，判断用户操作，不区分大小写
void read_user_print(MYSQL *pmysql, MYSQL *connection)
{
    if (connection == NULL || pmysql == NULL)
    {
        printf("read_user_print() params not correct!\n");
        return;
    }
    /*等待用户控制台输入*/
    //获取数据库名称
    char buf_mysql[100] = { 0 };
    strcpy(buf_mysql, "mysql1>");
    int i = 0,j = 0;
    char temp_buf[1024] = { 0 };
    while (1)
    {
        /*显示mysql1>*/
        write(STDOUT_FILENO, buf_mysql, sizeof(buf_mysql));
        /*等待用户输入*/
        char buf_read[1024] = { 0 };
        read(STDIN_FILENO, buf_read, sizeof(buf_read));
        /*执行用户SQL*/
        /*判断用户输入的是查询操作，还是更新操作，
         * 更新操作有 update,delete,use,set,insert
         * 注意不区分大小写匹配
         * 将用户所有输入都转化为大写，遇到空格结束
         * */
        memset(temp_buf,0,sizeof(temp_buf));
        strcpy(temp_buf, buf_read);
        for (i=0; i < strlen(temp_buf); i++)
        {
            //如果当前字符不是空格
            if (temp_buf[i] != ' ')
            {
                if (temp_buf[i] > 96 && temp_buf[i] < 123)
                {
                    /*小写转化成大写*/
                    temp_buf[i] = temp_buf[i] - 32;
                }
            } else
            {
                break;
            }
        }
        int flag = 0;
        if (strncmp(temp_buf, "UPDATE", 6) == 0)
        {
            flag = 1;
        } else if (strncmp(temp_buf, "DELETE", 6) == 0)
        {
            flag = 1;
        } else if (strncmp(temp_buf, "USE", 3) == 0)
        {
            flag = 1;
        } else if (strncmp(temp_buf, "SET", 3) == 0)
        {
            flag = 1;
        } else if (strncmp(temp_buf, "INSERT", 6) == 0)
        {
            flag = 1;
        }
        //执行mysql_query()
        if (mysql_query(connection, buf_read) != 0)
        {
            printf("SQL error:%s\n", mysql_error(pmysql));
            break;
        }
        if (flag)
        {
            printf("SQL 更新成功！\n");
        } else
        {
            //执行查询语句
            MYSQL_RES *result = mysql_store_result(connection);
            if (result == NULL)
            {
                printf("mysql_store_result() failed !\n");
                break;
            }
            MYSQL_ROW row;
            MYSQL_FIELD *field;
            /*展示列数据*/
            int numx = 0;
            while (1)
            {
                field = mysql_fetch_field(result);
                if (field == NULL)
                    break;
                printf("%s\t", field->name);
                numx++;
            }
            printf("\n");
            while (1)
            {
                row = mysql_fetch_row(result);
                if (row == NULL)
                    break;
                /*打印一行中每一列的数据*/
                for (j=0; j < numx; j++)
                {
                    printf("%s\t", row[j]);
                }
                /*每行换行*/
                printf("\n");
            }
            //释放查询结果集
            mysql_free_result(result);
        }
        printf("\n");
    }
}

//mysql客户端实现
#include "pub.h"

int main(int arg, char *args[])
{
    //mysql -h 192.168.1.101 -u dbuser1 -p
    if (arg < 4)
    {
        printf("please print three params!\n");
        return -1;
    }
    char hostname[100] = { 0 };
    char username[100] = { 0 };
    char passwd[100] = { 0 };
    //char dbname[100] = { 0 };
    if (strncmp(args[1], "-h", 2) == 0)
    {
        strcpy(hostname, args[1]);
        strcpy(username, args[4]);
    } else if (strncmp(args[1], "-u", 2) == 0)
    {
        strcpy(hostname, "localhost");
        strcpy(username, args[2]);
    } else
    {
        printf("error print!");
        return -1;
    }
    //获取密码
    strcpy(passwd, getpass("Enter password: "));
    //设置退格键不回显
    if (set_Backkey() < 0)
    {
        printf("设置退格键不回显失败！\n");
    }
    //创建mysql connection
    MYSQL mysql, *connection=NULL;
    //init mysql
    mysql_init(&mysql);
    //connect mysql server
    connection = mysql_real_connect(&mysql, hostname, username, passwd, "", 0,
            0, 0);
    if (connection == NULL)
    {
        printf("mysql_real_connect() failed ! error message:%s\n",
                mysql_error(&mysql));
        return -1;
    }
    /*用户输入*/
    read_user_print(&mysql,connection);
    //close sql server
    mysql_close(connection);
    return 0;
}

.SUFFIXES:.c .o
CC=gcc
SRCS=tec01.c\
    pub.c
OBJS=$(SRCS:.c=.o)
EXEC=runsql

start:$(OBJS)
    $(CC) -o $(EXEC) $(OBJS) -lmysqlclient
    @echo "-------OK----------"
.c.o:
    $(CC) -Wall -g -o $@ -c $<
clean:
    rm -f $(OBJS)
    rm -f $(EXEC)