analytics/informix/demo/odbcdemo/dbcreate.h

/***************************************************************************
*	Licensed Materials - Property of IBM and/or HCL
*
*	IBM Informix Client-SDK
*
*	(C) Copyright IBM Corporation 2002 All rights reserved.
*	(c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.
*
*
*
*
*
*  Title:          dbCreate.h
*
*  Description:    Header file for dbCreate.c 
*                  Contains the arrays containg the SQL statements to 
*                  create the sample database and arrays of the data to 
*                  be loaded
*
***************************************************************************
*/


#define NUM_DBCREATE_STMTS	9
#define NUM_INSERT_STMTS	3

#define NUM_CUSTOMERS		9
#define NUM_ITEMS			4
#define NUM_ORDERS			9

#define BUFFER_LEN     1000




/*********************************************************************************
**
**  SQL STATEMENT BUFFERS
**
**********************************************************************************
*/

/*  SQL Statements for creating the sample database for UDO enabled
**  database servers (version number > 9)
*/

SQLCHAR createUdoDbStmts[NUM_DBCREATE_STMTS][BUFFER_LEN] = 
     {"CREATE DATABASE odbc_demodb WITH LOG;",
      "GRANT connect TO public;",
	  "GRANT dba TO public;",
      "DATABASE odbc_demodb;",
      "CREATE DISTINCT TYPE dollar AS decimal(5,2);",
	  "CREATE TABLE xa_tab1(a int)",
      "CREATE TABLE customer (cust_num      INTEGER PRIMARY KEY,\
                              fname         VARCHAR(10),\
                              lname         VARCHAR(10),\
                              address       ROW (address1 VARCHAR(20),\
                                                 city     VARCHAR(15),\
                                                 state    VARCHAR(5),\
                                                 zip      VARCHAR(6)),\
                              contact_dates LIST (DATETIME YEAR TO DAY NOT NULL));",
      "CREATE TABLE item (item_num    INTEGER PRIMARY KEY,\
                          description VARCHAR(20),\
                          stock       SMALLINT,\
                          ship_unit   VARCHAR(10),\
                          unit_price  dollar,\
                          advert      CLOB);",
      "CREATE TABLE orders (order_num  INTEGER PRIMARY KEY,\
                            cust_num   INTEGER,\
                            item_num   INTEGER,\
                            order_date DATE,\
                            quantity   INTEGER,\
                            FOREIGN KEY (cust_num) REFERENCES customer (cust_num),\
                            FOREIGN KEY (item_num) REFERENCES item (item_num));"
     };



/*  SQL Statements for creating the sample database for non-UDO enabled
**  database servers (version number < 9)
*/

SQLCHAR createNonUdoDbStmts[NUM_DBCREATE_STMTS - 1][BUFFER_LEN] = 
     {"CREATE DATABASE odbc_demodb WITH LOG;",
      "GRANT connect TO public;",
	  "GRANT dba TO public;",
      "DATABASE odbc_demodb;",
	  "CREATE TABLE xa_tab1(a int)",
      "CREATE TABLE customer (cust_num      INTEGER PRIMARY KEY,\
                              fname         VARCHAR(10),\
                              lname         VARCHAR(10),\
                              address1      VARCHAR(20),\
                              city          VARCHAR(15),\
                              state         VARCHAR(5),\
                              zip           VARCHAR(6),\
                              first_contact DATETIME YEAR TO DAY,\
                              last_contact  DATETIME YEAR TO DAY);",
      "CREATE TABLE item (item_num    INTEGER PRIMARY KEY,\
                          description VARCHAR(20),\
                          stock       SMALLINT,\
                          ship_unit   VARCHAR(10),\
                          unit_price  DECIMAL(5,2),\
                          advert      VARCHAR(200));",
      "CREATE TABLE orders (order_num  INTEGER PRIMARY KEY,\
                            cust_num   INTEGER,\
                            item_num   INTEGER,\
                            order_date DATE,\
                            quantity   INTEGER,\
                            FOREIGN KEY (cust_num) REFERENCES customer (cust_num),\
                            FOREIGN KEY (item_num) REFERENCES item (item_num));"
     };



/*  SQL Statements for inserting data into the sample database 
*/

SQLCHAR insertDBStmts[NUM_INSERT_STMTS][BUFFER_LEN] = 
     {"INSERT INTO customer VALUES ",
      "INSERT INTO item VALUES ",
      "INSERT INTO orders VALUES "};





/*********************************************************************************
**
**  DATA BUFFERS -- table 'CUSTOMER'
**
**********************************************************************************
*/

/*  Array containing values to be inserted into the 'customer' table for UDO 
**  enabled database servers (version number > 9)
*/


