服务器端的查询程序有问题,等深入学习后在回来修改
net.h
#ifndef __NET_H__
#define __NET_H__
#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <strings.h>
#include <sys/types.h>
#include <netinet/in.h>
#include <netinet/ip.h>
#include <errno.h>
#include <stdlib.h>
#include <arpa/inet.h>
#include <pthread.h>
#include <pthread.h>
#include <signal.h>
#include <sys/wait.h>
#include <sqlite3.h>
#define SERV_PORT 5001
#define SERV_IP_ADDR "192.168.26.128"
#define BACKLOG 5
#define QUIT_STR "quit"
#endif
server.c
#include "net.h"
#define DATABASE "student1.db"
#define N 128
int do_insert(sqlite3 *db, int newfd, char *buf)
{
int id;
char name[32] = {};
char sex[6] = {};
int score;
char sql[N] = {};
char *errmsg;
write(newfd, "Input id:",9);
read (newfd, buf, BUFSIZ - 1);
//while(read < 0);
id = atoi(buf);
printf("%d",id);
write(newfd, "Input name:", 11);
read (newfd, buf, BUFSIZ - 1);
strncpy(name,buf,sizeof(buf));
printf("%s", name);
write(newfd, "Input sex:", 10);
read (newfd, buf, BUFSIZ - 1);
strncpy(sex,buf,sizeof(buf));
printf("%s",sex);
write(newfd, "Input score:", 12);
read (newfd, buf, BUFSIZ - 1);
score = atoi(buf);
printf("%d",score);
sprintf(sql, "insert into stu values(%d, '%s', '%s', %d)", id, name, sex, score);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
write(newfd,errmsg,sizeof(errmsg));
write(newfd,"\n", 1);
}
else
{
write(newfd, "Insert done.\n", 13);
}
return 0;
}
int do_delete(sqlite3 *db, int newfd, char *buf)
{
int id;
char sql[N] = {};
char *errmsg;
write(newfd, "Input id:", 9);
read (newfd, buf, BUFSIZ - 1);
id = atoi(buf);
sprintf(sql, "delete from stu where id = %d", id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
write(newfd, "Delete done.\n",13);
}
return 0;
}
int do_update(sqlite3 *db, int newfd, char *buf)
{
int id;
char sql[N] = {};
char name[32] = {};
char *errmsg;
write(newfd, "Input id:",9);
read (newfd, buf, BUFSIZ - 1);
//while(read < 0);
id = atoi(buf);
printf("%d",id);
write(newfd, "Input name:", 11);
read (newfd, buf, BUFSIZ - 1);
strncpy(name,buf,sizeof(buf));
printf("%s", name);
sprintf(sql, "update stu set name='%s' where id=%d", name,id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
write(newfd, "update done.\n",13);
}
return 0;
}
int callback(void *arg, int f_num, char ** f_value, char ** f_name)//FIXME
{
char *buf;
int i = 0;
int newfd = (int)arg;
for(i = 0; i < f_num; i++)
{
// printf("%-8s %s", f_value[i], f_name[i]);
sprintf(buf,"%-8s", f_value[i]);
write(5, buf, sizeof(buf));
}
write(5, "++++++++++++++++++++++",strlen("++++++++++++++++++++++"));
//putchar(10);
write(5, " ", 10);
return 0;
}
int do_query(sqlite3 *db, int newfd, char *buf)//FIXME
{
char *errmsg;
char sql[N] = "select * from stu;";
if(sqlite3_exec(db, sql, callback,(void*)newfd , &errmsg) != SQLITE_OK)
{
printf("%s", errmsg);
}
else
{
write(newfd,"select done.\n",strlen("select done.\n"));
}
}
int do_query1(sqlite3 *db, int newfd, char *buf)
{
char *errmsg;
char ** resultp;
int nrow;
int ncolumn;
//char buf1[32];
if(sqlite3_get_table(db, "select * from stu", &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
{
write(newfd, errmsg,sizeof(errmsg));
write(newfd, "\n",1);
return -1;
}
else
{
write(newfd,"query done.\n",strlen("query done.\n"));
}
int i = 0;
int j = 0;
int index = ncolumn;
for(j = 0; j < ncolumn; j++)
{
sprintf(buf,"%-10s ", resultp[j]);
write(newfd,buf,1);
}
//putchar(10);
for(i = 0; i < nrow; i++)
{
for(j = 0; j < ncolumn; j++)
{
sprintf(buf,"%-10s ", resultp[index++]);
write(newfd,buf,1);
}
//putchar(10);
}
return 0;
}
void sig_child_handle(int signo)
{
if(SIGCHLD == signo) {
waitpid(-1, NULL, WNOHANG);
}
}
void cli_data_handle (void *arg, sqlite3 *db)
{
int newfd = *(int *) arg;
printf ("Child handling process: newfd =%d\n", newfd);
//..和newfd进行数据读写
int ret = -1;
char buf[BUFSIZ];
while (1) {
write(newfd, "********************************************\n1: insert 2:query 3:delete 4:update 5:quit\n********************************************\nPlease select:", strlen("********************************************\n1: insert 2:query 3:delete 4:update 5:quit\n********************************************\nPlease select:"));
bzero (buf, BUFSIZ);
do {
ret = read (newfd, buf, BUFSIZ - 1);
} while (ret < 0 && EINTR == errno);
if (ret < 0) {
perror ("read");
exit (1);
}
if (!ret) { //对方已经关闭
break;
}
printf ("Receive data: %s\n", buf);
//write(buf1);
switch(atoi(buf))
{
case 1:
do_insert(db, newfd, buf);
break;
case 2:
do_query(db, newfd, buf);//FIXME
//do_query1(db, newfd, buf);
break;
case 3:
do_delete(db, newfd, buf);
break;
case 4:
do_update(db, newfd, buf);
break;
case 5:
printf("main exit.\n");
sqlite3_close(db);
exit(0);
break;
default :
write(newfd, "Invalid data n.\n", strlen("Invalid data n.\n"));
}
/*if (!strncasecmp (buf, QUIT_STR, strlen (QUIT_STR))) { //用户输入了quit字符
printf ("Client(fd=%d) is exiting!\n", newfd);
break;
}*/
}
close (newfd);
}
int main(int argc, const char *argv[])
{
sqlite3 *db;
char *errmsg;//errmsg是一个二级指针,定义一个一级指针取地址。
int n;
if(sqlite3_open(DATABASE, &db) != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(db));
return -1;
}
else
{
printf("open DATABASE success.\n");
}
if(sqlite3_exec(db, "create table if not exists stu(id int, name char , sex char , score int);",NULL, NULL, &errmsg) != SQLITE_OK)//分号可加可不加
{
printf("%s\n", errmsg);
}
else
{
printf("Create or open table success.\n");
}
int fd = -1;
struct sockaddr_in sin;
signal(SIGCHLD, sig_child_handle);
/* 1. 创建socket fd */
if ((fd = socket (AF_INET, SOCK_STREAM, 0)) < 0) {
perror ("socket");
exit (1);
}
/*优化4: 允许绑定地址快速重用 */
int b_reuse = 1;
setsockopt (fd, SOL_SOCKET, SO_REUSEADDR, &b_reuse, sizeof (int));
/*2. 绑定 */
/*2.1 填充struct sockaddr_in结构体变量 */
bzero (&sin, sizeof (sin));
sin.sin_family = AF_INET;
sin.sin_port = htons (SERV_PORT); //网络字节序的端口号
/*优化1: 让服务器程序能绑定在任意的IP上 */
#if 1
sin.sin_addr.s_addr = htonl (INADDR_ANY);
#else
if (inet_pton (AF_INET, SERV_IP_ADDR, (void *) &sin.sin_addr) != 1) {
perror ("inet_pton");
exit (1);
}
#endif
/*2.2 绑定 */
if (bind (fd, (struct sockaddr *) &sin, sizeof (sin)) < 0) {
perror ("bind");
exit (1);
}
/*3. 调用listen()把主动套接字变成被动套接字 */
if (listen (fd, BACKLOG) < 0) {
perror ("listen");
exit (1);
}
printf ("Server starting....OK!\n");
int newfd = -1;
/*4. 阻塞等待客户端连接请求 */
struct sockaddr_in cin;
socklen_t addrlen = sizeof (cin);
while(1) {
pid_t pid = -1;
if ((newfd = accept (fd, (struct sockaddr *) &cin, &addrlen)) < 0) {
perror ("accept");
break;
}
/*创建一个子进程用于处理已建立连接的客户的交互数据*/
if((pid = fork()) < 0) {
perror("fork");
break;
}
if(0 == pid) { //子进程中
close(fd);
char ipv4_addr[16];
if (!inet_ntop (AF_INET, (void *) &cin.sin_addr, ipv4_addr, sizeof (cin))) {
perror ("inet_ntop");
exit (1);
}
printf ("Clinet(%s:%d) is connected!\n", ipv4_addr, ntohs(cin.sin_port));
cli_data_handle(&newfd, db);
return 0;
} else { //实际上此处 pid >0, 父进程中
close(newfd);
}
}
close (fd);
return 0;
}
client.c
/*./client serv_ip serv_port */
#include "net.h"
void usage (char *s)
{
printf ("\n%s serv_ip serv_port", s);
printf ("\n\t serv_ip: server ip address");
printf ("\n\t serv_port: server port(>5000)\n\n");
}
int main (int argc, char **argv)
{
int fd = -1;
int port = -1;
struct sockaddr_in sin;
if (argc != 3) {
usage (argv[0]);
exit (1);
}
/* 1. 创建socket fd */
if ((fd = socket (AF_INET, SOCK_STREAM, 0)) < 0) {
perror ("socket");
exit (1);
}
port = atoi (argv[2]);
if (port < 5000) {
usage (argv[0]);
exit (1);
}
/*2.连接服务器 */
/*2.1 填充struct sockaddr_in结构体变量 */
bzero (&sin, sizeof (sin));
sin.sin_family = AF_INET;
sin.sin_port = htons (port); //网络字节序的端口号
#if 0
sin.sin_addr.s_addr = inet_addr (SERV_IP_ADDR);
#else
if (inet_pton (AF_INET, argv[1], (void *) &sin.sin_addr) != 1) {
perror ("inet_pton");
exit (1);
}
#endif
if (connect (fd, (struct sockaddr *) &sin, sizeof (sin)) < 0) {
perror ("connect");
exit (1);
}
printf ("Client staring...OK!\n");
/*3. 读写数据 */
char buf[BUFSIZ];
char buf1[BUFSIZ];
int ret = -1;
while (1) {
bzero (buf1, BUFSIZ);
do {
ret = read (fd, buf1, BUFSIZ - 1);
} while (ret < 0 && EINTR == errno);
if (ret < 0) {
perror ("read");
exit (1);
}
if (!ret) { //对方已经关闭
break;
}
printf("%s", buf1);
bzero (buf, BUFSIZ);
if (fgets (buf, BUFSIZ - 1, stdin) == NULL) {
continue;
}
do {
ret = write (fd, buf, strlen (buf));
} while (ret < 0 && EINTR == errno);
if (!strncasecmp (buf, QUIT_STR, strlen (QUIT_STR))) { //用户输入了quit字符
printf ("Client is exiting!\n");
break;
}
}
/*4.关闭套接字 */
close (fd);
}