textcopy.cpp

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

/***********************************************************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
***********************************************************************/
////////////////////////////////////////////////////////////////////////
//
//  FILE: textcopy.cpp
//              
//      Text and image copy application
//
//  FUNCTIONS:
//
//      main() - Main application
//
//  COMMENTS:
//
////////////////////////////////////////////////////////////////////////
// This sample uses mixed mode security, other than Windows NT Authentication, 
// to establish connections. To use Windows NT Authentication, please use 
// DBSETLSECURE to set the secure connection flag.

#include <afx.h>                // MFC 
#include <iostream.h>           // iostream
#include <stdlib.h>             // C run-time

#if defined (_DEBUG)
#undef THIS_FILE
static char BASED_CODE THIS_FILE[] = __FILE__;
#endif

#define DBNTWIN32               // Win32 DB-Library for Windows NT
#include <sqlfront.h>           // DB-Library
#include <sqldb.h>              // DB-Library

extern "C"
{
    #include "getopt.h"         // GetOption
}

#include "textcopy.h"           // specific to this program

// GLOBAL VARIABLES
BOOL bDebug = FALSE;            // debug info
BYTE* aBuf;

///////////////////////////////////////////////////////////////////////////////
//
//  FUNCTION: main()
//
//      Main application
//
//  PARAMETERS:
//
//      argc - count of command line arguments
//      argv - array of command line argument strings
//      envp - array of environment strings
//
//  RETURNS: 0
//
//  COMMENTS:
//
///////////////////////////////////////////////////////////////////////////////
 
