Solved

Building SQL problem

Posted on 2002-06-20
12
160 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now