📄 110.cpp
字号:
// 110.cpp : Defines the entry point for the console application.
//
#include "stdafx.h"
#include "110.h"
#include "sql.h"
#include "sqlext.h"
#include "sqltypes.h"
#include "windows.h"
//#include <mysql.h>
#include <stdio.h>
#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif
/////////////////////////////////////////////////////////////////////////////
// The one and only application object
CWinApp theApp;
using namespace std;
int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
int nRetCode = 0;
// initialize MFC and print and error on failure
if (!AfxWinInit(::GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0))
{
// TODO: change error code to suit your needs
cerr << _T("Fatal Error: MFC initialization failed") << endl;
nRetCode = 1;
}
else
{
// TODO: code your application's behavior here.
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt1,hstmt2;
SQLRETURN retcode1,retcode2;
SQLINTEGER number[15],number0,record_number[15],record_number0,name[15],name0,\
sex[15],sex0,age[15],age0,address[128],address0,work_unit[128],work_unit0,\
register_date[16],register_date0;
char ins_sel_del;//执行查询，插入还是删除
char query[512];
//select 语句的条件选项
char record_number1[12],name1[16],sex1[4],register_date1[32];
char number1[12],age1[6];
char address1[128],work_unit1[128];
char sign1,sign2,sign3;
int record_number2,number2,name2,age2,sex2,register_date2;
//configure handle----------------------------------------------------------
/*configure enverimental handle*/
retcode1 = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
if(retcode1 == SQL_SUCCESS || retcode1 == SQL_SUCCESS_WITH_INFO)
{
/*set enverimental attribution of ODBC*/
SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
/*configure connection handle*/
retcode1 = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
if(retcode1 == SQL_SUCCESS || retcode1 == SQL_SUCCESS_WITH_INFO)
{
/*set max time of load is 5sec*/
//SQLSetConnectAttr(hdbc,(long*)SQL_ATTR_LOGIN_TIMEOUT,5,0);
/*connect to database*/
retcode1 = SQLConnect(hdbc,(SQLCHAR*)"lab_member",SQL_NTS,
(SQLCHAR*)"root",SQL_NTS,(SQLCHAR*)"bren",SQL_NTS);
if(retcode1 == SQL_SUCCESS || retcode1 == SQL_SUCCESS_WITH_INFO)
{
//configure statment
retcode1 = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt1);
retcode2 = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt2);
if((retcode1 == SQL_SUCCESS || retcode1 == SQL_SUCCESS_WITH_INFO)
&& (retcode2 == SQL_SUCCESS || retcode2 == SQL_SUCCESS_WITH_INFO))
{
//execute SQL--------------------------------------------------------------------------------
//create database
retcode1 = SQLExecDirect(hstmt1,(SQLCHAR*)"create database yldb",SQL_NTS);
if(retcode1 == SQL_SUCCESS || retcode1 == SQL_SUCCESS_WITH_INFO)
{
printf("建立数据库成功!\n");
}
//use database
retcode1 = SQLExecDirect(hstmt1,(SQLCHAR*)"use yldb",SQL_NTS);
if(retcode1 == SQL_SUCCESS || retcode1 == SQL_SUCCESS_WITH_INFO)
{
printf("使用数据库成功!\n");
}
//create table
retcode1 = SQLExecDirect(hstmt1,(SQLCHAR*)"create table ylxt(\
number VARCHAR(12),\
record_number VARCHAR(12),\
name VARCHAR(16),\
sex VARCHAR(4),\
age VARCHAR(6),\
address VARCHAR(128),\
work_unit VARCHAR(128),\
register_date DATE,\
primary key (record_number))",\
SQL_NTS);
if(retcode1 == SQL_SUCCESS || retcode1 == SQL_SUCCESS_WITH_INFO)
{
printf("建表成功!\n");
}
printf("insert values of ins_sel_del\n\
'i' for insert\n\
's' for select\n\
'd' for delete\n\
't' for select data today\n");
scanf("%s",&ins_sel_del);
//insert data 开始=============================================================
if(ins_sel_del == 'i')
{
printf("输入想要插入的病人信息顺序为：病人编号，病人档案号，姓名，性别，年龄,家庭地址，工作单位，登记时间\n");
printf("登记时间格式为yyyy-mm-dd\n");
scanf("%s%s%s%s%s%s%s%s",\
number1,record_number1,name1,sex1,age1,address1,work_unit1,register_date1);
sprintf(query,"insert into ylxt values('%s','%s','%s','%s','%s','%s','%s','%s')",\
number1,record_number1,name1,sex1,age1,address1,work_unit1,register_date1);
retcode1 = SQLExecDirect(hstmt1,(SQLCHAR*)query,SQL_NTS);
}
//insert 结束=====================================================================
//select data开始==================================================================
if(ins_sel_del == 's')
{
printf("输入查询的约束条件！\n");
sprintf(query,"select number,record_number,name,sex,age,address,work_unit,register_date from ylxt");
//查询条件有没有病人档案号
printf("有无档案号?\n有'1' 没有'0'\n");
scanf("%d",&record_number2);
if(record_number2)
{
printf("输入档案号的值.\n");
scanf("%s",record_number1);
sprintf(query,"%s where record_number='%s'",query,record_number1);
}
//查询条件有没有病人编号
printf("有无病人编号?\n有'1',没有'0'\n");
scanf("%d",&number2);
if(number2 && record_number2)
{
printf("输入病人编号!\n");
scanf("%s",number1);
sprintf(query,"%s and number",query);
printf("输入 '=','<'或'>'\n");
scanf("%s",&sign1);
if(sign1 == '=')
{
sprintf(query,"%s=%s",query,number1);
}
if(sign1 == '<')
{
sprintf(query,"%s<%s",query,number1);
}
if(sign1 == '>')
{
sprintf(query,"%s>%s",query,number1);
}
}
else if(number2 && !record_number2)
{
printf("输入病人编号!\n");
scanf("%s",number1);
sprintf(query,"%s where number",query);
printf("输入 '=','<'或'>'\n");
scanf("%s",&sign1);
if(sign1 == '=')
{
sprintf(query,"%s=%s",query,number1);
}
if(sign1 == '<')
{
sprintf(query,"%s<%s",query,number1);
}
if(sign1 == '>')
{
sprintf(query,"%s>%s",query,number1);
}
}
//查询条件有没有姓名
printf("有无姓名?\n有'1',没有'0'\n");
scanf("%d",&name2);
if(name2 && record_number2 || name2 && number2)
{
printf("输入姓名!\n");
scanf("%s",name1);
sprintf(query,"%s and name='%s'",query,name1);
}
else if(name2 && !record_number2 && !number2)
{
printf("输入姓名!\n");
scanf("%s",name1);
sprintf(query,"%s where name='%s'",query,name1);
}
//查询条件有没有性别
printf("有无性别?\n有'1',没有'0'\n");
scanf("%d",&sex2);
if(sex2 && record_number2 || sex2 && number2 ||sex2 && name2)
{
printf("输入性别!\n");
scanf("%s",sex1);
sprintf(query,"%s and sex='%s'",query,sex1);
}
else if(sex2 && !record_number2 && !number2 && !name2)
{
printf("输入性别!\n");
scanf("%s",sex1);
sprintf(query,"%s where sex='%s'",query,sex1);
}
//查询条件有没有年龄
printf("有无年龄?\n有'1',没有'0'\n");
scanf("%s",&age2);
if(age2 && record_number2 || age2 && number2 || age2 && name2 || age2 && sex2)
{
printf("输入年龄!\n");
scanf("%s",age1);
sprintf(query,"%s and age",query);
printf("输入 '=','<'或'>'\n");
scanf("%s",&sign2);
if(sign2 == '=')
{
sprintf(query,"%s=%s",query,age1);
}
if(sign2 == '<')
{
sprintf(query,"%s<%s",query,age1);
}
if(sign2 == '>')
{
sprintf(query,"%s>%s",query,age1);
}
}
else if(age2 && !record_number2 && !number2 && !name2 && !sex2)
{
printf("输入年龄!\n");
scanf("%s",age1);
sprintf(query,"%s where age",query);
printf("输入 '=','<'或'>'\n");
scanf("%s",&sign2);
if(sign2 == '=')
{
sprintf(query,"%s=%s",query,age1);
}
if(sign2 == '<')
{
sprintf(query,"%s<%s",query,age1);
}
if(sign2 == '>')
{
sprintf(query,"%s>%s",query,age1);
}
}
//查询条件有没有登记时间
printf("有无登记时间?\n有'1',没有'0'\n");
scanf("%d",®ister_date2);
if(register_date2 && record_number2 || register_date2 && number2\
|| register_date2 && name2 || register_date2 && sex2\
|| register_date2 && age2)
{
printf("输入登记时间!\n格式为yyyy-mm-dd\n");
scanf("%s",register_date1);
sprintf(query,"%s and register_date",query);
printf("输入 '=','<'或'>'\n");
scanf("%s",&sign3);
if(sign3 == '=')
{
sprintf(query,"%s=%s",query,register_date1);
}
if(sign3 == '<')
{
sprintf(query,"%s<%s",query,register_date1);
}
if(sign3 == '>')
{
sprintf(query,"%s>%s",query,register_date1);
}
}
else if(register_date2 && !record_number2 && !number2 && !name2\
&& !sex2 && !age2)
{
printf("输入登记时间!\n格式为yyyy-mm-dd\n");
scanf("%s",register_date1);
sprintf(query,"%s where register_date",query);
printf("输入 '=','<'或'>'\n");
scanf("%s",&sign3);
if(sign3 == '=')
{
sprintf(query,"%s=%s",query,register_date1);
}
if(sign3 == '<')
{
sprintf(query,"%s<%s",query,register_date1);
}
if(sign3 == '>')
{
sprintf(query,"%s>%s",query,register_date1);
}
}
//执行查询语句
retcode2 = SQLExecDirect(hstmt2,(SQLCHAR*)query,SQL_NTS);
if(retcode2 == SQL_SUCCESS || retcode2 == SQL_SUCCESS_WITH_INFO)
{
printf("number record_number name sex age address work_unit register_date\n");
while(SQLFetch(hstmt2) == 0)
{
SQLGetData(hstmt2,1,SQL_C_CHAR,&number,15,&number0);
SQLGetData(hstmt2,2,SQL_C_CHAR,&record_number,15,&record_number0);
SQLGetData(hstmt2,3,SQL_C_CHAR,&name,15,&name0);
SQLGetData(hstmt2,4,SQL_C_CHAR,&sex,15,&sex0);
SQLGetData(hstmt2,5,SQL_C_CHAR,&age,15,&age0);
SQLGetData(hstmt2,6,SQL_C_CHAR,&address,128,&address0);
SQLGetData(hstmt2,7,SQL_C_CHAR,&work_unit,128,&work_unit0);
SQLGetData(hstmt2,8,SQL_C_CHAR,®ister_date,16,®ister_date0);
printf("%6s%10s%6s%4s%4s%30s%30s%10s\n",number,record_number,name,sex,age,address,work_unit,register_date);
memset(number,0x00,sizeof(number));
memset(record_number,0x00,sizeof(record_number));
memset(name,0x00,sizeof(name));
memset(sex,0x00,sizeof(sex));
memset(age,0x00,sizeof(age));
memset(address,0x00,sizeof(address));
memset(work_unit,0x00,sizeof(work_unit));
memset(register_date,0x00,sizeof(register_date));
}
}
}
//select data 结束===================================================
//select data 今日登记
if(ins_sel_del == 't')
{
sprintf(query,"select number,record_number,name,sex,age,address,work_unit,register_date from ylxt where register_date=CURDATE()");
retcode2 = SQLExecDirect(hstmt2,(SQLCHAR*)query,SQL_NTS);
if(retcode2 == SQL_SUCCESS || retcode2 == SQL_SUCCESS_WITH_INFO)
{
printf("number record_number name sex age address work_unit register_date\n");
while(SQLFetch(hstmt2) == 0)
{
SQLGetData(hstmt2,1,SQL_C_CHAR,&number,15,&number0);
SQLGetData(hstmt2,2,SQL_C_CHAR,&record_number,15,&record_number0);
SQLGetData(hstmt2,3,SQL_C_CHAR,&name,15,&name0);
SQLGetData(hstmt2,4,SQL_C_CHAR,&sex,15,&sex0);
SQLGetData(hstmt2,5,SQL_C_CHAR,&age,15,&age0);
SQLGetData(hstmt2,6,SQL_C_CHAR,&address,128,&address0);
SQLGetData(hstmt2,7,SQL_C_CHAR,&work_unit,128,&work_unit0);
SQLGetData(hstmt2,8,SQL_C_CHAR,®ister_date,16,®ister_date0);
printf("%6s%10s%6s%4s%4s%30s%30s%10s\n",number,record_number,name,sex,age,address,work_unit,register_date);
memset(number,0x00,sizeof(number));
memset(record_number,0x00,sizeof(record_number));
memset(name,0x00,sizeof(name));
memset(sex,0x00,sizeof(sex));
memset(age,0x00,sizeof(age));
memset(address,0x00,sizeof(address));
memset(work_unit,0x00,sizeof(work_unit));
memset(register_date,0x00,sizeof(register_date));
}
}
}
//delect data 开始==================================================
if(ins_sel_del == 'd')
{
printf("输入想要删除行的病人档案号!\n");
scanf("%s",record_number1);
sprintf(query,"delete from ylxt where record_number='%s';",record_number1);
retcode1 = SQLExecDirect(hstmt1,(SQLCHAR*)query,SQL_NTS);
if(retcode1 == SQL_SUCCESS)
printf("删除成功!");
}
//delect data 结束====================================================
//free handle--------------------------------------------------------------------------------
SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
SQLFreeHandle(SQL_HANDLE_STMT,hstmt2);
}
SQLDisconnect(hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
}
SQLFreeHandle(SQL_HANDLE_ENV,henv);
}
}
return nRetCode;
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -