dbinterface.cpp

一个在linux下连接Sybase数据库

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "../inc/dbInterface.hpp"

/**************************************/
#define MAX_TMSI_LEN     4   //the TMSI/P-TMSI is 4 octets long                                               
#define MAX_IMSI_LEN     15  //The IMSI shall not exceed 15 digits                                               
#define MAX_MSISDN_LEN   32                                                
#define MAX_FILENAME_LEN 128 //NOTE                                         
#define MAX_A_SPC_LEN    3   //A接口点码长度BYTE                                
#define MAX_LR_LEN       3       
#define MAX_TIME_LEN     32
#define MAX_FACILITY_LEN 256 //
#define MAX_FN_LEN		 32
#define MINIX(a, b)		 ((a) > (b) ? (b) : (a))
/**************************************/

#ifndef _MULTITHREAD_SUPPORT_ 
exec sql include sqlca;
#endif

/*
cpre_r sample.cp

gcc -g -D_REENTRANT -I/opt/sybase/OCS-15_0/include -L/opt/sybase/OCS-15_0/lib -lsybct_r -lsybtcl_r -lsybcs_r -lsybcomn_r -lsybunic -rdynamic -ldl -lpthread -lnsl -lm /opt/sybase/OCS-15_0/include/sybesql.c sample.c -o sample
*/
/*
#ifdef _MULTITHREAD_SUPPORT_
typedef struct threadLocalData
{
	int retCode;
	char errMsg[512];
}  threadLocalData;

#define OBJECT_SIZE sizeof(threadLocalData)

static pthread_key_t key;
static pthread_once_t key_once = PTHREAD_ONCE_INIT;
static void make_key()
{
    (void) pthread_key_create(&key, NULL);
}


void threadInit()
{
    void *ptr;
    (void) pthread_once(&key_once, make_key);    
    if ((ptr = pthread_getspecific(key)) == NULL) 
    {
        ptr = malloc(OBJECT_SIZE);
        if(ptr != NULL) memset(ptr, 0, OBJECT_SIZE);
        (void) pthread_setspecific(key, ptr);
    }
}
static atomic_t iConnectCnt = ATOMIC_INIT(0);
#else
unsigned int iConnectCnt = 0;
#endif

*/
static unsigned long  iConnectCnt =0;
int getConnectName(char* cntBuf)
{
	int cnt = ++iConnectCnt;
	sprintf(cntBuf, "joyit@zyj%d", cnt);	
	return cnt; 
}

int connectDb(char *uname, char *pwd, char *srv)
{
    exec sql begin declare section;
    char username[32];
    char passwd[32]  ;
    char servname[32];
    char cntname[32];
    exec sql end declare section;

    
	if(uname != NULL) strncpy(username, uname, 32);
	if(pwd != NULL)   strncpy(passwd,   pwd,   32);
	if(srv != NULL)   strncpy(servname, srv,   32);
	int id = getConnectName(cntname);
	exec sql connect :username identified by :passwd at :cntname using :servname;
	if(sqlca.sqlcode < 0) 
		return sqlca.sqlcode;
	else return id;
}

int disconnectDb(int id)
{
	if(0 == id)
	{
		exec sql disconnect all;
	}
    else
    {
		exec sql begin declare section;
		char cntname[32];
		exec sql end declare section;
		
		sprintf(cntname, "joyit%d", id);
		exec sql disconnect :cntname;
    }
    return sqlca.sqlcode;
}

