Reserved Words
Posted: Thu 25 January, 2007 Filed under: Geeky, Own Business, Sweary 5 Comments »On occasion, I really hate SQL. (This is a geeky post. Don’t bother reading on if you’ve no interest in databases or PHP/SQL)
Over the last couple of weeks, I’ve been working on a site for a fund-raising organisation, who want to keep track of grants given to them. So part of the database was, logically, a table called ‘grant’ which held the details for a grant.
However, trying to enter data into that table resulted in “an error in your SQL syntax has occurred”. Not the most helpful of messages, particularly when I checked, checked, and rechecked, and the SQL was fine. In fact, for proof, the SQL’s here…
INSERT INTO grant SET grant_id=NULL, grant_donor_id=10, grant_organisation_id=1
And the problem? Of course, it’s not a syntax error per se, it’s actually an error in the fact that I’ve named a table the same as an SQL reserved word, so SQL is getting all confused.
But does it send back an error saying “Illegal word” or “You have used a reserved word” ? Does it bollocks.
“Syntax error” and that’s it.
So yes, on occasion, I really hate SQL.
Tut tut – poor naming conventions if you ask me. But you probably aren’t.
Asking me, that is.
Ah, it’s not a bad naming convention – GRANT is used for all the user permissions and so on, which I hadn’t remembered at the time.
But definitely bad error-handling – I can think of many messages that’d be more useful than “Syntax Error” – reminds me of early BASIC, and its bizarre and unhelpful error messages.
True enough, it is a poor error message. Had one of my own this week – client rang up to say they were getting the message “Cannot access property used by newer version of application” or similar. Looked up in the knowledge base and the solution has absolutely nothing to do with versions.
Re: naming conventions – I’m just surprised you don’t use prefixes on tables (if your tablename given is an example) to makesure they’re unique and unlikely to clash with reserved words.
Ah, see what you mean.
Yeah, I could do that – but when I know the entire site, and what’s going in to it, sometimes I just don’t bother. I should, but I don’t. So I suppose you could also lay some of the blame at my door. *shrug* Still shouldn’t let you create the fuckin’ table in the first place though, should it?
I’ll think about the naming thing – I do it when it’s a db bit I might shove on multiple sites, but when it’s a one-off I tend not to. Ah well, that might have to change… Arses.
I tend to follow the prefix option (although have been known to add “_table” as a suffic if I know I’ll be handing a site over to an inexperienced admin at some stage).