SQL Errors – What a Difference a Comma Makes

Errors that can be encountered while using SQL include errors that occur as a result of issues with punctuation, operations that go beyond set parameters and constraints, invalid use of reserved words, logic issues, missing information, etc. Variations in errors can also be based on the version of SQL being used. Due to the varied selection of errors, this article will focus on some of the errors that you may come across while using SQL. In order to understand why an error occurred and how to prevent it, it is helpful to be able to decipher what an error code or message indicates. If you have recently started using SQL, then you may come across multiple errors; moreover, the same could be said about well experienced SQL users.

Have you considered taking a class in order to learn more about concepts, such as parameters and logical data structures, in regards to Oracle database administration?  If so, then explore a class on Udemy. 

Punctuation

In regards to punctuation errors; syntax errors can often involve errors in, what may seem to be, basic punctuation errors. For instance; if a comma is left out or misplaced, a period or space is added where it should not be, or a word used for a programming object is not correctly used in relation to the order of other objects, then a syntax related error could be presented. To demonstrate how syntax errors could occur, I have included the following examples:

In this scenario, I’m attempting to create a table called Employee_Tbl, which has the following columns: Employee_Id, Employee_First, Employee_Last, and Employee_dept. I’m receiving an error indicating that an error in syntax has occurred. The error message for syntax errors can differ based on factors such as version of SQL being used and the root cause of the error. The error message that I had received in this case, stated the following: “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Employee_First varchar(20), Employee_Last varchar(20), Employee_Department int(‘ at line 4:” and had been based on syntax expectations for MySQL. The following is what I had typed in an attempt to create my table:

create table Employee_Tbl
(
Employee_Id number(5)
Employee_First varchar(10),
Employee_Last varchar(10),
Employee_Department number(5)
);
insert into Employee_Tbl values
(1475, 'Doyle', 'Clayton', 01410);
insert into Employee_Tbl values
(1476, 'Wilbert', 'Holmes', 02420);
insert into Employee_Tbl values
(1477, 'Kristen', 'Walters', 03410);
insert into Employee_Tbl values
(1478, 'Catherine', 'Mckinney', 04420);
insert into Employee_Tbl values
(1479, 'Adam', 'Wade', 05430);
insert into Employee_Tbl values
(1545, 'Miranda', 'Morgan', 06430);
insert into Employee_Tbl values
(1546, 'Elisa', 'Wolfe', 07410);
insert into Employee_Tbl values
(1547, 'Amber', 'Miles', 08430);
insert into Employee_Tbl values
(1548, 'Aimee', 'Green', 09420);
insert into Employee_Tbl values
(1549, 'Ken', 'Norton', 10430);
insert into Employee_Tbl values
(1630, 'Sean', 'Taylor', 10530);
insert into Employee_Tbl values
(1631, 'Erma', 'Snyder', 10610);
insert into Employee_Tbl values
(1632, 'Hugh', 'Elliot', 10720);
insert into Employee_Tbl values
(1633, 'Pete', 'Jones', 10810);
insert into Employee_Tbl values
(1634, 'Connie', 'Grant', 10910);
insert into Employee_Tbl values
(1635, 'Josephine', 'Sullivan', 20420);

While looking at the code above, the issue may not be immediately obvious. I have created the table with all four columns and had used the correct datatype for each column. I had inserted all of the values in the correct order in respect to the columns I had created, I had placed single quotation marks around the characters (names), and I had added semi-colons to the end of the Create Table statement as well as at the end of each Insert statement. Upon looking closer, a missing comma had been found to be the cause of this syntax error (after the Employee_Id column within the Create Table statement). After adding the missing comma, the Schema had been built for the table, and the table had been created (as seen below).

EMPLOYEE_ID EMPLOYEE_FIRST EMPLOYEE_LAST EMPLOYEE_DEPARTMENT

1475 Doyle Clayton 1410

1476 Wilbert Holmes 2420

1477 Kristen Walters 3410

1478 Catherine Mckinney 4420

1479 Adam Wade 5430

1545 Miranda Morgan 6430

1546 Elisa Wolfe 7410

1547 Amber Miles 8430

1548 Aimee Green 9420

1549 Ken Norton 10430

1630 Sean Taylor 10530

1631 Erma Snyder 10610

1632 Hugh Elliot 10720

1633 Pete Jones 10810

1634 Connie Grant 10910

1635 Josephine Sullivan 20420

If one omits a comma that should have been in an Insert statement in Oracle, then they could receive an ORA-00917 error code. For the next case, consider that I am using MySQL and I receive an error code 1064 while attempting to use the following Select statement to select all columns from the Employee_Tbl where the Employee_First column contains the field value of “Doyle”:

SELECT * FROM EMPLOYEE_TBL WHERE EMPLOYEE_FIRST = ‘Doyle;

In the example above, the cause of the error had been a missing single quote after Doyle. After adding the single quote and running the query again, the correct query result had been displayed as follows:

EMPLOYEE_ID EMPLOYEE_FIRST EMPLOYEE_LAST EMPLOYEE_DEPARTMENT

1475 Doyle Clayton 1410

Truncation and Implicit Conversion

When a field value is inadvertently truncated, it has a portion of its components removed as a result of a limitation set for its field. In SQL, there is also a Truncate Table statement that can be used in cases such as when a table should be removed. When an error message related to truncation is received; it can often be due to data entered for a column field being treated as incorrect data, by reason of an invalid format or too many characters, based on restrictions set for the column for which the data had been entered.

Implicit conversion involves the datatype of a field being converted “behind the scenes” as an attempt for data to be automatically converted to a datatype that is compatible with the restrictions or parameters set for column data. If data is not correctly converted, then an error can occur. As an example, when using the Create Table statement displayed below, an error message including the statement, “Data truncation: Incorrect date value”, had been received. Likewise, an issue with the conversion of data in Oracle could result in an error code of ORA-01858.

Feel free to try to identify what may have triggered an error involving truncation in the following code:

create table Employee_Tbl
(
Employee_Id int(5),
Employee_First varchar(10),
Employee_Last varchar(10),
Employee_Department int(5),
Hire_Date date
);
insert into Employee_Tbl values
(1475, 'Doyle', 'Clayton', 01410, '01-JAN-2012');
insert into Employee_Tbl values
(1476, 'Wilbert', 'Holmes', 02420, '01-JAN-2012');
insert into Employee_Tbl values
(1477, 'Kristen', 'Walters', 03410, '01-MAY-2012');
insert into Employee_Tbl values
(1478, 'Catherine', 'Mckinney', 04420, '01-JAN-2014');
insert into Employee_Tbl values
(1479, 'Adam', 'Wade', 05430, '01-JAN-2014');
insert into Employee_Tbl values
(1545, 'Miranda', 'Morgan', 06430, '01-JAN-2012');
insert into Employee_Tbl values
(1546, 'Elisa', 'Wolfe', 07410, '01-APR-2014');
insert into Employee_Tbl values
(1547, 'Amber', 'Miles', 08430, '01-JAN-2012');
insert into Employee_Tbl values
(1548, 'Aimee', 'Green', 09420, '01-MAY-2014');
insert into Employee_Tbl values
(1549, 'Ken', 'Norton', 10430, '01-JAN-2012');
insert into Employee_Tbl values
(1630, 'Sean', 'Taylor', 10530, '01-JAN-2012');
insert into Employee_Tbl values
(1631, 'Erma', 'Snyder', 10610, '01-MAY-2012');
insert into Employee_Tbl values
(1632, 'Hugh', 'Elliot', 10720, '01-JAN-2014');
insert into Employee_Tbl values
(1633, 'Pete', 'Jones', 10810, '01-JAN-2012');
insert into Employee_Tbl values
(1634, 'Connie', 'Grant', 10910, '01-JAN-2014');
insert into Employee_Tbl values
(1635, 'Josephine', 'Sullivan', 20420, '01-MAY-2014');

After changing the datatype to varchar for the Hire_Date column, the table had been created.

create table Employee_Tbl
(
Employee_Id int(5),
Employee_First varchar(10),
Employee_Last varchar(10),
Employee_Department int(5),
Hire_Date varchar (20);
)

You can see that the date appears to be displayed correctly.

SELECT * FROM Employee_Tbl WHERE Employee_Id = 1475;

EMPLOYEE_ID EMPLOYEE_FIRST EMPLOYEE_LAST EMPLOYEE_DEPARTMENT HIRE_DATE

1475 Doyle Clayton 1410 01-JAN-2012

Reserved Words

As with other programming languages, SQL has words that are designated as being Reserved Words. Reserved Words in programming languages are words that are somewhat retained or protected from being able to be used out of a predefined context. As in the case with other aspects, Reserved Words can possibly differ from each version of SQL. There is an abundance of Reserved Words in SQL; examples of some of the words include the following: where, update, union, rows, select, output, module, and current. Using a Reserved Word in a manner that is unintended for the word is also classified as a syntax error and can result in the receiving of error code 1064 in MySQL or error code ORA-01747 in Oracle.

Suppose that instead of the first column for the Employee_Tbl table being named Employee_Id, that the column had been named Insert.

create table Employee_Tbl
(
Insert int(5), - -using a reserved word
Employee_First varchar(10),
Employee_Last varchar(10),
Employee_Department int(5),
Hire_Date varchar(20)
);

An error message would be displayed as using Reserved words can not only result in errors but can also result in requests, such as those involved with object creation or queries, being rejected.

Contraints

Constraints may be described as being restrictions, or required guidelines, set for specific fields. In many cases; restrictions are set in order to only allow defined parameters to be used. Parameters that may be set include those associated with the following: ranges of data, types of data, uniqueness of data, level of modifications allowed, etc. For instance; if a constraint has been set on a column to disallow null values, then attempting to complete tasks for the column when some field values contain nulls, can result in an error.

Can you identify the reason that following code should result in an error when it is used in an attempt to create and add values to a table?

create table Employee_Tbl
(
Employee_Id int(5) NOT NULL,
Employee_First varchar(10),
Employee_Last varchar(10),
Employee_Department int(5),
Hire_Date varchar(20)
);
insert into Employee_Tbl values
(1475, 'Doyle', 'Clayton', 01410, '01-JAN-2012');
insert into Employee_Tbl values
(1476, 'Wilbert', 'Holmes', 02420, '01-JAN-2012');
insert into Employee_Tbl values
(1477, 'Kristen', 'Walters', 03410, '01-MAY-2012');
insert into Employee_Tbl values
(1478, 'Catherine', 'Mckinney', 04420, '01-JAN-2014');
insert into Employee_Tbl values
(1479, 'Adam', 'Wade', 05430, '01-JAN-2014');
insert into Employee_Tbl values
(1545, 'Miranda', 'Morgan', 06430, '01-JAN-2012');
insert into Employee_Tbl values
(1546, 'Elisa', 'Wolfe', 07410, '01-APR-2014');
insert into Employee_Tbl values
(null, 'Amber', 'Miles', 08430, '01-JAN-2012');
insert into Employee_Tbl values
(1548, 'Aimee', 'Green', 09420, '01-MAY-2014');
insert into Employee_Tbl values
(1549, 'Ken', 'Norton', 10430, '01-JAN-2012');
insert into Employee_Tbl values
(1630, 'Sean', 'Taylor', 10530, '01-JAN-2012');
insert into Employee_Tbl values
(1631, 'Erma', 'Snyder', 10610, '01-MAY-2012');
insert into Employee_Tbl values
(1632, 'Hugh', 'Elliot', 10720, '01-JAN-2014');
insert into Employee_Tbl values
(1633, 'Pete', 'Jones', 10810, '01-JAN-2012');
insert into Employee_Tbl values
(1634, 'Connie', 'Grant', 10910, '01-JAN-2014');
insert into Employee_Tbl values
(1635, 'Josephine', 'Sullivan', 20420, '01-MAY-2014');

The reason that the code above would result in an error is because the Employee_Id column has a Not Null constraint added to it and the field for Amber Miles has a null value in the Employee_Id column. Interested in learning more about relational databases and datatypes? Consider completing a tutorial on Udemy.

Inconsistent Code

There may even be some cases in which an operation is completed without error, but that it does not yield the correct or expected results. Issues with logic in code can cause errors or simply result in output that is not beneficial. For instance, consider that is a known fact that the Employee_Tbl that we had used earlier should not have any employees in more than one department. If the following query is used, then it would result in data from the table not being returned:

SELECT * FROM Employee_Tbl
WHERE Employee_Department = 01410
AND Employee_Department = 02420;

Transactional and Network Errors

Errors that may be more commonly handled by database administrators include SQL server related errors. An example of a network related error code is 1602; which may be caused by various issues, such as a connection from a database to a network being unavailable. Transactions involve processes, such as those for stored procedures and routines. Processes for transactions should enable a transaction to begin, continue, and end. If an error occurs during a transaction; in some cases, the transaction will not complete. Administrators use methods for error handling, which may include the implementation of Try…Catch Constructs. Try…Catch Contructs can be set to automatically identify characteristics or errors, perform actions based on the potential error identified, and retrieve information for the error.

Error Logs

Error logs are stored files containing recorded information for errors and potential errors. In order for events associated with outlined conditions to be designated as possible errors, settings can be implemented in order to define what types of events should trigger alerts. It is a good practice for error logs to be monitored. If an error occurs, then an administrator or other authorized database user, should research the error in order to determine its root cause; level of severity; and which database objects are associated with the error. Error log monitoring can help with preventing ongoing issues and preventing data loss. If you wish to learn how to delve into error logs as well as learn about how data can be preserved and recovered, then inclusive tutorials found on Udemy could prove to be very worthwhile.

Conclusion

Errors can be encountered by users, administrators, and programmers. There are varying characteristics for errors which may involve severity level, associated events, corresponding codes, and locations for logs. Understanding what an error code or message is indicating can help with correcting and/or preventing the error. When learning the basics of SQL or mastering SQL administration, errors can often provide the clues needed in order to complete tasks and maintain database operations.