Tech Tip: Utility method to get primary key field pointers of a table

PRODUCT: 4D | VERSION: 15.1 | PLATFORM: Mac & Win

Published On: June 23, 2016

Below is an utility method to retrieve the pointers to the primary key fields of a given table. A second paramater of a pointer to an pointer array can be passed for composite primary keys.

// Method: UTIL_GET_TABLE_PK
// Returns pointer to primary key field(s) of a given table
//
// Parameters:
// $1 - Pointer to table
// $2 - Pointer to an array of pointers to contain primary key fields (OPTIONAL)
// $0 - Returns pointer to field that is primary key
C_POINTER($1;$tblPtr;$0;$pkPointer)
C_LONGINT($tblNum;$i)
C_TEXT($cID)
If(Count parameters>=1)
  $tblPtr:=$1
  $tblNum:=Table($tblPtr)
  ARRAY TEXT($constrIDs;0)
  Begin SQL
    Select CONSTRAINT_ID from _USER_CONSTRAINTS
    Where Table_ID=:$tblNum AND CONSTRAINT_TYPE='P'
    Into :$constrIDs
  End SQL

  ARRAY LONGINT($colIDs;0)
  ARRAY TEXT($colNames;0)
  For ($i;1;Size of array($constrIDs))
  $cID:=$constrIDs{$i}
  Begin SQL
    Select COLUMN_ID, COLUMN_NAME from _USER_CONS_COLUMNS
    Where Table_ID=:$tblNum AND CONSTRAINT_ID=:$cID
    Into :$colIDs, :$colNames
  End SQL
  End for

  if(Size of array($colIDs)>=1)
    $pkPointer:=Field($tblNum;$colIDs{1})
    $0:=$pkPointer
  End if
  If (Count parameters>=2)
    C_POINTER($2;$arrPKs)
    $arrPKs:=$2
    For ($i;1;Size of array($colIDs))
      APPEND TO ARRAY($arrPKs->;Field($tblNum;$colIDs{$i}))
    End for
  End if
End if

Example:

Given the following table:

The primary key for the table is the "ID" field. The following code can be ran to programmatically retrieve the field pointer:

$pkPtr:=UTIL_GET_TABLE_PK(->[Table_1])
//$pkPtr:= ->[Table_1]ID

Below is a table with a composite primary key. The primary key fields are ID1, ID2, and ID3:

Running the following code will retrieve three pointers to the field in the $arrPks array.

ARRAY POINTER($arrPks;0)
UTIL_GET_TABLE_PK(->[CompositeKey];->$arrPks)

Below are the results of running the code: