📄 db_func.cpp

📁 一个简单的公交查询管理系统
💻 CPP
📖 第 1 页 / 共 4 页
字号:
12 3 4 下一页
#include "stdafx.h"
#include "DB_Func.h"
#include "sql.h"
#include "stdio.h"
#include "stdlib.h"
#include "windows.h"
#include "sqltypes.H"
#include "sqlext.h"
#include <math.h>

//////////////////////////////////////////////////////////////////////////

SQLHENV   henv      = SQL_NULL_HENV;      //环境句柄
SQLHDBC   hdbc      = SQL_NULL_HDBC;      //连接句柄
SQLHSTMT  hstmt     = SQL_NULL_HSTMT;     //语句句柄
SQLRETURN  retcode;

/***********   打开连接数据源    ***********/
int DB_Open(SQLHENV *phenv, SQLHDBC *phdbc, SQLHSTMT *phstmt)
{
	//申请ODBC环境句柄
	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, phenv);
    if((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
    {
		AfxMessageBox("分配环境句柄失败!");
		return -1;
	}
	
    //设置ODBC版本号
    retcode = SQLSetEnvAttr(*phenv,SQL_ATTR_ODBC_VERSION,(void *)SQL_OV_ODBC3,0); 
	if((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
    {
		//关闭环境句柄
        retcode = SQLFreeHandle(SQL_HANDLE_ENV,phenv);
	    if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
		{
			AfxMessageBox("设置ODBC版本号失败,关闭环境句柄失败!");
		    return -1;
		}
        //
		AfxMessageBox("设置ODBC版本号失败!");
		return -1;
	}
	   
	// 分配连接句柄
    retcode = SQLAllocHandle(SQL_HANDLE_DBC,*phenv,phdbc);    
    if((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))    
    {
		//关闭环境句柄
        retcode = SQLFreeHandle(SQL_HANDLE_ENV,phenv);
	    if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
		{
			AfxMessageBox("关闭环境句柄失败!");
		    return -1;
		}
		//
		AfxMessageBox("分配连接句柄失败!");
		return -1;
	}

    //连接到数据源
	retcode = SQLConnect(*phdbc,(SQLCHAR *)"busquery",SQL_NTS,(unsigned char *)"sa",SQL_NTS,(unsigned char *)"sa",SQL_NTS);
    if((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))  
	{
		//AfxMessageBox("连接到数据源失败!");
		return -1;
	}

	//分配语句句柄
	retcode = SQLAllocHandle(SQL_HANDLE_STMT,*phdbc,phstmt);
    if((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("分配语句句柄失败!");
		return -1;
	}
	//
	return 0;

}

           
/************** 关闭连接函数  *************/
int DB_Close(SQLHENV phenv, SQLHDBC phdbc, SQLHSTMT phstmt)
{
	//释放语句句柄	

	if(phstmt == NULL)
	{
		AfxMessageBox("句柄参数为空!");
		return -1;	
	}

	retcode = SQLFreeHandle(SQL_HANDLE_STMT,phstmt);
    if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("关闭语句句柄失败!");
		return -1;
	}
	
	//释放句柄
	if(phdbc == NULL)
	{
		AfxMessageBox("句柄参数为空!");
		return -1;	
	}

    retcode = SQLDisconnect(phdbc); 
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("断开连接失败!");
		return -1;
	}

	//释放连接句柄
	if(phdbc == NULL)
	{
		AfxMessageBox("句柄参数为空!");
		return -1;	
	}

    retcode = SQLFreeHandle(SQL_HANDLE_DBC,phdbc);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("关闭连接句柄失败!");
		return -1;
	}

	//释放环境句柄
	if(phenv == NULL)
	{
		AfxMessageBox("句柄参数为空!");
		return -1;	
	}
    retcode = SQLFreeHandle(SQL_HANDLE_ENV,phenv);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("关闭环境句柄失败");
		return -1;
	}

	//
	return 0;
}

//////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////

int DB_BUS_TYPE_Query(SQLHSTMT g_hstmt, BUS_TYPE *bus_type, BUS_TYPE *head)
{
	BUS_TYPE *pS,*pEnd;
	SQLCHAR *SqlStatement;
	//    
	SQLINTEGER cbBus_Type_Name = SQL_NTS;
    //Sql Statement
	
	SqlStatement = (unsigned char *) " SELECT bus_type_name FROM bus_type ORDER BY bus_type_name ASC";
	
	//Prepare
	retcode = SQLPrepare(g_hstmt,SqlStatement,SQL_NTS);
	if ((retcode!= SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("prepare error!");
		return -1;
	}
	
	//绑定列参数
	retcode = SQLBindCol(g_hstmt,1,SQL_C_CHAR,bus_type->Bus_Type_Name,sizeof(bus_type->Bus_Type_Name),&cbBus_Type_Name);
	if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLBindCol error!");
		return -1;
	}
	
	//执行查询
	retcode = SQLExecute(g_hstmt);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLExecute error!");
		return -1;
	}
	
	//获取数据
    retcode = SQLFetch(g_hstmt);
    if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		//关闭
		retcode = SQLFreeStmt(hstmt,SQL_CLOSE);
		if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
		{
			AfxMessageBox("SQLCloseCursor!");
			return -1;
		} 
		return -1;
	}
	
	//赋值
	strcpy(head->Bus_Type_Name,    bus_type->Bus_Type_Name);
	
    pEnd = head;
	memset(bus_type,0,sizeof(BUS_TYPE));
	while ( (retcode=SQLFetch(g_hstmt)) != SQL_NO_DATA )
	{ 
		pS = new BUS_TYPE;
		memset(pS,0,sizeof(BUS_TYPE));
		//赋值
		//赋值
		strcpy(pS->Bus_Type_Name,   bus_type->Bus_Type_Name);
		
		
		pEnd->next = pS;
		pEnd = pS;
		memset(bus_type,0,sizeof(BUS_TYPE));
	}    //end of while  
	
	pEnd->next = NULL;
	
    //关闭当前
	retcode = SQLFreeStmt(g_hstmt,SQL_CLOSE);
	if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
	{
		AfxMessageBox("SQLFreeStmt error!");
		return -1;
	} 	
	
	//
	return 0;
	

}

int DB_BUS_TYPE_Modify(SQLHSTMT g_hstmt, char *old_bus_type, char *new_bus_type)
{
	//
	SQLCHAR *SqlStatement;
	//  
	SQLINTEGER cbold_bus_type = SQL_NTS;
	SQLINTEGER cbnew_bus_type = SQL_NTS;
    //Sql Statement
	SqlStatement =(unsigned char *) "UPDATE bus_type SET bus_type_name=? WHERE bus_type_name=?";
	
	//Prepare
	retcode = SQLPrepare(g_hstmt,SqlStatement,SQL_NTS);
	if ((retcode!= SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("prepare error!");
		return -1;
	}
	
	//绑定插入参数
	retcode = SQLBindParameter(g_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,strlen(new_bus_type),0,new_bus_type,strlen(new_bus_type),&cbnew_bus_type);
	if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLBindParameter error!");
		return -1;
	}
	
	retcode = SQLBindParameter(g_hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,strlen(old_bus_type),0,old_bus_type,strlen(old_bus_type),&cbold_bus_type);
	if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLBindParameter error!");
		return -1;
	}
	//执行查询
	retcode = SQLExecute(g_hstmt);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLExecute error!");
		//关闭
		retcode = SQLFreeStmt(hstmt,SQL_CLOSE);
		if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
		{
			AfxMessageBox("SQLCloseCursor!");
			return -1;
		} 
		return -1;
		
	}
	
	
    //关闭当前
	retcode = SQLFreeStmt(g_hstmt,SQL_CLOSE);
	if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
	{
		AfxMessageBox("SQLFreeStmt error!");
		return -1;
	} 	
	
	//
	return 0;
	

}  

int DB_BUS_TYPE_Delete(SQLHSTMT g_hstmt, char *bus_type)
{
	SQLCHAR *SqlStatement;
	//  
	SQLINTEGER cbbus_type = SQL_NTS;
    //Sql Statement
	SqlStatement =(unsigned char *) "DELETE FROM bus_type WHERE bus_type_name=?";
	
	//Prepare
	retcode = SQLPrepare(g_hstmt,SqlStatement,SQL_NTS);
	if ((retcode!= SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("prepare error!");
		return -1;
	}
	
	//绑定插入参数
	retcode = SQLBindParameter(g_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,strlen(bus_type),0,bus_type,strlen(bus_type),&cbbus_type);
	if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLBindParameter error!");
		return -1;
	}
	
	//执行查询
	retcode = SQLExecute(g_hstmt);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		//关闭
		retcode = SQLFreeStmt(hstmt,SQL_CLOSE);
		if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
		{
			AfxMessageBox("SQLCloseCursor!");
			return -1;
		} 
		return -1;
		
	}
	
	
    //关闭当前
	retcode = SQLFreeStmt(g_hstmt,SQL_CLOSE);
	if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
	{
		AfxMessageBox("SQLFreeStmt error!");
		return -1;
	} 	
	
	//
	return 0;
}

int DB_BUS_TYPE_Delete_All(SQLHSTMT g_hstmt)
{
    SQLCHAR *SqlStatement;
	//  
    //Sql Statement
	SqlStatement =(unsigned char *) "DELETE FROM bus_type";
	
	//Prepare
	retcode = SQLPrepare(g_hstmt,SqlStatement,SQL_NTS);
	if ((retcode!= SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("prepare error!");
		return -1;
	}
	
	//执行查询
	retcode = SQLExecute(g_hstmt);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		//关闭
		retcode = SQLFreeStmt(hstmt,SQL_CLOSE);
		if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
		{
			AfxMessageBox("SQLCloseCursor!");
			return -1;
		} 
		return -1;
		
	}
	
	
    //关闭当前
	retcode = SQLFreeStmt(g_hstmt,SQL_CLOSE);
	if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
	{
		AfxMessageBox("SQLFreeStmt error!");
		return -1;
	} 	
	
	//
	return 0;
}

int DB_BUS_TYPE_Insert(SQLHSTMT g_hstmt, char *bus_type)
{
	SQLCHAR *SqlStatement;
	//    
	SQLINTEGER cbbus_type = SQL_NTS;
    //Sql Statement
	SqlStatement =(unsigned char *) "INSERT INTO bus_type (bus_type_name) VALUES (?)";
	
	//Prepare
	retcode = SQLPrepare(g_hstmt,SqlStatement,SQL_NTS);
	if ((retcode!= SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("prepare error!");
		return -1;
	}
	
	//绑定插入参数
	retcode = SQLBindParameter(g_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,strlen(bus_type),0,bus_type,strlen(bus_type),&cbbus_type);
	if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLBindParameter error!");
		return -1;
	}
	
	//执行查询
	retcode = SQLExecute(g_hstmt);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		//关闭
		retcode = SQLFreeStmt(hstmt,SQL_CLOSE);
		if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
		{
			AfxMessageBox("SQLCloseCursor!");
			return -1;
		} 
		return -1;
		
	}
	
	
    //关闭当前
	retcode = SQLFreeStmt(g_hstmt,SQL_CLOSE);
	if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
	{
		AfxMessageBox("SQLFreeStmt error!");
		return -1;
	} 	
	
	//
	return 0;

}

//////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////
  
int DB_STATION_TYPE_Query(SQLHSTMT g_hstmt, STATION_TYPE *station_type, STATION_TYPE *head)  
{
	STATION_TYPE *pS,*pEnd;
	SQLCHAR *SqlStatement;
	//    
	SQLINTEGER cbStation_Type_Name = SQL_NTS;
    //Sql Statement
	
	SqlStatement = (unsigned char *) " SELECT station_type_name FROM station_type"; //ORDER BY station_type_name ASC";
	
	//Prepare
	retcode = SQLPrepare(g_hstmt,SqlStatement,SQL_NTS);
	if ((retcode!= SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("prepare error!");
		return -1;
	}
	
	//绑定列参数
	retcode = SQLBindCol(g_hstmt,1,SQL_C_CHAR,station_type->Station_Type_Name,sizeof(station_type->Station_Type_Name),&cbStation_Type_Name);
	if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLBindCol error!");
		return -1;
	}
	
	//执行查询
	retcode = SQLExecute(g_hstmt);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("SQLExecute error!");
		return -1;
	}
	
	//获取数据
    retcode = SQLFetch(g_hstmt);
    if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		//关闭
		retcode = SQLFreeStmt(hstmt,SQL_CLOSE);
		if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
		{
			AfxMessageBox("SQLCloseCursor!");
			return -1;
		} 
		return -1;
	}
	
	//赋值
	strcpy(head->Station_Type_Name,    station_type->Station_Type_Name);
	
    pEnd = head;
	memset(station_type,0,sizeof(STATION_TYPE));
	while ( (retcode=SQLFetch(g_hstmt)) != SQL_NO_DATA )
	{ 
		pS = new STATION_TYPE;
		memset(pS,0,sizeof(STATION_TYPE));
		//赋值
		//赋值
		strcpy(pS->Station_Type_Name,   station_type->Station_Type_Name);
		
		
		pEnd->next = pS;
		pEnd = pS;
		memset(station_type,0,sizeof(STATION_TYPE));
	}    //end of while  
	
	pEnd->next = NULL;
	
    //关闭当前
	retcode = SQLFreeStmt(g_hstmt,SQL_CLOSE);
	if( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
	{
		AfxMessageBox("SQLFreeStmt error!");
		return -1;
	} 	
	
	//
	return 0;
12 3 4 下一页
💿 文件大小 2129 K
👤 上传用户 zzxt
📂 所属分类其他数据库
🏷️ 相关标签

#查询 #管理系统
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -