earwig75
asked on
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?
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>ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ..
ASKER
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?
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#;">
<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#;">
ASKER
Ok, and with this option I would not need cftransaction correct?
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...
Also what version of CF (8,9 or 10) and database type (MS SQL)?