/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
** 
** The following sample of source code ("Sample") is owned by International 
** Business Machines Corporation or one of its subsidiaries ("IBM") and is 
** copyrighted and licensed, not sold. You may use, copy, modify, and 
** distribute the Sample in any form without payment to IBM, for the purpose of 
** assisting you in the development of your applications.
** 
** The Sample code is provided to you on an "AS IS" basis, without warranty of 
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
** not allow for the exclusion or limitation of implied warranties, so the above 
** limitations or exclusions may not apply to you. IBM shall not be liable for 
** any damages you suffer as a result of using, copying, modifying or 
** distributing the Sample, even if IBM has been advised of the possibility of 
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: dtudt.sqc 
**    
** SAMPLE: How to create, use, and drop user-defined distinct types 
**           
** SQL STATEMENTS USED:
**         CREATE DISTINCT TYPE 
**         EXECUTE IMMEDIATE
**         DROP DISTINCT TYPE
**
**                           
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, building, and running DB2 
** applications, visit the DB2 Information Center: 
**     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h"

int UdtCreate(void);
int UdtUse(void);
int UdtDrop(void);

EXEC SQL BEGIN DECLARE SECTION;
  char strStmt[257];
EXEC SQL END DECLARE SECTION;

int main(int argc, char *argv[])
{
  int rc = 0;
  char dbAlias[SQL_ALIAS_SZ + 1];
  char user[USERID_SZ + 1];
  char pswd[PSWD_SZ + 1];

  /* check the command line arguments */
  rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
  if (rc != 0)
  {
    return rc;
  }

  printf("\nTHIS SAMPLE SHOWS HOW TO CREATE/USE/DROP UDTs.\n");

  /* connect to the database */
  rc = DbConn(dbAlias, user, pswd);
  if (rc != 0)
  {
    return rc;
  }

  rc = UdtCreate();
  rc = UdtUse();
  rc = UdtDrop();

  /* disconnect from the database */
  rc = DbDisconn(dbAlias);
  if (rc != 0)
  {
    return rc;
  }

  return 0;
} /* end main */

int UdtCreate(void)
{
  struct sqlca sqlca;

  printf("\n-----------------------------------------------------------");
  printf("\nUSE THE SQL STATEMENTS:\n");
  printf("  CREATE DISTINCT TYPE\n");
  printf("  COMMIT\n");
  printf("TO CREATE UDTs.\n");

  printf("\n  CREATE DISTINCT TYPE udt1 AS INTEGER WITH COMPARISONS");

  EXEC SQL CREATE DISTINCT TYPE udt1 AS INTEGER WITH COMPARISONS;
  EMB_SQL_CHECK("udt1 -- create");

  printf("\n  CREATE DISTINCT TYPE udt2 AS CHAR(2) WITH COMPARISONS");

  EXEC SQL CREATE DISTINCT TYPE udt2 AS CHAR(2) WITH COMPARISONS;
  EMB_SQL_CHECK("udt2 -- create");

  printf(
    "\n  CREATE DISTINCT TYPE udt3 AS DECIMAL(7, 2) WITH COMPARISONS\n");

  EXEC SQL CREATE DISTINCT TYPE udt3 AS DECIMAL(7, 2) WITH COMPARISONS;
  EMB_SQL_CHECK("udt3 -- create");

  printf("  COMMIT\n");

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  return 0;
} /* UdtCreate */

int UdtUse(void)
{
  struct sqlca sqlca;

  printf("\n-----------------------------------------------------------");
  printf("\nUSE THE SQL STATEMENTS:\n");
  printf("  CREATE TABLE\n");
  printf("  INSERT\n");
  printf("  COMMIT\n");
  printf("TO USE UTDs.\n");

  /* create udt_table */
  printf("\n  CREATE TABLE udt_table(col1 udt1, col2 udt2, col3 udt3)\n");
  strcpy(strStmt,
         "  CREATE TABLE udt_table (col1 udt1, col2 udt2, col3 udt3)");
 
  EXEC SQL EXECUTE IMMEDIATE :strStmt;
  EMB_SQL_CHECK("stmt -- execute immediate");

  printf("  COMMIT\n");

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  /* insert udt_table */
  printf("\n  INSERT INTO udt_table"
         "\n    VALUES(CAST(77 AS udt1),"
         "\n           CAST('ab' AS udt2),"
         "\n           CAST(111.77 AS udt3))\n");

  strcpy(strStmt, "  INSERT INTO udt_table "
                  "    VALUES(CAST(77 AS udt1), "
                  "           CAST('ab' AS udt2), "
                  "           CAST(111.77 AS udt3)) ");

  EXEC SQL EXECUTE IMMEDIATE :strStmt;
  EMB_SQL_CHECK("stmt -- execute immediate");

  printf("  COMMIT\n");

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  /* drop udt_table */
  printf("\n  DROP TABLE udt_table\n");
  strcpy(strStmt, "  DROP TABLE udt_table ");

  EXEC SQL EXECUTE IMMEDIATE :strStmt;
  EMB_SQL_CHECK("stmt -- execute immediate");

  printf("  COMMIT\n");

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  return 0;
} /* UdtUse */

int UdtDrop(void)
{
  struct sqlca sqlca;

  printf("\n-----------------------------------------------------------");
  printf("\nUSE THE SQL STATEMENTS:\n");
  printf("  DROP\n");
  printf("  COMMIT\n");

  printf("TO DROP UDTs.\n");

  printf("\n  DROP DISTINCT TYPE udt1\n");

  EXEC SQL DROP DISTINCT TYPE udt1;
  EMB_SQL_CHECK("udt1 -- drop");

  printf("  DROP DISTINCT TYPE udt2\n");

  EXEC SQL DROP DISTINCT TYPE udt2;
  EMB_SQL_CHECK("udt2 -- drop");

  printf("  DROP DISTINCT TYPE udt3\n");

  EXEC SQL DROP DISTINCT TYPE udt3;
  EMB_SQL_CHECK("udt3 -- drop");

  printf("  COMMIT\n");

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  return 0;
} /* UdtDrop */