Mixing
How to loop over array and insert records in Oracle database?
I have form that has an option for adding extra fields. The user can add as many fields as they want. Once they submit the form I need to loop over from scope and insert records in ORACLE database. This is example of my code:
<cfquery name="insertRec" datasource="dbs">
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
)
<cfif count NEQ arrayLen(arrDpr)>,</cfif>
<cfset count++>
</cfloop>
The first problem when I tried to test the code above is with my Oracle Insert statement. I'm getting error message:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended
I looked over the error message and Oracle insert code looks like this:
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES (
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
)
The code above is obviously wrong and SQL insert string is not built the right way. I'm wondering how this can be fixed? What is the best way to do this in ColdFusion for ORACLE Database?
Also the other problem I have is related to ColdFusion and outputting array index in cfqueryparam. As you can see above values for Subject and Description columns are hardcoded. I'm trying to find the way to output array index value. So for example if my array has this values [1,3,2], then in cfloop I would need to get this in cfqueryparam:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column#i#)#" maxlength="50">
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr#i#)#" maxlength="500">
Code above is wrong and I can not have ## inside of another ##. Is there a way to get actual value of i appended to column and datadescr? They would look like:
column1 datadescr1
column3 datadescr3
column2 datadescr2
I'm new in Oracle world and this is something that I can't figure it out. Any help would be greatly appreciated.
oracle oracle11g coldfusion insert cfloop
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
|
show 4 more comments
I have form that has an option for adding extra fields. The user can add as many fields as they want. Once they submit the form I need to loop over from scope and insert records in ORACLE database. This is example of my code:
<cfquery name="insertRec" datasource="dbs">
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
)
<cfif count NEQ arrayLen(arrDpr)>,</cfif>
<cfset count++>
</cfloop>
The first problem when I tried to test the code above is with my Oracle Insert statement. I'm getting error message:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended
I looked over the error message and Oracle insert code looks like this:
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES (
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
)
The code above is obviously wrong and SQL insert string is not built the right way. I'm wondering how this can be fixed? What is the best way to do this in ColdFusion for ORACLE Database?
Also the other problem I have is related to ColdFusion and outputting array index in cfqueryparam. As you can see above values for Subject and Description columns are hardcoded. I'm trying to find the way to output array index value. So for example if my array has this values [1,3,2], then in cfloop I would need to get this in cfqueryparam:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column#i#)#" maxlength="50">
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr#i#)#" maxlength="500">
Code above is wrong and I can not have ## inside of another ##. Is there a way to get actual value of i appended to column and datadescr? They would look like:
column1 datadescr1
column3 datadescr3
column2 datadescr2
I'm new in Oracle world and this is something that I can't figure it out. Any help would be greatly appreciated.
oracle oracle11g coldfusion insert cfloop
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
By "adding extra fields" do you mean thatFirst,Last,Email,SubjectandDescriptioncan be added multiple times or that they may addEmailandDescriptionbut leave the others out?
– Shawn
Nov 19 '18 at 22:49
Actually, looking at the second part of your question, I think that you mean the latter. So is this going to be one record for a person that is inserting multiple rows? Can you provide a small example of your input and the values you expect to insert into your database? Pseudo-code would be very helpful.
– Shawn
Nov 19 '18 at 22:52
@Shawn You guessed right, I'm saving user of entering first,last name, and email multiple times. Instead I gave them an option to click on the button and add subject and description fields if necessary. So when I loop over form fields I copy paste first, last, email and then have column1, column2, column3 and description1, description2 and description3 for example. Does that make sense?
– espresso_coffee
Nov 19 '18 at 23:43
What version of CF are you using for this one? I forgot.
– Shawn
Nov 19 '18 at 23:45
CF10, We might be migrating to 2016 or 2018 soon.
– espresso_coffee
Nov 19 '18 at 23:46
|
show 4 more comments
I have form that has an option for adding extra fields. The user can add as many fields as they want. Once they submit the form I need to loop over from scope and insert records in ORACLE database. This is example of my code:
<cfquery name="insertRec" datasource="dbs">
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
)
<cfif count NEQ arrayLen(arrDpr)>,</cfif>
<cfset count++>
</cfloop>
The first problem when I tried to test the code above is with my Oracle Insert statement. I'm getting error message:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended
I looked over the error message and Oracle insert code looks like this:
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES (
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
)
The code above is obviously wrong and SQL insert string is not built the right way. I'm wondering how this can be fixed? What is the best way to do this in ColdFusion for ORACLE Database?
Also the other problem I have is related to ColdFusion and outputting array index in cfqueryparam. As you can see above values for Subject and Description columns are hardcoded. I'm trying to find the way to output array index value. So for example if my array has this values [1,3,2], then in cfloop I would need to get this in cfqueryparam:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column#i#)#" maxlength="50">
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr#i#)#" maxlength="500">
Code above is wrong and I can not have ## inside of another ##. Is there a way to get actual value of i appended to column and datadescr? They would look like:
column1 datadescr1
column3 datadescr3
column2 datadescr2
I'm new in Oracle world and this is something that I can't figure it out. Any help would be greatly appreciated.
oracle oracle11g coldfusion insert cfloop
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
I have form that has an option for adding extra fields. The user can add as many fields as they want. Once they submit the form I need to loop over from scope and insert records in ORACLE database. This is example of my code:
<cfquery name="insertRec" datasource="dbs">
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
)
<cfif count NEQ arrayLen(arrDpr)>,</cfif>
<cfset count++>
</cfloop>
The first problem when I tried to test the code above is with my Oracle Insert statement. I'm getting error message:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended
I looked over the error message and Oracle insert code looks like this:
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES (
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
) ,
(
SYS_GUID(),
(param 1) ,
(param 2) ,
(param 3) ,
(param 4) ,
(param 5) ,
CURRENT_TIMESTAMP
)
The code above is obviously wrong and SQL insert string is not built the right way. I'm wondering how this can be fixed? What is the best way to do this in ColdFusion for ORACLE Database?
Also the other problem I have is related to ColdFusion and outputting array index in cfqueryparam. As you can see above values for Subject and Description columns are hardcoded. I'm trying to find the way to output array index value. So for example if my array has this values [1,3,2], then in cfloop I would need to get this in cfqueryparam:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column#i#)#" maxlength="50">
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr#i#)#" maxlength="500">
Code above is wrong and I can not have ## inside of another ##. Is there a way to get actual value of i appended to column and datadescr? They would look like:
column1 datadescr1
column3 datadescr3
column2 datadescr2
I'm new in Oracle world and this is something that I can't figure it out. Any help would be greatly appreciated.
oracle oracle11g coldfusion insert cfloop
oracle oracle11g coldfusion insert cfloop
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
asked Nov 19 '18 at 16:25
espresso_coffee
1,98152044
1,98152044
By "adding extra fields" do you mean thatFirst,Last,Email,SubjectandDescriptioncan be added multiple times or that they may addEmailandDescriptionbut leave the others out?
– Shawn
Nov 19 '18 at 22:49
Actually, looking at the second part of your question, I think that you mean the latter. So is this going to be one record for a person that is inserting multiple rows? Can you provide a small example of your input and the values you expect to insert into your database? Pseudo-code would be very helpful.
– Shawn
Nov 19 '18 at 22:52
@Shawn You guessed right, I'm saving user of entering first,last name, and email multiple times. Instead I gave them an option to click on the button and add subject and description fields if necessary. So when I loop over form fields I copy paste first, last, email and then have column1, column2, column3 and description1, description2 and description3 for example. Does that make sense?
– espresso_coffee
Nov 19 '18 at 23:43
What version of CF are you using for this one? I forgot.
– Shawn
Nov 19 '18 at 23:45
CF10, We might be migrating to 2016 or 2018 soon.
– espresso_coffee
Nov 19 '18 at 23:46
|
show 4 more comments
By "adding extra fields" do you mean thatFirst,Last,Email,SubjectandDescriptioncan be added multiple times or that they may addEmailandDescriptionbut leave the others out?
– Shawn
Nov 19 '18 at 22:49
Actually, looking at the second part of your question, I think that you mean the latter. So is this going to be one record for a person that is inserting multiple rows? Can you provide a small example of your input and the values you expect to insert into your database? Pseudo-code would be very helpful.
– Shawn
Nov 19 '18 at 22:52
@Shawn You guessed right, I'm saving user of entering first,last name, and email multiple times. Instead I gave them an option to click on the button and add subject and description fields if necessary. So when I loop over form fields I copy paste first, last, email and then have column1, column2, column3 and description1, description2 and description3 for example. Does that make sense?
– espresso_coffee
Nov 19 '18 at 23:43
What version of CF are you using for this one? I forgot.
– Shawn
Nov 19 '18 at 23:45
CF10, We might be migrating to 2016 or 2018 soon.
– espresso_coffee
Nov 19 '18 at 23:46
By "adding extra fields" do you mean that
First, Last, Email, Subject and Description can be added multiple times or that they may add Email and Description but leave the others out?– Shawn
Nov 19 '18 at 22:49
By "adding extra fields" do you mean that
First, Last, Email, Subject and Description can be added multiple times or that they may add Email and Description but leave the others out?– Shawn
Nov 19 '18 at 22:49
Actually, looking at the second part of your question, I think that you mean the latter. So is this going to be one record for a person that is inserting multiple rows? Can you provide a small example of your input and the values you expect to insert into your database? Pseudo-code would be very helpful.
– Shawn
Nov 19 '18 at 22:52
Actually, looking at the second part of your question, I think that you mean the latter. So is this going to be one record for a person that is inserting multiple rows? Can you provide a small example of your input and the values you expect to insert into your database? Pseudo-code would be very helpful.
– Shawn
Nov 19 '18 at 22:52
@Shawn You guessed right, I'm saving user of entering first,last name, and email multiple times. Instead I gave them an option to click on the button and add subject and description fields if necessary. So when I loop over form fields I copy paste first, last, email and then have column1, column2, column3 and description1, description2 and description3 for example. Does that make sense?
– espresso_coffee
Nov 19 '18 at 23:43
@Shawn You guessed right, I'm saving user of entering first,last name, and email multiple times. Instead I gave them an option to click on the button and add subject and description fields if necessary. So when I loop over form fields I copy paste first, last, email and then have column1, column2, column3 and description1, description2 and description3 for example. Does that make sense?
– espresso_coffee
Nov 19 '18 at 23:43
What version of CF are you using for this one? I forgot.
– Shawn
Nov 19 '18 at 23:45
What version of CF are you using for this one? I forgot.
– Shawn
Nov 19 '18 at 23:45
CF10, We might be migrating to 2016 or 2018 soon.
– espresso_coffee
Nov 19 '18 at 23:46
CF10, We might be migrating to 2016 or 2018 soon.
– espresso_coffee
Nov 19 '18 at 23:46
|
show 4 more comments
1 Answer
1
active
oldest
votes
For outputting correct values in cfqueryparam, you can use this:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Evaluate('form.column#i#')#" maxlength="50">
Or
<cfset tempCol = FORM["column#i#"]>
And then use this variable in your query.
Also regarding your query:
You need to set your Insert Into inside the loop or use from dual. This question should help
Best way to do multi-row insert in Oracle?
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
)
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
select SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
) from dual
<cfif count NEQ arrayLen(arrDpr)> union all </cfif>
<cfset count++>
</cfloop>
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
I'm still getting an error with the code you provided above. Seems thatevaluate()did not fix the problem.
– espresso_coffee
Nov 19 '18 at 18:01
It should work, I have a code working in prod with evaluate on dynamic field. What is the error you see?
– CFML_Developer
Nov 19 '18 at 18:05
Ah, wait. Modifying the answer.
– CFML_Developer
Nov 19 '18 at 18:06
2
There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible.
– Ageax
Nov 19 '18 at 21:26
1
Yes, but it's also trivial to exploit. Since there are safer alternatives in this case, there's no need to use or recommend evaluate() at all ;-) @espresso_coffee - The FORM scope is a structure. To access a key dynamically, useform["staticName"& index]or with your cfqueryparam<cfqueryparam value="#trim(form['first'& i])#" ...>.
– Ageax
Nov 20 '18 at 5:56
|
show 7 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378854%2fhow-to-loop-over-array-and-insert-records-in-oracle-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
For outputting correct values in cfqueryparam, you can use this:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Evaluate('form.column#i#')#" maxlength="50">
Or
<cfset tempCol = FORM["column#i#"]>
And then use this variable in your query.
Also regarding your query:
You need to set your Insert Into inside the loop or use from dual. This question should help
Best way to do multi-row insert in Oracle?
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
)
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
select SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
) from dual
<cfif count NEQ arrayLen(arrDpr)> union all </cfif>
<cfset count++>
</cfloop>
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
I'm still getting an error with the code you provided above. Seems thatevaluate()did not fix the problem.
– espresso_coffee
Nov 19 '18 at 18:01
It should work, I have a code working in prod with evaluate on dynamic field. What is the error you see?
– CFML_Developer
Nov 19 '18 at 18:05
Ah, wait. Modifying the answer.
– CFML_Developer
Nov 19 '18 at 18:06
2
There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible.
– Ageax
Nov 19 '18 at 21:26
1
Yes, but it's also trivial to exploit. Since there are safer alternatives in this case, there's no need to use or recommend evaluate() at all ;-) @espresso_coffee - The FORM scope is a structure. To access a key dynamically, useform["staticName"& index]or with your cfqueryparam<cfqueryparam value="#trim(form['first'& i])#" ...>.
– Ageax
Nov 20 '18 at 5:56
|
show 7 more comments
For outputting correct values in cfqueryparam, you can use this:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Evaluate('form.column#i#')#" maxlength="50">
Or
<cfset tempCol = FORM["column#i#"]>
And then use this variable in your query.
Also regarding your query:
You need to set your Insert Into inside the loop or use from dual. This question should help
Best way to do multi-row insert in Oracle?
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
)
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
select SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
) from dual
<cfif count NEQ arrayLen(arrDpr)> union all </cfif>
<cfset count++>
</cfloop>
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
I'm still getting an error with the code you provided above. Seems thatevaluate()did not fix the problem.
– espresso_coffee
Nov 19 '18 at 18:01
It should work, I have a code working in prod with evaluate on dynamic field. What is the error you see?
– CFML_Developer
Nov 19 '18 at 18:05
Ah, wait. Modifying the answer.
– CFML_Developer
Nov 19 '18 at 18:06
2
There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible.
– Ageax
Nov 19 '18 at 21:26
1
Yes, but it's also trivial to exploit. Since there are safer alternatives in this case, there's no need to use or recommend evaluate() at all ;-) @espresso_coffee - The FORM scope is a structure. To access a key dynamically, useform["staticName"& index]or with your cfqueryparam<cfqueryparam value="#trim(form['first'& i])#" ...>.
– Ageax
Nov 20 '18 at 5:56
|
show 7 more comments
For outputting correct values in cfqueryparam, you can use this:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Evaluate('form.column#i#')#" maxlength="50">
Or
<cfset tempCol = FORM["column#i#"]>
And then use this variable in your query.
Also regarding your query:
You need to set your Insert Into inside the loop or use from dual. This question should help
Best way to do multi-row insert in Oracle?
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
)
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
select SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
) from dual
<cfif count NEQ arrayLen(arrDpr)> union all </cfif>
<cfset count++>
</cfloop>
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
For outputting correct values in cfqueryparam, you can use this:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Evaluate('form.column#i#')#" maxlength="50">
Or
<cfset tempCol = FORM["column#i#"]>
And then use this variable in your query.
Also regarding your query:
You need to set your Insert Into inside the loop or use from dual. This question should help
Best way to do multi-row insert in Oracle?
INSERT INTO myTbl(
RecordID, First, Last, Email, Subject, Description, ActionDt
)
<cfset count = 1>
<cfloop from="1" to="#arrayLen(arrData)#" index="i">
(
select SYS_GUID(),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">,
CURRENT_TIMESTAMP
) from dual
<cfif count NEQ arrayLen(arrDpr)> union all </cfif>
<cfset count++>
</cfloop>
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
edited Nov 20 '18 at 4:53
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
answered Nov 19 '18 at 17:48
CFML_Developer
1,088416
1,088416
I'm still getting an error with the code you provided above. Seems thatevaluate()did not fix the problem.
– espresso_coffee
Nov 19 '18 at 18:01
It should work, I have a code working in prod with evaluate on dynamic field. What is the error you see?
– CFML_Developer
Nov 19 '18 at 18:05
Ah, wait. Modifying the answer.
– CFML_Developer
Nov 19 '18 at 18:06
2
There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible.
– Ageax
Nov 19 '18 at 21:26
1
Yes, but it's also trivial to exploit. Since there are safer alternatives in this case, there's no need to use or recommend evaluate() at all ;-) @espresso_coffee - The FORM scope is a structure. To access a key dynamically, useform["staticName"& index]or with your cfqueryparam<cfqueryparam value="#trim(form['first'& i])#" ...>.
– Ageax
Nov 20 '18 at 5:56
|
show 7 more comments
I'm still getting an error with the code you provided above. Seems thatevaluate()did not fix the problem.
– espresso_coffee
Nov 19 '18 at 18:01
It should work, I have a code working in prod with evaluate on dynamic field. What is the error you see?
– CFML_Developer
Nov 19 '18 at 18:05
Ah, wait. Modifying the answer.
– CFML_Developer
Nov 19 '18 at 18:06
2
There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible.
– Ageax
Nov 19 '18 at 21:26
1
Yes, but it's also trivial to exploit. Since there are safer alternatives in this case, there's no need to use or recommend evaluate() at all ;-) @espresso_coffee - The FORM scope is a structure. To access a key dynamically, useform["staticName"& index]or with your cfqueryparam<cfqueryparam value="#trim(form['first'& i])#" ...>.
– Ageax
Nov 20 '18 at 5:56
I'm still getting an error with the code you provided above. Seems that
evaluate() did not fix the problem.– espresso_coffee
Nov 19 '18 at 18:01
I'm still getting an error with the code you provided above. Seems that
evaluate() did not fix the problem.– espresso_coffee
Nov 19 '18 at 18:01
It should work, I have a code working in prod with evaluate on dynamic field. What is the error you see?
– CFML_Developer
Nov 19 '18 at 18:05
It should work, I have a code working in prod with evaluate on dynamic field. What is the error you see?
– CFML_Developer
Nov 19 '18 at 18:05
Ah, wait. Modifying the answer.
– CFML_Developer
Nov 19 '18 at 18:06
Ah, wait. Modifying the answer.
– CFML_Developer
Nov 19 '18 at 18:06
2
2
There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible.
– Ageax
Nov 19 '18 at 21:26
There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible.
– Ageax
Nov 19 '18 at 21:26
1
1
Yes, but it's also trivial to exploit. Since there are safer alternatives in this case, there's no need to use or recommend evaluate() at all ;-) @espresso_coffee - The FORM scope is a structure. To access a key dynamically, use
form["staticName"& index] or with your cfqueryparam <cfqueryparam value="#trim(form['first'& i])#" ...>.– Ageax
Nov 20 '18 at 5:56
Yes, but it's also trivial to exploit. Since there are safer alternatives in this case, there's no need to use or recommend evaluate() at all ;-) @espresso_coffee - The FORM scope is a structure. To access a key dynamically, use
form["staticName"& index] or with your cfqueryparam <cfqueryparam value="#trim(form['first'& i])#" ...>.– Ageax
Nov 20 '18 at 5:56
|
show 7 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378854%2fhow-to-loop-over-array-and-insert-records-in-oracle-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
By "adding extra fields" do you mean that
First,Last,Email,SubjectandDescriptioncan be added multiple times or that they may addEmailandDescriptionbut leave the others out?– Shawn
Nov 19 '18 at 22:49
Actually, looking at the second part of your question, I think that you mean the latter. So is this going to be one record for a person that is inserting multiple rows? Can you provide a small example of your input and the values you expect to insert into your database? Pseudo-code would be very helpful.
– Shawn
Nov 19 '18 at 22:52
@Shawn You guessed right, I'm saving user of entering first,last name, and email multiple times. Instead I gave them an option to click on the button and add subject and description fields if necessary. So when I loop over form fields I copy paste first, last, email and then have column1, column2, column3 and description1, description2 and description3 for example. Does that make sense?
– espresso_coffee
Nov 19 '18 at 23:43
What version of CF are you using for this one? I forgot.
– Shawn
Nov 19 '18 at 23:45
CF10, We might be migrating to 2016 or 2018 soon.
– espresso_coffee
Nov 19 '18 at 23:46