Troubleshooting | SQL Error Codes: Pitfalls and Solutions

SQL or Structured Query Language is a programming language used in communicating with a database. Many database systems are based on SQL codes and we can use these codes to perform operations such as updating a database or retrieving data from a database.

And just like in any other programming language, SQL error codes can sometimes occur no matter how careful we are. However, dealing with an SQL error is not the hardest thing to do as the errors usually occur similarly. That is to say, if we are looking for how to fix a database error, then we will not need to look too far as a lot of people tend to make the same mistake thereby creating a vast pool of reference for us. We are going to learn how exactly we can fix SQL error codes. But before do, let us try to understand what SQL errors mean and what the types of errors are.

An Explanation of What SQL Errors Are and Types of Db2 Error Codes

An error occurs with a SQL code when the code returns a minus sign (-). This is usually to tell us that the execution of that code was unsuccessful. Using any Db2 SQL supported server has the unique feature of allowing us to connect or query from numerous database. While executing these databases, an error SQL may be returned due to many reasons including misspellings, wrong syntax, omitting a bracket amongst others. However, another advantage of running a database with Db2 computer programs is the ease with which any arising errors can be handled.

The most common types of SQL errors include:

• Syntax Error

Generally, a syntax involves the way a word or command is arranged. You will agree that when we try to write for people and we place a letter in the wrong place, it is always very hard for them to understand what we are trying to communicate. It is the same for executing SQL codes. Writing “FAST TRAKC” instead of “FAST TRACK” will throw back an error in the SQL return code.

• Misspelling Error

This type of error is similar to one above and we usually find that it is common amongst both new and experienced coders. It is sometimes caused by chubby fingers that tend to press more than one word at a time or trying to type too fast or even typing recklessly. Misspelling a command such as “FROM” in a program can return a SQL error message upon execution.

• Keyword Errors

The SQL as a query language has its own words that are used to give a command. These words or clauses are what we refer to as keywords. Misspelling the command by either omitting a letter or simply misplacing a letter can result in keyword errors. Words such as “UPDATE”, “SELECT”, “GROUP BY”, “FROM” “ORDER BY”, “WHERE”, “HAVING”, and “TABLE” are keywords that we cannot afford to missthem as it can easily give a Db2 SQL error.

• Quotation and Bracket Errors

We know how quotations and brackets are vital in programming. However, using they wrongly especially in SQL can return a SQL error message after we execute. Quotations usually work in pairs. And when we are dealiphrasesh a short straightforward clause or command, the proper quotes to use are the single quotes. When the string becomes longer with more words and phrase and there becomes a need to use double quotes, then we must place the double quotes within the single quotes. And where we use an apostrophe within the string, then we must place a second apostrophe next to the first one to avoid the program from reading the first apostrophe as the end of the command string. The rules with brackets, however, are not as many. Yet we must not also forget to use the brackets in pairs as omitting one can throw back an error message.

• Case-Sensitive Errors

These errors usually arise when we run our queries without considering that SQL is not usually sensitive to letter cases. When we have two separate database objects under the same name but with different casing. Say, for instance, we have one database object as BigFirm and the other as BIGFIRM, trying to write a query for BigFirm may give us the results from BIGFIRM as the program is not big on discriminating casing. This result will be wrong and hence an error.

How to Deal with These Db2 Error Codes

Dealing with Db2 error codes is relatively easy as the program already has a system for handling errors with each error throwing back a specific error messagetoal with the SQL error codes, we must first be able to spot them out. This is simply because identifying the problem will bring us a step closer towards fixing. There are three (3) common ways to handle Db2 error codes:

1. Using TRY…CATCH Function

With this function, we can write two blocks of codes, one for the TRY function and the other for the CATCH function. Then we will place the code we are trying to test for error in between the BEGIN TRY statemen,t and END TRY statement. The error, once identified in the TRY block of code will be passed down to the CATCH block where it either be fixed, reported or logged.

2. Using RAISEERROR Function

Translating and understanding a SQL error may prove a bit challenging for some. This is why we use the RAISEERROR function to create custom error messages that help translate confusing messages into something that makes sense even to a newbie. Creating a custom error message is especially useful when we are convinced that a particular type of error is likely to occur. For instance, say we are entering the names and ID of all the staff in an organization and we suspect that there is a chance that we could enter an invalid ID, then creating a custom error message that tells us explicitly that a particular ID does not exist would be a great way to deal with such an error.

3. Using TRANSACTION Function

The TRANSACTION function comes very handily when we are dealing with errors that occur in codes that involve transactions. The errors we deal with here are considered to be tricky and the process which involves using the TRY…CATCH function also includes wrapping a TRANSACTION function around an Insert statement.

Examples of SQL Errors

To give a better understanding of what a Db2 SQL error code, let us look at some examples of these errors.

Example 1. When we run a program that assigns 32,760 to Number variable and 32,770 to Result variable, we will get an error message since smallint holds numbers from -32,768 to 32,767 as Overflow. This will look like this:

SET @Number = 32760;
SET @Result = @Number + 10;

Running the above code along with the TRY…CATCH function will look like this:

BEGIN TRY
    DECLARE @Number smallint,
	    @Result smallint;
    SET @Number = 32760;
    SET @Result = @Number + 10;
SELECT @Number AS Number, 
       @Result AS Result;
END TRY
BEGIN CATCH
  PRINT N'Error Message = ' + ERROR_MESSAGE();
END CATCH

The resulting error would appear like this:

Error Message: Arithmetic overflow error for the data type smallint, value = 32770

Example 2. When we misspell a word or command such as FROM like in the code below:

SELECT *

FORM dish

WHERE NAME = ‘Prawn Salad’;

We will end up with an error message like this one:

Syntax error in SQL statement "SELECT * FORM[*] dish WHERE NAME = 'Prawn Salad';"; 
SQL statement: SELECT * FORM dish WHERE NAME = 'Prawn Salad'; [42000-176]

Example 3. Whenever we forget to use a complete pair of brackets as in the query below:

SELECT *

FROM artist

WHERE first_name = ‘Vincent’ and (last_name = ‘Monet’ or last_name = ‘Da Vinci’;

The result would be an error message like the one below:

ERROR: syntax error at or near ";" Position: 102

Solutions and Tips for Db2 Error Codes

When you are looking for solutions and tips for dealing with SQL errors, the best advice anyone navigates gives you is “never stop practicing. Practice, practice, and practice some more.” If you are always practicing, you will find it a lot easier navigating through the pitfalls of SQL. Meanwhile, here are a few solutions and tips to guide you.

• Fmisspelledellings and other errors that involve using the wrong word or command, always take advantage of the highlighting feature of the SQL editor. Any misspelt word or command will not be highlighted. When we see that a keyword is not highlighted to look light purple and is still the same color as other arguments (black), then we know there is a problem.
• Remember to always use quotations and brackets in pairs. Single quotations are usually the preferred choice and in queries where you have to use both quotations, always use the double quotations within the single quotations.
• Avoid using names for an object if you can and when you specifically have to use a name for a table, be sure you use double quotes around that name. Or separate it to make it a phrase rather than a single word.

Conclusion

Many developers consider SQL to be both an interesting and straightforward programming language. And although they are easy to find and fix, SQL error codes occur all the time. The errors discussed here are the ones we consider to be very common. There are many others and we plan to discuss them as well as provide you with more information in subsequent articles. You can subscribe to join our email newsletter so that you never have to miss any part of this. Also, if you think you have gained anything from this article then be sure to share it with your friends as well.