4D Doc Center : SQL code error tracking and debugging

Home

4D v14

SQL code error tracking and debugging

In 4D, there are two main possibilities for tracing and correcting your code: either using the Debugger to trace and correct any errors, or calling the ON ERR CALL command to catch the error and initiate the appropriate action. We can use both of these techniques to solve problems encountered with the SQL code.
Here is an example where a right parenthesis is missing intentionally: instead of HAVING SUM(Sold_Tickets <:$MinSoldTickets), we have HAVING SUM(Sold_Tickets <:$MinSoldTickets.

 ARRAY LONGINT(aSoldTickets;0)

 ARRAY INTEGER(aMovieYear;0)

 C_LONGINT($MovieYear;$MinSoldTickets)

 $MovieYear:=1979

 $MinSoldTickets:=10000000

 

 Begin SQL

    SELECT Year_of_Movie, SUM(Sold_Tickets)

    FROM MOVIES

    WHERE Year_of_Movie >= :$MovieYear

    GROUP BY Year_of_Movie

    HAVING SUM(Sold_Tickets < :$MinSoldTickets

    ORDER BY 1

    INTO :aMovieYear,  :aSoldTickets;

 End SQL

As you can see in the window below, the application detects the error and opens the Syntax Error Window which provides more detailed information about the error and the place where it occurred. It is then easy to fix by simply pressing the Edit button.

If the error is more complex, the application provides more information including the stack content, which can be displayed by pressing the Details button.
To test the above example, in the main window of the "4D SQL Code Samples" database, press the Debugging SQL code button.

The second main possibility for tracking SQL errors is using the ON ERR CALL command.
Here is an example that sets the SQL_Error_Handler method to catch errors encountered in the SQL code.

 ARRAY LONGINT(aSoldTickets;0)

 ARRAY INTEGER(aMovieYear;0)

 C_LONGINT($MovieYear;$MinSoldTickets;MySQL_Error)

 $MovieYear:=1979

 $MinSoldTickets:=10000000

 MySQL_Error:=0

 

  ` Trigger the SQL_Error_Handler method to catch (trap) errors

 ON ERR CALL("SQL_Error_Handler")

 Begin SQL

    SELECT Year_of_Movie, SUM(Sold_Tickets)

    FROM MOVIES

    WHERE Year_of_Movie >= :$MovieYear

    GROUP BY Year_of_Movie

    HAVING SUM(Sold_Tickets < :$MinSoldTickets

    ORDER BY 1

    INTO :aMovieYear,  :aSoldTickets;

 End SQL

  ` Disable the SQL_Error_Handler method

 ON ERR CALL("")

 If(MySQL_Error#0)

    ALERT("SQL Error number: "+String(MySQL_Error))

 End if

The SQL_Error_Handler method is as follows:

  `(P) SQL_Error_Handler

 MySQL_Error:=Error

To test the above example, in the main window of the "4D SQL Code Samples" database, press the Using ON ERR CALL button.