dynamic_cur.c

sybase linux or unix develope library package

/* Program name : dynamic_cur.c 
**
** Description  : This program makes use of dynamic sql to retrieve values 
**		  from the 'titles' table in the 'pubs2' database.
**                The select statement contains placeholders with identifiers 
**		  which is sent to the server to be partially compiled and 
**		  stored. Therefore, every time we call the select , in effect 
**		  we only pass  new values for the key value based on which
**		  we wish a row to be retrieved. 
**		  The behaviour is like input parameters to stored procedures.
**		  It also uses cursors to retrieve rows one by one, which can
**		  be manipulated as required. 
**
** References   : Open Client-Library/C reference manual. Refer to the sections
**                on Dynamic SQL , ct_dynamic and ct_cursor.
**
** Note         : Dynamic SQL is mainly intended for precompiler support.
**
*/

#include<stdio.h>
#include<ctpublic.h>
#include<cstypes.h>
#include<string.h>
#include<stdlib.h>
#include <example.h>

#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(stderr, "FATAL ERROR! Line %d\n", __LINE__);exit(-1);}

CS_STATIC CS_RETCODE       init_db PROTOTYPE((
                                                CS_CONTEXT**
                                                ));
 
CS_STATIC CS_RETCODE       connect_db PROTOTYPE((
                                                CS_CONTEXT      *,
                                                CS_CONNECTION   **,
                                                CS_CHAR         *,
                                                CS_CHAR         *
                                            ));
CS_STATIC CS_RETCODE       cleanup_db PROTOTYPE((
                                                CS_CONTEXT      *,
                                                CS_RETCODE       
                                            ));
CS_STATIC CS_RETCODE       open_cursor PROTOTYPE((
                                                CS_COMMAND      *
					    ));
CS_STATIC CS_RETCODE       send_sql PROTOTYPE((
                                                CS_COMMAND      *,
						CS_CHAR		*
                                            ));
CS_STATIC CS_RETCODE       handle_returns PROTOTYPE((
                                                CS_COMMAND      *
                                            ));
CS_STATIC CS_RETCODE       bind_columns PROTOTYPE((
                                                CS_COMMAND      *
                                            ));
CS_STATIC CS_RETCODE       fetch_n_print PROTOTYPE((
                                                CS_COMMAND      *
                                            ));


/*
** Forward declaration of routines to be called in the event of messages 
** or error reports.
*/

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    *
                                                ));
 
/* Global variables and defines*/

#define	MAXCOLSIZE	255
CS_CHAR		rowbuffer[MAXCOLSIZE][MAXCOLSIZE];

int main()
{
	CS_CONTEXT  	*cntx_ptr;  	/* common context area pointer */
	CS_CONNECTION 	*conn_ptr;	/* handle for this server connection */
	CS_COMMAND	*cmd_ptr;	/* handle for this SQL command batch */
	CS_RETCODE	ret;   		/* to hold all CT-Lib function returns */

				/* setup context of database connections */
	ret = init_db(&cntx_ptr); 
		EXIT_IF(ret);
				/* connect to SQL Server */	
	ret = connect_db(cntx_ptr, &conn_ptr, EX_USERNAME, EX_PASSWORD );
		EXIT_IF(ret);

				/* alloc a command struct (controls SQL sent) */
	ret = ct_cmd_alloc(conn_ptr, &cmd_ptr);
		EXIT_IF(ret);

				/* Begin cursor operations */
	ret = open_cursor(cmd_ptr);
		EXIT_IF(ret);
	
				/* close the connection to the server */
	ret = ct_close(conn_ptr, CS_UNUSED);
		EXIT_IF(ret);

	ret = cleanup_db(cntx_ptr, ret);/*cleanup at both Client & Server*/
		EXIT_IF(ret);

	printf("Program completed successfully! \n");
	exit(0);
}

