setting a property for the primary key breaks save()?

[funaroma]

I am creating our "ideal" set of model files, but I am faced with
having those models connect to a very poor database schema.
Eventually the database schema will be revised and the data migrated,
but for my current tasks there is just no time to do so.

So, here's an example of what I've done. Ignore the call to
purgeDeprecatedFields() - it simply gets rid of the fields from the
database that we will not be using, that are created in the object on
New() (with the BlankDefaults plugin) or Retrieve().

/models/Person.cfc:

<cffunction name="init">

<cfscript>

afterNew("constructor");
afterFind("purgeDeprecatedFields"); //TODO: DevNote 3

dataSource("rwMembership");
table("MembershipCore");

property(name="id", column="M_ID");

property(name="vchEmailAddress", column="Email");
property(name="vchHandle", column="Handle");
property(name="vchPassword", column="Password");
property(name="vchAutoAuthKey", column="autoAuthKey");
property(name="bitRememberLogin", column="rememberLogin");

property(name="vchFirstName", column="FirstName");
property(name="vchLastName", column="LastName");
property(name="vchAddress1", column="Address1");
property(name="vchAddress2", column="Address2");
property(name="vchCity", column="City");
property(name="vchState", column="State");
property(name="vchZip", column="Zip");
property(name="chrCountry", column="Country");

property(name="vchPhoneNumber", column="Phone");
property(name="vchFaxNumber", column="Fax");
property(name="vchJobTitle", column="Title");
property(name="vchCompanyName", column="Company");

property(name="vchSignupSource", column="SignupSource");

property(name="dtmCreated", column="CRD");
property(name="dtmModified", column="LMD");
property(name="dtmLastVisit", column="LastVisitDateTime");
property(name="dtmDeleted", column="deletedDateTime");
property(name="bitIsSearchBot", column="isSearchBot");

property(name="dtmSuppressEsp", column="suppressEsp");

hasMany(name="Permissions", shortcut="roles");

</cfscript>

</cffunction>


Note that the table's primary key, M_ID, is aliased to "id" (by
convention) for Person.cfc.

Now, when I try to create a new person, set some values and save(), in
my controller, like so:

<cfset objPerson = model("Person").new() />
<cfset objPerson.vchEmailAddress = params.vchEmailAddress />
<cfset objPerson.vchSignupSource = "Polls.registerVote">
<cfset objPerson.save() />

I get one of two errors:

-- When my object is created, id is defaulted to zero. so when I try
to save it, I get this error:

Cannot insert explicit value for identity column in table
'MembershipCore' when IDENTITY_INSERT is set to OFF.

(so, it's trying to save the id=0 to the database, and (rightfully)
failing.)

-- If I remove "id" from the "this" struct of Person when a new one is
created, the record gets saved to the database, BUT the new id created
by the database never ends up in the person object, so when I
reference it, I get your standard CF error about the variable not
existing.


I have a feeling this is the result of "aliasing" the primary key
field.. but I don't know what to do; I cannot make changes to my
database schema at all at this time.

What to do? Is there a setting related to the primary key that I'm
missing?

[funaroma]

reference Retrieve() above was an error. should have said
afterFind().

[Chris Peters]

Not quite sure about the primary key issue, but it looks like you have the name and column values switched from how they should be. Maybe fix that and see if the primary key thing is still an issue?

--

You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To post to this group, send email to cfwh...@googlegroups.com.
To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

[funaroma]

Chris,
I can see why you'd think that, but they are in fact correct. This:

property(name="vchEmailAddress", column="Email");

Means the database column was named "Email" and we do in fact want to
reference it as vchEmailAddress. We use a "lite" form of hungarian
notation in our database schema and our variables here.

On Jul 22, 6:57 am, Chris Peters <ch...@clearcrystalmedia.com> wrote:
> Not quite sure about the primary key issue, but it looks like you have the
> name and column values switched from how they should be. Maybe fix that and
> see if the primary key thing is still an issue?
>

> > cfwheels+u...@googlegroups.com<cfwheels%2Bunsu...@googlegroups.com>
> > .

[tpet...@gmail.com]

just for the heck of it, can you change the name of your key to
something else and try to save?

property(name="id", column="M_ID");

to

property(name="mid", column="M_ID");

[funaroma]

I will give this a try with a test controller and model; i already
have a work-around for the original use-case and don't want to play
with it/break it again at the moment :)

[tpet...@gmail.com]

i might have missed it, but what was the workaround?

[funaroma]

Before save, I actually have to remove the ID variable from the
object:

beforeSave("removeId");

