Building SQL problem

Experts,

I have an app that's behavious is dependant on a catagory value. There is an insert statement that is partly dependant on this value, so i want to use a cfquery to update a database using some values that are used for all catagorys, as well as some that are catagory specific. eg:

cfswitch catagory
<case 1> cfset dynamic_value = "'#form.a#'"
<case 2> cfset dynamic_value = "'#form.b#'"
<case 3> cfset dynamic_value = "'#form.c#'"
<case 4> cfset dynamic_value = "'#form.d#'"
/cfswitch

cfquery
insert into .... (...)
values ('#form.all_cases#', dynamic_value)

(Assuming that the above was actual CF code) Cold Fusion seems to change the single quotes ' to double quotes " so the insert fails.

I want to use the case as otherwise there will be 12 cases with 10 lines of duplicated querries in each, and one or two lines of case dependant fields so over a hundred lines of code taht aren't really needed. And if it changes, then comes extra hassle.

Am using Oracle, with CF 5Pro if that makes a difference.
Currently developing in windows, moving to a linux produciton server when the code is ready.

Cheers, GR.
LVL 1
googlyralphAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nathansConnect With a Mentor Commented:
Use the Function PreserveSingleQuotes()

<html>
<head>
     <title>PreserveSingleQuotes</title>
</head>

<body>
<cfset dynamic_value = "'test'">
<cfset form.all_cases = "'test1','test2','test3'">

<cfoutput>
     <cfset SQL_Data =PreserveSingleQuotes(form.all_cases)>
     values (#SQL_Data#,#dynamic_value#)<br>
</cfoutput>

</body>
</html>


For a test copy the above code into a blank CFM file and test.
0
 
dash420Commented:
If that is only case
you can handel this with this code

<cfswitch expression="#form.catagory#">
  <cfcase value="1">
     <cfset dynamic_value = #form.a#>
  </cfcase>  
  <cfcase value="2">
     <cfset dynamic_value = #form.b#>
  </cfcase>  
  <cfcase value="3">
     <cfset dynamic_value = #form.c#>
  </cfcase>  
  <cfcase value="4">
     <cfset dynamic_value = #form.d#>
  </cfcase>  

in cfquery just used like
<cfquery datasource="dsn">
   insert into .... (...)
    values ('#form.all_cases#', '#dynamic_value#')
</cfquery>


hope this will solve yur problem.




0
 
googlyralphAuthor Commented:
Ah, sorry, there are several seperate values for each case, so this approach wont work, as each value needs to be enclosed in quotes.

GR.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
dash420Commented:
can u please explain me what is the exact problem
0
 
googlyralphAuthor Commented:
I have an online data input system. Large amount of data entering the system. There are blocks of this data that are the same for all catagorys. To reflect this, and minimise size of code, and duplication i want to create a sql update statement. The bluck of this statement is static, but as each catagory has several unique fields this needs to be accounted for in the insert statement.

At the moment, i have a CFSET creating two variables, which represent the variable fields (ie catogory dependant) one has the field names, the other the field values.

When i use the case statement to assing a value to the two variables, Cold Fusion is replacing the single quotes with double quotes in the string that it uses to make up the SQL insert. The result is that Oracle is complaing about the input format, becuase the query being created has some single quotes, and some double quotes representing the values. As Oracle (and SQL?) require single quotes around the values entered into the database it is causing an error.

<CFSET dynamic_fields = "ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, POSTCODE">
<CFSET dynamic_values = "'#FORM.address1#', '#FORM.address2#', '#FORM.address3#', '#FORM.address4#', '#FORM.postcode#'">

The first CF SET works fine, but the second one, for some reason Cold Fusion replaces them with double quotes. The above two cfsets are in each case of the switch, but there are different fields, depending on the case.

<CFQUERY  datasource="#Application.db#">
    INSERT INTO WEB_HALL_DATA (INSERT_DATE, APPLIC_NO, SURNAME, FORENAMES, #dynamic_fields#, GP_NAME, SURGERY)

VALUES (#createODBCDateTime(Now())#, '#FORM.applic_no#', '#FORM.surname#', '#FORM.forenames#'#dynamic_values#,
'#FORM.gp_name#', '#FORM.surgery#')
</CFQUERY>

The error in cold fusion is:

SQL = "INSERT INTO WEB_HALL_DATA (INSERT_DATE, APPLIC_NO, SURNAME, FORENAMES, ABER_ADDRESS1, ABER_ADDRESS2, ABER_ADDRESS3, ABER_ADDRESS4, ABER_POSTCODE, GP_NAME, SURGERY)
VALUES ({ts '2002-06-20 11:37:29'}, '994050492', 'SMITH', 'PAUL MICHAEL', "street", "village", "town", "country", "postcode", '', '2')"

As you can see, the single quotes from dynamic_values have been changed into double quotes.

That make it clearer?

GR.
0
 
googlyralphAuthor Commented:
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
googlyralphAuthor Commented:
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
googlyralphAuthor Commented:
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
googlyralphAuthor Commented:
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
dash420Commented:
Hi
I don't know its failing. for me is comming fine.
Your problem is depending upon the control you want to insert into database.

don't do append anything apostrop in the dynamic value.
rather u can gives the apostrope in value attributes of control.

like
<cfparam name="value1" defualt ="">

<cfparam name="value2" defualt ="">

<input type="text" name="address1" value="'#value1#'">  
this for varchar fields

<input type="text" name="num" value="#value2#">
this for number fields


<CFSET dynamic_fields = "ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, POSTCODE">
<CFSET dynamic_values = PreserveSingleQuotes("#FORM.address1#, #FORM.address2#, #FORM.address3#, #FORM.address4#,
#FORM.postcode#")>

here u have advantage to handle number as well as varchar.







0
 
dash420Commented:
Hi
I don't know its failing. for me is comming fine.
Your problem is depending upon the control you want to insert into database.

don't do append anything apostrop in the dynamic value.
rather u can gives the apostrope in value attributes of control.

like
<cfparam name="value1" defualt ="">

<cfparam name="value2" defualt ="">

<input type="text" name="address1" value="'#value1#'">  
this for varchar fields

<input type="text" name="num" value="#value2#">
this for number fields


<CFSET dynamic_fields = "ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, POSTCODE">
<CFSET dynamic_values = PreserveSingleQuotes("#FORM.address1#, #FORM.address2#, #FORM.address3#, #FORM.address4#,
#FORM.postcode#")>

here u have advantage to handle number as well as varchar.







0
 
nathansCommented:
I knew the answer because I had missed this one once myself.  There are plenty of functions you can miss if you never use them.

Nathan
www.cftipsplus.com
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.