/**************************************************************************** ** (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 */