cursor_sel.c

sybase linux or unix develope library package

/* Program Name	: cursor_sel.c 
**
** Description  : This program uses a cursor to retrieve data from a table.
**		  It also accepts an input parameter for the 'where' clause.
** 
** Inputs	: Value for the input parameter ( 'state' column from the
**		  'publishers' table ).
**
** References   : Open Client Reference manual pages for ct_cursor and also
**		  ct_param.
**
*/

#include <stdio.h>
#include <ctpublic.h>
#include "./example.h"

/* Macro definitions for error handling */
#define RETURN_IF(a,b) if (a != CS_SUCCEED) \
	{fprintf(stdout,"error in %s\n",b); return a;}

#define EXIT_IF(a) if (a != CS_SUCCEED) \
	{fprintf(stdout,"FATAL error on line %d \n",__LINE__); exit(-1) ;}

/* Global definitions */
#define MAX_COLUMN 	10 
#define MAX_COLSIZE 	255
#define MAX_COMMAND	255
char			rowbuffer[MAX_COLUMN][MAX_COLSIZE] ;
char			commandbuff[MAX_COMMAND] ;

/* Function declarations */
CS_RETCODE      init_db PROTOTYPE((
                                                CS_CONTEXT      **
                                                ));               
CS_RETCODE      connect_db PROTOTYPE((
                                                CS_CONTEXT      *,
                                                CS_CONNECTION   **,
                                                CS_CHAR         *, 
                                                CS_CHAR         * 
                                            ));                  
CS_RETCODE      cleanup_db PROTOTYPE((
                                                CS_CONTEXT      *,
                                                CS_RETCODE        
                                            ));           
CS_RETCODE      handle_returns PROTOTYPE((
                                                CS_COMMAND      *
                                            ));
CS_RETCODE      bind_columns PROTOTYPE((
                                                CS_COMMAND      *
                                            ));                  
CS_RETCODE      fetch_n_print PROTOTYPE((
                                                CS_COMMAND      *
                                            ));                  
CS_RETCODE      open_cursor PROTOTYPE((
                                                CS_COMMAND   *
                                            ));              

extern CS_RETCODE CS_PUBLIC     ctlib_client_msg_handler PROTOTYPE((
                                                CS_CONTEXT      *,  
                                                CS_CONNECTION   *,
                                                CS_CLIENTMSG    * 
                                                ));              
extern CS_RETCODE CS_PUBLIC     ctlib_server_msg_handler PROTOTYPE((
                                                CS_CONTEXT      *,  
                                                CS_CONNECTION   *,
                                                CS_SERVERMSG    * 
                                                ));              

int main()
{
	CS_CONTEXT	*cntx_ptr ;
	CS_CONNECTION	*conn_ptr ;
	CS_COMMAND	*cmd_ptr ;
	CS_RETCODE	ret ;
	CS_INT		command_type ;

		/* Allocate a context and initialize client-library */
	ret = init_db(&cntx_ptr);
	  	EXIT_IF(ret);

		/* Establish a connection to the server */
	ret = connect_db (cntx_ptr, &conn_ptr, EX_USERNAME, EX_PASSWORD);
 		EXIT_IF (ret);
	
		/* Allocate a command structure */
	ret = ct_cmd_alloc (conn_ptr, &cmd_ptr );
 		EXIT_IF (ret);

		/* Perform cursor operations */
	ret = open_cursor ( cmd_ptr); 
 		EXIT_IF (ret);

		/* Drop the command structure */
	ret = ct_cmd_drop ( cmd_ptr );
		EXIT_IF ( ret );

		/* Close connection to the server */
	ret = ct_close ( conn_ptr, CS_UNUSED ) ;	
 		EXIT_IF (ret);

		/* Drop the context and do general cleanup */
	ret = cleanup_db (cntx_ptr, ret );
 		EXIT_IF (ret);

	printf("\n End of program run! \n");
	exit(0);
}

CS_RETCODE 	init_db (cntx_ptr)
CS_CONTEXT	**cntx_ptr ;
{
	CS_RETCODE	retcode ;

			/* allocate a context */
	retcode = cs_ctx_alloc ( CS_VERSION_100,cntx_ptr );
		RETURN_IF ( retcode, "cs_ctx_alloc ");

			/* initialize the library */
	retcode = ct_init (*cntx_ptr,  CS_VERSION_100 );
		RETURN_IF ( retcode, "ct_init ");

			/* install the server message callback */
	retcode = ct_callback ( *cntx_ptr, NULL, CS_SET, CS_SERVERMSG_CB,
				(CS_VOID *)ctlib_server_msg_handler) ;
		RETURN_IF ( retcode, "ct_callback ");

			/* install the client message callback */
	retcode = ct_callback (*cntx_ptr, NULL, CS_SET, CS_CLIENTMSG_CB,
				(CS_VOID *)ctlib_client_msg_handler);
		RETURN_IF ( retcode, "ct_callback: CLIENTMSG ");

	return retcode ;
}

