Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
19 >= 19 - query error
Author: Adrian Lynch
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#238050
I'm not sure if the terms 'passive' and 'active' are correct, I think I
heard it somewhere, but...
... passive will generate code that you take and use somewhere, active will
mess with the actual code.
If anyone knows different, pipe up!
Adrian
>
> Nope, I use a passive code generator. Less risky :O)
Passive? Generation is generation, man! Heh. What do you mean?
"Incoming! (Heh. Not often is it the other guys yelling that)"
>
> No comment on the friendly fire! :O.
Oooo! Good point. Not as funny an idea tho, see...
"look out bad guys, a grenade is rolling your way!"
"You may want to duck, as I'm shooting at you!"
A callback to a bygone age. Like vaudville. Damn Sun Tzu...
[ = Sorry. Old war stuff on PBS, don't'cha know...
----- Excess quoted text cut - see Original Post for more -----
Author: Denny Valliant
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237974
Something that I like about the 1=1... It's pretty handy for getting rid of
those "WHERE blah blah" "AND blah blah" things that sometimes happen
when you have conditional WHERE stuff. Just put a WHERE 1=1, and,
the rest are ANDs.
Just a little tidbit that makes it easier when you do have to do that stuff.
If that even makes any sense. Sheesh. =P Must be a lazy day today.
:den
----- Excess quoted text cut - see Original Post for more -----
Author: Denny Valliant
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237973
> Nope, I use a passive code generator. Less risky :O)
Passive? Generation is generation, man! Heh. What do you mean?
"Incoming! (Heh. Not often is it the other guys yelling that)"
>
> No comment on the friendly fire! :O.
Oooo! Good point. Not as funny an idea tho, see...
"look out bad guys, a grenade is rolling your way!"
"You may want to duck, as I'm shooting at you!"
A callback to a bygone age. Like vaudville. Damn Sun Tzu...
[ = Sorry. Old war stuff on PBS, don't'cha know...
----- Excess quoted text cut - see Original Post for more -----
Author: Adrian Lynch
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237833
Nope, I use a passive code generator. Less risky :O)
"Incoming! (Heh. Not often is it the other guys yelling that)"
No comment on the friendly fire! :O.
>
> And to balance out the argument some, writing all the columns takes longer
> to write!
>
> Go on, fire away, I'm in my bunker and the flaps are down :OD
>
> Adrian
Incoming! (Heh. Not often is it the other guys yelling that)
Why wouldn't you write out columns? Isn't all your SQL generated? O.o
Surely you're not actually hard-coding names... right? You do have just
one place where if you change a field name, it updates the forms, the
SQL... basically everything where that name was referenced?
Then you can force your DB person use your CF code to manipulate
the DB. They love that. Really.
:Deni
----- Excess quoted text cut - see Original Post for more -----
Author: Denny Valliant
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237820
> And to balance out the argument some, writing all the columns takes longer
> to write!
>
> Go on, fire away, I'm in my bunker and the flaps are down :OD
>
> Adrian
Incoming! (Heh. Not often is it the other guys yelling that)
Why wouldn't you write out columns? Isn't all your SQL generated? O.o
Surely you're not actually hard-coding names... right? You do have just
one place where if you change a field name, it updates the forms, the
SQL... basically everything where that name was referenced?
Then you can force your DB person use your CF code to manipulate
the DB. They love that. Really.
:Deni
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237798
Adam Howitt wrote:
> This bug usually happens when you change something on the database
> server when you are using a prepared statement (cfqueryparam). The
> workarounds I can recommend are
> 1. Disable "maintain client connections" in the cf admin - less desirable
> 2. Change the query ever so slightly. I usually add an extra where
> clause : "AND 1=1" which has no performance penalty but makes the
> prepared statement recompile
That sounds like the answer! The problem didn't occur until I used
query analyzer to drop some columns from the table.
Rick
Author: Adrian Lynch
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237796
And to balance out the argument some, writing all the columns takes longer
to write!
Go on, fire away, I'm in my bunker and the flaps are down :OD
Adrian
Rick,
This is the easiest problem in the world to fix....
Don't use SELECT *.
Name the column in the select statement. Not only does this cut out your
problem, it also:
1. Makes the select statement more clear to anyone else reading.
2. Increases speed of the query.
3. Decreases the amount (most likely) of info that SQL has to transfer to
the CF memory space.
Cheers,
ben
.......................
Ben Nadel
www.nylontechnology.com
Sanders: Lightspeed too slow?
Helmet: Yes we'll have to go right to ludacris speed.
what the heck is this?
19 >= 19
The error occurred in
E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20
18 : A.*, B.name as PRMLNAME
19 : FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
20 : where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
21 : </CFQUERY>
22 : <cfoutput query="data">
It's worth noting that there are 18 columns in the table. I dropped another
column and ran the query again and I got "18 >= 18" (now there are only 17
columns)
here's the full query:
SELECT
A.*, B.name as PRMLNAME
FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
Rick
Author: Ben Nadel
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237794
Rick,
This is the easiest problem in the world to fix....
Don't use SELECT *.
Name the column in the select statement. Not only does this cut out your
problem, it also:
1. Makes the select statement more clear to anyone else reading.
2. Increases speed of the query.
3. Decreases the amount (most likely) of info that SQL has to transfer to
the CF memory space.
Cheers,
ben
......................
Ben Nadel
Web Developer
Nylon Technology
350 7th Ave.
Suite 1005
New York, NY 10001
212.691.1134 x 14
212.691.3477 fax
www.nylontechnology.com
Sanders: Lightspeed too slow?
Helmet: Yes we'll have to go right to ludacris speed.
what the heck is this?
19 >= 19
The error occurred in
E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20
18 : A.*, B.name as PRMLNAME
19 : FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
20 : where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
21 : </CFQUERY>
22 : <cfoutput query="data">
It's worth noting that there are 18 columns in the table. I dropped another
column and ran the query again and I got "18 >= 18" (now there are only 17
columns)
here's the full query:
SELECT
A.*, B.name as PRMLNAME
FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
Rick
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237776
Dan G. Switzer, II wrote:
>
> The error may be because you've used an ambigious column name of "id". Try:
>
> where A.id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#"
> />
That's interesting because the query worked exactly as it was until I
started dropping other columns. "Id" only exists in the dbo.results
table so this usually works.
I did solve the problem by specifying columns in my select list rather
than using A.* ... still have the "ambiguous" id field in the where clause.
Rick
Author: S. Isaac Dealey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237772
You may also eliminate this problem by changing the "max pooled
statements" value in your DSN to 0. I'm not certain of that, but I
know that the pooled statements are a form of SQL syntax caching and
that for most applications they're not very effective in improving
performance, so unlike disabling the "maintain client connections"
feature there's not much to lose. I have some recollection of having
the same sort of issues myself (change db structure outside cf, get
error from unchanged and still valid select statement in cf) and being
able to eliminate them that way.
----- Excess quoted text cut - see Original Post for more -----
Author: Adam Howitt
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237760
This bug usually happens when you change something on the database
server when you are using a prepared statement (cfqueryparam). The
workarounds I can recommend are
1. Disable "maintain client connections" in the cf admin - less desirable
2. Change the query ever so slightly. I usually add an extra where
clause : "AND 1=1" which has no performance penalty but makes the
prepared statement recompile
----- Excess quoted text cut - see Original Post for more -----
Author: Dan G. Switzer, II
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237754
Rick,
----- Excess quoted text cut - see Original Post for more -----
The error may be because you've used an ambigious column name of "id". Try:
where A.id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#"
/>
(I'm assuming the "id" column is in the results table.)
-Dan
Author: Adrian Lynch
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237753
What happens if you remove the cfqueryparam and run it again?
what the heck is this?
19 >= 19
The error occurred in
E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20
18 : A.*, B.name as PRMLNAME
19 : FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
20 : where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
21 : </CFQUERY>
22 : <cfoutput query="data">
It's worth noting that there are 18 columns in the table. I dropped
another column and ran the query again and I got "18 >= 18" (now there
are only 17 columns)
here's the full query:
SELECT
A.*, B.name as PRMLNAME
FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
Rick
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:45532#237751
what the heck is this?
19 >= 19
The error occurred in
E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20
18 : A.*, B.name as PRMLNAME
19 : FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
20 : where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
21 : </CFQUERY>
22 : <cfoutput query="data">
It's worth noting that there are 18 columns in the table. I dropped
another column and ran the query again and I got "18 >= 18" (now there
are only 17 columns)
here's the full query:
SELECT
A.*, B.name as PRMLNAME
FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email
where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
Rick
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
