Solved

Building SQL problem

Posted on 2002-06-20
12
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 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