Tech Tip: Replacement Method for AP Add Table and Fields

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

Published On: October 26, 2020

The 4D Pack Plugin is no longer supported and has been obsolete since v16.

Below is a method that can be used to replace the AP Add Table and Fields plugin method:

// Method: AP_AddTableandFields
// Description
// Replaces AP Add Table and fields to create
// a table and its fields
//
// Parameters
// $0 - Error Code, 0 is no errors, -1 if parameter count does not match
//
// $1 - Name of table
// $2 - Pointer to text array of field names
// $3 - Pointer to longint array of field types
// $4 - Pointer to longint array of alpha length or 0 if not alpha
// ----------------------------------------------------

C_LONGINT($0)

C_TEXT($1)
C_POINTER($2;$3;$4)

C_TEXT($tblName_t)
C_TEXT($sql_stmt_t)
C_LONGINT($i)
ARRAY TEXT($fieldname_as;0)
ARRAY LONGINT($fieldtype_al;0)
ARRAY LONGINT($fieldlength_al;0)

If (Count parameters=4)
   ON ERR CALL("AP_AddTableandFields")
   $0:=0

   $tblName_t:=$1
   COPY ARRAY($2->;$fieldname_as)
   COPY ARRAY($3->;$fieldtype_al)
   COPY ARRAY($4->;$fieldlength_al)

   $sql_stmt_t:="CREATE TABLE "+$tblName_t+"( "
   For ($i;1;Size of array($fieldname_as))
     $sql_stmt_t:=$sql_stmt_t+$fieldname_as{$i}+" "
     Case of
     : ($fieldtype_al{$i}=Is alpha field)
       $sql_stmt_t:=$sql_stmt_t+"VARCHAR("
       $sql_stmt_t:=$sql_stmt_t+String($fieldlength_al{$i})+")"

     : ($fieldtype_al{$i}=Is text)
       $sql_stmt_t:=$sql_stmt_t+"TEXT"

     : ($fieldtype_al{$i}=Is real)
       $sql_stmt_t:=$sql_stmt_t+"REAL"

     : ($fieldtype_al{$i}=Is integer)
       $sql_stmt_t:=$sql_stmt_t+"INT16"

     : ($fieldtype_al{$i}=Is longint)
       $sql_stmt_t:=$sql_stmt_t+"INT"

     : ($fieldtype_al{$i}=Is integer 64 bits)
       $sql_stmt_t:=$sql_stmt_t+"INT64"

     : ($fieldtype_al{$i}=Is date)
       $sql_stmt_t:=$sql_stmt_t+"TIMESTAMP"

     : ($fieldtype_al{$i}=Is time)
       $sql_stmt_t:=$sql_stmt_t+"DURATION"

     : ($fieldtype_al{$i}=Is boolean)
       $sql_stmt_t:=$sql_stmt_t+"BOOLEAN"

     : ($fieldtype_al{$i}=Is picture)
       $sql_stmt_t:=$sql_stmt_t+"PICTURE"

     : ($fieldtype_al{$i}=Is BLOB)
       $sql_stmt_t:=$sql_stmt_t+"BLOB"

     End case
     If ($i<Size of array($fieldname_as))
       $sql_stmt_t:=$sql_stmt_t+","
     End if
   End for
   $sql_stmt_t:=$sql_stmt_t+");"

   Begin SQL
     EXECUTE IMMEDIATE :$sql_stmt_t;
   End SQL

   ON ERR CALL("")

   If (Error#0)
     ARRAY LONGINT($code_al;0)
     ARRAY LONGINT($intComp_al;0)
     ARRAY TEXT($detail_at;0)

     GET LAST ERROR STACK($code_al;$intComp_al;$detail_at)
     $0:=$code_al{Size of array($code_al)}
   End if
Else
   $0:=-1
End if

The method can then be used to replace the plugin call while keeping the parameters, but referencing them with a pointer.
For example:

$res_l:=AP Add table and fields ($tblName_t;$fieldname_at;$fieldtype_al;$fieldLength_al)

Can be converted to:

$res_l:=AP_AddTableandFields ($tblName_t;->$fieldname_at;->$fieldtype_al;->$fieldLength_al)