/*******************************************************************************
*                                                                       
*N  {schobj_table_data.c}  -- demonstrates table create, edit, query 
*                             and statistics
*   
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*P  Purpose:
*    This sample C program demonstrates how to create, edit, query and truncate
*	 tables; and how to retrieve statistics for a specific column.
*
*E                                                                 
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* How to compile:: 
*	 Please refer section "Build C Samples" in the SDE sample
*
*E
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* Usage:
*     schobj_table_data {server} {instance} {database} {user} {password} {keyword}
*
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*X  Legalese:
*
* Copyright © 2007 ESRI
*
* All rights reserved under the copyright laws of the United States and 
* applicable international laws, treaties, and conventions.
*
* You may freely redistribute and use this sample code, with or without 
* modification, provided you include the original copyright notice and use 
* restrictions.  
*
* Disclaimer:  THE SAMPLE CODE IS PROVIDED "AS IS" AND ANY EXPRESS OR IMPLIED 
* WARRANTIES, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 
* FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL ESRI OR 
* CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, 
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; 
* OR BUSINESS INTERRUPTION) SUSTAINED BY YOU OR A THIRD PARTY, HOWEVER CAUSED 
* AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR 
* TORT ARISING IN ANY WAY OUT OF THE USE OF THIS SAMPLE CODE, EVEN IF ADVISED 
* OF THE POSSIBILITY OF SUCH DAMAGE.
* 
* For additional information, contact:
* Environmental Systems Research Institute, Inc.
* Attn: Contracts and Legal Services Department
* 380 New York Street
* Redlands, California, 92373
* USA
* 
* email: contracts@esri.com
*
*E
****************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sdetype.h"
#include "sdeerno.h"

typedef enum {
  S_DM_BY_ROWID,
  S_DM_BY_WHERE
} S_DM_MODE;


/* Function macros */
#define check_rc_return_on_failure(c,s,rc,f) \
	{if(rc!= SE_SUCCESS) {check_rc_(c,s,rc,f,__LINE__,__FILE__);return SE_FAILURE; }}
#ifdef WIN32
	#define strcasecmp(s1,s2)   _stricmp(s1,s2)
#endif


/*****************************/
/*  Function Prototypes     */
/****************************/
void check_rc_(SE_CONNECTION Connection, SE_STREAM Stream, LONG rc, 
				 char *comment, LONG line_no, char* file_name) ;
static LONG S_create_table(SE_CONNECTION connection,CHAR *table,CHAR *keyword);
static LONG S_insert_rows(SE_CONNECTION  handle,const CHAR *table,
							 int num_of_row,BOOL buf_write_mode);
static LONG S_update_row(SE_CONNECTION  handle,const CHAR *table,
							S_DM_MODE update_mode, LONG rowid);
static LONG S_delete_rows(SE_CONNECTION  connection,const CHAR  *table,S_DM_MODE,
						    LONG  delete_count,const LONG *delete_list);
static LONG S_query_table(SE_CONNECTION connection, const CHAR *table,const CHAR* where);
static LONG S_print_table_stats(SE_CONNECTION  connection, const CHAR *table,const CHAR* col);

/************************************/
/*  Global variables Prototypes     */
/***********************************/
static CHAR ROWID_COL[SE_MAX_COLUMN_LEN+1];
//NEXT_INT16_VALUE -- We will used it to track value for the int16_col.
static SHORT NEXT_INT16_VALUE=1; 

