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

C/C++ Source or Header | 1991-01-02 | 5.0 KB | 187 lines

/*
**    example3.c
**
** This example selects some information from the "pubs" database.
** It illustrates binding of both aggregate and compute results.
**
** Note that this example will only work if the "pubs" database exists
** on your SQL Server. Consult the Installation Guide for information
** about installing the "pubs" database.
*/

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

#define PLEN 4
#define DATEPRINT 26
#define MONEYPRINT 12

/* 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()
{

    LOGINREC       *login;
    DBPROCESS      *dbproc;

    /* Declare the datatypes for the columns in the table "titles". */

    DBINT          pcount;
    DBINT          sales;
    DBINT          salesavg;
    DBINT          sumsale;
    DBCHAR         date[DATEPRINT+1];
    DBCHAR         price[MONEYPRINT+1];
    DBCHAR         priceavg[MONEYPRINT+1];
    DBCHAR         pubid[PLEN+1];
    RETCODE        result_code; /* to hold the results of dbresults(). */
    STATUS         row_code;    /* to hold the results of dbnextrow(). */

    /* 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);

    /* Set up the login information. */

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

    dbproc = dbopen(login, NULL);

    /* Send a "use database" command. */

    dbuse(dbproc,"pubs");

    /* Put the SQL statement into the command buffer. */

    dbcmd(dbproc, "select pub_id, pubdate, price, avg(price), ytd_sales,");
    dbcmd(dbproc, " avg(ytd_sales), sum(ytd_sales) from titles");
    dbcmd(dbproc, " group by pub_id");
    dbcmd(dbproc, " order by pub_id");
    dbcmd(dbproc, " compute count(pub_id) by pub_id");

    /* Send the command buffer to SQL Server for execution. */

    dbsqlexec(dbproc);

    /*
    ** Using the aggregates "sum" and "avg" with the COMPUTE clause
    ** necessitates special handling when binding the results. Since each
    ** aggregate creates a new column, this is accounted for in the bind.
    ** Notice that avg(price) is the fourth column in the select-list,
    ** and is also specified as the fourth column in the dbbind() routine.
    **
    ** The COMPUTE clause creates a compute row, which requires a
    ** special bind routine called dbaltbind().
    */

    while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
    {
        if (result_code == SUCCEED)
        {
            dbbind(dbproc, 1, NTBSTRINGBIND, (DBINT)0, pubid);
            dbbind(dbproc, 2, NTBSTRINGBIND, (DBINT)0, date);
            dbbind(dbproc, 3, NTBSTRINGBIND, (DBINT)0, price);
            dbbind(dbproc, 4, NTBSTRINGBIND, (DBINT)0, priceavg);
            dbbind(dbproc, 5, INTBIND, (DBINT)0, (BYTE *)&sales);
            dbbind(dbproc, 6, INTBIND, (DBINT)0, (BYTE *)&salesavg);
            dbbind(dbproc, 7, INTBIND, (DBINT)0, (BYTE *)&sumsale);

            /* dbaltbind() binds compute columns. */
            dbaltbind(dbproc, 1, 1, INTBIND, (DBINT)0, (BYTE *)&pcount);

            printf("\nAccounts:\n");
            printf("---------\n\n");
            printf
                ("%-5s  %-19s  %-6s  %-10s  %-5s  %-10s  %-10s\n\n",
                 "pubid", "date", "price", "avg(price)",
                 "sales", "avg(sales)", "sum(sales)");

            /*
            ** Print out each result row, using different statements
            ** depending on whether the row is a regular row or a
            ** compute row.
            */

            while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS)
            {
                if (row_code == REG_ROW)
                {
                    printf
                        ("%5s  %19s  %6s  %10s  %5ld  %10ld  %10ld\n",
                         pubid, date, price, priceavg, sales,
                         salesavg, sumsale);
                }
                else
                    printf("title count:  %ld\n\n",pcount);
            }
        }
    }

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