Note: For the most recent version of this sample code, see tbident-sqC in the db2-samples repository on Git Hub.
/****************************************************************************
** (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 <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h"
#if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \
(DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) )
#include <iomanip>
#include <iostream>
using namespace std;
#else
#include <iomanip.h>
#include <iostream.h>
#endif
// Host variable declarations
EXEC SQL BEGIN DECLARE SECTION;
char create[100];
char insert[300];
char print[50];
short id;
char address[20];
char city[11] = {0};
short floors;
short capacity;
short employees;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
class TbIdent
{
public:
int GenerateAlways(void);
int GenerateByDefault(void);
};
int TbIdent::GenerateAlways(void)
{
int rc = 0;
struct sqlca sqlca;
// Create the table 'building'
cout << "USE THE SQL STATEMENTS:" << endl;
cout << " CREATE TABLE" << endl;
cout << " INSERT INTO" << endl;
cout << "TO CREATE AN IDENTITY COLUMN WITH VALUE 'GENERATED ALWAYS'\n";
cout << "AND TO INSERT DATA IN THE TABLE\n" << endl;
cout << " CREATE TABLE building(bldnum INT" << endl;
cout << " GENERATED ALWAYS AS IDENTITY" << endl;
cout << " (START WITH 1, INCREMENT BY 1),\n";
cout << " addr VARCHAR(20)," << endl;
cout << " city VARCHAR(10)," << endl;
cout << " floors SMALLINT," << endl;
cout << " employees SMALLINT)\n" << endl;
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'
cout << " INSERT INTO building(bldnum, addr, city, floors, employees)\n";
cout << " VALUES(DEFAULT, '110 Woodpark St', 'Smithville', 3, 10),\n";
cout << " (DEFAULT, '123 Sesame Ave', 'Jonestown', 16, 13),\n";
cout << " (DEFAULT, '738 Eglinton Rd', 'Whosburg', 2, 10)\n";
cout << " (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");
cout << endl << print << endl;
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");
cout << " ID ADDRESS CITY FLOORS EMP" << endl;
cout << " --- --------------------- ------------ ------ ---" << endl;
while (sqlca.sqlcode != 100)
{
cout << " "
<< setw(3) << id
<< setw(21) << address
<< setw(12) << city
<< setw(6) << floors
<< setw(3) << employees
<< endl;
EXEC SQL FETCH bldCursor INTO :id, :address, :city, :floors, :employees;
EMB_SQL_CHECK("Cursor: fetch");
}
EXEC SQL CLOSE bldCursor;
EMB_SQL_CHECK("Cursor: close");
cout << "\n Dropping sample table building" << endl;
EXEC SQL DROP TABLE building;
EMB_SQL_CHECK("Table: drop");
return rc;
} //TbIdent::GenerateAlways
int TbIdent::GenerateByDefault(void)
{
int rc = 0;
struct sqlca sqlca;
// Create the table 'warehouse'
cout << "\nUSE THE SQL STATEMENT:" << endl;
cout << " CREATE TABLE" << endl;
cout << " INSERT INTO" << endl;
cout << "TO CREATE AN IDENTITY COLUMN WITH VALUE 'GENERATED BY DEFAULT'\n";
cout << "AND TO INSERT DATA IN THE TABLE\n" << endl;
cout << " CREATE TABLE warehouse(whnum INT" << endl;
cout << " GENERATED BY DEFAULT AS IDENTITY\n";
cout << " (START WITH 1, INCREMENT BY 1),\n";
cout << " addr VARCHAR(20)," << endl;
cout << " city VARCHAR(10)," << endl;
cout << " capacity SMALLINT," << endl;
cout << " employees SMALLINT)\n" << endl;
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
cout << " INSERT INTO warehouse(whnum, addr, city, capacity, employees)\n";
cout << " VALUES(DEFAULT, '92 Bothfield Dr', 'Yorkville', 23, 100),\n";
cout << " (DEFAULT, '33 Giant Road', 'Centertown', 100, 22),\n";
cout << " (3, '8200 Warden Blvd', 'Smithville', 254, 10)\n";
cout << " (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");
cout << endl << print << endl;
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");
cout << " ID ADDRESS CITY FLOORS EMP" << endl;
cout << " --- --------------------- ------------ ------ ---" << endl;
while (sqlca.sqlcode != 100)
{
cout << " " << setw(3) << id
<< " " << setw(21) << address
<< " " << setw(12) << city
<< " " << setw(6) << capacity
<< " " << setw(3) << employees
<< endl;
EXEC SQL FETCH warehouseCursor INTO :id, :address, :city, :capacity,
:employees;
EMB_SQL_CHECK("Cursor: Fetch");
}
EXEC SQL CLOSE warehouseCursor;
EMB_SQL_CHECK("Cursor: Fetch");
cout << "\n NOTE:" << endl;
cout << " An Identity Column with value 'GENERATED BY DEFAULT' might \n";
cout << " not contain a unique value for each row! To ensure a unique \n";
cout << " value for each row, define a unique index on the";
cout << "Identity Column.\n";
cout << "\n Dropping sample table warehouse" << endl;
EXEC SQL DROP TABLE warehouse;
EMB_SQL_CHECK("Table: Drop");
return rc;
} //GenerateByDefault
int main(int argc, char *argv[])
{
int rc = 0;
struct sqlca sqlca;
CmdLineArgs check;
DbEmb db;
TbIdent ident;
// check the command line arguments
rc=check.CmdLineArgsCheck1(argc,argv,db);
if (rc != 0)
{
return rc;
}
cout << "\nTHIS SAMPLE SHOWS HOW TO USE IDENTITY COLUMNS" << endl;
rc = db.Connect();
if (rc != 0)
{
return rc;
}
cout << "\n------------------------------------------------------" << endl;
// Demonstrate how to create tables with identity columns
// generated always
ident.GenerateAlways();
cout << "\n------------------------------------------------------" << endl;
// Demonstrate how to create tables with identity columns
// generated by default
ident.GenerateByDefault();
cout << "\n------------------------------------------------------" << endl;
// disconnect from sample data
rc = db.Disconnect();
if (rc != 0)
{
return rc;
}
return rc;
} //main