/*******************************************************************************
*                                                                       
*N  {schobj_view_sample.c}  -- demonstrates usages of view in ArcSDE
*   
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*P  Purpose:
*    This sample C program demonstrates how to create and query views programmingly
*	 using ArcSDE C API
*
*    Scenarios:
*	 (1) Create regular view without spatial column based on one table;
*	 (2) Create spatial view with a spatial column based on one table;
*	 (3) Create regular view without spatial column based on two tables(with join);
*	 (4) Create spatial view with a spatial column based on two tables(with join);
*
*E                                                                 
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* How to compile:: 
*	 Please refer section "Build C Samples" in the SDE sample
*
*
*E
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* Usage:
*     schobj_view_sample {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"


/* 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 rc; }}


/*****************************/
/* Local Function Prototypes */
/****************************/
void check_rc_(SE_CONNECTION Connection, SE_STREAM Stream, LONG rc, 
				 char *comment, LONG line_no, char* file_name) ;
/*Populate the main table*/
static LONG S_prepare_main_table (SE_CONNECTION,CHAR *table,CHAR *keyword,LONG num_of_row);
/*Populate the lookup table */
static LONG S_prepare_lookup_table (SE_CONNECTION connection, CHAR *table, 
                             CHAR *keyword,LONG num_of_row);
/*create views on one table*/
static LONG S_create_view (SE_CONNECTION,CHAR *table,CHAR *viewname,
						   CHAR* where,BOOL withspatialcol); //without join
/*create views on two tables*/
static LONG S_create_view2 (SE_CONNECTION connection,CHAR *table,CHAR* reftable,
							CHAR *view,CHAR* where,BOOL withspatialcol);//with join
/*query functions the table or view*/
static LONG S_query_table(SE_CONNECTION  connection, const CHAR *table,const CHAR* where);

