Teddy R. Payne
Wed, 27 Jan 2010 14:53:10 -0800
De nada, amigo.
Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com
On Wed, Jan 27, 2010 at 5:49 PM, Dusty Hale <du...@climbonline.com> 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 <teddyrpa...@gmail.com>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 - teddyrpa...@gmail.com
>>
>>
>>
>> On Wed, Jan 27, 2010 at 4:25 PM, Dusty Hale <du...@climbonline.com>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
>>> <fcg0...@wctel.net>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
>>>> <fcg0...@wctel.net>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 <
>>>>>> teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com>> 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 - teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale <du...@climbonline.com
>>>>>> <mailto:du...@climbonline.com>> 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
>>>>>> <teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com>> 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 - teddyrpa...@gmail.com
>>>>>> <mailto:teddyrpa...@gmail.com>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
>>>>>> <du...@climbonline.com <mailto:du...@climbonline.com>>
>>>>>> 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: du...@dustyhale.com
>>>>>> Phone (Atlanta): 404.474.3754
>>>>>> Phone (Toll Free USA): 877.841.3370
>>>>>> Website: www.DustyHale.com <http://www.DustyHale.com>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Dusty Hale
>>>>>> Email: du...@dustyhale.com
>>>>>> 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: du...@dustyhale.com
>>>> 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: du...@dustyhale.com
>>> Phone (Atlanta): 404.474.3754
>>> Phone (Toll Free USA): 877.841.3370
>>> Website: www.DustyHale.com
>>>
>>
>>
>
>
> --
> Dusty Hale
> Email: du...@dustyhale.com
> Phone (Atlanta): 404.474.3754
> Phone (Toll Free USA): 877.841.3370
> Website: www.DustyHale.com
>