int main (
    int argc,
    char** argv,
    char** envp)
{
    char chOpt;                 // gotten option character
    char* pszParam;             // gotten parameter
    int nChunkSize = 4096;          // chunk size
    CString strChunkSize = "4096";  // chunk size
    CString strColumnType;

    CString strServer,          // SQL Server
        strLogin,               // login
        strPassword,            // password
        strDatabase,            // database
        strTable,               // table
        strColumn,              // column
        strWhere,               // where clause
        strFile;                // file
    BOOL bServer = FALSE,       // flags for tracking options
        bLogin = FALSE,
        bPassword = FALSE,
        bDatabase = FALSE,
        bTable = FALSE,
        bColumn = FALSE,
        bWhere = FALSE,
        bFile = FALSE,
        bIn = FALSE,
        bOut = FALSE;

    // banner
    cout << "TEXTCOPY Version 1.0" << endl;

    // initialize DB-Library, get DB-Library version number
    CString strDblibVersion;
    strDblibVersion = dbinit();
    if (strDblibVersion.IsEmpty())
    {
        cout << err << "Could not initialize DB-Library" << endl;
        return (1);
    }
    cout << strDblibVersion << endl;
    
    // get command-line options and parameters
    while (TRUE)
    {
        // all the following command-line options are valid, they
        // are neither case sensitive nor white-space sensitive, and
        // both '/' and '-' are valid switch characters
        //
        //  OPTION  PARAMETER           DESCRIPTION
        //
        //  /S      [sqlserver]         SQL Server
        //  /U      [login]             Login
        //  /P      [password]          Password
        //  /D      [database]          Database
        //  /T      table               Table
        //  /C      column              Column
        //  /W      "where clause"      Where clause in double quotes
        //  /F      file                File
        //  /I                          Into SQL Server from file
        //  /O                          Out of SQL Server into file
        //  /K      chunksize           Chunk size in bytes
        //  /Z                          Debug information
        //  /?                          Usage information
        //
        // for example, all of the following are valid ways to specify
        // a connection to a SQL Server named 'gizmo'
        //
        //  /Sgizmo     /sgizmo     -Sgizmo     -sgizmo
        //  /S gizmo    /s gizmo    -S gizmo    -s gizmo

        chOpt = GetOption(argc, argv, "s:S:u:U:p:P:d:D:t:T:c:C:w:W:f:F:k:K:iIoOzZ?", &pszParam);
        if (chOpt > 1)
        {
            // chOpt is valid argument
            switch (chOpt)
            {
            case 's':   // SQL Server
            case 'S':
                bServer = TRUE;
                strServer = pszParam;
                break;
            case 'u':   // login
            case 'U':
                bLogin = TRUE;
                strLogin = pszParam;
                break;
            case 'p':   // password
            case 'P':
                bPassword = TRUE;
                strPassword = pszParam;
                break;
            case 'd':   // database
            case 'D':
                bDatabase = TRUE;
                strDatabase = pszParam;
                break;
            case 't':   // table
            case 'T':
                bTable = TRUE;
                strTable = pszParam;
                break;
            case 'c':   // column
            case 'C':
                bColumn = TRUE;
                strColumn = pszParam;
                break;
            case 'w':   // where clause
            case 'W':
                bWhere = TRUE;
                strWhere = pszParam;
                break;
            case 'f':   // file
            case 'F':
                bFile = TRUE;
                strFile = pszParam;
                break;
            case 'i':   // direction: into SQL Server from file
            case 'I':
                bIn = TRUE;
                break;
            case 'o':   // direction: out of SQL Server into file
            case 'O':
                bOut = TRUE;
                break;
            case 'k':   // chunk size in bytes
            case 'K':
                if (pszParam != NULL)
                {
                    nChunkSize = atoi (pszParam);
                    _itoa (nChunkSize, strChunkSize.GetBuffer(20), 10);
                    strChunkSize.ReleaseBuffer();
                    if (strChunkSize != pszParam)
                    {
                        cout << err << "Converted chunk size '" << pszParam << "' to " << nChunkSize << endl;
                        return (0);
                    }
                }
                break;
            case 'z':   // debug
            case 'Z':
                bDebug = TRUE;
                break;
            case '?':   // usage info
                DisplayUsage();
                return(0);
                break;
            }
        }
        if (chOpt == 0)
        {
            // end of argument list
            break;
        }
        if ((chOpt == 1) || (chOpt == -1))
        {
            // standalone param or error
            cout << err << "Argument '" << pszParam << "' not recognized" << endl;
            break;
        }
    }

    if ((chOpt == 1) || (chOpt == -1))
    {
        // exit on error
        return (1);
    }

    // prompt the user for any unspecified options
    if (!bServer)
    {
        cout << "Type the SQL Server to connect to: ";
        cinstr (strServer);
    }

    if (!bLogin)
    {
        cout << "Type your login: ";
        cinstr (strLogin);
		if (strLogin.GetLength() == 0)
			strLogin = "sa";
    }

    if (!bPassword)
    {
        cout << "Type your password: ";
        cinstr (strPassword);
    }

    if (!bDatabase)
    {
        cout << "Type the database: ";
        cinstr (strDatabase);
    }

    if (!bTable)
    {
        cout << "Type the table: ";
        cinstr (strTable);
    }
    
    if (!bColumn)
    {
        cout << "Type the text or image column: ";
        cinstr (strColumn);
    }
    
    if (!bWhere)
    {
        cout << "Type the where clause: ";
        cinstr (strWhere);
    }
    
    if (!bFile)
    {
        cout << "Type the file: ";
        cinstr (strFile);
    }

    if (!bIn && !bOut)
    {
        while (TRUE)
        {
            CString strDirection;
            cout << "Type the direction ('I' for in, 'O' for out): ";
            cinstr (strDirection);
            if (strDirection.CompareNoCase("i") == 0)
            {
                bIn = TRUE;
                break;
            }
            else
            {
                if (strDirection.CompareNoCase("o") == 0)
                {
                    bOut = TRUE;
                    break;
                }
                else
                {
                    cout << err << "The value '" << strDirection << "' is invalid." << endl;
                }
            }
        }
    }

    // parameter validation
    if (strTable.IsEmpty())
    {
        cout << err << "You did not specify a table." << endl;
        return (1);
    }

    if (strColumn.IsEmpty())
    {
        cout << err << "You did not specify a column." << endl;
        return (1);
    }

    if (strWhere.IsEmpty())
    {
        cout << err << "You did not specify a where clause." << endl;
        return (1);
    }

    /*
    CString strLowerWhere = strWhere;
    strLowerWhere.MakeLower();
    if (strLowerWhere.Find ("where ") == -1)
    {
        cout << err << "Your where clause '" << strWhere << "' did not contain the keyword 'where'." << endl;
        return (1);
    }
    */

    if (strFile.IsEmpty())
    {
        cout << err << "You did not specify a file." << endl;
        return (1);
    }

    if (bIn && bOut)
    {
        cout << err << "You cannot specify both 'in' and 'out' directions." << endl;
        return (1);
    }

    if (nChunkSize < 1024)
    {
        cout << err << "Your specified chunk size of " << nChunkSize << " bytes is too small." << endl;
        return (1);
    }

    if (bDebug)
    {
        cout << dbg << "Final parameters:" << endl;
        cout << dbg << "  Server: " << strServer << endl;
        cout << dbg << "  Login: " << strLogin << endl;
        cout << dbg << "  Password: " << strPassword << endl;
        cout << dbg << "  Database: " << strDatabase << endl;
        cout << dbg << "  Table: " << strTable << endl;
        cout << dbg << "  Column: " << strColumn << endl;
        cout << dbg << "  Where clause: " << strWhere << endl;
        cout << dbg << "  File: " << strFile << endl;
        cout << dbg << "  Direction: ";
        if (bIn)
        {
            cout << "Into SQL Server from file." << endl;
        }
        if (bOut)
        {
            cout << "Out of SQL Server into file." << endl;
        }
        cout << dbg << "  Chunk size: " << nChunkSize << " bytes" << endl;
    }

    // install error and message handlers
    dberrhandle (ErrorHandler);
    dbmsghandle (MessageHandler);

    // set DB-Library options
    dbsettime(30);
    dbsetlogintime(10);

    // get login record
    PLOGINREC pLoginRec;
    pLoginRec = dblogin();
    if (pLoginRec == NULL)
    {
        cout << err << "Could not allocate a login record" << endl;
        return (1);
    }

    // fill the login record 
    DBSETLUSER (pLoginRec, strLogin);       // set the login
    DBSETLPWD (pLoginRec, strPassword);     // set the password
    DBSETLAPP (pLoginRec, "textcopy");      // set the app name
    DBSETLHOST (pLoginRec, "textcopy");     // set the host name
    // To use secure, or trusted, connection, uncomment the following line.
    // DBSETLSECURE (login);

    // attempt to connect to SQL Server
    PDBPROCESS pDbproc;
    pDbproc = dbopen (pLoginRec, strServer);
    dbfreelogin (pLoginRec);
    if (pDbproc == NULL)
    {
        cout << err << "Could not connect to SQL Server '" << strServer << "'" << endl;
        return (1);
    }

    // re-used DB-Library return code
    RETCODE r;

    // set textlimit and textsize options for this connection
    if (bOut)
    {
        dbsetopt (pDbproc, DBTEXTLIMIT, strChunkSize);
        dbsetopt (pDbproc, DBTEXTSIZE, "2147483647");
    }

    if (bIn)
    {
        dbsetopt (pDbproc, DBTEXTSIZE, "1024");
    }

    // must send and execute batch to set options
    r = dbsqlexec (pDbproc);
    if (r == FAIL)
    {
        cout << err << "Query execution failed." << endl;
        Cleanup (pDbproc);
        return (1);
    }

    // get empty result set(s) from setting options
    while (TRUE)
    {
        r = dbresults (pDbproc);
        if (r == FAIL)
        {
            cout << err << "Query results failed." << endl;
            Cleanup (pDbproc);
            return (1);
        }
        if (r == NO_MORE_RESULTS)
            break; // while loop
    }

    // use specified database
    if (!strDatabase.IsEmpty())
    {
        r = dbuse (pDbproc, strDatabase);
        if (r == FAIL)
        {
            cout << err << "Could not use database '" << strDatabase << "'" << endl;
            Cleanup(pDbproc);
            return (1);
        }
    }

    // build query
    CString strQuery;

    strQuery = "select " + strColumn + " from " + strTable + " " + strWhere;
    D(cout << "Query: " << strQuery << endl);

    r = dbcmd (pDbproc, strQuery);

    // send and execute query 
    r = dbsqlexec (pDbproc);
    if (r == FAIL)
    {
        cout << err << "Query execution failed." << endl;
        Cleanup (pDbproc);
        return (1);