|
|
|
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
DROP PROCEDURE Define;
CREATE PROCEDURE Define()
BEGIN
int TABLE_ALREADY_EXIST = -20041;
int INDEX_ALREADY_EXIST = -20028;
try
{
SQLIStatement cm = new SQLIStatement (
"CREATE TABLE custmast (" +
"cm_custnumb CHAR(4), " +
"cm_custzipc CHAR(9), " +
"cm_custstat CHAR(2), " +
"cm_custrtng CHAR(1), " +
"cm_custname VARCHAR(47), " +
"cm_custaddr VARCHAR(47), " +
"cm_custcity VARCHAR(47))"
);
cm.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement cm1 = new SQLIStatement (
"CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)"
);
cm1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co = new SQLIStatement (
"CREATE TABLE custordr (" +
"co_ordrdate DATE, " +
"co_promdate DATE, " +
"co_ordrnumb CHAR(6), " +
"co_custnumb CHAR(4))"
);
co.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co1 = new SQLIStatement (
"CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)"
);
co1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement co2 = new SQLIStatement (
"CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)"
);
co2.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi = new SQLIStatement (
"CREATE TABLE ordritem (" +
"oi_sequnumb SMALLINT, " +
"oi_quantity SMALLINT, " +
"oi_ordrnumb CHAR(6), " +
"oi_itemnumb CHAR(5))"
);
oi.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi1 = new SQLIStatement (
"CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)"
);
oi1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement oi2 = new SQLIStatement (
"CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)"
);
oi2.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement im = new SQLIStatement (
"CREATE TABLE itemmast (" +
"im_itemwght INTEGER, " +
"im_itempric MONEY, " +
"im_itemnumb CHAR(5), " +
"im_itemdesc VARCHAR(47))"
);
im.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != TABLE_ALREADY_EXIST)
throw e;
}
try
{
SQLIStatement im1 = new SQLIStatement (
"CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)"
);
im1.execute();
}
catch (DhSQLException e)
{
if (e.sqlErr != INDEX_ALREADY_EXIST)
throw e;
}
END
-- Define
ECHO DEFINE;
ECHO Create tables...;
CALL Define();
