Yo Adrian! / OOCF 7.3.1: How does my bean get a new ID when calling Create()?

Justin Treher asks: “Yo, Adrian. I see that you are using autonumber for your tables. Whenever you run the CREATE method in the DAO you are just returning true. How does the application get the ID?”

He continues:

“For instance, if I do:

contactDAO.create(contact);

contact.getContactID() will be 0 both before and after create.

It seems like you would want:

arguments.contact.setContactID(newContactID);

in the create method.

I’m used to using UUID, but needed to use autonumber. A google search later landed me on your blog for an example. I read and reread, but couldn’t find joy.”

This is a very good question. The answer is simple, but somehow you’ve missed the obvious:

Do exactly what you just outlined!

What’s your workflow?

The application that I’ve touched on through this series has a basic workflow:

1. List data in table
2. Create new data in a form
3. Save new data to a table
4. List data in a table (now contains new record)

The workflow you have might look like this:

1. List data
2. Create new data in a form
3. Save new data to a database
4. Work with newly created data

Programming your workflow

So let’s take a look again at the Create() method I outlined in part 7.3.

ContactDAO.cfc - create()

<cffunction name="create" access="public" output="false"
  returntype="boolean"><!--- [1] --->

  <!--- [2] --->
  <cfargument name="contact" required="true"
    type="contact" hint="Contact bean" />

  <!--- [3] --->
     <cfset var qCreateContact = "" />
  <cfset var qNewContact = "" />
  <cfset var newContactID = 0 />
  <cfset var categoryIDArray = arrayNew(1) />
  <cfset var x = 0 />
  <cfset var qCreateContactCategory = "" />

  <!--- [4] --->
  <cftransaction action="begin">

    <!--- [5] --->
    <cftry>

      <!--- [6] --->
      <cfquery name="qCreateContact"
        datasource="#variables.DSN#">
        INSERT INTO CONTACTS
        (
          FIRST_NAME,
          LAST_NAME
        )
        VALUES
        (
          <cfqueryparam
            value="#arguments.contact.getFirstName()#"
            cfsqltype="cf_sql_varchar" />,
          <cfqueryparam
            value="#arguments.contact.getLastName()#"
            cfsqltype="cf_sql_varchar" />
        )
      </cfquery>

      <!--- [7] --->
      <cfquery name="qNewContact"
        datasource="#variables.DSN#">

        SELECT LAST_INSERT_ID() AS NEW_ID

      </cfquery>

      <cfset newContactID = qNewContact.NEW_ID />

      <!--- [8] --->
      <cfset categoryIDArray = arguments.contact.getCategoryIDArray() />

      <!--- [9] --->
      <cfloop index="x"
        from="1"
        to="#arrayLen( categoryIDArray )#">

        <cfquery name="qCreateContactCategory"
          datasource="#variables.DSN#">
          INSERT INTO CONTACT_CATEGORIES
          (
            CONTACT_ID,
            CATEGORY_ID
          )
          VALUES
          (
            <!--- [10] --->
            <cfqueryparam
              value="#newContactID#"
              cfsqltype="cf_sql_integer" />,
            <!--- [11] --->
            <cfqueryparam
              value="#categoryIDArray[x]#"
              cfsqltype="cf_sql_integer"  />
          )
        </cfquery>

      </cfloop>

      <!--- [12] --->
      <cfcatch type="database">
        <!--- [12.1] --->
        <cftransaction action="rollback" />
              <!--- [12.2] --->
              <cfreturn false />
      </cfcatch>

    </cftry>

  <!--- [13] --->
  </cftransaction>

  <!--- [14] --->
  <cfreturn true />

</cffunction>

Let’s just hit the high points:

1. The function returns a boolean value.
2. The function takes a single argument of the Contact bean.
3. The Contact bean is passed by reference into Create().
4. We create a single record in the CONTACTS table.
5. We get the ID of the new Contact record so we can associate it to records in another table.
6. We loop over the associated CategoryIDs and create a record in the table CONTACT_CATEGORIES for each one.

