How to loop over array and insert records in Oracle database?

Question

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.

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
@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
And does Subject and Description line up? Can one or the other be blank? You may be able to just leave those form fields as the same name and pass those through. The `form` scope will treat those as a comma-delimited string or as an array depending on your settings. That would probably be easier to work with. I'm about to head home, and I'll take a look at this again when I get there. — Shawn, Nov 19 '18 at 23:48
@Shawn well they are separate fields and both are required. Obviously they store different data but if you can think of better way to approach this problem I'm open for suggestions. — espresso_coffee, Nov 19 '18 at 23:50
So if you add additional `Subject` fields, but leave the name of the field as `subject`, then you'll end up with `form.subject` = `subject1,subject2,subject3` or the array version of that. Much easier to work with than trying to find out if `form.subject42` was passed. — Shawn, Nov 19 '18 at 23:50
@Shawn I will try to reverse engineer your logic but if you have some time later please provide small example on how that should look like. Also how to avoid `evaluate()`. I heard a lot of reason why that should be avoided. — espresso_coffee, Nov 19 '18 at 23:52

CFML_Developer · Answer 1 · 2018-11-20T04:53:33.623

0

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>

edited Nov 20 '18 at 04:53

answered Nov 19 '18 at 17:48

CFML_Developer

1,565
7
18

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
Ah, wait. Modifying the answer. – CFML_Developer Nov 19 '18 at 18:06
Wrap for.column#i# in quotes. – CFML_Developer Nov 19 '18 at 18:07
You are missing quotes inside of Evaluate(). Also, regarding ORACLE Insert in this case, can you provide small sample of code how that should look like using the FROM Dual method? – espresso_coffee Nov 19 '18 at 18:08
2

There is no need for Evaluate(). Just use assoc. array notation. For security reasons, best to avoid evaluate whenever possible. – SOS Nov 19 '18 at 21:26
It's hard to stress enough how much `evaluate()` should be avoided. It just adds a lot of potential for someone to do bad things when there are usually other ways to do the same thing. – Shawn Nov 19 '18 at 21:53
What is `arrData` and why do you use that to determine the number of form entries to insert? You can just loop over the form itself and make sure you get all of the submitted fields. – Shawn Nov 19 '18 at 22:14
The `arrData` has form fields numbers/indexes for columns and datadescr. I have user enter only once First, Last and Email but Column/Subject and Description can have more than one entry. So when I insert records on the back end they should have the same indexes and copy first last and email in each row. I hope this explains the process and how my code works. – espresso_coffee Nov 19 '18 at 23:45
@Ageax Any example on how to replace `evaluate()` with assoc. array notation? – espresso_coffee Nov 20 '18 at 01:35
Evaluate is just one of the way to do it. Modifying the answer to include array notation. – CFML_Developer Nov 20 '18 at 04:49
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 ``. – SOS Nov 20 '18 at 05:56

How to loop over array and insert records in Oracle database?

1 Answers1