How to Add A New SQL Error Diagnostic

This article is for Trafodion developers who need to add a new SQL error or warning to the Trafodion SQL engine.

Step-by-step guide

To create a new SQL error or warning diagnostic, do the following:

1. Pick a number for the SQL code for your new diagnostic. There are two considerations here:
   
   1. Determine the range that your new SQL code should fall within. See the discussion below.
   2. Pick a number that is not already in use. The SQL messages file core/sql/bin/SqlciErrors.txt has a line for every used SQL code, so check there.
2. Add your new SQL code to the SQL messages file.
3. For some parts of the code, we have enums that map to the SQL codes. If an enum header file exists for your SQL code, add it to the enum. See the discussion below for a list of the enum header files.
4. Add code where appropriate to raise the new diagnostic. Usually this involves creating a DgSqlCode object and inserting it into a ComDiagsArea using operator<<. Some components, such as the Executor, have their own macros that do this for you. Read the module where you are raising your new diagnostic to see what style to use.
5. Consider what parameters you might add to your diagnostic. Error messages can be parameterized to substitute integer or string values into the text. Certain named tokens such as table names, catalog names and schema names can be added as well. 
   1. Designate your parameter in the SQL messages file using syntax such as $0~Int0. This represents the first integer parameter passed to the message text.
   2. Put your parameter into the diagnostics area at run time. For the first integer parameter, for example, create a DgInt0 object with the parameter value and insert that into the ComDiagsArea using operator<<.
6. Then build and test your code to make sure it does what you think it does.
7. Add a description of your new diagnostic to the Trafodion SQL Messages Guide. The source for the Guide can be found in the trafodion/docs/messages_guide directory.
8. Then build the guide and proof-read your changes using a web browser.

The table below describes what number ranges are reserved for what parts of the code, and also points to the enum file for those number ranges if one exists.

Checking the Consistency of the SQL Message Text and the Trafodion Messages Guide

Unfortunately, in the early days of the Trafodion project, we were not very good at keeping the Messages Guide up-to-date. Some work has been done to bring them back into sync (see for example  Unable to render Jira issues macro, execution error. ). If you feel a calling to improve the Messages Guide, there is a script that can help you. In core/sqf/sql/scripts, the Python script analyzeMessageGuide.py compares the Messages Guide text to that in the code. It flags which messages don't match. It also gives details that are useful for researching the message to bring it back into sync. For example, it will tell you if the message is in the Guide but not in the messages file or vice versa. For messages that are in the messages file, it will tell you what enum file the message is defined in (if any) and tell you what modules reference that message (either by number or by enum). It will also tell you if there are regression tests that have examples of the message.

Related articles