Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Insert query inserts values in parent table, but not relational table ...

Posted on 2011-10-15
7
Medium Priority
?
318 Views
Last Modified: 2012-05-12
ColdFusion 9
MS SQL Server 2005

... yet the update query updates both parent and relational tables. Update works; insert works partially.

I've struggled with this problem much of today. This is the contractors project that I worked on back in August, with help from gdemaria, _agx_, and brij. I just noticed this problem (and I am glad I noticed it before the client did).

If I update an existing contractor record, the update process works correctly: parent and relational tables are updated.

If I create (Insert) a contractor record, the record is properly inserted into parent table PNWContractors, but the relational table PNWContractorsCat is <em>not</em> updated.

One complicating factor is, the record ID is a UUID, not a simple integer. I did not build it this way; this is a database that was built around 2000, that I inherited. Here is the insert query:

INSERT query:
       <!--- We are updating two tables at the same time, so enclose the Update statements in cftransaction --->
<cftransaction>
<!--- statement to insert new record into table PNWContractors --->
			<cfquery name="InsertPNWContractors" datasource="#application.datasource#" result="newUUID">
				 INSERT INTO PNWContractors
     					(
                        Contractor_Name
      				   ,Contractor_Address
    			       ,Contractor_Address2
    			       ,Contractor_City
			           ,Contractor_State
			           ,Contractor_Zip
			           ,Contractor_Email
			           ,Contractor_ContactName
			           ,Contractor_Phone
			           ,Contractor_Website
			           ,Contractor_Inactive
			           ,InsertTimeStamp
                        )
			     VALUES(
	          	<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Name,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address2,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_City,50))#">,  
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Zip,40))#">, 
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Email,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ContactName,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Phone,20))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Website,100))#">,
				<cfqueryparam cfsqltype="cf_sql_bit" value="#val(form.Contractor_Inactive)#">,
				<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
				         )         
					</cfquery>
  
 <!--- use result attribute from query InsertPNWContractors (result="newUUID") to set value for form.Contractor_ID --->
        <cfset form.Contractor_ID = newUUID.IDENTITYCOL>
        
      
			<!--- INSERT new values Contractor_ID, Work_Type_ID --->
            <!--- into relational table PNWContractorsCat --->
            <!--- Remember that columns Contractor_ID and Work_Type_ID are UUIDs, not integers --->
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">
	      	,Work_Type_ID  
     FROM    PNWCat 
		WHERE  Work_Type_ID  IN ( 
                     <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar">
                 )
   </cfquery>          
   

    </cftransaction>
    
    

Open in new window

 
To me, this query InsertPNWContractors looks OK. It creates a new record in parent table PNWContractors; but, if I select work types to insert into Relational table PNWContractorsCat, those work types are not inserted.

Do you see anything wrong with the Insert query? Thank for advice.

Eric

Here are tables I am working with:

Parent table PNWContractors:
Contractor_ID      nvarchar(36)
Contractor_Name      nvarchar(255)
Contractor_Address      nvarchar(50)
Contractor_City      nvarchar(50)
Contractor_State      nvarchar(50)
Contractor_Zip      nvarchar(40)
Contractor_Email      nvarchar(255)
Contractor_ContactName      nvarchar(255)
Contractor_Phone      nvarchar(20)
Contractor_Website      nvarchar(100)
Int_Contractor_ID      int

Child table PNWCat:
Work_Type_ID      nvarchar(36)
Work_Type      nvarchar(50)
Work_Type_Description      nvarchar(255)
Display_Color      nvarchar(7)
InsertTimeStamp      datetime
UpdateTimeStamp      datetime
IntWork_Type_ID      int
      
Relational table PNWContractorsCat:
Contractor_ID (UUID value, datatype varchar)
Work_Type_ID (UUID value, datatype varchar)

0
Comment
Question by:Eric Bourland
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 600 total points
ID: 36974837
Eric, in the table  PNWContractors, which column is the identity column?

You are taking the identity column value and using it in your PNWContractorsCat table.  

    <cfset form.Contractor_ID = newUUID.IDENTITYCOL>   <----- here, you get the identity column's value ----------


     INSERT INTO PNWContractorsCat (
           Contractor_ID
              ,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">  <------- use identify value (INTEGER) here


check your PNWContractorsCat  table to see if records with a non UUID are being saved...

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36974925
>>>in the table  PNWContractors, which column is the identity column?

The identity column is: IntWork_Type_ID      (datatype int)

So, should I do this? (see code below)

Except IntWork_Type_ID isnot defined in scope form ...




<cfset form.IntWork_Type_ID = newID.IDENTITYCOL>
        

   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">
	      	,Work_Type_ID
            ,IntWork_Type_ID
     FROM    PNWCat 
		WHERE  IntWork_Type_ID  IN ( 
                     <cfqueryparam value="#form.IntWork_Type_ID#" list="true" cfsqltype="cf_sql_integer">
                 )
   </cfquery>

Open in new window

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 1400 total points
ID: 36975042
      >> <cfquery name="InsertPNWContractors" result="newUUID" ...>

That variable name is misleading.  The "result" won't contain a UUID. It only returns the identity value (integer) of the new record. I think that's throwing you off and I'd recommend changing it to something more clear, like result="newIntegerID"



        >> So, should I do this? (see code below)

No. That's still inserting an integer instead of a UUID (and for the wrong column).  Remember the tricky part about this structure is you need to do 2 steps to get the UUID:

1) First run create your contractor record.  Remember to change the "result" name!

      <cfquery name="InsertPNWContractors" result="newIntegerID" ...>
           INSERT INTO PNWContractors  ....etc....
      </cfquery>

