sqlexamp.c

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

/***********************************************************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
***********************************************************************/
/* 
** This program provides a simple example of logging onto a SQL Server,
** sending down commands, retrieving metadata, and result rows.  Formatting
** and printing those results on the console.
**
*/
// 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.

#if defined(DBNTWIN32)
#include <windows.h>
#endif

#include "stdio.h"      // include standard header
#include "sqlfront.h"   // include dblib macro/manifest defines
#include "sqldb.h"      // include dblib datatype, prottypes, etc
#include "string.h"     // include for string functions
#include "malloc.h"     // include for malloc and free


// Prototypes for internal functions.
int DetermineRowSize(DBPROCESS *,int);
RETCODE PrintHeaders(DBPROCESS *);
RETCODE PrintRow(DBPROCESS *);

// Prototypes for DB-Library error and message handling.
int err_handler(DBPROCESS*, int, int, int, char*, char*);
int msg_handler(DBPROCESS*, DBINT, int, int, char*);

/*
** The below main is a mini isql interpreter and as such is only
** used for demonstration purposes.  Command line args include the Server
** name as arg 1, User ID as arg 2, assumes the password is null.
** This routine requests opens the connection after obtaining the login record
** and filling it with the necessary info.  Once the connection is established
** it accpets command input, set's it into the dbproc.  On "go" it executes
** the command against the server, processes each results set and then returns
** to accepting command input.  If "quit" or "exit" is input the program
** is terminated.  This interpreter will not process COMPUTE statements,
** and will not work with commands that return text/image data.
*/
int main(int argc, char *argv[])
    {
    LOGINREC*   login;      // login rec pointer
    DBPROCESS*  dbproc;     // SQL Server connection structure pointer

    char        cmd[150];   // command buffer
    char        server[30]; // server name buffer
    int         x = 1;      // command line counter
    STATUS      retc;       // return code

    const char* sqlversion; // pointer for version string

    *server = '\0';         // null start these two buffers
    *cmd = '\0';

    if(argc == 1)           // if no server name, request it
        {
	    printf("Enter Server Name: ");
	    gets(server);
        }
    else                    // else it was input as first arg
       strcpy(server,argv[1]);

    if(argc < 2)            // if no login id, request it
        {
        printf("Enter User Name: ");
        gets(cmd);
        }
    else                    // otherwise it was input as second arg.
        strcpy(cmd,argv[2]);

    // check to see if communications layer was loaded (DOS ONLY)
    if((sqlversion = dbinit()) == (BYTE *)NULL)
        {
        // DOS TSR (DBNMPIPE.EXE) is not loaded, don't bother going any farther
        printf("Error in DB-Library initialization, exiting\n");
        return 1;
        }
    else
        printf("DB-Library version: %s\n",sqlversion);  // print dblib version

    dbsettime(30);          // set timeouts to 30 seconds
    
    // set error/msg handlers for this program
    dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
    dberrhandle((DBERRHANDLE_PROC)err_handler);

    login = dblogin();      // get a login rec

    DBSETLUSER(login,cmd);  // set login id
    DBSETLHOST(login,"SQL EXAMPLE");    // set host name for sp_who
    DBSETLVERSION(login, DBVER60);
    // To use secure, or trusted, connection, uncomment the following line.
    // DBSETLSECURE (login);

    // open connection to requested server.  Pass null server name for local
    // connection, if name not entered.
    if((dbproc = dbopen(login,(*server) ? server : (char *)NULL)) == (DBPROCESS *)NULL)
        {
        // no one answered, so couldn't connect or error occurred
        printf("Login failed\n");
        return 1;
        }
    else
        {
        // loop on command input until quit or exit appears in first 4 bytes.
        while((strnicmp(cmd,"quit",4) != 0) && (strnicmp(cmd,"exit",4)!=0))
            {
            printf("%d> ", x++);                // print command prompt
            gets(cmd);                          // get command
            if(strnicmp(cmd,"go",2) == 0)       // is it go
                {
                if(dbsqlexec(dbproc) == FAIL)   // execute command
                    {
                    // problem occurred, just try another command
                    printf("Error in executing command batch!\n");
                    x = 1;
                    continue;
                    }
                // command executed correctly, get results information
                while((retc = dbresults(dbproc)) != NO_MORE_RESULTS)
                    {
                    if (retc == FAIL)           // if error get out of loop
                        break;

                    // headers and data could be printed here with only two
                    // function calls, dbprhead(dbproc), and dbprrow(dbproc),
                    // which would output the headers, and all the data to
                    // standard output.  However, that isn't very informative
                    // toward understanding how this data is obtained and
                    // processed, so I do it the hard way, one column at a time.

                    PrintHeaders(dbproc);       // print header data

                    // loop on each row, until all read
                    while((retc= dbnextrow(dbproc))!=NO_MORE_ROWS)
                        {
                        if(retc == FAIL)        // if fail, then clear
                            {                   // connection completely, just
                            dbcancel(dbproc);   // in case.
                            break;
                            }
                        else
                            PrintRow(dbproc);   // else print the current row
                        }

                    if (DBCOUNT(dbproc) == 1L)  // print the row count
                        printf("(1 row effected)\n");
                    else
                        printf("(%ld rows effected)\n",DBCOUNT(dbproc));

                    }                           // end while(dbresults())

                x = 1;                      // reset command line counter
                }
            else
                {
                strcat(cmd," ");            // go not detected, so put space
                dbcmd(dbproc,cmd);          // between each command and set in
                }                           // dbproc.

            } // end while()

        dbclose(dbproc);                // quit/exit input, close connection

        // print adios and exit.
        printf("SQL Server Connection to %s closed, bye bye.\n",server);
        return 0;
        }
    }

/*
** DetermineRowSize(DBPROCESS *,int)
**
** This function returns either the size of all columns in the row, converted
** to character data (SQLCHAR) with one space between each column, or
** if col is non-zero, the length of the input column converted to string.
** It is used to build the header strings, and each row of data, and is
** called to allocate the memory needed for each row, and determine how
** much of that space is to be used for each column
*/
int DetermineRowSize
    (
    DBPROCESS* dbproc,  // The SQL Server connection structure
    int col             // column size to get, 0 for all
    )
    {
    int x,cols;         // counters
    int length=0;       // total length of column(row).
    int namelength;     // length of name of column
    int prlength;       // printable length
    char *name;         // pointer to column name
    
    if (!col)           // get number of columns
        cols = dbnumcols(dbproc);
    
    // count from 1 to numcols if col is 0, else x will = col only
    for(x=((col) ? col : 1);x<=((col) ? col : cols);x++)
        {
        switch(dbcoltype(dbproc,x)) // get column type, determine SQLCHAR
            {                       // converted length
            case SQLNUMERIC:
            case SQLDECIMAL:
                {
                DBCOL Col;

                Col.SizeOfStruct = sizeof(DBCOL);

                dbcolinfo(dbproc, CI_REGULAR, x, 0, &Col);