Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

I did end up re doing the entire query in a cfquery tag instead of the
connection string. I also used the queryparam tags like below. Seems to have
resolved the error and hopefully prevents SQL injection too.

Thanks

On Wed, Jan 27, 2010 at 5:30 PM, Teddy R. Payne wrote:

> Dusty,
> How about something like this?
>
>
>  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 [haircolor] like  cfsqltype="cf_sql_varchar" />
> 
>
> 
>
> 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  wrote:
>
>> I also stopped using the  tag and now it works:
>>
>> 
>> 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);
>> 

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

Yea that's a good point Dean. Will  tags solve that? Also if
using a connection string approach as opposed to a cfquery tag, what's the
best way to deal with SQLInjection?

I know what SQLinjection is and I'm sure I should probably be more concerned
with it that I have been in the past. Thanks for pointing that out and I'm
going to make it a point to do better in that area.

Dusty

On Wed, Jan 27, 2010 at 6:29 PM, Dean H. Saxe
wrote:

> D'oh!  Anyone else see the classic SQLinjection vuln here...
> --
> Dean H. Saxe
> "A true conservationist is a person who knows that the world is not
> given by his fathers, but borrowed from his children."  -- John James
> Audubon
>
>
>
> On Wed, Jan 27, 2010 at 11:47 AM, Dusty Hale 
> 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 
> > 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
> >>
> >>
> >>
> >> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale 
> wrote:
> >>>
> >>> Hi:
> >>>
> >>> I've run into a very strange issue. I have a cfc which has a 
> >>> 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 :   
> >>> 148 :datasource="#application.dsn_name#"
> >>> username="#application.db_user#" password="#application.db_pword#">
> >>> 149 :   #qText#
> >>>
> >>> 150 :   
> >>> -
> >>>
> >>> 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
> >
>
>
> -
> To unsubscribe from this list, manage your profile @
> http://www.acfug.org?falogin.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
> -
>
>
>
>

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[shawn gorrell]

Yep. But I knew that we could count on you to point it out... 

You could mitigate the possibility to a degree with a filter like my 
cf_xssblock tag, plus configuring the db to only allow select and exec. 

I actually used to include something very similar to that in my servertools app 
so you could run adhoc SQL from a form against any CF dsn, but then I thought 
about how bad that could be in the wrong hands and binned it. 





From: Dean H. Saxe 
To: discussion@acfug.org
Sent: Wed, January 27, 2010 6:29:03 PM
Subject: Re: [ACFUG Discuss] a very strange sql error that only happens when  
using CF

D'oh!  Anyone else see the classic SQLinjection vuln here...
--
Dean H. Saxe
"A true conservationist is a person who knows that the world is not
given by his fathers, but borrowed from his children."  -- John James
Audubon



On Wed, Jan 27, 2010 at 11:47 AM, Dusty Hale  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 
> 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
>>
>>
>>
>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale  wrote:
>>>
>>> Hi:
>>>
>>> I've run into a very strange issue. I have a cfc which has a 
>>> 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 : 
>>> 148 : >> username="#application.db_user#" password="#application.db_pword#">
>>> 149 : #qText#
>>>
>>> 150 : 
>>> -
>>>
>>> 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
>


-
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
-


-
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
-

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dean H. Saxe]

D'oh!  Anyone else see the classic SQLinjection vuln here...
--
Dean H. Saxe
"A true conservationist is a person who knows that the world is not
given by his fathers, but borrowed from his children."  -- John James
Audubon



On Wed, Jan 27, 2010 at 11:47 AM, Dusty Hale  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 
> 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
>>
>>
>>
>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale  wrote:
>>>
>>> Hi:
>>>
>>> I've run into a very strange issue. I have a cfc which has a 
>>> 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 :   
>>> 148 :   >> username="#application.db_user#" password="#application.db_pword#">
>>> 149 :   #qText#
>>>
>>> 150 :   
>>> -
>>>
>>> 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
>


-
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
-

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Teddy R. Payne]

De nada, amigo.

Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com



On Wed, Jan 27, 2010 at 5:49 PM, Dusty Hale  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
>
> 
>  
> 
>  
> 
> 
> 
>
> 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 wrote:
>
>> Dusty,
>> How about something like this?
>>
>>
>> > 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 [haircolor] like > cfsqltype="cf_sql_varchar" />
>> 
>>
>> 
>>
>> 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 wrote:
>>
>>> I also stopped using the  tag and now it works:
>>>
>>> 
>>> 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 Selec

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

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


 

 




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 wrote:

> Dusty,
> How about something like this?
>
>
>  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 [haircolor] like  cfsqltype="cf_sql_varchar" />
> 
>
> 
>
> 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  wrote:
>
>> I also stopped using the  tag and now it works:
>>
>> 
>> 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();
>>   

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Teddy R. Payne]

Dusty,
How about something like this?



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 [haircolor] like 




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  wrote:

> I also stopped using the  tag and now it works:
>
> 
> 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);
> 
>
>
> On Wed, Jan 27, 2010 at 4:18 PM, Forrest C. Gilmore 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:
>>
>> 
>>
>> 
>>
>> 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:
>>
>> 
>>

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

I also stopped using the  tag and now it works:


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);


On Wed, Jan 27, 2010 at 4:18 PM, Forrest C. Gilmore 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:
>
> 
>
> 
>
> 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:
>
> 
>
> 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 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>> wrote:
>>>
>>>Dusty,
>>>As a source of troubleshooting, have you put the SQL string in

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

So just to let you all know. I did find a solution.

I basically rolled my code back to the way it was yesterday because it
always worked before. Then I made the same little update I made today AGAIN
(just adding a reference to one other column in the query). Now it works.

I still don't for the life of me get how I can output the string of SQL and
run it fine in both a query analyzer and and in a cfquery tag yet when I
pass it in as a variable, CF adds additional single quotes on its own. That
is a new and very bizarre thing to see for me.

Well I can't figure out but I did at least get past it so I can move on but
still the curiosity is killing me.

Thanks to all that replied and have a great day and a great rest of the
week.

On Wed, Jan 27, 2010 at 3:53 PM, 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 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>> 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.
>>>
>>>>>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
>>>
>>>
>>>Does this run correctly?
>>>
>>>
>>>
>>>Teddy R. Payne, ACCFD
>>>Google Talk - teddyrpa...@gmail.com 
>>>
>>>
>>>
>>>
>>>On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale >>> 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
>>>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
>>>
>>>
>>>
>>>
>>>
>>>
>>>On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
>>>mailto:du...@climbonline.com>> wrote:
>>>
>>>Hi:
>>>
>>>I've run into a very strange issue. I have a cfc which
>>>has a  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
>>>
>>>   

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

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 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>> 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.
>>
>>>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
>>
>>
>>Does this run correctly?
>>
>>
>>
>>Teddy R. Payne, ACCFD
>>Google Talk - teddyrpa...@gmail.com 
>>
>>
>>
>>
>>On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale >> 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
>>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
>>
>>
>>
>>
>>
>>
>>On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
>>mailto:du...@climbonline.com>> wrote:
>>
>>Hi:
>>
>>I've run into a very strange issue. I have a cfc which
>>has a  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 :   
>>148 :   > datasource="#application.dsn_name#" username="#application.db_user#"
>> password="#application.db_pword#">
>>*149 :  #qText#*
>>
>>
>>
>>
>>
>>150 :   
>>-
>>
>>
>>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, Se

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

Very familiar with that function but in this case. That's not it. If I
remember correctly that function is to preserve single quotes that might be
in the string like:

'It's a problem'

in this case the string never has a quote:

select * from mydonors where donorid like '%ABC123%'



On Wed, Jan 27, 2010 at 3:36 PM, Forrest C. Gilmore 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>> 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.
>>
>>>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
>>
>>
>>Does this run correctly?
>>
>>
>>
>>Teddy R. Payne, ACCFD
>>Google Talk - teddyrpa...@gmail.com 
>>
>>
>>
>>
>>On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale >> 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
>>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
>>
>>
>>
>>
>>
>>
>>On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
>>mailto:du...@climbonline.com>> wrote:
>>
>>Hi:
>>
>>I've run into a very strange issue. I have a cfc which
>>has a  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 :   
>>148 :   > datasource="#application.dsn_name#" username="#application.db_user#"
>> password="#application.db_pword#">
>>*149 :  #qText#*
>>
>>
>>
>>
>>
>>150 :   
>>-
>>
>>
>>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, eyecol

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Forrest C. Gilmore]

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 > 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.


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


Does this run correctly?





Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com 



On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale 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
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





On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
mailto:du...@climbonline.com>> wrote:

Hi:

I've run into a very strange issue. I have a cfc which
has a  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 :   
148 :   
*149 :  #qText#*





