4D v13.4

SQL code error tracking and debugging

Home

 
4D v13.4
SQL code error tracking and debugging

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.

 
PROPERTIES 

Product: 4D
Theme: Tutorial