Tech Tip: Utility Method to Get a Field's Index Type

PRODUCT: 4D | VERSION: 15.x | PLATFORM: Mac & Win

Published On: December 29, 2016

Below is a utility method that will return the index type of a single field index for the field specified by the table and field number passed.

// Util_GET_FIELD_INDEX_TYPE
//
// Details:
//    Returns the index type of the
//    single field index for the specified field
//
// Parameters:
//    $1 - Table Number in longint
//    $2 - Field Number in longint
// Output:
//    $0 - Index Type in longint
//       -1 = No index
//       1 = B-Tree
//       3 = Cluster B-Tree
//       7 = Automatic

C_LONGINT($1;$tblNum_l)
C_LONGINT($2;$fldNum_l)
C_LONGINT($0;$indxType_l)

ARRAY TEXT($indxID_at;0)

C_TEXT($indxID_t)
C_LONGINT($cnt_l)
C_LONGINT($fldCnt_l)

$tblNum_l:=$1
$fldNum_l:=$2

Begin SQL
   Select _USER_INDEXES.INDEX_ID
   From _USER_INDEXES
   Inner JOIN _USER_IND_COLUMNS
   Where _USER_IND_COLUMNS.TABLE_ID=:tblNum_l
   And _USER_IND_COLUMNS.COLUMN_ID=:fldNum_l
   And _USER_IND_COLUMNS.COLUMN_POSITION=1
   And _USER_INDEXES.INDEX_ID=_USER_IND_COLUMNS.INDEX_ID
   Into :indxID_at
End SQL

$cnt_l:=1
While ($cnt_l<=Size of array($indxID_at))
   $indxID_t:=$indxID_at{$cnt_l}
   Begin SQL
      Select _USER_INDEXES.INDEX_TYPE,Count(_USER_IND_COLUMNS.COLUMN_ID)
      From _USER_INDEXES
      Inner JOIN _USER_IND_COLUMNS
      Where _USER_INDEXES.INDEX_ID=:indxID_t
      And _USER_INDEXES.INDEX_ID=_USER_IND_COLUMNS.INDEX_ID
      Group by _USER_INDEXES.INDEX_TYPE
      INTO :indxType_l,:fldCnt_l
   End SQL

   If ($fldCnt_l=1)
      $cnt_l:=Size of array($indxID_at)+1
   Else
      $cnt_l:=$cnt_l+1
      $indxType_l:=-1
   End if
End while
$0:=$indxType_l

Commented by bernd bippus on February 21, 2017 at 3:06 AM

// indxType_l:=Util_GET_FIELD_INDEX_TYPE(tblNum_l;fldNum_l) // // Details: // Returns the index type of the // single field index for the specified field // // Parameters: // $1 - Table Number in longint // $2 - Field Number in longint // Output: // $0 - Index Type in longint // -1 = No index // 1 = B-Tree // 3 = Cluster B-Tree // 7 = Automatic // // 2017-02-21 by BB: BugFix: tblNum_l not $tblNum_l ... C_LONGINT($1;tblNum_l) C_LONGINT($2;fldNum_l) C_LONGINT($0;indxType_l) ARRAY TEXT(indxID_at;0) C_TEXT(indxID_t) C_LONGINT($cnt_l) C_LONGINT(fldCnt_l) tblNum_l:=$1 fldNum_l:=$2 Begin SQL Select _USER_INDEXES.INDEX_ID From _USER_INDEXES Inner JOIN _USER_IND_COLUMNS Where _USER_IND_COLUMNS.TABLE_ID=:tblNum_l And _USER_IND_COLUMNS.COLUMN_ID=:fldNum_l And _USER_IND_COLUMNS.COLUMN_POSITION=1 And _USER_INDEXES.INDEX_ID=_USER_IND_COLUMNS.INDEX_ID Into :indxID_at End SQL $cnt_l:=1 While ($cnt_l<=Size of array(indxID_at)) indxID_t:=indxID_at{$cnt_l} Begin SQL Select _USER_INDEXES.INDEX_TYPE,Count(_USER_IND_COLUMNS.COLUMN_ID) From _USER_INDEXES Inner JOIN _USER_IND_COLUMNS Where _USER_INDEXES.INDEX_ID=:indxID_t And _USER_INDEXES.INDEX_ID=_USER_IND_COLUMNS.INDEX_ID Group by _USER_INDEXES.INDEX_TYPE INTO :indxType_l,:fldCnt_l End SQL If (fldCnt_l=1) $cnt_l:=Size of array(indxID_at) 1 Else $cnt_l:=$cnt_l 1 indxType_l:=-1 End if End while $0:=indxType_l

Commented by bernd bippus on February 21, 2017 at 3:06 AM

// 2017-02-21 by BB: BugFix: tblNum_l not $tblNum_l ...

Commented by bernd bippus on February 21, 2017 at 3:05 AM