De nada, amigo. Teddy R. Payne, ACCFD Google Talk - [email protected]
On Wed, Jan 27, 2010 at 5:49 PM, Dusty Hale <[email protected]> wrote: > 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 >
