dynamic_ins.c

sybase linux or unix develope library package

/* Program name : dynamic_ins.c
**
** Description  : This program makes use of dynamic sql to insert values into a
**		  table 'x' number of times. The insert statement contains
**		  placeholders with identifiers which is sent to the server to 
**		  be partially compiled and stored. Therefore, every time we
**		  call the insert routine, in effect we only pass  new values
**		  for the insert ( parameters to the temporary stored procs ).
**
** Script file  : dynamic_ins.sql is included at the end of this file.
**
** References   : Open Client-Library/C reference manual. Refer to the sections 
**		  on Dynamic SQL and ct_dynamic.
**
** Note 	: Dynamic SQL is mainly intended for precompiler support.
**
*/

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

/* Macro definitions for error handling */
#define RETURN_IF(a,b) if (a != CS_SUCCEED)\
	{ fprintf(stderr, "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);}

/* 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 		do_dynamic_insert PROTOTYPE((
						CS_COMMAND	*,
					        CS_CHAR	        *,
						CS_INT
						));
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;  	/* 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 CLib 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);

				/*
				** send a dynamic insert to the server
				** the ? marks are placeholders for
				** values to be supplied latter
				** The integer is the number of times
				** to repeat the insert, in this case, 2.
				*/

	ret = do_dynamic_insert(cmd_ptr, "insert tempdb..test values (? ,?)",2);
		EXIT_IF(ret);

				/* drop the command structure */
	ret = ct_cmd_drop (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("End of program run!\n");
	exit(0);
}

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

				/* Install the server error handler */
	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-library error handler */
        retcode = ct_callback (*cntx_ptr, NULL, CS_SET, CS_CLIENTMSG_CB,
                                (CS_VOID *)ctlib_client_msg_handler);
          RETURN_IF ( retcode, "ct_callback" );
				 
	return retcode;

}

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_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_RETCODE
do_dynamic_insert(cmd_ptr, insert_statement, repeat_count)
CS_COMMAND	*cmd_ptr;
CS_CHAR		*insert_statement;
CS_INT		repeat_count;
{
	CS_RETCODE	ret;
	CS_CHAR		col2[255];
	CS_INT		col1;
	CS_DATAFMT 	param_fmt;
	int 		i;

				/* prepare the dynamic statement */
	ret = ct_dynamic(cmd_ptr, 
			CS_PREPARE, "d_insert", strlen("d_insert"),
			insert_statement, CS_NULLTERM);
		RETURN_IF(ret, "prepare phase failed");

	ret = ct_send ( cmd_ptr ); 	
		RETURN_IF(ret, "prepare phase send failed");

	ret = handle_returns ( cmd_ptr );
		RETURN_IF(ret, "prepare phase results failed");

				/* 
				** loop the requested number of times 
				** prompting for parameters and
				** sending actual values to server 
				*/
	for(i=0; i < repeat_count; i++)
	{
		printf("Enter value for col1 ( int ) ");
		fflush(stdout);
		scanf("%d",&col1);
		/* fflush (stdin); */
		getchar();
		printf("Enter value for col2 ( char )");
		gets(col2);


				/* Execute phase of the dynamic statement */
		ret = ct_dynamic(cmd_ptr, CS_EXECUTE, 
					"d_insert", strlen("d_insert"), 
					NULL, CS_UNUSED);
			RETURN_IF(ret, "execute phase failed");	

				/* prepare the first parameter description */
		strcpy(param_fmt.name, "@col1");
		param_fmt.namelen = CS_NULLTERM;
		param_fmt.status = CS_INPUTVALUE;
		param_fmt.datatype = CS_INT_TYPE;
		param_fmt.format = CS_FMT_UNUSED;
				/* set up the first paramenter */
		ret = ct_param(cmd_ptr, &param_fmt, &col1, CS_SIZEOF(col1), 
				CS_UNUSED);
			RETURN_IF(ret, "parameter one setup failed");

				/* prepare the second parameter description */
		strcpy(param_fmt.name, "@col2");
		param_fmt.namelen = CS_NULLTERM;
		param_fmt.status = CS_INPUTVALUE;
		param_fmt.datatype = CS_CHAR_TYPE;
		param_fmt.format = CS_FMT_NULLTERM;

				/* set up the second paramenter */
		ret = ct_param(cmd_ptr, &param_fmt, col2, 
				strlen(col2), CS_UNUSED);
			RETURN_IF(ret, "second parameter setup failed");

				/* send the parameter packet to server */
		ret = ct_send(cmd_ptr);
			RETURN_IF(ret,"do_dynamic: ct_send: parameters failed");

		handle_returns(cmd_ptr);
			RETURN_IF(ret,"do_dynamic: handle returns ");
	} /*end of do insert loop */
				
				/* clean up the dynamic insert */
	ret = ct_dynamic(cmd_ptr, CS_DEALLOC, "d_insert", strlen("d_insert"), 
					NULL, CS_UNUSED);
			RETURN_IF(ret, "do_dynamic, dealloc failed");

	ret = ct_send(cmd_ptr);
			RETURN_IF(ret,"do_dynamic: ct_send: dealloc failed");

	ret = handle_returns(cmd_ptr);
			RETURN_IF(ret,"do_dynamic: handle returns ");

	return CS_SUCCEED;

}

CS_RETCODE
handle_returns(cmd_ptr)
CS_COMMAND *cmd_ptr;
{

	CS_RETCODE results_ok;
	CS_INT result_type;
				/*
                                ** Loop on a request to return result
                                ** descriptions until no more are available
                                */
	while((results_ok = ct_results(cmd_ptr, &result_type)) == CS_SUCCEED)
	{
		switch ((int)result_type)
		{
			case CS_ROW_RESULT:
					  printf("TYPE: ROW RESULT \n");
					  break ;

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

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

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

			case CS_PARAM_RESULT :
					  printf("TYPE: PARAM RESULT \n");
					  break ;

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