#define MAIN_TABLE_NAME		"SAMPLE_MAINTBLE"
#define REF_TABLE_NAME		"SAMPLE_REFTBL"
#define VIEW_NAME			"SAMPLE_TMPVW"

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],view[SE_MAX_TABLE_LEN+1],
					reftable[SE_MAX_TABLE_LEN+1];
	CHAR			where[128];

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

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


	strcpy(table,MAIN_TABLE_NAME);
    strcpy(reftable,REF_TABLE_NAME);
    strcpy(view,VIEW_NAME);

	printf("\n***********************************************************************\n");
	printf("* Prepare testing: prepare the main table and a lookup table.....     *\n");
	printf("***********************************************************************\n");

	/*
	 *Prepare the main table and insert 10 rows into it
	 */
	result=S_prepare_main_table(conn,table,keyword,10);
	if(result!=SE_SUCCESS){
		return result;
	}
	printf("\nQuery the main table ......\n");
	strcpy(where,"");
    result=S_query_table(conn, table,where);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,table);
		return SE_FAILURE;
	}

	/*Prepare the main lookup and insert 10 rows into it*/
	result=S_prepare_lookup_table(conn,reftable,keyword,10);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,table);
		return result;
	}

	/* Sample 1: Create and query  view without spatial column on the main table */
	//create view on the table with where='rowidcol>5';
	//the FLT64COL in the view will be defined as (maintable.FLT64COL+10);
	//Spatail column will not be included in the view;
	printf("\n*****************************************************************\n");
	printf("* Create and query view without spatial column based on 1 table *\n");
	printf("*****************************************************************\n");
	strcpy(where,"ROWIDCOL>5");
	printf("Created regular view %s with WHERE=ROWIDCOL>5...\n",view);
	result =S_create_view(conn,table,view,where,FALSE);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}
	//query view
	printf("\nQuery view %s ...\n",view);
	strcpy(where,"");
    result=S_query_table(conn, view,where);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,view);
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}

	/* Sample 2: Create and query spatial on the main table*/
	//create view on the table with where='rowidcol>5';
	//the FLT64COL in the view is defined as (maintable.FLT64COL+10);
	//Spatail column will be included in the view;
	printf("\n******************************************************************\n");
	printf("* Create and query view with spatial column based on on 1 table  *\n");
	printf("******************************************************************\n");
	strcpy(where,"ROWIDCOL>5");
	printf("Created spatial view %s with WHERE=ROWIDCOL>5...\n",view);
	result =S_create_view(conn,table,view,where,TRUE);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}

	//query view
	printf("\nQuery view %s ...\n",view);
	strcpy(where,"");
    result=S_query_table(conn, view,where);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,view);
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}

	/* Sample 3: Create regualr view with join*/
	//create view with where='rowidcol>5 AND maintable.ROWIDCOL=lookuptable.REFID';
	//the new strcol in the view is from the reference table;
	//the new FLT64COL in the view will be defined as (maintable.FLT64COL+10);
	//The spatial column will not be included in the view.
	printf("\n*******************************************************************\n");
	printf("* Create and query view without spatial column based on 2 tables  *\n");
	printf("*******************************************************************\n");
	sprintf(where,"%s.ROWIDCOL>5 AND %s.ROWIDCOL=%s.REFID",
		table,table,reftable);
	printf("Created regular view %s with join...\n",view);
	result =S_create_view2(conn,table,reftable,view,where,FALSE);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}
	//query view
	printf("\nQuery view %s ...\n",view);
	strcpy(where,"");
    result=S_query_table(conn, view,where);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,view);
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}


	/*Sample 4: Create spatail view with join*/
	//create view with where='rowidcol>5 AND maintable.ROWIDCOL=lookuptable.REFID';
	//the new strcol in the view is from the reference table
	//the new FLT64COL in the view is defined as (maintable.FLT64COL+10);
	//The spatial column will be included in the view.
	printf("\n*******************************************************************\n");
	printf("* Create and query view with spatial column based on on 2 tables  *\n");
	printf("*******************************************************************\n");
	sprintf(where,"%s.ROWIDCOL>5 AND %s.ROWIDCOL=%s.REFID",
		table,table,reftable);
	printf("Created spatial view %s with join...\n",view);
	result =S_create_view2(conn,table,reftable,view,where,TRUE);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}
	//query view
	printf("\nQuery view %s ...\n",view);
	strcpy(where,"");
    result=S_query_table(conn, view,where);
	if(result!=SE_SUCCESS){
		SE_table_delete(conn,view);
		SE_table_delete(conn,table);
		SE_table_delete(conn,reftable);
		return result;
	}

	//Clean up: delte view and table
	printf("\nClean up.....\n");
	printf("detet view...");
	result=SE_table_delete(conn,view);
	check_rc_return_on_failure( conn, NULL, result, "SE_table_delete" );
	printf("Done\n");
	printf("detet reference table...");
	result=SE_table_delete(conn,reftable);
	check_rc_return_on_failure( conn, NULL, result, "SE_table_delete" );
	printf("Done\n");
	printf("detet main table...");
	result=SE_table_delete(conn,table);
	check_rc_return_on_failure( conn, NULL, result, "SE_table_delete" );
	printf("Done\n");

	SE_connection_free(conn);
	return SE_SUCCESS;
}

/***********************************************************************
*
*N  {S_prepare_main_table}  -  prepare the main table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Create the main table and populated it with n rows
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>  ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*     <keyword >    ==  (CHAR*) the keyword for table creation
*     <num_of_row > ==  (LONG) number of rows to be populated
*E
***********************************************************************/
static LONG S_prepare_main_table (SE_CONNECTION connection, CHAR *table,CHAR *keyword,LONG num_of_row)
{
	SE_LAYERINFO        layer;
	SE_COLUMN_DEF       column_definitions[8];
	CHAR				**columns_names;
	SE_COORDREF         coordref;
	LONG                result,row,i;
	SE_REGINFO          registration;
	CHAR				reg_desc[128];
	SE_SHAPE			shape;
	SE_ENVELOPE			rectangle;
	CHAR				str_val[128];
	LFLOAT				float64_val;
	SHORT				str_ind, float64_ind,shape_ind;
	SE_STREAM			stream;

	printf("Creating main table/layer......\n");

	// setup the column definitions
	columns_names = (CHAR **) malloc (3 * sizeof(CHAR *));
	for(i=0;i<3;i++)
		columns_names[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN*sizeof(CHAR));
	strcpy(	columns_names[0],"STRCOL");
	strcpy(	columns_names[1],"FLT64COL");
	strcpy(	columns_names[2],"SHAPE");
	strcpy(column_definitions[0].column_name,columns_names[0]);
	column_definitions[0].sde_type = SE_STRING_TYPE;
	column_definitions[0].size = 128;
	column_definitions[0].decimal_digits = -1;
	column_definitions[0].nulls_allowed = FALSE;
	strcpy (column_definitions[1].column_name,columns_names[1]);
	column_definitions[1].sde_type = SE_FLOAT64_TYPE;
	column_definitions[1].size = 0;
	column_definitions[1].decimal_digits = 0;
	column_definitions[1].nulls_allowed = TRUE;

	/* Create the base table. Delete the table first if it already existed */
	result = SE_table_delete(connection,table);
	if(result!=SE_TABLE_NOEXIST)
		check_rc_return_on_failure (connection,NULL,result,"SE_table_delete");

	result = SE_table_create (connection,table,2,
							column_definitions,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_create");

	/* Alter the registration of the table to have 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,"main table.");
	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,"ROWIDCOL",
							   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);

	/* Add the spatial column to the table that will containing the */
	/* polygon geometries */
	result = SE_coordref_create (&coordref);
	check_rc_return_on_failure (connection,NULL,result,"SE_coordref_create");
	result = SE_coordref_set_xy (coordref,0.0,0.0,10.0);
	check_rc_return_on_failure (connection,NULL,result,"SE_coordref_set_xy");
	result = SE_layerinfo_create (coordref,&layer);
	check_rc_return_on_failure (connection,NULL,result,"SE_layerinfo_create");
	result = SE_layerinfo_set_grid_sizes (layer,1000.0,0.0,0.0);
	check_rc_return_on_failure (connection,NULL,result,"SE_layerinfo_set_grid_sizes");
	result = SE_layerinfo_set_shape_types (layer,SE_AREA_TYPE_MASK|SE_NIL_TYPE_MASK);
	check_rc_return_on_failure (connection,NULL,result,"SE_layerinfo_set_shape_types");
	result = SE_layerinfo_set_spatial_column (layer,table,"SHAPE");
	check_rc_return_on_failure (connection,NULL,result,"SE_layerinfo_set_spatial_column");
	result = SE_layerinfo_set_creation_keyword (layer,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_layerinfo_set_creation_keyword");
	result = SE_layer_create (connection,layer,0,0);
	check_rc_return_on_failure (connection,NULL,result,"SE_layer_create");

	printf("\tMain table/layer created.\n");

	/*
	* Now populate the main table
	*/
	printf("\nPopulate the main table/layer...\n");

	/* Set up for insert. */
	result = SE_shape_create (coordref,&shape);
	check_rc_return_on_failure (connection,NULL,result,"SE_shape_create");

	result = SE_connection_start_transaction (connection);
	check_rc_return_on_failure (connection,NULL,result,"SE_connection_start_transaction");

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

	result = SE_stream_insert_table (stream,table,3,(const CHAR **)columns_names);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_insert_table");

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

	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,&float64_val,&float64_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");

	result = SE_stream_bind_input_column (stream,3,shape,&shape_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");

	/* Perform the insert. */
	str_ind   = SE_IS_NOT_NULL_VALUE;
	float64_ind   = SE_IS_NOT_NULL_VALUE;
	shape_ind   = SE_IS_NOT_NULL_VALUE;


	for (row = 0;row < num_of_row ;row++) 
	{
		float64_val =(LFLOAT)row*1.0+1 ;
		sprintf (str_val, "str %d in the main table", row+1); 

		rectangle.minx = 1000.0 + row * 50.0;
		rectangle.miny = 1000.0;
		rectangle.maxx = 1100.0 + row * 50.0;
		rectangle.maxy = 1100.0;
		result = SE_shape_generate_rectangle (&rectangle,shape);
		check_rc_return_on_failure (connection,NULL,result,"SE_shape_generate_rectangle");

		result = SE_stream_execute (stream);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_execute");

	}

	result = SE_stream_flush_buffered_writes (stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_flush_buffered_writes");

	printf ("\t%d rows were written to  main table\n",row);

	result = SE_stream_free(stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_free");

	result = SE_connection_commit_transaction (connection);
	check_rc_return_on_failure (connection,NULL,result,"SE_connection_commit_transaction");

	//Free resources
	SE_shape_free(shape);
	SE_layerinfo_free(layer);
	SE_coordref_free (coordref);

	for(i=0;i<3;i++)
		free(columns_names[i]);
	free(columns_names);
	
	return(SE_SUCCESS);
}           


/***********************************************************************
*
*N  {S_prepare_lookup_table}  -  prepare the lookup table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Create the lookup table and populated it with n rows
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>  ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*     <keyword >    ==  (CHAR*) the keyword for table creation
*     <num_of_row > ==  (LONG) number of rows to be populated
*E
***********************************************************************/
static LONG S_prepare_lookup_table (SE_CONNECTION connection, CHAR *table, 
									CHAR *keyword,LONG num_of_row)
{
	SHORT               number_of_columns;
	SE_COLUMN_DEF       column_definitions[8];
	LONG                result,row;
	SE_REGINFO          registration;
	CHAR				ROWID_COL[SE_MAX_COLUMN_LEN];
	CHAR				reg_desc[128];
	SE_STREAM			stream;
	CHAR				*column,refvalue[128];
	SHORT				value_ind;

	printf("Creating lookup table......\n");

	// setup the column definitions
	strcpy(ROWID_COL,"REFID");
	number_of_columns = 1;
	strcpy(column_definitions[0].column_name,"REFVALUE");
	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;

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

	/* Alter the registration of the table to have 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,"lookup table");
	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);

	printf("Populate lookup table......\n");
	column=(CHAR *) malloc (SE_MAX_COLUMN_LEN*sizeof(CHAR));
	/* Set up for insert. */
	strcpy(	column,"REFVALUE");

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

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

	result = SE_stream_bind_input_column (stream,1,refvalue,&value_ind);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_bind_input_column");

	/* Perform the insert. */
	value_ind   = SE_IS_NOT_NULL_VALUE;
	for (row = 0;row < num_of_row ;row++) 
	{
		sprintf (refvalue, "str %d in the lookup table", row+1); 
		result = SE_stream_execute (stream);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_execute");
	}
	printf ("\t%d rows were written to  lookup table\n",row);
	result = SE_stream_free(stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_free");

	free(column);
	return(SE_SUCCESS);

}          

/***********************************************************************
*
*N  {S_query_table}  -  prepare the table or view 
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Query a table or view with provided where clause
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>  ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*     <keyword >    ==  (CHAR*) the keyword for table creation
*     <where >      ==  (const CHAR*) the where clause to be used
*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[128];
	LFLOAT          float64_col;
    SHORT           str_ind,float64_ind, shape_ind,rowid_ind;
	SE_LAYERINFO    layer;
	SE_COORDREF     coordref;
	SE_SHAPE        shape;
	SE_ENVELOPE		shp_extent;
	SE_QUERYINFO    qinfo;
	SE_STREAM		query_stream;
	LONG			feteched_row=0,i;
	CHAR			orderby[128];
	BOOL			found_mismatch=FALSE,haslayer=FALSE;
	SE_REGINFO		reg;

	result=SE_reginfo_create (&reg);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_create");
	result=SE_registration_get_info(connection,table,reg);
	check_rc_return_on_failure (connection,NULL,result,"SE_registration_get_info");
	haslayer=SE_reginfo_has_layer(reg);
	SE_reginfo_free (reg);

	// setup the column definitions
	num_of_column = 3;
	if(haslayer)
		num_of_column = 4;
	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],"ROWIDCOL");
	strcpy(	columns[1],"STRCOL");
	strcpy(	columns[2],"FLT64COL");

	if(haslayer){//if the table /view has a spatial column
		strcpy(	columns[3],"SHAPE");
		result = SE_layerinfo_create (NULL,&layer);
		check_rc_return_on_failure (connection,NULL,result,"SE_layerinfo_create");
		result = SE_coordref_create (&coordref);
		check_rc_return_on_failure (connection,NULL,result,"SE_coordref_create");
		result = SE_layer_get_info (connection,table,columns[3],layer);
		check_rc_return_on_failure (connection,NULL,result,"SE_layer_get_info");
		result = SE_layerinfo_get_coordref (layer,coordref);
		check_rc_return_on_failure (connection,NULL,result,"SE_layerinfo_get_coordref");
		result = SE_shape_create (coordref,&shape);
		check_rc_return_on_failure (connection,NULL,result,"SE_shape_create");
	}
	//prepare the query
	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 ROWIDCOL");
	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");

	//binding columns values to local variables
	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,&float64_col,&float64_ind);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	if(haslayer){
		result = SE_stream_bind_output_column (query_stream,4,shape,&shape_ind);
		check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_bind_output_column");
	}

    result = SE_stream_execute (query_stream);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_execute");


	if(haslayer){
		printf("%-10s %-30s %-10s %-8s %-8s %-8s %-8s\n","ROWID_COL","STRCOL","FLT64COL","SHPMINX","SHPMINY","SHPMAXX","SHPMAXY");
		printf("------------------------------------------------------------------------------\n");
	}else{
		printf("%-10s %-30s %-10s\n","ROWID_COL","STRCOL","FLT64COL");
		printf("-------------------------------------------------------------------------\n");
	}

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

			if(haslayer){
				result=SE_shape_get_extent (shape,0, &shp_extent);
				printf("%-10d %-30s %-10.1f %-8.1f %-8.1f %-8.1f %-8.1f\n",rowid,str_col,float64_col,
					shp_extent.minx,shp_extent.miny,shp_extent.maxx,shp_extent.maxy);
			}else
				printf("%-10d %-30s %-10.1f\n",rowid,str_col,float64_col);
		   feteched_row++;
	   }
	}	
	if(haslayer)
		printf("------------------------------------------------------------------\n");
	else
		printf("---------------------------------------------------------\n");

	result = SE_stream_close (query_stream,TRUE);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_close");

	//clean up and free resources
	if(haslayer){
		SE_coordref_free(coordref);
		SE_shape_free(shape);
		SE_layerinfo_free(layer);
	}
	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  {S_create_view}  -  prepare a view on the main table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Create a view on the main table. The view will have same column 
*     names as the main able
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>     ==  (SE_CONNECTION) The connection handle.
*     <table >         ==  (CHAR*) the base table name
*     <view >          ==  (CHAR*) the view name
 *    <where >         ==   the where clause used to create view
*     <withspatialcol >==  (BOOL) is it a spatial view or not
*E
***********************************************************************/
static LONG S_create_view (SE_CONNECTION connection,CHAR *table,CHAR *view,CHAR* where,BOOL withspatialcol)
{
	LONG			result;
	SHORT			num_of_column,i;
	CHAR			**tablecols, **viewcols;
	SE_SQL_CONSTRUCT *sqlc;

	// setup the columns for view creating
	num_of_column = 3;
	if(withspatialcol)
		num_of_column = 4;
	tablecols = (CHAR **) malloc (num_of_column * sizeof(CHAR *));
	viewcols = (CHAR **) malloc (num_of_column * sizeof(CHAR *));
	for(i=0;i<num_of_column;i++){
		tablecols[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN*sizeof(CHAR));
		viewcols[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN*sizeof(CHAR));
	}

	//define the colomn in the table
	strcpy(	tablecols[0],"ROWIDCOL");
	strcpy(	tablecols[1],"STRCOL");
	strcpy(	tablecols[2],"(FLT64COL+10) NEWFLT");

	//define the colomn in the target view
	strcpy(	viewcols[0],"ROWIDCOL");
	strcpy(	viewcols[1],"STRCOL");
	strcpy(	viewcols[2],"FLT64COL");

	if(withspatialcol){
		strcpy(	tablecols[3],"SHAPE");
		strcpy(	viewcols[3],"SHAPE");
	}
	
	//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=where; 

	//delete the view if it already existed
	result = SE_table_delete(connection,view);
	if(result!=SE_TABLE_NOEXIST)
		check_rc_return_on_failure (connection,NULL,result,"SE_table_delete");

	//create view
	result=SE_table_create_view(connection,view,num_of_column,
		num_of_column,(const CHAR **)viewcols,(const CHAR **)tablecols,sqlc);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_create_view");

	SE_sql_construct_free (sqlc);
	for(i=0;i<num_of_column;i++){
		free(tablecols[i]);
		free(viewcols[i]);
	}
	free(viewcols);
	free(tablecols);
	return SE_SUCCESS;
}


/***********************************************************************
*
*N  {S_create_view2}  -  Create a view on the two tables with a join
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Create a view on the two tables with a join.The view will have same  
*     column names as the main able
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>     ==  (SE_CONNECTION) The connection handle.
*     <table >         ==  (CHAR*) the main table name
*     <reftable >      ==  (CHAR*) the lookup table name
*     <view >          ==  (CHAR*) the view name
 *    <where >         ==   the where clause used to create view
*     <withspatialcol >==  (BOOL) is it a spatial view or not
*E
***********************************************************************/
static LONG S_create_view2 (SE_CONNECTION connection,CHAR *table,CHAR* reftable,
							CHAR *view,CHAR* where,BOOL withspatialcol)
{
	LONG			result;
	SHORT			num_of_column,i;
	CHAR			**tablecols, **viewcols,tmpstr[SE_MAX_COLUMN_LEN+1];
	SE_SQL_CONSTRUCT *sqlc;

	// setup the columns for view creating
	num_of_column = 3;
	if(withspatialcol)
		num_of_column = 4;

	tablecols = (CHAR **) malloc (num_of_column * sizeof(CHAR *));
	viewcols = (CHAR **) malloc (num_of_column * sizeof(CHAR *));
	for(i=0;i<num_of_column;i++){
		tablecols[i]=(CHAR *) malloc (SE_QUALIFIED_COLUMN_LEN*sizeof(CHAR));
		viewcols[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN*sizeof(CHAR));
	}

	//define the colomns in the base tables
	strcpy(	tablecols[0],"ROWIDCOL");
	strcpy(tablecols[1],reftable);
	strcpy(tmpstr,".REFVALUE NEWSTRCOL");
	strcat(tablecols[1],tmpstr);
	strcpy(	tablecols[2],"(FLT64COL+10) NEWFLT");

	//define the colomns in the target view
	strcpy(	viewcols[0],"ROWIDCOL");
	strcpy(	viewcols[1],"STRCOL");
	strcpy(	viewcols[2],"FLT64COL");

	if(withspatialcol){
		strcpy(	tablecols[3],"SHAPE");
		strcpy(	viewcols[3],"SHAPE");
	}
	//prepare sql construct
	result = SE_sql_construct_alloc (2, &sqlc);
	check_rc_return_on_failure (connection,NULL,result,"SE_sql_construct_alloc");
	sqlc->num_tables = 2;
	strcpy (sqlc->tables[0], table);
	strcpy (sqlc->tables[1], reftable);
	sqlc->where=where; 

	//delete the view if it already existed
	result = SE_table_delete(connection,view);
	if(result!=SE_TABLE_NOEXIST)
		check_rc_return_on_failure (connection,NULL,result,"SE_table_delete");
	//create view
	result=SE_table_create_view(connection,view,num_of_column,
		num_of_column,(const CHAR **)viewcols,(const CHAR **)tablecols,sqlc);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_create_view");
	SE_sql_construct_free (sqlc);
	for(i=0;i<num_of_column;i++){
		free(tablecols[i]);
		free(viewcols[i]);
	}
	free(viewcols);
	free(tablecols);
	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 */

	} 
}