I’m exploring the world of ORM within ColdBox. I was able to get the simple “contacts” example working; however, now adapting to our actual application is not working out too well.
I continue to get this error:
[SQLServer]Conversion failed when converting from a character string to uniqueidentifier.
which came from:
line 515
Template:…\hibertate\BaseORMService.cfc
As a result of the call from my handler:
function editor(event,rc,prc)
{
event.paramValue(“id”,0);
prc.item=ormService.get(rc.id);
event.setView(item/editor);
}
Snippet of model object:
component persistent=“true” table=“SystemList”
{
/Primary Key/
proptery name=“systemID” fieldtype=“id” column=“SystemID” generator=“native” setter=“false”;
The PK in MSSQL is defined as “uniqueidentifier”. For a new record, the rc.id is 0. I’ve tried several suggestions, such as (ormtype=“string” generated=“insert” generator=“guid”), but none resolved the issue.
Do I need to somehow convert the rc.id (string) into GUID before calling ormService.get()? Are there examples on how to do so?
A lot of the questions you’re going to have with CF-ORM are going to be answered by understanding how it works,
But YOU don’t want to be playing with the uniqueID from the table and the mapping to the entity, for example the error that you are getting is probably because you are changing something that you should not be changing.
Therefore I strongly suggest reading the above link, get to know it well. You can thank me later.
It doesn’t have anything to do with mucking with the uniqueID or changing something that shouldn’t be changed. It’s just a convention specific to BaseORMService’s get() which provides a convenience method for retrieving an entity by ID, or returning a new instance of an entity if the ID is not found.
The only problem he had is that he needs to pass an empty string instead of 0 since his id field is a uniqueidentifier type
That is correct, the param of the ID to 0 is the issue, so again I state that if you’re not familiar with ORM in ColdFusion then it is advised to read up on it before asking further questions. Setting the ID either directly to 0 via the get or by param would cause issues even if it was not using ColdBox as Identity fields can’t be zero.
Thanks Andrew for the reference. I sure will read up on it to understand more about ORM.
Joel, yes, being in learning mode, I simply followed the ColdBox doc and examples to implement and learn. I tried passing in an empty string as you suggested, and it seems to be working properly. Yeah! Thank you so much, I’ve spent quite a few hours scratching my head on this one. I’m so glad there’s a ColdBox community to ask questions.
Well, I spoke too soon… I got pass the .get issue by passing in an empty string instead of a 0… even though, the doc says either one should work.
Now, i’m having issues with saving… I cannot find any documentation or example of how to deal with unidentifier data fields…(not only for PK, but also for FK). I continue to get [SQLServer]Conversion failed when converting from a character string to uniqueidentifier…when trying to save an entity. If ColdBox has an example, please point me to it. Thank you for your help.
My component contains 2 FK to other tables, which are “uniqueidentifier” in MSSQL
Snippet of model object:
component persistent=“true” table=“SystemList”
{
/Primary Key/
proptery name=“systemID” fieldtype=“id” column=“SystemID” generator=“guid” setter=“false”;
/Properties/
property name=“versionID” ormtype=“string”;
property name=“relateA” ormtype=“string”; //FK defined as “uniqueidentifier” in MSSQL
property name=“relateB” ormtype=“string”; //FK defined as “uniqueidentifier” in MSSQL
//others…
}
And then Save…
function save(event,rc,prc){
event.paramValue(“id”, “”);
var system = populateModel(ppsdService.get( rc.id ) );
var vResults = validateModel(system );
if( !vResults.hasErrors() ){
ppsdService.save( system );
getPlugin(“MessageBox”).info(“System Saved!”);
setNextEvent(“ppsd/listORM”);
}
else{
getPlugin(“MessageBox”).error(messageArray=vResults.getAllErrors());
return editor(event,rc,prc);
}
}
**The error came from, but does not show what causes the error
Line: 1474
Template: C:\inetpub\wwwroot\coldbox\system\orm\hibernate\BaseORMService.cfc
Message: ERROR 12:09:24.19 PM ESEE
Application Execution Exception CFErrorType=Database CFDetails=The root cause of this exception was: coldfusion.orm.hibernate.HibernateSessionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting from a character string to uniqueidentifier… CFMessage=An exception occurred when committing the transaction. CFStackTrace=coldfusion.tagext.sql.TransactionTag$TransactionCommitException: An exception occurred when committing the transaction. at coldfusion.tagext.sql.TransactionTag.commit(TransactionTag.java:418) at coldfusion.tagext.sql.TransactionTag.txCommit(TransactionTag.java:814) at coldfusion.tagext.sql.TransactionTag.callScriptMethod(TransactionTag.java:804) at coldfusion.runtime.CFPage.TransactionCommit(CFPage.java:9976) at cfBaseORMService2ecfc214483273$func$TRANSACTIONED.runFunction(C:\inetpub\wwwroot\coldbox\system\orm\hibernate\BaseORMService.cfc:1474) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2659) at cfBaseORMService2ecfc214483273$funcSAVE.runFunction(C:\inetpub\wwwroot\coldbox\system\orm\hibernate\BaseORMService.cfc:800) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:655) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:444) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:414) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2432) at cfPPSDHandlerORM2ecfc433929541$funcSAVE.runFunction(C:\inetpub\wwwroot\ESEEMVC\handlers\PPSDHandlerORM.cfc:38) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:518) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:660) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:469) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2373) at coldfusion.tagext.lang.InvokeTag.doEndTag(InvokeTag.java:402) at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2800) at cfController2ecfc1454261474$funcINVOKER.runFunction(C:\inetpub\wwwroot\coldbox\system\web\Controller.cfc:764) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2659) at cfController2ecfc1454261474$funcRUNEVENT.runFunction(C:\inetpub\wwwroot\coldbox\system\web\Controller.cfc:648) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:518) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:660) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:469) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2373) at cfColdbox2ecfc255186317$funcPROCESSCOLDBOXREQUEST.runFunction(C:\inetpub\wwwroot\coldbox\system\Coldbox.cfc:236) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:655) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:444) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:414) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2432) at cfApplication2ecfc1893888985$funcONREQUESTSTART.runFunction(C:\inetpub\wwwroot\ESEEMVC\Application.cfc:67) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:655) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:444) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:414) at coldfusion.runtime.AppEventInvoker.invoke(AppEventInvoker.java:108) at coldfusion.runtime.AppEventInvoker.onRequestStart(AppEventInvoker.java:278) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:417) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:112) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:79) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:151) at coldfusion.CfmServlet.service(CfmServlet.java:219) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:414) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:204) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:298) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) Caused by: coldfusion.orm.hibernate.HibernateSessionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting from a character string to uniqueidentifier. at coldfusion.orm.hibernate.SessionWrapper.flush(SessionWrapper.java:181) at coldfusion.orm.hibernate.HibernatePersistenceManager.onCommit(HibernatePersistenceManager.java:951) at coldfusion.orm.hibernate.HibernatePersistenceManager$HibernateEventListener.onCommit(HibernatePersistenceManager.java:1100) at coldfusion.tagext.sql.TransactionTag.fireOnCommit(TransactionTag.java:743) at coldfusion.tagext.sql.TransactionTag.commit(TransactionTag.java:401) … 112 more Caused by: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting from a character string to uniqueidentifier. at macromedia.jdbc.sqlserverbase.ddca.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddca.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.v(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddq.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.ddj.m(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.e(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.a(Unknown Source) at macromedia.jdbc.sqlserverbase.dddf.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.v(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.x(Unknown Source) at macromedia.jdbc.sqlserverbase.dddf.executeUpdate(Unknown Source) at macromedia.jdbc.sqlserverbase.dddj.executeUpdate(Unknown Source) at coldfusion.server.j2ee.sql.JRunPreparedStatement.executeUpdate(JRunPreparedStatement.java:96) at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:46) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2418) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2858) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:268) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:260) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206) at coldfusion.orm.hibernate.SessionWrapper.flush(SessionWrapper.java:176) … 116 more CFTagContext=ID: CF_CFPAGE; LINE: 1474; TEMPLATE: C:\inetpub\wwwroot\coldbox\system\orm\hibernate\BaseORMService.cfc ID: CF_UDFMETHOD; LINE: 800; TEMPLATE: C:\inetpub\wwwroot\coldbox\system\orm\hibernate\BaseORMService.cfc ID: CF_TEMPLATEPROXY; LINE: 38; TEMPLATE: C:\inetpub\wwwroot\ESEEMVC\handlers\PPSDHandlerORM.cfc ID: CFINVOKE; LINE: 764; TEMPLATE: C:\inetpub\wwwroot\coldbox\system\web\Controller.cfc ID: CF_UDFMETHOD; LINE: 648; TEMPLATE: C:\inetpub\wwwroot\coldbox\system\web\Controller.cfc ID: CF_TEMPLATEPROXY; LINE: 236; TEMPLATE: C:\inetpub\wwwroot\coldbox\system\Coldbox.cfc ID: CF_TEMPLATEPROXY; LINE: 67; TEMPLATE: C:\inetpub\wwwroot\ESEEMVC\Application.cfc
Usually, foreign keys will be relationships to other ORM entities. You might check out the ContentBox source for an example of that (I don’t have the link handy at the moment).
Andrew, the vResults is empty - no errors and so trying to commit to the DB.
Joel, the FKs are not required to exists, if they don’t, then a NULL value should be saved to the DB. This applies for all other fields in the model. I showed the RC.objecttosave and all values entered look correct, except for the ones that did not get filled in contain “[empty string]”. Which i think should be fine right? because if empty string then a NULL value should be saved? or do I need to explicit set each value to “NULL”?
I looked into the nullEmptyInclude parameter for this line of code, but could not get it to work. What is wrong with this syntax?
var system = populateModel(ppsdService.get( rc.id ), nullEmptyInclude="*");
All the examples i’ve found are only dealt with a regular integer for an PK…
I’m so confused at this point…please help.
i’m sorry, i’m not sure what you meant by “mixing named and positional parameters”. I am just following the examples on http://wiki.coldbox.org/wiki/Models.cfm#populateModel()
in order to learn, and have not gotten to the point where I can tell what I’m doing wrong…i sure have a lot to learn! I went through the whole exercise and got everything to work, but now at the point of “Virtual Service Layer Style”…and dealing with the “uniqueidentifier”, which is not part of the example… so I hit a roadblock… if there is an example for this would be great!
however, I fixed the line of code, but it does not seem that the model is setting all the [empty string] to null…
Ok, instead of starting with populateModel (which is kind of a black box for debugging…at least initially), maybe try something a bit more rudimentary. For example, start with just straight ColdFusion ORM, set a few properties, and save it:
If that works, but you’re still getting issues with populate(), it’s possible that there’s something unexpected in RC that is trying to set a property with an illegal value for the data type.
As far as examples go, be sure to check out the ContentBox source code. There are lots of good examples in there of how this works and how pieces can be stitched together: