database.cpp

为电力系统开发保护设备装置通讯的源代码 采用串口通讯, ret hat 9下 QT开发

#include "database.h"
#include <string.h>
#include <qdatetime.h>
#include <qstring.h>

CDataBase :: CDataBase()
{
    m_pconnect=NULL;
    m_pghost="";m_pgport="";m_pgoptions="";m_pgtty="";m_dbname="znl4";
    m_dbuser="";m_dbpassword="";m_bconnect=0;
}

CDataBase :: ~CDataBase()
{
    return;
}

bool CDataBase :: ConnectDataBase()
{
    m_pconnect = PQsetdbLogin(m_pghost.data(),m_pgport.data(),m_pgoptions.data(),
			      m_pgtty.data(),m_dbname.data(),m_dbuser.data(),m_dbpassword.data());
   if (PQstatus(m_pconnect) == CONNECTION_BAD)
   {
       fprintf(stderr,"Connection to database '%s' failed!\\n",m_dbname.data());
       PQfinish(m_pconnect);
	   return 0;
   }
   return 1;
}

bool CDataBase :: ConnectDataBase(long &hdbc,QCString dbname,QCString dbuser,QCString dbpass)
{
    m_pconnect = PQsetdbLogin(NULL,NULL,NULL,NULL,
			      dbname.data(),dbuser.data(),dbpass.data());
   if (PQstatus(m_pconnect) == CONNECTION_BAD)
   {
       //fprintf(stderr,"Connection to database '%s' failed!\n",m_dbname.data());
       PQfinish(m_pconnect);
       hdbc=0;
	return 0;
   }
   hdbc=(long)m_pconnect;
   return 1;
}

bool CDataBase :: DisConnectDataBase(long &hdbc)
{
       PQfinish((PGconn*)hdbc);
       hdbc=0;
       return 1;
}

int CDataBase :: SqlExec(long hdbc,QCString SqlStr)
{
    PGconn *conn=(PGconn*)hdbc;
    if(!conn) return -1;
    PGresult *res = PQexec(conn,SqlStr.data());
   /**运行查询命令*/
   if(PQresultStatus(res)!=PGRES_COMMAND_OK) 
   {
	fprintf(stderr,PQresultErrorMessage(res));
       fprintf(stderr,"Exec Query Fauled!\\n"); PQclear(res);return -1;
   }
   PQclear(res);
   return 0;
}

long CDataBase :: SqlSelect(long hdbc,QCString SqlStr,long &result)
{
    PGconn *conn=(PGconn*)hdbc;
    if(!conn) return -1;
    PGresult *res = PQexec(conn,SqlStr.data());
   /**运行查询命令*/
   if(  PQresultStatus(res)  !=  PGRES_TUPLES_OK) 
    {
       fprintf(stderr,"Exec Query Fauled!\\n");
       PQclear(res);
       result=0;
	return -1;
   }
   int i = PQntuples(res);
   result=(long)res;
   return (long)i;
}

int CDataBase :: GetRecordCount(long result)
{
    PGresult* res=(PGresult*)result;
    if(!res) return -1;
    return PQntuples(res);
}

int CDataBase :: GetFieldCount(long result)
{
    PGresult* res=(PGresult*)result;
    if(!res) return -1;
    return PQnfields(res);
}

QCString CDataBase :: GetFieldName(long result,unsigned int iField)
{
    QCString strres="";
    PGresult* res=(PGresult*)result;
    if(!res) return strres;
    strres.sprintf("%s",PQfname(res,(int)iField));
    return strres;
}

int CDataBase :: GetFieldType(long result,unsigned int iField)
{
    PGresult* res=(PGresult*)result;
    if(!res) return 0;
    return (int)PQftype(res,(int)iField);
}

QCString CDataBase :: GetFieldValue(long result,unsigned int iRow,unsigned int iField)
{
    QCString strres="";
    PGresult* res=(PGresult*)result;
    if(!res) return strres;
    strres.sprintf("%s",PQgetvalue(res,(int)iRow,(int)iField));
    return strres;
}

void CDataBase :: ClearResult(long &result)
{
    PGresult* res=(PGresult*)result;
    if(!res) return;
    PQclear(res);result=0;
    return;
}

bool CDataBase :: OpenCursor(long hdbc,QCString SqlStr,long &pCursor)
{
    PGresult *res = PQexec((PGconn*)hdbc, "BEGIN");
    if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
    {
	fprintf(stderr, "BEGIN command failed\n");PQclear(res);pCursor=0;
	return 0;
    }
    PQclear(res);
    QDate sdate;QTime stime;QCString s1,s2;
    s1.sprintf("cur%04d%02d%02d%02d%02d%02d%03d",sdate.year(),sdate.month(),sdate.day(),
	       stime.hour(),stime.minute(),stime.second(),stime.msec());
    s2="DECLARE "+s1+" CURSOR FOR "+SqlStr;
    res = PQexec((PGconn*)hdbc, s2);
    if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
    {
       fprintf(stderr, "DECLARE CURSOR command failed\n");PQclear(res);pCursor=0;
	return 0;
    }
    PQclear(res);
    char *sres=new char[s1.length()+1];
    memset(sres,0x00,s1.length()+1);
    memcpy(sres,s1.data(),s1.length()+1);
    pCursor=(long)sres;
    return 1;
}

