Liren Large Software Subsidy 5/05.iso/a/a063/9.img/SAMPLE/DBLIB/EXAMPLE1.C

C/C++ Source or Header | 1991-01-02 | 5.8 KB | 208 lines

/*
**    example1.c
**
**    This example illustrates how to send two queries to
**    SQL Server in a command batch.  It binds each set
**    of results and prints the rows.
**
*/

#include <stdlib.h>
#include <stdio.h>
#include <string.h>

#include <sybfront.h>
#include <sybdb.h>

#define DATELEN     26
#define TYPELEN      2

/* Function Prototypes */
int             err_handler(DBPROCESS *, int, int, int, char *, char *);
int             msg_handler(DBPROCESS *, DBINT, int, int, char *, char *,
                                        char *, DBUSMALLINT);
int                   main();

main()
{
    DBPROCESS     *dbproc;       /* Our connection with SQL Server. */
    LOGINREC      *login;        /* Our login information. */

    /* These are the variables used to store the returning data. */

    DBCHAR         crdate[DATELEN+1];
    DBINT          id;
    DBCHAR         name[MAXNAME+1];    /* MAXNAME is defined in
                                        * "sybdb.h" as the maximum
                                        * length for names of database
                                        * objects, such as tables,
                                        * columns, and procedures.
                                        */
    DBCHAR         type[TYPELEN+1];
    RETCODE        result_code;

    /* Initialize DB-Library. */
    if (dbinit() == FAIL)
        exit(ERREXIT);

    /* Install the user-supplied error-handling and message-handling
     * routines. They are defined at the bottom of this source file.
     */
    dberrhandle(err_handler);
    dbmsghandle(msg_handler);

    /*
    ** Get a LOGINREC structure and fill it with the necessary
    ** login information.
    */

    login = dblogin();
    DBSETLPWD(login, "server_password");
    DBSETLAPP(login, "example1");

    /*
    ** Get a DBPROCESS structure for communicating with SQL Server.
    ** A NULL servername defaults to the server specified by DSQUERY.
    */

    dbproc = dbopen(login, NULL);

    /*
    ** We are going to retrieve some information, from a table
    ** named "sysobjects", regarding names of system tables and
    ** stored procedures.
    ** We will submit two queries.  The first finds all the rows
    ** that describe system tables.  The second finds all the rows
    ** that describe stored procedures.  The program will only look
    ** at the first 10 rows that describe stored procedures.
    */

    /* First, put the commands into the command buffer. */

    dbcmd(dbproc, "select name, type, id, crdate from sysobjects");
    dbcmd(dbproc, " where type = 'S' ");
    dbcmd(dbproc, "select name, type, id, crdate from sysobjects");
    dbcmd(dbproc, " where type = 'P' ");

    /*
    ** Sql Server processes the command batch in the following
    ** order:
    **
    ** 1) It will check for syntax errors (i.e., "use database pubs"
    **    is syntactically incorrect; it should be "use pubs").
    ** 2) The second check is a semantic check (i.e., "select * from
    **    titels" will be incorrect because the spelling should be
    **    "titles".)
    ** 3) The third check occurs in the actual execution phase. This
    **    check involves issues like permissions or memory problems.
    **
    ** In the execution phase, dbsqlexec() and dbresults() can return
    ** the value "SUCCEED", which means there are more commands in the
    ** batch to process and that that command was successful. A value
    ** of "FAIL" means that the query failed but there may be more
    ** commands in the batch to process. A value of "NO_MORE_RESULTS"
    ** means that there are no more commands in the batch to process.
    ** Therefore, the programmer must check the return values after
    ** dbsqlexec() and dbresults(), as illustrated below.
    **
    */

    /* Send the commands to SQL Server and start execution. */
    dbsqlexec(dbproc);

    /* Process each command until there are no more. */

    while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
    {
        if (result_code == SUCCEED)
        {
            /* Bind program variables. */

            dbbind(dbproc, 1, NTBSTRINGBIND, (DBINT)0, name);
            dbbind(dbproc, 2, NTBSTRINGBIND, (DBINT)0, type);
            dbbind(dbproc, 3, INTBIND, (DBINT)0, (BYTE *)&id);
            dbbind(dbproc, 4, NTBSTRINGBIND, (DBINT)0, crdate);

            /* Print appropriate header for the type of
             * data coming back.
             */

            printf("\n %s Objects: \n\n",
            DBCURCMD(dbproc) == 1 ? "System Table": "Procedure");

            /* Now print the rows. */

            while (dbnextrow(dbproc) != NO_MORE_ROWS)
            {
                /*
                ** If this is the 2nd command and
                ** 10th row, flush the rest of the
                ** rows for that command.
                */

                if ((DBCURCMD(dbproc) == 2)
                    && (DBCURROW(dbproc) > 10))
                    continue;

                printf
                    ("%s %s %ld %s\n", name, type, id, crdate);
            }
        }
    }


    /* Close our connection and exit the program. */

    dbexit();
    exit(STDEXIT);
}

int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr)
DBPROCESS       *dbproc;
int             severity;
int             dberr;
int             oserr;
char            *dberrstr;
char            *oserrstr;
{
    if ((dbproc == NULL) || (DBDEAD(dbproc)))
        return(INT_EXIT);
    else
    {
        printf("DB-Library error:\n\t%s\n", dberrstr);

        if (oserr != DBNOERR)
            printf("Operating-system error:\n\t%s\n", oserrstr);

        return(INT_CANCEL);
    }
}

int msg_handler(dbproc, msgno, msgstate, severity, msgtext,
                srvname, procname, line)

DBPROCESS       *dbproc;
DBINT           msgno;
int               msgstate;
int               severity;
char            *msgtext;
char            *srvname;
char            *procname;
DBUSMALLINT     line;

{
    printf ("Msg %ld, Level %d, State %d\n",
            msgno, severity, msgstate);

    if (strlen(srvname) > 0)
        printf ("Server '%s', ", srvname);
    if (strlen(procname) > 0)
        printf ("Procedure '%s', ", procname);
    if (line > 0)
        printf ("Line %d", line);

    printf("\n\t%s\n", msgtext);

    return(0);
}