dbapi_sample.cpp

ncbi源码

        float f = 2.999f;

        // call stored procedure
        NcbiCout << endl << "Calling stored procedure..." << endl;
        
        ICallableStatement *cstmt = conn->PrepareCall("SampleProc", 3);
        cstmt->SetParam(CVariant(5), "@id");
        cstmt->SetParam(CVariant::Float(&f), "@f");
        cstmt->SetOutputParam(CVariant(eDB_Int), "@o");
        cstmt->Execute(); 
    
        // Below is an example of using auto_ptr to avoid resource wasting
        // in case of multiple resultsets, statements, etc.
        // NOTE: Use it with caution, when the wrapped parent object
        // goes out of scope, all child objects are destroyed.
        while(cstmt->HasMoreResults()) {
            auto_ptr<IResultSet> rs(cstmt->GetResultSet());

            //NcbiCout << "Row count: " << cstmt->GetRowCount() << endl;

            if( !cstmt->HasRows() ) {
                continue;
            }

            switch( rs->GetResultType() ) {
            case eDB_ParamResult:
                while( rs->Next() ) {
                    NcbiCout << "Output param: "
                             << rs->GetVariant(1).GetInt4()
                             << endl;
                }
                break;
            case eDB_RowResult:
                while( rs->Next() ) {
                    if( rs->GetVariant(1).GetInt4() == 2121 ) {
                            NcbiCout << rs->GetVariant(2).GetString() << " "
                                     << rs->GetVariant(3).GetString() << " "
                                     << rs->GetVariant(4).GetString() << " "
                                     << rs->GetVariant(5).GetString() << " "
                                     << rs->GetVariant(6).GetString() << " "
                                     << rs->GetVariant(7).GetString() << " "
                                     << endl;
                    }
                    else {
                        NcbiCout << rs->GetVariant(1).GetInt4() << "|"
                                 << rs->GetVariant(2).GetFloat() << "|"
                                 << rs->GetVariant("date_val").GetString() << "|"
                                 << endl;
                    }
                }
                break;
            }
        }
        NcbiCout << "Status : " << cstmt->GetReturnStatus() << endl;
        NcbiCout << endl << ds->GetErrorInfo() << endl;

       
        delete cstmt;

        // call stored procedure using language call
        NcbiCout << endl << "Calling stored procedure using language call..." << endl;
        
        sql = "exec SampleProc @id, @f, @o output";
        stmt->SetParam(CVariant(5), "@id");
        stmt->SetParam(CVariant::Float(&f), "@f");
        stmt->SetParam(CVariant(5), "@o");
        stmt->Execute(sql); 
    
        while(stmt->HasMoreResults()) {
            IResultSet *rs = stmt->GetResultSet();

            //NcbiCout << "Row count: " << stmt->GetRowCount() << endl;

            if( rs == 0 )
                continue;

            switch( rs->GetResultType() ) {
            case eDB_ParamResult:
                while( rs->Next() ) {
                    NcbiCout << "Output param: "
                             << rs->GetVariant(1).GetInt4()
                             << endl;
                }
                break;
            case eDB_StatusResult:
                while( rs->Next() ) {
                    NcbiCout << "Return status: "
                             << rs->GetVariant(1).GetInt4()
                             << endl;
                }
                break;
            case eDB_RowResult:
                while( rs->Next() ) {
                    if( rs->GetVariant(1).GetInt4() == 2121 ) {
                            NcbiCout << rs->GetVariant(2).GetString() << "|"
                                     << rs->GetVariant(3).GetString() << "|"
                                     << rs->GetVariant(4).GetString() << "|"
                                     << rs->GetVariant(5).GetString() << "|"
                                     << rs->GetVariant(6).GetString() << "|"
                                     << rs->GetVariant(7).GetString() << "|"
                                     << endl;
                    }
                    else {
                        NcbiCout << rs->GetVariant(1).GetInt4() << "|"
                                 << rs->GetVariant(2).GetFloat() << "|"
                                 << rs->GetVariant("date_val").GetString() << "|"
                                 << endl;
                    }
                }
                break;
            }
        }

        stmt->ClearParamList();
        //exit(1);

        // Reconnect
        NcbiCout << endl << "Reconnecting..." << endl;

        delete conn;
        conn = ds->CreateConnection();

        conn->SetMode(IConnection::eBulkInsert);

        conn->Connect("anyone",
                      "allowed",
                      server,
                      "DBAPI_Sample");


        stmt = conn->CreateStatement();

        NcbiCout << endl << "Reading BLOB..." << endl;

        // Read blob to vector
        vector<char> blob;

 
        NcbiCout << "Retrieve BLOBs using streams" << endl;

        stmt->ExecuteUpdate("set textsize 2000000");
    
        stmt->Execute("select str_val, text_val, text_val \
from SelectSample where int_val = 1");
    
        while( stmt->HasMoreResults() ) { 
            if( stmt->HasRows() ) {
                IResultSet *rs = stmt->GetResultSet();
                int size = 0;
                while(rs->Next()) { 
                    NcbiCout << "Reading: " << rs->GetVariant(1).GetString() 
                             << endl;
                    istream& in1 = rs->GetBlobIStream();
                    int c = 0; 
                    NcbiCout << "Reading first blob..." << endl;
                    for( ;(c = in1.get()) != CT_EOF; ++size ) {
                        blob.push_back(c);
                    }
                    istream& in2 = rs->GetBlobIStream();
                    NcbiCout << "Reading second blob..." << endl;
                    
                    for( ;(c = in2.get()) != CT_EOF; ++size ) { 
                        blob.push_back(c);
                    }
                } 
                NcbiCout << "Bytes read: " << size << endl;
            }
        }

    
        // create a table
        NcbiCout << endl << "Creating BlobSample table..." << endl;
        sql = "if exists( select * from sysobjects \
where name = 'BlobSample' \
AND type = 'U') \
begin \
	drop table BlobSample \
end";
        stmt->ExecuteUpdate(sql);


        sql = "create table BlobSample (\
	id int null, \
	blob text null, unique (id))";
        stmt->ExecuteUpdate(sql);

        // Write BLOB several times
        const int COUNT = 5;
        int cnt = 0;

        // Create alternate blob storage
        char *buf = new char[blob.size()];
        char *p = buf;
        vector<char>::iterator i_b = blob.begin();
        for( ; i_b != blob.end(); ++i_b ) {
            *p++ = *i_b;
        }

        //Initialize table using bulk insert
        NcbiCout << "Initializing BlobSample table..." << endl;
        IBulkInsert *bi = conn->CreateBulkInsert("BlobSample", 2);
        CVariant col1 = CVariant(eDB_Int);
        CVariant col2 = CVariant(eDB_Text);
        bi->Bind(1, &col1);
        bi->Bind(2, &col2);
        for(int i = 0; i < COUNT; ++i ) {
            string im = "BLOB data " + NStr::IntToString(i);
            col1 = i;
            col2.Truncate();
            col2.Append(im.c_str(), im.size());
            bi->AddRow();
        }
        bi->Complete();

        

        NcbiCout << "Writing BLOB using cursor..." << endl;

        ICursor *blobCur = conn->CreateCursor("test", 
           "select id, blob from BlobSample for update of blob");
    
        IResultSet *blobRs = blobCur->Open();
        while(blobRs->Next()) {
                NcbiCout << "Writing BLOB " << ++cnt << endl;
                ostream& out = blobCur->GetBlobOStream(2, blob.size(), eDisableLog);
                out.write(buf, blob.size());
                out.flush();
        }
     
        blobCur->Close();

