RE: Execute Query string w CFQUERYPARAM

[Dave Watts]

> So in CF 4 they transfered the query to a JDBC statement?

No, ODBC also supports prepared statements with bind parameters.
 
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444 


~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=35

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182437
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Execute Query string w CFQUERYPARAM

[Aaron Rouse]

Dave,

So in CF 4 they transfered the query to a JDBC statement?   


On Fri, 22 Oct 2004 19:47:54 -0400, Dave Watts <[EMAIL PROTECTED]> wrote:
> > I'd assume that is something that started at a certain version
> > of CF?
> 
> It started when the CFQUERYPARAM tag was added to CF - CF 4, I think.
> 
> > Does it take just one cfqueryparam in the statement for that
> > to happen?  Such as a insert statement with two columns, one
> > of which has a cfqueryparam and the other is a hard coded
> > value (for whatever reason).
> 
> If you want to get the full benefits of a prepared statement, to the degree
> that it would reuse query plans as much as an analogous stored procedure,
> you would need to replace all literal values in your SQL with CFQUERYPARAM
> values.
> 
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
> phone: 202-797-5496
> fax: 202-797-5444
> 
> 
> 

~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=36

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182416
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Execute Query string w CFQUERYPARAM

[Dave Watts]

> I'd assume that is something that started at a certain version 
> of CF? 

It started when the CFQUERYPARAM tag was added to CF - CF 4, I think.

> Does it take just one cfqueryparam in the statement for that 
> to happen?  Such as a insert statement with two columns, one 
> of which has a cfqueryparam and the other is a hard coded 
> value (for whatever reason).

If you want to get the full benefits of a prepared statement, to the degree
that it would reuse query plans as much as an analogous stored procedure,
you would need to replace all literal values in your SQL with CFQUERYPARAM
values.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444


~|
The annual ColdFusion User Conference is being held Sat 6/26 - Sun 6/27/04 8am-5pm in 
the Washington DC Area. 
http://www.houseoffusion.com/banners/view.cfm?bannerid=44

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182415
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Execute Query string w CFQUERYPARAM

[Joseph Flanigan]

Mark thanks for the posting.

The article makes a couple of good points but does it does not dismiss 
performance gains. The article illustrates using select queries in 
unplanned manner.

When a procedure is complied, columns that depend on indexes are 
represented in the compile. These indexes become part of the execution 
plan. When a procedure is used in an unplanned manner, like the article 
describes, slower performance should be expected. Just from reading the 
information that the author presented, it appears the use of the indexes 
was in an unplanned manner.  At the end of the article the author modified 
the procedure to use case statements to regain the performance.

This is a very good article in that it demonstrates the importance of using 
execution plans that match query parameters.


At 08:02 AM 10/19/2004, you wrote:
>Hey - in regards to this topic I found an insiteful article in a Builder.com
>newsletter. Sometimes an SP doesn't give you the performance gains you think
>it could because of the query cache.  I couldn't find a link to it on
>builder so I posted it on my blog.
>
>http://blog.mxconsulting.com/
>
>-Mk

http://www.switch-box.org/CFSQLTool/Download/

Switch_box  MediaFirm, Inc.
www.Switch-box.org  Loveland, CO  USA


~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=34

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181884
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Execute Query string w CFQUERYPARAM

[Mark A Kruger]

Uh... did I miss something?

-Mark

-Original Message-
From: Andy Ousterhout [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 19, 2004 9:23 AM
To: CF-Talk
Subject: RE: Execute Query string w CFQUERYPARAM


Nail bitter down to the finish with major lawsuits to follow.  Buy stock in
antacid companies.

Andy

-Original Message-
From: Mark A Kruger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 19, 2004 9:03 AM
To: CF-Talk
Subject: RE: Execute Query string w CFQUERYPARAM


Hey - in regards to this topic I found an insiteful article in a Builder.com
newsletter. Sometimes an SP doesn't give you the performance gains you think
it could because of the query cache.  I couldn't find a link to it on
builder so I posted it on my blog.

http://blog.mxconsulting.com/

-Mk

-Original Message-
From: Joseph Flanigan [mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 5:47 PM
To: CF-Talk
Subject: Re: Execute Query string w CFQUERYPARAM


 > Why it is better in your opinion to use stored procedures for
insert/update/deletes?

The quick answer is that stored procedures are pre-complied execution plans.

To run in the  database engine, every SQL statement is compiled into
something the database people call execution plans. These plans are the
code that change and read data. Whenever a query is run that changes the
structure of tables, efficient query plans result in the best performance
of the database. A rule I follow is that if any changes are made that
result in any table index being changed then use stored procedures. Insert
and deletes always change indexes, most of the time updates do too.








~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=38

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181850
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Execute Query string w CFQUERYPARAM

[Andy Ousterhout]

Nail bitter down to the finish with major lawsuits to follow.  Buy stock in
antacid companies.

Andy

-Original Message-
From: Mark A Kruger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 19, 2004 9:03 AM
To: CF-Talk
Subject: RE: Execute Query string w CFQUERYPARAM


Hey - in regards to this topic I found an insiteful article in a Builder.com
newsletter. Sometimes an SP doesn't give you the performance gains you think
it could because of the query cache.  I couldn't find a link to it on
builder so I posted it on my blog.

http://blog.mxconsulting.com/

-Mk

-Original Message-
From: Joseph Flanigan [mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 5:47 PM
To: CF-Talk
Subject: Re: Execute Query string w CFQUERYPARAM


 > Why it is better in your opinion to use stored procedures for
insert/update/deletes?

The quick answer is that stored procedures are pre-complied execution plans.

To run in the  database engine, every SQL statement is compiled into
something the database people call execution plans. These plans are the
code that change and read data. Whenever a query is run that changes the
structure of tables, efficient query plans result in the best performance
of the database. A rule I follow is that if any changes are made that
result in any table index being changed then use stored procedures. Insert
and deletes always change indexes, most of the time updates do too.






~|
Sams Teach Yourself Regular Expressions in 10 Minutes  by Ben Forta 
http://www.houseoffusion.com/banners/view.cfm?bannerid=40

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181841
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Execute Query string w CFQUERYPARAM

[Aaron Rouse]

Does the same type of logic apply in Oracle or does it handle SPs
differently than SQL Server?


On Tue, 19 Oct 2004 09:02:30 -0500, Mark A Kruger
<[EMAIL PROTECTED]> wrote:
> Hey - in regards to this topic I found an insiteful article in a Builder.com
> newsletter. Sometimes an SP doesn't give you the performance gains you think
> it could because of the query cache.  I couldn't find a link to it on
> builder so I posted it on my blog.
> 
> http://blog.mxconsulting.com/
> 
> -Mk
>

~|
Protect your mail server with built in anti-virus protection. It's not only good for 
you, it's good for everybody.
http://www.houseoffusion.com/banners/view.cfm?bannerid=39

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181839
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Execute Query string w CFQUERYPARAM

[Mark A Kruger]

Hey - in regards to this topic I found an insiteful article in a Builder.com
newsletter. Sometimes an SP doesn't give you the performance gains you think
it could because of the query cache.  I couldn't find a link to it on
builder so I posted it on my blog.

http://blog.mxconsulting.com/

-Mk

-Original Message-
From: Joseph Flanigan [mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 5:47 PM
To: CF-Talk
Subject: Re: Execute Query string w CFQUERYPARAM


 > Why it is better in your opinion to use stored procedures for
insert/update/deletes?

The quick answer is that stored procedures are pre-complied execution plans.

To run in the  database engine, every SQL statement is compiled into
something the database people call execution plans. These plans are the
code that change and read data. Whenever a query is run that changes the
structure of tables, efficient query plans result in the best performance
of the database. A rule I follow is that if any changes are made that
result in any table index being changed then use stored procedures. Insert
and deletes always change indexes, most of the time updates do too.




~|
Protect your mail server with built in anti-virus protection. It's not only good for 
you, it's good for everybody.
http://www.houseoffusion.com/banners/view.cfm?bannerid=39

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181831
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Execute Query string w CFQUERYPARAM

[Aaron Rouse]

I'd assume that is something that started at a certain version of CF? 
Does it take just one cfqueryparam in the statement for that to
happen?  Such as a insert statement with two columns, one of which has
a cfqueryparam and the other is a hard coded value(for whatever
reason).


On Tue, 19 Oct 2004 10:24:52 +0200, Jochem van Dieten
<[EMAIL PROTECTED]> wrote:
> Joseph Flanigan wrote:
> >> Why it is better in your opinion to use stored procedures for
> >> insert/update/deletes?
> >
> > The quick answer is that stored procedures are pre-complied execution plans.
> 
> So are queries that use cfqueryparam. (They are translated to a
> JDBC prepared statement.)
> 
> Jochem

~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=37

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181818
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Execute Query string w CFQUERYPARAM

[Joseph Flanigan]

 > I am curious do you make a SP for each query need.  Meaning for example 
do you have a separate SP for inserts that go into table A, B, or C.  Or do 
you have one single SP that handles all three based upon what is fed into it?

The simple answer is, yes every insert is its own procedure. Yes. every 
delete is its own procedure.  Yes, nearly every update is a procedure. Yep, 
that's a lot of procedures. Nope, it is not a programming problem. No there 
is not one procedure to handle alternate procedures.

This a good leading question as why I built  CFSQLTool  -- inserts -- and 
why each requires it's own procedure. Oh, there are people trying to do 
schema analysis of meta-data to do one-fit-all queries, but these seem more 
difficult to manage for support.  My approach with CFSQLTool was to make it 
as easy and as fast as possible to generate procedures. While, the tool 
does both stored procedure and ad hoc queries using the same techniques, 
stored procedures should be used for inserts and deletes.

CFSQLTool has something called an insert predictor. (I don't know of any 
other SQL tool that has this feature, it is what got me started writing 
CFSQLTool in the first place.)  The insert predictor looks at the schema 
for the table and makes a guess based on some simple rules to build a 
proper insert statement.

The insert predictor should always propose  a correct insert statement for 
the table based on the data model.  If the table uses identity columns as 
the primary key, the delete predictor proposes a delete statement.  Also, 
the update predictor overstates a proposed update statement but is accurate 
for editing.  The select predictor has no pre-defined constraints (where 
clause), these can be added with the user interface.

I designed CFSQLTool for programmers. It writes both cfquery and stored 
procedure style queries using checkboxes to modify code generation. This 
approach allows code design from the application prospective but still 
proposes queries based on the schema. Tools that exclusively use the schema 
for query proposals limit application design.

Joseph




~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=36

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181806
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Execute Query string w CFQUERYPARAM

[Joseph Flanigan]

  > Why it is better in your opinion to use stored procedures for
insert/update/deletes?

The quick answer is that stored procedures are pre-complied execution plans.

To run in the  database engine, every SQL statement is compiled into
something the database people call execution plans. These plans are the
code that change and read data. Whenever a query is run that changes the
structure of tables, efficient query plans result in the best performance
of the database. A rule I follow is that if any changes are made that
result in any table index being changed then use stored procedures. Insert
and deletes always change indexes, most of the time updates do too.

SQL passed in a cfquery has to be complied into a query plan on the fly
every time. (Yes some db engines can remember a previous ad hoc query but
the DB still has to prove the previous is the same.)  Whereas, when using
stored procedures, the CF/DB interface needs only to pass values and not
the query and the values.

Likewise to insert, updates and deletes, any select query that uses
un-parameterized joins should be a made a database view. A  view is a
stored query, that is pre-complied execution plan. Rarely should a cfquery
include join statements.

Joseph




~|
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
http://www.houseoffusion.com/banners/view.cfm?bannerid=11

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181799
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Execute Query string w CFQUERYPARAM

[Joseph Flanigan]

 > Why it is better in your opinion to use stored procedures for 
insert/update/deletes?

The quick answer is that stored procedures are pre-complied execution plans.

To run in the  database engine, every SQL statement is compiled into 
something the database people call execution plans. These plans are the 
code that change and read data. Whenever a query is run that changes the 
structure of tables, efficient query plans result in the best performance 
of the database. A rule I follow is that if any changes are made that 
result in any table index being changed then use stored procedures. Insert 
and deletes always change indexes, most of the time updates do too.

SQL passed in a cfquery has to be complied into a query plan on the fly 
every time. (Yes some db engines can remember a previous ad hoc query but 
the DB still has to prove the previous is the same.)  Whereas, when using 
stored procedures, the CF/DB interface needs only to pass values and not 
the query and the values.

Likewise to insert, updates and deletes, any select query that uses 
un-parameterized joins should be a made a database view. A  view is a 
stored query, that is pre-complied execution plan. Rarely should a cfquery 
include join statements.

Joseph

At 02:47 PM 10/18/2004, you wrote:
>Joseph,
>
>I was just curious why it is better in your opinion to use stored
>procedures for insert/update/deletes?
>
>Aaron
>
>
>On Mon, 18 Oct 2004 14:41:22 -0600, Joseph Flanigan
><[EMAIL PROTECTED]> wrote:
> > The technique of assigning a SQL  query to variable and then resolving the
> > variable in the cfquery is called the folded string technique. Since
> > cfqueryparam is a sub-tag of cfquery it cannot be used in a folded string
> > outside the cfquery. The function of cfqueryparam is to do data type
> > binding between CF and the database. So forget trying to used cfquaryparam
> > outside a cfquery. There are other approaches that work better.
> >
> > 1. Don't use cfquery for inserts, use stored procedures. Inserts, updates
> > and deletes always work best using stored procedures. But that is not
> > always an option.
> >
> > 2. Put the query in a cffunction and pass parameters to the query. By
> > wrapping the query inside a cffunction and passing the insert values as a
> > structure into to function uses pass-by-reference, there is minimum moving
> > of  variable data and it still uses cfqueryparam for data binding.
> >
> > 3. Use SQL variables to build folded strings. If you still want to use
> > folded strings, then build the string using SQL variables. For data
> > binding, declare the SQL variable  and its  data type then set the SQL
> > variable to the CF variable value.
> >
> > Below are examples of both the folded string and the cffunction using your
> > table example. I like the cffunction approach. It is what my CFSQLTool
> > generates. In the tool is wizard for running stored procedure that uses a
> > variation on folded strings. So both techniques work.
> >
> > Joseph
> >
> > 
> >
> > 
> > 
> > DECLARE @product int
> > SET @product = #product#
> >
> > DECLARE @c_productDesc varchar(50)
> > SET @c_productDesc = '#c_productDesc#'
> >
> > DECLARE @active bit
> > SET @active = #active#
> >
> >   INSERT INTO  tblProducts
> > (
> >   product ,
> >  c_productDesc ,
> >   active
> > )
> >   VALUES
> > (
> >  @product,
> >  @c_productDesc,
> >  @active
> > )
> > 
> > 
> >
> > 
> >   #PreserveSingleQuotes(DansQuery)#
> > 
> >
> > 
> > 
> >   > required="yes" >
> >  
> >   
> >
> >   > maxrows=-1 >
> >   BEGIN TRANSACTION
> >   INSERT INTO tblProducts
> >(
> >[product],
> >[c_productDesc],
> >[active]
> >)
> >VALUES
> >(
> > > cfsqltype="CF_SQL_INTEGER" >,
> > > cfsqltype="CF_SQL_varchar" >,
> > > cfsqltype="CF_SQL_bit" NULL="#isNull(theParams.$active)#" >
> >)
> >
> >   COMMIT TRAN
> >  
> > 
> > 
> >
> > At 08:33 AM 10/18/2004, you wrote:
> > >I have a variable that contains the following:
> > >
> > >Insert into tblProducts (product,c_productDesc,active)
> > >Values
> > >,
> > >,
> > > > >
> > >I want to execute this string inside a cfquery tag. 1st problem is,
> > >when I am building the string and looping over an array, if I try to
> > >use < and > in the string, my string ends up being empty. If I replace
> > >that with @lt; and @gt;, the string is built OK, as displayed abouve,
> > >but get an ODBC error on the @.
> > >
> > >I have played around with evaluating and DE, but cannot get past this
> > >point. Is this possible?
> > >
> > >
> > >Dan O'Keefe
> > >
> > >--
> > >[Todays Threads]
> > >[This Message]
> 

Re: Execute Query string w CFQUERYPARAM

[Joseph Flanigan]

The technique of assigning a SQL  query to variable and then resolving the 
variable in the cfquery is called the folded string technique. Since 
cfqueryparam is a sub-tag of cfquery it cannot be used in a folded string 
outside the cfquery. The function of cfqueryparam is to do data type 
binding between CF and the database. So forget trying to used cfquaryparam 
outside a cfquery. There are other approaches that work better.

1. Don't use cfquery for inserts, use stored procedures. Inserts, updates 
and deletes always work best using stored procedures. But that is not 
always an option.

2. Put the query in a cffunction and pass parameters to the query. By 
wrapping the query inside a cffunction and passing the insert values as a 
structure into to function uses pass-by-reference, there is minimum moving 
of  variable data and it still uses cfqueryparam for data binding.

3. Use SQL variables to build folded strings. If you still want to use 
folded strings, then build the string using SQL variables. For data 
binding, declare the SQL variable  and its  data type then set the SQL 
variable to the CF variable value.

Below are examples of both the folded string and the cffunction using your 
table example. I like the cffunction approach. It is what my CFSQLTool 
generates. In the tool is wizard for running stored procedure that uses a 
variation on folded strings. So both techniques work.

Joseph





DECLARE @product int
SET @product = #product#

DECLARE @c_productDesc varchar(50)
SET @c_productDesc = '#c_productDesc#'

DECLARE @active bit
SET @active = #active#

   INSERT INTO  tblProducts
 (
   product ,
  c_productDesc ,
   active
 )
   VALUES
 (
  @product,
  @c_productDesc,
  @active
 )




   #PreserveSingleQuotes(DansQuery)#




  
required="yes" >
  
   

  
maxrows=-1 >
   BEGIN TRANSACTION
   INSERT INTO tblProducts
    (
    [product],
    [c_productDesc],
    [active]
    )
    VALUES
    (
    
cfsqltype="CF_SQL_INTEGER" >,
    
cfsqltype="CF_SQL_varchar" >,
    
cfsqltype="CF_SQL_bit" NULL="#isNull(theParams.$active)#" >
    )

   COMMIT TRAN
  



At 08:33 AM 10/18/2004, you wrote:
>I have a variable that contains the following:
>
>Insert into tblProducts (product,c_productDesc,active)
>Values
>,
>,
>
>
>I want to execute this string inside a cfquery tag. 1st problem is,
>when I am building the string and looping over an array, if I try to
>use < and > in the string, my string ends up being empty. If I replace
>that with @lt; and @gt;, the string is built OK, as displayed abouve,
>but get an ODBC error on the @.
>
>I have played around with evaluating and DE, but cannot get past this
>point. Is this possible?
>
>
>Dan O'Keefe
>
>--
>[Todays Threads] 
>[This Message] 
>[Subscription] 
>[Fast 
>Unsubscribe] [User Settings] 
>[Donations 
>and Support]
>
>--
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Execute Query string w CFQUERYPARAM

[Dan O'Keefe]

Thanks, I will check that out - Mine is the Heinekin pattern, so if it
resembles someone else's, just coincidence.

Dan

On Mon, 18 Oct 2004 17:08:39 +0100, Greg Stewart <[EMAIL PROTECTED]> wrote:
> Sounds to me like you are looking at the DAO pattern
> (http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html).
>  Without starting an OO/mach-ii debate or anything like that, you may
>  want to have a quick look at the Phil Cruz's mach-ii.info site to see
>  how he implemented the DAO so that the application could use
>  Access/MySQL, etc databases.
>  
>  Just a pointer!
>  Cheers
> 
> 
>  G
>  
>  On Mon, 18 Oct 2004 11:33:00 -0400, Dan O'Keefe <[EMAIL PROTECTED]>
> wrote:
>  > Thanks, that is what it is starting to look like, but defats the
>  > intended purpose which was being able to use this component from any
>  > database comonent and generate the syntax.
>  > 
>  > Dan
>  > 
>  > >  If you are building up the query using cf logic (and not pulling the
> bits
>  > > of the query from a db or elsewhere) then you should consider putting
> all of
>  > > that logic in between the cfquery tags and not try building a query
> string
>  > > first. That is probably you best approach.
>  > >
>  > >
>  > >  Mark
>  > 
>  >
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Execute Query string w CFQUERYPARAM

[Greg Stewart]

Sounds to me like you are looking at the DAO pattern
(http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html).
Without starting an OO/mach-ii debate or anything like that, you may
want to have a quick look at the Phil Cruz's mach-ii.info site to see
how he implemented the DAO so that the application could use
Access/MySQL, etc databases.

Just a pointer!
Cheers
G

On Mon, 18 Oct 2004 11:33:00 -0400, Dan O'Keefe <[EMAIL PROTECTED]> wrote:
> Thanks, that is what it is starting to look like, but defats the
> intended purpose which was being able to use this component from any
> database comonent and generate the syntax.
> 
> Dan
> 
> >  If you are building up the query using cf logic (and not pulling the bits
> > of the query from a db or elsewhere) then you should consider putting all of
> > that logic in between the cfquery tags and not try building a query string
> > first. That is probably you best approach.
> >
> >
> >  Mark
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Execute Query string w CFQUERYPARAM

[Dan O'Keefe]

Thanks, that is what it is starting to look like, but defats the
intended purpose which was being able to use this component from any
database comonent and generate the syntax.

Dan

>  If you are building up the query using cf logic (and not pulling the bits
> of the query from a db or elsewhere) then you should consider putting all of
> that logic in between the cfquery tags and not try building a query string
> first. That is probably you best approach.
>  
>  
>  Mark
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Execute Query string w CFQUERYPARAM

[Dan O'Keefe]

Thanks Dina,

Thats what I meant (&). I tried htmlEditFormat and still get an error on the &.

Dan

On Mon, 18 Oct 2004 07:58:49 -0700, Dina Hess <[EMAIL PROTECTED]> wrote:
> try htmleditformat(); and it's ">" not "@gt;"
>    - Original Message - 
>    From: Dan O'Keefe
>    To: CF-Talk
>    Sent: Monday, October 18, 2004 7:33 AM
>    Subject: Execute Query string w CFQUERYPARAM
>  
>    I have a variable that contains the following:
>  
>    Insert into tblProducts (product,c_productDesc,active)
>    Values
>    ,
>    ,
>    
>  
>    I want to execute this string inside a cfquery tag. 1st problem is,
>    when I am building the string and looping over an array, if I try to
>    use < and > in the string, my string ends up being empty. If I replace
>    that with @lt; and @gt;, the string is built OK, as displayed abouve,
>    but get an ODBC error on the @.
>  
>    I have played around with evaluating and DE, but cannot get past this
>    point. Is this possible?
>  
>    
>    Dan O'Keefe
> 
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Execute Query string w CFQUERYPARAM

[Dan O'Keefe]

A blackbox component that I can pass in an array, with field name,
value, and CFSQL type, and have the component return a complete insert
or update statement to be executed.

I am strating to think it cannot be done, and I see Pascal agree's

Thanks,

Dan

On Mon, 18 Oct 2004 15:49:07 +0100, Thomas Chiverton
<[EMAIL PROTECTED]> wrote:
> On Monday 18 Oct 2004 15:33 pm, Dan O'Keefe wrote:
>  > I want to execute this string inside a cfquery tag. 1st problem is,
>  > when I am building the string and looping over an array, if I try to
>  > use < and > in the string, my string ends up being empty. If I replace
>  > that with @lt; and @gt;, the string is built OK, as displayed abouve,
>  > but get an ODBC error on the @.
>  
>  I don't think you can do what you are trying to do, as the parameters will
> be 
>  bound inside the JDBC driver at runtime...
>  
>  What are you trying achieve ?
>  
>  -- 
>  Tom Chiverton 
>  Advanced ColdFusion Programmer
>  
>  Tel: +44(0)1749 834997
>  email: [EMAIL PROTECTED]
>  BlueFinger Limited
>  Underwood Business Park
>  Wookey Hole Road, WELLS. BA5 1AF
>  Tel: +44 (0)1749 834900
>  Fax: +44 (0)1749 834901
>  web: www.bluefinger.com
>  Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple
>  Quay, BRISTOL. BS1 6EG.
>  *** This E-mail contains confidential information for the addressee
>  only. If you are not the intended recipient, please notify us
>  immediately. You should not use, disclose, distribute or copy this
>  communication if received in error. No binding contract will result from
>  this e-mail until such time as a written document is signed on behalf of
>  the company. BlueFinger Limited cannot accept responsibility for the
>  completeness or accuracy of this message as it has been transmitted over
>  public networks.***
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Execute Query string w CFQUERYPARAM

[d.a.collie]

I don't think you can build up a string with cfqueryparam and execute it
inside a cfquery tag (check the archives for a definitive answer)

 
you would need to build some sort of udf's (custom tag pre-cfmx) that
would carry out this sort of thing AFAIK  

 
(I think Issac's onTap does something like this from what has been
talked about in previous discussions but you would need to check)

 
-- 
dc
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Execute Query string w CFQUERYPARAM

[Gaulin, Mark]

I'm guessing that this is not possible unless you are willing to write the string to a file and cfinclude it.  The "<" problem sounds like a coding issue, and maybe separating the "<" from the "cf" would help with with the parsing, but I couldn't say for sure without looking at your code.

 
Without the cfqueryparam tags in there you should be able to get this working (but the whole "preserve single quotes" issue would still be your responsibility to get right).

 
If you are building up the query using cf logic (and not pulling the bits of the query from a db or elsewhere) then you should consider putting all of that logic in between the cfquery tags and not try building a query string first. That is probably you best approach.

 
    Mark

-Original Message-
From: Dan O'Keefe [mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 10:34 AM
To: CF-Talk
Subject: Execute Query string w CFQUERYPARAM

I have a variable that contains the following:

Insert into tblProducts (product,c_productDesc,active) 
Values 
,
,


I want to execute this string inside a cfquery tag. 1st problem is,
when I am building the string and looping over an array, if I try to
use < and > in the string, my string ends up being empty. If I replace
that with @lt; and @gt;, the string is built OK, as displayed abouve,
but get an ODBC error on the @.

I have played around with evaluating and DE, but cannot get past this
point. Is this possible?


Dan O'Keefe 
  _
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Execute Query string w CFQUERYPARAM

[Dina Hess]

try htmleditformat(); and it's ">" not "@gt;"
  - Original Message - 
  From: Dan O'Keefe
  To: CF-Talk
  Sent: Monday, October 18, 2004 7:33 AM
  Subject: Execute Query string w CFQUERYPARAM

  I have a variable that contains the following:

  Insert into tblProducts (product,c_productDesc,active)
  Values
  ,
  ,
  

  I want to execute this string inside a cfquery tag. 1st problem is,
  when I am building the string and looping over an array, if I try to
  use < and > in the string, my string ends up being empty. If I replace
  that with @lt; and @gt;, the string is built OK, as displayed abouve,
  but get an ODBC error on the @.

  I have played around with evaluating and DE, but cannot get past this
  point. Is this possible?

  
  Dan O'Keefe
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: Execute Query string w CFQUERYPARAM

[Pascal Peters]

You can't build a string to execute within cfquery and use cfqueryparam
at the same time. Build your sql in the cfquery tag itself.

Pascal

> -Original Message-
> From: Dan O'Keefe [mailto:[EMAIL PROTECTED]
> Sent: 18 October 2004 16:34
> To: CF-Talk
> Subject: Execute Query string w CFQUERYPARAM
> 
> I have a variable that contains the following:
> 
> Insert into tblProducts (product,c_productDesc,active)
> Values
> ,
> ,
> 
> 
> I want to execute this string inside a cfquery tag. 1st problem is,
> when I am building the string and looping over an array, if I try to
> use < and > in the string, my string ends up being empty. If I replace
> that with @lt; and @gt;, the string is built OK, as displayed abouve,
> but get an ODBC error on the @.
> 
> I have played around with evaluating and DE, but cannot get past this
> point. Is this possible?
> 
> 
> Dan O'Keefe
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: Execute Query string w CFQUERYPARAM

[Thomas Chiverton]

On Monday 18 Oct 2004 15:33 pm, Dan O'Keefe wrote:
> I want to execute this string inside a cfquery tag. 1st problem is,
> when I am building the string and looping over an array, if I try to
> use < and > in the string, my string ends up being empty. If I replace
> that with @lt; and @gt;, the string is built OK, as displayed abouve,
> but get an ODBC error on the @.

I don't think you can do what you are trying to do, as the parameters will be 
bound inside the JDBC driver at runtime...

What are you trying achieve ?

-- 
Tom Chiverton 
Advanced ColdFusion Programmer

Tel: +44(0)1749 834997
email: [EMAIL PROTECTED]
BlueFinger Limited
Underwood Business Park
Wookey Hole Road, WELLS. BA5 1AF
Tel: +44 (0)1749 834900
Fax: +44 (0)1749 834901
web: www.bluefinger.com
Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple
Quay, BRISTOL. BS1 6EG.
*** This E-mail contains confidential information for the addressee
only. If you are not the intended recipient, please notify us
immediately. You should not use, disclose, distribute or copy this
communication if received in error. No binding contract will result from
this e-mail until such time as a written document is signed on behalf of
the company. BlueFinger Limited cannot accept responsibility for the
completeness or accuracy of this message as it has been transmitted over
public networks.***
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Execute Query string w CFQUERYPARAM

[Dan O'Keefe]

I have a variable that contains the following:

Insert into tblProducts (product,c_productDesc,active) 
Values 
,
,


I want to execute this string inside a cfquery tag. 1st problem is,
when I am building the string and looping over an array, if I try to
use < and > in the string, my string ends up being empty. If I replace
that with @lt; and @gt;, the string is built OK, as displayed abouve,
but get an ODBC error on the @.

I have played around with evaluating and DE, but cannot get past this
point. Is this possible?


Dan O'Keefe
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]