LONG main(int argc, char *argv[]){
	CHAR   			*server, 
       	       		*user, 
					*passwd, 
					*database,
					*instance,
					*keyword;
	SE_CONNECTION 	conn;
	SE_ERROR 		error;
	LONG			result;
	CHAR			table[SE_MAX_TABLE_LEN+1];
	LONG			delete_list[10];
	LONG			delete_count;
	CHAR			where[128];

	if(argc<7){
		printf("Usage %s <server> <instance> <database> <user> <password> <keyword>\n\n",argv[0]);
		exit(1);
	}
	server=argv[1];
	instance=argv[2];
	database=argv[3];
	user=argv[4];
	passwd=argv[5];
	keyword=argv[6];

	strcpy(ROWID_COL,"ROWID_COL");
	strcpy(table,"SAMPLE_TBLDATA");
	strcpy(where,"");
	NEXT_INT16_VALUE=1;

	/*connect to SDE server*/
	result = SE_connection_create( server, instance, database, user, passwd, &error, &conn );
	check_rc_return_on_failure (conn,NULL,result,"SE_connection_create");
	printf("Conn: connected to %s:%s as %s\n",argv[1],argv[2],argv[4]);

	/*create sample table*/
	printf("Creating table... \n");
	result=S_create_table(conn,table,keyword);
	if(result!=SE_SUCCESS)
		exit(1);

	/*insert 10 rows into the sample table*/
	printf("Insert 5 rows with buffered insert disabled(row 1-5)...\n");
	result=S_insert_rows(conn,table,5,FALSE);
	if(result!=SE_SUCCESS)
		exit(1);
	printf("Insert 5 rows with buffered insert enabled(row 6-10)...\n");
	result=S_insert_rows(conn,table,5,TRUE);
	if(result!=SE_SUCCESS)
		exit(1);

	/*delete 6 rows from table with different methods */
    //Delete by ROWID:
	//if delete_count=1: SE_stream_delete_row will be used,
	//if delete_count>1: SE_stream_delete_by_id_list will be used,
	printf("Delete rows 1,2,3,4,5,6...\n");
	delete_count=1;
	delete_list[0]=1;
	result=S_delete_rows(conn,table,S_DM_BY_ROWID,delete_count, delete_list);
	delete_count=2;
	delete_list[0]=2;
	delete_list[1]=3;
	result=S_delete_rows(conn,table,S_DM_BY_ROWID,delete_count, delete_list);
	delete_count=3;
	delete_list[0]=4;
	delete_list[1]=5;
	delete_list[2]=6;
	//delete by where clause
	result=S_delete_rows(conn,table,S_DM_BY_WHERE,delete_count, delete_list);
	if(result!=SE_SUCCESS)
		exit(1);

	/*perform some updates with different methods*/
	printf("Update row 7 by rowid...\n");
	result=S_update_row(conn,table,S_DM_BY_ROWID, 7);
	if(result!=SE_SUCCESS)
		exit(1);
	printf("Update row 8 by where clause...\n");
	result=S_update_row(conn,table,S_DM_BY_WHERE, 7);
	if(result!=SE_SUCCESS)
		exit(1);

	/*Query the table*/
	printf("Query table....\n");
	S_query_table(conn,table,where);
	if(result!=SE_SUCCESS)
		return result;

	/*Get statistics on the INT32_COL column*/
	S_print_table_stats(conn,table,"INT32_COL");

	/*truncate the sample table: remove all the rows*/
	printf("\n\nTruncating sample table....\n");
	result=SE_table_truncate(conn,table);
	check_rc_return_on_failure (conn,NULL,result,"SE_layer_truncate");

	/*delete the sample table*/
	printf("\n\nDelete sample table....\n");
	result=SE_table_delete(conn,table);
	check_rc_return_on_failure( conn, NULL, result, "SE_table_delete" );

	SE_connection_free(conn);

	return SE_SUCCESS;

}


