bulkcopy.c

sybase linux or unix develope library package

/* Program name : bulkcopy.c 
** 
** Description  : This program makes use of bulk library calls to bulk copy
**		  data into a copy of the publishers table, row by row. 
**		  We will treat all data as character strings, except for 
**		  the first field to demonstrate bulk binding with other
**		  data types. We don't need to specify SQL datatypes because
**		  the host data is in character format. Irrespective
**		  of their type in the SQL Server, the Server will perform
**		  the necessary conversions. However, if data is stored in 
**		  the native form ( binary ), we will need to define our 
**		  host variables accordingly.
**
** Script file  : Run bulkcopy.sql before executing this program.
**
** References   : Open Client/Open Server Common Libraries reference manual
**		  for bulk library routines.
**
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctpublic.h>
#include <bkpublic.h>
#include <example.h>

/* Macro definitions for error handling */
#define RETURN_IF(a,b) if (a != CS_SUCCEED) {fprintf(stdout,"Error - %s\n",b); return a;}
 
#define EXIT_IF(a) if (a != CS_SUCCEED) {fprintf(stdout,"FATAL error on line %d\n",__LINE__ ); exit(-1) ;}


/*
** Defines for the table used in the sample program
*/
#define MAX_PUBID       5
#define MAX_PUBNAME     41
#define MAX_PUBCITY     21
#define MAX_PUBST       3
#define MAX_BIO         255
#define MAXLEN          255
#define DATA_END        (0)
#define MAX_COLUMN      10
#define MAX_COLSIZE     255
 
 /* Global definitions */
typedef struct  _blk_data
{
        CS_INT          pub_id;
        CS_CHAR         pub_name[MAX_PUBNAME];
        CS_CHAR         pub_city[MAX_PUBCITY];
        CS_CHAR         pub_st[MAX_PUBST];
	CS_CHAR		pub_bio[MAX_BIO];
} Blk_Data;

/*
** static definition of table use in the example
*/
 
static  Blk_Data BLKDATA[] =
{
        {1,    "Taylor & Ng",  "San Francisco",        "CA",""},
        {2,    "Scarey Books", "Sleepy Hollow",        "MA",""},
        {3,    "Witch Craft & Spells", "Salem",        "MA",""},
        {DATA_END,      "",     "",     "",""},
};
char		rowbuffer[MAX_COLUMN][MAX_COLSIZE];

/* 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	send_sql PROTOTYPE((
						CS_COMMAND	*,
						CS_CHAR		*
				  	    ));
CS_RETCODE	handle_returns PROTOTYPE((
						CS_COMMAND	*
					    ));
CS_RETCODE 	bind_columns PROTOTYPE((
						CS_COMMAND	*
					    ));
CS_RETCODE	fetch_n_print PROTOTYPE((
						CS_COMMAND	*
				            ));
CS_RETCODE 			write_to_table PROTOTYPE((
						CS_CONNECTION	*
						));

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 ;

		/* Initialize a context */
	ret = init_db(&cntx_ptr);
	  EXIT_IF(ret);

		/* Routine to make 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);

		/* Send a command to set the database context */
	ret = send_sql (cmd_ptr, "use tempdb");
	  EXIT_IF(ret);

		/* handle results from the current command */
	ret = handle_returns ( cmd_ptr );
	  EXIT_IF(ret);
	
		/* Call the bulk copy routine */
        ret = write_to_table(conn_ptr);
	  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);

		/* Clean up the context */
	ret = cleanup_db (cntx_ptr, ret );
 		EXIT_IF ( ret );

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


