ORM and uniqueidentifier

[tuannie nguyen]

I have been having issues with "hibernate not able to convert string to uniqueidentifier" while learning to use ORM for few weeks now. I've searched the internet and asked questions, but it seems that not many people have implemented ORM with uniqueidentifier. I haven't been able to find a good example of it either.

I have a requirement that the DB objects are uniqueidentifier for PK and FKs. The PK key is generated on insert; however, i'm not sure how to properly map the FKs that are also uniqueidentifier. Here's a code snippet of what I have mapped...according to the Adobe CF 9 Unique foreign key association

Component System.cfc
/*Primary Key*/
property name="SystemID" column="SystemID" type="string" ormtype="string" fieldtype="id" generator="guid";

/*Foreign Keys*/
/*property name="RegistrationNumber" fieldtype="one-to-one" cfc="Registration" fkcolumn="RegistrationNumber" hint="Constraint is Null";*/

Component Registration.cfc
/*Primary Key*/
property name="RegistrationID" column="RegistrationID" type="string" ormtype="string" fieldtype="id" generator="assigned";
property name="System" fieldtype="one-to-one" cfc="System" mappedby="RegistrationNumber"

If you could point me to a good reference of how to implement ORM and uniqueidentifier, would greatly appreciate it. Sorry if my question is trivial, as I'm just learning CF and ORM on my own.

Regards.

[Cameron Childress]

Looks like you are using "guid" generator for the SystemID and "assigned" generator for the RegistrationID.

GUID means that ORM will try to create the ID on your behalf and assigned means that ORM expects you to provide one yourself. 

I'd recommend switching all PK definitions to be consistant. You probably either need to assign all the PKs or let them all be generated, but not mix the two. I will typically use UUID instead of GUID but I either is just as good as the other. You also don't really need the ORMtype definition since it's a duplicate of the type. This is the code I'd use for both of the PK definitions:

property name='SystemID' column='SystemID' type='string' length='32' fieldtype='id' generator='uuid';

property name='RegistrationID' column='RegistrationID' type='string' length='32' fieldtype='id' generator='uuid';

There is an older post that may be helpful to you covering my views on the ormtype vs type in ORM properties:

https://groups.google.com/forum/#!msg/railo/2QUOkfWr5Fw/12rVGfVZsSIJ

-Cameron

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.
To post to this group, send email to cf-or...@googlegroups.com.
Visit this group at http://groups.google.com/group/cf-orm-dev.
For more options, visit https://groups.google.com/d/optout.

--

Cameron Childress
--
p:   678.637.5072

im: cameroncf

facebook | twitter | google+

[tuannie nguyen]

Thanks Cameron for the reference. Will be reading though it.

I have tried setting both the PKs to be "generated" before. But the underlining issue isn't the creation or the PKs. It's saving the "assigned" guid to the uniqueidentifier field. Hibernate always complain about not being able to convert string to uniqueidentifier. It's only accepting it when it is set to NULL. The same issue with boolean field. Perhaps, I have yet to understand the mapping of data for ORM types? (maybe after reading your reference, I'll find an answer there?) From my limited understanding, CF treats most data types as simply String... so does ORM just automatically knows to appropriately converts to SQL type upon saving?

Thank you for your time in and help.

[Cameron Childress]

On Tue, Jan 13, 2015 at 11:12 AM, tuannie nguyen wrote:

I have tried setting both the PKs to be "generated" before.  But the underlining issue isn't the creation or the PKs.  It's saving the "assigned" guid to the uniqueidentifier field. 

Your primary key is your unique identifier. I am not sure if we are having a language problem here, but the PK is your Primary Key and you are using a guid for this, which is a unique identifier datatype. 

If you are going to settle on using a guid (uniqueidentifier) datatype for your Primary Keys, you should probably do this on all tables, everywhere. IF you don't, you re going to end up with some very confusing code and hard to troubleshoot problems. In fact this may be your problem. If you are switching things all around you may want to just nuke and recreate the tables and code example taking extra care that you have all the right datatypes everywhere.

 

