Many programmers hit a brick wall in their SQL queries, slowing development to a crawl and causing an over abundance of hair loss and coffee consumption. If you have been staring at the screen with a blank look and a pounding heart, wondering what you did to deserve this mess, you have come to the right place. In this article I will unveil the six most common SQL query errors and give you tips on stress prevention.
Since the Structured Query Language (SQL) is used to access all three commonly used website databases; mySQL, SQL and Access, I have chosen the errors that are most common to all, rather than snub a database and cause an influx of complaints. (wink)
Fingers A’Flyin
The most common SQL errors occur from typing mistakes. Take note of the word structured’ in Structured Query Language. They weren’t kidding, SQL wants its statements written according to syntax, with no exceptions. Think of SQL as the Prima Donna of all database languages.
If you receive a SQL error when testing your program, the first thing you should ALWAYS do is print that query to the page before you attempt to execute it in the code. Get a clean cloth, clean your glasses, wipe the dust off the monitor, and peer closely at the screen. Are the commas where the commas belong? Are you missing an ending tick when setting a column equal to a string data type? Is the entire query there, or did you perhaps forget to combine your statement lines?
Tic Tack Quote
Most often, I tend to get sidetracked and forget to add the tic marks (single quotes) around my strings properly, sometimes neglecting them all together. Another possibility is using double quotes inside the string where they do not belong.
For example, your query should look like this:
-==-
It should NOT look like this:
-==-
If you write a query like the second one, the error returned is, “Expected end of statement.” SQL feels sorry for you and gives you an exact location of the first double quote that doesn’t belong in your string. If you try leaving out the ending quote mark in your statement, you will get a patronizing, “Unterminated string constant” error message.
Type your SQL statements slowly and reference your database chart often. Take your time with your statements now, or spend it later debugging.
Out To Lunch
Let’s not forget the programmer’s tendency to attempt to collect column data from the wrong table, asking SQL to get the UserName from the Products table. Before you run off and try that one, I can tell you now that it doesn’t work. (grin) SQL gets really testy with me when I do that.
SQL likes to be vague about this error, telling you “No value given for one or more required parameters.” If you see this error, check your column name spellings and table name. Most often, one of them has been misspelled.
The best way to avoid this mistake is to create a database chart for your current program and put it right next to your keyboard. Refer to the chart when writing your queries and this error will become much less common in your program.
Apples and Oranges
Data type errors are also extremely common when working with databases. Often, a programmer will attempt to insert letters or symbols into a column that has been set to contain numeric data, or vice versa. SQL is very forgiving about this error, nicely telling you there is a “Data type mismatch in criteria expression.”
Avoiding this error is a simple matter of referring to your database chart or schematic when writing your queries. I cannot stress the importance of a detailed database chart enough when it comes to programming with a database.
Size Does Matter
When creating a table, the most often ignored setting for the column is the size property. Good database planning before creating your tables will prevent you from seeing this error message, “The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.” This message means exactly what it says, you can’t shove 25 characters into a column you set up to hold a maximum of 10.
Spend some time researching before creating your database charts. What type of data do you expect to be entered? Is there a maximum length a certain field should hold? A minimum length? Should the field be allowed to be empty, or is that column holding data that another part of your program MUST have to function properly?
Play it Again, Sam
The primary key of a database is a unique column in the database that ensures all rows remain separate. They are unique handles used to grab data that otherwise might be too similar. There might be ten Don Johnson’s in your database, but only one of them is Don Johnson with the ID number of 145. A primary key, or ID number cannot be duplicated. The integrity of your data depends on it, and SQL will tell you that it “Cannot update ‘User_ID’; field not updateable” if you even think about it.
Pest Control
If all else fails and your database query has more bugs than a Sunday picnic, pull out everything but the SQL statement, set each value in the statement without pulling info from the querystring or a form post, then slowly replace each variable until you find the cause of the error.
Rappin’ It Up
SQL is a database programming language most of us learn by trial and error. We all begin with a less than complete understanding of how to write our queries, leaning on more experienced programmers in our times of desperation.
To get off to a good start and prevent SQL errors from rearing their ugly heads:
- Think Ahead – Plan your database carefully, allowing enough room for the data you intend it to hold.
- Create a Database Chart – Reference your chart while writing all programming code.
- Don’t Rush – Watch your typing when writing SQL queries.
If you still find yourself in over your head, or tearing out your hair in frustration, email me with your SQL query question. If I don’t know the answer, I will find someone who does!
Monique Angelich is a successful freelance Internet Programmer, Software
Engineer, and Web Designer specializing in helping companies use the
Internet as their portal to success. You can visit the Devedia family of
websites at http://www.devedia.com. Email Monique directly at dev@devedia.com.