2) Next, use the returned identity value to lookup the UUID for the new record.


<cfquery name="lookupNewUUID" ...>
SELECT  Contractor_ID AS ContractorUUID
FROM    PNWContractors
WHERE   Int_Contractor_ID =  <cfqueryparam value="#newIntegerID.IDENTITYCOL#" cfsqltype="cf_sql_integer">
</cfquery>


3) Finally, use the new UUID in your insert into PNWContractorsCat.  

    INSERT INTO PNWContractorsCat ( Contractor_ID,Work_Type_ID  )
     SELECT <cfqueryparam value="#lookupNewUUID.ContractorUUID#" cfsqltype="cf_sql_varchar">,Work_Type_ID  
     FROM    PNWCat
    WHERE  Work_Type_ID  IN (
                     <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar">
                 )



0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 600 total points
ID: 36975822
agx has given you the answer you need to get it working.    I don't want to confuse things, so if I do, just ignore this post.

I am wondering why you are using the UUID in the join-table at all?   The UUID is to help you find the contracts record in a somewhat encrypted fassion.   Joining tables is the job of the integer ID..

It seems to me that your join-table should be...

Relational table PNWContractorsCat:
  INT_Contractor_ID (INT)
  INTWork_Type_ID  (INT)


It seems you inherited this design, although I thought we tweaked it at one point... if you're stuck with it, then its moot.



0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36975981
>>>It seems to me that your join-table should be...
Relational table PNWContractorsCat:
  INT_Contractor_ID (INT)
  INTWork_Type_ID  (INT)

I agree -- it's very inconvenient. I did inherit this database and application with the join table already populated with UUIDs. Maybe there is a way to convert the UUIDs to their analogous interger IDs, but for now I don't have time to worry about that.

_agx_ I will work on your solution later today -- it might not be until this evening. I have obligations for the rest of the entire day and much of the evening.

>>>>Remember the tricky part about this structure is you need to do 2 steps to get the UUID:

I remember now. =) Thanks, gentlemen. I'll be back later tonight. Hope your day is good.

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 36977678
I finally got back to this question. (Long day.) _agx_, your solution worked perfectly the first time.

>>Remember the tricky part about this structure is you need to do 2 steps to get the UUID:

You've told me this at least twice before. =) Obviously I need to review this two-step process until I understand it intuitively.

* Query InsertPNWContractors updates table PNWContractors, and derives result newIntegerID.

* Next, CFSET form.Contractor_ID = newIntegerID.IDENTITYCOL
... which makes the value of form contractorID the value of the new identity column. This is not UUID column -- one thing I had mixed up, before.

* THis next part makes a lot of sense to me now, but I had to think about it for a minute:

<!--- use the returned identity value to lookup the UUID for the new record --->

<cfquery name="lookupNewUUID" datasource="#application.datasource#">
SELECT  Contractor_ID AS ContractorUUID
FROM    PNWContractors
WHERE   Int_Contractor_ID =  <cfqueryparam value="#newIntegerID.IDENTITYCOL#" cfsqltype="cf_sql_integer">
</cfquery>

.... One of my challenges is to understand WHY it is necessary to look up the UUID value -- but then I think about the purpose of the application, and what the other parts of the application are doing, and what parts of the data tables are not yet accounted for.

* And here is where we finally update the relational table:
<!--- use the new UUID in your insert into PNWContractorsCat --->

<cfquery name="UpdateCategory" datasource="#application.datasource#">
     INSERT INTO PNWContractorsCat ( Contractor_ID,Work_Type_ID  )
     SELECT <cfqueryparam value="#lookupNewUUID.ContractorUUID#" cfsqltype="cf_sql_varchar">,Work_Type_ID  
     FROM    PNWCat
    WHERE  Work_Type_ID  IN (
                     <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar">
                 )
</cfquery>

.. this makes more sense, but I am going to go over it a couple of more times before I turn in, tonight.

Thank you as always.

Also, gdemaria, thank you as always for your insight.

This task did give me the chance to carefully review the contractors update application. It's a much better application now. I also found some null values in the relational table that I cleaned out. =)

Thanks again _agx_ and gdemaria. I hope your respective evenings are pleasant.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36993230
> One of my challenges is to understand WHY it is necessary to look up the UUID value

Yeah. Keep in mind there are ways to automatically return the UUID from an insert into that table. Like putting the logic in a stored proc instead or using a trigger.  A stored proc would certainly be more modular. But switching gears at this juncture might make things even more confusing. That's why I didn't suggest it.  

If it makes it easier conceptually, you could also merge the 2 cfqueries into one. It's still two queries. But if you ever had to duplicate the cfquery in another another page it'd be harder to overlook the 2nd query.  Just a thought ..
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month20 days, 17 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question