Product Documentation

FairCom Java Stored Procedures

Previous Topic

Next Topic

Define

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();

TOCIndex