Solved

Building SQL problem

Posted on 2002-06-20
12
165 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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 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