A

Anonymous

Answered

Dynamically built Insert Into not working

Forum|Forum|17 years ago
August 7, 2008
4 replies
1385 views

This is puzzling me badly. It's got to be syntax, it nearly always is with my code. I'm dynamically building an Insert query. The Insert Into clause works. The Values clause does not. Instead of translating and passing actual values, the system is passing the clause character for character. I've built dynamic Where clauses and Order By clauses many times. Even here, the Insert Into clause seems to be working, just not the Values clause. Why?

Database access

This topic has been closed for replies.

Correct answer Dan_Bracuk

quote:

Originally posted by: indnajns
There is an entire page infront of this one that looks for invalid dates, null values, etc etc. I'm not going to double check to see if the sun rose this morning, I'm going to let the computer do what it's supposed to.

I DID try to put an evaluate tag in there. But I obviously didn't do it right, since it still didn't work. As I said, my syntax is usually the problem.

I thought it would be nice to have the computer create the sql, rather than manually code it. This is but part of the page. The page is actually handed form vars that have to be split into three seperate tables. I was under the impression that we had computers to do the tedious stuff for us. If I'm going to have to manually do everything, well, that kind of defeats the purpose of "simple maintenance" and all that jazz that I'm always hearing about. Yes, I can code this manually, I have several pages where I do, but I thought since this gets split across three tables, this would be a good time to automate some things.

So, back to the original question, where would I put the evaluate tag?

You don't.

The best answer so far is Jochem's. But, if you don't want to do it that way, when you are building your input strings, put in the variable values instead of the variable names.

A

Anonymous

Well, Duh. Thank you Mr Dan for such a simple and concise answer to such a simple question! Exactly what I needed. Should've known I was making it harder than I needed to. Greatly appreciated.

N

Newsgroup_User

Inspiring

indnajns- wrote:
> <cfloop list = "#fieldlist#" index = "curfield">
> <CFif ListFindNoCase(numlist, curfield)>
> <CFif Left(curfield, 5) IS "date_">
> <CFset insert_string = insert_string & "##createODBCDate(form." &
> curfield & ")##, ">
> <CFelse>
> <CFset insert_string = insert_string & "##form." & curfield & "##, ">
> </CFif>
> <CFelse>
> <CFset insert_string = insert_string & "'##form." & curfield & "##', ">
> </CFif>
> <CFoutput>insert_string - #insert_string#<br><br></CFoutput>
> </cfloop>
> <CFset insert_string = RemoveChars(insert_string, Len(insert_string)-1, 2)>

