Solved

Get count of inserts on cfquery

Posted on 2013-01-18
8
269 Views
Last Modified: 2013-01-18
I know this is not the most secure method of writing multiple records but for now I am stuck with it in our secure environment.

Using the below sample - I would like a way to somehow relate all of the records that are inserted with the "Insertstuff" query. In other words; if the Insert is run 5 times I'd like a way to know that those 5 records were all submitted at the same time by a person...with this query. Could someone assist with the code or offer a recommendation?

<CFLOOP QUERY="data" startRow="2">

<CFSET sqlins = sqlins & "INSERT INTO myTable
	(col1, col2, col3)

VALUES ('#col1#', '#col2#',
 '#col3#')#CRLF#;">


</CFLOOP>

<CFQUERY NAME="Insertstuff"
	DATASOURCE="#datasource#">
	#PreserveSingleQuotes(sqlins)#
</CFQUERY>

Open in new window

0
Comment
Question by:earwig75
  • 5
  • 3
8 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38794256
"Relate" how? Not sure what that means...  There may be a better option. So what is your ultimate goal in plain english?

Also what version of CF (8,9 or 10) and database type (MS SQL)?
0
 

Author Comment

by:earwig75
ID: 38794282
CF9. The way it is written now, each insert is a single record with a new identity (ID) in the table. I would like a way for each set of inserts to have a secondary ID that all match. So if it inserts 3 records like the below... I'd like one column to have the same # so I know that submission was all done at once. In the sample below the first 3 would be 1 submission...

ID     2ndID     col1     col2     col3
1           1            a         b           c
2           1            d         e           f
3           1            g         h           i
4           2            q         v           r
5           3            d         f            l
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38794343
Easiest would be create a single UUID ie createUUID() before the insert. Then insert the UUID in all of the records.  That means using a varchar column, not int, but then you don't have to worry about generating a unique INT value.

Another approach I've used is create a secondary table that stores more details about the action. It comes in handy for reporting because you have access to more details than just "the same user created these records".

The secondary table [UserAction] usually has these columns

         UserActionID - (identity) Unique record id
         UserID - identifies the user performing the action (userID, IP Address, ..whatever)
         ActionDate - Date and time action was performed
         ActionType - (Optional) Indicates action

Before inserting the 5 records, I add a record to the UserAction table and use the "result" attribute to grab the new record ID.

       <cfquery result="userAction" ...>
        INSERT INTO UserAction (UserID, ActionDate, ActionType)
        VALUES (
          (
                   <cfqueryparam value="#theCurrentUser#" ...>
                  , getDate()
                  , <cfqueryparam value="#someActionType#" ...>
         )
       </cfquery>

Then insert that "ID" into the other  table along with the rest of the data.

          INSERT INTO OtherTable (UserActionID, OtherColumns, ....)
          VALUES
          (
                   <cfqueryparam value="#userAction.IDENTITYCOL#" cfsqltype="cf_sql_integer">
                  , .... rest of values
           )

Important: As with any multiple insert query, wrap everything in a cftransaction
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38794379
Sorry just noticed your code is using PreserveSingleQuotes (ouch). Anyway, you don't *have* to use cfqueryparam. The logic above will work with either. It's just a lot more dangerous without the cfqueryparam ..
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:earwig75
ID: 38794546
Please forgive me, I am a bit confused. Using my example since multiple records are written how would I add the same unique ID? The first option you mention sounds viable. Could I create a UUID and then insert the same one in my query that uses the preservesinglequotes?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38794620
Just create the UUID before the loop:

      <cfset theUUID = createUUID()>

Then add another value to your insert code. dislaimer, I never ever use PreserveSingleQuotes, so the quoting may be off. Adjust as needed.

<CFSET sqlins = sqlins & "INSERT INTO myTable
      (col1, col2, col3, theNewUUIDColumn)

VALUES ('#col1#', '#col2#', '#col3#' , '#theUUID#' )#CRLF#;">
0
 

Author Comment

by:earwig75
ID: 38794638
Ok, and with this option I would not need cftransaction correct?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38794682
No, you always need cftransaction whenever you're doing related inserts.  It keeps everything together. ie Treats the multiple inserts as one action.  All of them succeed or they all fail.  Otherwise if an error occurs halfway through the inserts, only some of the data will be inserted. You won't know what got inserted and what didn't, and won't be able to retry without creating duplicates...
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now