/***********************************************************************
*
*N  {S_create_table}  -  create a test table with provided specification
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Create a sample table with provided specification
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>  ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*     <keyword >    ==  (CHAR*) the keyword for table creation
*E
***********************************************************************/
static LONG S_create_table(SE_CONNECTION connection,CHAR *table,CHAR *keyword)
{
	SHORT               number_of_columns;
	SE_COLUMN_DEF       column_definitions[8];
	LONG                result;
	SE_REGINFO          registration;
	CHAR				reg_desc[128];

	/* Create the base table. */
	number_of_columns = 7;
	strcpy(column_definitions[0].column_name,"STR_COL");
	column_definitions[0].sde_type = SE_STRING_TYPE;
	column_definitions[0].size = 60;
	column_definitions[0].decimal_digits = -1;
	column_definitions[0].nulls_allowed = FALSE;

	strcpy(column_definitions[1].column_name,"INT16_COL");
	column_definitions[1].sde_type = SE_INT16_TYPE;
	column_definitions[1].size = 0;
	column_definitions[1].decimal_digits = 0;
	column_definitions[1].nulls_allowed = TRUE;

	strcpy (column_definitions[2].column_name,"INT32_COL");
	column_definitions[2].sde_type = SE_INT32_TYPE;
	column_definitions[2].size = 0;
	column_definitions[2].decimal_digits = 0;
	column_definitions[2].nulls_allowed = TRUE;

	strcpy (column_definitions[3].column_name,"FLOAT32_COL");
	column_definitions[3].sde_type = SE_FLOAT32_TYPE;
	column_definitions[3].size = 0;
	column_definitions[3].decimal_digits = 0;
	column_definitions[3].nulls_allowed = TRUE;

	strcpy (column_definitions[4].column_name,"FLOAT64_COL");
	column_definitions[4].sde_type = SE_FLOAT64_TYPE;
	column_definitions[4].size = 0;
	column_definitions[4].decimal_digits = 0;
	column_definitions[4].nulls_allowed = TRUE;

	strcpy (column_definitions[5].column_name,"DATE_COL");
	column_definitions[5].sde_type = SE_DATE_TYPE;
	column_definitions[5].size = 0;
	column_definitions[5].decimal_digits = 0;
	column_definitions[5].nulls_allowed = TRUE;

	strcpy (column_definitions[6].column_name,"BLOB_COL");
	column_definitions[6].sde_type = SE_BLOB_TYPE;
	column_definitions[6].size = 0;
	column_definitions[6].decimal_digits = 0;
	column_definitions[6].nulls_allowed = TRUE;

	//delete the table if already existed
	result = SE_table_delete(connection,table);
	if(result!=SE_TABLE_NOEXIST)
		check_rc_return_on_failure (connection,NULL,result,"SE_table_delete");
	//create the base table
	result = SE_table_create (connection,table,number_of_columns,
							column_definitions,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_create");

	/* Add an SDE maintained rowid */
	result = SE_reginfo_create (&registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_create");
	result = SE_registration_get_info (connection,table,registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_registration_get_info");
	result = SE_reginfo_set_creation_keyword (registration,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_creation_keyword");
	strcpy(reg_desc,"BASE TABLE FOR qa_layer_data");
	result = SE_reginfo_set_description (registration,reg_desc );
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_description");
	result = SE_reginfo_set_rowid_column (registration,ROWID_COL,
							   SE_REGISTRATION_ROW_ID_COLUMN_TYPE_SDE);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_rowid_column");
	result = SE_registration_alter (connection,registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_registration_alter");
	SE_reginfo_free(registration);

	return(SE_SUCCESS);
}           



/***********************************************************************
*
*N  {S_insert_rows}  -  insert rows into a table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Insert rows into a table
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <handle>        ==  (SE_CONNECTION) The connection handle.
*     <table >        ==  (CHAR*) the layer name
*     <num_of_row>    ==  (LONG) number of rows to be inserted
*     <buf_write_mode>==  (BOOL) using buffer writers or not
*E
***********************************************************************/
static LONG S_insert_rows(SE_CONNECTION  handle,const CHAR *table,
							 int num_of_row,BOOL buf_write_mode)
{
	SE_STREAM       stream;
	LONG            result,row;
	SHORT           number_of_columns;
	CHAR            **base_columns;
	LONG			part_offsets[1];
	CHAR            str_val[32];
	char			ansi_str[32];
	SHORT           int16_val;
	SE_INT32        int32_val;
	FLOAT           float32_val;
	LFLOAT          float64_val;
	struct tm       date_val;
	SE_BLOB_INFO    blob_val;
	SHORT           str_ind,int16_ind, float32_ind, int32_ind,
				    float64_ind, date_ind,blob_ind;
	LONG			blob_size,count,i;


	number_of_columns = 7;
	base_columns = (CHAR **) malloc (number_of_columns * sizeof(CHAR *));
	for(i=0;i<number_of_columns;i++)
		base_columns[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN * sizeof(CHAR));
	
	/* Set up for insert. */
	strcpy(	base_columns[0],"STR_COL");
	strcpy(	base_columns[1],"INT16_COL");
	strcpy(	base_columns[2],"INT32_COL");
	strcpy(	base_columns[3],"FLOAT32_COL");
	strcpy(	base_columns[4],"FLOAT64_COL");
	strcpy(	base_columns[5],"DATE_COL");
	strcpy(	base_columns[6],"BLOB_COL");

	result = SE_stream_create (handle,&stream);
	check_rc_return_on_failure (handle,NULL,result,"SE_stream_create");

	result = SE_stream_insert_table (stream,table,number_of_columns,
								   (const CHAR **)base_columns);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_insert_table");

	result = SE_stream_set_write_mode (stream,buf_write_mode);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_set_write_mode");

	/*initialize the bindings*/
	result = SE_stream_bind_input_column (stream,1,str_val,&str_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");
	result = SE_stream_bind_input_column (stream,2,&int16_val,&int16_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");
	result = SE_stream_bind_input_column (stream,3,&int32_val,&int32_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");
	result = SE_stream_bind_input_column (stream,4,&float32_val,&float32_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");
	result = SE_stream_bind_input_column (stream,5,&float64_val,&float64_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");
	result = SE_stream_bind_input_column (stream,6,&date_val,&date_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");
	result = SE_stream_bind_input_column (stream,7,&blob_val,&blob_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");

	/* Perform the insert. */
	str_ind   = SE_IS_NOT_NULL_VALUE;
	int16_ind      = SE_IS_NOT_NULL_VALUE;
	int32_ind = SE_IS_NOT_NULL_VALUE;
	float32_ind    = SE_IS_NOT_NULL_VALUE;
	float64_ind   = SE_IS_NOT_NULL_VALUE;
	date_ind     = SE_IS_NOT_NULL_VALUE;
	blob_ind     = SE_IS_NOT_NULL_VALUE;

	memset (&date_val, 0, sizeof(struct tm));
	part_offsets[0]=1;
	for (row = 0;row < num_of_row ;row++) 
	{
		int16_val=(SHORT)NEXT_INT16_VALUE; 
		int32_val=NEXT_INT16_VALUE; 
		float32_val =(FLOAT)NEXT_INT16_VALUE*1; 
		float64_val =(LFLOAT)NEXT_INT16_VALUE*1.0 ;

		date_val.tm_mday = 1;
		date_val.tm_mon  = 1;
		date_val.tm_year = 91;

		sprintf (ansi_str, "Inserted row %d", NEXT_INT16_VALUE); 
		strcpy(str_val,ansi_str);

		//initialize  blob content
		blob_size =(row+1)*100;
		blob_val.blob_length = blob_size;
		blob_val.blob_buffer = (char*)malloc(blob_size);
		count=0;
		while( count < blob_size) {
			blob_val.blob_buffer[count] ='i';
			count++;
		}

		// Execute the insert
		result = SE_stream_execute (stream);
		if(result!=SE_SUCCESS){
			free(blob_val.blob_buffer);
			free(base_columns);
			check_rc_return_on_failure (NULL,stream,result,"SE_stream_execute");
		}
		free(blob_val.blob_buffer);
		NEXT_INT16_VALUE++;
	}
	if(buf_write_mode){
		result = SE_stream_flush_buffered_writes (stream);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_flush_buffered_writes");
	}

	//free resources
	result = SE_stream_free(stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_free");
	for(i=0;i<number_of_columns;i++)
		free(base_columns[i]);
	free(base_columns);

	return(SE_SUCCESS);
}


/***********************************************************************
*
*N  {S_update_row}  -  update a specified row in the table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     update a specified row in the table
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <handle>        ==  (SE_CONNECTION) The connection handle.
*     <table >        ==  (CHAR*) the tabke name
*     <update_mode>   ==  (S_DM_MODE) update mode: by rowid or by whereclause
*     <rowid>         ==  (LONG) the row to updated
*E
***********************************************************************/
static LONG S_update_row(SE_CONNECTION  handle,const CHAR *table,
							S_DM_MODE update_mode, LONG rowid)
{
	SE_STREAM       stream;
	LONG            result,i;
	SHORT           number_of_columns;
	CHAR            **base_columns;
	CHAR            str_val[32];
	char			ansi_str[32];
	SE_INT32        int32_val;
	SHORT           str_ind,int32_ind;
	CHAR			where_clause[128];

	number_of_columns = 2;
	base_columns = (CHAR **) malloc (number_of_columns * sizeof(CHAR *));
	for(i=0;i<number_of_columns;i++)
		base_columns[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN * sizeof(CHAR));

	/* Set up for update. */
	strcpy(	base_columns[0],"STR_COL");
	strcpy(	base_columns[1],"INT32_COL");

	result = SE_stream_create (handle,&stream);
	check_rc_return_on_failure (handle,NULL,result,"SE_stream_create");
	if(update_mode==S_DM_BY_ROWID){
		result = SE_stream_update_row(stream, (const CHAR *)table, (LONG*)&rowid,
								   number_of_columns,(const CHAR **)base_columns);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_update_row");
	}else{
		sprintf(ansi_str,"%s = %d","ROWID_COL",rowid);
		strcpy(where_clause,ansi_str);
		result = SE_stream_update_table(stream, (const CHAR *)table, number_of_columns,
								   (const CHAR **)base_columns,(const CHAR*)where_clause);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_update_table");
	}

	result = SE_stream_bind_input_column (stream,1,str_val,&str_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");

	result = SE_stream_bind_input_column (stream,2,&int32_val,&int32_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");


	/* Perform the insert. */
	str_ind   = SE_IS_NOT_NULL_VALUE;
	int32_ind = SE_IS_NOT_NULL_VALUE;

	int32_val=rowid*100; 
	sprintf (ansi_str, "updated row %d",rowid); 
	strcpy(str_val,ansi_str);

	//Execute the update
	result = SE_stream_execute (stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_execute");

	//free resources
	result = SE_stream_free(stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_free");
	for(i=0;i<number_of_columns;i++)
		free(base_columns[i]);
	free(base_columns);

	return(SE_SUCCESS);
}


/***********************************************************************
*
*N  {S_delete_rows}  -  delete specified rows from the table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Delete some rows from the table
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <handle>        ==  (SE_CONNECTION) The connection handle.
*     <table >        ==  (CHAR*) the layer name
*     <delete_mode>   ==  (S_DM_MODE) delete mode: by id list or by whereclause
*     <delete_count>  ==  (LONG) number of rows to be deleted
*     <delete_list>   ==  (LONG*) an array of rowids to be deleted
*E
***********************************************************************/
static LONG S_delete_rows(SE_CONNECTION  connection,
                          const CHAR     *table,
						  S_DM_MODE		 delete_mode,
                          LONG           delete_count,
                          const LONG     *delete_list)
{
	SE_STREAM       stream;
	LONG            result,i;
	CHAR			where[256];
	char			ansi_str[256];

	result = SE_stream_create (connection,&stream);
	check_rc_return_on_failure (connection,NULL,result,"SE_stream_create");
	if (delete_count >   0) {
		if(delete_mode==S_DM_BY_ROWID){
			if(delete_count==1){
				result = SE_stream_delete_row (stream,table,delete_list[0]);				
				if(result!= SE_SUCCESS && result!=SE_NO_ROWS_DELETED)
					check_rc_return_on_failure (NULL,stream,result,"SE_stream_delete_row");
			}else{
				result = SE_stream_delete_by_id_list (stream,table,
											  (LONG *)delete_list,delete_count);
				if(result!= SE_SUCCESS && result!=SE_NO_ROWS_DELETED)
					check_rc_return_on_failure (NULL,stream,result,"SE_stream_delete_by_id_list");
			}
		}else{
			sprintf(ansi_str,"%s IN (","ROWID_COL");
			for(i=0;i<delete_count;i++){
				if(i==0)
					sprintf(ansi_str,"%s%d",ansi_str,delete_list[i]);
				else
					sprintf(ansi_str,"%s,%d",ansi_str,delete_list[i]);
			}
			sprintf(ansi_str,"%s)",ansi_str);
			strcpy(where,ansi_str);
			result = SE_stream_delete_from_table (stream,table,where);
			if(result!= SE_SUCCESS && result!=SE_NO_ROWS_DELETED)
				check_rc_return_on_failure (NULL,stream,result,"SE_stream_delete_from_table");
		}
	}

	/* Done, clean up and commit. */
	result = SE_stream_free (stream);
	if (SE_SUCCESS != result) {
		(void) SE_connection_rollback_transaction (connection);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_free");
	}
	return (SE_SUCCESS);
}


/***********************************************************************
*
*N  {S_print_table_stats}  - check and print table statistics 
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     check and print table statistics 
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>    ==  (SE_CONNECTION) The connection handle.
*     <layname >      ==  (CHAR*) the layer name
*E
***********************************************************************/
static LONG S_print_table_stats(SE_CONNECTION  connection, const CHAR *table,const CHAR* col){

	LONG			  result,mask=SE_ALL_STATS,max_distinct=10,i;
	SE_SQL_CONSTRUCT  *sqlc;
	SE_STATS		  *stats;

	//prepare sql construct
	result = SE_sql_construct_alloc (1, &sqlc);
	check_rc_return_on_failure (connection,NULL,result,"SE_sql_construct_alloc");
	sqlc->num_tables = 1;
	strcpy (sqlc->tables[0], table);
	sqlc->where=NULL;

	// Calculate and display the statistics
	result=SE_table_calculate_stats(connection,col,SE_ALL_STATS,sqlc,max_distinct,&stats);
	check_rc_return_on_failure (connection,NULL,result,"SE_sql_construct_alloc");
	printf("\n\nDetailed statistics on column %s:\n",col);
	printf("%-15s = %-d\n","count",stats->count);
	printf("%-15s = %-5.2f\n","min",stats->min);
	printf("%-15s = %-5.2f\n","max",stats->max);
	printf("%-15s = %-5.2f\n","average",stats->average);
	printf("%-15s = ","distinct values");
	for(i=0;i<stats->distinct->num_distinct;i++)
	{
		switch (stats->distinct->type){
			case SE_STRING_TYPE:
				printf("%s[%d] ",stats->distinct->values[i].uval.str_val,stats->distinct->values[i].freq);
				break;
			case SE_INTEGER_TYPE:
				printf("%d[%d] ",stats->distinct->values[i].uval.int_val,stats->distinct->values[i].freq);
				break;
			case SE_DOUBLE_TYPE:
				printf("%f[%d] ",stats->distinct->values[i].uval.double_val,stats->distinct->values[i].freq);
				break;
			case SE_DATE_TYPE:
				printf("%s[%d] ",stats->distinct->values[i].uval.date_val,stats->distinct->values[i].freq);
				break;
		}
	}


	return (SE_SUCCESS);

}

/***********************************************************************
*
*N  {S_query_table} : query rows in the table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Query a table with a where clause
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>  ==  (SE_CONNECTION) The connection handle.
*     <table  >     ==  (CHAR*) the result layer name
*     <where  >     ==  (CHAR*) the where clause
*E
***********************************************************************/
static LONG S_query_table(SE_CONNECTION connection, const CHAR *table,const CHAR* where){
	LONG			result,num_of_column,rowid;
	CHAR			**columns;
	CHAR            str_col[32];
	SHORT           int16_col;
	SE_INT32        int32_col;
	FLOAT           float32_col;
	LFLOAT          float64_col;
	struct tm       date_col;
    SHORT           str_ind,int16_ind, float32_ind, int32_ind,
					float64_ind, date_ind,blob_ind, rowid_ind;
	SE_BLOB_INFO    blob_val;
	SE_QUERYINFO    qinfo;
	SE_STREAM		query_stream;
	LONG			fetched_row=0,i;
	CHAR			orderby[128];
	char			ansi_str[128];

	// Setup the table's column list
	num_of_column = 8;

	columns = (CHAR **) malloc (num_of_column * sizeof(CHAR *));
	for(i=0;i<num_of_column;i++)
		columns[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN * sizeof(CHAR));
	strcpy(	columns[0],"ROWID_COL");
	strcpy(	columns[1],"STR_COL");
	strcpy(	columns[2],"INT16_COL");
	strcpy(	columns[3],"INT32_COL");
	strcpy(	columns[4],"FLOAT32_COL");
	strcpy(	columns[5],"FLOAT64_COL");
	strcpy(	columns[6],"DATE_COL");
	strcpy(	columns[7],"BLOB_COL");

	//Setup the SE_QUERYINFO structure
	result=SE_stream_create(connection, &query_stream);
	check_rc_return_on_failure (connection,NULL,result,"SE_stream_create");

	result = SE_queryinfo_create(&qinfo);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_create");
	result = SE_queryinfo_set_tables (qinfo, 1, &table, NULL);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_tables");
	result = SE_queryinfo_set_columns (qinfo, num_of_column, (const CHAR **)columns);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_columns");
	result = SE_queryinfo_set_where_clause(qinfo,where);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_where_clause");
	strcpy(orderby,"order by INT16_COL");
	result = SE_queryinfo_set_by_clause(qinfo,orderby);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_by_clause");
	result = SE_stream_query_with_info (query_stream,qinfo);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_query_with_info");

	//Setup the variable binding
	blob_val.blob_length = 100000;
	blob_val.blob_buffer = (char*)malloc(100000);

	result = SE_stream_bind_output_column (query_stream,1,&rowid,&rowid_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	result = SE_stream_bind_output_column (query_stream,2,str_col,&str_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	result = SE_stream_bind_output_column (query_stream,3,&int16_col,&int16_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	result = SE_stream_bind_output_column (query_stream,4,&int32_col,&int32_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	result = SE_stream_bind_output_column (query_stream,5,&float32_col,&float32_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	result = SE_stream_bind_output_column (query_stream,6,&float64_col,&float64_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	result = SE_stream_bind_output_column (query_stream,7,&date_col,&date_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	result = SE_stream_bind_output_column (query_stream,8,&blob_val,&blob_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");

	//Execute the query
    result = SE_stream_execute (query_stream);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_execute");

	//Fetch and display the contents of the table
	while(result==SE_SUCCESS){
	   result = SE_stream_fetch (query_stream);
	   if(result!=SE_FINISHED){
		    check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_fetch");
			printf("\n----------------------------------------------------------------------\n");			
		    printf("%-10s = %-10d","ROW ID",rowid);
		    printf("%-10s = %-20s\n","STR_COL",str_col);
		    printf("%-10s = %-10d","INT16_COL",int16_col);
		    printf("%-10s = %-20d\n","INIT32_COL",int32_col);
		    printf("%-10s = %-10.1f","FLT32_COL",float32_col);
		    printf("%-10s = %-10.1f\n","FLT64_COL",float64_col);
			sprintf(ansi_str,"%c%c...",blob_val.blob_buffer[0],blob_val.blob_buffer[1]);
			printf("%-10s = %-10s","BLOB_COL",ansi_str);
	   }
	}	

	//free resource
	free(blob_val.blob_buffer);
	SE_queryinfo_free(qinfo);
	SE_stream_free(query_stream);
	for(i=0;i<num_of_column;i++)
		free(columns[i]);
	free(columns);
	return (SE_SUCCESS);
}


/***********************************************************************
*
*N  {check_rc_}  -  check the returned code
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Check returned sde error code and print the error messages.
***********************************************************************/
void check_rc_(SE_CONNECTION Connection, SE_STREAM Stream, LONG rc, 
				 char *comment, LONG line_no, char* file_name) 
{

	SE_ERROR	error;
	CHAR		error_string [SE_MAX_MESSAGE_LENGTH];
	LONG		temp_rc=SE_FAILURE;


	if ((rc != SE_SUCCESS) && (rc != SE_FINISHED)) 
	{
		error_string[0] = '\0';
		SE_error_get_string (rc, error_string);

	    printf ("%s encountered a %d error:\"%s\" \nat line %ld in file %s\n",comment, rc,error_string, line_no, file_name);

		/*Print extended error info, if any */
		if ((SE_DB_IO_ERROR == rc) | (SE_INVALID_WHERE == rc) | (SE_SSA_FUNCTION_ERROR == rc)) 
		{
			if (NULL != Stream) 
			{
				/* Assume this is a stream error */
				temp_rc = SE_stream_get_ext_error (Stream, &error);
			}else if (NULL != Connection) {
				/*Assume this is a connection error */
				temp_rc = SE_connection_get_ext_error (Connection, &error);
			}
			if (SE_SUCCESS == temp_rc)
			{
					printf("Extended error code: %d, extended error string:\n%s\n",
					error.ext_error, error.err_msg1);
			}
		} /*End SE_DB_IO_ERROR */

	} 
}