issue with ORMExecuteQuery[ColdBox 5.4.0][cborm 1.5.0]

I am having an issue with cborm.
I’m trying to run a findAll and count call on the virtualEntityService.
Unfortunately lucee and ColdFusion both return a null pointer or a null pointer exception.
At first I tried using named params, however here Coldbox returned the "MaybeInvalidParamCaseException " error (even when using only non alphabetic Characters like “_0”).
I changed my code to use positional characters instead, however here I got the error that a struct was expected and not an array.
I updated Lucee and Hibernate as well as Coldbox and cborm to the newest version that I could install (previous ColdBox 4.7. and cborm 1.1.0)
Even after removing all parameters from the query and calling the functions without parameters I got the "MaybeInvalidParamCaseException " exception,
System Config:

ColdBox 5.4.0

cborm 1.5.0


var ouID = ARBACAccessMgr.orgUnitID();
var query = "ORG_USER_FK in (?) ";
var cQuery = "";
var params = ArrayNew(1);
arrayAppend(params, ouID);ARBACAccessMgr.orgUnitID();
result["recordsTotal"] = ormService.count(query, params);
if (structKeyExists(rc, "search[value]") && rc["search[value]"]!=""){
/* Search value existing: Search in all fields if like value */
   query &= "AND ( LIKE ? "
         &"OR LIKE ? "
         &"OR serialnumber LIKE ? "
         &"OR CAST(yearbuild AS CHAR) LIKE ? "
         &"OR CAST(nextexamination AS CHAR) LIKE ? "
         &"OR CAST(lastexamination AS CHAR) LIKE ? "
         &"OR location.placename LIKE ? "
         &"OR user.description LIKE ? "
         &"OR LIKE ? "
         &"OR testerName LIKE ? OR testerCompany LIKE ? OR testerAddress LIKE ?) ";
   for(i=1;i<=12;i++){arrayAppend(params, rc["search[value]"]);}
/* Define table columns */
var cols = ["btns", "ous", "", "", "serialnumber", "yearbuild", "nextexamination", "lastexamination", "location.placename", "user.description", "", "tester"];
for(var i = 3; i <= arrayLen(cols); i++){
   /* Search if any column Search is used */
   if(structKeyExists(rc, "columns[#i-1#][search][value]") && rc['columns[#i-1#][search][value]']!=""){
      /* Append column search to query */
         query &= "AND (#cols[i]#Name LIKE ? OR #cols[i]#Company LIKE ? OR #cols[i]#Address LIKE ?) ";
         for(i=1;i<=3;i++){arrayAppend(params, rc['columns[#i-1#][search][value]']);}
      }else if(5<i<9){
         query &= "AND CAST(#cols[i]# AS CHAR) LIKE ? ";
         arrayAppend(params, rc['columns[#i-1#][search][value]']);
         query &= "AND #cols[i]# LIKE ? ";
         arrayAppend(params, rc['columns[#i-1#][search][value]']);
/* Calculate how many records can be found */
result["recordsFiltered"] = ormService.count(query, params);
/* If custom ordering: append to query */
if(structKeyExists(rc, "order[0][column]") && rc["order[0][column]"]!=""&&rc["order[0][column]"]!=0){
      query &= "ORDER BY #cols[rc["order[0][column]"]+1]#Name "&rc["order[0][dir]"]&" ";
      query &= "ORDER BY #cols[rc["order[0][column]"]+1]# "&rc["order[0][dir]"]&" ";
/* Prepend to query to match findAll Syntax */
query = "FROM RFID_OBJECT WHERE " & query;
var objects = structNew();
/* Differentiate show all entries/show max length of entries */
if(!structKeyExists(rc, "length") || rc.length < 1){
   objects = ormService.findAll(query, params);
   if (!structKeyExists(rc, "start")){rc.start=0;}
   objects = ormService.findAll(query = query , params=params, max = rc.length, offset = rc.start+1);


property name="ID" fieldtype="id" column="ID" ormtype="int" generator="native";

property name="uuid" ormtype="string";
property name="datechanged" ormtype="timestamp";
property name="inventorynumber" ormtype="string";
property name="serialnumber" ormtype="string";
property name="serialnumber1Lable" ormtype="string";
property name="CEmark" ormtype="string";
property name="yearbuild" ormtype="date";
property name="commissioningdate" ormtype="date";
property name="lastexamination" ormtype="date";
property name="nextexamination" ormtype="date";
property name="email" ormtype="string";
property name="feature1" ormtype="string";
property name="testerName" ormtype="string";
property name="testerCompany" ormtype="string";
property name="testerAddress" ormtype="string";

property name="ou" fieldtype="many-to-one" cfc="ARBACOrgUnitUser" fkcolumn="ORG_USER_FK"  orderby="name";
property name="location" fieldtype="many-to-one" cfc="RFID_Location" fkcolumn="LOCATION_FK"  orderby="placename";
property name="user" fieldtype="many-to-one" cfc="RFID_User" fkcolumn="USER_FK"  orderby="lastname";
property name="manufacturer" fieldtype="many-to-one" cfc="RFID_Manufacturer" fkcolumn="MANUFACTURER_FK"  orderby="name";
property name="category" fieldtype="many-to-one" cfc="RFID_Category" fkcolumn="CATEGORY_FK"  orderby="name";

Let me analyze the code and respond back sir.

Luis Majano
Ortus Solutions, Corp

P/F: 1-888-557-8057

just curios if you had time to look into this issue.

Christian Hillebrand

I think your best bet is to use criteria queries, instead of building some much HQL by hand. We also are working on cborm v2, you can test it out on the new version via box install cborm@be

Thanks for your response!
My problem with criteria queries is, that i am trying to search a string (given by the user) in the database date value.
Until now i couldn’t find any option to convert the date to a string with a given template (like dateTimeFormat does).
I think unless there is no option to call a database function (like “DATE_FORMAT”) directly I have to use regular SQL.

You can use an SQL restriction in a criteria query. Your big OR statement would look something like.


r.SQLRestriction( “CAST(nextexamination AS CHAR) LIKE ‘#rc[“search[value]”]#’” ),
r.SQLRestriction( “CAST(yearbuild AS CHAR) LIKE ‘#rc[“search[value]”]#’” )


You would, however want to pre-sanitize the input so that you don’t open yourself up to SQL injection. ( Which brings up a note for Luis that it would be handy if the SQLRestriction method accepted params like the Hibernate Java method does:

. :slight_smile: