Creating a Custom Table in Millennium

In this article we describe describe on a high level, one way of creating a custom table in Millennium. The table will have an auto-numeric field as the primary key. It will also have several indexed and foreign keys.

Once the table is created in the Oracle database, it must also be created as a Discern Explorer table if you intend to use the table from within Millennium. Both of these objectives can be accomplished at once if you create the table with the appropriate CCL statement. Your user must be of group 0.

Attached is an example CCL program that creates a custom table. The program should be run from the back-end in CCL on all nodes:

IMPORTANT: Before creating the table, you need to be very sure you are not about to override an existing table.

Following is the CCL statement from our attached example that creates the custom table:

select into table CUST_LAB_STAMP
  ALPHA					= type("VC90")
, CONTROL				= type("VC90")
, EMISSION_DT_TM		= type("DQ8")
, LAB_STAMP_ID			= type("F8")
, MESSAGE				= type("VC90")
, ORDER_ID				= type("F8")
, STAMPSTRING			= type("VC120")
, STATUS				= type("VC3")
, UPDT_DT_TM			= type("DQ8")

with organization = "P"
   , indexUnique(LAB_STAMP_ID)
   , index(ORDER_ID)
   , synonym = "CUST_LAB_STAMP"

The LAB_STAMP_ID attribute will be the primary key. Attribute ORDER_ID is created as a indexed key.

This is how we give all users permissions to use the table:

rdb grant select on CUST_LAB_STAMP to public end
rdb grant insert on CUST_LAB_STAMP to public end
rdb grant update on CUST_LAB_STAMP to public end
rdb grant delete on CUST_LAB_STAMP to public end

We needed an auto-numeric primary key. To accomplish this, we create the following sequence:

rdb drop public synonym CUST_LABSTAMP_ID_SEQ end
rdb drop sequence CUST_LABSTAMP_ID_SEQ end
rdb create sequence CUST_LABSTAMP_ID_SEQ
  start with 1
  increment by 1
  minvalue 1
  nomaxvalue
  nocache
  nocycle
  order
end

An example for an insert statement would be the following:

INSERT INTO
	CUST_LAB_STAMP L
SET
  L.ALPHA = "7KYO3L5SRVV4H0A3G3BBG774",
  L.CONTROL = "7858",
  L.EMISSION_DT_TM = cnvtdatetime ("03-JUL-2024 00:00:00:00"), 
  L.LAB_STAMP_ID = seq(CUST_LABSTAMP_ID_SEQ, nextval),
  L.MESSAGE = "SUCCESS",
  L.ORDER_ID = 348549273,
  L.STAMPSTRING	= "[SELLO CTHER / 5 0.05 / 775E / 2024-1-34E47798 / 80069]",
  L.STATUS = "0",
  L.UPDT_DT_TM = cnvtdatetime(curdate, curtime3)

WITH NOCOUNTER
COMMIT

Now we can use this table just like any other table in Millennium:

An example of a result for a query on the custom table:

It’s as simple as that!

Although the steps above should be sufficient in most cases, we recommend reading the related reference page.