example6.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 */

/*
**	example6.c
**
** This example illustrates opening a data file, inserting data
** from the file into a newly created table containing several 
** SQL Server datatypes, and updating the table using browse-mode
** techniques.
*/

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

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

#define BUFLEN 2048


main()
{
	LOGINREC         *login;
	DBPROCESS        *q_dbproc;     /* This DBPROCESS will be used to
	                                 * query the database.
	                                 */
	DBPROCESS        *u_dbproc;     /* This DBPROCESS will be used to
	                                 * simultaneously update the database.
	                                 */
	char             *qualptr;      /* This will point to the WHERE clause
	                                 * appropriate for updating q_dbproc's
	                                 * current data row.
	                                 */
	RETCODE          return_code;

	DBTINYINT        age;

	char             cmdbuf[BUFLEN];
	FILE             *infile;

	printf("Demo of inserting and updating data into a table\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);
	
	/* Allocate and initialize the LOGINREC structure to be used
	 * to open a connection to SQL Server.
	 */

	login = dblogin();
	DBSETLUSER(login, USER);
	DBSETLPWD(login, PASSWORD);
	DBSETLAPP(login, "example6");

	q_dbproc = dbopen(login, NULL);
	u_dbproc = dbopen(login, NULL);

	printf("Creating the 'alltypes' table.\n");

	/* Create a table that contains several SQL Server data types. */
	dbcmd(q_dbproc,"create table alltypes ");
	dbcmd(q_dbproc,"(age tinyint,");
	dbcmd(q_dbproc,"userid smallint,");
	dbcmd(q_dbproc,"royalty int,");
	dbcmd(q_dbproc,"name char(25),");
	dbcmd(q_dbproc,"title_id varbinary(20),"); 
	dbcmd(q_dbproc,"us_citizen bit,");
	dbcmd(q_dbproc,"account float,");
	dbcmd(q_dbproc,"title varchar(20),");
	dbcmd(q_dbproc,"manager char(25),");
	dbcmd(q_dbproc,"timestamp)");

	dbcmd(q_dbproc, "create unique index index1 on alltypes(userid)");
  
	dbsqlexec(q_dbproc);
	while (dbresults(q_dbproc) != NO_MORE_RESULTS)
		continue;

	/* Insert rows of data into the newly created table "alltypes".
	 * We will read in the contents of the file and form an
	 * INSERT statement.
	 */

	if ((infile=fopen("datafile","r")) == NULL)
	{
		dbexit();
		printf("Unable to open file 'datafile'.\n");
		exit(STDEXIT);
	}

	printf("Inserting rows into the 'alltypes' table...\n");

	while ((fgets(cmdbuf,BUFLEN,infile)) != NULL)
	{
		dbfcmd(q_dbproc,"insert into alltypes \n");
		dbfcmd(q_dbproc,"values(%s, null) \n", (char *)cmdbuf);
	}

	dbsqlexec(q_dbproc);

	/* Process the results of each of the INSERT statements. */

	while ((return_code = dbresults(q_dbproc)) != NO_MORE_RESULTS)
	{
		if (return_code == FAIL)
			printf("One of the insert statements FAILed.\n");
	}

	/* Using DB-Library's browse-mode facilities, we'll increment
	 * the age of every person in the table.
	 */

	printf("Updating rows in the 'alltypes' table...\n");

	dbcmd(q_dbproc,"select * from alltypes for browse");
	dbsqlexec(q_dbproc);

	while ((return_code = dbresults(q_dbproc)) != NO_MORE_RESULTS)
	{
		if (return_code == SUCCEED)
		{
			while (dbnextrow(q_dbproc) != NO_MORE_ROWS)
			{
				age = *((DBTINYINT *)(dbdata(q_dbproc, 1)));
				qualptr = dbqual(q_dbproc, -1, "alltypes");
				if (qualptr == NULL)
				{
					printf("dbqual() failed.  Exiting.\n");
					dbexit();
					exit(ERREXIT);
				}
				dbcmd(u_dbproc, "update alltypes");
				dbfcmd (u_dbproc, " set age = %d %s", age+1, qualptr);
				dbsqlexec(u_dbproc);
				dbresults(u_dbproc);
				dbfreequal(qualptr);
			}
		}
	}

	/* Now, we'll look at the updated contents of the table, to
	 * verify that the ages were properly incremented.
	 */
	printf("Selecting rows from the 'alltypes' table:\n");
	dbcmd(q_dbproc, "select * from alltypes");
	dbsqlexec(q_dbproc);
	dbresults(q_dbproc);
	dbprrow(q_dbproc);

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