<Elle Driver>Now, you should listen to this, ‘cause this concerns you.</Elle Driver>

We return true if everything inserted correctly or false if something went wrong. We do this so that the process that called create() knows if it created or not. We could return a struct with a key that’s boolean and a key that’s a string with an error message. We could do a lot of things, but this basic function only returns true or false.

So let’s alter Create() to populate the Contact bean’s CONTACT_ID property with our new ID.

Getting the new ID

When we created a record in the CONTACTS table, we retrieved the new ID using the MySQL function LAST_INSERT_ID(). Each database has its own function or process for retrieving a new ID after INSERT. I outlined many of them in the post Please stop using SELECT MAX(id).

If you’re using ColdFusion 8, the CFQUERY tag has been updated to automatically return the new ID as part of the returned data. The only downside is that the variable’s name differs based on your database.

The next thing we have to remember is that for the sake of this Primer, the Contact bean has private setters. This means that we cannot call them from outside the bean. Only public methods inside the bean can call its private methods.

So you have two options:

1. Make setContactID() public
2. call the init() method and pass all the bean’s properties back into itself

If you choose option 1, the updated Create() looks like:

      <!--- [12] --->
      <cfcatch type="database">
        <!--- [12.1] --->
        <cftransaction action="rollback" />
              <!--- [12.2] --->
              <cfreturn false />
      </cfcatch>

    </cftry>

  <!--- [13] --->
  </cftransaction>

  <!--- [14] --->
  <cfset arguments.contact.setContactID( newContactID ) />

  <!--- [15] --->
  <cfreturn true />

</cffunction>

If you choose option 2:

      <!--- [12] --->
      <cfcatch type="database">
        <!--- [12.1] --->
        <cftransaction action="rollback" />
              <!--- [12.2] --->
              <cfreturn false />
      </cfcatch>

    </cftry>

  <!--- [13] --->
  </cftransaction>

  <!--- [14] --->
  <cfset arguments.contact.init(
    CONTACT_ID = newContactID,
    CATEGORY_ID_ARRAY = arguments.contact.getCategoryIDArray(),
    FIRST_NAME = arguments.contact.getFirstName(),
    LAST_NAME = arguments.contact.getLastName()
    ) />

  <!--- [15] --->
  <cfreturn true />

</cffunction>

If the INSERTs failed, then we return false (12.2) and exit the function without updating CONTACT_ID. Otherwise, the CONTACT_ID property gets updated and we return true (15).

What about the calling process?

So let’s use this updated code in our presentation layer. Assume we have these files:

1. contact_list.cfm
2. contact_create.cfm
3. contact_process.cfm
4. contact_view.cfm

On contact_list.cfm, we have a link to create a new contact.

On contact_create.cfm, we have a form that submits to the processing page.

The contact_process.cfm page could then look something like this:

<cfset contact = createObject("component", "Contact").init(
  CONTACT_ID = form.CONTACT_ID,
  CONTACT_ID_ARRAY = listToArray(form.CATEGORY_ID),
  FIRST_NAME = form.FIRST_NAME,
  LAST_NAME = form.LAST_NAME
  ) />

<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) />

<cfif contactDAO.create( contact )>
  <cflocation url="contact_view.cfm?CONTACT_ID=#contact.getContactID()#" />
<cfelse>
  <cflocation url="contact_list.cfm?error=true" />
</cfif>

When Create() returns true, the contact bean’s CONTACT_ID property should be populated, so we can use it as part of the querystring in the cflocation tag. When it returns false, we can redirect back to the list page and display an error. Alternately, we could also return to the form page and pre-populate the form fields using the data from the bean via session or URL variables.

Moving on (I hope)

Justin, I hope this clears up your question. I know the most obvious answer can often seem like the wrong one. I’ve spent the last few weeks learning Flex and trust me, I’ve hit the “obvious” wall almost daily.

To everyone, please let me know if anything else here is unclear and I’ll do my best to reply via e-mail or on the site. I hope to have the post on the Collection object online in the very near future.