/**< 获�数�库连接 */
int TransProcess::GetConn()
{
try{
dbConn.Connect(_TSA(g_Cfg.DbsProp.TnsName), _TSA(g_Cfg.DbsProp.LgnName), _TSA(g_Cfg.DbsProp.LgnPwd), SA_Oracle_Client);
return 0;
}catch(SAException &x){
trace_log(ERR, "SQLAPI Exception %s", _T(x.ErrText()));
return -1;
}catch(...){
trace_log(SYS, "Error in ransProcess::GetConn()");
return -1;
}
}
int SQLServer_CE_GUID()
{
SAConnection con;
con.setOption(_TSA("OLEDBProvider")) = _TSA("CompactEdition");
con.setOption(_TSA("CreateDatabase")) = _TSA("TRUE");
try
{
con.Connect(
_TSA("C:\\test.sdf"), _TSA("sa"), _TSA("sa"), SA_SQLServer_Client);
printf("SQLCE Connected OK!\n");
SACommand cmd(&con, _TSA("create table t1(f1 uniqueidentifier, f2 nvarchar(100))"));
cmd.Execute();
cmd.setCommandText(_TSA("insert into t1(f1, f2) values('{6F9619FF-8B86-D011-B42D-00C04FC964FF}','test')"));
cmd.Execute();
cmd.setCommandText(_TSA("select * from t1"));
cmd.Execute();
printf("SQLCE query executed OK!\n");
while(cmd.FetchNext())
{
printf("%s=%s\n",
cmd.Field(1).Name().GetMultiByteChars(),
cmd.Field(1).asString().GetMultiByteChars());
printf("%s=%s\n\n",
cmd.Field(2).Name().GetMultiByteChars(),
cmd.Field(2).asString().GetMultiByteChars());
}
printf("SQLCE data fetched OK!\n");
}
catch(SAException &x)
{
try
{
con.Rollback();
}
catch(SAException &)
{
}
printf("ERROR: %s\n", x.ErrText().GetMultiByteChars());
}
return 0;
}
bool isDBConnect(SAConnection *psaConn, char * slocale)
{
if (NULL == psaConn)
return false;
#ifdef _WIN32_
if (0 != g_LastExceptSQLAPIaddr && (int)psaConn != g_LastExceptSQLAPIaddr)
return false;
pthread_mutex_lock(&g_SQLAPI_lock);
#endif
try
{
if (!psaConn->isAlive()) //不要使用isConneted()进行判断! 在SAConnection被动断开连接时，isConnected()函数无效
{
trace_log(LOG_DEBUG,"%sSQLAPI[%p][%s][%s][%s] Connecting...", slocale, psaConn, g_Cfg.DbsProp.TnsName,g_Cfg.DbsProp.LgnName,g_Cfg.DbsProp.LgnPwd);
psaConn->Connect(_TSA(g_Cfg.DbsProp.TnsName), _TSA(g_Cfg.DbsProp.LgnName), _TSA(g_Cfg.DbsProp.LgnPwd), SA_Oracle_Client); //连接数据库
}
if (psaConn->isAlive())
{
psaConn->setAutoCommit(SA_AutoCommitOn); //恢复[自动提交]方式(因是共享连接，则有可能在其他线程中因业务原因修改[为手工提交])
#ifdef _WIN32_
g_LastExceptSQLAPIaddr = 0;
pthread_mutex_unlock(&g_SQLAPI_lock);
#endif
return true;
}
}
catch(SAException &x)
{
trace_log(LOG_WARNING,"%sWARNING! SQLAPI[%p] Exception: %s",slocale, psaConn, _T(x.ErrText()));
#ifdef _WIN32_
g_LastExceptSQLAPIaddr = (int)psaConn;
#endif
}
#ifdef _WIN32_
pthread_mutex_unlock(&g_SQLAPI_lock);
#endif
return false;
}
SAConnection * DBObject::getDBConn(char *strlsn, char * struname, char * strpwd)
{
if(NULL == strlsn || NULL == struname || NULL == strpwd)
{
trace_log(ERR, "DB parameter Error.");
return NULL;
}
saDBconn = new SAConnection();
try{
saDBconn->Connect(_TSA(strlsn), _TSA(struname), _TSA(strpwd), SA_Oracle_Client);
//trace_log(DBG,"DBConn success");
return saDBconn;
}catch(SAException &x){
trace_log(ERR, "SQLAPI Exception %s", _T(x.ErrText()));
return NULL;
}catch(...){
trace_log(SYS, "Error in Dataprocess::GetConn()");
return NULL;
}
}
int main(int/* argc*/, char** /* argv[]*/)
{
// setlocale(LC_ALL, "");
SAConnection con;
try
{
con.Connect("test", "", "", SA_DB2_Client);
long nVersionServer = con.ServerVersion();
short minor = (short)(nVersionServer & 0xFFFF);
short major = (short)(nVersionServer >> 16);
sa_printf(_TSA("Server: %") SA_FMT_STR _TSA("\n"), (const SAChar*)con.ServerVersionString());
sa_printf(_TSA("Server version: %hd.%hd\n"), major, minor);
long nVersionClient = con.ClientVersion();
if(nVersionClient)
{
short minor = (short)(nVersionClient & 0xFFFF);
short major = (short)(nVersionClient >> 16);
sa_printf(_TSA("Client version: %hd.%hd\n"), major, minor);
}
else
{
sa_printf(_TSA("Client version: unknown after connection\n"));
}
SACommand cmd(&con, "select f1,f2 from t1");
cmd.Execute();
while( cmd.FetchNext() )
{
sa_printf(_TSA("Row fetched\n"));
sa_printf(_TSA("F1: %d, F2: %") SA_FMT_STR _TSA("\n"), cmd[1].asLong(), (const SAChar*)cmd[2].asString());
}
}
//CMD_EXECSQL
//远程客户端请求执行SQL
//请求: [B0: 协议字] [B1:是否需要返回结果集] [B2~B5: 请求的起始记录号] [B6~B9: 最大返回的记录数] [B10~Bn: SQL语句]
//响应: [B0: 协议字] [B1: 执行结果0|1] [B2: 是否还有后续数据0|1] [B3..Bn: CDS结果集]
int OnExecSQL(QUEUE_LIST *pDataNode)
{
#ifdef _SUPPORT_CLITR //支持客户端事务连接
if (NULL != pDataNode->psaConn)
{
SAConnection *p = pDataNode->pCliTr->getCliTrConn(pDataNode->SrvrPort, pDataNode->PeerSockId);
if (p) pDataNode->psaConn = p;
}
#endif
//报文(MSG)
unsigned char *p = pDataNode->Buffer;
p++;
int iStartRecNo, iMaxCount, iSqlLen;
bool bNeedReturnData = (*p == 1);
p++;
P2DW(iStartRecNo, p);
p += 4;
P2DW(iMaxCount, p);
p += 4;
P2W(iSqlLen, p);
p += 2;
if (iSqlLen > 8 * ONE_PACKSIZE)
{
setCmdError(pDataNode);
return 1;
}
unsigned char *pSQL = (unsigned char *) GetMem(iSqlLen + 1);
memcpy(pSQL, p, iSqlLen);
pSQL[iSqlLen] = 0;
thd_log(LOG_DEBUG,"Execute SQL: %s", pSQL);
SACommand saCmd;
try{
saCmd.setConnection(pDataNode->psaConn);
saCmd.setCommandText(_TSA(pSQL));
saCmd.Execute();
}
catch(SAException &x)
{
thd_log( LOG_ERROR,(char *)"SQLAPI Exception %s", _T(x.ErrText()));
setCmdError(pDataNode);
return 1;
}
p = &pDataNode->Buffer[1];
int iOutLen = 1;
*p++ = CMD_OK; //执行SQL成功
iOutLen++;
*p++ = 0x0;
iOutLen++;
//不需要返回数据
if (!bNeedReturnData)
{
//m_pDataNode->psaConn->Commit();
int rowUpd = saCmd.RowsAffected(); //更新的记录数
W2P(p, rowUpd);
iOutLen += 2;
pDataNode->Length = iOutLen;
FreeMem(pSQL);
return 1;
}
bool bHasNext = false;
p += 2;
//返回数据
int iRet = CopyDataSet(&bHasNext, p, ALL_PACKSIZE, &saCmd, iStartRecNo, iMaxCount);
p -= 2;
W2P(p, iRet); //记录集大小
if (bHasNext) pDataNode->Buffer[2] = 0x1; //存在未返回记录
iOutLen += 2 + iRet;
pDataNode->Length = iOutLen;
thd_log(LOG_DEBUG,"cdsfmt2 len = %d",pDataNode->Length);
trace_mem( LOG_DEBUG,pDataNode->Buffer, pDataNode->Length);
FreeMem(pSQL);
return 1;
}
int SQLServer_CE()
{
SAConnection con;
SAConnection conCE;
conCE.setOption(_TSA("UseAPI")) = _TSA("OLEDB");
conCE.setOption(_TSA("OLEDBProvider")) = _TSA("CompactEdition");
conCE.setOption(_TSA("CreateDatabase")) = _TSA("TRUE");
try
{
con.Connect(
_TSA("srv2@pubs"), _TSA("sa"), _TSA("sa"), SA_SQLServer_Client);
printf("SQLServer Connected OK!\n");
conCE.Connect(
_TSA("C:\\test.sdf"), _TSA("sa"), _TSA("sa"), SA_SQLServer_Client);
printf("SQLCE Connected OK!\n");
SACommand cmd(&con, _TSA("select user"));
cmd.Execute();
printf("SQLServer query executed OK!\n");
while(cmd.FetchNext())
{
printf("%s=%s\n",
cmd.Field(1).Name().GetMultiByteChars(),
cmd.Field(1).asString().GetMultiByteChars());
}
printf("SQLServer data fetched OK!\n");
SACommand cmdCE(&conCE, _TSA("create table t1(f1 nvarchar(20))"));
cmdCE.Execute();
cmdCE.setCommandText(_TSA("insert into t1(f1) values('test')"));
cmdCE.Execute();
cmdCE.setCommandText(_TSA("select * from t1"));
cmdCE.Execute();
printf("SQLCE query executed OK!\n");
while(cmdCE.FetchNext())
{
printf("%s=%s\n",
cmdCE.Field(1).Name().GetMultiByteChars(),
cmdCE.Field(1).asString().GetMultiByteChars());
}
printf("cmdCE data fetched OK!\n");
}
catch(SAException &x)
{
// SAConnection::Rollback()
// can also throw an exception
// (if a network error for example),
// we will be ready
try
{
// on error rollback changes
con.Rollback();
}
catch(SAException &)
{
}
// print error message
printf("ERROR: %s\n", x.ErrText().GetMultiByteChars());
}
return 0;
}
