Tech Tip: How to get a Pointer to a Field via SQL

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

Published On: April 1, 2011

(This Tech Tip applies to both 4D v11 SQL and 4D v12)

This Tech Tip shows how to get a pointer to a field using SQL. This can be useful for writing generic code but, more specifically, components do not have direct access to host database fields; pointers are required when using DB4D code from a component.

Certainly a component can simply use the table number and field number (if known) to get a pointer to a field but what happens if the component is used in more than one database where the numbers for the same fields might be different? In terms of code readability and re-use, it is much more effective to use the table name and field name.

In order to get a pointer to a field using the Field command, the table number and field number are required. An easy way to acquire a these numbers based on the names is to use the SQL System Table "_USER_COLUMNS".

Here is a method to get a pointer to a field given the name of the table and field:

C_TEXT($1;$tableName_t)
C_TEXT($2;$fieldName_t)
C_POINTER($0;$fieldPointer_p)

C_LONGINT($tableNumber_l;$fieldNumber_l)

$tableName_t:=$1
$fieldName_t:=$2

Begin SQL
SELECT TABLE_ID, COLUMN_ID
FROM _USER_COLUMNS
WHERE TABLE_NAME = :$tableName_t
AND COLUMN_NAME = :$fieldName_t
INTO :$tableNumber_l, :$fieldNumber_l;
End SQL

$fieldPointer_p:=Field($tableNumber_l;$fieldNumber_l)

$0:=$fieldPointer_p

Here is a sample call to "UTIL_GetFieldPointer":

$regUID_p:=UTIL_GetFieldPointer ("REGISTRATION";"uid")