Tech Tip: Method detecting invalid sql names

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

Published On: November 3, 2011

There are a number of different rules concerning what constitutes as a valid SQL name. Since the SQL name rules are different from the 4D naming rules it is possible for existing databases to contain fields whose names are not SQL compatible.

Because of this it would be usefull if there was a programatic way to find all instances within a database of field names that are not SQL comatible. Here is a utility method that will determine whether a specified name is valid per 4D's implementation of SQL.

// ----------------------------------------------------
// Method: UTIL_IsNameValid
// Description - Detects whether a given name is compatible with 4D's
//      SQL implementation.
//
// Parameters
// $1 (Text) - The name to check
//
// Returns
// $0 (Boolean) - True if the name is valid, False if the name is not valid.
// ----------------------------------------------------

C_BOOLEAN($0;$isValid_f)

C_TEXT($1;$nameToCheck_t)
C_TEXT($sql_t;$tblName_t)

$nameToCheck_t:=$1
$tblName_t:="temp_"+String(Random)

$sql_t:="CREATE TABLE "+$tblName_t+" ("+$nameToCheck_t+" VARCHAR)"

ON ERR CALL("SQL_ERR_HANDLER")
SQL_ERROR:=0

Begin SQL
  EXECUTE IMMEDIATE :$sql_t;
End SQL

If (SQL_ERROR#0)
   //name is invalid
$isValid_f:=False
Else
   //name is valid
$isValid_f:=True

   //cleanup: drop the temp table
$sql_t:="DROP TABLE "+$tblName_t
Begin SQL
   EXECUTE IMMEDIATE :$sql_t;
End SQL

End if

ON ERR CALL("")

$0:=$isValid_f

Note that rather than attempt to programatically test each specific rule, this method goes straight to trying out the name in a SQL statement. An error handler is installed so that we can programatically detect if an error occurs while executing the SQLstatement. If an error does occur, then we know the name is invalid.

So here is an example of how to use this method:

C_BOOLEAN($valid_f)
$valid_f:=UTIL_IsValidSQLName ("INSERT NAME HERE")

Here is a more interesting and useful example. The following method goes through all the tables and fields of a database and builds an array of every name that is not a valid SQL name.

C_LONGINT($currTbl_l;$currFld_l;$maxFldCt_l;$maxTblNum_l)
C_BOOLEAN($valid_f)
C_TEXT($currTblName_t;$currFldname_t)

ARRAY TEXT($invalidNames_at;0)

//iterate through all Tables
$maxTblNum_l:=Get last table number
For ($currTbl_l;1;$maxTblNum_l)
If (Is table number valid($currTbl_l))
    //check if the Table name is valid
  $currTblName_t:=Table name($currTbl_l)
  $valid_f:=UTIL_IsValidSQLName ($currTblName_t)
  If (Not($valid_f))
   APPEND TO ARRAY($invalidNames_at;"Invalid Table name: "+$currTblName_t)
  End if
    //iterate through all Fields
  $maxFldCt_l:=Get last field number(Table($currTbl_l))
  For ($currFld_l;1;$maxFldCt_l)
   If (Is field number valid($currTbl_l;$currFld_l))
      //now check if the Field name is valid
    $currFldname_t:=Field name($currTbl_l;$currFld_l)
    $valid_f:=UTIL_IsValidSQLName ($currFldname_t)
    If (Not($valid_f))
     APPEND TO ARRAY($invalidNames_at;\
       "Invalid Field name: "+$currTblName_t+": "+$currFldname_t)
    End if
   End if
  End for
End if
End for

Once this method executes, the array $invalidNames_at will contain the invalid SQL names within the database. Here is an example of what the array woudl contain:

Invalid Table name: Table 4
Invalid Field name: Table_3: name with spaces
Invalid Field name: Table_3: punctuation*
Invalid Field name: Table_3: Sum
Invalid Field name: Table_2: False
Invalid Field name: Table_1: escape