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
--
32 years of continuous innovation!
17 Years of R:BASE Technologies, Inc. making R:BASE what it is today!
--
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
