example3.c

在SCO UNIX5.05

/*
** Confidential property of Sybase, Inc.
** (c) Copyright Sybase, Inc. 1992 to ???
** All rights reserved.
*/

/*
** %M%:      %I%      %G%      %U%
**  
**  
**  
*/
#if USE_SCCSID
static char Sccsid[] = {"%Z% %M% %I% %G%"};
#endif /* USE_SCCSID */

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

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

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

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


main(argc,argv)
int              argc;
char             *argv[];
{

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

	printf("Demo of binding aggregate and compute results\n\n");
	fflush(stdout);

	/* 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();
	DBSETLUSER(login, USER);
	DBSETLPWD(login, PASSWORD);
	DBSETLAPP(login, "example3");
 
	dbproc = dbopen(login, NULL);

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

	dbuse(dbproc,"pubs2");

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

	dbcmd(dbproc, "select pub_id, pubdate, price, avg(price), total_sales,");
	dbcmd(dbproc, " avg(total_sales), sum(total_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, (BYTE *)pubid);
			dbbind(dbproc, 2, NTBSTRINGBIND, (DBINT)0, (BYTE *)date);
			dbbind(dbproc, 3, NTBSTRINGBIND, (DBINT)0, (BYTE *)price);
			dbbind(dbproc, 4, NTBSTRINGBIND, (DBINT)0, (BYTE *)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  %-26s  %-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  %26s  %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 == (DBPROCESS *)NULL) || (DBDEAD(dbproc)))
        return(INT_EXIT);
    else 
    {
        fprintf (ERR_CH, "DB-Library error:\n\t%s\n", dberrstr);

        if (oserr != DBNOERR)
            fprintf (ERR_CH, "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;

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

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

	fprintf (ERR_CH, "\n\t%s\n", msgtext);

	return(0);
}