Working with Transfer ORM: TQL, MSSQL and Reserved Words
2008 January 25
I'm definitely marking this down in the "I should have known better" category. If you use MSSQL, you probably know by know that "user" is a reserved word. So, unless you use [user] as a table identifier it's imperative that you name your table something like "tbl_user" or use the plural "users". I don't like plural table names, so I opted for tbl_user. I just ran into a gotcha with my TQL for a query against tbl_user. Why? Here's a snippet from my method to check the uniqueness of a user e-mail.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '.'.
I finally decided to write up a unit test to try and get some meaningful debugging information. From there I pulled a query, ran that in the Query Analyzer. Scratched head a bit more, then...voila! Reserved word issue. How silly....
Of course, this is not purely a Transfer or even TQL problem, merely a cautionary tale regarding reserved words in MSSQL that I happened upon via Transfer TQL.
For the record, the new TQL query reads:
// local vars struct
var local = StructNew();
// default result
local.result = false;
// tql for list
local.tql = "from user.User as user where user.Email = :Email AND user.Id != :Id";
//create a query object
local.query = getTransfer().createQuery(local.tql);
//set the named parameters
local.query.setParam("Email", getEmail(), "string");
local.query.setParam("Id", getId(), "numeric");
//run it
local.userList = getTransfer().listByQuery(local.query);
// if no records exist e-mail passes unique test
if (local.userList.recordcount eq 0)
{
local.result = true;
}
return local.result;
My transfer object class name is user.User, so why not alias that as "user"? That's a logical alias, right? Wrong!
After pulling my hair out debugging the ever-so-descriptive error message,
local.tql = "from user.User where user.User.Email = :Email AND user.User.Id != :Id";The other big a-ha moment for me is that if an error occurs within a method invocation, I should debug by unit testing, not re-initializing, rinsing and repeating on the client side.