Friday, January 02, 2015
Tip of the Day: Undocumented IINFO Function
Product.: R:BASE eXtreme 9.5 (32/64)
Build...: 9.5.4.31125 or higher www.rupdates.com, www.rbaseupdates.com
Section.: R:BASE Functions
Keywords: Functions, Tables, Server tables, Columns, Indexes
There is an undocumented IINFO function in R:BASE which returns
information about tables, columns, or indexes
by reading internal bitmask flags. The function requires the ID number
for the table, column, or index. This
ID number can be obtained from the system tables SYS_TABLES,
SYS_COLUMNS, or SYS_INDEXES, respectively. IINFO
returns 0 if FALSE, or the number in argument 3 if TRUE.
Syntax:
(IINFO(flagtype,id,bitmask))
Where:
flagtype specifies if the info returned is for table flags, column
flags, column flags
for server tables, or index flags
id specifies the ID number from the system tables for the
table ID, column ID, or
index ID
bitmask species the flag in the system table
Remarks:
. The values for arg1, arg2, and arg3 must be non-null integers, even
if a particular
argument is not needed for that case.
. IINFO returns 0 if FALSE, or the bitmask number in parameter 3 if
TRUE (flags 4-7).
Flags:
Info Flag Type ID Bitmask Description
--------------------------------------------------------------------------------------------------------------
Row ID 0 0 0 Returns the rowid of
the current row.
Minimum Data 1 integer 0 Returns the minimum
scale for the data type.
Type Scale
Maximum Data 2 integer 0 Returns the maximum
scale for the data type
Type Scale
Table Cascade 3 table ID 0 Returns cascade flag
for a table
Flag
Column Flags 4 column ID 1 Returns bitmask value
if is an autonumber column
4 column ID 2 Returns bitmask value
if a comment exists for column
4 column ID 4 Returns bitmask value
if column has a default value
4 column ID 8 Returns bitmask value
if column is temporary
4 column ID 16 Returns bitmask value
if column has an index
4 column ID 32 Returns bitmask value
if contains a USER default
4 column ID 64 Returns bitmask value
if contains a Not NULL flag
4 column ID 128 Returns bitmask value
if is a primary key or unique key
Column Flags 5 column ID 1 Returns bitmask value
if column is an optimal row qualifier
(Server Tables) 5 column ID 2 Returns bitmask value
if server column is read only
5 column ID 4 Returns bitmask value
if server column is autonumbered
5 column ID 8 Returns bitmask value
if server column row version qualifier
Table Flags 6 table ID 1 Returns bitmask value
if comment exists for table
6 table ID 2 Returns bitmask value
if table has a primary key
6 table ID 4 Returns bitmask value
if table has a foreign key
6 table ID 8 Returns bitmask value
if table has an autonumbered column
6 table ID 16 Returns bitmask value
if table has a default column
6 table ID 32 Returns bitmask value
if table is readonly (dBASE)
6 table ID 64 Returns bitmask value
if table is temporary
6 table ID 128 Returns bitmask value
if table has a referenced key
6 table ID 256 Returns bitmask value
if table has a Not NULL column
6 table ID 512 Returns bitmask value
if table has a unique key
6 table ID 1024 Returns bitmask value
if table has a column with a data type
greater than 10
6 table ID 2048 Returns bitmask value
if table has a VARBIT/VARCHAR column
6 table ID 4096 Returns bitmask value
if a cascade flag updates and deletes
through all primary
keys and unique keys
6 table ID 8192 Returns bitmask value
if server table has column aliases
6 table ID 16384 Returns bitmask value
if there is at least one trigger defined
for the table
6 table ID 32768 Returns bitmask value
if relation as system view which created
during multiple inner
joins
Index Flags 7 index ID 3 Returns bitmask value
for the constraint type:
7 index ID 4 Returns bitmask value
if this is a dBase index
7 index ID 8 Returns bitmask value
if this is a unique index
7 index ID 16 Returns bitmask value
if index is temporary
7 index ID 32 Returns bitmask value
if this is a referenced key
7 index ID 64 Returns bitmask value
if this is a case sensitive index
7 index ID 128 Returns bitmask value
if the is a Foreign Index
Examples:
-- Example 01:
-- Using flag type 0 for the Titles table in the RRBYW18 database
SELECT EmpTID,EmpTitle,(IINFO(0,0,0)) FROM Titles
EmpTID EmpTitle (IINFO(0,0
---------- ------------------------------ ----------
1 Office Manager 524289
2 Receptionist 524341
3 Sales Clerk 524393
4 Director Marketing 524445
5 Director Corporate Sales 524497
6 Director Government Sales 524549
7 Manager Support & Services 524601
8 Outside Sales 524653
-- Example 02:
-- Returns minimum and maximum data type scales using IINFO and
(CVAL('ROWCOUNT')) with the RRBYW18 database.
-- The values for currency will vary based upon the current CURRENCY
DIGITS setting.
SELECT (CVAL('ROWCOUNT')) AS SYS_TYPE, SYS_TYPE_NAME=18, +
(IINFO(1, (INT(CVAL('ROWCOUNT'))), 0)) AS SYS_MIN, +
(IINFO(2, (INT(CVAL('ROWCOUNT'))), 0)) AS SYS_MAX +
FROM SYS_TYPES
Here is what it generates:
SYS_TYPE SYS_TYPE_NAME SYS_MIN SYS_MAX
--------------- ------------------ ---------- ----------
1 CURRENCY 2 2
2 VARBIT -0- -0-
3 BITNOTE -0- -0-
4 BIT -0- -0-
5 VARCHAR -0- -0-
6 BIGNUM -0- -0-
7 BSTR -0- -0-
8 GUID -0- -0-
9 TEXT -0- -0-
10 NUMERIC 0 15
11 INTEGER 0 0
12 REAL -0- -0-
13 DOUBLE -0- -0-
14 DATE -0- -0-
15 TIME 0 3
16 DATETIME 0 3
17 NOTE -0- -0-
-- Example 03:
-- Displays tables with Cascade within the RRBYW18 database.
-- Note that the tables with 1 for the cascade value are tables
-- with primary keys that cascade to tables with foreign keys.
SELECT SYS_TABLE_NAME=20, +
SYS_TABLE_ID, +
(IINFO(3,SYS_TABLE_ID,0)) AS SYS_CASCADE +
FROM SYS_TABLES WHERE SYS_TABLE_TYPE = 'TABLE'
SYS_TABLE_NAME SYS_TABLE_ SYS_CASCAD
-------------------- ---------- ----------
Customer 29 1
CompUsed 30 0
SalesBonus 31 0
PaymentTerms 32 0
Contact 33 0
ProdLocation 34 0
Levels 35 0
Component 36 0
Product 37 0
SecurityTable 38 0
InvoiceHeader 39 0
PrintOptions 40 0
InvoiceDetail 41 0
LicenseInformation 43 0
Titles 44 1
Employee 45 1
StateAbr 46 0
FormTable 47 0
BonusRate 48 0
TestNote 49 0
RThemes_eXtreme 50 0
ContactCallNotes 51 0
tempemployee 74 0
-- Example 04:
-- Checks that the CustState colun in the Customer table is indexed
SET VAR vCustStateHasIndex = (IINFO(4,191,16))
SHOW VAR vCustStateHasIndex
16
-- Example 05:
-- Checks that the Employee table has a primary key
SET VAR vEmployeeHasPK = (IINFO(6,45,2))
SHOW VAR vEmployeeHasPK
2
-- Example 06:
-- Checks that the Component table has a referenced key.
SET VAR vComponentRef = (IINFO(6,39,128))
SHOW VAR vComponentRef
128
-- Example 07:
-- Returns the constraint type for the EmpID in the SalesBonus table
-- (0 = index, 1 = foreign key, 2 = primary key, 3 = unique key)
SET VAR vIsForeignKey = (IINFO(7,42,3))
SHOW VAR vIsForeignKey
1
SELECT (CVAL('ROWCOUNT')) AS SYS_TYPE, SYS_TYPE_NAME=18, +
(IINFO(1, (INT(CVAL('ROWCOUNT'))), 0)) AS SYS_MIN, +
(IINFO(2, (INT(CVAL('ROWCOUNT'))), 0)) AS SYS_MAX +
FROM SYS_TYPES
Here is what it generates:
SYS_TYPE SYS_TYPE_NAME SYS_MIN SYS_MAX
--------------- ------------------ ---------- ----------
1 CURRENCY 2 2
2 VARBIT -0- -0-
3 BITNOTE -0- -0-
4 BIT -0- -0-
5 VARCHAR -0- -0-
6 BIGNUM -0- -0-
7 BSTR -0- -0-
8 GUID -0- -0-
9 TEXT -0- -0-
10 NUMERIC 0 15
11 INTEGER 0 0
12 REAL -0- -0-
13 DOUBLE -0- -0-
14 DATE -0- -0-
15 TIME 0 3
16 DATETIME 0 3
17 NOTE -0- -0-
-- Example 03:
-- Displays tables with Cascade within the RRBYW18 database.
-- Note that the tables with 1 for the cascade value are tables
-- with primary keys that cascade to tables with foreign keys.
SELECT SYS_TABLE_NAME=20, +
SYS_TABLE_ID, +
(IINFO(3,SYS_TABLE_ID,0)) AS SYS_CASCADE +
FROM SYS_TABLES WHERE SYS_TABLE_TYPE = 'TABLE'
SYS_TABLE_NAME SYS_TABLE_ SYS_CASCAD
-------------------- ---------- ----------
Customer 29 1
CompUsed 30 0
SalesBonus 31 0
PaymentTerms 32 0
Contact 33 0
ProdLocation 34 0
Levels 35 0
Component 36 0
Product 37 0
SecurityTable 38 0
InvoiceHeader 39 0
PrintOptions 40 0
InvoiceDetail 41 0
LicenseInformation 43 0
Titles 44 1
Employee 45 1
StateAbr 46 0
FormTable 47 0
BonusRate 48 0
TestNote 49 0
RThemes_eXtreme 50 0
ContactCallNotes 51 0
tempemployee 74 0
-- Example 04:
-- Checks that the CustState colun in the Customer table is indexed
SET VAR vCustStateHasIndex = (IINFO(4,191,16))
SHOW VAR vCustStateHasIndex
16
-- Example 05:
-- Checks that the Employee table has a primary key
SET VAR vEmployeeHasPK = (IINFO(6,45,2))
SHOW VAR vEmployeeHasPK
2
-- Example 06:
-- Checks that the Component table has a referenced key.
SET VAR vComponentRef = (IINFO(6,39,128))
SHOW VAR vComponentRef
128
-- Example 07:
-- Returns the constraint type for the EmpID in the SalesBonus table
-- (0 = index, 1 = foreign key, 2 = primary key, 3 = unique key)
SET VAR vIsForeignKey = (IINFO(7,42,3))
SHOW VAR vIsForeignKey
1
Very Best R:egards,
Razzak.
www.rbase.com
www.facebook.com/rbase