bool CDataBase :: FetchPriorRow(long hdbc,long pCursor,long &result)
{
    QCString s1;
    s1.sprintf("FETCH PRIOR in %s",(char*)pCursor);
    PGresult *res = PQexec((PGconn*)hdbc, s1.data());
    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH PRIOR command didn't return tuples properly\n");PQclear(res);
        result=0;return 0;
    }
    result=(long)res;
    return 1;
}

bool CDataBase :: FetchNextRow(long hdbc,long pCursor,long &result)
{
    QCString s1;
    s1.sprintf("FETCH NEXT in %s",(char*)pCursor);
    PGresult *res = PQexec((PGconn*)hdbc, s1.data());
    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH NEXT command didn't return tuples properly\n");PQclear(res);
        result=0;return 0;
    }
    result=(long)res;
    return 1;
}

bool CDataBase :: FetchAllRow(long hdbc,long pCursor,long &result)
{
    QCString s1;
    s1.sprintf("FETCH ALL in %s",(char*)pCursor);
    PGresult *res = PQexec((PGconn*)hdbc, s1.data());
    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH NEXT command didn't return tuples properly\n");PQclear(res);
        result=0;return 0;
    }
    result=(long)res;
    return 1;
}

void CDataBase :: CloseCursor(long hdbc,long &pCursor)
{
    QCString s1;
    s1.sprintf("CLOSE %s",(char*)pCursor);
    PGresult *res = PQexec((PGconn*)hdbc, s1.data());
    PQclear(res);
    res = PQexec((PGconn*)hdbc, "COMMIT");
    PQclear(res);
    //释放光标内存
    delete [] (char*)pCursor;
    return ;
}
//**********************填充参数************************************//
QCString CDataBase :: FillParam(QCString SqlStr,int ParamIndex,int ParamType,QCString param)
{
    QCString tmps,tmps1;
    int istart=0,index=-1;
    for(int i=0;i<ParamIndex;i++)
    {
	index=SqlStr.find("?",istart);
	if(index==-1) return "";
	istart=index+1;
    }
    switch(ParamType)
    {
    case 0:    //数字
	tmps=SqlStr.replace(index,1,param.data());
	break;
    case 1:    //字符
	tmps1.sprintf("'%s'",param.data());
	tmps=SqlStr.replace(index,1,tmps1.data());
	break;
    default:
	break;
    }
    return tmps;
}

void CDataBase :: GetTableFromSql(QCString SqlStr,QCString &Table,QCString &WhereStr,QStringList &FieldList)
{
    //判定SQL语句类型
    QCString tmps1,tmps2;QString qstmp;int iStart,i,j;
    int index=SqlStr.find(" ",0);
    if(index<0) return;
    FieldList.clear();
    tmps1=SqlStr.mid(0,index);tmps1=tmps1.upper();tmps1=tmps1.stripWhiteSpace();
    if(tmps1=="SELECT")
    {
	index=SqlStr.find(" from ",0);if(index<0) index=SqlStr.find(" From ",0);
	if(index<0) index=SqlStr.find(" FROM ",0);
	if(index<0) return;
	//字段
	iStart=SqlStr.find(" ",0);
	while((i=SqlStr.find(",",iStart+1)>=0)&&i<index)
	{
	    tmps2=SqlStr.mid(iStart+1,i-iStart-1);qstmp.sprintf("%s",tmps2.data());FieldList.append(qstmp);
	    iStart=i;
	}
       tmps2=SqlStr.mid(iStart+1,index-iStart-1);qstmp.sprintf("%s",tmps2.data());FieldList.append(qstmp);
	//表名
       iStart=index;
       index=SqlStr.find(" where ",iStart+1);if(index<0) index=SqlStr.find(" Where ",iStart+1);
       if(index<0) index=SqlStr.find(" WHERE ",iStart+1);
       if(index<0)
       {
	   Table=SqlStr.mid(iStart+6);
	   WhereStr="";
	   return;
       }
       Table=SqlStr.mid(iStart+6,index-iStart-6);
       WhereStr=SqlStr.mid(index+1);
       return;
    }
    else if(tmps1=="UPDATE")
    {
	index=SqlStr.find(" set ",0);if(index<0) index=SqlStr.find(" Set ",0);
	if(index<0) index=SqlStr.find(" SET ",0);
	if(index<0) return;
	iStart=SqlStr.find(" ",0);
	//表名
	Table=SqlStr.mid(iStart+1,index-iStart-1);
	//字段
       iStart=index;
       index=SqlStr.find(" where ",iStart+1);if(index<0) index=SqlStr.find(" Where ",iStart+1);
       if(index<0) index=SqlStr.find(" WHERE ",iStart+1);
       if(index<0) index=SqlStr.length();
       iStart+=4;
	while((i=SqlStr.find(",",iStart+1)>=0)&&i<index)
	{
	    tmps2=SqlStr.mid(iStart+1,i-iStart-1);
	    j=tmps2.find("=",iStart+1);tmps2=tmps2.mid(iStart+1,j-iStart-1);