I may have to do that because now I have another issue LOL. While using the
connection string approach, it works great on our production server. However
I can't for the life of me get connections strings like this to work on my
development server (Mac Book with VMWare Win XP CF 8) so I'm likely going to
do something like
<cfif "production server">
<!--- use connection string approach ---->
<cfelseif "my Mac Book">
<!--- use cfquery tag --->
<cfelse>
<!--- give up and go to the nearest bar and begin drinking vodka shots
--->
</cfif>
Muchos Gracias Teddy. I'm knockin off but will try this in the morning.
On Wed, Jan 27, 2010 at 5:30 PM, Teddy R. Payne <[email protected]>wrote:
> Dusty,
> How about something like this?
>
>
> <cfquery name="q"
> datasource="#application.dsn_name#"
> username="#application.db_user#"
> password="#application.db_pword#">
>
> select
> [donorid] ,
> [occupation] ,
> [race] ,
> [haircolor] ,
> [hairtexture] ,
> [eyecolor] ,
> [religion] ,
> [bloodtype] ,
> [height] ,
> [weight] ,
> [heightmetric] ,
> [weightmetric] ,
> [reportedpregnancy] ,
> [opendonorid] ,
> [infomp3avail] ,
> [ethnicity] ,
> [cmvstatus] ,
> [DateEntered] ,
> [ARTavail] ,
> [ARTonly] ,
> [SelectDonors]
> from
> [v_websearch]
> where
> [available] = 1
> <cfif len(drpHairColor)>
> and [haircolor] like <cfqueryparam value="#drpHairColor#%"
> cfsqltype="cf_sql_varchar" />
> </cfif>
>
> </cfquery>
>
> Qualify the columns and the view and query param the search string. This
> may also add some legibility as well.
>
>
>
> Teddy R. Payne, ACCFD
> Google Talk - [email protected]
>
>
>
> On Wed, Jan 27, 2010 at 4:25 PM, Dusty Hale <[email protected]> wrote:
>
>> I also stopped using the <cfquery> tag and now it works:
>>
>> <cfscript>
>> classLoader = createObject("java", "java.lang.Class");
>> classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
>> dm = createObject("java","java.sql.DriverManager");
>>
>> con = dm.getConnection("jdbc:odbc:DRIVER={SQL Server};Database=" &
>> application.db_name & ";Server=" & application.dbserver_name & ";",
>> application.db_user, application.db_pword);
>>
>> qText = "Select donorid, occupation, race, haircolor, hairtexture,
>> eyecolor, religion, bloodtype, height, weight, heightmetric, weightmetric,
>> reportedpregnancy, opendonorid, infomp3avail, ethnicity, cmvstatus,
>> DateEntered, ARTavail, ARTonly, SelectDonors FROM v_websearch where
>> available = 1 ";
>> if(len(drpHairColor)){
>> qText = qText & "AND haircolor like '" & drpHairColor & "%' ";
>> }
>> if(len(drpEyeColor)){
>> qText = qText & "AND eyecolor like '" & drpEyeColor & "%' ";
>> }
>> if(len(drpEthnic)){
>> qText = qText & "AND ethnicity like '" & drpEthnic & "%' ";
>>
>> }
>> if(len(txtDonorId)){
>> qText = qText & "AND donorid like '%" & txtDonorId & "%' ";
>> }
>> //if(chkAudioFile){
>> //qText = qText & "AND InfoMP3Avail = 1 ";
>> //}
>> if(chkOpenId){
>> qText = qText & "AND OpenDonorID = 1 ";
>> }
>> if(chkCanadian){
>> qText = qText & "AND CanadianCompliantAvail = 1 ";
>> }
>> if(len(drpRace)){
>> qText = qText & "AND race like '" & drpRace & "%' ";
>> }
>> if(len(drpReligion)){
>> qText = qText & "AND religion like '" & drpReligion & "%' ";
>> }
>> if(len(drpBloodType)){
>> qText = qText & "AND bloodtype like '" & drpBloodType & "%' ";
>> }
>> if(len(drpHeight)){
>> if(drpHeight eq "1-66"){
>> qText = qText & "AND heightregular < 507 ";
>> }
>> if(drpHeight eq "67-71"){
>> qText = qText & "AND heightregular < 512 AND heightregular >
>> 507 ";
>> }
>> if(drpHeight eq "72-76"){
>> qText = qText & "AND heightregular < 605 AND heightregular >
>> 512 ";
>> }
>> if(drpHeight eq "77-100"){
>> qText = qText & "AND heightregular > 604 ";
>> }
>> }
>> if(IsNumeric(txtWeight1) AND IsNumeric(txtWeight2)){
>> if(chkWeight is "kg"){
>> qText = qText & " AND weightmetric <= " & txtWeight2 & " AND
>> weightmetric >= " & txtWeight1 & " ";
>> }
>> else{
>> qText = qText & " AND weight <= " & txtWeight2 & " AND weight
>> >= " & txtWeight1 & " ";
>> }
>> }
>> if(len(drpCMVstatus)){
>> qText = qText & "AND cmvstatus like '" & drpCMVstatus & "%' ";
>> }
>> if(chkART){
>> qText = qText & "AND ARTavail IS NOT NULL ";
>> }
>> if(chkSelect){
>> qText = qText & "AND SelectDonors IS NOT NULL ";
>> }
>> qText = qText & "ORDER BY donorid";
>> st = con.createStatement();
>> rs = st.ExecuteQuery(qText);
>> q = createObject("java", "coldfusion.sql.QueryTable").init(rs);
>>
>> stRecordCount = con.createStatement();
>> rs2 = stRecordCount.ExecuteQuery("Select count(*) AS MyCount FROM
>> tbldonors where available = 1");
>> qRecordCount = createObject("java",
>> "coldfusion.sql.QueryTable").init(rs2);
>> </cfscript>
>>
>>
>> On Wed, Jan 27, 2010 at 4:18 PM, Forrest C. Gilmore <[email protected]>wrote:
>>
>>> Can you show me the exact code you used when you tried
>>> PreserveSingleQuotes?
>>> According to the documentation, "This function is useful in SQL
>>> statements to defer evaluation of a variable reference until runtime. "
>>> Seems to be just what you need, but it must be used properly.
>>>
>>> *Example B*: Consider this code:
>>>
>>> <cfset list0 = " '1','2''3' ">
>>>
>>> <cfquery sql = "select * from foo where bar in (#list0#)">
>>>
>>> ColdFusion escapes the single-quote characters in the list as follows:
>>>
>>> ""1"", ""2"", ""3""
>>>
>>> The cfquery tag throws an error.
>>>
>>> You code this function correctly as follows:
>>>
>>> <cfquery sql = "select * from foo where bar in
>>> (#preserveSingleQuotes(list0)#)">
>>>
>>> This function ensures that ColdFusion evaluates the code as follows:
>>>
>>> '1', '2', '3'
>>> --------------------------------------
>>>
>>> Forrest C. Gilmore
>>> ===========================
>>> Dusty Hale wrote:
>>>
>>> Just to rule it out though I tried it. Didn't help though.
>>>
>>>
>>> On Wed, Jan 27, 2010 at 3:36 PM, Forrest C. Gilmore
>>> <[email protected]>wrote:
>>>
>>>> Go into CF Help and look up the Preservesinglequotes function.
>>>>
>>>> Forrest C. Gilmore
>>>> ========================
>>>> Dusty Hale wrote:
>>>>
>>>>> Yep that runs fine.
>>>>>
>>>>> This is really strange. The value of the qText variable is the same as
>>>>> the query below yet when I stuff #qText# in there, it somehow doubles the
>>>>> quotes by itself. Damn I just don't get how it could or would do that.
>>>>>
>>>>> If I output the value of the qText variable on a page, quotes are
>>>>> correct. Have you ever seen anything like this?
>>>>>
>>>>> Thanks,
>>>>> Dusty
>>>>>
>>>>> On Wed, Jan 27, 2010 at 3:00 PM, Teddy R. Payne <
>>>>> [email protected] <mailto:[email protected]>> wrote:
>>>>>
>>>>> Dusty,
>>>>> As a source of troubleshooting, have you put the SQL string into
>>>>> the cfquery statement in its final form? I am wondering if the
>>>>> SQL runs correctly in the cfquery before the dynamic evaluation.
>>>>>
>>>>> <cfquery name="q" datasource="#application.dsn_name#"
>>>>> username="#application.db_user#" password="#application.db_
>>>>>
>>>>> pword#">
>>>>> Select donorid, occupation, race, haircolor, hairtexture,
>>>>> eyecolor, religion, bloodtype, height, weight, heightmetric,
>>>>> weightmetric, reportedpregnancy, opendonorid, infomp3avail,
>>>>> ethnicity, cmvstatus, DateEntered, ARTavail, ARTonly, SelectDonors
>>>>> FROM v_websearch where available = 1 AND donorid like '%9986%'
>>>>> ORDER BY donorid
>>>>> </cfquery>
>>>>>
>>>>> Does this run correctly?
>>>>>
>>>>>
>>>>>
>>>>> Teddy R. Payne, ACCFD
>>>>> Google Talk - [email protected] <mailto:[email protected]>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale <[email protected]
>>>>> <mailto:[email protected]>> wrote:
>>>>>
>>>>> Teddy here's how I build the qText string part where the
>>>>> quotes are:
>>>>>
>>>>> if(len(txtDonorId)){
>>>>> qText = qText & "AND donorid like '%" & txtDonorId &
>>>>> "%' ";
>>>>> }
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Jan 27, 2010 at 2:28 PM, Teddy R. Payne
>>>>> <[email protected] <mailto:[email protected]>> wrote:
>>>>>
>>>>> Dusty,
>>>>> What type of single quotes are those? What is the source
>>>>> of the text? Was the query copied and pasted from a
>>>>> Microsoft document?
>>>>>
>>>>>
>>>>> Teddy R. Payne, ACCFD
>>>>> Google Talk - [email protected]
>>>>> <mailto:[email protected]>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
>>>>> <[email protected] <mailto:[email protected]>>
>>>>> wrote:
>>>>>
>>>>> Hi:
>>>>>
>>>>> I've run into a very strange issue. I have a cfc which
>>>>> has a <cfquery> tag in it. I recently added one field
>>>>> the SQL in the query and am getting an error I've
>>>>> never seen before. I can't seem to dig out any info to
>>>>> solve this. If anyone is familiar, please share.
>>>>>
>>>>> Of course when I output the SQL and run in a SQL
>>>>> Studio Query window, the query runs fine with no errors.
>>>>>
>>>>> Here the error I see in CF:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -----------------------------------------------------------------------------------------------------------------------------
>>>>> [Macromedia][SQLServer JDBC Driver][SQLServer]Divide
>>>>> by zero error encountered.
>>>>>
>>>>> The error occurred in
>>>>> *D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>>>> line 149*
>>>>> *Called from*
>>>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>>>> line 139
>>>>> *Called from*
>>>>> D:\websites\xytexcom_stage2\htdocs\search.cfm: line 48
>>>>> *Called from*
>>>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>>>> line 149
>>>>> *Called from*
>>>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>>>> line 139
>>>>> *Called from*
>>>>> D:\websites\xytexcom_stage2\htdocs\search.cfm: line 48
>>>>>
>>>>> 147 : </cfscript>
>>>>> 148 : <cfquery name="q"
>>>>> datasource="#application.dsn_name#" username="#application.db_user#"
>>>>> password="#application.db_pword#">
>>>>> *149 : #qText#*
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> 150 : </cfquery>
>>>>> -----------------------------------------
>>>>>
>>>>>
>>>>> Here's the SQL code in the qText variable. Please note
>>>>> that it runs fun in Query Analyzer. Also note that no
>>>>> division is being used.
>>>>>
>>>>>
>>>>> ----------------------------------------------------------------
>>>>> Select donorid, occupation, race, haircolor,
>>>>> hairtexture, eyecolor, religion, bloodtype, height,
>>>>> weight, heightmetric, weightmetric, reportedpregnancy,
>>>>> opendonorid, infomp3avail, ethnicity, cmvstatus,
>>>>> DateEntered, ARTavail, ARTonly, SelectDonors FROM
>>>>> v_websearch where available = 1 AND donorid like
>>>>> '%9986%' ORDER BY donorid
>>>>>
>>>>>
>>>>> ----------------------------------------------------------------------------------
>>>>>
>>>>> Any advise or thought on this of course is greatly
>>>>> appreciated.
>>>>>
>>>>> Dusty
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -- Dusty Hale
>>>>> Email: [email protected]
>>>>> Phone (Atlanta): 404.474.3754
>>>>> Phone (Toll Free USA): 877.841.3370
>>>>> Website: www.DustyHale.com <http://www.DustyHale.com>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Dusty Hale
>>>>> Email: [email protected]
>>>>> Phone (Atlanta): 404.474.3754
>>>>> Phone (Toll Free USA): 877.841.3370
>>>>> Website: www.DustyHale.com <http://www.DustyHale.com>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>> -------------------------------------------------------------
>>>> To unsubscribe from this list, manage your profile @
>>>> http://www.acfug.org?fa=login.edituserform
>>>>
>>>> For more info, see http://www.acfug.org/mailinglists
>>>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
>>>> List hosted by http://www.fusionlink.com
>>>> -------------------------------------------------------------
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Dusty Hale
>>> Email: [email protected]
>>> Phone (Atlanta): 404.474.3754
>>> Phone (Toll Free USA): 877.841.3370
>>> Website: www.DustyHale.com
>>>
>>>
>>>
>>> -------------------------------------------------------------
>>> To unsubscribe from this list, manage your profile @
>>> http://www.acfug.org?fa=login.edituserform
>>>
>>> For more info, see http://www.acfug.org/mailinglists
>>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
>>> List hosted by FusionLink <http://www.fusionlink.com>
>>> -------------------------------------------------------------
>>
>>
>>
>>
>> --
>> Dusty Hale
>> Email: [email protected]
>> Phone (Atlanta): 404.474.3754
>> Phone (Toll Free USA): 877.841.3370
>> Website: www.DustyHale.com
>>
>
>
--
Dusty Hale
Email: [email protected]
Phone (Atlanta): 404.474.3754
Phone (Toll Free USA): 877.841.3370
Website: www.DustyHale.com