> <CFquery datasource="mydata">
> INSERT INTO Items_Sold
> (#fieldlist#)
> VALUES (#PreserveSingleQuotes(insert_string)#);
> </CFquery>

Always use cfqueryparam for variables:

<cfquery datasource="mydata">
INSERT INTO Items_Sold
(#fieldlist#)
VALUES (
<cfloop list="#fieldlist#" index="curfield">
<CFif ListFindNoCase(numlist, curfield) >
<CFif Left(curfield, 5) IS "date_">
<cfset sqlType = "cf_sql_date" />
<CFelse>
<cfset sqlType = "cf_sql_integer" />
</CFif>
<CFelse>
<cfset sqlType = "cf_sql_varchar" />
</CFif>
<cfqueryparam cfsqltype="#sqlType#" value="#form[curfield]#" />
<cfif curField IS NOT ListLast(fieldList)>, </cfif>
</cfloop>
)
</cfquery>

Jochem

--
Jochem van Dieten
Adobe Community Expert for ColdFusion

A

Anonymous

There is an entire page infront of this one that looks for invalid dates, null values, etc etc. I'm not going to double check to see if the sun rose this morning, I'm going to let the computer do what it's supposed to.

I DID try to put an evaluate tag in there. But I obviously didn't do it right, since it still didn't work. As I said, my syntax is usually the problem.

I thought it would be nice to have the computer create the sql, rather than manually code it. This is but part of the page. The page is actually handed form vars that have to be split into three seperate tables. I was under the impression that we had computers to do the tedious stuff for us. If I'm going to have to manually do everything, well, that kind of defeats the purpose of "simple maintenance" and all that jazz that I'm always hearing about. Yes, I can code this manually, I have several pages where I do, but I thought since this gets split across three tables, this would be a good time to automate some things.

So, back to the original question, where would I put the evaluate tag? Around the whole thing [#PreserveSingleQuotes(insert_string)#] or around each individual #form.filedname#?
#PreserveSingleQuotes(Evaluate(insert_string))# didn't work.

btw, the semi-colon seems to be a MS Access thing. It appears in other Insert Queries I have that do work.

>> I presume the form variables don't exist at the time you're creating the string, hence "escaping" the pound signs >> so they don't actually get evaluated.
Well, that was the kind of odd part. The form fields DO actually exist, but during testing CF barfed if there was only one # sign. It complained that a variable can't end with a '.' (The '#form.' part of the concatenated insert_string.) I would've thought that since it was inside a quoted string variable that CF wouldn't have paid any attention. But, that's just me. It obviously has other ideas.

D

Dan_BracukCorrect answer

Inspiring

quote:

Originally posted by: indnajns
There is an entire page infront of this one that looks for invalid dates, null values, etc etc. I'm not going to double check to see if the sun rose this morning, I'm going to let the computer do what it's supposed to.

I DID try to put an evaluate tag in there. But I obviously didn't do it right, since it still didn't work. As I said, my syntax is usually the problem.

I thought it would be nice to have the computer create the sql, rather than manually code it. This is but part of the page. The page is actually handed form vars that have to be split into three seperate tables. I was under the impression that we had computers to do the tedious stuff for us. If I'm going to have to manually do everything, well, that kind of defeats the purpose of "simple maintenance" and all that jazz that I'm always hearing about. Yes, I can code this manually, I have several pages where I do, but I thought since this gets split across three tables, this would be a good time to automate some things.

So, back to the original question, where would I put the evaluate tag?

You don't.

The best answer so far is Jochem's. But, if you don't want to do it that way, when you are building your input strings, put in the variable values instead of the variable names.

N

Newsgroup_User

Inspiring

<CFset insert_string = insert_string & "##form." & curfield & "##, ">

What is your goal with this line?

What is is doing is to create a string that will be the hash|pound|#
character followed by the string 'form.' followed by the value of the
curfield variable followed by the hash|pound|# character. I.E. exactly
what you are complaining about.

A

Anonymous

Yes, that line creates the dynamic string. The value of curfield will be the next actual field name from the list, resulting in '#form.field1#',' #form.field2#', etc. And that part is working correctly. It creates the exact same line, character for character, that I can type in manually for the VALUES that does work. The difference is, when I use the manual version, it correctly substitutes the actual values in the final SQL; when I use the dynamically created one, it doesn't. Why? The #fieldlist# variable is being parsed correctly in the INSERT INTO line. Why isn't #Insert_String#? Is it too many levels? It's like it needs a giant "evaluate" tag around it.

D

Dan_Bracuk

Inspiring

First, get rid of the semi-colon.

To troubleshoot, reduce your fieldlist to just the id, and run your code. Add list elements one by one until it fails. That will tell you which field is causing your problem.

Are all those fields really text?

A

Anonymous

Which semi-colon?
"Which field" isn't the problem. EVERY field is being sent as "#form.field#", NOT the actual value of "form.field". It fails as soon as it hits the first date, because a date can't be "form.date_listed". It's as if it needed to be in a <CFoutput></CFoutput> tag (which I actually tried out of desperation). When I say that's the actual SQL, I mean, that's the actual SQL being handed to the server as copied from the bottom of the page in the Debugging Information. No values, just #form.this#, #form.that#. And yes, most of the fields are text. Only 10 are numbers or dates.

D

Dan_Bracuk

Inspiring

quote:

Originally posted by: indnajns-
Which semi-colon?
"Which field" isn't the problem. EVERY field is being sent as "#form.field#", NOT the actual value of "form.field". It fails as soon as it hits the first date, because a date can't be "form.date_listed". It's as if it needed to be in a <CFoutput></CFoutput> tag (which I actually tried out of desperation). When I say that's the actual SQL, I mean, that's the actual SQL being handed to the server as copied from the bottom of the page in the Debugging Information. No values, just #form.this#, #form.that#. And yes, most of the fields are text. Only 10 are numbers or dates.

There was only one semi-colon that I saw.

In any event, your approach has some weaknesses. Specifically, you are not validating your form fields or doing anything about empty fields. You might want to try something something less dynamic.

I just did something similar, except I was processing a pipe delimited file, not a form. There are 19 fields, either numeric, date, timestamp, or text. My approach was:

<cfscript>
ThisRow = Replace(ThisRow, "||", "|null|", "all");
if (right(ThisRow, 1) is "|")
ThisRow = ThisRow & "null";
</cfscript>


<cfloop list="1,2,3,4,5,6,7,8,10,11,12,13,15,16,17,18" index="element">
<cfset variables[RTrim(ListGetAt(ThisColumnList, element, ","))] = ListGetAt(ThisRow, element, "|")>
</cfloop>


<cfloop list="9,14" index="element">
<cfscript>
if (ListGetAt(ThisRow, element, "|") is "null") {
variables[RTrim(ListGetAt(ThisColumnList, element, ","))] = now();
}
else {
variables[RTrim(ListGetAt(ThisColumnList, element, ","))] = ParseDateTime(ListGetAt(ThisRow, element, "|"));
}
</cfscript>
</cfloop>


<cfloop list="2,3,4,5,6,11,12,13,14,18" index="element" delimiters=",">
<cfscript>
if (ListGetAt(ThisRow, element, "|") is "null") {
variables[RTrim(ListGetAt(ThisColumnList, element, ",")) & "_null"] = true;
}
else {
variables[Rtrim(ListGetAt(ThisColumnList, element, ",")) & "_null"] = false;
}

</cfscript>

<cfquery name="insert" datasource="#dsn#">
insert into micro_temp
(#ThisColumnList#)
values
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#ptnumber#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#admitdate#" null="#admitdate_null#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#regno#" null="#regno_null#">
, <cfqueryparam cfsqltype="cf_sql_char" value="#admitphys1#" null="#admitphys1_null#">
, <cfqueryparam cfsqltype="cf_sql_timestamp" value="#date_coll#">
etc

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded