wide_compute.c

sybase数据库ct library的开发,使用了所以有函数

/*
** COMPUTE EXAMPLE PROGRAM: For XNL feature
** ---------------------------------------------
**
** Description
** -----------
**	This example demonstrates processing compute results with wide column
**	and more than 255 columns per table (XNL feature) on. It sends a
**	canned query to the server via a language command.  It processes
**	the results using the standard ct_results() while loop.  It binds
**	the column values to program variables.  It then fetches and displays 
**	the rows in the standard ct_fetch() while loop.
**
**	This is the canned query:
**	select type, price from titles where type like "%cook" 
**	order by type, price 
**	compute sum(price) by type 
**	compute sum(price)
**
**	This query returns both regular rows and compute rows.  The compute
**	rows are generated by the 2 compute clauses.  The first compute
**	clause, 'compute sum(price) by type' generates a compute row each
**	time the value of type changes.  The second compute clause 'compute
**	sum(price)' generates one compute row which is the last to be 
**	returned.
**
**
** Routines Used
** -------------
**	cs_ctx_alloc()
**	ct_init()
**	ct_config()
**	ct_callback()
**	ct_con_alloc()
**	ct_con_props()
**	ct_connect()
**	ct_cmd_alloc()
**	ct_send()
**	ct_results()
**	ct_res_info()
**	ct_describe()
**	ct_compute_info()
**	ct_bind()
**	ct_fetch()
**	ct_cmd_drop()
**	ct_close()
**	ct_con_drop()
**	ct_exit()
**	ct_ctx_drop()
**
**
** Input
** -----
**	This example uses a hard-coded query of the titles table in the XNL_TEST
**	database.  The query is defined by SELECT1 and SELECT2
**
**
** Output
** ------
**	This example displays the rows as they are returned from the server.
**	
**
** Server Dependencies
** -------------------
**	None.
**
**
** Server Tables
** -------------
**	This example relies on the XNL_TEST database and the titles table.
**
**
**
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctpublic.h>
#include "example.h"
#include "exutils.h"

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

/*****************************************************************************
** 
** defines and globals used.
** 
*****************************************************************************/

/*
** Global names used in this module
*/
CS_CHAR *Ex_appname  = "compute";
CS_CHAR	*Ex_dbname   = "XNL_TEST";
CS_CHAR *Ex_server   = EX_SERVER;
CS_CHAR *Ex_username = EX_USERNAME;
CS_CHAR *Ex_password = EX_PASSWORD;


/*
** Global context structure
*/
CS_CONTEXT	*Ex_context;

#define DATABASE	1
#define TABLE		2
#define INSERTROW	3
#define SELECT		4
/*
** Define a sample select statment
*/
#define SELECT1	"select type, price from titles \
				where type like \"%business\" \
				order by type, price \
				compute sum(price) by type \
				compute sum(price)"

#define SELECT2 "select title_id, notes, c294, c295  from XNL_TEST..titles"

#define CREATEDB "if not exists (select * from master.dbo.sysdatabases \
			where name = \"XNL_TEST\" ) \
		  begin \
		  	create database XNL_TEST \
		  end" 

/*
** The string for the create table statement is cut into two pieces
** because on Windows platforms, the maximum string size (prior to 
** concatenation) is 2048.
*/
#define CREATETBL1 "if not exists (select name from XNL_TEST.dbo.sysobjects \
			where name = \"titles\" ) \
		   begin \
			create table XNL_TEST..titles \
			(title_id varchar(6) not null, \
        		title varchar(80) not null, \
        		type char(12) not null, \
      		  	pub_id char(4) null, \
        		price money null, \
        		advance money null, \
        		total_sales int null, \
        		notes varchar(300) null, \
        		pubdate datetime not null, \
        		contract bit not null, \
			c11 int, c12 int, c13 int, c14 int, \
			c15 int, c16 int, c17 int, c18 int, \
			c19 int, c20 int, c21 int, c22 int, \
			c23 int, c24 int, c25 int, c26 int, \
			c27 int, c28 int, c29 int, c30 int, \
			c31 int, c32 int, c33 int, c34 int, \
			c35 int, c36 int, c37 int, c38 int, \
			c39 int, c40 int, c41 int, c42 int, \
			c43 int, c44 int, c45 int, c46 int, \
			c47 int, c48 int, c49 int, c50 int, \
			c51 int, c52 int, c53 int, c54 int, \
			c55 int, c56 int, c57 int, c58 int, \
			c59 int, c60 int, c61 int, c62 int, \
			c63 int, c64 int, c65 int, c66 int, \
			c67 int, c68 int, c69 int, c70 int, \
			c81 int, c82 int, c83 int, c84 int, \
			c85 int, c86 int, c87 int, c88 int, \
			c89 int, c90 int, c91 int, c92 int, \
			c93 int, c94 int, c95 int, c96 int, \
			c97 int, c98 int, c99 int, c100 int, "

#define CREATETBL2     "c101 int, c102 int, c103 int, c104 int, \
			c105 int, c106 int, c107 int, c108 int, \
			c109 int, c110 int, c111 int, c112 int, \
			c114 int, c115 int, c116 int, c117 int, \
			c118 int, c119 int, c120 int, c121 int, \
			c122 int, c124 int, c125 int, c126 int, \
			c127 int, c128 int, c129 int, c130 int, \
			c131 int, c132 int, c134 int, c135 int, \
			c136 int, c137 int, c138 int, c139 int, \
			c140 int, c141 int, c142 int, c144 int, \
			c145 int, c146 int, c147 int, c148 int, \
			c149 int, c150 int, c151 int, c152 int, \
			c154 int, c155 int, c156 int, c157 int, \
			c158 int, c159 int, c160 int, c161 int, \
			c162 int, c164 int, c165 int, c166 int, \
			c167 int, c168 int, c169 int, c170 int, \
			c171 int, c172 int, c174 int, c175 int, \
			c176 int, c177 int, c178 int, c179 int, \
			c180 int, c181 int, c182 int, c184 int, \
			c185 int, c186 int, c187 int, c188 int, \
			c189 int, c190 int, c191 int, c192 int, \
			c194 int, c195 int, c196 int, c197 int, \
			c198 int, c199 int, c200 int, c201 int, \
			c202 int, c203 int, c204 int, c205 int, \
			c206 int, c207 int, c208 int, c209 int, \
			c210 int, c211 int, c212 int, c214 int, \
			c215 int, c216 int, c217 int, c218 int, \
			c219 int, c220 int, c221 int, c222 int, \
			c224 int, c225 int, c226 int, c227 int, \
			c228 int, c229 int, c230 int, c231 int, \
			c232 int, c234 int, c235 int, c236 int, \
			c237 int, c238 int, c239 int, c240 int, \
			c241 int, c242 int, c244 int, c245 int, \
			c246 int, c247 int, c248 int, c249 int, \
			c250 int, c251 int, c252 int, c254 int, \
			c255 int, c256 int, c257 int, c258 int, \
			c259 int, c260 int, c261 int, c262 int, \
			c264 int, c265 int, c266 int, c267 int, \
			c268 int, c269 int, c270 int, c271 int, \
			c272 int, c274 int, c275 int, c276 int, \
			c277 int, c278 int, c279 int, c280 int, \
			c281 int, c282 int, c284 int, c285 int, \
			c286 int, c287 int, c288 int, c289 int, \
			c290 int, c291 int, c292 int, c294 int, \
			c295 int )\
		   end"


#define INSERT "insert into titles \
	values (\"BU1032\", \"The Busy Excutive's Database Guide\", \
	\"business\", \"1389\", $19.99, $50.00, 4095, \
	replicate('b', 270), getdate(),1, 11,12,13,14,15,16,17,18,19,20, \
	21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40, \
	41,42,43,44,45,46,47,48,49,50, \
	51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70, \
	71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90, \
	91,92,93,94,95,96,97,98,99,100, \
	101,102,103,104,105,106,107,108,109,110,111,112,113,114,115, \
	116,117,118,119,120, \
	121,122,123,124,125,126,127,128,129,130,131,132,133,134,135, \
	136,137,138,139,140, \
	141,142,143,144,145,146,147,148,149,150,151,152,153,154,155, \
	156,157,158,159,160, \
	161,162,163,164,165,166,167,168,169,170,171,172,173,174,175, \
	176,177,178,179,180, \
	181,182,183,184,185,186,187,188,189,190,191,192,193,194,195, \
	196,197,198,199,200, \
	201,202,203,204,205,206,207,208,209,210,211,212,213,214,215, \
	216,217,218,219,220, \
	221,222,223,224,225,226,227,228,229,230,231,232,233,234,235, \
	236,237,238,239,240, \
	241,242,243,244,245,246,247,248,249,250,251,252,253,254,255, \
	256,257,258,259,260, \
	261,262,263,264, 265, 266, 267 \
)"

/*
** Prototypes for routines in sample code
*/ 
CS_STATIC CS_RETCODE CS_INTERNAL DoCommand PROTOTYPE((
	CS_CONNECTION *connection,
	CS_INT i
	));
CS_STATIC CS_RETCODE CS_INTERNAL DoCompute PROTOTYPE((
	CS_CONNECTION *connection
	));
CS_STATIC CS_RETCODE CS_INTERNAL FetchComputeResults PROTOTYPE((
	CS_COMMAND *cmd
	));
CS_STATIC CS_CHAR * CS_INTERNAL GetAggOp PROTOTYPE((
	CS_INT op
	));


/*
** main()
** 
** Purpose:
**	Entry point for example program.
** 
** Parameters:
**	None, argc and argv will not be used.
**
** Returns:
** 	EX_EXIT_ERROR  or EX_EXIT_SUCCEED
**
*/

int
main()
{
	CS_CONNECTION	*connection;
	CS_RETCODE	retcode;

	EX_SCREEN_INIT();
	
	fprintf(stdout, "Compute Example\n");
	fflush(stdout);

	/* 
	** Allocate a context structure and initialize Client-Library 
	*/
	retcode = ex_init(&Ex_context);
	if (retcode != CS_SUCCEED)
	{
		ex_panic("ex_init failed");
	}

	/* 
	** Allocate a connection structure, set its properties, and  
	** establish a connection.
	*/
	retcode = ex_connect(Ex_context, &connection, Ex_appname,
				Ex_username, Ex_password, Ex_server);

	/*
	** Execute the routines for the compute sample
	*/
	if (retcode == CS_SUCCEED)
	{
		printf("Creating database XNL_TEST if it does not exist...\n");
		retcode = DoCommand(connection, DATABASE); 
	}

	if (retcode == CS_SUCCEED)
	{
		printf("Creating a table titles if it does not exist...\n");
		retcode = DoCommand(connection, TABLE); 
	}

	if (retcode == CS_SUCCEED)
	{
		printf("Inserting rows into table titles...\n");
		retcode = DoCommand(connection, INSERTROW); 
	}
	if (retcode == CS_SUCCEED)
	{
		printf("Computing........\n");
		retcode = DoCompute(connection);
	}
	if (retcode == CS_SUCCEED)
	{
		retcode = DoCommand(connection,SELECT);
	}

	/*
	** Make sure that the created databases are dropped. Cleanup.
	*/
       
        if ((retcode = ex_use_db(connection, "master")) != CS_SUCCEED)
        {
                ex_error("DoCommand: ex_use_db(master) failed");
                return retcode;
        }
        retcode = ex_remove_db(connection, Ex_dbname);
        if (retcode != CS_SUCCEED)
        {
                ex_error("DoCommand: ex_remove_db() failed");
                return retcode;
        }

	/*
	** Deallocate the allocated structures, close the connection,
	** and exit Client-Library.
	*/
	if (connection != NULL)
	{
		retcode = ex_con_cleanup(connection, retcode);
	}
	
	if (Ex_context != NULL)
	{
		retcode = ex_ctx_cleanup(Ex_context, retcode);
	}

	return (retcode == CS_SUCCEED) ? EX_EXIT_SUCCEED : EX_EXIT_FAIL;
}

/*
** DoCompute(connection)
**
** Type of function:
** 	compute program internal api
**
** Purpose:
**	This function is the core of the compute example.  It sends 
**	a select statment with a compute clause to the server.  It 
**	then processes the results in the standard ct_results() loop, 
**	calling ProcessRowResults()	when the type of result is 
**	CS_ROW_RESULT and calling ProcessComputeResults() when the
**	type of result is CS_COMPUTE_RESULT.
**	
** Parameters:
**	connection - Pointer to connection structure
**
** Return:
**	CS_SUCCEED if compute result set was processed correctly
**	Otherwise a Client-Library failure code.
**
*/

CS_STATIC CS_RETCODE CS_INTERNAL
DoCompute(connection)
CS_CONNECTION	*connection;
{

	CS_RETCODE	retcode;
	CS_COMMAND	*cmd;
	CS_INT		res_type;	/* result type from ct_results */

	/*
	** Use the XNL_TEST database
	*/
        if ((retcode = ex_use_db(connection, Ex_dbname)) != CS_SUCCEED)
        {
                ex_error("DoCompute: ex_use_db(XNL_TEST) failed");
                return retcode;
        }

	/*
	** Allocate a command handle to send the compute query with
	*/
        if ((retcode = ct_cmd_alloc(connection, &cmd)) != CS_SUCCEED)
        {
                ex_error("DoCompute: ct_cmd_alloc() failed");
                return retcode;
        }

	/*
	** Define a language command that contains a compute clause.  SELECT1
	** is a select statment defined in the header file.
	*/
	retcode = ct_command(cmd, CS_LANG_CMD, SELECT1, CS_NULLTERM, CS_UNUSED);
	if (retcode != CS_SUCCEED)
	{
                ex_error("DoCompute: ct_command() failed");
                return retcode;
	}

	/*
	** Send the command to the server 
	*/
	if (ct_send(cmd) != CS_SUCCEED)
	{
                ex_error("DoCompute: ct_send() failed");
                return retcode;
	}
					
	/*
	** Process the results.  Loop while ct_results() returns CS_SUCCEED.
	*/
	while ((retcode = ct_results(cmd, &res_type)) == CS_SUCCEED)
	{
		switch ((int)res_type)
		{
		case CS_CMD_SUCCEED:
			/*
			** This means no rows were returned.  
			*/
			break;

		case CS_CMD_DONE:
			/*
			** This means we're done with one result set.
			*/
   			break;

		case CS_CMD_FAIL:
			/*
			** This means that the server encountered an error while
			** processing our command.
			*/
			ex_error("DoCompute: ct_results() returned CMD_FAIL");
			break;

		case CS_ROW_RESULT:
			retcode = ex_fetch_data(cmd);
			if (retcode != CS_SUCCEED)
			{
				ex_error("DoCompute: ex_fetch_data() failed");
				return retcode;
			}
			break;

		case CS_COMPUTE_RESULT:
			retcode = FetchComputeResults(cmd);
			if (retcode != CS_SUCCEED)
			{
				ex_error("DoCompute: FetchComputeResults() failed");
				return retcode;
			}
			break; 

		default:
			/*
			** We got an unexpected result type.
			*/
			ex_error("DoCompute: ct_results() returned unexpected result type");
			return CS_FAIL;
		}
	}

	/*
	** We're done processing results. Let's check the
	** return value of ct_results() to see if everything
	** went ok.
	*/
	switch ((int)retcode)
	{
		case CS_END_RESULTS:
			/*
			** Everything went fine.
			*/
			break;

		case CS_FAIL:
			/*
			** Something went wrong.
			*/
			ex_error("DoCompute: ct_results() failed");
			return retcode;

		default:
			/*
			** We got an unexpected return value.
			*/
			ex_error("DoCompute: ct_results() returned unexpected result code");
			return retcode;
	}