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