4D v19
SQL code error tracking and debugging
- 4D SQL Reference
-
- Tutorial
-
- Introduction
- Receiving an SQL query result in a variable
- Using the WHERE clause
- Receiving an SQL query result into arrays
- Using CAST
- Using the ORDER BY clause
- Using the GROUP BY clause
- Using Statistical functions
- Using the HAVING clause
- Calling 4D methods inside the SQL code
- Joins
- Using Aliases
- Subqueries
- SQL code error tracking and debugging
- Data Definition Language
- External connections
- Connection to the 4D SQL engine via the ODBC Driver
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 SQLAs 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 ifThe SQL_Error_Handler method is as follows:
`(P) SQL_Error_Handler
MySQL_Error:=ErrorTo test the above example, in the main window of the "4D SQL Code Samples" database, press the Using ON ERR CALL button.
Product: 4D
Theme: Tutorial
4D SQL Reference ( 4D v19)