CS_RETCODE write_to_table(connection)
CS_CONNECTION   *connection; 
{
	CS_BLKDESC      *blkdesc;
    	CS_DATAFMT	datafmt;	/* hold the variable descriptions  */
	Blk_Data 	*dptr;		/* point to the data for transfer */
	CS_INT		datalen[5];	/* the length of the variable data */
	CS_INT          numrows;
	CS_RETCODE      retcode ;

	/*
	** We are now ready to start the bulk copy operation.
	** Start by getting the bulk descriptor and initialize
	*/
	retcode = blk_alloc(connection, BLK_VERSION_100, &blkdesc);
		  RETURN_IF ( retcode, "Bulk alloc ");

	retcode = blk_init(blkdesc, CS_BLK_IN,
				 "test_pubs", strlen("test_pubs"));
		  RETURN_IF ( retcode, "Bulk Init ");


	/*
	** Now bind the variables to the columns and transfer the data
	*/
        datafmt.locale = 0;
	datafmt.count = 1;
	dptr = BLKDATA;
	while (dptr->pub_id != DATA_END)
	{
		datafmt.datatype = CS_INT_TYPE;
		datafmt.maxlength = sizeof(CS_INT);
		datalen[0] = CS_UNUSED;

		if (blk_bind(blkdesc, 1, &datafmt, &dptr->pub_id, 
					&datalen[0], NULL) != CS_SUCCEED)
		{
			printf("BulkCopyIn: blk_bind(1) failed");
			return CS_FAIL;
		}
		datafmt.datatype = CS_CHAR_TYPE;
		datafmt.maxlength = MAX_PUBNAME - 1;
		datalen[1] = strlen(dptr->pub_name);
		if (blk_bind(blkdesc, 2, &datafmt, (CS_VOID *)dptr->pub_name,
					 &datalen[1], NULL) != CS_SUCCEED)
		{
			printf("BulkCopyIn: blk_bind(2) failed");
			return CS_FAIL;
		}
		datafmt.datatype = CS_CHAR_TYPE;
		datafmt.maxlength = MAX_PUBCITY - 1;
		datalen[2] = strlen(dptr->pub_city);
		if (blk_bind(blkdesc, 3, &datafmt, (CS_VOID *)dptr->pub_city,
					 &datalen[2], NULL) != CS_SUCCEED)
		{
			printf("BulkCopyIn: blk_bind(3) failed");
			return CS_FAIL;
		}
		datafmt.maxlength = MAX_PUBST - 1;
		datalen[3] = strlen(dptr->pub_st);
		if (blk_bind(blkdesc, 4, &datafmt, (CS_VOID *)dptr->pub_st,
					 &datalen[3], NULL) != CS_SUCCEED)
		{
			printf("BulkCopyIn: blk_bind(4) failed");
			return CS_FAIL;
		}
		datafmt.maxlength = MAX_BIO - 1;
		datalen[4] = strlen(dptr->pub_bio);
		if (blk_bind(blkdesc, 5, &datafmt, (CS_VOID *)dptr->pub_bio,
					 &datalen[4], NULL) != CS_SUCCEED)
		{
			printf("BulkCopyIn: blk_bind(5) failed");
			return CS_FAIL;
		}
		if (blk_rowxfer (blkdesc) == CS_FAIL)
		{
			printf("BulkCopyIn: blk_rowxfer() failed");
			return CS_FAIL;
		}

		dptr++;
	}
    	if (blk_done(blkdesc, CS_BLK_ALL, &numrows) == CS_FAIL)
        {
                printf("BulkCopyIn: blk_done() failed");
                return CS_FAIL;
        }

	fprintf(stderr, "Number of data rows transferred = %d.\n", numrows);
	fflush(stderr);

	if (blk_drop(blkdesc) == CS_FAIL)
	{
		printf("BulkCopyIn: blk_drop() failed");
		return CS_FAIL;
	}

        return CS_SUCCEED;
}


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 ;
	CS_BOOL		bool;

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

        /* enable bulk login property */
	bool = CS_TRUE;
  	retcode = ct_con_props( *conn_ptr, CS_SET, CS_BULK_LOGIN, 
				&bool, CS_UNUSED, NULL);
		RETURN_IF ( retcode, "connect_db: ct_con_props: bulk login"); 

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

	return  CS_SUCCEED ;
}

CS_RETCODE	send_sql (cmd_ptr, sqltext)
CS_COMMAND	*cmd_ptr ;
CS_CHAR		sqltext[30];
{
	CS_RETCODE	retcode ;
	CS_DATAFMT	datafmt ;

	printf("sql = %s\n",sqltext);
		/* Build and send the command to the server */
 	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 ");

	return CS_SUCCEED ;
}

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

		/* 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: 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");
					  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_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_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 = 255  ;
		target_format.count    = 1 ;
		target_format.format   =  CS_FMT_NULLTERM;
		target_format.locale   = NULL;

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


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