char* getLastError()
{
    static char error[512];
    memset(error, 0, 512);
    snprintf(error, 512, sqlca.sqlerrm.sqlerrmc);
    return error; 
}
/*exec sql declare AlarmsTable1 cursor for select (convert(char(10),Time,102))+' '+(convert(char(9),Time,108)), AlarmCode,Info from SapDB.dbo.TBL_Alarms;*/
/*exec sql declare AlarmsTable1 cursor for select (convert(char(10),Time,102))+' '+(convert(char(9),Time,108)), AlarmCode,Info from SapDB.dbo.TBL_Alarms  a where not exists(select 1 from SapDB.dbo.TBL_Alarms where AlarmCode =a.AlarmCode and Time>a.Time);*/
exec sql declare AlarmsTable1 cursor for select (convert(char(10),Time,102))+' '+(convert(char(9),Time,108)), AlarmCode,Info from SapLogDB.dbo.TBL_Alarms  a where not exists(select 1 from SapLogDB.dbo.TBL_Alarms where AlarmCode%16 =a.AlarmCode%16 and Time>a.Time);
int readAlarmsTable(AlarmsTable* pNums,int isFromBegin, int id)
{

	exec sql begin declare section;
 	struct _AlarmsTable 
 	{
 		CS_CHAR Time[32];
		CS_INT AlarmCode;
		CS_CHAR Info[256];
 	} TAlarms;
	exec sql end declare section;
    if(0 != isFromBegin)
    {
		exec sql close AlarmsTable1;
		exec sql open AlarmsTable1;
		if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    }
	memset(&TAlarms, 0, sizeof(TAlarms));

    exec sql fetch AlarmsTable1 into :TAlarms.Time, :TAlarms.AlarmCode,:TAlarms.Info;
    if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    if(sqlca.sqlcode == 0)
    {
    	memcpy(pNums->DateTime,TAlarms.Time,sizeof((*pNums).DateTime));
		memcpy(pNums->Info,TAlarms.Info,sizeof((*pNums).Info));
		memcpy(&pNums->AlarmCode,&TAlarms.AlarmCode,sizeof(sizeof(pNums->AlarmCode)));
		return 1;
    }
    else if(sqlca.sqlcode == 100)
    {
		/*exec sql close AlarmsTable1;*/
		return 0;
    }

	return sqlca.sqlcode;
}
exec sql declare SMSendStrategy cursor for select * from SapDB.dbo.TBL_SMSendStrategy;
int ReadSMSendStrategy(SendStrategy* pNums, int isFromBegin, int id)
{
	exec sql begin declare section;
 	struct _SendStrategy
 	{
 		CS_INT 			InfoType;
		CS_TINYINT	 	AlarmMode;
		CS_INT			DelayTime;
		CS_INT 			DelayCount;
		CS_CHAR 		MSISDN_AlarmList[128];
 	} Strategy;
	exec sql end declare section;
 	if(0 != isFromBegin)
    {
		exec sql close SMSendStrategy;
		exec sql open SMSendStrategy;
		if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    }
	memset(&Strategy, 0, sizeof(Strategy));
	
   	exec sql fetch SMSendStrategy into :Strategy.InfoType, :Strategy.AlarmMode,:Strategy.DelayTime,:Strategy.DelayCount,:Strategy.MSISDN_AlarmList;
    if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    if(sqlca.sqlcode == 0)
    {
    	memcpy(pNums->MSISDN_AlarmList,Strategy.MSISDN_AlarmList,sizeof((*pNums).MSISDN_AlarmList));
		pNums->AlarmMode=Strategy.AlarmMode;
		pNums->DelayCount=Strategy.DelayCount;
		pNums->DelayTime=Strategy.DelayTime;
		pNums->InfoType=Strategy.InfoType;
		return 1;
    }
    else if(sqlca.sqlcode == 100)
    {
		/*exec sql close SMSendStrategy;*/
		return 0;
    }

	return sqlca.sqlcode;
}
/*exec sql declare SMSendRecord cursor for select AlarmCode,(convert(char(10),SendTime,102))+' '+(convert(char(9),SendTime,108)),InfoType,Info from SapDB.dbo.TBL_SMSendRecord;*/
exec sql declare SMSendRecord cursor for select AlarmCode,(convert(char(10),SendTime,102))+' '+(convert(char(9),SendTime,108)),InfoType,Info from SapDB.dbo.TBL_SMSendRecord a where not exists(select 1 from SapDB.dbo.TBL_SMSendRecord where InfoType=a.InfoType and SendTime>a.SendTime);
int ReadSMSendRecord(SMSendRecord* pNums, int isFromBegin, int id)
{
	exec sql begin declare section;
 	struct _SMSendRecord
 	{
 		CS_INT 			AlarmCode;
		CS_CHAR	 		SendTime[20];
		CS_INT			InfoType;
		CS_CHAR 		Info[256];
 	} Strategy;
	exec sql end declare section;
    if(0 != isFromBegin)
    {
		exec sql close 	SMSendRecord;
		exec sql open 	SMSendRecord;
		if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    }
	memset(&Strategy, 0, sizeof(Strategy));

    exec sql fetch SMSendRecord into :Strategy.AlarmCode, :Strategy.SendTime,:Strategy.InfoType,:Strategy.Info;
    if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    if(sqlca.sqlcode == 0)
    {
    	memcpy(pNums->Info,Strategy.Info,sizeof((*pNums).Info));
		memcpy(pNums->SendTime,Strategy.SendTime,sizeof((*pNums).SendTime));
		pNums->InfoType=Strategy.InfoType;
		pNums->AlarmCode=Strategy.AlarmCode;
		return 1;
    }
    else if(sqlca.sqlcode == 100)
    {
		/*exec sql close cr4ImsiMsisdn;*/
		return 0;
    }

	return sqlca.sqlcode;
}
 /*   exec sql prepare sqlsa from "insert into SapDB.dbo.TBL_SMSendRecord values(?,?,?,?)";
 exec sql execute sqlsa using :SRecord.AlarmCode, :SRecord.SendTime,:SRecord.InfoType,:SRecord.Info;
*/
int WriteSendRecord(SMSendRecord* Record, int id)
{
    exec sql begin declare section;
	struct _SendRecord
	{
		CS_INT AlarmCode;
		CS_CHAR SendTime[32];
		CS_INT InfoType;
		CS_CHAR Info[256];
	}SRecord;
    exec sql end declare section;

	memcpy(&SRecord.SendTime,Record->SendTime,sizeof(SRecord.SendTime));
	memcpy(SRecord.Info,Record->Info,sizeof(SRecord.Info)>sizeof(Record->Info)?sizeof(Record->Info):sizeof(SRecord.Info));
	memcpy(&SRecord.AlarmCode,&Record->AlarmCode,sizeof(SRecord.AlarmCode));
	memcpy(&SRecord.InfoType,&Record->InfoType,sizeof(SRecord.InfoType));
	exec sql insert into SapDB.dbo.TBL_SMSendRecord (AlarmCode, SendTime, InfoType,Info) values (:SRecord.AlarmCode, :SRecord.SendTime,:SRecord.InfoType,:SRecord.Info);
	if(0!=sqlca.sqlcode)
		return sqlca.sqlcode;
    exec sql commit work;
    return sqlca.sqlcode;
}

