KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Using the 4D Pasteboard as an SQL debugging aid
PRODUCT: 4D | VERSION: 11.5 | PLATFORM: Mac & Win
Published On: December 18, 2009
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

When writing a complex SQL statement, SET TEXT TO PASTEBOARD and Get text from pasteboard can be a great debugging aid.

For dynamic SQL the typical solution is to build an SQL statement into a string and then use:

Begin SQL
    EXECUTE IMMEDIATE :$SQL_T;
End SQL


to execute the statement. If you get an SQL parsing error it can be very difficult to tell just where the statement is in error.

For easy debugging try the following:

  1. Place the SQL statement on the pasteboard: SET TEXT TO PASTEBOARD($SQL_T)

    This would also be a good place to use the utility from Tech Tip "Using the 4D v11 SQL Pasteboard as a Debugging aid".

  2. Paste the SQL statement into a text editor that recognizes SQL code, such as BBEdit, Coda, or TextMate on the Mac and TextPad or Notepad++ on Windows.

  3. Format the SQL statement for easy understanding, do the necessary editing, and copy the text the system clipboard.

  4. Use the following code to test the SQL:

    $SQL_T:= Get text from pasteboard
    Begin SQL
        EXECUTE IMMEDIATE :$SQL_T;
    End SQL

Once the query performs as expected, use the template created in the text editor to edit the code in 4D to produce the same statement.