database.cpp

电力故障信息采集,主要是针对南自的保护装置,这个程序用在Linux操作系统下

#include "database.h"
#include <string.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)
   {
       PQfinish(m_pconnect);m_hdbc=0;m_bconnect=0;return 0;
   }
   //addy hyk,resolve encoding
   PQsetClientEncoding(m_pconnect,"gbk");
   m_hdbc=(long)m_pconnect;m_bconnect=1;
   return 1;
}

bool CDataBase :: ConnectDataBase(long &hdbc)
{
   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)
   {
	PQfinish(m_pconnect);hdbc=0;return 0;
   }
   //add by hky,resolve encoding
   PQsetClientEncoding(m_pconnect,"gbk");
   hdbc=(long)m_pconnect;m_hdbc=hdbc;
   return 1;
}

bool CDataBase :: ConnectDataBase(long &hdbc,QString dbname,QString dbuser,QString dbpass)
{
   m_pconnect = PQsetdbLogin(NULL,NULL,NULL,NULL,dbname.data(),dbuser.data(),dbpass.data());
   if (PQstatus(m_pconnect) == CONNECTION_BAD)
   {
       PQfinish(m_pconnect);hdbc=0;m_bconnect=0;return 0;
   }
   //add by hyk,resolve encoding
   PQsetClientEncoding(m_pconnect,"gbk");
   hdbc=(long)m_pconnect;m_bconnect=1;m_hdbc=hdbc;
   return 1;
}

bool CDataBase :: DisConnectDataBase(long &hdbc)
{
    PQfinish((PGconn*)hdbc);hdbc=0;m_hdbc=hdbc;
    return 1;
}
/////****************得到数据库的连接状态*******************////////
//*CONNECTION_BAD  		连接失败
//*CONNECTION_OK			连接成功
//*CONNECTION_STARTED		开始连接......
//*CONNECTION_MADE			已经建立数据库连接
int CDataBase :: GetDbConnectState(long hdbc)
{
    return PQstatus((PGconn*)hdbc);
}


int CDataBase :: SqlExec(long hdbc,QString 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,QString SqlStr,long &result)
{
    PGconn *conn=(PGconn*)hdbc;
    if(!conn) return -1;
    //QCString Sqltmp(2048);Sqltmp.sprintf("%s",SqlStr.data());
    PGresult *res = PQexec(conn,SqlStr.data());
   /**运行查询命令*/
   if(  PQresultStatus(res)  !=  PGRES_TUPLES_OK) 
    {
       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;
}

void CDataBase :: ClearResult(long hdbc,long &result)
{
    ClearResult(result);    
    DisConnectDataBase(hdbc);
    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);
    QCString s1,s2;
    s1.sprintf("cur%s",getTimeStr());
    s2="DECLARE "+s1+" CURSOR FOR "+SqlStr;
    res = PQexec((PGconn*)hdbc, s2.data());
    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;SqlStr="";
    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;pCursor=0;
    return ;
}

//**********************填充参数************************************//
QString CDataBase :: FillParam(QString SqlStr,int ParamIndex,int ParamType,QString param)
{
    QString 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(QString SqlStr,QString &Table,QString &WhereStr,QStringList &FieldList)
{
    //判定SQL语句类型
    QString 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);