exec sql declare AlarmsInfo cursor for select t3.InfoType,t3.AlarmMode,t3.AlarmMode,t3.DelayCount,t3.MSISDN_AlarmList from SapDB.dbo.TBL_Alarms t1,SapDB.dbo.TBL_SMSendStrategy t3,SapDB.dbo.TBL_SMSendRecord t2 where t1.Time>t2.SendTime and convert(int,substring(convert(binary,t1.AlarmCode),0,4),0)=t2.AlarmCode and t3.InfoType=t2.InfoType;
int GetAlarmsInfo(SendStrategy* pNums, int isFromBegin, int id)
{
	exec sql begin declare section;
 	struct _SendStrategy
 	{
 		CS_INT 			InfoType;
		CS_TINYINT	 	AlarmMode;
		CS_INT			DelayTime;
		CS_INT 			DelayCount;
		CS_CHAR 		MSISDN_AlarmList[128];
 	} Strategy;
	exec sql end declare section;
 	if(0 != isFromBegin)
    {
		exec sql close AlarmsInfo;
		exec sql open AlarmsInfo;
		if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    }
	memset(&Strategy, 0, sizeof(Strategy));
	
   	exec sql fetch AlarmsInfo into :Strategy.InfoType, :Strategy.AlarmMode,:Strategy.DelayTime,:Strategy.DelayCount,:Strategy.MSISDN_AlarmList;
    if(sqlca.sqlcode < 0) return sqlca.sqlcode;
    if(sqlca.sqlcode == 0)
    {
    	memcpy(pNums->MSISDN_AlarmList,Strategy.MSISDN_AlarmList,sizeof((*pNums).MSISDN_AlarmList));
		pNums->AlarmMode=Strategy.AlarmMode;
		pNums->DelayCount=Strategy.DelayCount;
		pNums->DelayTime=Strategy.DelayTime;
		pNums->InfoType=Strategy.InfoType;
		return 1;
    }
    else if(sqlca.sqlcode == 100)
    {
		/*exec sql close AlarmsInfo;*/
		return 0;
    }
	return sqlca.sqlcode;
}
int SendGSMOntime(GSMOntime* pOntime, int id)
{
    exec sql begin declare section;
	struct _GSMOntime
	{
		CS_CHAR ACCOUNT[30];
		CS_CHAR SERVICEID[30];
		CS_CHAR CALLED[250];
		CS_CHAR CONTENT[250];
		CS_CHAR SRVPARAM[250];
		CS_CHAR SENDTIME[250];
		CS_CHAR CURSRVDT[19];
		CS_CHAR STARTDT[19];
		CS_CHAR STOPDT[19];
		CS_CHAR REGISTDT[19];
		CS_INT REGIDX;
		CS_INT CONTENTTYPE;
		CS_INT CALLEDTYPE;
		CS_INT SRVTIMES;
		CS_INT TIMESLIMIT;
		CS_INT RESENDCOUNT;
		CS_CHAR REGSRC[30];