Jan 252008
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.
Recent Comments