<cffunction name="removeId">
<cfif this.isNew()>
<cfset StructDelete(this,"id")>
</cfif>
</cffunction>

And then when I want the id of the new object that was saved, I have
to do a findOne() using one or more incoming variables to get the
record I just saved.

I'll admit this whole thing is an entire edge-case... the database(s)
i'm having to work with are such a clusterf$@k right now that having
to set aliases in CFW for things like primary key fields is ridiculous
to begin with. I can't wait until the pressure's off so I can
actually migrate the data into one well designed database and update
my model to what it should be!

[tpet...@gmail.com]

oh ok...

that is something that i'm hoping we can automate at some point.

when you do an insert (call save on a new record or a create) wheels
should see if the primary key has been set (if it's blank). if it
isn't then it should be removed so that you don't have to do that
workaround (which i use myself).

[funaroma]

So NORMALLY wheels checks to see if ID exists at all... if it doesn't
it performs and insert upon save(); otherwise it attempts to do an
update using the value of .id ? Except in this case, where apparently
my aliasing of the id field is breaking that behavior?

On Jul 26, 12:48 pm, "tpetru...@gmail.com" <tpetru...@gmail.com>
wrote:

[Per Djurner]

What do you do in the "constructor" function that you have set to run
on "afterNew"?

> To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.

[Per Djurner]

No, Wheels knows whether it's a new record or existing one based on
how it was initially created.
If created with new() for example Wheels knows that it will eventually
be inserted to the database for the first time.
When you use findByKey() on the other hand Wheels knows that it
already exists and will never try to run an INSERT SQL statement, only
UPDATE.

On Mon, Jul 26, 2010 at 10:22 PM, funaroma <advan...@gmail.com> wrote:

> To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.

[Mike Henke]

Per,

Could that be functionality be changed for save? The doc and example
doesn't mention if the record was created by new() or findByKey()
http://cfwheels.org/docs/function/save

Lets say I have action called bookmark and it will go to
bookmark.cfm. The action will set what to display to the user, either
Update or Create bookmark

the form will have <input type="hidden" name="id" value="" /> or with
a value and submit to saveBookmark

I would think save could do the findKey key itself to determine it it
is an update or insert instead of the coder always have to do it.

// saveBookmark - create/update bookmark:
function saveBookmark() {

bookmark = model("bookmark").new(params);

if (bookmark.save()) {
redirectTo(action="main");
} else {
flashInsert(message="Please complete the bookmark form!");
redirectTo(action="bookmark");

}
}

On Jul 26, 4:46 pm, Per Djurner <per.djur...@gmail.com> wrote:
> No, Wheels knows whether it's a new record or existing one based on
> how it was initially created.
> If created with new() for example Wheels knows that it will eventually
> be inserted to the database for the first time.
> When you use findByKey() on the other hand Wheels knows that it
> already exists and will never try to run an INSERT SQL statement, only
> UPDATE.
>
>
>
> On Mon, Jul 26, 2010 at 10:22 PM, funaroma <advantex...@gmail.com> wrote:
> > So NORMALLY wheels checks to see if ID exists at all... if it doesn't

> > it performs and insert uponsave(); otherwise it attempts to do an

> > update using the value of .id ?  Except in this case, where apparently
> > my aliasing of the id field is breaking that behavior?
>
> > On Jul 26, 12:48 pm, "tpetru...@gmail.com" <tpetru...@gmail.com>
> > wrote:
> >> oh ok...
>
> >> that is something that i'm hoping we can automate at some point.
>

> >> when you do an insert (callsaveon a new record or a create) wheels

> >> should see if the primary key has been set (if it's blank). if it
> >> isn't then it should be removed so that you don't have to do that
> >> workaround (which i use myself).
>
> >> On Jul 26, 7:17 am, funaroma <advantex...@gmail.com> wrote:
>

> >> > Beforesave, I actually have to remove the ID variable from the

> >> > > > > > > > Now, when I try to create a new person, set some values andsave(), in

> >> > > > > > > > my controller, like so:
>
> >> > > > > > > > <cfset objPerson = model("Person").new() />
> >> > > > > > > > <cfset objPerson.vchEmailAddress = params.vchEmailAddress />
> >> > > > > > > > <cfset objPerson.vchSignupSource = "Polls.registerVote">
> >> > > > > > > > <cfset objPerson.save() />
>
> >> > > > > > > > I get one of two errors:
>
> >> > > > > > > > -- When my object is created, id is defaulted to zero.  so when I try

> >> > > > > > > > tosaveit, I get this error:

>
> >> > > > > > > > Cannot insert explicit value for identity column in table
> >> > > > > > > > 'MembershipCore' when IDENTITY_INSERT is set to OFF.
>

> >> > > > > > > > (so, it's trying tosavethe id=0 to the database, and (rightfully)

> >> > > > > > > > failing.)
>
> >> > > > > > > > -- If I remove "id" from the "this" struct of Person when a new one is
> >> > > > > > > > created, the record gets saved to the database, BUT the new id created
> >> > > > > > > > by the database never ends up in the person object, so when I
> >> > > > > > > > reference it, I get your standard CF error about the variable not
> >> > > > > > > > existing.
>
> >> > > > > > > > I have a feeling this is the result of "aliasing" the primary key
> >> > > > > > > > field.. but I don't know what to do; I cannot make changes to my
> >> > > > > > > > database schema at all at this time.
>
> >> > > > > > > > What to do? Is there a setting related to the primary key that I'm
> >> > > > > > > > missing?
>
> >> > > > > > > > --
> >> > > > > > > > You received this message because you are subscribed to the Google Groups
> >> > > > > > > > "ColdFusion on Wheels" group.
> >> > > > > > > > To post to this group, send email to cfwh...@googlegroups.com.
> >> > > > > > > > To unsubscribe from this group, send email to

> >> > > > > > > > cfwheels+u...@googlegroups.com<cfwheels%2Bunsubscribe@googlegroups.c om>

> >> > > > > > > > .
> >> > > > > > > > For more options, visit this group at
> >> > > > > > > >http://groups.google.com/group/cfwheels?hl=en.
>
> > --
> > You
>

> ...
>
> read more »

[Mike Henke]

I have really confused myself tonight by reading the docs :-) I see no
case for to explicitly call save() from a form to do an update. This
assumes my form action will be the same for update/save.

If I do a bookmark = model("bookmark").findByKey(id), I will do a
bookmark.update(params.bookmark) so I won't need to call the save().

I can't seem to get the proper logic.

// saveBookmark - create/update bookmark:
function saveBookmark() {

what goes here :-)

if (bookmark.save()) {
flashInsert(message="Success!");

[Per Djurner]

Your logic will depend on whether you are updating an existing
bookmark or creating a new one.

You can do the actual save/update process in a bunch of different ways
but the important thing to remember is that if you are updating an
existing record you need to fetch it first. You can fetch it with
findByKey() and then call update() for example. Another way is to call
updateByKey() which does the same thing. Yet another way is to call
save() instead of update(). The reason you might do that is because
save() is more flexible in that it can both update or insert a new
record so you can use it regardless of what you have done previously
so to speak.

If you're creating a new record you can do it with new() and then
save(). Another way is to call create() which just calls new() and
save() internally for you. Again, using save() is useful from a
flexibility standpoint.

One example:

if (structKeyExists(params, "key"))
{
// fetch it
obj = model("x").findByKey(params.key);
}
else
{
// we're creating a new one since the key was not passed in from the form
obj = model("x").new(params.myObject);
}
// now we can call save and it will figure out whether to insert or
update the record
obj.save();

Curious, is this how other handle this common scenario as well?

> --
> You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
> To post to this group, send email to cfwh...@googlegroups.com.

> To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.

[Chris Peters]

I create separate actions for create and update scenarios because it's cleaner, leaner, and easier to follow. Your example scenario there gets hairier when it's time to do things within the action like respond to errors and set messages in the Flash.

[Per Djurner]

Yes, for the record, I usually have separate actions as well for the
same reasons you mentioned so I would recommend that as well :)

[Mike Henke]

Yeah, I think Wheels nudges you into the direct of separate actions
for create and update. I'll try Per's example.

I guess the save() doc could be clearer about the insert/update
depends on how the record was created. I thought it just knew by doing
some db check.

On Aug 6, 7:31 am, Per Djurner <per.djur...@gmail.com> wrote:
> Yes, for the record, I usually have separate actions as well for the
> same reasons you mentioned so I would recommend that as well :)
>
> On Fri, Aug 6, 2010 at 12:13 PM, Chris Peters
>
> <ch...@clearcrystalmedia.com> wrote:
> > I create separate actions for create and update scenarios because it's
> > cleaner, leaner, and easier to follow. Your example scenario there gets
> > hairier when it's time to do things within the action like respond to errors
> > and set messages in the Flash.
>

[Mike Henke]

Ok, I am looking at Per's example. How do we get the new values like
param.myForm into the fetched object to then call save it?

[Per Djurner]

I would use this one:
http://cfwheels.org/docs/function/setproperties

[Mike Henke]

Ah, very nice. Thanks alot