EXEC SQL include "udtexpo.h";

main(int argc, char *argv[])
{
   Initialize();

   /* Connect to the database */

   if (argc == 2 )
     Connect(argv[1]);
   else
   {
     printf("Usage:  udtexpo dbname@servername\n");
     exit(0);
   }

   /* Creates and inserts values into a row type table */

   create_RowType_table();

   /* Creates and inserts values into qualified type table */

   create_QualifiedType_table();

   /* Creates and inserts values into a table containing collection
      types - list, set, and multiset. */

   create_CollectionType_table();

   /* Creates and inserts values into a table containing distinct 
      types - dollar and yen */

   create_DistinctType_table();

   /* Creates a table with an opaque type and inserts data into the table */

   create_OpaqueType_table();

   /* Queries for row type values. This method shows how to use row type
      values in esql/c  */

   query_RowType_values();

   /* Queries for distinct type values. This method shows how to use distinct
      type values like dollar and yen in esql/c  */

   query_DistinctType_values();

   /* shows the usage of collection types - list, set, and multiset*/

   query_CollectionType_values();

   /* Queries for opaque types. This method shows how to use an opaque 
      type value */

   query_OpaqueType_values();

   /* Disconnect the database */

   EXEC SQL disconnect current;
   printf("Disconnected.\n");
}

void Initialize()
{
   /* Initialization code goes in here if necessary */
}

/*******************************************************************
	Method Name	: Connect
	Parameters	: dbName - database name to connect to
	Return type	: None
	Description	: This method is used to connect to a given
			  database
********************************************************************/

void Connect(char *dbName)
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   char *db;
   int errCode;
   EXEC SQL END DECLARE SECTION;
   db = dbName;
   printf("ESQL program for accessing UDTs on the client side\n");
   sprintf(queryText, "%s \'%s\'", "connect to", dbName);
   EXEC SQL connect to :db; 
   errCode = SQLCODE;
   printf("Connected to udtexpo\n");
}

/*******************************************************************
	Method Name	: exec_query 
	Parameters	: Text - query string to be executed 
	Return type	: int - contains the error code 
	Description	: This method is used to execute a query 
			  that is passed in as a parameter
********************************************************************/

int exec_query( char * Text )
{
   EXEC SQL BEGIN DECLARE SECTION;
   char *  queryText;
   EXEC SQL END DECLARE SECTION;
   queryText = Text;
   EXEC SQL  EXECUTE IMMEDIATE :queryText;
   return SQLCODE;
}                                

/*******************************************************************
	Method Name	: create_RowType_table 
	Parameters	: None 
	Return type	: None 
	Description	: Creates a table with row types and 
			  inserts values into it.
********************************************************************/

void create_RowType_table()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;
   
   /* construct the query string for creating table */

   sprintf(queryText, "create table employee ( emp_id integer, emp_name name_t, emp_address address_t)");
   errCode = exec_query(queryText);
   if (errCode == 0)
      printf("Created row type table\n");

   /* insert the values into the table */

   insert_RowType_values();
}

/*******************************************************************
	Method Name	: create_QualifiedType_table 
	Parameters	: None 
	Return type	: None 
	Description	: Creates a table with qualified types. Calls
			  insert_Qualified_values() function to insert 
			  values into it.
*********************************************************************/

void create_QualifiedType_table()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;
   /* This function shows how to create a table with qualified types*/
   sprintf(queryText, "create table teams (name varchar(20), city varchar(20), state char(2), zip char(5) )");
   errCode = exec_query(queryText);
   if (errCode == 0)
       printf("Created qualified type table\n");
   insert_QualifiedType_values();
}

/*******************************************************************
	Method Name	: create_CollectionType_table 
	Parameters	: None 
	Return type	: None 
	Description	: Creates a table with set, multiset, and 
			  list as columns. Calls insert_Collection_
			  Type_values() function to load values.
********************************************************************/

void create_CollectionType_table()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;
   sprintf(queryText, "create table collections (names list(name_t not null), age set(integer not null), isStudent multiset(boolean not null) )");
   errCode = exec_query(queryText);
   if (errCode == 0)
       printf("Created Collections table\n");
   insert_CollectionType_values();
}

/*******************************************************************
	Method Name	: create_DistinctType_table 
	Parameters	: None 
	Return type	: None 
	Description	: Creates a table with distinct types and 
			  inserts values into it. Uses the distinct
			  types dollar and yen created using DBDK.
********************************************************************/

void create_DistinctType_table()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;

   /* Creating a table containing distinct types. */

   sprintf(queryText, "create table items (item_name varchar(20), us_price dollar, japan_price yen)");
   errCode = exec_query(queryText);
   if (errCode == 0)
       printf("Created items table\n");
   insert_DistinctType_values();
}