150 :   
-
  



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.DustyHa

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

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 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.
>
>  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
> 
>
> Does this run correctly?
>
>
>
> Teddy R. Payne, ACCFD
> Google Talk - teddyrpa...@gmail.com
>
>
>
> On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale  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 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
>>>
>>>
>>>
>>>
>>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale wrote:
>>>
 Hi:

 I've run into a very strange issue. I have a cfc which has a 
 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 :  
 148 :  >>> username="#application.db_user#" password="#application.db_pword#">
 *149 : #qText#*




 150 :  
 -


 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
>>
>
>


-- 
Dusty Hale
Email: du...@dustyhale.com
Phone (Atlanta): 404.474.3754
Phone (Toll Free USA): 877.841.3370
Website: www.DustyHale.com

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

I agree with you because in the last part of the CF error message notice the
SQL state the quotes are doubled up. However, when I output the string there
not doubled up. I'm testing it out now.

Never seen anything like this in all my years LOL ...



 The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

  The following information is meant for the website developer for debugging
purposes.Error Occurred While Processing Request  Error Executing
Database Query.[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 :   
148 :   
*149 :  #qText#*
150 :   
151 :   

  --
   SQLSTATE   22012  SQLSelect 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
VENDORERRORCODE   8134  DATASOURCE   xytexcom_sql2005Resources:

   - Check the ColdFusion
documentationto verify
that you are using the correct syntax.
   - Search the Knowledge
Baseto find a solution
to your problem.

 Browser   Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.7)
Gecko/20091221 Firefox/3.5.7 (.NET CLR 3.5.30729)  Remote Address
65.13.120.212  Referrer   http://stage2.xytex.com/search_adv.cfm
Date/Time   27-Jan-10 02:01 PM Stack Trace   at
cfdonorsearch2ecfc665956255$funcDONORSEARCH._factor8(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:149)
at
cfdonorsearch2ecfc665956255$funcDONORSEARCH.runFunction(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:139)
at
cfsearch2ecfm984473465.runPage(D:\websites\xytexcom_stage2\htdocs\search.cfm:48)
at
cfdonorsearch2ecfc665956255$funcDONORSEARCH._factor8(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:149)
at
cfdonorsearch2ecfc665956255$funcDONORSEARCH.runFunction(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:139)
at
cfsearch2ecfm984473465.runPage(D:\websites\xytexcom_stage2\htdocs\search.cfm:48)


java.sql.SQLException: [Macromedia][SQLServer JDBC
Driver][SQLServer]Divide by zero error encountered.
at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source)
at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source)
at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown 
Source)
at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown 
Source)
at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at 
macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown
Source)
at macromedia.jdbc.base.BaseStatement.commonTransitionToState(Unknown 
Source)
at macromedia.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at macromedia.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at macromedia.jdbc.base.BaseStatement.executeInternal(Unknown Source)
at macromedia.jdbc.base.BaseStatement.execute(Unknown Source)
at 
coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348)
at coldfusion.sql.Executive.executeQuery(Executive.java:1210)
at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
at coldfusion.sql.Executive.executeQuery(Executive.java:939)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)
at 
cfdonorsearch2ecfc665956255$funcDONORSEARCH._factor8(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:149)
at 
cfdonorsearch2ecfc665956255$funcDONORSEARCH.runFunction(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:139)
at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:418)
at 
coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:360)
at 
coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:324)
at 
coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:56)
at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:277)
at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:46

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Teddy R. Payne]

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.


**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


Does this run correctly?



Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com



On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale  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 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
>>
>>
>>
>>
>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale wrote:
>>
>>> Hi:
>>>
>>> I've run into a very strange issue. I have a cfc which has a 
>>> 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 :   
>>> 148 :   >> username="#application.db_user#" password="#application.db_pword#">
>>> *149 :  #qText#*
>>>
>>>
>>>
>>> 150 :   
>>> -
>>>
>>>
>>> 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
>

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

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 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
>
>
>
>
> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale  wrote:
>
>> Hi:
>>
>> I've run into a very strange issue. I have a cfc which has a  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 :
>> 148 :> username="#application.db_user#" password="#application.db_pword#">
>> *149 :   #qText#*
>>
>>
>> 150 :
>> -
>>
>>
>> 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

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Craig . Nassal]

Sorry - thought I put it in my e-mail:

http://www.mail-archive.com/cf-t...@houseoffusion.com/msg214188.html

Craig Nassal

Federal Reserve Bank of Atlanta
1000 Peachtree St. N.E.
Atlanta, Ga 30309-4470
(404)498-8437
craig.nas...@atl.frb.org



Dusty Hale  
Sent by: ad...@acfug.org
01/27/10 02:44 PM
Please respond to
discussion@acfug.org


To
discussion@acfug.org
cc

Subject
Re: [ACFUG Discuss] a very strange sql error that only happens when using 
CF






Craig do you have a link to that thread?

Many thanks,
Dusty

On Wed, Jan 27, 2010 at 2:38 PM,  wrote:
Looking out on the internet, I came across an interesting article that
might point to the issue - take a look at the surrounding threads on here
for further info - not sure this will be the solution - just an
ideasounds the very same as what you're seeing.

Thanks,

Craig Nassal

Federal Reserve Bank of Atlanta
1000 Peachtree St. N.E.
Atlanta, Ga 30309-4470
(404)498-8437
craig.nas...@atl.frb.org



Dusty Hale 
Sent by: ad...@acfug.org
01/27/10 02:25 PM
Please respond to
discussion@acfug.org


To
discussion@acfug.org
cc

Subject
[ACFUG Discuss] a very strange sql error that only happens when using CF






Hi:

I've run into a very strange issue. I have a cfc which has a  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 :
148 :
149 :#qText#

150 :
-

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




-
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 http://www.fusionlink.com
-

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

Craig do you have a link to that thread?

Many thanks,
Dusty

On Wed, Jan 27, 2010 at 2:38 PM,  wrote:

> Looking out on the internet, I came across an interesting article that
> might point to the issue - take a look at the surrounding threads on here
> for further info - not sure this will be the solution - just an
> ideasounds the very same as what you're seeing.
>
> Thanks,
>
> Craig Nassal
>
> Federal Reserve Bank of Atlanta
> 1000 Peachtree St. N.E.
> Atlanta, Ga 30309-4470
> (404)498-8437
> craig.nas...@atl.frb.org
>
>
>
> Dusty Hale 
> Sent by: ad...@acfug.org
> 01/27/10 02:25 PM
> Please respond to
> discussion@acfug.org
>
>
> To
> discussion@acfug.org
> cc
>
> Subject
> [ACFUG Discuss] a very strange sql error that only happens when using CF
>
>
>
>
>
>
> Hi:
>
> I've run into a very strange issue. I have a cfc which has a  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 :
> 148 : datasource="#application.dsn_name#" username="#application.db_user#"
> password="#application.db_pword#">
> 149 :#qText#
>
> 150 :
> -
>
> 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
>
>
>
>
> -
> 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

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

No I just basically output the query before it gets to the cfquery tag. That
outputs it on the page instead of running the query in CF. Then I copied the
query into SQL Studio and it runs fine. However when I run the same query in
a cfquery tag I get the message. No division is being used.

The single quotes are just straight ASCII text. No evil  MS Word.

On Wed, Jan 27, 2010 at 2:28 PM, Teddy R. Payne 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
>
>
>
>
> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale  wrote:
>
>> Hi:
>>
>> I've run into a very strange issue. I have a cfc which has a  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 :
>> 148 :> username="#application.db_user#" password="#application.db_pword#">
>> *149 :   #qText#*
>>
>>
>> 150 :
>> -
>>
>>
>> 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

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Craig . Nassal]

Looking out on the internet, I came across an interesting article that 
might point to the issue - take a look at the surrounding threads on here 
for further info - not sure this will be the solution - just an 
ideasounds the very same as what you're seeing.

Thanks,

Craig Nassal

Federal Reserve Bank of Atlanta
1000 Peachtree St. N.E.
Atlanta, Ga 30309-4470
(404)498-8437
craig.nas...@atl.frb.org



Dusty Hale  
Sent by: ad...@acfug.org
01/27/10 02:25 PM
Please respond to
discussion@acfug.org


To
discussion@acfug.org
cc

Subject
[ACFUG Discuss] a very strange sql error that only happens when using CF






Hi:

I've run into a very strange issue. I have a cfc which has a  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 :
148 :
149 :#qText#

150 :
-

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




-
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
-

Re: [ACFUG Discuss] a very strange sql error that only happens when using CF

[Teddy R. Payne]

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



On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale  wrote:

> Hi:
>
> I've run into a very strange issue. I have a cfc which has a  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 : 
> 148 :  username="#application.db_user#" password="#application.db_pword#">
> *149 :#qText#*
>
> 150 : 
> -
>
>
> 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
>

[ACFUG Discuss] a very strange sql error that only happens when using CF

[Dusty Hale]

Hi:

I've run into a very strange issue. I have a cfc which has a  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 :   
148 :   
*149 :  #qText#*
150 :   
-


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