Solved

Building SQL problem

Posted on 2002-06-20
12
164 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:googlyralph
  • 6
  • 4
  • 2
12 Comments
 
LVL 6

Expert Comment

by:dash420
ID: 7095381
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
 
LVL 1

Author Comment

by:googlyralph
ID: 7095522
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
 
LVL 6

Expert Comment

by:dash420
ID: 7095535
can u please explain me what is the exact problem
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Accepted Solution

by:
nathans earned 200 total points
ID: 7095553
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
 
LVL 1

Author Comment

by:googlyralph
ID: 7095585
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
 
LVL 1

Author Comment

by:googlyralph
ID: 7095658
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
LVL 1

Author Comment

by:googlyralph
ID: 7095686
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
LVL 1

Author Comment

by:googlyralph
ID: 7095687
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
LVL 1

Author Comment

by:googlyralph
ID: 7095701
Perfect, always so simple when you have seen the solution.

Thankyou Kindly, GR.
0
 
LVL 6

Expert Comment

by:dash420
ID: 7095737
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
 
LVL 6

Expert Comment

by:dash420
ID: 7095756
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
 
LVL 5

Expert Comment

by:nathans
ID: 7095789
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question