/*******************************************************************
	Method Name	: create_OpaqueType_table 
	Parameters	: None 
	Return type	: None 
	Description	: Creates a table with opaque types and 
			  inserts values into it. The opaque type
			  used is a complex number.
********************************************************************/

void create_OpaqueType_table()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;
   sprintf(queryText, "create table complex ( x integer, number ComplexNumber)");
   errCode = exec_query(queryText);
   if (errCode == 0)
      printf("Created Opaque Type table\n");
   insert_OpaqueType_values();
}

/*******************************************************************
	Method Name	: insert_RowType_values ()
	Parameters	: None 
	Return type	: None 
	Description	: This function is called by create_RowType_values().
			  The function inserts values into a table containing 
			  row types.
********************************************************************/

void insert_RowType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;


   /* Using a Row Type (zip_t)  in another Row Type (address_t) */

   sprintf(queryText, "insert into employee values( 1, row('Smith', 'John', 'X')::name_t, row('100 State St.', 'Carmel', 'CA', row('95401')::zip_t)::address_t)");
   exec_query(queryText);
   sprintf(queryText, "insert into employee values( 2, row('Clark', 'Eric', 'M')::name_t, row('2010 Flagstone Dr.', 'Huntsville', 'AL', row('22045')::zip_t)::address_t)");
   exec_query(queryText);
   sprintf(queryText, "insert into employee values( 3, row('Jones', 'Jimmy', 'M')::name_t, row('432 Lakeside Dr.', 'Mt. View', 'CA', row('94087')::zip_t)::address_t)");
   errCode = exec_query(queryText);
   if (errCode == 0)
       printf("Inserted Values into row type table\n");
}

/*******************************************************************
	Method Name	: insert_QualifiedType_values() 
	Parameters	: None 
	Return type	: None 
	Description	: Inserts values into the table containing
			  qualified types. 
********************************************************************/

void insert_QualifiedType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;

   /* Using Qualified Types */

   sprintf(queryText, "insert into teams values( 'Jets', 'New York', 'NY', '11021')");
   exec_query(queryText);
   sprintf(queryText, "insert into teams values( 'Niners', 'San Francisco', 'CA', '95040')");
   exec_query(queryText);
   sprintf(queryText, "insert into teams values( 'Packers', 'Green Bay', 'WI', '53120')");
   exec_query(queryText);
   sprintf(queryText, "insert into teams values( 'Raiders', 'Oakland', 'CA', '94056')");
   errCode = exec_query(queryText);
   if (errCode == 0)
      printf("Inserted Values into teams table\n");
}

/*******************************************************************
	Method Name	: insert_DistinctType_values() 
	Parameters	: None 
	Return type	: None 
	Description	: Called by create_DistinctType_table. The 
			  function inserts values into a table 
			  containing distinct types. 
********************************************************************/

void insert_DistinctType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;

   /* Using Distinct Types */

   sprintf(queryText, "insert into items values( 'Pencil', 1.00::money::dollar, 38.00::money::yen)");
   exec_query(queryText);
   sprintf(queryText, "insert into items values( 'Note Book', 1.60::money::dollar, 94.00::money::yen)");
   exec_query(queryText);
   sprintf(queryText, "insert into items values( 'Bag',19.60::money::dollar, 2004.00::money::yen)");
   exec_query(queryText);
   sprintf(queryText, "insert into items values( 'Eraser', 0.60::money::dollar, 24.00::money::yen)");
   errCode = exec_query(queryText);
   if (errCode == 0)
      printf("Inserted Values into items table\n");
}

/*******************************************************************
	Method Name	: insert_CollectionType_values() 
	Parameters	: None 
	Return type	: None 
	Description	: This function inserts values into a table  
			  containing collection types. 
********************************************************************/

void insert_CollectionType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;

   /* Using Collection Types */

   sprintf(queryText, "insert into collections values( 'list{row(\"Smith\", \"John\", \"X\"), row(\"Jordan\", \"Steve\", \"M\"), row(\"Moore\", \"Eric\", \"T\")}', 'set{22, 35, 17}', 'multiset{\"t\", \"f\", \"t\"}')");
/*
   sprintf(queryText, "insert into collections values( 'list{row(\"Smith\", \"John\", \"X\")}', 'set{22}', 'multiset{\"t\"}')");
*/
   errCode = exec_query(queryText);
   if (errCode == 0)
      printf("Inserted Values into collections table\n");
}

/*******************************************************************
	Method Name	: insert_OpaqueType_values 
	Parameters	: None 
	Return type	: None 
	Description	: This function is called by create_Opaque 
			  Type_table to insert values into the table
			  containing opaque types.
********************************************************************/

