textcopy.cpp

本代码展示了使用DB_LIB库对SQL SERVER的连接和各种操作的实例。

    }

    // get first result set
    r = dbresults (pDbproc);
    if (r != SUCCEED)
    {
        cout << err << "Query results failed." << endl;
        Cleanup (pDbproc);
        return (1);
    }

    // verify that only a single column was selected    
    if (dbnumcols (pDbproc) != 1)
    {
        cout << err << "More than one column specified." << endl;
        Cleanup (pDbproc);
        return (1);
    }

    // verify that the single column selected is either text or image
    int nColumnType;
    nColumnType = dbcoltype (pDbproc, 1);
    if ((nColumnType != SQLTEXT) && (nColumnType != SQLIMAGE))
    {
        cout << err << "Specified column is not a text or image column." << endl;
        Cleanup (pDbproc);
        return (1);
    }
    else
    {
        if (nColumnType == SQLTEXT)
        {
            strColumnType = "text";
        }
        if (nColumnType == SQLIMAGE)
        {
            strColumnType = "image";
        }
    }

    // buffer for data transfer between DB-Library and file
    aBuf = new BYTE[nChunkSize];
    if (aBuf == 0)
    {
        cout << err << "Unable to allocate transfer buffer of '" << nChunkSize << "' bytes." << endl;
        Cleanup (pDbproc);
        return (1);
    }

    // if the data is coming out of SQL Server and into a file, use dbreadtext
    // (instead of dbnextrow) to read the text or image data from the row
    // in chunks
    if (bOut)
    {
    try
    {
        // set up file 
        CFile file (strFile, CFile::modeCreate | CFile::modeWrite);
        D(cout << "File '" << strFile << "' opened for write" << endl);

        DBINT lWriteBytes;
        while (TRUE)
        {
            // read chunk of text or image data from SQL Server into aBuf
            lWriteBytes = dbreadtext (pDbproc, aBuf, nChunkSize);
            switch (lWriteBytes)
            {
            case 0:
                // end of text or image row
                D(cout << "End of row" << endl);
                break;
            case -1:
                // dbreadtext failed
                cout << err << "Text or image data retrieval failed." << endl;
                Cleanup (pDbproc);
                return (1);
                break;
            case NO_MORE_ROWS:
                D(cout << "No more rows" << endl);
                break;
            default:
                // dbreadtext has placed lBytes of text or image data
                // into aBuf, now write that chunk to the file
                file.Write (aBuf, lWriteBytes);
                D(cout << "Wrote " << lWriteBytes << " bytes to file" << endl);
                break;
            }
            if ((lWriteBytes == -1) || (lWriteBytes == NO_MORE_ROWS))
                break; // while loop
        }
        file.Close();
        D(cout << "File closed" << endl);
    }
    catch (CFileException* e)
    {
        //if (e->m_cause == CFileException::fileNotFound)
        cout << err << "Problem with file '" << strFile << "'." << endl;
        e->Delete();
        Cleanup (pDbproc);
        return (1);
    }

    }

    DBBINARY aTextPointer[DBTXPLEN], aTextTimestamp[DBTXTSLEN];
    DBBINARY *pTextPointer, *pTextTimestamp;
    
    // if the data is going into SQL Server from a file, copy the text
    // pointer and text timestamp values into private buffers
    if (bIn)
    {
        // get the single row
        STATUS s;
        s = dbnextrow (pDbproc);
        if (s != REG_ROW)
        {
            cout << err << "Row retrieval failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }

        // get the pointers to the text pointer and text timestamp values
        pTextPointer = dbtxptr (pDbproc, 1);
        pTextTimestamp = dbtxtimestamp (pDbproc, 1);
        if ((pTextPointer == NULL) || (pTextTimestamp == NULL))
        {
            cout << err << "Text or image pointer and timestamp retrieval failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }

        // copy the actual text pointer and text timestamp values into
        // private buffers
        memcpy (aTextPointer, pTextPointer, DBTXPLEN);
        memcpy (aTextTimestamp, pTextTimestamp, DBTXTSLEN);

        // we should only have received one row, so call dbnextrow
        // to get NO_MORE_ROWS
        s = dbnextrow (pDbproc);
        switch (s)
        {
        case NO_MORE_ROWS:
            // this is what we expect
            break;
        case REG_ROW:
            cout << err << "Where clause returned more than one row." << endl;
            Cleanup (pDbproc);
            return (1);
            break;
        default:
            cout << err << "Row retrieval failed." << endl;
            Cleanup (pDbproc);
            return (1);
            break;
        }
    }
    
    // get NO_MORE_RESULTS to clear out all results
    r = dbresults (pDbproc);
    if (r != NO_MORE_RESULTS)
    {
        cout << err << "Query results failed." << endl;
        Cleanup (pDbproc);
        return (1);
    }

    // if the data is going into SQL Server from a file, use dbwritetext
    // (with the private copies of the text or image pointer and timestamp
    // values) and dbmoretext to write the text or image data to
    // SQL Server in chunks
    if (bIn)
    {
    try
    {
        // set up file
        CFile file (strFile, CFile::modeRead);
        D(cout << "File '" << strFile << "' opened for read" << endl);
        D(cout << "File is " << file.GetLength() << " bytes long" << endl);

        // call dbwritetext will NULL text value to indicate that the text
        // values will be sent in chunks using dbmoretext
        r = dbwritetext (pDbproc, strTable + "." + strColumn,
            aTextPointer, DBTXPLEN, aTextTimestamp,
            TRUE, file.GetLength(), NULL);
        if (r == FAIL)
        {
            cout << err << "Text or image write failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }
        
        // call dbsqlok and dbresults to prepare for calling dbmoretext
        r = dbsqlok (pDbproc);
        if (r == FAIL)
        {
            cout << err << "Text or image write failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }
        r = dbresults (pDbproc);
        if (r == FAIL)
        {
            cout << err << "Query results failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }

        // read each chunk from the file and write it to SQL Server
        UINT nReadBytes = nChunkSize;
        while (nReadBytes == UINT(nChunkSize))
        {
            // read chunk from file into aBuf
            nReadBytes = file.Read (aBuf, nChunkSize);
            D(cout << "Read "<< nReadBytes << " bytes from file" << endl);
            
            // write chunk of text or image data from aBuf to SQL Server
            r = dbmoretext (pDbproc, nReadBytes, aBuf);
            if (r == FAIL)
            {
                cout << err << "Text or image write failed." << endl;
                Cleanup (pDbproc);
                return (1);
            }
        }

        file.Close();
        D(cout << "File closed" << endl);

        // call dbsqlok and dbresults to signal end of calls to
        // dbmoretext and completion of text or image write
        r = dbsqlok (pDbproc);
        if (r == FAIL)
        {
            cout << err << "Text or image write failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }
        r = dbresults (pDbproc);
        if (r == FAIL)
        {
            cout << err << "Query results failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }
    }
    catch (CFileException* e)
    {
        //if (e->m_cause == CFileException::fileNotFound)
        cout << err << "Problem with file '" << strFile << "'." << endl;
        e->Delete();
        Cleanup (pDbproc);
        return (1);
    }

    }

    // display success message
    if (bIn)
    {
        cout << "Data copied into SQL Server " << strColumnType << " column from file '" << strFile << "'." << endl;
    }
    if (bOut)
    {
        cout << "Data copied out of SQL Server " << strColumnType << " column into file '" << strFile << "'." << endl;
    }

    // prepare DB-Library for exit
    Cleanup (pDbproc);

    return (0);
}

///////////////////////////////////////////////////////////////////////////////
//
//  FUNCTION: Cleanup()
//
//      Prepare DB-Library for exit
//
//  PARAMETERS:
//
//      pDbproc - pointer to DBPROCESS connection
//
//  RETURNS: 0
//
//  COMMENTS:
//
///////////////////////////////////////////////////////////////////////////////

int Cleanup (PDBPROCESS pDbproc)
{
    delete [] aBuf;
    dbcancel (pDbproc);
    dbclose (pDbproc);
    return (0);
}

///////////////////////////////////////////////////////////////////////////////
//
//  FUNCTION: ErrorHandler()
//
//      Called when a DB-Library error occurs
//
//  PARAMETERS:
//
//      pDbproc - pointer to DBPROCESS connection
//      nDblibSeverity - DB-Library error severity
//      nDblibErrorNo - DB-Library error number
//      nOSErrorNo - operating system error number
//      lpstrDblibError - DB-Library error string
//      lpstrOSError - operating system error string
//
//  RETURNS: One of three DB-Library return continuation codes:
//
//      INT_CANCEL
//      INT_CONTINUE
//      INT_EXIT
//
//  COMMENTS:
//
///////////////////////////////////////////////////////////////////////////////

extern "C" int ErrorHandler (
    PDBPROCESS pDbproc,
    INT nSeverity,
    INT nDBLibError,
    INT nOSError,
    LPCSTR pszDBLibError,
    LPCSTR pszOSError)
{
    // display DB-Library error information
    cout << "DB-Library Error " << nDBLibError << ": " << pszDBLibError << endl;

    if ((pDbproc == NULL) || (DBDEAD(pDbproc)))
    {
        return(INT_EXIT);
    }
    else
    {
        if (nOSError != DBNOERR)
        {
            // this DB-Library error was caused by an operating system
            // error, so display OS error information
            cout << "Operating System Error " << nOSError << ": " << pszOSError << endl;
        }

        return(INT_CANCEL);
    }
}

///////////////////////////////////////////////////////////////////////////////
//
//  FUNCTION: MessageHandler()
//
//      Called when a SQL Server message is received
//
//  PARAMETERS:
//
//      pDbproc - pointer to DBPROCESS connection
//      lMessage - SQL Server message number
//      nState - SQL Server message state
//      nSeverity - SQL Server message severity
//      pszMessage - SQL Server message string
//      pszServer - the SQL Server that sent this message
//      pszProcedure - the stored procedure (if any) where the message
//                     occurred
//      usLine - the line number (if any) of the batch or stored procedure
//            	 where the message occurred
//
//  RETURNS: 0
//
//  COMMENTS:
//
///////////////////////////////////////////////////////////////////////////////

extern "C" int MessageHandler (
    PDBPROCESS pDbproc,
    DBINT lMessage,
    INT nState,
    INT nSeverity,
    LPCSTR pszMessage,
    LPCSTR pszServer,
    LPCSTR pszProcedure,
    DBUSMALLINT usLine)
{
    // only display certain SQL Serve messages when debug info enabled
    switch (lMessage)
    {
    case 5701:
        if (!bDebug)
        {
            return (0);
        }
        break;
    }

    // display SQL Server message information
    cout << "SQL Server";

    if (pszServer != NULL)
    {
        if (*pszServer != '\0')
        {
            cout << " '" << pszServer << "'";
        }
    }

    cout << " Message " << lMessage << ": " << pszMessage;

    if (usLine != 0)
    {
        cout << " (Concerning line " << usLine;
        if (pszProcedure != NULL)
        {
            cout << " of stored procedure '" << pszProcedure << "'";
        }
        cout << ")";
    }

    cout << endl;

    return(0);
}

///////////////////////////////////////////////////////////////////////////////
//
//  FUNCTION: DisplayUsage()
//
//      Display usage information
//
//  PARAMETERS:
//
//  RETURNS: 0
//
//  COMMENTS:
//
///////////////////////////////////////////////////////////////////////////////

int DisplayUsage (void)
{
    cout << endl;
    cout << "Copies a single text or image value into or out of SQL Server. The value" << endl;
    cout << "is a specified text or image 'column' of a single row (specified by the" << endl;
    cout << "\"where clause\") of the specified 'table'." << endl;
    cout << endl;
    cout << "If the direction is IN (/I) then the data from the specified 'file' is" << endl;
    cout << "copied into SQL Server, replacing the existing text or image value. If the" << endl;
    cout << "direction is OUT (/O) then the text or image value is copied from" << endl;
    cout << "SQL Server into the specified 'file', replacing any existing file." << endl;
    cout << endl;
    cout << "TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]" << endl;
    cout << "  [/D [database]] [/T table] [/C column] [/W\"where clause\"]" << endl;
    cout << "  [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]" << endl;
    cout << endl;
    cout << "  /S sqlserver       The SQL Server to connect to. If 'sqlserver' is not" << endl;
    cout << "                     specified, the local SQL Server is used." << endl;
    cout << "  /U login           The login to connect with. If 'login' is not specified," << endl;
    cout << "                     'sa' will be used." << endl;
    cout << "  /P password        The password for 'login'. If 'password' is not" << endl;
    cout << "                     specified, a NULL password will be used." << endl;
    cout << "  /D database        The database that contains the table with the text or" << endl;
    cout << "                     image data. If 'database' is not specified, the default" << endl;
    cout << "                     database of 'login' is used." << endl;
    cout << "  /T table           The table that contains the text or image value." << endl;
    cout << "  /C column          The text or image column of 'table'." << endl;
    cout << "  /W \"where clause\"  A complete where clause (including the WHERE keyword)" << endl;
    cout << "                     that specifies a single row of 'table'." << endl;
    cout << "  /F file            The file name." << endl;
    cout << "  /I                 Copy text or image value into SQL Server from 'file'." << endl;
    cout << "  /O                 Copy text or image value out of SQL Server into 'file'." << endl;
    cout << "  /K chunksize       Size of the data transfer buffer in bytes. Minimum" << endl;
    cout << "                     value is 1024 bytes, default value is 4096 bytes." << endl;
    cout << "  /Z                 Display debug information while running." << endl;
    cout << "  /?                 Display this usage information and exit." << endl;
    cout << endl;
    cout << "You will be prompted for any required options you did not specify." << endl;

    return (0);
}