KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Building dynamic SQL queries
PRODUCT: 4D Developer | VERSION: 11 | PLATFORM: Mac & Win
Published On: November 14, 2007
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

4D v11 SQL provides the ability to build dynamic SQL queries via the EXECUTE IMMEDIATE command. For example, you might want to execute the same SELECT statement on more than one table. In "pure" SQL this would need to be written as two different statements:

Begin SQL
   SELECT * FROM Table_1 INTO :mylistbox
End SQL

Begin SQL
   SELECT * FROM Table_2 INTO :mylistbox
End SQL


This can be made dynamic by using EXECUTE IMMEDIATE:

C_TEXT($SQL)
C_TEXT($tableName;$1)
$tableName:=$1
$SQL:="SELECT * FROM "+$tableName+" INTO :mylistbox"
Begin SQL
   EXECUTE IMMEDIATE :$SQL
End SQL


With this technique you are not limited to only executing static SQL statements.