(IINFO(arg1,arg2,arg3))
The IINFO function is used to return 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 the type of information returned; table flags, column flags, column flags for server tables, index flags, row ID
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. The rowid is the offset from the start of file 2 where that row is stored. The values for arg2 and arg3 are not used. Using a 0 for arg2 and arg3 is recommended. |
Minimum Data Type Scale |
1 |
integer |
0 |
Returns the minimum scale for the data type. The value for arg2 must be an integer value. The value for arg3 is not used. Using a 0 for arg3 is recommended. |
Maximum Data Type Scale |
2 |
integer |
0 |
Returns the maximum scale for the data type. The value for arg2 must be an integer value. The value for arg3 is not used. Using a 0 for arg3 is recommended. |
Table Cascade Flag |
3 |
table ID |
0 |
Returns cascade flag for a table. The value for arg3 is not used. Using a 0 for arg3 is recommended. |
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 (Server Tables) |
5 |
column ID |
1 |
Returns bitmask value if column is an optimal row qualifier |
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: 0 = index, 1 = foreign key, 2 = primary key, 3 = unique key |
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 this is a Foreign Index |
Examples:
-- Example 01:
-- Using flag type 0 for the Titles table in the RRBYW20 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 RRBYW20 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 RRBYW20 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
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