CS_STATIC CS_RETCODE
init_db(cntx_ptr)
CS_CONTEXT **cntx_ptr;
{
	CS_RETCODE retcode;
				/* initialize context handle */

	retcode = cs_ctx_alloc(CS_VERSION_100, cntx_ptr); 
		RETURN_IF(retcode, "ct_ctx_alloc");

  				/* initialize the library */
	retcode = ct_init(*cntx_ptr, CS_VERSION_100);    
		RETURN_IF(retcode, "ct_init");
				 
	
        retcode = ct_callback ( *cntx_ptr, NULL, CS_SET, CS_SERVERMSG_CB,
                                (CS_VOID *)ctlib_server_msg_handler) ;
                RETURN_IF ( retcode, "ct_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_STATIC CS_RETCODE
cleanup_db(cntx_ptr, status)
CS_CONTEXT 	*cntx_ptr;
CS_RETCODE	status;
{
	CS_RETCODE	retcode;
	CS_INT	exit_type;
				/* set exit_type to FORCE if bad retcode */
	exit_type = (status != CS_SUCCEED) ? CS_FORCE_EXIT : CS_UNUSED;

				/* break the server connection */
	retcode = ct_exit(cntx_ptr, exit_type);
		RETURN_IF(retcode, "db_cleanup:ct_exit");

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

	return retcode;
}

CS_STATIC CS_RETCODE
connect_db(cntx_ptr, conn_ptr, user_name, password)
CS_CONTEXT *cntx_ptr;
CS_CONNECTION **conn_ptr;
CS_CHAR	*user_name;
CS_CHAR	*password;
{
	CS_RETCODE	ret;
				/* allocate the connection and init conn_ptr */
	ret = ct_con_alloc(cntx_ptr, conn_ptr);
		RETURN_IF(ret, "connect_db: ct_con_alloc");

				/* set the user name and password properties */
	ret = ct_con_props(*conn_ptr, CS_SET, CS_USERNAME,
				  user_name, CS_NULLTERM, NULL);
		RETURN_IF(ret, "connect_db: ct_conprops USERNAME");

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

				/* request connection to SQL Server */
 	ret = ct_connect(*conn_ptr, NULL, 0);
		RETURN_IF(ret, "connect_db: ct_connect");

	return CS_SUCCEED;
}
							
CS_STATIC CS_RETCODE open_cursor(cmd)
CS_COMMAND	*cmd ;
{  

  	CS_DATAFMT         data_format;
	CS_RETCODE	   retcode;
  	char               title_id[7], statement[100];
	int		   i = 0;

  	/* Prepare the sql statement for the first cursor */
        sprintf(statement, "select title_id, type, price from pubs2..titles where title_id = (?) ");
  	retcode = ct_dynamic(cmd, CS_PREPARE, "mycursor", CS_NULLTERM, 
				statement, CS_NULLTERM);
		RETURN_IF(retcode, "open_cursor: ct_dynamic prepare");

      		/* Send the prepared statement to the server */
  	retcode = ct_send(cmd);
		RETURN_IF(retcode, "open_cursor: ct_dynamic send");

  		/* See if the prepared statement was successful */
	retcode = handle_returns(cmd);
		RETURN_IF(retcode, "open_cursor: ct_dynamic results");

  		/* Declare a cursor for the prepared statement */
  	retcode = ct_dynamic(cmd, CS_CURSOR_DECLARE, "mycursor",
     				CS_NULLTERM, "mycursor", CS_NULLTERM);
		RETURN_IF(retcode, "open_cursor: cursor declare");

  		/* Set the cursor to read only */
  	retcode = ct_cursor(cmd, CS_CURSOR_OPTION, NULL, CS_UNUSED, 
    				NULL, CS_UNUSED, CS_READ_ONLY);
		RETURN_IF(retcode, "open_cursor: cursor options ");

  	/* We need to get values for pub_id */
	printf("Enter title id value - enter an X if you wish to stop: \n");
	while ( toupper(title_id[0]) != 'X' ) 
	{
	printf("Retrieve detail record for title id: ?");
	gets(title_id);

	/* Open the cursor */
  	retcode = ct_cursor(cmd, CS_CURSOR_OPEN, NULL, CS_UNUSED, 
    				NULL, CS_UNUSED, CS_UNUSED);
		RETURN_IF(retcode, "open_cursor: cursor open ");

	/* Define the input parameter */
  	memset ( &data_format, 0, CS_SIZEOF(data_format)) ;
    	data_format.status = CS_INPUTVALUE;
        data_format.namelen = CS_NULLTERM ;
        data_format.datatype = CS_CHAR_TYPE;
        data_format.format = CS_FMT_NULLTERM;
        data_format.maxlength = 255;
        data_format.count = 1;
 
        retcode = ct_param(cmd, &data_format, (CS_VOID *)title_id,
                                strlen(title_id), CS_UNUSED);
		RETURN_IF(retcode, "open_cursor: cursor param ");
 
			/* cursor open and params command */
  	retcode = ct_send(cmd); 
		RETURN_IF(retcode, "open_cursor: cursor param send");

	retcode = handle_returns (cmd);
		RETURN_IF(retcode, "open_cursor: cursor param results");

  	retcode = ct_cursor(cmd, CS_CURSOR_CLOSE, NULL, CS_UNUSED,
                                NULL, CS_UNUSED, CS_UNUSED);
		RETURN_IF(retcode, "open_cursor: cursor close ");
   
	retcode = ct_send(cmd);
		RETURN_IF(retcode, "open_cursor: cursor close send");
   
	retcode = handle_returns (cmd);
		RETURN_IF(retcode, "open_cursor: cursor close results");

	} /* end of loop to retrieve values*/

	/* Now, deallocate the prepared statement */
  	retcode = ct_dynamic(cmd, CS_DEALLOC, "mycursor", CS_NULLTERM, 
				NULL, CS_UNUSED);
		RETURN_IF(retcode, "ct_dynamic dealloc");

      		/* Send the dealloc statement to the server */
  	retcode = ct_send(cmd);
		RETURN_IF(retcode, "ct_dynamic dealloc send");

		/* process results from above commend */
	retcode = handle_returns(cmd);
		RETURN_IF(retcode, "ct_dynamic dealloc results");

  return CS_SUCCEED ;
}


CS_STATIC CS_RETCODE	send_sql (cmd_ptr, sqltext)
CS_COMMAND	*cmd_ptr ;
CS_CHAR		sqltext[100];
{
	CS_RETCODE	retcode ;

 	retcode = ct_command (cmd_ptr, CS_LANG_CMD, sqltext,
				CS_NULLTERM, CS_UNUSED );
		RETURN_IF ( retcode, "ct_command ");

	retcode = ct_send (cmd_ptr);
		RETURN_IF ( retcode, "ct_send ");

	retcode = handle_returns(cmd_ptr);
		RETURN_IF ( retcode, "ct_results ");

	return CS_SUCCEED ;
}

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

	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:	
				  break ;

			case CS_CMD_DONE   :
				  break ;

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

			case CS_PARAM_RESULT :
				  printf("TYPE: PARAM 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_CURSOR_RESULT :
                                          printf("TYPE: CURSOR RESULT \n\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 row");
                                          break ;

			case CS_STATUS_RESULT :
				  printf("TYPE: STATUS RESULTS \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_COMPUTE_RESULT :
				  printf("TYPE: COMPUTE RESULTS \n");
				  break ;

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

CS_STATIC CS_RETCODE 	bind_columns (cmd_ptr)
CS_COMMAND	*cmd_ptr ;
{
	CS_RETCODE	retcode ;
	CS_INT		i , num_cols , ind;
	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 = 255  ;
		target_format.count    = 1 ;
		target_format.format   =  CS_FMT_NULLTERM;
		target_format.locale   = NULL;

		retcode = ct_bind ( cmd_ptr, i, &target_format, rowbuffer[i-1],
					&ind, NULL ) ;
		RETURN_IF ( retcode, "bind_columns: ct_bind ");

	}
	return CS_SUCCEED ;
}

CS_STATIC CS_RETCODE	fetch_n_print (cmd_ptr)
CS_COMMAND	*cmd_ptr ;
{
	CS_RETCODE 	retcode ;
	CS_INT		len, 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 ");


	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 \t ", rowbuffer[i-1] );
		}
		printf("\n");
	}

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