Hibernate always complain about not being able to convert string to uniqueidentifier. 

This is why I would recommend allowing ORM to generate them for you rather than trying to assign them yourself. 

 

It's only accepting it when it is set to NULL.   

I don't understand how a PK could be set to NULL. However, if you are using generated PKs, it will be NULL till you do an entitySave(), which is when the guid is actually generated for you by ORM.

 

The same issue with boolean field. 

As a PK? I think you are mixing up your original question here since I don't think you'd be using a Boolean as a PK.

 

Perhaps, I have yet to understand the mapping of data for ORM types? (maybe after reading your reference, I'll find an answer there?)  From my limited understanding, CF treats most data types as simply String... so does ORM just automatically knows to appropriately converts to SQL type upon saving?

CF does treat simple datatypes as strings, but when you are passing this into ORM the value will need to be transformed along the way:

CF Datatype ---> ORM DataType --> SQL Datatype

In CF a GUID is "just a string" but as ORM/Hibernate does it's magic it will need to make sure that string is actually a valid GUID on it's way all the way down to the database, which in many cases also needs to have the correct matching datatype for the column as well.

-Cameron

[tuannie nguyen]

Thank you for clarifying different issues for me. I'm sorry, just feeling so confused!

I changed the PK as you recommended

Component System.cfc
/*Primary Key*/
property name="SystemID" column="SystemID" type="string" ormtype="string" fieldtype="id" generator="guid";

/*Foreign Keys*/
/*property name="RegistrationNumber" fieldtype="one-to-one" cfc="Registration" fkcolumn="RegistrationNumber" hint="Constraint is Null";*/

Component Registration.cfc
/*Primary Key*/

property name="RegistrationID" column="RegistrationID" type="string" ormtype="string" fieldtype="id" generator="guid";

property name="System" fieldtype="one-to-one" cfc="System" mappedby="RegistrationNumber"

So, then when saving a new entry:

var system = populateModel(systemService.get(id )); //should return record, if id="" then generate a new id
var registration = populateModel(registrationService.get(registrationNumber));
system.setRegistrationNumber(ditprdon);
systemService.save( system );

Upon save, I still get "hibernate not able to convert string to uniqueidentifier".

I turned on the SQL Log, but the log doesn't show the actual values, just prepared sql statement... Does ORM/Hibernate expects the columns in the Entity to be in the exact order as the database table?

[Cameron Childress]

On Thu, Jan 15, 2015 at 1:45 PM, tuannie nguyen wrote:

var system = populateModel(systemService.get(id ));  //should return record, if id="" then generate a new id
var registration = populateModel(registrationService.get(registrationNumber));
system.setRegistrationNumber(ditprdon);
systemService.save( system );

Upon save, I still get "hibernate not able to convert string to uniqueidentifier".

I don't know what populateModel() does or what systemService does (and I don't want to). Why don't you slim down and un-complicate your code a bit and eliminate all the services and populating model stuff and just do a simple one file example using entitySave() and entityNew() etc... 

Get ORM working by itself first then you can start adding layers of services and complicated things on top.

-Cameron

 

[tuannie nguyen]

populateModel() does or what systemService() are part of the ColdBox framework. I have learned to use the framework incrementally, as far as building the model and service layer without using ORM. So, now just adding another level of complexity by implementing ORM...

You're correct on your suggestion of "crawl, walk, and then run". However, on a job, sometimes we don't have that luxury of time. So, just have to learn how to skip.

I appreciate your suggestions.

[Cameron Childress]

On Fri, Jan 16, 2015 at 10:27 AM, tuannie nguyen wrote:

You're correct on your suggestion of "crawl, walk, and then run".  However, on a job, sometimes we don't have that luxury of time.  So, just have to learn how to skip.

Writing your code in it's own isolated test folder will take virtually no time at all and is likely to shortcut the 4 days of back and forth on this thread quite significantly. If you are short on time, that is the best way to go, but you do what you need to do and good luck.

-Cameron