dbapi_sample.cpp

ncbi源码

/*
 * ===========================================================================
 * PRODUCTION $Log: dbapi_sample.cpp,v $
 * PRODUCTION Revision 1000.2  2004/06/01 18:31:51  gouriano
 * PRODUCTION PRODUCTION: UPGRADED [GCC34_MSVC7] Dev-tree R1.12
 * PRODUCTION
 * ===========================================================================
 */

/*  $Id: dbapi_sample.cpp,v 1000.2 2004/06/01 18:31:51 gouriano Exp $
* ===========================================================================
*
*                            PUBLIC DOMAIN NOTICE
*               National Center for Biotechnology Information
*
*  This software/database is a "United States Government Work" under the
*  terms of the United States Copyright Act.  It was written as part of
*  the author's official duties as a United States Government employee and
*  thus cannot be copyrighted.  This software/database is freely available
*  to the public for use. The National Library of Medicine and the U.S.
*  Government have not placed any restriction on its use or reproduction.
*
*  Although all reasonable efforts have been taken to ensure the accuracy
*  and reliability of the software and data, the NLM and the U.S.
*  Government do not and cannot warrant the performance or results that
*  may be obtained by using this software or data. The NLM and the U.S.
*  Government disclaim all warranties, express or implied, including
*  warranties of performance, merchantability or fitness for any particular
*  purpose.
*
*  Please cite the author in any work or product based on this material.
*
* ===========================================================================
*
* Author: Michael Kholodov
*
* File Description:
*   String representation of the database character types.
*
* ===========================================================================
*/


#include <ncbi_pch.hpp>
#include <corelib/ncbiapp.hpp>
#include <corelib/ncbiargs.hpp>
#include <corelib/ncbienv.hpp>

#include <dbapi/dbapi.hpp>
#include <dbapi/driver/drivers.hpp>
#include <vector>

USING_NCBI_SCOPE;

/////////////////////////////////////////////////////////////////////////////
//  MAIN

class CDbapiTest : public CNcbiApplication
{
private:
    virtual void Init();
    virtual int Run();
    virtual void Exit();

  
    CArgDescriptions *argList;
};


void CDbapiTest::Init()
{

    argList = new CArgDescriptions();

    argList->SetUsageContext(GetArguments().GetProgramBasename(),
                             "DBAPI test program");


#ifdef WIN32
   argList->AddDefaultKey("s", "string",
                           "Server name",
                           CArgDescriptions::eString, "MSSQL2");

   argList->AddDefaultKey("d", "string",
                           "Driver <ctlib|dblib|ftds|odbc>",
                           CArgDescriptions::eString, 
                           "odbc");
#else
    argList->AddDefaultKey("s", "string",
                           "Server name",
                           CArgDescriptions::eString, "STRAUSS");

    argList->AddDefaultKey("d", "string",
                           "Driver <ctlib|dblib|ftds>",
                           CArgDescriptions::eString, 
                           "ctlib");
#endif

    SetupArgDescriptions(argList);
}
  