CS_RETCODE 	cleanup_db ( cntx_ptr, status )
CS_CONTEXT	*cntx_ptr ;
CS_RETCODE	status ;
{
	CS_RETCODE	retcode ;
	CS_INT		exit_type ;

	exit_type = ( status != CS_SUCCEED ) ? CS_FORCE_EXIT : CS_UNUSED ;

			/* close and cleanup connection to the server */
	retcode = ct_exit (cntx_ptr, exit_type );
		RETURN_IF ( retcode, "db_cleanup:ct_exit ");

			/* drop the context */
	retcode = cs_ctx_drop (cntx_ptr) ;
		RETURN_IF ( retcode, "db_cleanup:cs_drop ");

	return retcode ; 
}	

CS_RETCODE	connect_db (cntx_ptr, conn_ptr, user_name, password )
CS_CONTEXT	*cntx_ptr ;
CS_CONNECTION	**conn_ptr ;
CS_CHAR		*user_name, *password ;
{
	CS_RETCODE	retcode ;

			/* Allocate a connection pointer */
	retcode = ct_con_alloc (cntx_ptr, conn_ptr );
		RETURN_IF ( retcode, "connect_db: ct_con_alloc ");

			/*  Set the username and password properties */
	retcode = ct_con_props ( *conn_ptr, CS_SET, CS_USERNAME, user_name,
					CS_NULLTERM, NULL );
		RETURN_IF ( retcode, "connect_db: ct_con_props: username ");

	retcode = ct_con_props ( *conn_ptr, CS_SET, CS_PASSWORD, password,
					CS_NULLTERM, NULL );
		RETURN_IF ( retcode, "connect_db: ct_con_props: password"); 

			/* connect to the server */
	retcode = ct_connect ( *conn_ptr, NULL, 0 ) ;
		RETURN_IF ( retcode, "connect_db: ct_connect ") ;

	return  CS_SUCCEED ;
}

CS_RETCODE	handle_returns ( cmd_ptr )
CS_COMMAND	*cmd_ptr ;
{
	CS_RETCODE	results_ok ;
	CS_INT		result_type ;

		/* Process all returned result types */
	while (( results_ok = ct_results ( cmd_ptr, &result_type)) == CS_SUCCEED )
	{
		switch ((int)result_type)
		{
			case CS_ROW_RESULT:
				  printf("TYPE: ROW RESULT. \n");
				  results_ok = bind_columns ( cmd_ptr );
				  RETURN_IF ( results_ok, "bind columns");
				  results_ok = fetch_n_print ( cmd_ptr );
				  RETURN_IF ( results_ok, "fetch_n_print");
				  break ;

			case CS_CMD_SUCCEED:	
				  printf("TYPE: COMMAND SUCCEEDED. \n");
				  break ;

			case CS_CMD_DONE   :
				  printf("TYPE: COMMAND DONE. \n");
				  break ;

			case CS_CMD_FAIL   :
				  printf("TYPE: COMMAND FAIL. \n");
				  break ;

			case CS_CURSOR_RESULT :
				  printf("TYPE: CURSOR RESULT ");
				  results_ok = bind_columns ( cmd_ptr );
				  RETURN_IF(results_ok, "bind columns");
				  results_ok = fetch_n_print(cmd_ptr);
				  RETURN_IF ( results_ok, "fetch row");
				  break ;

			default    :
				  RETURN_IF ( CS_FAIL, "unknown results \n");
				  break ;
		} ;
	}
	if ( results_ok == CS_END_RESULTS )
	 	return CS_SUCCEED ;
	return CS_FAIL ;
}

CS_RETCODE 	bind_columns (cmd_ptr )
CS_COMMAND	*cmd_ptr ;
{
	CS_RETCODE	retcode ;
	CS_INT		i , num_cols ;
	CS_DATAFMT	target_format ;

	retcode = ct_res_info( cmd_ptr, CS_NUMDATA, &num_cols, CS_UNUSED, NULL);
		RETURN_IF ( retcode, "ct_bind : ct_res_info ");

	for ( i = 1 ; i <= num_cols ; i++ )
	{
		memset ( &target_format, 0, CS_SIZEOF(target_format)) ;

		target_format.datatype = CS_CHAR_TYPE ;
		target_format.maxlength = MAX_COLSIZE  ;
		target_format.count    = 1 ;
		target_format.format   = CS_FMT_NULLTERM ;

			 /* Bind returned data to host variables */
		retcode = ct_bind (cmd_ptr, i, &target_format, rowbuffer[i-1],
					NULL, NULL ) ;

		RETURN_IF ( retcode, "bind_columns: ct_bind ");
	}
	return CS_SUCCEED ;
}

