/**************************************************************************** ** (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: tbident.sqc ** ** SAMPLE: How to use identity columns ** ** This program creates two tables in the sample database with ** identity columns. The "building" table contains the identity ** column, 'GENERATED ALWAYS'. The "warehouse" table contains the ** identity column, 'GENERATED BY DEFAULT'. Some values are inserted ** into both tables and the contents of the tables are displayed. ** There is NO relation between the two tables. The tables are ** dropped at the end of the program. ** ** SQL STATEMENTS USED: ** INCLUDE ** CREATE TABLE ** INSERT ** EXECUTE IMMEDIATE ** PREPARE ** DECLARE CURSOR ** OPEN ** FETCH ** CLOSE ** DROP TABLE ** ** STRUCTURES USED: ** sqlca ** ** ***************************************************************************** ** ** 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" /* Function prototypes */ int GenerateAlways(void); int GenerateByDefault(void); /* Host variable declarations */ EXEC SQL BEGIN DECLARE SECTION; char create[100]; char insert[300]; char print[50]; short id; char address[20]; char city[10] = {0}; short floors; short capacity; short employees; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; 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]; struct sqlca sqlca; /* check the command line arguments */ rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd); if (rc != 0) { return rc; } printf("\nTHIS SAMPLE SHOWS HOW TO USE IDENTITY COLUMNS\n"); /* connect to the database */ rc = DbConn(dbAlias, user, pswd); if (rc != 0) { return rc; } printf("\n-------------------------------------------------------\n"); /* Demonstrate how to create tables with identity columns generated always */ GenerateAlways(); printf("\n-------------------------------------------------------\n"); /* Demonstrate how to create tables with identity columns generated by default*/ GenerateByDefault(); printf("\n-------------------------------------------------------\n"); /* disconnect from sample data */ rc = DbDisconn(dbAlias); if (rc != 0) { return rc; } return rc; } int GenerateAlways(void) { int rc = 0; struct sqlca sqlca; /* Create the table 'building' */ printf("USE THE SQL STATEMENTS:\n"); printf(" CREATE TABLE\n"); printf(" INSERT INTO\n"); printf("TO CREATE AN IDENTITY COLUMN WITH VALUE 'GENERATED ALWAYS'\n"); printf("AND TO INSERT DATA IN THE TABLE\n\n"); printf(" CREATE TABLE building(bldnum INT\n"); printf(" GENERATED ALWAYS AS IDENTITY\n"); printf(" (START WITH 1, INCREMENT BY 1),\n"); printf(" addr VARCHAR(20),\n"); printf(" city VARCHAR(10),\n"); printf(" floors SMALLINT,\n"); printf(" employees SMALLINT)\n\n"); EXEC SQL CREATE TABLE building(bldnum INT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), addr VARCHAR(20), city VARCHAR(10), floors SMALLINT, employees SMALLINT); EMB_SQL_CHECK("Table: create"); /* Insert records into the table 'building' */ printf(" INSERT INTO building(bldnum, addr, city, floors, employees)\n"); printf(" VALUES(DEFAULT, '110 Woodpark St', 'Smithville', 3, 10),\n"); printf(" (DEFAULT, '123 Sesame Ave', 'Jonestown', 16, 13),\n"); printf(" (DEFAULT, '738 Eglinton Rd', 'Whosburg', 2, 10)\n"); printf(" (DEFAULT, '832 Lesley Blvd', 'Centertown', 2, 18)\n"); strcpy(insert, "INSERT INTO building(bldnum, addr, city,"); strcat(insert, "floors, employees)"); strcat(insert, "VALUES(DEFAULT, '110 Woodpark St', 'Smithville', 3, 10),"); strcat(insert, "(DEFAULT, '123 Sesame Ave', 'Jonestown', 16, 13),"); strcat(insert, "(DEFAULT, '738 Eglinton Rd', 'Whosburg', 2, 10),"); strcat(insert, "(DEFAULT, '832 Lesley Blvd', 'Centertown', 2, 18)"); EXEC SQL EXECUTE IMMEDIATE :insert; EMB_SQL_CHECK("Table: insert"); /* Print Building Table */ strcpy(print, "SELECT * FROM building"); printf("\n %s\n", print); EXEC SQL PREPARE printBuilding FROM :print; EXEC SQL DECLARE bldCursor CURSOR FOR printBuilding; EXEC SQL OPEN bldCursor; EMB_SQL_CHECK("Cursor: open"); EXEC SQL FETCH bldCursor INTO :id, :address, :city, :floors, :employees; EMB_SQL_CHECK("Cursor: fetch"); printf (" ID ADDRESS CITY FLOORS EMP\n"); printf (" --- --------------------- ------------ ------ ---\n"); while (sqlca.sqlcode != 100) { printf(" %3d %-21s %-12s", id, address, city); printf(" %6d %3d\n", floors, employees); EXEC SQL FETCH bldCursor INTO :id, :address, :city, :floors, :employees; EMB_SQL_CHECK("Cursor: fetch"); } EXEC SQL CLOSE bldCursor; EMB_SQL_CHECK("Cursor: close"); printf("\n Dropping sample table building\n"); EXEC SQL DROP TABLE building; EMB_SQL_CHECK("Table: drop"); return rc; } int GenerateByDefault(void) { int rc = 0; struct sqlca sqlca; /* Create the table 'warehouse' */ printf("\nUSE THE SQL STATEMENT:\n"); printf(" CREATE TABLE\n"); printf(" INSERT INTO\n"); printf("TO CREATE AN IDENTITY COLUMN WITH VALUE 'GENERATED BY DEFAULT'\n"); printf("AND TO INSERT DATA IN THE TABLE\n\n"); printf(" CREATE TABLE warehouse(whnum INT\n"); printf(" GENERATED BY DEFAULT AS IDENTITY\n"); printf(" (START WITH 1, INCREMENT BY 1),\n"); printf(" addr VARCHAR(20),\n"); printf(" city VARCHAR(10),\n"); printf(" capacity SMALLINT,\n"); printf(" employees SMALLINT)\n\n"); EXEC SQL CREATE TABLE warehouse(whnum INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), addr VARCHAR(20), city VARCHAR(10), capacity SMALLINT, employees SMALLINT); EMB_SQL_CHECK("Table: create"); /* Insert records into the table warehouse */ printf(" INSERT INTO warehouse(whnum, addr, city, capacity, employees)\n"); printf(" VALUES(DEFAULT, '92 Bothfield Dr', 'Yorkville', 23, 100),\n"); printf(" (DEFAULT, '33 Giant Road', 'Centertown', 100, 22),\n"); printf(" (3, '8200 Warden Blvd', 'Smithville', 254, 10)\n"); printf(" (DEFAULT, '53 4th Ave', 'Whosburg', 97, 28)\n"); strcpy(insert, "INSERT INTO warehouse(whnum, addr, city, "); strcat(insert, "capacity, employees) "); strcat(insert, "VALUES(DEFAULT, '92 Bothfield Dr',"); strcat(insert, "'Yorkville', 23, 100),"); strcat(insert, "(DEFAULT, '33 Giant Road', 'Centertown', 100, 22), "); strcat(insert, "(3, '8200 Warden Blvd', 'Smithville', 254, 10), "); strcat(insert, "(DEFAULT, '53 4th Ave', 'Whosburg', 97, 28)"); EXEC SQL EXECUTE IMMEDIATE :insert; EMB_SQL_CHECK("Table: insert"); /* Print Warehouse Table */ strcpy(print, "SELECT * FROM warehouse"); printf("\n %s\n", print); EXEC SQL PREPARE printWarehouse FROM :print; EXEC SQL DECLARE warehouseCursor CURSOR FOR printWarehouse; EXEC SQL OPEN warehouseCursor; EMB_SQL_CHECK("Cursor: Open"); EXEC SQL FETCH warehouseCursor INTO :id, :address, :city, :capacity, :employees; EMB_SQL_CHECK("Cursor: Fetch"); printf (" ID ADDRESS CITY FLOORS EMP\n"); printf (" --- --------------------- ------------ ------ ---\n"); while (sqlca.sqlcode != 100) { printf(" %3d %-21s %-12s", id, address, city); printf(" %6d %3d\n", capacity, employees); EXEC SQL FETCH warehouseCursor INTO :id, :address, :city, :capacity, :employees; EMB_SQL_CHECK("Cursor: Fetch"); } EXEC SQL CLOSE warehouseCursor; EMB_SQL_CHECK("Cursor: Fetch"); printf("\n NOTE:\n"); printf(" An Identity Column with value 'GENERATED BY DEFAULT' might \n"); printf(" not contain a unique value for each row! To ensure a unique \n"); printf(" value for each row, define a unique index on the Identity Column.\n"); printf("\n Dropping sample table warehouse\n"); EXEC SQL DROP TABLE warehouse; EMB_SQL_CHECK("Table: Drop"); return rc; }