int CDbapiTest::Run() 
{

    CArgs args = GetArgs();

    IDataSource *ds = 0;

    try {
        
        CDriverManager &dm = CDriverManager::GetInstance();

        string server = args["s"].AsString();
        string driver = args["d"].AsString();


        // Register driver explicitly for static linkage if needed
#ifdef WIN32
        DBAPI_RegisterDriver_ODBC(dm);
#endif
        //DBAPI_RegisterDriver_DBLIB(dm);

        // Create data source - the root object for all other
        // objects in the library.
        //
        // set TDS version for STRAUSS
        if( NStr::CompareNocase(server, "STRAUSS") == 0 ) {
            map<string,string> attr;
            attr["version"] = "100";
            ds = dm.CreateDs(driver, &attr);
        }
        else
            ds = dm.CreateDs(driver);

        // Redirect error messages to CMultiEx storage in the
        // data source object (global). Default output is sent
        // to standard error
        //
        //ds->SetLogStream(0);


        // Create connection. 
        IConnection* conn = ds->CreateConnection();

        // Set this mode to be able to use bulk insert
        conn->SetMode(IConnection::eBulkInsert);

        // To ensure, that only one database connection is used
        // use this method. It will throw an exception, if the
        // library tries to open additional connections implicitly, 
        // like creating multiple statements.
        //conn->ForceSingle(true);

        conn->Connect("anyone",
                      "allowed",
                      server,
                      "DBAPI_Sample");
    
        NcbiCout << "Using server: " << server
                 << ", driver: " << driver << endl;
/*
        // Test section start
        try {
            IDataSource *ds2 = dm.CreateDs(driver);

            IConnection* conn2 = ds2->CreateConnection();
            
            conn2->Connect("kholodov",
                           "newuser",
                           "MOZART");
            IStatement *stmt = conn2->CreateStatement();
            stmt->ExecuteUpdate("print 'Test print from MOZART'");
            delete conn2;
        }
        catch (exception& e) {
            NcbiCerr << e.what() << endl;
        }

        exit(0);
        // Test section end
*/    
        IStatement *stmt = conn->CreateStatement();

        string sql = "select int_val, fl_val, date_val, str_val \
from SelectSample ";
        NcbiCout << endl << "Testing simple select..." << endl
                 << sql << endl;

        conn->MsgToEx(true);

        try {
            stmt->Execute(sql);
    
            while( stmt->HasMoreResults() ) {
                if( stmt->HasRows() ) {   
                    IResultSet *rs = stmt->GetResultSet();

                    const IResultSetMetaData* rsMeta = rs->GetMetaData();

                    rs->BindBlobToVariant(true);

                    for(unsigned int i = 1; i <= rsMeta->GetTotalColumns(); ++i )
                        NcbiCout << rsMeta->GetName(i) << "  ";

                    NcbiCout << endl;

                    while(rs->Next()) { 
                        for(unsigned int i = 1;  i <= rsMeta->GetTotalColumns(); ++i ) {
                            if( rsMeta->GetType(i) == eDB_Text
                                || rsMeta->GetType(i) == eDB_Image ) {

                                CDB_Stream *b = dynamic_cast<CDB_Stream*>(rs->GetVariant(i).GetData());
                                _ASSERT(b);

                                char *buf = new char[b->Size() + 1];
                                b->Read(buf, b->Size());
                                buf[b->Size()] = '\0';
                                NcbiCout << buf << "|";
                                delete buf;
                                
                            }
                            else
                                NcbiCout << rs->GetVariant(i).GetString() << "|";
                        }
                        NcbiCout << endl;
                                
                            
#if 0
                        NcbiCout << rs->GetVariant(1).GetInt4() << "|"
                                 << rs->GetVariant(2).GetFloat() << "|"
                                 << rs->GetVariant("date_val").GetString() << "|"
                                 << rs->GetVariant(4).GetString()
                                 << endl;
#endif
                    
                    } 
                    // Use Close() to free database resources and leave DBAPI framwork intact
                    // All closed DBAPI objects become invalid.
                    rs->Close();
                    
                    // Delete object to get rid of it completely. All child objects will be also deleted
                    delete rs;
                }
            }
        }
        catch(CDB_Exception& e) {
            NcbiCout << "Exception: " << e.what() << endl;
            NcbiCout << conn->GetErrorInfo();
        } 

        NcbiCout << "Rows : " << stmt->GetRowCount() << endl;

        conn->MsgToEx(false);
/*
        // Testing MSSQL XML output
        sql += " for xml auto, elements";
        NcbiCout << "Testing simple select with XML output..." << endl
                 << sql << endl;

        conn->MsgToEx(true);

        try {
            stmt->Execute(sql);
    
            while( stmt->HasMoreResults() ) {
                if( stmt->HasRows() ) {   
                    IResultSet *rs = stmt->GetResultSet();
                    const IResultSetMetaData *rsMeta = rs->GetMetaData();

                    rs->BindBlobToVariant(true);

                    for(int i = 1; i <= rsMeta->GetTotalColumns(); ++i )
                        NcbiCout << rsMeta->GetName(i) << "  ";

                    NcbiCout << endl;

                    while(rs->Next()) { 
                        for(int i = 1;  i <= rsMeta->GetTotalColumns(); ++i ) {
                            if( rsMeta->GetType(i) == eDB_Text
                                || rsMeta->GetType(i) == eDB_Image ) {

                                CDB_Stream *b = dynamic_cast<CDB_Stream*>(rs->GetVariant(i).GetData());
                                _ASSERT(b);

                                char *buf = new char[b->Size() + 1];
                                b->Read(buf, b->Size());
                                buf[b->Size()] = '\0';
                                NcbiCout << buf << "|";
                                delete buf;
                                
                            }
                            else
                                NcbiCout << rs->GetVariant(i).GetString() << "|";
                        }
                        NcbiCout << endl;
                    
                    } 
                    
                }
            }
        }
        catch(CDB_Exception& e) {
            NcbiCout << "Exception: " << e.what() << endl;
            NcbiCout << ds->GetErrorInfo();
        } 
        
        exit(1);
        //delete stmt;
*/
        // Testing bulk insert w/o BLOBs
        NcbiCout << endl << "Creating BulkSample table..." << endl;
        sql = "if exists( select * from sysobjects \
where name = 'BulkSample' \
AND type = 'U') \
begin \
	drop table BulkSample \
end";
        stmt->ExecuteUpdate(sql);


        sql = "create table BulkSample (\
	id int not null, \
	ord int not null, \
    mode tinyint not null, \
    date datetime not null)";

        stmt->ExecuteUpdate(sql);

        //I_DriverContext *ctx = ds->GetDriverContext();
        //delete ctx;

        try {
            //Initialize table using bulk insert
            NcbiCout << "Initializing BulkSample table..." << endl;
            IBulkInsert *bi = conn->CreateBulkInsert("BulkSample", 4);
            CVariant b1(eDB_Int);
            CVariant b2(eDB_Int);
            CVariant b3(eDB_TinyInt);
            CVariant b4(eDB_DateTime);
            bi->Bind(1, &b1);
            bi->Bind(2, &b2);
            bi->Bind(3, &b3);
            bi->Bind(4, &b4);
            int i;
            for( i = 0; i < 10; ++i ) {
                b1 = i;
                b2 = i * 2;
                b3 = Uint1(i + 1);
                b4 = CTime(CTime::eCurrent);
                bi->AddRow();
                //bi->StoreBatch();
            }
            
            bi->Complete();

        }
        catch(...) {
            throw;
        }


        // create a stored procedure
        sql = "if exists( select * from sysobjects \
where name = 'SampleProc' \
AND type = 'P') \
begin \
	drop proc SampleProc \
end";
        stmt->ExecuteUpdate(sql);


        sql = "create procedure SampleProc \
	@id int, \
	@f float, \
    @o int output \
as \
begin \
  select int_val, fl_val, date_val from SelectSample \
  where int_val < @id and fl_val <= @f \
  select @o = 555 \
  select 2121, 'Parameter @id:', @id, 'Parameter @f:', @f, 'Parameter @o:', @o  \
  print 'Print test output' \
  return @id \
end";
        //  raiserror('Raise Error test output', 1, 1) 

        stmt->ExecuteUpdate(sql);
        stmt->ExecuteUpdate("print 'test'");