#ifndef WIN32 // Not supported by ODBC driver
        NcbiCout << "Writing BLOB using resultset..." << endl;

        sql = "select id, blob from BlobSample";
        if( NStr::CompareNocase(driver, "ctlib") == 0 )
            sql += " at isolation read uncommitted";

        stmt->Execute(sql);
    
        cnt = 0;
        while( stmt->HasMoreResults() ) {
            if( stmt->HasRows() ) {
                IResultSet *rs = stmt->GetResultSet();
                while(rs->Next()) {
                    NcbiCout << "Writing BLOB " << ++cnt << endl;
                    ostream& out = rs->GetBlobOStream(blob.size(), eDisableLog);
                    out.write(buf, blob.size());
                    out.flush();
                }
            }
        }

#endif

#if 0
        while( stmt->HasMoreResults() ) {
            if( stmt->HasRows() ) {
                IResultSet *rs = stmt->GetResultSet();
                while(rs->Next()) {
                    ostream& out = rs->GetBlobOStream(blob.size());
                    vector<char>::iterator i = blob.begin();
                    for( ; i != blob.end(); ++i )
                        out << *i;
                    out.flush();
                }
            }
        }
#endif
        delete buf;

        // check if Blob is there
        NcbiCout << "Checking BLOB size..." << endl;
        stmt->Execute("select 'Written blob size' as size, datalength(blob) \
from BlobSample where id = 1");
    
        while( stmt->HasMoreResults() ) {
            if( stmt->HasRows() ) {
                IResultSet *rs = stmt->GetResultSet();
                while(rs->Next()) {
                    NcbiCout << rs->GetVariant(1).GetString() << ": " 
                             << rs->GetVariant(2).GetInt4() << endl;
                }
            }
        }

        // Cursor test (remove blob)
        NcbiCout << "Cursor test, removing blobs" << endl;

        ICursor *cur = conn->CreateCursor("test", 
                                          "select id, blob from BlobSample for update of blob");
    
        IResultSet *rs = cur->Open();
        while(rs->Next()) {
            cur->Update("BlobSample", "update BlobSample set blob = ' '");
        }
     
        cur->Close();

        // drop BlobSample table
        NcbiCout << "Deleting BlobSample table..." << endl;
        sql = "drop table BlobSample";
        stmt->ExecuteUpdate(sql);
        NcbiCout << "Done." << endl;


    }
    catch(out_of_range) {
        NcbiCout << "Exception: Out of range" << endl;
    }
    catch(exception& e) {
        NcbiCout << "Exception: " << e.what() << endl;
        NcbiCout << ds->GetErrorInfo();
    }

    return 0;
}

