Solved

Get count of inserts on cfquery

Posted on 2013-01-18
8
274 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

807 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