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);
}
}
BEGIN_NCBI_SCOPE
///////////////////////////////////////////////////////////////////////////////
BOOST_AUTO_TEST_CASE(Test_DateTime)
{
string sql;
CQuery query = GetDatabase().NewQuery();
CTime t;
CTime dt_value;
try {
if (true) {
// Initialization ...
{
sql =
"CREATE TABLE #test_datetime ( \n"
" id INT, \n"
" dt_field DATETIME NULL \n"
") \n";
query.SetSql( sql );
query.Execute();
query.RequireRowCount(0);
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
}
{
// Initialization ...
{
sql = "INSERT INTO #test_datetime(id, dt_field) "
"VALUES(1, GETDATE() )";
query.SetSql( sql );
query.Execute();
query.RequireRowCount(0);
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
}
// Retrieve data ...
{
sql = "SELECT * FROM #test_datetime";
query.SetSql( sql );
query.Execute();
query.RequireRowCount(1);
BOOST_CHECK( query.HasMoreResultSets() );
CQuery::iterator it = query.begin();
BOOST_CHECK( it != query.end() );
BOOST_CHECK( !it[2].IsNull());
dt_value = it[2].AsDateTime();
BOOST_CHECK( !dt_value.IsEmpty() );
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
}
// Insert data using parameters ...
{
query.SetSql( "DELETE FROM #test_datetime" );
query.Execute();
query.RequireRowCount(0);
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
query.SetParameter( "@dt_val", dt_value );
sql = "INSERT INTO #test_datetime(id, dt_field) "
"VALUES(1, @dt_val)";
query.SetSql( sql );
query.Execute();
query.RequireRowCount(0);
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
}
// Retrieve data again ...
{
sql = "SELECT * FROM #test_datetime";
// ClearParamList is necessary here ...
query.ClearParameters();
query.SetSql( sql );
query.Execute();
query.RequireRowCount(1);
BOOST_CHECK( query.HasMoreResultSets() );
CQuery::iterator it = query.begin();
BOOST_CHECK( it != query.end() );
BOOST_CHECK( !it[2].IsNull());
CTime dt_value2 = it[2].AsDateTime();
BOOST_CHECK_EQUAL( dt_value.AsString(), dt_value2.AsString() );
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
}
// Insert NULL data using parameters ...
{
query.SetSql( "DELETE FROM #test_datetime" );
query.Execute();
query.RequireRowCount(0);
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
query.SetNullParameter( "@dt_val", eSDB_DateTime );
sql = "INSERT INTO #test_datetime(id, dt_field) "
"VALUES(1, @dt_val)";
query.SetSql( sql );
query.Execute();
query.RequireRowCount(0);
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
}
// Retrieve data again ...
{
sql = "SELECT * FROM #test_datetime";
// ClearParamList is necessary here ...
query.ClearParameters();
query.SetSql( sql );
query.Execute();
query.RequireRowCount(1);
BOOST_CHECK( query.HasMoreResultSets() );
CQuery::iterator it = query.begin();
BOOST_CHECK( it != query.end() );
BOOST_CHECK( it[2].IsNull());
BOOST_CHECK_NO_THROW(query.VerifyDone(CQuery::eAllResultSets));
}
}
}
}
catch(const CException& ex) {
DBAPI_BOOST_FAIL(ex);
}
}