Entries Tagged as MSSQL
MSSQL money datatype and <cfqueryparam/>
Posted by Paul Marcotte | Tags: ColdFusion , MSSQL
This is just a quick post about the <cfqueryparam/> and cf_sql_type to use with when working with Microsoft SQL Server (MSSQL) money datatype.
Working with Transfer ORM: TQL, MSSQL and Reserved Words
Posted by Paul Marcotte | Tags: MSSQL , Transfer
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.
// 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.
Finding Recurring Dates in MSSQL using HAVING
Posted by Paul Marcotte | Tags: MSSQL
Recently, I automated a manual billing process for recurring invoices. In order to batch process the job, I decided to use a stored procedure that gets the billing details for all recurring invoices by date. MSSQL date functions DATEADD, DATEDIFF and DATEPART take some getting used to, but are well documented and (once understood) made the query nice and light. What I missed was the fact that not all months are created equal.