Switch to: V9V8V7V6V5

OID Field

This field plays the role of a unique identifier of a record in scope of the whole VDatabase. The OID field is of ULLONG type (8 bytes).

The “OID” field value is calculated by the formula (TableID « 32) + RecID. As it is the combination of 2 unique values in the scope of VDatabase, the OID value also is unique.

It is easy to see that OID Values are big unsigned integer numbers. Actually, even for TableID = 1 and RecID = 1 we get:

(1 << 32) + 1 = 4,294,967,297        -- note that this value does not fit 4 bytes

4,294,967,297 = 0x0000000100000001   -- in hex

OID Field and ODMG Standard

The OID field of Valentina engine perfectly fits into the requirements of the ODMG standard for Object-Oriented databases. It can be used to ensure the uniqueness of a record (i.e. of an object/instance) in the scope of the whole database.

Usage of the OID Field

Let's assume you've got some table T with the ULLONG column “REF”. In this column, you can store the OID values of any record of any table of this VDatabase. Easy.

Now, the question is how to find a record pointed by some OID. The solution is very easy and effective:

// This function returns a Table pointed by a given OID 
// and this Table has the current record pointed by OID.
//
VTable VDatabase.GoToRecord( inOID )
{
    TableID tblID = (TableID)(inOID >> 32);
 
    // Find table by its ID 
    VTable tbl = this->get_Table( tblID ); 
    if( tbl )
        tbl.Recid = inOID & 0xFFFFFFFF; 
 
    return tbl;
}

OID Field Access

You can access the OID field only by name. Remember that the OID field is of ULLong type, so you may need to cast to this type to get/set its value.

OID Field in SQL

Valentina seamlessly integrates the OID field into SQL. You can use it in any SELECT query. Note that “SELECT * …” does neither select the RecID nor other Table Methods. You need to use “SELECT ** …” or specify the OID field explicitly by name.

SELECT * FROM T -- does not select OID but only custom fields.
 
SELECT ** FROM T -- selects custom fields and OID, RecID and all other Table methods.
 
SELECT OID, f1, f2 FROM T -- selects 3 fields.

Assume you need some stored procedure which is able to take OID as argument and return table name and current RecID.

CREATE OR REPLACE PROCEDURE sp1( IN inOID llong )
BEGIN 
	DECLARE tblID llong;
	SET tblID = (inOID >> 32);
 
	DECLARE tblName String(50);
	DECLARE cur1 CURSOR FOR SELECT name FROM (SHOW TABLES) WHERE type = 'TABLE' AND id = tblID;
	OPEN cur1; 
	BEGIN
		FETCH FIRST cur1 INTO tblName;
	EXCEPTION
	WHEN ERR_CURSOR_WRONG_POSITION THEN 
		SET tblName = NULL;
	END
	CLOSE cur1;
 
	SELECT tblName, bit_and(inOID,x'FFFFFFFF');
END

Another example. Assume you need procedure which is able to take OID as argument and return cursor from the table which OID belongs to.

CREATE OR REPLACE PROCEDURE sp1( IN inOID llong )
BEGIN 
	DECLARE tblID llong;
	SET tblID = (inOID >> 32);
 
	DECLARE tblName String(50);
	DECLARE cur1 CURSOR FOR SELECT name FROM (SHOW TABLES) WHERE type = 'TABLE' AND id = tblID;
	OPEN cur1; 
	BEGIN
		FETCH FIRST cur1 INTO tblName;
	EXCEPTION
	WHEN ERR_CURSOR_WRONG_POSITION THEN 
		SET tblName = NULL;
	END
	CLOSE cur1;
 
        IF tblName IS NOT NULL THEN
            DECLARE queryStr String(200);
            SET queryStr = 'SELECT * FROM ' || tblName;
	    EXECUTE queryStr;
        END IF
END