Tech Tip: Do not use Local Variables and EXECUTE IMMEDIATE in SQL with a Compiled DB

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

Published On: April 16, 2009

When building dynamic SQL code, it is common and convienient to build an SQL statement in a string and then pass that string off to the SQL command EXECUTE IMMEDIATE.

Consider the code below:

C_TEXT($SQL_T)
C_LONGINT($TmpCount_L)
$SQL_T:="SELECT COUNT(*)"
$SQL_T:=$SQL_T+" FROM "+Table name(EventTable_ptr)
$SQL_T:=$SQL_T+" WHERE "+Field name(UserIDFld_ptr)+" = '"+UserIDs_aT{$Ndx}+"'"
$SQL_T:=$SQL_T+" INTO :$TmpCount_L;"
Begin SQL
EXECUTE IMMEDIATE :$SQL_T;
End SQL

The above code works 100% of the time if the database is run in interpreted mode. But as soon as you compile it, you will get a SQL error telling you that $TmpCount_L is unknown.

To run the code in either interpreted or compiled, change the variable $TmpCount_L to a process variable, TmpCount_L. See the code below. This is not a bug but standard behavior that developers need to be aware of.

C_TEXT($SQL_T)
C_LONGINT(TmpCount_L)
$SQL_T:="SELECT COUNT(*)"
$SQL_T:=$SQL_T+" FROM "+Table name(EventTable_ptr)
$SQL_T:=$SQL_T+" WHERE "+Field name(UserIDFld_ptr)+" = '"+UserIDs_aT{$Ndx}+"'"
$SQL_T:=$SQL_T+" INTO :TmpCount_L;"
Begin SQL
EXECUTE IMMEDIATE :$SQL_T;
End SQL

Commented by Brian Campbell on September 25, 2010 at 3:53 PM

As of 11.7 (perhaps prior), it appears that the issue using local variables in a BEGIN SQL/END SQL block has been resolved. The code in the note by Luis on 6/19/2009 should now execute as expected.

I did discover problems when invisible characters were imbedded inside the BEGIN SQL/END SQL block. It appears that these were introduced by the code editor in a prior version of v11 SQL. Removing these invisible characters with an outside text editor resolves the Unknown Variable issues under 4D 11.7.

Commented by Luis on June 12, 2009 at 9:24 AM

In general you should avoid using local variables as part of SQL statements.
The following code will display the expected results in an interpreted database, but not in a compiled database:

BeginSQL
SELECT Company_Name, Company_Status
FROM Companies
WHERE Company_ID = <<$CompanyID>>
ORDER BY Company_Name, Company_Status DESC
INTO <<◊LB_SQLResults>>
End SQL