Hello folks,
I had a pretty strange experience with CFQueryParam today. Basically, I
have an innocent-looking query:
cfquery datasource=#application.myDB# name=qGetUser
*SELECT* username, password
*FROM* someTable
*WHERE* someID = cfqueryparam cfsqltype=CF_SQL_INTEGER value=
#cookie.theID
Qing Xia wrote:
I simply cannot fathom why conversion was done and why it was
necessary.
Presumably not necessary, definitely undesirable as you describe it.
But seeing that value 521636a makes me wonder if something is trying to
interpret the value as a hexadecimal for some reason?
cached prepared statements before.
~Brad
Original Message
Subject: my cfqueryparam grievance
From: Qing Xia txiasum...@gmail.com
Date: Tue, May 12, 2009 2:16 pm
To: cf-talk cf-talk@houseoffusion.com
Hello folks,
I had a pretty strange experience with CFQueryParam today
Start logging the value of COOKIE.theID. Just save it somewhere and see if
it's ever an unexpected value.
Adrian
-Original Message-
From: Qing Xia [mailto:txiasum...@gmail.com]
Sent: 12 May 2009 20:16
To: cf-talk
Subject: my cfqueryparam grievance
Hello folks,
I had
Ummm, not sure why your cookie.theID would shift, but I would say absolutely
that the CF_SQL_TYPE is designed to match the database column data type, not
the incoming variable parameter. The entire point of the CF_SQL_TYPE is to let
the JDBC driver handle the data pass-through for you in a
I have been searching for some explanation of
the different Types used in CFQueryParam.
understand that SCALE= is used to validate the position of decimal,
but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4.
I am using CFquery INSERT
do I need to use CFQueryParam
Hi Bob,
Check here for a list of the available options and their corresponding data
types for some major SQL providers (for some reason MySQL isn't there):
http://www.cfquickdocs.com/cf8/#cfqueryparam.
As for your question, generally using cfqueryparam is recommended for any
value that could
I have been searching for some explanation of
the different Types used in CFQueryParam.
understand that SCALE= is used to validate the position of decimal,
but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4.
These correspond with specific database field types. You'd need to
know
bobsh...@ntlworld.com wrote:
I have been searching for some explanation of
the different Types used in CFQueryParam.
understand that SCALE= is used to validate the position of decimal,
but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4.
I am using CFquery INSERT
do I need
This is the link to the function code in case it was not apparent from my
post... I really need to slow down. ;o)
http://coz.pastebin.com/f588cde23
G!
--
Gerald Guido
http://www.myinternetisbroken.com
http://www.cfsimple.org/
To invent, you need a good imagination and a pile of junk.
--
I've always been curious as to how cfqueryparam works. Does anyone know if
it just performs some scanning and filtering on the actual values of the
parameters passed to it or whether it somehow signals to the RDBMS that the
values are parameters to the query thereby treating an SQLI attack
I've always been curious as to how cfqueryparam works. Does anyone know if
it just performs some scanning and filtering on the actual values of the
parameters passed to it or whether it somehow signals to the RDBMS that the
values are parameters to the query thereby treating an SQLI attack
Dave Watts wrote:
it somehow signals to the RDBMS that the
values are parameters to the query thereby treating an SQLI attack as an
escaped string or something?
It builds a prepared statement. It doesn't scan or filter anything.
Thus the database knows the data is data and not
cfqueryparam works. Does anyone know
if
it just performs some scanning and filtering on the actual values of the
parameters passed to it or whether it somehow signals to the RDBMS that
the
values are parameters to the query thereby treating an SQLI attack as an
escaped string or something
cfqueryparam works. Does anyone know
if
it just performs some scanning and filtering on the actual values of the
parameters passed to it or whether it somehow signals to the RDBMS that
the
values are parameters to the query thereby treating an SQLI attack as an
escaped string or something
with Google
) that to do that within CFML I'd have to drop into Java and use the MySQL
Java API to achieve that?
On Thu, Apr 16, 2009 at 10:36 AM, Dave Watts dwa...@figleaf.com wrote:
I've always been curious as to how cfqueryparam works. Does anyone know
if
it just performs some scanning
And for all those reading this and using MSSQL, an inline statement would look
like this:
cfquery ...
declare @p1 nvarchar(50)
set @p1 = '#userSuppliedValue#'
select *
from tableName
where column = @p1
/cfquery
So, basically cfqueryparam is creating the Declare and Set for you
Actually, I believe cfqueryparam uses bindings, which effectively passes
parameters to the SQL engine. In your example, you are still open to SQL
injection attacks.
However, if you need to use your value several times, you can use declare /
set to define a variable in SQL, rather than using
Ah, yes, Francois, you are correct. I forgot to mention that in addition
to creating the @var parameters (which you can see in the CF debug output),
the CFQUERYPARAM also ensures that you don't get '; BAD SQL INJECTION'
stuff getting through into your SET @p1 = '#myUserVar#' expression
:::It builds a prepared statement. It doesn't scan or filter anything.
You could build a prepared statement yourself.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer
I get the error: Invalid data etc
Just a little thing to add here is that I believe you should
parametize all values in your query, whether user generated or
constant values or whatever. This is not for security but performance.
So:
SELECT fu
FROM bar
WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# /
AND live
/
Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer
I get the error: Invalid data etc for CFSQLTYPE CF_SQL_INTEGER.
Which leads me to believe it is being scanned/validated before being sent to
MySQL, and also makes me wonder whether cfqueryparam even uses prepared
getting cached. Not every value has
to be a parameter, however, the query just has to be exactly the same. So,
if it only ever looks like this:
SELECT fu
FROM bar
WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# /
AND live = 1
Then that will cache just fine, regardless of the value
Roger that re execution plans Jason, that makes perfect sense.
Dominc
2009/4/16 Jason Fisher ja...@wanax.com:
LECT fu
FROM bar
WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# /
AND live = cfqueryparam cf_sql_type=cf_sql_bit value=1
Well from what I read today it seems like the performance is improved for
reused prepared statements, which may not be how cfqueryparam is
implemented.
Prepared statements are stored and reused by name, passing in the values for
the parameters.
So if cfqueryparam is using prepared statements
Everything but your guess is correct ;)
Dominic
2009/4/16 David McGuigan davidmcgui...@gmail.com:
Well from what I read today it seems like the performance is improved for
reused prepared statements, which may not be how cfqueryparam is
implemented.
Prepared statements are stored
Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer
I get the error: Invalid data etc for CFSQLTYPE CF_SQL_INTEGER.
Which leads me to believe it is being scanned/validated before being sent to
MySQL, and also makes me wonder whether cfqueryparam even uses prepared
So if cfqueryparam is using prepared statements (which my guess would be
that it's not), cfqueryparamming all of your variable values would improve
performance because the statement itself isn't being re-sent to the RDMS on
each subsequent query, only the values. But if it's not, it could
I can't speak for MySQL, but in MSSQL, every query (not just prepared
statements) is processed into a Query Plan before processing. The server then
caches as many of these QPs as possible, so that repeated calls to the same
'query definition' do not have the overhead of having to re-generate
cffunctions.
Thanks again.
On Thu, Apr 16, 2009 at 1:33 PM, Dave Watts dwa...@figleaf.com wrote:
So if cfqueryparam is using prepared statements (which my guess would be
that it's not), cfqueryparamming all of your variable values would
improve
performance because the statement itself
if cfqueryparam is using prepared statements (which my guess would be
that it's not), cfqueryparamming all of your variable values would
improve
performance because the statement itself isn't being re-sent to the RDMS
on
each subsequent query, only the values. But if it's not, it could
actually
. This is worse than the fact that you can't use cfform
controls outside of a cfform tag in CFC cffunctions.
I don't believe it makes sense for CF to be able to do what you are
asking. Given a bare cfqueryparam .../, how is it to know what to do
with it?
Dominic
Sounds interesting :). Give all the googlers a bone and drop us a link
or an explanation...
2009/4/16 David McGuigan davidmcgui...@gmail.com:
Nevermind, you can totally hack cfquery! Woot. Thanks for all the help.
~|
Adobe®
)
cfqueryparam
cfsqltype=#ListGetAt(attributes.SQLTypeList,variables.LoopCounter)#
value=#ListGetAt(attributes.valueList,variables.LoopCounter)#
null=#YesNoFormat(not
Len(ListGetAt
So my real issue was that I wanted to super dynamically assemble the SQL and
then either execute it or do a variety of other things with it ( which I
won't go into ).
If you try to use cfqueryparam outside of a containing cfquery, ColdFusion
breaks.
So,
cfsavecontent
...cfqueryparam
Most excellent. Just what I was looking for. Thank you sir!
G!
On Sun, Mar 22, 2009 at 10:54 PM, Dave Watts dwa...@figleaf.com wrote:
BTW I was looking though my archive and I have a
bunch of MySQL databases with the data type being
Enum with either 0,1 or 'yes,no, true, false
as the
Does anyone have a list of the SQL datatypes for MySQL and MSSQL as they are
related to CfQueryParam cfsqltype when returned by cfdbinfo's TYPE_NAME?
I am building a function to tease those out and want to make sure I get as
many as I can matched up correctly. I thought I would ask here before
Try this for MSSQL: http://www.cfquickdocs.com/cf8/#cfqueryparam.
I have no idea why MySQL isn't on there, but it should at least get you
started.
Francois Levesque
http://blog.critical-web.com/
On Sun, Mar 22, 2009 at 7:38 PM, Gerald Guido gerald.gu...@gmail.comwrote:
Does anyone have
Thanx... I wan't paying enough much attention or I would have noticed that
Google gave me the 6.1 docs when I searched for CFSQLType
Thanx
G!
On Sun, Mar 22, 2009 at 7:59 PM, Francois Levesque cfab...@gmail.comwrote:
Try this for MSSQL: http://www.cfquickdocs.com/cf8/#cfqueryparam.
I
BTW I was looking though my archive and I have a
bunch of MySQL databases with the data type being
Enum with either 0,1 or 'yes,no, true, false
as the values. Seeing that enum can have more than
2 values and can be text values how do those
work with cfsqltype?
MySQL ENUM columns are
the arguments.login in cfqueryparam statements?
--
Scott Stewart
ColdFusion Developer
4405 Oakshyre Way
Raleigh, NC 27616
(h) 919.874.6229 (c) 703.220.2835
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic
They currently look like this:
cfprocparam type=in cfssqltype=cf_sql_varchar
value=#arguments.login#
null=no
Do I still need to wrap the arguments.login in cfqueryparam statements?
--
Scott Stewart
ColdFusion Developer
4405 Oakshyre Way
Raleigh, NC 27616
(h) 919.874.6229 (c) 703.220.2835
No, cfprocparam is equivalent to cfqueryparam.
Adrian
-Original Message-
From: Scott Stewart [mailto:sstwebwo...@bellsouth.net]
Sent: 06 March 2009 15:10
To: cf-talk
Subject: cfqueryparam with cfstoredprocparam
Hey all,
I'm calling a stored procedure using cfstoredproc
On Tuesday 16 Dec 2008, Mike Little wrote:
WHERE #PreserveSingleQuotes(boolsearch)#
Note that doesn't protect against SQL injection.
--
Tom Chiverton
Helping to dramatically reintermediate 24/7 low-risk cross-platform
applications
This
Try wrapping the QueryParams...
i.e.
Change (P.product_code LIKE cfqueryparam cfsqltype=cf_sql_varchar
value=%tents%)
to (P.product_code LIKE (cfqueryparam cfsqltype=cf_sql_varchar
value=%tents%))
HTH
2008/12/16 Mike Little m...@nzsolutions.co.nz:
hi guys,
getting an error using
It is as Dave suggested, you can not use CFQUERYPARAM as part
of the actual SQL query.
You are writting the WHERE clause as a variable, remove the queryparam from
that since you are declaring the variable
hi guys,
getting an error using the following syntax...
WHERE ((P.product_code LIKE cfqueryparam cfsqltype=cf_sql_varchar
value=%tents%) OR (PD.product_title LIKE cfqueryparam
cfsqltype=cf_sql_varchar value=%tents%) OR (PD.product_description LIKE
cfqueryparam cfsqltype=cf_sql_varchar value
What does the rendered query transaction look like? It should be
somewhere in the error dump.
Mike Little wrote:
hi guys,
getting an error using the following syntax...
WHERE ((P.product_code LIKE cfqueryparam cfsqltype=cf_sql_varchar
value=%tents%) OR (PD.product_title LIKE cfqueryparam
INNER JOIN categories_description CD ON C.cid = CD.cid
INNER JOIN brand B ON P.bid = B.bid
WHERE ((P.code LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)
OR (PD.ptitle LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)
OR (PD.pdesc LIKE cfqueryparam cfsqltype=cf_sql_varchar value
The generated SQL has the cfqueryparam tags in it There's something
wrong with that picture. How are you writing that query?
On Tue, Dec 16, 2008 at 3:13 PM, Mike Little wrote:
jake this is the actual dump...
SELECT P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle
ah, it comes from a function matt.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
in my query, i have...
WHERE #PreserveSingleQuotes(boolsearch)#
boolsearch is a string that is generated based on the search string.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get
in my query, i have...
WHERE #PreserveSingleQuotes(boolsearch)#
boolsearch is a string that is generated based on the search string.
You can only use CFQUERYPARAM within a query. You can't build a string
with CFQUERYPARAM, then use it in a query.
Dave Watts, CTO, Fig Leaf Software
http
Question... the more I read about CFQUERYPARAM the more it seems it
mitigates many of the problems that using stored procedures also solves. I
realize with stored procedures you have a lot more power in terms of SQL
scripting, but for basic queries, is CFQUERYPARAM just as fast as (or faster
than
: cfqueryparam vs cfstoredproc?
Question... the more I read about CFQUERYPARAM the more it seems it
mitigates many of the problems that using stored procedures also solves. I
realize with stored procedures you have a lot more power in terms of SQL
scripting, but for basic queries, is CFQUERYPARAM just as fast
We call stored procedures using cfqueryparam:
cfquery datasource=DSN name=SomeQuery
EXEC StoredProcName
@ParamName = cfqueryparam cfsqltype=cf_sql_integer
value=#session.value#
/cfquery
On Thu, Oct 30, 2008 at 10:49 AM, Marie Taylore [EMAIL PROTECTED]wrote:
Question... the more I read about
The only issue I have run into with CFQUERYPARAM is that is can degrade
performance on dynamic queries.
This is an inherent issue in what CFQUERYPARAM does, it essentially makes
your queries into stored procs, if you actually watch the traffic flow
through a MS SQL Server for example, you will see
Marie,
In my experience with SQL Server there is zero notable performance difference
between well-formed SQL in a stored proc and the same well-formed SQL in a
CFQUERY with CFQUERYPARAM: both gain from the built-in performance tuning of
the data server. Also, note that you can run nearly any
Original Message
Subject: cfqueryparam vs cfstoredproc?
From: Marie Taylore [EMAIL PROTECTED]
I realize with stored procedures you have a lot more power in terms of SQL
scripting,
This is not really true. You can put anything you want in a cfquery
block. temp tables, CTE's
Let's hope you don't ever need to handle more than one result set. :)
Also, that requires you get the return code manually as well.
~Brad
Original Message
Subject: Re: cfqueryparam vs cfstoredproc?
From: morgan l [EMAIL PROTECTED]
We call stored procedures using
query
plan, and that increases performance. Without using CFQUERYPARAM at all, every
instance of the query is 'new' and that should (in theory) kill your
performance, not boost it. In other words, the following queries would have
the same plan with params but are each 'new' and distinct without
performance. Without using CFQUERYPARAM at
all, every instance of the query is 'new' and that should (in theory) kill
your performance, not boost it. In other words, the following queries would
have the same plan with params but are each 'new' and distinct without
params:
Query with last name
Interesting, indeed. Wonder if there's an issue of table scan vs index and how
the initial execution plans are getting cached. Definitely something to keep
your eye on!
Interesting...
I thought the same thing until I ran these tests. I analyzed the results
with ColdFusion debugging output,
Something of note... If you are using cfqueryparam all of the variables
need to be bound. Leaving any variable hanging out there will not allow
you to take advantage of the execution plan - even if it's a constant.
This query
cfquery
SELECT col1,col2
FROMusers
WHERE
benefits as
CFQUERYPARAM.
I'm a big believer in doing database things on the database and display
stuff in the web server. I'll confess though that I don't use CF much
anymore except for CFCs-- most everything I do is in Flex
)
#column1#
cfqueryparam cfsqltype=CF_SQL_INTEGER
value=#ARGUMENTS.column1#
cfset separator = ,
/cfif
cfif StructKeyExists(ARGUMENTS, column2
steps to paramaterize it. (requires
sp_executesql)
FYI: My advice assumes MS SQL.
~Brad
Original Message
Subject: RE: cfqueryparam vs cfstoredproc?
From: Adrian Lynch [EMAIL PROTECTED]
Date: Thu, October 30, 2008 1:06 pm
To: cf-talk cf-talk@houseoffusion.com
An open question
/
-Original Message-
From: [EMAIL PROTECTED]
Sent: 30 October 2008 18:28
To: cf-talk
Subject: RE: cfqueryparam vs cfstoredproc?
exec()
or sp_executesql
You would need to pass in the arguments as a list to the procedure and
then do the looping and building of a dynamic query with SQL
:
http://adrianlynch.co.uk/post.cfm?postID=21
Adrian
Building a database of ColdFusion errors at http://cferror.org/
-Original Message-
From: [EMAIL PROTECTED]
Sent: 30 October 2008 18:28
To: cf-talk
Subject: RE: cfqueryparam vs cfstoredproc?
exec()
or sp_executesql
You would need
PROTECTED]
Sent: 30 October 2008 18:28
To: cf-talk
Subject: RE: cfqueryparam vs cfstoredproc?
exec()
or sp_executesql
You would need to pass in the arguments as a list to the procedure and
then do the looping and building of a dynamic query with SQL. Then
execute what you have created.
Good
EXEC()ing a string won't produde the same execution plan as the base SQL
(--- a guess) and you lose cfqueryparam and cfprocparam's biggest
benefit, protecting against injection.
Adrian
-Original Message-
From: Aaron Rouse
Sent: 30 October 2008 19:52
To: cf-talk
Subject: Re: cfqueryparam
in to hopefully avoid the possibility of a SQL injection attack.
On Thu, Oct 30, 2008 at 2:57 PM, Adrian Lynch [EMAIL PROTECTED]wrote:
EXEC()ing a string won't produde the same execution plan as the base SQL
(--- a guess) and you lose cfqueryparam and cfprocparam's biggest
benefit, protecting
I have a variable passed though a URL that looks like this
index.cfm?a=1,2,3
The variable a is passed to a SQL statement WHERE clause as
part of an IN operator
IE: WHERE b in (#url.a#).
How would I encapsulate url.a in a CFQueryParam properly, is this a
case where I wouldn't define
cfqueryparam cfsqltype=cf_sql_integer value=#url.a# list=true
separator=,
Obviously you would need to change the sqltype to what your sql type is.
On Tue, Oct 28, 2008 at 10:13 AM, Scott Stewart [EMAIL PROTECTED]wrote:
I have a variable passed though a URL that looks like this
index.cfm
WHERE b IN ( cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#url.a#
list=yes )
The list=yes parameter of CFQUERYPARAM will properly wrap the single quotes
around each list element.
-Jason
I have a variable passed though a URL that looks like this
index.cfm?a=1,2,3
The variable a is passed
cfqp has a list attribute.
Adrian
Building a database of ColdFusion errors at http://cferror.org/
-Original Message-
From: Scott Stewart
Sent: 28 October 2008 17:13
To: cf-talk
Subject: CFQueryParam question
I have a variable passed though a URL that looks like this
index.cfm?a=1,2,3
Try this:
cfqueryparam value=#Trim(URL.a)# cfsqltype=cf_sql_varchar
list=true /
Thanks,
Eric Cobb
Scott Stewart wrote:
I have a variable passed though a URL that looks like this
index.cfm?a=1,2,3
The variable a is passed to a SQL statement WHERE clause as
part of an IN operator
IE
The list=yes parameter of CFQUERYPARAM will properly wrap the single quotes
around each list element.
That's not quite right. What it will do is create a parameter for
every element in the list and map it to the database type you supply.
Even with character data, no single quotes are used when
Hello -
I'm reading conflicting reports and wanted to get some clarification. Adobe's
website (CF8) says that you cannot use the cachedAfter and cachedWithin
while using cfqueryparam. Is this true? I'm using CF8.
Adobe says no but lots of users say yes.
http://livedocs.adobe.com/coldfusion
you can in cf8
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Hunsaker, Michael Scott wrote:
Hello -
I'm reading conflicting reports and wanted to get some clarification.
Adobe's website (CF8) says that you cannot use the cachedAfter and
cachedWithin while using cfqueryparam
Add to that list, locally scope the query.
cfset var get =
Adrian
-Original Message-
From: Peter Boughton [mailto:[EMAIL PROTECTED]
Sent: 01 October 2008 00:01
To: cf-talk
Subject: Re: cfqueryparam within a cfc
cfcomponet
cffunction name=function
cfargument name=field_value type
Add to that list, locally scope the query.
cfset var get =
Very good point - probably the most critical change to make.
I can't believe I missed that. :'(
~|
Adobe® ColdFusion® 8 software 8 is the most important and
: cfqueryparam within a cfc
Add to that list, locally scope the query.
cfset var get =
Very good point - probably the most critical change to make.
I can't believe I missed that. :'(
~|
Adobe® ColdFusion® 8 software 8 is the most
I have columns in MySQL database containing boolean values, using a data
type of unsigned tinyint. Would the cfsqltype of cf_sql_tinyint or
cf_sql_bit be best to use? If using cf_sql_bit what does cfqueryparam do
in that case with values such as -1 or 3 - will it cast them to 1/0
Hello -
We are consistently using the CFQUERYPARAM tag in our code but not within out
CFCs. Here's a quick example:
cfcomponet
cffunction name=function
cfargument name=field_value type=numeric required=yes
cfquery name=get
SELECT * FROM TABLE WHERE field = cfqueryparam value
Just use it all the time, then it's never an issue.
Plus it does more than just protect against SQL injection.
Adrian
-Original Message-
From: Hunsaker, Michael Scott [mailto:[EMAIL PROTECTED]
Sent: 30 September 2008 21:21
To: cf-talk
Subject: cfqueryparam within a cfc
Hello -
We
No it's not overkill. You could be passing in the argument as a form or url
scoped variable for example:
cfinvoke component=mycomponent
method=function
field_value=#url.field_value#
If you just cfqueryparam everything you don't have to worry about it.
-- Josh
- Original
I have columns in MySQL database containing boolean values, using a data
type of unsigned tinyint. Would the cfsqltype of cf_sql_tinyint or
cf_sql_bit be best to use? If using cf_sql_bit what does cfqueryparam do
in that case with values such as -1 or 3 - will it cast them to 1/0
cfcomponet
cffunction name=function
cfargument name=field_value type=numeric required=yes
cfquery name=get
SELECT * FROM TABLE WHERE field = cfqueryparam value=#field_value#
cfsqltype=cf_sql_integer
/cfquery
/cffunction
/cfcomponent
Is this over-kill or good practice?
Use
My original query code below, which has been running for months with no
error, suddenly start throwing an unspecified database error today, plus
actually timed out once, and the server reset in the middle of the query
another time:
Select ... from ...
WHERE ml_id IN
(cfqueryparam
value
Mizzell [mailto:[EMAIL PROTECTED]
Sent: 15 September 2008 21:37
To: CF-Talk
Subject: cfqueryparam - couple ofquestions from a problem that occured
today...
My original query code below, which has been running for months with no
error, suddenly start throwing an unspecified database error today
Maybe you hit a limit in the number of items
valuelist or cfqueryparam could handle.
try building the string outside the query with
valuelist, to see if the valuelist triggers an error..
then use that string with cfqueryparam in your
query and if it doesn't work, try the string
(just once
I don't see where you specified the database you're using, but SQL Server
will allow a limit of 2,100 records be used in the IN statement. I'm
assuming that MySQL and other db servers have a similar limitation in place.
~|
not have that option...
~Brad
Original Message
Subject: cfqueryparam - couple ofquestions from a problem that occured
today...
From: Les Mizzell [EMAIL PROTECTED]
Date: Mon, September 15, 2008 3:36 pm
To: CF-Talk cf-talk@houseoffusion.com
My original query code below, which
I don't see where you specified the database you're using, but SQL Server
will allow a limit of 2,100 records be used in the IN statement.
It is SQL server.
But, why did CFQUERYPARAM fail, and this works:
WHERE ml_id IN
(cfoutput query=getGROUP
cfif getGroup.CurrentRow NEQ 1,/cfif
It failed because with cfqueryparam you're binding thousands of
variables into the statement as parameters, which is a Very Bad Thing
(TM).
If it's all against the same DB, use a subquery for your IN clause. It
should perform much better than either of the current solutions.
On Tue, Sep 16, 2008
For this:
cfqueryparam
value=#myFORM.myVAR#
cfsqltype=CF_SQL_CHAR
maxlength=1,
Two questions:
1. If #myFORM.myVAR# doesn't exist, what's the best way to set a null?
This?
cfqueryparam
value=#myFORM.myVAR#
cfsqltype=CF_SQL_CHAR
maxlength=1
null = #YesNoFormat
For this:
cfqueryparam
value=#myFORM.myVAR#
cfsqltype=CF_SQL_CHAR
maxlength=1,
Two questions:
1. If #myFORM.myVAR# doesn't exist, what's the best way to set a null?
This?
cfqueryparam
value=#myFORM.myVAR#
cfsqltype=CF_SQL_CHAR
maxlength=1
null
[mailto:[EMAIL PROTECTED]
Sent: 12 September 2008 17:01
To: CF-Talk
Subject: A Little Confusion on CFQUERYPARAM and Nulls...
For this:
cfqueryparam
value=#myFORM.myVAR#
cfsqltype=CF_SQL_CHAR
maxlength=1,
Two questions:
1. If #myFORM.myVAR# doesn't exist, what's the best way to set
, 2008 at 12:00 PM, Les Mizzell [EMAIL PROTECTED] wrote:
For this:
cfqueryparam
value=#myFORM.myVAR#
cfsqltype=CF_SQL_CHAR
maxlength=1,
Two questions:
1. If #myFORM.myVAR# doesn't exist, what's the best way to set a null?
This?
cfqueryparam
value=#myFORM.myVAR#
cfsqltype
201 - 300 of 1538 matches
Mail list logo
