[coldbox-3.6.0] criteriaBuilder not creating the correct SQL! Arghh! What am I doing wrong????

Using the following Entity - Drillers.cfc


component persistent=“true” extends=“coldbox.system.orm.hibernate.ActiveEntity” entityname=“Driller” table=“WELL_DRILLERS”{
property name=“driller_id” fieldtype=“id” ormType=“integer” generator=“sequence” params="{sequence=‘MLWWELTS.SEQ_WELL_DRILLERS’}";

property name=“company_name” ormType=“string”;

property name=“first_name” ormType=“string”;
property name=“middle_name” ormType=“string”;
property name=“last_name” ormType=“string”;
property name=“address” ormType=“string”;
property name=“city” ormType=“string”;
property name=“state” ormType=“string”;
property name=“zip” ormType=“string”;
property name=“day_phone” ormType=“string”;
property name=“alt_phone” ormType=“string”;
property name=“fax_phone” ormType=“string”;
property name=“email” ormType=“string”;
property name=“date_registered” ormType=“string”;
property name=“verified” ormType=“string” setter=“false”;
property name=“date_verified” ormType=“string”;
property name=“completed” ormType=“string” setter=“false”;
property name=“d_uid” ormType=“string”;
property name=“d_pwd” ormType=“string”;

// validation
this.constraints = {


And using the following LoginService.cfc

component singleton{
property name=“drillerEntity” inject=“entityService:Driller”;

public Driller function init(){
return this;

any function login(required string username, required string password){

try {
var user = drillerEntity.newCriteria();
var propertyNames = ArrayToList(drillerEntity.getPropertyNames());
user = user.and(
user.restrictions.isEQ(“d_uid”, arguments.username),
user.restrictions.isEQ(“d_pwd”, arguments.password)
}catch (any e){


If I call the login() function of LoginService with a username and password
Coldfusion/ORM/Hibernate builds the following SQL

SELECT this_.COMPANY_NAME AS y0_, this_.FIRST_NAME AS y1_, this_.MIDDLE_NAME AS y2_, this_.LAST_NAME AS y3_, this_.ADDRESS AS y4_, this_.CITY AS y5_, this_."STATE" AS y6_, this_.ZIP AS y7_, this_.DAY_PHONE AS y8_, this_.ALT_PHONE AS y9_, this_.FAX_PHONE AS y10_, this_.EMAIL AS y11_, this_.DATE_REGISTERED AS y12_, this_.VERIFIED AS y13_, this_.DATE_VERIFIED AS y14_, this_.COMPLETED AS y15_, this_.D_UID AS y16_, this_.D_PWD AS y17_, this_.DRILLER_ID AS y18_ FROM MLWWELTS.WELL_DRILLERS this_ WHERE (y16_=? AND y17_ =?)


[Macromedia][Oracle JDBC Driver][Oracle]ORA-00904: “Y17_”: invalid identifier


NOTE: Yes we are using the Oracle JDBC Driver…

Why does the Criteria in the login function not build a correct SQL Statement?
I could do this with two subcriterias, but then I have a SQL statement that is twice and long as it needs to be…

This has been plaguing me since I started using the CriteriaBuilder to create my code…

Is it me?

I would like to know why! A technical explanation would be acceptable…

Thanks for any help up front,


Just a follow-up… I still haven’t found out why this happens…
The solution I came up with was to actually alias those two property names “d_uid:username” and “d_pwd:password” in the property attribute of the withProjections function.
Works but is a little hacky…


I know this is old, but I ran across it and thought I’d share what Joel had posted on one of my questions. It seems using the word “this” can be used to get around this:

.withProjections( property=“this.MyProperty:MyProperty” )