Tech Tip: How to get a Pointer to a Table 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 table using SQL. This can be useful for writing generic code but, more specifically, components do not have direct access to host database tables; pointers are required when using DB4D code from a component.

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

In order to get a pointer to a table using the Table command, the table number is required. An easy way to acquire a table number based on the table name is to use the SQL System Table "_USER_TABLES".

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

C_TEXT($1;$tableName_t)
C_POINTER($0;$tablePointer_p)

C_LONGINT($tableNumber_l)

$tableName_t:=$1

Begin SQL
SELECT TABLE_ID
FROM _USER_TABLES
WHERE TABLE_NAME = :$tableName_t
INTO :$tableNumber_l;
End SQL
$tablePointer_p:=Table($tableNumber_l)

$0:=$tablePointer_p

Here is a sample call to the method "UTIL_GetTablePointer":

$registration_p:=UTIL_GetTablePointer ("REGISTRATION")