void main() /* Thread One */
{
/* Get display screen information & clear the screen.*/
hConsoleOut = GetStdHandle(STD_OUTPUT_HANDLE);
WriteTitle();
/* Create the mutexes and reset thread count. */
hScreenMutex = CreateMutex(NULL, FALSE, NULL); /* Cleared */
hQueryMutex = CreateMutex(NULL, FALSE, NULL); /* Cleared */
try
{
// connect
g_Conn.Connect(DATABASE, USER, PASSWORD, CLIENT);
/* Start waiting for keyboard input to dispatch threads or exit. */
KbdFunc();
}
catch(SAException &x)
{
std::cout << (const char*)x.ErrText() << std::endl;
try
{
g_Conn.Rollback();
}
catch(SAException &)
{
}
}
/* All threads done. Clean up handles. */
CloseHandle(hScreenMutex);
CloseHandle(hQueryMutex);
CloseHandle(hConsoleOut);
}
void Oracle_Cancel()
{
SAConnection con2;
SAConnection con;
SACommand cmd2;
SACommand cmd;
cmd2.setConnection(&con2);
cmd.setConnection(&con);
try
{
con2.Connect(
"test", "scott", "tiger", SA_Oracle_Client);
//con.setOption("UseAPI") = "OCI7";
con.Connect(
"test", "scott", "tiger", SA_Oracle_Client);
// block
cmd2.setCommandText("Update DEPT set DNAME=DNAME");
cmd2.Execute();
DWORD ThreadId;
HANDLE hThread;
hThread = ::CreateThread(
NULL, 0,
Oracle_Cancel_Thread, &con,
0, &ThreadId);
WaitForSingleObject(hThread, 1000);
cout << "Calling Cancel!" << endl;
cmd.Open();
cmd.Cancel();
cout << "Cancel has been called!" << endl;
WaitForSingleObject(hThread, 2000);
cout << "Calling con2.Rollback()!" << endl;
con2.Rollback();
cout << "con2.Rollback() has been called!" << endl;
WaitForSingleObject(hThread, INFINITE);
}
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
cout << (const char*)x.ErrText() << endl;
}
}
int main(int argc, char* argv[])
{
SAConnection con; // connection object
SACommand cmd; // command object
try
{
// connect to database (Oracle in our example)
con.Connect("test", "tester", "tester", SA_Oracle_Client);
// associate a command with connection
cmd.setConnection(&con);
// Insert 2 rows
cmd.setCommandText(
"Insert into test_tbl(fid, fvarchar20) values(:1, :2)");
// use first method of binding - param assignment
cmd.Param(1).setAsLong() = 2;
cmd.Param(2).setAsString() = "Some string (2)";
// Insert first row
cmd.Execute();
// use second method of binding - stream binding
cmd << (long)3 << "Some string (3)";
// Insert second row
cmd.Execute();
// commit changes on success
con.Commit();
printf("Input parameters bound, rows inserted!\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("%s\n", (const char*)x.ErrText());
}
return 0;
}
int main(int argc, char* argv[])
{
SAConnection con; // connection object
SACommand cmd; // create command object
try
{
// connect to database (Oracle in our example)
con.Connect("test", "tester", "tester", SA_Oracle_Client);
// associate a command with connection
// connection can also be specified in SACommand constructor
cmd.setConnection(&con);
// create table
cmd.setCommandText(
"Create table test_tbl(fid integer, fvarchar20 varchar(20), fblob blob)");
cmd.Execute();
// insert value
cmd.setCommandText(
"Insert into test_tbl(fid, fvarchar20) values (1, 'Some string (1)')");
cmd.Execute();
// commit changes on success
con.Commit();
printf("Table created, row inserted!\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("%s\n", (const char*)x.ErrText());
}
return 0;
}
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;
}
void OracleRefCursor()
{
SAConnection con;
try
{
SACommand cmd(&con);
con.Connect(
"demo", "scott", "tiger", SA_Oracle_Client);
cout << "Connected OK!" << "\n";
cmd.setCommandText("TestRefCursorPkg.TestRefCursorProc");
cmd.Execute();
cout << "Stored procedure executed OK!" << "\n";
SACommand *pRefCursor = cmd.Param("REFCURSOR");
while(pRefCursor->FetchNext())
{
cout
<< (const char*)pRefCursor->Field(1).Name() << "="
<< (const char*)pRefCursor->Field(1).asString() << "\n";
}
cout << "Ref cursor 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
cout << (const char*)x.ErrText() << "\n";
}
}
CDbDataReader CDbCommand::_queryInternal(const CCommandParameterMap & params) throw (CDbException)
{
mergeParametersWith(params);
string parametersDump = _makeParametersDump(_params);
#ifdef CWS_DEBUG
Cws::trace("Executing SQL: " + _text + parametersDump, "system.db.CDbCommand");
#endif
try {
SAConnection * connection = _connection->getConnection();
_saCommand = new SACommand(connection, _text.c_str(), SA_CmdSQLStmt);
if (connection->Client() == SA_MySQL_Client) {
_saCommand->setOption("UseStatement") = "TRUE";
}
for (CCommandParameterMap::const_iterator iter = _params.begin(); iter != _params.end(); ++iter) {
iter->second.get()->bind(_saCommand);
}
PROFILE_BEGIN("Executing SQL: " + _text + parametersDump);
_saCommand->Execute();
PROFILE_END();
return CDbDataReader(_saCommand);
} catch (const SAException & e) {
string message = e.ErrText().GetMultiByteChars();
stringstream ss;
ss << "CDbCommand::_queryInternal() failed: " << message
<< ". Code: " << (int)e.ErrNativeCode();
Cws::log(
"CDbCommand::_queryInternal() failed: " + ss.str()
+ ". The SQL statement executed was: " + _text + "."
);
#ifdef CWS_DEBUG
message += ". The SQL statement executed was: " + _text + parametersDump;
#endif
throw CDbException(
"CDbCommand failed to execute the SQL statement: " + message,
(int)e.ErrNativeCode()
);
}
}
//测试数据库连接
static bool testDbsConfig()
{
SAConnection saCon;
SAString sDatabase = _TSA(g_Cfg.DbsProp.TnsName);
SAString sUsername, sPassword;
for (int i=0; i<3; i++)
{
cout << g_Cfg.DbsProp.LgnName <<endl;
cout << g_Cfg.DbsProp.TnsName << endl;
if ((0 == strlen(g_Cfg.DbsProp.LgnName)) || (0 == strlen(g_Cfg.DbsProp.LgnPwd)))
{
cout << "### Database Configuration for [TNS_NAME: " << g_Cfg.DbsProp.TnsName << "] ###" << endl;
cout << "Please Input User Name: ";
cin.getline(g_Cfg.DbsProp.LgnName, sizeof(g_Cfg.DbsProp.LgnName));
cout << "Please Input User Password: "******"SQLAPI exception " << _T(x.ErrText()) << endl;
}
cin.clear();
memset(g_Cfg.DbsProp.LgnPwd, 0, sizeof(g_Cfg.DbsProp.LgnPwd));
}
return saCon.isConnected();
}
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());
}
}
long unsigned int CDbCommand::execute(const CCommandParameterMap & params) throw (CDbException)
{
mergeParametersWith(params);
string parametersDump = _makeParametersDump(_params);
#ifdef CWS_DEBUG
Cws::trace("Executing SQL: " + _text + parametersDump, "system.db.CDbCommand");
#endif
try {
SAConnection * connection = _connection->getConnection();
_saCommand = new SACommand(connection);
_saCommand->setCommandText(_text.c_str());
for (CCommandParameterMap::const_iterator iter = _params.begin(); iter != _params.end(); ++iter) {
iter->second.get()->bind(_saCommand);
}
PROFILE_BEGIN("Executing SQL: " + _text + parametersDump);
_saCommand->Execute();
connection->Commit();
PROFILE_END();
return _saCommand->RowsAffected();
} catch (const SAException & e) {
string message = e.ErrText().GetMultiByteChars();
Cws::log(
"CDbCommand::execute() failed: " + message
+ ". The SQL statement executed was: " + _text + "."
);
#ifdef CWS_DEBUG
message += ". The SQL statement executed was: " + _text + parametersDump;
#endif
throw CDbException(
"CDbCommand failed to execute the SQL statement: " + message,
(int)e.ErrNativeCode()
);
}
return 0;
}
void InterBaseEvents()
{
SAConnection con;
SACommand cmd;
event_data_t event_data;
try
{
con.Connect(
"transfer:d:/temp/siemens/controller.gdb", // database name
"sysdba", // user name
"masterkey", // password
SA_InterBase_Client);
event_data.pibAPI = (ibAPI *)con.NativeAPI();
event_data.pibConnectionHandles = (ibConnectionHandles *)con.NativeHandles();
con.setAutoCommit(SA_AutoCommitOn);
// register interest for "TestEvent" event
event_data.length = event_data.pibAPI->isc_event_block(
&event_data.event_buffer,
&event_data.result_buffer,
1,
"TestEvent");
event_data.que_events();
// this should fire event
cmd.setConnection(&con);
cmd.setCommandText("a");
cmd.Param("sEvent").setAsString() = "TestEvent";
cmd.Execute();
// wait for event to be fired
getchar();
}
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("Err = %s\nCode = %d\n", (const char*)x.ErrText(),
x.ErrNativeCode());
}
}
//CMD_TRSACTION
//请求: [B0: 协议字] [B1: 0x0-Rollback, 0x1-StartTrsaction, 0x2-Commit]
//响应: [B0: 协议字] [B1: 执行结果0|1]
int OnTrsaction(QUEUE_LIST *pDataNode)
{
#ifndef _SUPPORT_CLITR //不支持客户端事物
setCmdError(pDataNode);
return 1;
#endif
//客户端事务连接链表
CTrastionList *pTrList = pDataNode->pCliTr;
SAConnection *psaConn = pTrList->getCliTrConn(pDataNode->SrvrPort, pDataNode->PeerSockId);
//请求报文(MSG)
CliTrsaction_t Req = (CliTrsaction_t)pDataNode->Buffer[1];
switch (Req)
{
case TR_ROLLBACK:
if (NULL == psaConn)
setCmdError(pDataNode);
else
{
psaConn->Rollback();
//删除客户端事物连接
pTrList->delCliTrConn(pDataNode->SrvrPort, pDataNode->PeerSockId);
setCmdSucceed(pDataNode);
}
break;
case TR_START:
if (NULL == psaConn)
{
//新建客户端事物连接
TRSACTION_NODE *pNode = (TRSACTION_NODE *) GetMem(sizeof(TRSACTION_NODE));
pNode->SrvrPort = pDataNode->SrvrPort;
pNode->PeerSockId = pDataNode->PeerSockId;
pNode->psaConn = new SAConnection();
if (isDBConnect(pNode->psaConn, pDataNode->thd_info))
{
pTrList->PushHead((void *)pNode); //加入节点到链表
}
else
{
delete pNode->psaConn;
FreeMem(pNode);
setCmdError(pDataNode);
return 1;
}
}
setCmdSucceed(pDataNode);
break;
case TR_COMMIT:
if (NULL == psaConn)
setCmdError(pDataNode);
else
{
psaConn->Commit();
//删除客户端事物连接
pTrList->delCliTrConn(pDataNode->SrvrPort, pDataNode->PeerSockId);
setCmdSucceed(pDataNode);
}
break;
default: return 0;
}
return 1;
}
void Oracle_OCI_ATTR_CHARSET_FORM_OCI_ATTR_CHARSET_ID()
{
SAConnection con;
SACommand cmd;
cmd.setConnection(&con);
try
{
con.Connect("cit", "dwadm", "dwadm", SA_Oracle_Client);
bool bDropTable = !false;
bool bCreateTable = !false;
if(bDropTable)
{
cmd.setCommandText(
"drop table test_charset");
cmd.Execute();
}
if(bCreateTable)
{
cmd.setCommandText(
"Create table test_charset (f1 nchar(254), f2 char(254))");
cmd.Execute();
}
cmd.setCommandText("delete from test_charset");
cmd.Execute();
cmd.setCommandText("insert into test_charset values (:1, :2)");
cmd.Param(1).setOption("OCI_ATTR_CHARSET_FORM") = "SQLCS_NCHAR";
cmd.Param(1).setOption("OCI_ATTR_CHARSET_ID") = "171";
cmd.Param(2).setOption("OCI_ATTR_CHARSET_ID") = "CL8MSWIN1251";
cmd.Param(1).setAsString() = "string f1 - 1";
cmd.Param(2).setAsValueRead() = cmd.Param(1);
cmd.Execute();
cmd.Param(1).setAsString() = "������ f1 - 2";
cmd.Param(2).setAsValueRead() = cmd.Param(1);
cmd.Execute();
cmd.setCommandText("select * from test_charset");
cmd.Execute();
cmd.Field(1).setOption("OCI_ATTR_CHARSET_ID") = "171";
cmd.Field(2).setOption("OCI_ATTR_CHARSET_ID") = "CL8MSWIN1251";
while(cmd.FetchNext())
{
cout
<< (const char*)cmd[1].asString()
<< ""
<< (const char*)cmd[2].asString()
<< endl;
}
}
catch(SAException &x)
{
try
{
con.Rollback();
}
catch(SAException &)
{
}
printf("%s\n", (const char*)x.ErrText());
}
}
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;
}
