Ashraf,
You might need to show the code that is causing this, Ambiguous Columns usually means that you’re joining incorrectly.
Hi Andrew,
The entity Code is (no joins of any kind and hence find it strange with the message).
component entityname=“CourseType” table=“COURSETYPES” extends=“coldbox.system.orm.hibernate.ActiveEntity” persistent=“true” {
property name=“id” column=“ID” fieldtype=“id” generator=“assigned” length=“35”;
property name=“code” column=“CODE” ormtype=“string” length=“2” notnull=“true”;
property name=“name” column=“NAME” ormtype=“string” length=“120” notnull=“true”;
property name=“shortCourse” column=“SHORTCOURSE” ormtype=“string” length=“1” notnull=“false”;
property name=“sitsOldCourseCode” column=“SITSOLDCOURSECODE” ormtype=“string” length=“4” notnull=“false”;
property name=“dateValidFrom” column=“VALIDFROM” ormtype=“date”;
property name=“dateValidTo” column=“VALIDTO” ormtype=“date”;
property name=“note” column=“NOTE” ormtype=“string” length=“2000”;
property name=“dateCreated” column=“CREATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateUpdated” column=“UPDATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateDeleted” column=“DELETEDAT” ormtype=“timestamp”;
handler:
prc.rstCourseType = entityNew(“CourseType”).list();
dump(prc.rstCourseType);
abort;
Same code works with Railo 3.3.4.003. Switch to railo4 and the error message appears.
Error message:
Error Type: database : 0
Error Messages: invalid parameter for query, ambiguous column name
columnNames: id,code,name,shortCourse,sitsOldCourseCode,dateValidFrom,dateValidTo,note,dateCreated,dateUpdated,dateDeleted
Tag Context:
ID: ??
LINE: 148
Template: UserData\workspace\railo-express-4-cod-courses\webapps\railo\framework\coldbox-3.5.2\system\orm\hibernate\BaseORMService.cfc
ID: ??
LINE: 100
Template: UserData\workspace\railo-express-4-cod-courses\webapps\railo\framework\coldbox-3.5.2\system\orm\hibernate\VirtualEntityService.cfc
ID: ??
LINE: 8
Template: UserData\workspace\railo-express-4-cod-courses\webapps\railo\handlers\signin.cfc
Then you need to contact the Railo guys, I am almost 100% sure that this is a bug with Railo and not ColdBox. Especially if it works with an early version of Railo.
I think you have missed some parts of my 1st posting.
“Using the default ORM mechanism outside scope of ActiveEntity returns entityToQuery.”
Railo4 default ORM works no problem and returns the query set.
Ok I think I am confused, you are NOT explaining the problem.
You need to show the code that works in previous version of Railo, telling me that
prc.rstCourseType = entityNew(“CourseType”).list();
dump(prc.rstCourseType);
abort;
Works in previous version of Railo and doesn’t work in Railo 4 is obviously a Railo 4 problem. Ambiguous Column errors come about when you do not scope the tables with the table name and their are two columns with the same name, SQL will always throw an Ambiguous Column in cases like that.
So unless you are prepared to show the code that is not working, I can’t help you any further. Because as far as I am concerned you made it very clear that the same code in my reply above, works in a previous version of Railo, and if you make no changes and it is failing in Railo 4, then you have to talk to the Railo guys.
Also is that the entire entity details you posted?
Also ColdBox is at version 3.5.3, so you may also want to download that and try again before you report any further.
Again I guess my post title was not clear and I do prologize for this (RAILO 4/Coldbox 3.5.2 and 3) i.e. tried in both 3.5.2 and 3.5.3.
And I guess I am not being clear either…
Lets take this SQL for example
select tbl_registration.*, tbl_ebp.name as ebp_name, tbl_Users.id as user_id, tbl_ebp.id as linked_ebp_id
from tbl_registration
left outer join tbl_ebp on tbl_ebp.id = tbl_registration.ebp_id
left outer join tbl_users on tbl_registration.email = tbl_users.username
where id = [PARAM]p_id
notice the where clause?
This will throw an Ambiguous Column, because the ID in the where clause has no table name prefix. SQL sees that there is more than one column with the ID and doesn’t know what you mean.
So unless you show us the right code, I am sorry but I doubt this is a ColdBox issue.
ORM is no different…
Hi Andrew,
The whole code that has the issue is what been posted. I am not sure what more code do you need.
- Code working in railo3
Entity: CourseType.cfc
component entityname=“CourseType” table=“COURSETYPES” extends=“coldbox.system.orm.hibernate.ActiveEntity” persistent=“true” {
property name=“id” column=“ID” fieldtype=“id” generator=“assigned” length=“35”;
property name=“code” column=“CODE” ormtype=“string” length=“2” notnull=“true”;
property name=“persistedCode” persistent=“false”;
property name=“name” column=“NAME” ormtype=“string” length=“120” notnull=“true”;
property name=“shortCourse” column=“SHORTCOURSE” ormtype=“string” length=“1” notnull=“false”;
property name=“sitsOldCourseCode” column=“SITSOLDCOURSECODE” ormtype=“string” length=“4” notnull=“false”;
property name=“dateValidFrom” column=“VALIDFROM” ormtype=“date”;
property name=“dateValidTo” column=“VALIDTO” ormtype=“date”;
property name=“note” column=“NOTE” ormtype=“string” length=“2000”;
property name=“dateCreated” column=“CREATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateUpdated” column=“UPDATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateDeleted” column=“DELETEDAT” ormtype=“timestamp”;
}
Handler file. signin.cfm
prc.rstCourseType = entityNew(“CourseType”).list();
dump(prc.rstCourseType);
abort;
Return: query result set
- Code in Railo4
Entity: CourseType.cfc
component entityname=“CourseType” table=“COURSETYPES” extends=“coldbox.system.orm.hibernate.ActiveEntity” persistent=“true” {
property name=“id” column=“ID” fieldtype=“id” generator=“assigned” length=“35”;
property name=“code” column=“CODE” ormtype=“string” length=“2” notnull=“true”;
property name=“persistedCode” persistent=“false”;
property name=“name” column=“NAME” ormtype=“string” length=“120” notnull=“true”;
property name=“shortCourse” column=“SHORTCOURSE” ormtype=“string” length=“1” notnull=“false”;
property name=“sitsOldCourseCode” column=“SITSOLDCOURSECODE” ormtype=“string” length=“4” notnull=“false”;
property name=“dateValidFrom” column=“VALIDFROM” ormtype=“date”;
property name=“dateValidTo” column=“VALIDTO” ormtype=“date”;
property name=“note” column=“NOTE” ormtype=“string” length=“2000”;
property name=“dateCreated” column=“CREATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateUpdated” column=“UPDATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateDeleted” column=“DELETEDAT” ormtype=“timestamp”;
}
Handler file. signin.cfm
prc.rstCourseType = entityNew(“CourseType”).list();
dump(prc.rstCourseType);
abort;
Return: Error message
- Rail4 and using default ORM:
Entity: CourseType.cfc
component entityname=“CourseType” table=“COURSETYPES” persistent=“true” {
property name=“id” column=“ID” fieldtype=“id” generator=“assigned” length=“35”;
property name=“code” column=“CODE” ormtype=“string” length=“2” notnull=“true”;
property name=“persistedCode” persistent=“false”;
property name=“name” column=“NAME” ormtype=“string” length=“120” notnull=“true”;
property name=“shortCourse” column=“SHORTCOURSE” ormtype=“string” length=“1” notnull=“false”;
property name=“sitsOldCourseCode” column=“SITSOLDCOURSECODE” ormtype=“string” length=“4” notnull=“false”;
property name=“dateValidFrom” column=“VALIDFROM” ormtype=“date”;
property name=“dateValidTo” column=“VALIDTO” ormtype=“date”;
property name=“note” column=“NOTE” ormtype=“string” length=“2000”;
property name=“dateCreated” column=“CREATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateUpdated” column=“UPDATEDAT” ormtype=“timestamp” notnull=“true”;
property name=“dateDeleted” column=“DELETEDAT” ormtype=“timestamp”;
}
Handler file. signin.cfm
prc.rstCourseType = entityLoad(“CourseType”);
dump(entityToQuery(prc.rstCourseType));
abort;
Output: Returns query result set.
And I maintain that if it worked in Railo 3, and it is not working in Railo 4, then Railo 4 has broken something.
I know what you mean. However there are no other tables to join with and no duplicate column names.
The ORM console returns sql output fine:
Hibernate:
select
this_.ID as ID18_0_,
this_.CODE as CODE18_0_,
this_.NAME as NAME18_0_,
this_.SHORTCOURSE as SHORTCOU4_18_0_,
this_.SITSOLDCOURSECODE as SITSOLDC5_18_
this_.VALIDFROM as VALIDFROM18_0_,
this_.VALIDTO as VALIDTO18_0_,
this_.NOTE as NOTE18_0_,
this_.CREATEDAT as CREATEDAT18_0_,
this_.UPDATEDAT as UPDATEDAT18_0_,
this_.DELETEDAT as DELETEDAT18_0_
from
COURSETYPES this_
However on screen, I am getting
ambiguous column name message.
kk, my last post to you…
Talk to the Railo team about this, it is a Railo issue.
That is why I said I thinks this is incompatibility between Coldbox and new railo4. I an not blaming Coldbox, but I think it is incompatibility issue, which would be nice if it can be addressed.
Which means YOU NEED to talk to the Railo team, because it used to work in Railo 3…
Tell them what?
I had a code working in clodbox with railo 3, but in railo 4 is not working.
They will ask have you tried default ORM setup. I say yes and it works.
They will say it most likley with Coldbox then. Railo4 ORM works with no such error.
Off-course they will be differences between railo3 and railo4, but the out of box feature work as described. Can’t fault it for that.
Seriously!!!
Hi Ashraf,
I can confirm the problem when orm entity extends=“coldbox.system.orm.hibernate.ActiveEntity”.
If possible then remove extends=“coldbox.system.orm.hibernate.ActiveEntity” then Railo EntityToQuery will work.
We will investigate and try make to it compatible with Railo but Railo is so bad in ORM. Thats why we use Adobe CF which is much smoother.
Adobe CF standard is not expensive, so should consider using Adobe CF.
Thanks
Sana
Hi Sana,
Thanks for the reply and update. It is not easy remove the activeEntity as we use it through out our enterprise application, which uses Coldbox as its framework.
We used to use Coldfusion, but we are moving towards Railo, due to cost reduction. Open source is general direction we are taking as much as possible.
I do appreciate the fact you will investigate, because I always try keep up to date with latest releases from Railo and Coldbox.
Thank you once again.