void insert_OpaqueType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char queryText[200];
   int errCode;
   EXEC SQL END DECLARE SECTION;

   /* Using Opaque Types */

   sprintf(queryText, "insert into complex values(1, '1 2' )");
   errCode = exec_query(queryText);
   sprintf(queryText, "insert into complex values(2, '5 2' )");
   errCode = exec_query(queryText);
   sprintf(queryText, "insert into complex values(3, '3 5' )");
   errCode = exec_query(queryText);
   sprintf(queryText, "insert into complex values(4, '8 7' )");
   errCode = exec_query(queryText);
   if (errCode == 0)
      printf("Inserted Values into complex table\n");
}


/******************************************************************
	Method Name	: query_RowType_values 
	Parameters	: None 
	Return type	: None 
	Description	: Queries for row type values and shows how  
			  to display those values to standard output 
*******************************************************************/

void query_RowType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char name[20];
   char zip[10];
   int errCode;
   EXEC SQL END DECLARE SECTION;

   EXEC SQL declare empCursor cursor for 
   select emp_name.first_name, emp_address.zip.code into :name, :zip from employee;
   EXEC SQL open empCursor;
   for (;;)
   {
      EXEC SQL fetch empCursor;
      if (strncmp(SQLSTATE, "00", 2) != 0)
         break;
      printf("%s   %s\n", name, zip);
   }
   if (strncmp(SQLSTATE, "02", 2) == 0)
      printf("Fetched row type values successfully\n");
   EXEC SQL close empCursor;
   EXEC SQL free empCursor;
}

/*******************************************************************
	Method Name	: query_DistinctType_values() 
	Parameters	: None 
	Return type	: None 
	Description	: Defines a cursor for fetching distinct type
			  values and fetches those values into host
                          variables which can be used in esql/c programs.
********************************************************************/

void query_DistinctType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char name[20];
   float price;
   int errCode;
   EXEC SQL END DECLARE SECTION;

   EXEC SQL declare dtypeCursor cursor for 
   select item_name, us_price into :name, :price from items;
   EXEC SQL open dtypeCursor;
   for (;;)
   {
      EXEC SQL fetch dtypeCursor;
      if (strncmp(SQLSTATE, "00", 2) != 0)
         break;
      printf("%s  $ %6.2f\n", name, price);
   }
   if (strncmp(SQLSTATE, "02", 2) == 0)
      printf("Fetched distinct type values successfully\n");
   EXEC SQL close dtypeCursor;
   EXEC SQL free dtypeCursor;
}

/*******************************************************************
	Method Name	: query_CollectionType_values() 
	Parameters	: None 
	Return type	: None 
	Description	: This function queries for collection type 
			  values and stores them in host variable.
********************************************************************/

void query_CollectionType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   client collection set(integer not null) aSet; 
   client collection list(name_t not null) aList; 
   int setElem, errCode;
   EXEC SQL END DECLARE SECTION;

   EXEC SQL allocate collection :aSet;
   EXEC SQL allocate collection :aList;
   EXEC SQL select names, age into :aList, :aSet from collections;
   errCode = SQLCODE;
 /*  
   EXEC SQL declare collCursor cursor for 
   select names, age into :aList, :aSet from collections;

   EXEC SQL open collCursor;
   for (;;)
   {
      EXEC SQL fetch collCursor;
      if (strncmp(SQLSTATE, "00", 2) != 0)
         break;
   }
   if (strncmp(SQLSTATE, "02", 2) == 0)
      printf("Fetched collection type values successfully\n");
   EXEC SQL close collCursor;
   EXEC SQL free collCursor;
*/
   EXEC SQL deallocate collection :aSet;
   EXEC SQL deallocate collection :aList;
}

/*******************************************************************
	Method Name	: query_OpaqueType_values 
	Parameters	: None 
	Return type	: None 
	Description	: Queries for opqaue types and shows how to 
			  use those types through a host variable. 
********************************************************************/

void query_OpaqueType_values()
{
   EXEC SQL BEGIN DECLARE SECTION;
   lvarchar 'complexnumber' complexNum[5 ];
   fixed binary 'complexnumber' ComplexNumber cpNum;

   /* The following declaration should have worked. Could be a bug*/
   /*lvarchar 'ComplexNumber' complexNum[20];  */
   int num;
   int errCode;
   char cNum[3];
	char message[256];
	int msg_len;
   EXEC SQL END DECLARE SECTION;

   EXEC SQL declare opaqueCursor cursor for 
   select x, number, number::lvarchar into :num, :cpNum,:complexNum from complex;
   EXEC SQL open opaqueCursor;
   for (;;)
   {
      EXEC SQL fetch opaqueCursor;
      if (strncmp(SQLSTATE, "00", 2) != 0)
         break;
      printf("complex num in binary = %d+%di",cpNum.complexNum[0], cpNum.complexNum[1]);
      printf(" and in text = %s\n", complexNum); 
   }
   if (strncmp(SQLSTATE, "02", 2) == 0)
      printf("Fetched opaque type values successfully\n");
   EXEC SQL close opaqueCursor;
   EXEC SQL free opaqueCursor;
}