SQLAPI++的几个示例

现在用到了SQLAPI++，拷点自带的例子：

#include <stdio.h>  // for printf
#include <SQLAPI.h> // main SQLAPI++ header


int main(int argc, char* argv[])
{
    SAConnection con; // create connection object
    
    try
    {
        // connect to database
        // in this example it is Oracle,
        // but can also be Sybase, Informix, DB2
        // SQLServer, InterBase, SQLBase and ODBC
        con.Connect(
            "test",     // database name
            "tester",   // user name
            "tester",   // password
            SA_Oracle_Client);


        printf("We are connected!\n");


        // Disconnect is optional
        // autodisconnect will ocur in destructor if needed
        con.Disconnect();


        printf("We are disconnected!\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;
}

//////////////////////////////////////////////////////////////////////////////////

#include <stdio.h>  // for printf
#include <SQLAPI.h> // main SQLAPI++ header


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;
}

///////////////////////////////////////////////////////

#include <stdio.h>  // for printf
#include <SQLAPI.h> // main SQLAPI++ header


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;
}

//////////////////////////////////

#include <stdio.h>  // for printf
#include <SQLAPI.h> // main SQLAPI++ header


int main(int argc, char* argv[])
{
    SAConnection con; // connection object
    SACommand cmd(
        &con,
        "Select fid, fvarchar20 from test_tbl");    // command object
    
    try
    {
        // connect to database (Oracle in our example)
        con.Connect("test", "tester", "tester", SA_Oracle_Client);


        // Select from our test table
        cmd.Execute();
        // fetch results row by row and print results
        while(cmd.FetchNext())
        {
            printf("Row fetched: fid = %ld, fvarchar20 = '%s'\n", 
                cmd.Field("fid").asLong(),
                (const char*)cmd.Field("fvarchar20").asString());
        }


        // commit changes on success
        con.Commit();


        printf("Rows selected!\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;
}

/////////////////////////////////////////

#include <stdio.h>  // for printf
#include <SQLAPI.h> // main SQLAPI++ header


// forwards
SAString ReadWholeFile(const char *sFilename);
unsigned int FromFileWriter(
SAPieceType_t &ePieceType,
void *pBuf, unsigned int nLen, void *pAddlData);


int main(int argc, char* argv[])
{
    SAConnection con; // connection object
// command object
// update fblob field of our test table
    SACommand cmd(&con, 
"Update test_tbl set fblob = :fblob where fid =:1");
    
    try
    {
        // connect to database (Oracle in our example)
        con.Connect("test", "tester", "tester", SA_Oracle_Client);

        // use first method of Long(Lob) binding - as a whole
        cmd.Param(1).setAsLong() = 1; // fid
        cmd.Param("fblob").setAsBLob() = ReadWholeFile("test.doc");
        // update first row
        cmd.Execute();

        // use second method of binding - user defined callbacks
        cmd.Param(1).setAsLong() = 2;
cmd.Param("fblob").setAsBLob(
FromFileWriter,// our callback to provide blob data from file
10*1024, // desired size of each piece
(void*)"test.doc");// additional param, file name in our case
        // update second row
        cmd.Execute(); // at that moment Library will call user callback when needed

        // commit changes on success
        con.Commit();

        printf("Blob parameter bound, rows updated!\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;
}


SAString ReadWholeFile(const char *sFilename)
{
SAString s;
char sBuf[32*1024];
FILE *pFile = fopen(sFilename, "rb");


if(!pFile)
SAException::throwUserException(-1, 
"Error opening file '%s'\n", sFilename);
do
{
unsigned int nRead = fread(sBuf, 1, sizeof(sBuf), pFile);
s += SAString(sBuf, nRead);
}
while(!feof(pFile));

fclose(pFile);

return s;
}


static FILE *pFile = NULL;
static int nTotalBound;
unsigned int FromFileWriter(
SAPieceType_t &ePieceType,
void *pBuf, unsigned int nLen, void *pAddlData)
{
if(ePieceType == SA_FirstPiece)
{
const char *sFilename = (const char *)pAddlData;
pFile = fopen(sFilename, "rb");
if(!pFile)
SAException::throwUserException(-1, "Can not open file '%s'", sFilename);

nTotalBound = 0;
}

unsigned int nRead = fread(pBuf, 1, nLen, pFile);
nTotalBound += nRead;
// show progress
printf("%d bytes of file bound\n", nTotalBound);

if(feof(pFile))
{
ePieceType = SA_LastPiece;
fclose(pFile);
pFile = NULL;
}

return nRead;
}

//////////////////////////////////////////

#include <stdio.h>  // for printf
#include <SQLAPI.h> // main SQLAPI++ header


// forwards
void IntoFileReader(
SAPieceType_t ePieceType,
void *pBuf,
unsigned int nLen,
unsigned int nBlobSize,
void *pAddlData);


int main(int argc, char* argv[])
{
    SAConnection con; // connection object
    SACommand cmd(
        &con,
        "Select fblob from test_tbl");    // command object
    
    try
    {
        // connect to database (Oracle in our example)
        con.Connect("test", "tester", "tester", SA_Oracle_Client);


        // Usage 1. Read whole BLob(s) into internal buffers
// Select BLob from our test table
        cmd.Execute();
        // fetch results row by row and print results
        while(cmd.FetchNext())
        {
// after fetching a row all Long/Lob fields are automatically read into internal buffers
// just like other data types
SAString s = cmd.Field("fblob").asBLob();
            printf("Size of BLob is %d bytes\n", s.GetLength());
        }


        // Usage 2. Read BLob in pieces providing user callback for BLob data processing
// Select blob from our test table
        cmd.Execute();
// do not automatically read this field into internal buffer (into corresponding SAField object)
// we will provide a callback for BLob fetching after FetchNext
cmd.Field("fblob").setLongOrLobReaderMode(SA_LongOrLobReaderManual);
        
// fetch results row by row and print results
SAString sFilename;
int i = 0;
        while(cmd.FetchNext())
        {
sFilename.Format("fblob%d.bin", ++i);
// at that moment all fields are fetched except
// those that set for manual retrieving
// read them (fblob in our example) now
if(!cmd.Field("fblob").isNull())
cmd.Field("fblob").ReadLongOrLob(
IntoFileReader,// our callback to read BLob content into file
10*1024, // our desired piece size
(void*)(const char*)sFilename// additional data, filename in our example
);
}


        // commit changes on success
        con.Commit();


        printf("Rows with BLob field fetched!\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;
}


static FILE *pFile = NULL;
static int nTotalRead;
void IntoFileReader(
SAPieceType_t ePieceType,
void *pBuf,
unsigned int nLen,
unsigned int nBlobSize,
void *pAddlData)
{
if(ePieceType == SA_FirstPiece || ePieceType == SA_OnePiece)
{
nTotalRead = 0;
const char *sFilename = (const char *)pAddlData;
pFile = fopen(sFilename, "wb");
if(!pFile)
SAException::throwUserException(-1, "Can not open file '%s' for writing", sFilename);
}


fwrite(pBuf, 1, nLen, pFile);
nTotalRead += nLen;


// show progress
printf("%d bytes of %d read\n", nTotalRead, nBlobSize);


    if(ePieceType == SA_LastPiece || ePieceType == SA_OnePiece)
{
fclose(pFile);
pFile = NULL;
}
}

///////////////////////////////////////////////////

//  Cancel - Creates a new thread with long running query when the letter 'a' is typed.
//  Cancels a query when letter 'C' is typed.
//  All threads are terminated when the letter 'Q' is entered.
//
//  This program requires the multithread library.


#include <windows.h>
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <conio.h>
#include <process.h>


#include <iostream>


#include <SQLAPI.h>


void main(void); /* Thread 1: main */
void KbdFunc(void ); /* Keyboard input, thread dispatch */
void LongQuery(void *addl); /* Threads 2: Long running query */
void LongQueryCancel();
void ShutDown(void); /* Program shutdown */
void WriteTitle(); /* Display title bar information */


HANDLE  hConsoleOut;                   /* Handle to the console */
HANDLE  hQueryMutex;                   /* "Query Running" mutex */
HANDLE  hScreenMutex;                  /* "Screen update" mutex  */


SAConnection g_Conn;
SACommand g_Cmd(&g_Conn);


// connection and query information
// modify here to provide your real information
const char *LONG_RUNNING_QUERY =
"select * from test_bulk "
" where fvarchar20 like '%hj' or fvarchar20 like '%dfds'"
" or fvarchar20 in (select fvarchar20 from test_bulk)"
" order by fvarchar20 desc, finteger desc";
const char *DATABASE = "test";
const char *USER = "tester";
const char *PASSWORD = "tester";
SAClient_t CLIENT = SA_DB2_Client;




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 ShutDown(void)
{
// try to cancel the query if still running
if(WaitForSingleObject(hQueryMutex, 75L) == WAIT_TIMEOUT)
LongQueryCancel();
else
ReleaseMutex(hQueryMutex);
}


void KbdFunc(void)
{
    int         KeyInfo;


    do
    {
        KeyInfo = _getch();


        if(tolower(KeyInfo) == 'a')
_beginthread(LongQuery, 0, NULL);


        if(tolower(KeyInfo) == 'c')
            LongQueryCancel();
    }
while(tolower(KeyInfo) != 'q');


    ShutDown();
}


void LongQuery(void *addl)
{
if(WaitForSingleObject(hQueryMutex, 75L) == WAIT_TIMEOUT)
{
WaitForSingleObject(hScreenMutex, INFINITE);
std::cout << "query still running, use 'C' to cancel it." << std::endl;
ReleaseMutex(hScreenMutex);
return;
}


try
{
g_Cmd.setCommandText(LONG_RUNNING_QUERY);


WaitForSingleObject(hScreenMutex, INFINITE);
std::cout << "executing " << (const char*)g_Cmd.CommandText() << std::endl;
ReleaseMutex(hScreenMutex);


g_Cmd.Execute();


WaitForSingleObject(hScreenMutex, INFINITE);
std::cout << "executed" << std::endl;
ReleaseMutex(hScreenMutex);
}
catch(SAException &x)
{
WaitForSingleObject(hScreenMutex, INFINITE);
std::cout << (const char*)x.ErrText() << std::endl;
ReleaseMutex(hScreenMutex);
}


ReleaseMutex(hQueryMutex);
}


void LongQueryCancel()
{
try
{
WaitForSingleObject(hScreenMutex, INFINITE);
std::cout << "CANCELLING..." << std::endl;
ReleaseMutex(hScreenMutex);


g_Cmd.Cancel();


WaitForSingleObject(hScreenMutex, INFINITE);
std::cout << "CANCELLED" << std::endl;
ReleaseMutex(hScreenMutex);
}
catch(SAException &x)
{
WaitForSingleObject(hScreenMutex, INFINITE);
std::cout << (const char*)x.ErrText() << std::endl;
ReleaseMutex(hScreenMutex);
}
}


void WriteTitle()
{
    char    NThreadMsg[1024];


    sprintf(NThreadMsg, "Press 'A' to start a long running query in a thread, 'C' to cancel it, 'Q' to quit.");
    SetConsoleTitle(NThreadMsg);
}

///////////////////////////////////////////////////