int
CAutoTrans::GetTranCount(void)
{
int result = 0;
auto_ptr<CDB_LangCmd> auto_stmt(m_Conn.LangCmd("SELECT @@trancount as tc"));
if (auto_stmt->Send()) {
while(auto_stmt->HasMoreResults()) {
auto_ptr<CDB_Result> rs(auto_stmt->Result());
if (rs.get() == NULL) {
continue;
}
if (rs->ResultType() != eDB_RowResult) {
continue;
}
if (rs->Fetch()) {
CDB_Int tran_count;
rs->GetItem(&tran_count);
result = tran_count.Value();
}
while(rs->Fetch()) {
}
}
}
return result;
}
void
CAutoTrans::Rollback(void)
{
auto_ptr<CDB_LangCmd> auto_stmt(m_Conn.LangCmd("ROLLBACK"));
auto_stmt->Send();
auto_stmt->DumpResults();
}
void
CAutoTrans::Commit(void)
{
auto_ptr<CDB_LangCmd> auto_stmt(m_Conn.LangCmd("COMMIT"));
auto_stmt->Send();
auto_stmt->DumpResults();
}
void
CAutoTrans::BeginTransaction(void)
{
auto_ptr<CDB_LangCmd> auto_stmt(m_Conn.LangCmd("BEGIN TRANSACTION"));
auto_stmt->Send();
auto_stmt->DumpResults();
}
BEGIN_NCBI_SCOPE
///////////////////////////////////////////////////////////////////////////////
BOOST_AUTO_TEST_CASE(Test_Procedure)
{
try {
// Test a regular IStatement with "exec"
// Parameters are not allowed with this construction.
{
auto_ptr<IStatement> auto_stmt( GetConnection().GetStatement() );
// Execute it first time ...
auto_stmt->SendSql( "exec sp_databases" );
while( auto_stmt->HasMoreResults() ) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch ( rs->GetResultType() ) {
case eDB_RowResult:
while( rs->Next() ) {
// int col1 = rs->GetVariant(1).GetInt4();
}
break;
case eDB_ParamResult:
while( rs->Next() ) {
// int col1 = rs->GetVariant(1).GetInt4();
}
break;
case eDB_StatusResult:
while( rs->Next() ) {
int status = rs->GetVariant(1).GetInt4();
status = status;
}
break;
case eDB_ComputeResult:
case eDB_CursorResult:
break;
}
}
}
// Execute it second time ...
auto_stmt->SendSql( "exec sp_databases" );
while( auto_stmt->HasMoreResults() ) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch ( rs->GetResultType() ) {
case eDB_RowResult:
while( rs->Next() ) {
// int col1 = rs->GetVariant(1).GetInt4();
}
break;
case eDB_ParamResult:
while( rs->Next() ) {
// int col1 = rs->GetVariant(1).GetInt4();
}
break;
case eDB_StatusResult:
while( rs->Next() ) {
int status = rs->GetVariant(1).GetInt4();
status = status;
}
break;
case eDB_ComputeResult:
case eDB_CursorResult:
break;
}
}
}
// Same as before but do not retrieve data ...
auto_stmt->SendSql( "exec sp_databases" );
auto_stmt->SendSql( "exec sp_databases" );
}
// Test ICallableStatement
// No parameters at this time.
{
// Execute it first time ...
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("sp_databases")
);
auto_stmt->Execute();
while(auto_stmt->HasMoreResults()) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch( rs->GetResultType() ) {
case eDB_RowResult:
while(rs->Next()) {
// retrieve row results
}
break;
case eDB_ParamResult:
while(rs->Next()) {
// Retrieve parameter row
}
break;
default:
break;
}
}
}
// Get status
auto_stmt->GetReturnStatus();
// Execute it second time ...
auto_stmt.reset( GetConnection().GetCallableStatement("sp_databases") );
auto_stmt->Execute();
while(auto_stmt->HasMoreResults()) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch( rs->GetResultType() ) {
case eDB_RowResult:
while(rs->Next()) {
// retrieve row results
}
break;
case eDB_ParamResult:
while(rs->Next()) {
// Retrieve parameter row
}
break;
default:
break;
}
}
}
// Get status
auto_stmt->GetReturnStatus();
// Same as before but do not retrieve data ...
auto_stmt.reset( GetConnection().GetCallableStatement("sp_databases") );
auto_stmt->Execute();
auto_stmt.reset( GetConnection().GetCallableStatement("sp_databases") );
auto_stmt->Execute();
}
// Temporary test ...
// !!! This is a bug ...
if (false) {
auto_ptr<IConnection> conn( GetDS().CreateConnection( CONN_OWNERSHIP ) );
BOOST_CHECK( conn.get() != NULL );
conn->Connect(
"anyone",
"allowed",
"PUBSEQ_OS_LXA",
""
);
auto_ptr<ICallableStatement> auto_stmt(
conn->GetCallableStatement("id_seqid4gi")
);
auto_stmt->SetParam( CVariant(1), "@gi" );
auto_stmt->Execute();
while(auto_stmt->HasMoreResults()) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch( rs->GetResultType() ) {
case eDB_RowResult:
while(rs->Next()) {
// retrieve row results
}
break;
case eDB_ParamResult:
_ASSERT(false);
while(rs->Next()) {
// Retrieve parameter row
}
break;
default:
break;
}
}
}
// Get status
int status = auto_stmt->GetReturnStatus();
status = status; // Get rid of warnings.
}
if (false) {
const string query("[db_alias] is not null");
auto_ptr<IConnection> conn( GetDS().CreateConnection( CONN_OWNERSHIP ) );
BOOST_CHECK( conn.get() != NULL );
conn->Connect(
"*****",
"******",
"MSSQL31",
"AlignDb_Info"
);
auto_ptr<ICallableStatement> auto_stmt(
conn->PrepareCall("[dbo].[FindAttributesEx]")
);
auto_stmt->SetParam( CVariant(1), "@userid" );
auto_stmt->SetParam( CVariant("ALIGNDB"), "@application" );
auto_stmt->SetParam( CVariant("AlignDbMasterInfo"), "@classname" );
// LongChar doesn't work.
// auto_stmt->SetParam( CVariant(new CDB_LongChar(query.length(), query)), "@query" );
// auto_stmt->SetParam( CVariant::LongChar(query.data(), query.length()), "@query" );
auto_stmt->SetParam( CVariant(query), "@query" );
auto_stmt->SetParam( CVariant(1), "@max_results" );
auto_stmt->Execute();
while(auto_stmt->HasMoreResults()) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch( rs->GetResultType() ) {
case eDB_RowResult:
while(rs->Next()) {
// retrieve row results
}
break;
case eDB_ParamResult:
_ASSERT(false);
while(rs->Next()) {
// Retrieve parameter row
}
break;
default:
break;
}
}
}
// Get status
int status = auto_stmt->GetReturnStatus();
status = status; // Get rid of warnings.
}
// Test returned recordset ...
{
// In case of MS SQL 2005 sp_databases returns empty result set.
// It is not a bug. It is a difference in setiings for MS SQL
// 2005.
if (GetArgs().GetServerType() != CDBConnParams::eMSSqlServer) {
int num = 0;
// Execute it first time ...
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("sp_databases")
);
auto_stmt->Execute();
BOOST_CHECK(auto_stmt->HasMoreResults());
BOOST_CHECK(auto_stmt->HasRows());
auto_ptr<IResultSet> rs(auto_stmt->GetResultSet());
BOOST_CHECK(rs.get() != NULL);
while (rs->Next()) {
BOOST_CHECK(rs->GetVariant(1).GetString().size() > 0);
BOOST_CHECK(rs->GetVariant(2).GetInt4() > 0);
BOOST_CHECK_EQUAL(rs->GetVariant(3).IsNull(), true);
++num;
}
BOOST_CHECK(num > 0);
DumpResults(auto_stmt.get());
}
{
int num = 0;
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("sp_server_info")
);
auto_stmt->Execute();
BOOST_CHECK(auto_stmt->HasMoreResults());
BOOST_CHECK(auto_stmt->HasRows());
auto_ptr<IResultSet> rs(auto_stmt->GetResultSet());
BOOST_CHECK(rs.get() != NULL);
while (rs->Next()) {
BOOST_CHECK(rs->GetVariant(1).GetInt4() > 0);
BOOST_CHECK(rs->GetVariant(2).GetString().size() > 0);
BOOST_CHECK(rs->GetVariant(3).GetString().size() > 0);
++num;
}
BOOST_CHECK(num > 0);
DumpResults(auto_stmt.get());
}
}
// Test ICallableStatement
// With parameters.
{
{
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("sp_server_info")
);
// Set parameter to NULL ...
auto_stmt->SetParam( CVariant(eDB_Int), "@attribute_id" );
auto_stmt->Execute();
if (GetArgs().GetServerType() == CDBConnParams::eSybaseSQLServer) {
BOOST_CHECK_EQUAL( size_t(30), GetNumOfRecords(auto_stmt) );
} else {
BOOST_CHECK_EQUAL( size_t(29), GetNumOfRecords(auto_stmt) );
}
// Set parameter to 1 ...
auto_stmt->SetParam( CVariant( Int4(1) ), "@attribute_id" );
auto_stmt->Execute();
BOOST_CHECK_EQUAL( size_t(1), GetNumOfRecords(auto_stmt) );
}
// NULL value with CVariant ...
{
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("sp_statistics")
);
auto_stmt->SetParam(CVariant((const char*) NULL), "@table_name");
auto_stmt->Execute();
DumpResults(auto_stmt.get());
}
// Doesn't work for some reason ...
if (false) {
// Execute it first time ...
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("sp_statistics")
);
auto_stmt->SetParam(CVariant(GetTableName()), "@table_name");
auto_stmt->Execute();
{
BOOST_CHECK(auto_stmt->HasMoreResults());
BOOST_CHECK(auto_stmt->HasRows());
auto_ptr<IResultSet> rs(auto_stmt->GetResultSet());
BOOST_CHECK(rs.get() != NULL);
BOOST_CHECK(rs->Next());
DumpResults(auto_stmt.get());
}
// Execute it second time ...
auto_stmt->SetParam(CVariant("#bulk_insert_table"), "@table_name");
auto_stmt->Execute();
{
BOOST_CHECK(auto_stmt->HasMoreResults());
BOOST_CHECK(auto_stmt->HasRows());
auto_ptr<IResultSet> rs(auto_stmt->GetResultSet());
BOOST_CHECK(rs.get() != NULL);
BOOST_CHECK(rs->Next());
DumpResults(auto_stmt.get());
}
}
if (false) {
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("DBAPI_Sample..TestBigIntProc")
);
auto_stmt->SetParam(CVariant(Int8(1234567890)), "@num");
auto_stmt->ExecuteUpdate();
}
}
// Test output parameters ...
if (false) {
CRef<CDB_UserHandler_Diag> handler(new CDB_UserHandler_Diag());
I_DriverContext* drv_context = GetDS().GetDriverContext();
drv_context->PushDefConnMsgHandler(handler);
auto_ptr<ICallableStatement> auto_stmt(
GetConnection().GetCallableStatement("DBAPI_Sample..SampleProc3")
);
auto_stmt->SetParam(CVariant(1), "@id");
auto_stmt->SetParam(CVariant(2.0), "@f");
auto_stmt->SetOutputParam(CVariant(eDB_Int), "@o");
auto_stmt->Execute();
// auto_stmt->SendSql( "exec DBAPI_Sample..TestProc4 @test_out output" );
while(auto_stmt->HasMoreResults()) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch( rs->GetResultType() ) {
case eDB_RowResult:
while(rs->Next()) {
// retrieve row results
}
break;
case eDB_ParamResult:
BOOST_CHECK(rs->Next());
NcbiCout << "Output param: "
<< rs->GetVariant(1).GetInt4()
<< endl;
break;
case eDB_ComputeResult:
break;
case eDB_StatusResult:
break;
case eDB_CursorResult:
break;
default:
break;
}
}
}
// BOOST_CHECK(auto_stmt->HasMoreResults());
// BOOST_CHECK(auto_stmt->HasRows());
// auto_ptr<IResultSet> rs(auto_stmt->GetResultSet());
// BOOST_CHECK(rs.get() != NULL);
//
// while (rs->Next()) {
// BOOST_CHECK(rs->GetVariant(1).GetString().size() > 0);
// BOOST_CHECK(rs->GetVariant(2).GetInt4() > 0);
// BOOST_CHECK_EQUAL(rs->GetVariant(3).IsNull(), true);
// ++num;
// }
//
// BOOST_CHECK(num > 0);
DumpResults(auto_stmt.get());
drv_context->PopDefConnMsgHandler(handler);
}
// Temporary test ...
if (false) {
auto_ptr<IConnection> conn( GetDS().CreateConnection( CONN_OWNERSHIP ) );
BOOST_CHECK( conn.get() != NULL );
conn->Connect(
"anyone",
"allowed",
"",
"GenomeHits"
);
auto_ptr<ICallableStatement> auto_stmt(
conn->GetCallableStatement("NewSub")
);
auto_stmt->SetParam(CVariant("tsub2"), "@name");
auto_stmt->SetParam(CVariant("tst"), "@center");
auto_stmt->SetParam(CVariant("9606"), "@taxid");
auto_stmt->SetParam(CVariant("H**o sapiens"), "@organism");
auto_stmt->SetParam(CVariant(""), "@notes");
auto_stmt->Execute();
while(auto_stmt->HasMoreResults()) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch( rs->GetResultType() ) {
case eDB_RowResult:
while(rs->Next()) {
// retrieve row results
}
break;
case eDB_ParamResult:
_ASSERT(false);
while(rs->Next()) {
// Retrieve parameter row
}
break;
default:
break;
}
}
}
// Get status
int status = auto_stmt->GetReturnStatus();
status = status; // Get rid of warnings.
}
// Temporary test ...
if (false && GetArgs().GetServerType() != CDBConnParams::eSybaseSQLServer) {
auto_ptr<IConnection> conn( GetDS().CreateConnection( CONN_OWNERSHIP ) );
BOOST_CHECK( conn.get() != NULL );
conn->Connect(
"pmcupdate",
"*******",
"PMC3QA",
"PMC3QA"
);
auto_ptr<ICallableStatement> auto_stmt(
conn->PrepareCall("id_new_id")
);
auto_stmt->SetParam(CVariant("tsub2"), "@IdName");
auto_stmt->Execute();
while(auto_stmt->HasMoreResults()) {
if( auto_stmt->HasRows() ) {
auto_ptr<IResultSet> rs( auto_stmt->GetResultSet() );
switch( rs->GetResultType() ) {
case eDB_RowResult:
while(rs->Next()) {
// retrieve row results
}
break;
case eDB_ParamResult:
_ASSERT(false);
while(rs->Next()) {
// Retrieve parameter row
}
break;
default:
break;
}
}
}
// Get status
int status = auto_stmt->GetReturnStatus();
status = status; // Get rid of warnings.
}
}
catch(const CException& ex) {
DBAPI_BOOST_FAIL(ex);
}
}
int
CDemoApp::RunSample(void)
{
IDataSource* data_source = NULL;
try {
DBLB_INSTALL_DEFAULT();
CDriverManager& dm = CDriverManager::GetInstance();
#ifdef FTDS_IN_USE
DBAPI_RegisterDriver_FTDS();
data_source = dm.CreateDs("ftds");
#else
DBAPI_RegisterDriver_CTLIB();
data_source = dm.CreateDs("ctlib");
#endif
unique_ptr<IConnection> conn(data_source->CreateConnection());
conn->Connect(GetUserName(),
GetPassword(),
GetServerName()
);
unique_ptr<ICallableStatement> auto_stmt(conn->GetCallableStatement("sp_databases"));
// unique_ptr<ICallableStatement> auto_stmt(conn->GetCallableStatement("sp_who"));
auto_stmt->Execute();
while(auto_stmt->HasMoreResults()) {
if(auto_stmt->HasRows()) {
unique_ptr<IResultSet> rs(auto_stmt->GetResultSet());
unique_ptr<const IResultSetMetaData> md(rs->GetMetaData());
switch(rs->GetResultType()) {
case eDB_RowResult:
while(rs->Next()) {
for (unsigned int j = 1; j <= rs->GetTotalColumns(); ++j) {
const CVariant& value(rs->GetVariant(j));
const EDB_Type rt(md->GetType(j));
if (rt == eDB_Char || rt == eDB_VarChar) {
cout << md->GetName(j) << ": "
<< (value.IsNull()? "" : value.GetString())
<< " \t";
} else if (rt == eDB_Int ||
rt == eDB_SmallInt ||
rt == eDB_TinyInt) {
cout << md->GetName(j) << ": " << value.GetInt8()
<< ' ';
}
}
cout << endl;
}
break;
case eDB_ParamResult:
while(rs->Next()) {
// Retrieve parameter row
}
break;
default:
break;
}
}
}
} catch (CDB_Exception& e) {
CDB_UserHandler_Stream myExHandler(&cerr);
myExHandler.HandleIt(&e);
return 1;
} catch (const CException&) {
return 1;
}
return 0;
}
