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