CS_RETCODE	fetch_n_print ( cmd_ptr )
CS_COMMAND	*cmd_ptr ;
{
	CS_RETCODE 	retcode ;
	CS_INT		i, num_cols ;
	
	retcode = ct_res_info ( cmd_ptr, CS_NUMDATA, &num_cols, 
				CS_UNUSED, NULL );
		RETURN_IF ( retcode, "fetch_n_print: ct_res_info ");

	printf("\n");

		/* Fetch the bound data into host variables */
	while (( (retcode = ct_fetch(cmd_ptr, CS_UNUSED, CS_UNUSED, CS_UNUSED,
				(CS_INT *)NULL)) == CS_SUCCEED )	
	||
	      ( retcode == CS_ROW_FAIL ))
	{
		if ( retcode == CS_ROW_FAIL )
		{
			printf("ct_fetch returned row fail \n");
			continue ;
		}

		for ( i = 1 ; i <= num_cols ; i++ )
		{
			printf(" %s  ", rowbuffer[i-1] );
		}
 		printf("\n");
	}
	printf("\n");

	if ( retcode != CS_END_DATA)
		RETURN_IF ( retcode, "fetch_n_print ") ;
	
	return CS_SUCCEED ;
}

CS_RETCODE open_cursor ( cmd_ptr )
CS_COMMAND	*cmd_ptr ;
{
	CS_RETCODE	ret ;
	CS_CHAR		command[150], inp_state[3];
	CS_DATAFMT	datafmt;

	strcpy(command,"select * from pubs2.dbo.publishers where state = @state");
	/* This cursor will retrieve the records of all publishers for a
	** given state. This value is to be input by the user.
	*/
	printf("Retrieve records of publishers from which state:");
	printf(" [CA/MA/DC] ?");
	gets(inp_state);

	/* Declare and open the cursor. */
	ret = ct_cursor ( cmd_ptr, CS_CURSOR_DECLARE, "browse_cursor",
			  CS_NULLTERM, command, CS_NULLTERM, CS_READ_ONLY );
		RETURN_IF ( ret, "ct_cursor: declare ");

	/* Declare the input parameter for the cursor. */

	memset(&datafmt,0,sizeof(datafmt));
	strcpy(datafmt.name,"@state");
	datafmt.namelen   = CS_NULLTERM  ;
        datafmt.datatype  = CS_CHAR_TYPE ;
        datafmt.status    = CS_INPUTVALUE ;
        datafmt.maxlength = CS_UNUSED;
        datafmt.locale    = NULL ;
  	ret = ct_param ( cmd_ptr, &datafmt, NULL, CS_UNUSED, CS_UNUSED );
		RETURN_IF ( ret, "ct_cursor: declare param "); 

	ret = ct_cursor ( cmd_ptr, CS_CURSOR_OPEN, NULL, CS_UNUSED, NULL,
			CS_UNUSED, CS_UNUSED );
		RETURN_IF ( ret, "ct_cursor: open "); ;

	/* Define the input parameter. */
	memset(&datafmt,0,sizeof(datafmt));
	strcpy(datafmt.name,"@state");
	datafmt.namelen   = CS_NULLTERM  ;
        datafmt.datatype  = CS_CHAR_TYPE ;
        datafmt.status    = CS_INPUTVALUE ;
        datafmt.maxlength = CS_UNUSED;
        datafmt.locale    = NULL ;
 
        ret = ct_param ( cmd_ptr , &datafmt, (CS_VOID *)inp_state, 
			 strlen(inp_state), CS_UNUSED );
		RETURN_IF ( ret, "open_cursor : ct_param ");

	ret = ct_send ( cmd_ptr );
		RETURN_IF ( ret, "open cursor : param send ");

	ret = handle_returns ( cmd_ptr ) ;
		RETURN_IF ( ret, "open cursor : param results ");

	ret = ct_cursor( cmd_ptr, CS_CURSOR_CLOSE, NULL, CS_UNUSED,
			 NULL,CS_UNUSED,CS_DEALLOC);
		RETURN_IF ( ret, "ct_cursor : close ");

	ret = ct_send ( cmd_ptr );
		RETURN_IF ( ret, "ct_send : close cursor ");

	ret = handle_returns ( cmd_ptr ) ;
		RETURN_IF ( ret, "ct_results : close cursor ");

	return CS_SUCCEED ;
}