SQLCHAR udoCust[NUM_CUSTOMERS][BUFFER_LEN] = 
    {"(101,'Ludwig','Pauli',ROW('213 Erstwild Court','Sunnyvale','CA','94086'),LIST{'1994-08-16'})",
     "(102,'Carole','Sadler',ROW('785 Geary Street','San Francisco','CA','94117'),LIST{'1991-06-20','1993-07-17'})",
     "(103,'Philip','Currie',ROW('P.O. Box 3498','Palo Alto','CA','94303'),LIST{'1993-08-02','1993-08-20'})",
     "(104,'Anthony','Higgins',ROW('422 Bay Road','Redwood City','CA','94026'),LIST{'1994-02-08','1994-05-12'})",
     "(105,'Raymond','Vector',ROW('1899 La Loma Drive','Los Altos','CA','94022'),LIST{'1992-10-30','1994-03-22'})",
     "(106,'George','Watson',ROW('1143 Carver Place','Mountain View','CA','94063'),LIST{'1993-11-04'})",
     "(107,'Charles','Ream',ROW('41 Jordan Avenue','Palo Alto','CA','94304'),LIST{'1991-03-18','1993-04-26'})",
     "(108,'Donald','Quinn',ROW('587 Alvarado','Redwood City','CA','94063'),LIST{'1991-11-13','1994-01-06'})",
     "(109,'Jane','Miller',ROW('735 Maude Avenue','Sunnyvale','CA','94086'),LIST{'1993-11-17','1994-05-08'})"};



/*  Array containing values to be inserted into the 'customer' table for non-UDO 
**  enabled database servers (version number < 9)
*/


SQLCHAR nonUdoCust[NUM_CUSTOMERS][BUFFER_LEN] = 
    {"(101,'Ludwig','Pauli','213 Erstwild Court','Sunnyvale','CA','94086','1994-08-16','')",
     "(102,'Carole','Sadler','785 Geary Street','San Francisco','CA','94117','1991-06-20','1993-07-17')",
     "(103,'Philip','Currie','P.O. Box 3498','Palo Alto','CA','94303','1993-08-02','1993-08-20')",
     "(104,'Anthony','Higgins','422 Bay Road','Redwood City','CA','94026','1994-02-08','1994-05-12')",
     "(105,'Raymond','Vector','1899 La Loma Drive','Los Altos','CA','94022','1992-10-30','1994-03-22')",
     "(106,'George','Watson','1143 Carver Place','Mountain View','CA','94063','1993-11-04', '')",
     "(107,'Charles','Ream','41 Jordan Avenue','Palo Alto','CA','94304','1991-03-18','1993-04-26')",
     "(108,'Donald','Quinn','587 Alvarado','Redwood City','CA','94063','1991-11-13','1994-01-06')",
     "(109,'Jane','Miller','735 Maude Avenue','Sunnyvale','CA','94086','1993-11-17','1994-05-08')"};




/*********************************************************************************
**
**  DATA BUFFERS -- table 'ITEM'
**
**********************************************************************************
*/

/*  Array containing values to be inserted into the 'item' table for UDO 
**  enabled database servers (version number > 9)
*/


SQLCHAR udoItem[NUM_ITEMS][BUFFER_LEN] = 
    {"(1001,'Running shoes',250,'Pair','170.00',FILETOCLOB('item_1.txt','client'))",
     "(1002,'Bicycle chain',200,'Each','95.00',FILETOCLOB('item_2.txt','client'))",
     "(1003,'Golf balls',500,'Box of 6','49.50',FILETOCLOB('item_3.txt','client'))",
     "(1004,'Baseball glove',100,'Each','50.00',FILETOCLOB('item_4.txt','client'))"};



/*  Array containing values to be inserted into the 'item' table for non-UDO 
**  enabled database servers (version number < 9)
*/


SQLCHAR nonUdoItem[NUM_ITEMS][BUFFER_LEN] = 
    {"(1001,'Running shoes',250,'Pair','170.00',\
        'Super shock-absorbing gel pads disperse vertical energy into a horizontal plane\
         for extraordinary cushioned comfort. Great motion control. Mens only. Specify size.')",
     "(1002,'Bicycle chain',200,'Each','95.00',\
        'Double or triple crankset with choice of chainrings. For double crankset, chainrings\
         from 38-54 teeth. For triple crankset, chainrings from 24-48 teeth.')",
     "(1003,'Golf balls',500,'Box of 6','49.50',\
        'Long drive. Fluorescent yellow.')",
     "(1004,'Baseball glove',100,'Each','60.00',\
        'Brown leather. Specify baseman or infield/outfield style. Specify right- or left-handed.')"};



         
/*********************************************************************************
**
**  DATA BUFFERS -- table 'ORDERS'
**
**********************************************************************************
*/

/*  Array containing values to be inserted into the 'orders' table 
**  (same for UDO or non-UDO enabled databases)
*/


SQLCHAR orders[NUM_ORDERS][BUFFER_LEN] = 
    {"(1,104,1001,{d '1999-01-23'},3)",
     "(2,105,1001,{d '1998-12-30'},5)",
     "(3,101,1004,{d '1999-02-02'},11)",
     "(4,101,1002,{d '1998-11-26'},1)",
     "(5,102,1003,{d '1999-01-16'},12)",
     "(6,107,1004,{d '1998-12-27'},12)",
     "(7,107,1001,{d '1999-01-05'},2)",
     "(8,108,1004,{d '1999-01-23'},5)",
     "(9,106,1003,{d '1999-01-06'},8)"};