Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Get count of inserts on cfquery

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
earwig75
Asked:
earwig75
  • 5
  • 3
1 Solution
 
_agx_Commented:
"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
 
earwig75Author Commented:
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
 
_agx_Commented:
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
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.

 
_agx_Commented:
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
 
earwig75Author Commented:
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
 
_agx_Commented:
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
 
earwig75Author Commented:
Ok, and with this option I would not need cftransaction correct?
0
 
_agx_Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now