void CDbapiTest::Exit()
{

}


int main(int argc, const char* argv[])
{
    return CDbapiTest().AppMain(argc, argv, 0, eDS_Default, 0);
}


/*
* ---------------------------------------------------------------------------
* $Log: dbapi_sample.cpp,v $
* Revision 1000.2  2004/06/01 18:31:51  gouriano
* PRODUCTION: UPGRADED [GCC34_MSVC7] Dev-tree R1.12
*
* Revision 1.12  2004/05/21 21:41:41  gorelenk
* Added PCH ncbi_pch.hpp
*
* Revision 1.11  2004/03/01 21:03:47  kholodov
* Added comments
*
* Revision 1.10  2004/02/27 14:35:44  kholodov
* Comments added, work in progress
*
* Revision 1.9  2003/10/09 21:25:36  kholodov
* Several tests added
*
* Revision 1.8  2003/03/06 16:17:21  kholodov
* Fixed: incorrect TDS version for STRAUSS
*
* Revision 1.7  2002/10/23 13:32:59  kholodov
* Fixed: extra exit() call removed
*
* Revision 1.6  2002/10/03 18:59:55  kholodov
* *** empty log message ***
*
* Revision 1.5  2002/10/02 15:06:58  kholodov
* Modified: default connection settings for UNIX and NT platforms
*
* Revision 1.4  2002/10/01 18:57:45  kholodov
* Added: bulk insert test
*
* Revision 1.3  2002/09/19 14:38:49  kholodov
* Modified: to work with ODBC driver
*
* Revision 1.2  2002/09/17 21:17:15  kholodov
* Filed moved to new location
*
* Revision 1.5  2002/09/16 21:08:33  kholodov
* Added: bulk insert operations
*
* Revision 1.4  2002/09/03 18:41:53  kholodov
* Added multiple BLOB updates, use of IResultSetMetaData
*
* Revision 1.3  2002/07/12 19:23:03  kholodov
* Added: update BLOB using cursor for all platforms
*
* Revision 1.2  2002/07/08 16:09:24  kholodov
* Added: BLOB update thru cursor
*
* Revision 1.1  2002/07/02 13:48:30  kholodov
* First commit
*
*
*/