Re: Strange issue with cfquery
try and change the following #PreserveSingleQuotes(sql)# From: Nkonye Oyewusi To: cf-talk Sent: Wed, November 10, 2010 11:33:27 AM Subject: Strange issue with cfquery Hello, I am running CF 8 on an Oracle database. I'm trying to pass in a SQL script as a string and keep getting the error: ORA-00907: missing right parenthesis. This is what I'm trying to do: The doRunScript method is as follows: #sql# Is there a workaround for this? Thanks, Nikki ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339071 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Strange issue with cfquery
Hello, I am running CF 8 on an Oracle database. I'm trying to pass in a SQL script as a string and keep getting the error: ORA-00907: missing right parenthesis. This is what I'm trying to do: The doRunScript method is as follows: #sql# Is there a workaround for this? Thanks, Nikki ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339070 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
slow cfquery cfqueryparam?
Hi, I am running the following cfquery which inserts a large amount of rows into a table (approx 2200) INSERT INTO parentquestions VALUES , (,,) The query was taking on average 9 seconds to complete. However, sometimes it was erratically taking 40 seconds. I output the query as text, copied it into MySQL and it only took 0.14 seconds. So I then tried to build the sql text without applying the cfqueryparam as below, and it took only 1 second! #sqlstring# In understand that adding the cfqueryparam is adding approx 6600 validations, however would it really be the cause of slowing this query down so much? If so would there be any alternatives that I am missing? thanks for any help ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339059 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery and carriage return
alternatively could the problem be before the data is put into mysql. basically we are using an extjs text area to collect data, that passes the data over to a form which we put straight in the database. should we be doing anything with that data such as escaping it? > Hi, > > i have data stored in the database with a carriage return. however, > when coldfusion pulls the data out with cfquery it seems to lose the > carriage return, i dump out the query and it doesnt show the carriage > return. i tried looping through each character in the string and > outputting the ascii value and none show the character 13 for carriage > return > > does anyone know why this happens and how to get around it. > > thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338120 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery and carriage return
Hi, i have data stored in the database with a carriage return. however, when coldfusion pulls the data out with cfquery it seems to lose the carriage return, i dump out the query and it doesnt show the carriage return. i tried looping through each character in the string and outputting the ascii value and none show the character 13 for carriage return does anyone know why this happens and how to get around it. thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338118 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Javascript Cfquery
On Tue, Oct 12, 2010 at 1:20 PM, fun and learning
wrote:
>
> Hi All -
>
> I am trying to do the following:
>
>
> function abc(arg1, arg2) {
> <cfquery name="q" datasource="..">
> select * from table1 where col1 = arg1
> </cfquery>
> ..
> }
>
>
>
> Is it possible to pass javascript arguments to a coldfusion query which is
> in turn present in the javascript block?
>
ColdFusion runs on the server. JavaScript runs on the client. By the time
the browser has rendered the page, ColdFusion has done its duty and is
probably hanging out smoking a cigarette or using the little application
server room.
You can output CF variables to JS just like you do to HTML...
var myName = "<cfoutput>#myName#</cfoutput>";
... but it's not a two-way conversation (much like my conversations with my
wife). If you want them to talk to each other, you need to introduce AJAX
into the equation.
So short answer is, no... a JavaScript variable can't be used to manipulate
a ColdFusion variable (but the converse works a treat), without utilizing
AJAX.
And maybe worth noting that the "language" attribute of the
Re: Javascript Cfquery
On 10/12/2010 1:20 PM, fun and learning wrote:
> Hi All -
>
> I am trying to do the following:
>
>
>function abc(arg1, arg2) {
> <cfquery name="q" datasource="..">
> select * from table1 where col1 = arg1
> </cfquery>
> ..
> }
>
>
Not simultaneously!
ColdFusion runs on the server and JavaScript runs on the client.
Complete different systems with completely different memory and
variables executing completely different code and completely different
times. I.E. "Never will the twain, ColdFusion and JavaScript, meet."
But there are plenty of ways to have JavaScript make request of
ColdFusion and for ColdFusion to return JavaScript, just note that they
are now working "together".
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338092
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Javascript Cfquery
Hi All -
I am trying to do the following:
function abc(arg1, arg2) {
<cfquery name="q" datasource="..">
select * from table1 where col1 = arg1
</cfquery>
..
}
Is it possible to pass javascript arguments to a coldfusion query which is in
turn present in the javascript block?
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338091
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery, cachedwithin and cfscript
Does anyone know how to cache a query using cfquery but in cfscript?
This works, query is cached for a day:
SELECT GETDATE()
But this doesn't, it's always cached = false:
qry = new query();
qry.setDatasource("stuff");
qry.setSql("SELECT GETDATE()");
qry.setCachedWithin("1");
qry = qry.execute().getResult();
writedump(qry);
I know it's there, since doing will dump the function. For the cachedwithin
value, I've tried createTimeSpan(1,0,0,0) but it doesn't work too.
I'm on CF9.0.1, if it matters.
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337760
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
thanks for everyones help, its now solved!
the reason i am building it through a string is because it is interacting with
various extjs grids, which apply filters. i have a generic function that
creates the havingclause to filter the data.
i ran the havingclause through a loop printing out 1 character at a time. the
apostophe was printed as '%apos;' even though it appeared as ' in the sql
statement!
have taken on board comments re cfqueryparam and now that this error is fixed
will update it to include the sql injection
thanks for all the help :)
> hi, we are running a cfquery with the following sql statement:
>
> SELECT viewname, categoryname FROM views LEFT OUTER JOIN
> viewcategories on views.categoryid = viewcategories.categoryid GROUP
> BY viewname HAVING categoryname IN ("'s") ORDER BY viewname ASC
> LIMIT 0, 300
>
> if we type this directly into mysql it works fine, similarly if we
> type it directly into a it works fine. however the having
> clause is getting built from following code and is causing the
> problem:
>
>
>
>
>
>
> />
>
> the problem is occuring due to the apostrophe '. we have tried
> preserveSingleQuotes but still does not work.
>
> is there any way around this?
>
> thanks
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337159
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
If it was MSSQL, you would want single quotes, so more like this:
SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on
views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING 0 = 0
AND categoryname IN ('''s') ORDER BY viewname ASC LIMIT 0, 300
Instead of setting your "havingClause" variable and messing with
preserveSingleQuotes(), can you just do this?
SELECT viewname, categoryname
FROM views LEFT OUTER JOIN
viewcategories ON views.categoryid = viewcategories.categoryid
GROUP BY viewname
HAVING 0 = 0
AND categoryname IN ( )
ORDER BY viewname ASC LIMIT 0, 300
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337154
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
thanks, although the problem is there is no error to catch. when we run a
cfdump of the query, the sql is as follows:
SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on
views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING 0 = 0 AND
categoryname IN ("'s") ORDER BY viewname ASC LIMIT 0, 300
it just doesnt pull out the data, but if we copy it directly into mysql command
line it works fine
we do not even know how to trace the error as how can it work in mysql command
line but not in coldfusion? completely lost on this one!
>Wrap your actual cfquery with cftry and then cfoutput the variable cfcatch.sql
>
>
> ...
> type="Database">#cfcatch.sql#
>
>
>Then post that sql here.
>
>Rick
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337153
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
Wrap your actual cfquery with cftry and then cfoutput the variable cfcatch.sql ... #cfcatch.sql# Then post that sql here. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337131 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
Or instead of using a variable, if you can just put the clause in the SQL statement, let the list param do the work for you: HAVING categoryname IN ( ) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337123 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
On 9/16/2010 6:44 AM, Richard White wrote: > thanks for the replies, although the preserveSingleQuotes doesnt work You should be looking at the actual SQL that is being sent to the database. It would be provide clear evidence of what is wrong. Looking at this line: You may want to investigate the listQualify() function. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337120 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
thanks for the replies, although the preserveSingleQuotes doesnt work
> hi, we are running a cfquery with the following sql statement:
>
> SELECT viewname, categoryname FROM views LEFT OUTER JOIN
> viewcategories on views.categoryid = viewcategories.categoryid GROUP
> BY viewname HAVING categoryname IN ("'s") ORDER BY viewname ASC
> LIMIT 0, 300
>
> if we type this directly into mysql it works fine, similarly if we
> type it directly into a it works fine. however the having
> clause is getting built from following code and is causing the
> problem:
>
>
>
>
>
>
> />
>
> the problem is occuring due to the apostrophe '. we have tried
> preserveSingleQuotes but still does not work.
>
> is there any way around this?
>
> thanks
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337119
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
yea so don't forget to do your own SQL injection protection as well. On Thu, Sep 16, 2010 at 1:54 PM, Rick Root wrote: > > Aside from my trepidations about generating SQL like this because it > prevents you from using , what you're looking for is > #preserveSingleQuotes()# > > coldfusion automatically escapes any single quotes in variables that > are within a tag so if your query SQL is a variable > > > select blah blah blah > from blah > group by blah > #preserveSingleQuotes(havingClause)# > > > Otherwise, the single quotes get doubled up and you'll get errors. > > Rick > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337113 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
I'm with Rick. You should really change it up a wee bit so you can use cfqueryparam. It has many advantages. On Thu, Sep 16, 2010 at 8:54 AM, Rick Root wrote: > > Aside from my trepidations about generating SQL like this because it > prevents you from using , what you're looking for is > #preserveSingleQuotes()# > > coldfusion automatically escapes any single quotes in variables that > are within a tag so if your query SQL is a variable > > > select blah blah blah > from blah > group by blah > #preserveSingleQuotes(havingClause)# > > > Otherwise, the single quotes get doubled up and you'll get errors. > > Rick > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337112 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
Aside from my trepidations about generating SQL like this because it prevents you from using , what you're looking for is #preserveSingleQuotes()# coldfusion automatically escapes any single quotes in variables that are within a tag so if your query SQL is a variable select blah blah blah from blah group by blah #preserveSingleQuotes(havingClause)# Otherwise, the single quotes get doubled up and you'll get errors. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337111 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery not returning results with apostrophe
hi, we are running a cfquery with the following sql statement:
SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on
views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING
categoryname IN ("'s") ORDER BY viewname ASC LIMIT 0, 300
if we type this directly into mysql it works fine, similarly if we type it
directly into a it works fine. however the having clause is getting
built from following code and is causing the problem:
the problem is occuring due to the apostrophe '. we have tried
preserveSingleQuotes but still does not work.
is there any way around this?
thanks
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337109
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
toJson is better if you have room for the server-side overhead, and don't mind writing methods that can't be used in any other scenario than via Ajax. CFQueryReader is solely a client side solution, so that you can reuse your server-side code in any way that you see fit. Steve "Cutter" Blades Adobe Community Professional - ColdFusion Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer Co-Author of "Learning Ext JS" http://www.packtpub.com/learning-ext-js/book _ http://blog.cutterscrossing.com Richard White wrote: > in fact toJson is even better for th job: http://tojson.riaforge.org/ > > >> thanks, sorry dont think i was clear enough, i knew about >> serializejson but this doesnt work with extjs, in case anyone else >> stumbles on this post and is interested i found the following: >> http://blog.cutterscrossing.com/index.cfm/CFQueryReader >> >> >>> Google is your friend: >>> >>> Search "cfquery to json" and get: >>> >>> http://www.coldfusionjedi.com/index. >>> >> cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example >> >>> >>> > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334346 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
in fact toJson is even better for th job: http://tojson.riaforge.org/ > thanks, sorry dont think i was clear enough, i knew about > serializejson but this doesnt work with extjs, in case anyone else > stumbles on this post and is interested i found the following: > http://blog.cutterscrossing.com/index.cfm/CFQueryReader > > >Google is your friend: > > > >Search "cfquery to json" and get: > > > >http://www.coldfusionjedi.com/index. > cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example > > > > > > > >> ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334336 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
thanks, sorry dont think i was clear enough, i knew about serializejson but this doesnt work with extjs, in case anyone else stumbles on this post and is interested i found the following: http://blog.cutterscrossing.com/index.cfm/CFQueryReader >Google is your friend: > >Search "cfquery to json" and get: > >http://www.coldfusionjedi.com/index.cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example > > > >> ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334335 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
Google is your friend: Search "cfquery to json" and get: http://www.coldfusionjedi.com/index.cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example On Mon, Jun 7, 2010 at 11:53 AM, Richard White wrote: > > hi, > > is there a quick and easy way to convert a cfquery into a json array. i > will be returning the json array to extjs to present in a grid > > thanks > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery to json array
hi, is there a quick and easy way to convert a cfquery into a json array. i will be returning the json array to extjs to present in a grid thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334333 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery within javascript
Couldn't you just say: [...] cflogic [...] Sebastiaan = So long and thanx 4 all the fish ==> Onlinebase.nl > -Original Message- > From: fun and learning [mailto:funandlrnn...@gmail.com] > Sent: Thursday, 15 April 2010 6:13 AM > To: cf-talk > Subject: cfquery within javascript > > > hi, > > I am trying to do the following: > > I wrote a cfquery within javascript function as below: > > > <cfquery name="query1 datasource="abc"> > > </cfquery> > > <cfif query1.recordcount neq "0"> > <cfoutput query = "query1"> ><cfquery name="query2 datasource="abc"> > select * from table1 where name=#query1.name# ></cfquery> > </cfoutput> > alert(<cfoutput>#query2.RecordCount#</cfoutput>); > </cfif> > > > > > The alert value is 1 but the debugging section shows 0, and 0 is the correct > value. is there something wrong that I am doing. > > If so can you please point out... > > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332890 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery within javascript
Any reason you are doing it this way and not just no javascript and use cfdump instead? -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: Thursday, 15 April 2010 6:13 AM To: cf-talk Subject: cfquery within javascript hi, I am trying to do the following: I wrote a cfquery within javascript function as below: <cfquery name="query1 datasource="abc"> </cfquery> <cfif query1.recordcount neq "0"> <cfoutput query = "query1"> <cfquery name="query2 datasource="abc"> select * from table1 where name=#query1.name# </cfquery> </cfoutput> alert(<cfoutput>#query2.RecordCount#</cfoutput>); </cfif> The alert value is 1 but the debugging section shows 0, and 0 is the correct value. is there something wrong that I am doing. If so can you please point out... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332889 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery within javascript
What are you trying to show. If you want a literal put your alert results in
quotes. alert("#query2.RecordCount#");
Robert B. Harrison
Director of Interactive Services
Austin & Williams
125 Kennedy Drive, Suite 100
Hauppauge NY 11788
P : 631.231.6600 Ext. 119
F : 631.434.7022
http://www.austin-williams.com
Great advertising can't be either/or. It must be &.
Plug in to our blog: A&W Unplugged
http://www.austin-williams.com/unplugged
-Original Message-
From: fun and learning [mailto:funandlrnn...@gmail.com]
Sent: Wednesday, April 14, 2010 4:13 PM
To: cf-talk
Subject: cfquery within javascript
hi,
I am trying to do the following:
I wrote a cfquery within javascript function as below:
<cfquery name="query1 datasource="abc">
</cfquery>
<cfif query1.recordcount neq "0">
<cfoutput query = "query1">
<cfquery name="query2 datasource="abc">
select * from table1 where name=#query1.name#
</cfquery>
</cfoutput>
alert(<cfoutput>#query2.RecordCount#</cfoutput>);
</cfif>
The alert value is 1 but the debugging section shows 0, and 0 is the correct
value. is there something wrong that I am doing.
If so can you please point out...
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332888
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery within javascript
hi, I am trying to do the following: I wrote a cfquery within javascript function as below: <cfquery name="query1 datasource="abc"> </cfquery> <cfif query1.recordcount neq "0"> <cfoutput query = "query1"> <cfquery name="query2 datasource="abc"> select * from table1 where name=#query1.name# </cfquery> </cfoutput> alert(<cfoutput>#query2.RecordCount#</cfoutput>); </cfif> The alert value is 1 but the debugging section shows 0, and 0 is the correct value. is there something wrong that I am doing. If so can you please point out... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332887 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery writing to the wrong table..
I have an app I'm coding that will read a csv file and do some magic
on it and populate an Accounting_Temp table for moving to the main
table. It works until I get into commas in a field
(UnqualifiedReason). Any ideas on what could be happening or where I
can look?
===
* Up to this point in the code, the data is correct and stored on
Accounting_Temp correctly. *
Select *
from Accounting_Temp
* Still looking good.. *
Select *
from Accounting
Where EnrollmentNumber = #Check.EnrollmentNumber#
* Kablam! At this point, the Check dump shows that "test1, test2,
test3" is now "test1" in the database on Account_Temp. I also confirm
that the DB got rewritten with just Test1 *
Insert into Accounting
(Id,EnrollmentNumber,EnrollmentChannel,StatusName,PromoCode,QualificationType,CreationDate,State,ZipCode,UnqualifiedReason,
EnteredDate)
Values
('#Check.Id#',#Check.EnrollmentNumber#,'#Check.EnrollmentChannel#','#Check.StatusName#','#Check.PromoCode#','#Check.QualificationType#',#Check.CreationDate#,'#Check.State#','#Check.ZipCode#','#Check.UnqualifiedReason#',
Now())
#Check.EnrollmentNumber# Added
Update Accounting
Set Id='#Check.Id#'
,EnrollmentChannel='#Check.EnrollmentChannel#'
,StatusName='#Check.StatusName#'
,PromoCode='#Check.PromoCode#'
,QualificationType='#Check.QualificationType#'
,CreationDate=#Check.CreationDate#
,State='#Check.State#'
,ZipCode='#Check.ZipCode#'
,UnqualifiedReason='#Check.UnqualifiedReason#'
Where EnrollmentNumber=#Check.EnrollmentNumber#
#Check.EnrollmentNumber# Updated
#Added# added and #Updated# updated (out of #check.Recordcount# records)
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332707
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY Question
If the query is executing correctly but bringing back no records, this should work with blahname.recordcount equal zero . If the query is not executing correctly, blahname.recordcount will not equal zero. On Wed, Mar 31, 2010 at 10:59 AM, Dave Sueltenfuss wrote: > > I'm running into an odd behavior with CFQUERY (at least I think it is odd) > > I have the following code (example) > > > SELECT blah > FROM blah > WHERE blah = 1 > > > > 12345 > > > if the query blahName returns no results, I thought i would still be able to > access blahName.recordCount > > Am I wrong on this, or is there something else I am overlooking? > > Thanks > -Dave > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332503 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY Question
Ok, found the cause of the issue, by taking the query into SQL Server In SQL, the following is being returned *Warning: Null value is eliminated by an aggregate or other SET operation. (0 row(s) affected)* So it appears this is what is throwing off the query results in CF I am working on resolving this issue now. -Dave On Wed, Mar 31, 2010 at 2:18 PM, Robert Harrison wrote: > > You are correct that it should work, but they maybe something else in the > code which is preventing the query from being executed. On the top of your > page add: > > > > And that will deal with the case where the query is not executed. > > Robert B. Harrison > Director of Interactive Services > Austin & Williams > 125 Kennedy Drive, Suite 100 > Hauppauge NY 11788 > P : 631.231.6600 Ext. 119 > F : 631.434.7022 > http://www.austin-williams.com > > Great advertising can't be either/or. It must be &. > > Plug in to our blog: A&W Unplugged > http://www.austin-williams.com/unplugged > > > -Original Message- > From: Dave Sueltenfuss [mailto:dsueltenf...@gmail.com] > Sent: Wednesday, March 31, 2010 1:59 PM > To: cf-talk > Subject: CFQUERY Question > > > I'm running into an odd behavior with CFQUERY (at least I think it is odd) > > I have the following code (example) > > > SELECT blah > FROM blah > WHERE blah = 1 > > > > 12345 > > > if the query blahName returns no results, I thought i would still be able > to > access blahName.recordCount > > Am I wrong on this, or is there something else I am overlooking? > > Thanks > -Dave > > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332502 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQUERY Question
You are correct that it should work, but they maybe something else in the code which is preventing the query from being executed. On the top of your page add: And that will deal with the case where the query is not executed. Robert B. Harrison Director of Interactive Services Austin & Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be &. Plug in to our blog: A&W Unplugged http://www.austin-williams.com/unplugged -Original Message- From: Dave Sueltenfuss [mailto:dsueltenf...@gmail.com] Sent: Wednesday, March 31, 2010 1:59 PM To: cf-talk Subject: CFQUERY Question I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) SELECT blah FROM blah WHERE blah = 1 12345 if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332501 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY Question
You are correct, the record count for an empty query object does exist (and should be 0). What exactly is not working? Are you getting an error message? On Wed, Mar 31, 2010 at 1:59 PM, Dave Sueltenfuss wrote: > > I'm running into an odd behavior with CFQUERY (at least I think it is odd) > > I have the following code (example) > > > SELECT blah > FROM blah > WHERE blah = 1 > > > > 12345 > > > if the query blahName returns no results, I thought i would still be able > to > access blahName.recordCount > > Am I wrong on this, or is there something else I am overlooking? > > Thanks > -Dave > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332500 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQUERY Question
Your cfif statement asks for the recordCount to be returned on if the recordCount is greater than 0. Try cfif blahName.recordcount GTE 0 and you should get the recordCount either way. Rick -Original Message- From: Dave Sueltenfuss [mailto:dsueltenf...@gmail.com] Sent: Wednesday, March 31, 2010 1:59 PM To: cf-talk Subject: CFQUERY Question I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) SELECT blah FROM blah WHERE blah = 1 12345 if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332499 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQUERY Question
I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) SELECT blah FROM blah WHERE blah = 1 12345 if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332498 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
On 3/16/2010 8:22 AM, Steven Sprouse wrote: > If so, what is the currentRow value? Just a little confused there. currentRow is one of the values provided by ColdFusion about its query objects, just like columnList and recordCount. It is simple the current row of the record set that is being processed in the current iteration of a query loop, either or loops. You can use it any time you want to know what iteration you are on. I.E a way to alternate row colors: #darkBackground# You can normally ignore the need to keep track of the current row when you are looping over a single record set inside of a loop. But once you nest two or more separate query loops inside each other, ColdFusion can no longer know for certain which iteration of which record set do you want to out put at a given point. It falls on you to explicitly tell ColdFusion which iteration to use. I.E. the aQuery.currentRow. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331791 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
Just so I'm clear, are you saying that I need to change my #aQuery.aColumn[aQuery.currentRow]# formatting within the form? For example, I need to change this: #getSchools.SchoolBrief# to this: #getSchools.SchoolBrief[getSchools.currentRow]# If so, what is the currentRow value? Just a little confused there. ___ > > > > > > >That is illegal because ColdFusion does some automatic variable scoping >that would just not work in such a situation. To do this you have to >use the form. > >I.E. > > > > > > > > >Just realize with this from CF is not going to automatically scope the >query column names for you and you will have to keep track of the rows. >You output will look something like this. > >#aQuery.aColumn[aQuery.currentRow]# and #bQuery.bColumn[bQuery.currentRow]# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331788 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
That is illegal because ColdFusion does some automatic variable scoping that would just not work in such a situation. To do this you have to use the form. I.E. Just realize with this from CF is not going to automatically scope the query column names for you and you will have to keep track of the rows. You output will look something like this. #aQuery.aColumn[aQuery.currentRow]# and #bQuery.bColumn[bQuery.currentRow]# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331786 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Repeating Data - Unsure of my use of cfquery
You can put a inside of a tag. -Original Message- From: Steven Sprouse [mailto:sspro...@ccboe.com] Sent: Tuesday, March 16, 2010 10:39 AM To: cf-talk Subject: Re: Repeating Data - Unsure of my use of cfquery I got my original form to output the way I wanted it to. Now, I'm trying to actually process and validate that form and I'm running into more errors. I sort of know what I'm doing wrong, but unclear how to fix it. Here is my error: Invalid tag nesting configuration. A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a QUERY= attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing. The error occurred on line 299. Is there an easy way for me to send the file of my processing document on this forum? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331785 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
I got my original form to output the way I wanted it to. Now, I'm trying to actually process and validate that form and I'm running into more errors. I sort of know what I'm doing wrong, but unclear how to fix it. Here is my error: Invalid tag nesting configuration. A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a QUERY= attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing. The error occurred on line 299. Is there an easy way for me to send the file of my processing document on this forum? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331784 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
You guys are awesome. Sometimes it's the easiest things... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331753 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
Just separate each table into its own query. Use the data from the right table in the right place and the problem is solved. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ On 15 March 2010 22:39, Steven Sprouse wrote: > > Barney, would that require that the different tables have a linking > relationship? I think that's my problem. There is no common field shared > among these tables. > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331752 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
Barney, would that require that the different tables have a linking relationship? I think that's my problem. There is no common field shared among these tables. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331751 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
Looking at the way the data are used, the OP probably needs five queries. The queries are just building options in select inputs. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ On 15 March 2010 22:36, Barney Boisvert wrote: > > Your second query is the problem, both the repeating data and the > slowness. You have a full outer join of five tables, which is not > what you want. You need inner joins. Here is a simple reference that > might help you on your way: > > http://www.sql-tutorial.net/SQL-JOIN.asp > > Has a million reference to other pages that might help. > > If you get that query sorted you should be all set. > > cheers, > barneyb > > On Mon, Mar 15, 2010 at 6:21 AM, Steven Sprouse > wrote: > > > > My Code: (link to live page - > http://www2.ccboe.com/summeracademy/app/emp/index.cfm) > > > > > > > > SELECT SchNum, SchoolBrief > > FROM SCHOOLS > > ORDER BY SchoolBrief > > > > > > > > > > > > SELECT ApplySubject, ApplySchool, School, ApplyPosition, YrsPartic, > TshirtSize > > FROM ApplySchoolList, SRAStaff, ApplySubjectGrList, YrsParticList, > TshirtSizeList > > ORDER BY School > > > > > > > > action="/summeracademy/app/app_process.cfm"> > > > > > > > > > > > >Contact and Payroll > Information: > > > > > > > > > > > >Applicant Name: > >First: > >Last: > >MI: > > > > > > > > > > > >Employee ID: > > maxlength="6"> > > > > > > > > > > > >Current Regular School: > > size="1"> > > > Select your school > > > > > > value="#getSchools.SchoolBrief#">#getSchools.SchoolBrief# /> > > > > > > > > > > > > > > > >Current Grade and/or Subject that you > teach: > > type="text"> > > > > > > > > > > > >Phone Numbers: > >Home: type="text"> > >Cell: type="text"> > > > > > > > > > > > > > >Work: type="text"> > >IP Phone ext.: name="Voicemail" type="text"> > > > > > > > > > > > >Address: > >Street: type="text"> > > > City: State: type="text"> Zip: > > > > > > > > > > > >Email: > > type="text"> > >Please type this carefully so that we can > reach you. There are no spaces in an email address. > > > > > > > > > > > >Application > Information for Summer Academy 2010: > > > > > > > > > > > >What position are you applying > for: > > size="1"> > > > Select a position > > > > > > value="#getSrastaff.ApplyPosition#">#getSrastaff.ApplyPosition# > > > > > > > > > > > > > > > >What school would you prefer? > > size="1"> > > > Select a school > > > > > > value="#getSrastaff.School#">#getSrastaff.School# > > > > > > > > > > > > > > > >If you do not get your preferred > school, mark where you are willing to work: > > query="getSrastaff"> value="#getSrastaff.School#" /> #getSrastaff.School# > > > > > > > > > > > >What subject do you prefer to > teach? > > name="SubjectGradePreferred" size="1"> > > > Select a subject > > > > > > value="#getSrastaff.ApplySubject#">#getSrastaff.ApplySubject# > > > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331748 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
Your second query is the problem, both the repeating data and the slowness. You have a full outer join of five tables, which is not what you want. You need inner joins. Here is a simple reference that might help you on your way: http://www.sql-tutorial.net/SQL-JOIN.asp Has a million reference to other pages that might help. If you get that query sorted you should be all set. cheers, barneyb On Mon, Mar 15, 2010 at 6:21 AM, Steven Sprouse wrote: > > My Code: (link to live page - > http://www2.ccboe.com/summeracademy/app/emp/index.cfm) > > > > SELECT SchNum, SchoolBrief > FROM SCHOOLS > ORDER BY SchoolBrief > > > > > > SELECT ApplySubject, ApplySchool, School, ApplyPosition, YrsPartic, TshirtSize > FROM ApplySchoolList, SRAStaff, ApplySubjectGrList, YrsParticList, > TshirtSizeList > ORDER BY School > > > > > > > > > > Contact and Payroll > Information: > > > > > > Applicant Name: > First: > Last: > MI: > > > > > > Employee ID: > maxlength="6"> > > > > > > Current Regular School: > > > Select your school > > > > #getSchools.SchoolBrief# /> > > > > > > > > Current Grade and/or Subject that you > teach: > type="text"> > > > > > > Phone Numbers: > Home: > Cell: type="text"> > > > > > > > Work: > IP Phone ext.: name="Voicemail" type="text"> > > > > > > Address: > Street: type="text"> > > City: State: type="text"> Zip: > > > > > > Email: > > Please type this carefully so that we can > reach you. There are no spaces in an email address. > > > > > > Application Information > for Summer Academy 2010: > > > > > > What position are you applying > for: > size="1"> > > Select a position > > > > value="#getSrastaff.ApplyPosition#">#getSrastaff.ApplyPosition# > > > > > > > > What school would you prefer? > size="1"> > > Select a school > > > > value="#getSrastaff.School#">#getSrastaff.School# > > > > > > > > If you do not get your preferred school, > mark where you are willing to work: > name="SchoolApplyOther" type="checkbox" value="#getSrastaff.School#" /> > #getSrastaff.School# > > > > > > What subject do you prefer to > teach? > size="1"> > > Select a subject > > > > value="#getSrastaff.ApplySubject#">#getSrastaff.ApplySubject# > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331747 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Repeating Data - Unsure of my use of cfquery
My Code: (link to live page - http://www2.ccboe.com/summeracademy/app/emp/index.cfm) SELECT SchNum, SchoolBrief FROM SCHOOLS ORDER BY SchoolBrief SELECT ApplySubject, ApplySchool, School, ApplyPosition, YrsPartic, TshirtSize FROM ApplySchoolList, SRAStaff, ApplySubjectGrList, YrsParticList, TshirtSizeList ORDER BY School Contact and Payroll Information: Applicant Name: First: Last: MI: Employee ID: Current Regular School: Select your school #getSchools.SchoolBrief# Current Grade and/or Subject that you teach: Phone Numbers: Home: Cell: Work: IP Phone ext.: Address: Street: City: State: Zip: Email: Please type this carefully so that we can reach you. There are no spaces in an email address. Application Information for Summer Academy 2010: What position are you applying for: Select a position #getSrastaff.ApplyPosition# What school would you prefer? Select a school #getSrastaff.School# If you do not get your preferred school, mark where you are willing to work: #getSrastaff.School# What subject do you prefer to teach? Select a subject #getSrastaff.ApplySubject# What other subject(s) would you be willing to teach? #getSrastaff.ApplySubject# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331746 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Repeating Data - Unsure of my use of cfquery
I posted a few days ago about having multiple cfquery statements in the same document. After some troubleshooting, I finally figured out how to do this. I began building my test form, which is populated from fields in a database, I'm running into a problem where the data is just being repeated, to the point where it's making the page extremely slow to load. I have a feeling this is because I didn't use cfquery properly - but I'm not sure how to remedy the situation. The form kept looking over the data more and more as I added new rows querying new data. Any help is much appreciated. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331745 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Two CFQUERY statements?
Make sure the name you gave your datasource in the CF Administrator is the same as the name you are using in the "name" attribute of your cfquery tag. CF Appears to think there is no datasource with the name you are trying to use. -Original Message- From: Steven Sprouse [mailto:sspro...@ccboe.com] Sent: Thursday, March 11, 2010 3:39 PM To: cf-talk Subject: Re: Two CFQUERY statements? You'll have to excuse me, I'm still sort of a CF novice so I'm not too familiar with how to use CFTRANSACTION. What I was doing was just putting two separate CFQUERY statements at the head of my document, each with a different name attribute and different data sources. When I did my cfoutput of the second cfquery, that's when I got my error. The error was "Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC SequeLink driver][SequeLink Server]The specified data source is not defined." But I'm not sure that is very helpful to you. If one of the databases was converted into a table and placed into the other database, I'd have no problem with this, but for some reason I'm getting tripped up with the different data sources. Thanks for any help you can offer. > You can happily query multiple databases within the same request; > what > you can't do is query multiple databases within a single transaction. > So as long as your CFQUERY tags don't share a CFTRANSACTION block you > should be fine. > > Can you post the actual error message? > > cheers, > barneyb > > On Thu, Mar 11, 2010 at 12:26 PM, Steven Sprouse > wrote: > > > > I have someone who has two separate databases and they want me to > write a Web form generating option menus from both databases. > > > > I have set up both as separate data sources in my Coldfusion > administrator and have included one cfquery at the head of my document > generating a list of locations. It's working fine. When I try to query > the second database and do a cfoutput for another option menu, I get > errors. > > > > I seem to remember that it might not be possible to have two > separate cfquery statements in the same document. Is this correct? If > so, how would I go about achieving my desired result? > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331642 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Two CFQUERY statements?
In SQL Server, not only can you cross join two databases in a single query, you can join them across separate servers/instances (provided they are linked servers). -Original Message- From: Casey Dougall [mailto:ca...@uberwebsitesolutions.com] Sent: Thursday, March 11, 2010 3:38 PM To: cf-talk Subject: Re: Two CFQUERY statements? On Thu, Mar 11, 2010 at 3:35 PM, Barney Boisvert wrote: > > You can happily query multiple databases within the same request; what > you can't do is query multiple databases within a single transaction. > So as long as your CFQUERY tags don't share a CFTRANSACTION block you > should be fine. > > Can you post the actual error message? > > cheers, > barneyb > > But you can't do it from within the same tag as they are separate data sources. So you need two queries and then a QoQ to link them together if need be. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331640 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Two CFQUERY statements?
Sounds like ColdFusion is having problems connecting to one of your databases. Try this: 1) make sure you can "verify" both datasources in your ColdFusion Administrator. 2) make sure you didn't misspell one of the datasource names in your cfquery tags. thanks, eric cobb ecar technologies, llc http://www.cfgears.com Steven Sprouse wrote: > You'll have to excuse me, I'm still sort of a CF novice so I'm not too > familiar with how to use CFTRANSACTION. > > What I was doing was just putting two separate CFQUERY statements at the head > of my document, each with a different name attribute and different data > sources. When I did my cfoutput of the second cfquery, that's when I got my > error. > > The error was "Error Executing Database Query. [Macromedia][SequeLink JDBC > Driver][ODBC Socket][DataDirect][ODBC SequeLink driver][SequeLink Server]The > specified data source is not defined." But I'm not sure that is very helpful > to you. > > If one of the databases was converted into a table and placed into the other > database, I'd have no problem with this, but for some reason I'm getting > tripped up with the different data sources. > > Thanks for any help you can offer. > > >> You can happily query multiple databases within the same request; >> what >> you can't do is query multiple databases within a single transaction. >> So as long as your CFQUERY tags don't share a CFTRANSACTION block you >> should be fine. >> >> Can you post the actual error message? >> >> cheers, >> barneyb >> >> On Thu, Mar 11, 2010 at 12:26 PM, Steven Sprouse >> wrote: >> >>> I have someone who has two separate databases and they want me to >>> >> write a Web form generating option menus from both databases. >> >>> I have set up both as separate data sources in my Coldfusion >>> >> administrator and have included one cfquery at the head of my document >> generating a list of locations. It's working fine. When I try to query >> the second database and do a cfoutput for another option menu, I get >> errors. >> >>> I seem to remember that it might not be possible to have two >>> >> separate cfquery statements in the same document. Is this correct? If >> so, how would I go about achieving my desired result? >> >>> > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331615 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Two CFQUERY statements?
Steven Sprouse wrote: > If one of the databases was converted into a table and placed into the other > database, I'd have no problem with this, but for some reason I'm getting > tripped up with the different data sources. More than likely, this is a permissions issue on the other datasource or the datasource is not set up properly. -- http://www.linkedin.com/pub/roger-austin/8/a4/60 http://twitter.com/RogerTheGeek http://www.misshunt.com/ Home of the Clean/Dirty Magnet http://www.ncdevcon.com/ TACFUG 2010 Conference in North Carolina ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331614 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Two CFQUERY statements?
You'll have to excuse me, I'm still sort of a CF novice so I'm not too familiar with how to use CFTRANSACTION. What I was doing was just putting two separate CFQUERY statements at the head of my document, each with a different name attribute and different data sources. When I did my cfoutput of the second cfquery, that's when I got my error. The error was "Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC SequeLink driver][SequeLink Server]The specified data source is not defined." But I'm not sure that is very helpful to you. If one of the databases was converted into a table and placed into the other database, I'd have no problem with this, but for some reason I'm getting tripped up with the different data sources. Thanks for any help you can offer. > You can happily query multiple databases within the same request; > what > you can't do is query multiple databases within a single transaction. > So as long as your CFQUERY tags don't share a CFTRANSACTION block you > should be fine. > > Can you post the actual error message? > > cheers, > barneyb > > On Thu, Mar 11, 2010 at 12:26 PM, Steven Sprouse > wrote: > > > > I have someone who has two separate databases and they want me to > write a Web form generating option menus from both databases. > > > > I have set up both as separate data sources in my Coldfusion > administrator and have included one cfquery at the head of my document > generating a list of locations. It's working fine. When I try to query > the second database and do a cfoutput for another option menu, I get > errors. > > > > I seem to remember that it might not be possible to have two > separate cfquery statements in the same document. Is this correct? If > so, how would I go about achieving my desired result? > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331611 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Two CFQUERY statements?
On Thu, Mar 11, 2010 at 3:35 PM, Barney Boisvert wrote: > > You can happily query multiple databases within the same request; what > you can't do is query multiple databases within a single transaction. > So as long as your CFQUERY tags don't share a CFTRANSACTION block you > should be fine. > > Can you post the actual error message? > > cheers, > barneyb > > But you can't do it from within the same tag as they are separate data sources. So you need two queries and then a QoQ to link them together if need be. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331610 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Two CFQUERY statements?
That isn't true in the least, you can certainly have more than one db queried in a request. I have to hit 3 or 4 just for authentication, single sign on, personnel records and other info that's spread all across the University I work for. What are the errors? Are you nesting queries outputs inside one another? If so that won't work, you have to use cfloop instead. -Original Message- From: Steven Sprouse [mailto:sspro...@ccboe.com] Sent: Thursday, March 11, 2010 3:26 PM To: cf-talk Subject: Two CFQUERY statements? I have someone who has two separate databases and they want me to write a Web form generating option menus from both databases. I have set up both as separate data sources in my Coldfusion administrator and have included one cfquery at the head of my document generating a list of locations. It's working fine. When I try to query the second database and do a cfoutput for another option menu, I get errors. I seem to remember that it might not be possible to have two separate cfquery statements in the same document. Is this correct? If so, how would I go about achieving my desired result? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331609 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Two CFQUERY statements?
You can happily query multiple databases within the same request; what you can't do is query multiple databases within a single transaction. So as long as your CFQUERY tags don't share a CFTRANSACTION block you should be fine. Can you post the actual error message? cheers, barneyb On Thu, Mar 11, 2010 at 12:26 PM, Steven Sprouse wrote: > > I have someone who has two separate databases and they want me to write a Web > form generating option menus from both databases. > > I have set up both as separate data sources in my Coldfusion administrator > and have included one cfquery at the head of my document generating a list of > locations. It's working fine. When I try to query the second database and do > a cfoutput for another option menu, I get errors. > > I seem to remember that it might not be possible to have two separate cfquery > statements in the same document. Is this correct? If so, how would I go about > achieving my desired result? > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331608 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Two CFQUERY statements?
I have someone who has two separate databases and they want me to write a Web form generating option menus from both databases. I have set up both as separate data sources in my Coldfusion administrator and have included one cfquery at the head of my document generating a list of locations. It's working fine. When I try to query the second database and do a cfoutput for another option menu, I get errors. I seem to remember that it might not be possible to have two separate cfquery statements in the same document. Is this correct? If so, how would I go about achieving my desired result? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331607 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: problem with a condition cfif in a cfquery
THANKS DAVE THAT SOLVED IT!!! >Alex, > >You can't use a CF condition within your SQL that you want to evaluate each >record in the query. For that, you'd need to use an SQL condition. You >could use CF conditions inside your cfquery only to evaluate the 'text' of >the query (like to decide WHICH SQL statement to use for example based on >values that exist apart from your data). > >You can use a 'isnull' function to solve this (this is a DATABASE function, >not a CF function, and is, therefore, DB dependent). I'm not sure which DB >you are using, but for SQL Server it's isnull. For Oracle it's NVL. > > >SELECT isNull(emailaddr1,emailaddr2) as emailaddr >... > > >This says, "if emailaddr1 has a value, then return it as 'emailaddr', but if >it is null, then return the value in emailaddr as 'emailaddr'. If >emailaddr2 is also null, then 'emailaddr2' will be null. If you want >another default value such as 'N/A', then you would just wrap it all with >another function like this: > >SELECT isNull(isNull(emailaddr1,emailaddr2),'N/A') as emailaddr > >Hope this helps! > >Dave Phillips > >HI everybody > >I have a table with two email addresses for every person there are cases >though that emailaddr1 is empty and emailaddr2 has a value. I need to output >the addresses that if emailaddr1 is empty it would be substituted by >emailaddr2. I tried using a cfif inside the query written below but doesn't >work > > >SELECT emailaddr2, emailaddr1 > > >#recipients# > >thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331305 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: problem with a condition cfif in a cfquery
Alex, You can't use a CF condition within your SQL that you want to evaluate each record in the query. For that, you'd need to use an SQL condition. You could use CF conditions inside your cfquery only to evaluate the 'text' of the query (like to decide WHICH SQL statement to use for example based on values that exist apart from your data). You can use a 'isnull' function to solve this (this is a DATABASE function, not a CF function, and is, therefore, DB dependent). I'm not sure which DB you are using, but for SQL Server it's isnull. For Oracle it's NVL. SELECT isNull(emailaddr1,emailaddr2) as emailaddr ... This says, "if emailaddr1 has a value, then return it as 'emailaddr', but if it is null, then return the value in emailaddr as 'emailaddr'. If emailaddr2 is also null, then 'emailaddr2' will be null. If you want another default value such as 'N/A', then you would just wrap it all with another function like this: SELECT isNull(isNull(emailaddr1,emailaddr2),'N/A') as emailaddr Hope this helps! Dave Phillips -Original Message- From: alex poyaoan [mailto:ap.cli...@tiscali.it] Sent: Wednesday, March 03, 2010 7:09 AM To: cf-talk Subject: problem with a condition cfif in a cfquery HI everybody I have a table with two email addresses for every person there are cases though that emailaddr1 is empty and emailaddr2 has a value. I need to output the addresses that if emailaddr1 is empty it would be substituted by emailaddr2. I tried using a cfif inside the query written below but doesn't work SELECT emailaddr2, emailaddr1 #recipients# thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331303 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: problem with a condition cfif in a cfquery
> HI everybody > > I have a table with two email addresses for every person there are > cases though that emailaddr1 is empty and emailaddr2 has a value. I > need to output the addresses that if emailaddr1 is empty it would be > substituted by emailaddr2. I tried using a cfif inside the query > written below but doesn't work > > > SELECT emailaddr2, emailaddr1 > > > #recipients# > > thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331302 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: problem with a condition cfif in a cfquery
Got it I just forgot placing quotes on the ("emailaddr1") on the cfif statement
and now it works...
thanks anyway
> HI everybody
>
> I have a table with two email addresses for every person there are
> cases though that emailaddr1 is empty and emailaddr2 has a value. I
> need to output the addresses that if emailaddr1 is empty it would be
> substituted by emailaddr2. I tried using a cfif inside the query
> written below but doesn't work
>
>
> SELECT emailaddr2, emailaddr1
>
>
> #recipients#
>
> thanks
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331300
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
problem with a condition cfif in a cfquery
HI everybody I have a table with two email addresses for every person there are cases though that emailaddr1 is empty and emailaddr2 has a value. I need to output the addresses that if emailaddr1 is empty it would be substituted by emailaddr2. I tried using a cfif inside the query written below but doesn't work SELECT emailaddr2, emailaddr1 #recipients# thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331299 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery return datatypes
In CF8 there is cfdbinfo This function below will return a query object the following fields (see below). http://www.cfquickdocs.com/cf8/?getDoc=cfdbinfo#cfdbinfo If you don't have CF 8 you will have to query the metadata based on the SQL dialect for that particular database. You can look at the source code for Brian Rinaldi's Illudium cfcgenerator. Last time I looked he had cfcs for MySQL, MSSQL, Oracle and Postges. http://code.google.com/p/cfcgenerator/ COLUMN_NAME Name of the column. TYPE_NAME SQL data type of the column. IS_NULLABLE Whether the column allows nulls. IS_PRIMARYKEY Whether the column is a primary key. IS_FOREIGNKEY Whether the column is a foreign key. REFERENCED_PRIMARYKEY If the column is a foreign key, the name of the table it refers to. REFERENCED_PRIMARYKEY_TABLE If the column is a foreign key, the key name it refers to. COLUMN_SIZE Size of the column DECIMAL_DIGITS Number of digits to the right of the decimal point. COLUMN_DEFAULT_VALUE Default value of column. CHAR_OCTET_LENGTH Maximum length in bytes of a character or integer data type column. ORDINAL_POSITION Ordinal position of the column. REMARKS Remarks of the column. On Fri, Feb 26, 2010 at 12:13 PM, Joshua Rowe wrote: > > Hello. Is there a way to return a datatype list from your query along with > the column list? Example: > > >SELECT * FROM tblTest; > > > >#qryTest.columnlist# >#qryTest.datatypes# > > > Thanks! > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331193 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: cfquery return datatypes
You can usually get that info from the database's system tables, depending on your RDBMS. (In the following queries, 'typename' will give you the datatype.) MSSQL: SELECT syscolumns.colid, syscolumns.name, syscolumns.colorder AS sortorder, syscolumns.prec AS collen, syscolumns.xtype, syscolumns.typestat, syscolumns.xusertype, syscolumns.isnullable, systypes.name AS typename, ( SELECT COUNT(*) FROM sysindexkeys WHERE id = syscolumns.id AND indid = 1 AND colid = syscolumns.colid ) AS isPrimary FROM syscolumns LEFT JOIN systypes ON syscolumns.xtype = systypes.xtype AND syscolumns.xusertype = systypes.xusertype WHERE id = OBJECT_ID() ORDER BY colorder Oracle: SELECT table_name colid, column_name name, column_id sortorder, data_length collen, nullable isnullable, data_type typename, ( SELECT COUNT(*) FROM user_constraints c INNER JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name WHERE c.table_name = AND c.constraint_type = AND cc.column_name = user_tab_columns.column_name ) AS isPrimary FROM user_tab_columns WHERE table_name = ORDER BY column_id ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331186 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
I tried this but as you can see I am REgex illiterate and more... REReplaceNoCase(TheFile,"(]+>.+?)","(]+>.+?\L\1)","all") It will return the content of the cfquery but I am cannot find a way to lowercase it properly. Thanks >yes. REReplace. >The pattern your trying to match is the first line. It says that we're >looking to capture something that starts with a cfquery tag, has >content, and then ends with a closing cfquery tag. >The replace has a \L before the content returned from the pattern (the >query tag). \L means that everything that comes after it will be lower >cased. End result is a whole cfquery tag along with body converted to >lower case. > >-- >Michael Dinowitz > > > > > >> ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331182 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
On 2/26/10, phil phil wrote: > Problem is that in my code over the years the table name can be as 'Company' > or 'company' so just changing the table names in mysql to lowercase is not > enough but yes a necessary step (can do it from the GUI: PHPadmin) > Then as you wrote changing all occurrences of the table names (1500 cfquery > tags) to lowercase. I choose lowercase but it could be also uppercase as > long as everything on the code and db is coherent. > Anyway that is the only solution that I find at this time. How invested are you in MySQL? If your hoster offers PostgreSQL you could use that and have your case insensitive table names? Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331181 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfquery return datatypes
> Hello. Is there a way to return a datatype list from > your query along with the column list? Example: IIRC, there is no direct method. But you could certainly extract that information using getMetaData() ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331179 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfquery return datatypes
Hello. Is there a way to return a datatype list from your query along with the column list? Example: SELECT * FROM tblTest; #qryTest.columnlist# #qryTest.datatypes# Thanks! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331178 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
Problem is that in my code over the years the table name can be as 'Company' or 'company' so just changing the table names in mysql to lowercase is not enough but yes a necessary step (can do it from the GUI: PHPadmin) Then as you wrote changing all occurrences of the table names (1500 cfquery tags) to lowercase. I choose lowercase but it could be also uppercase as long as everything on the code and db is coherent. Anyway that is the only solution that I find at this time. Changing the db to0 be case non sensitive because as someone already mentioned it is a shared environment so other clients would get in troubles. Thanks guys for the great feedbacks! >> >> But why not do the conversion on the Access side before migrating to >> MySQL? In Access you can simply rename the tables to the case you use >> in your queries before you convert them. >> > > >IIRC there is a setting in my.cnf that will automatically lower case table >names. > >To turn it off you have to set it to 0 >--lower-case-table-names=0 > >I think that the default setting is to lower case table names... It was a >while back so forgive me if I am hazy on the details. > >More details can be found here: > >http://www.heidisql.com/forum.php?t=667#p3653 >http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html > >The way I got a round it was to either do a search and replace and lower >case the table names in the code or to change the table names in the >database using a MySQL GUI. Again, I forget the details, I was in a bit of a >rage at the time. ;) > >HTH > >G! > > >On Fri, Feb 26, 2010 at 10:05 AM, Jochem van Dieten wrote: > >> ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331177 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
> > But why not do the conversion on the Access side before migrating to > MySQL? In Access you can simply rename the tables to the case you use > in your queries before you convert them. > IIRC there is a setting in my.cnf that will automatically lower case table names. To turn it off you have to set it to 0 --lower-case-table-names=0 I think that the default setting is to lower case table names... It was a while back so forgive me if I am hazy on the details. More details can be found here: http://www.heidisql.com/forum.php?t=667#p3653 http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html The way I got a round it was to either do a search and replace and lower case the table names in the code or to change the table names in the database using a MySQL GUI. Again, I forget the details, I was in a bit of a rage at the time. ;) HTH G! On Fri, Feb 26, 2010 at 10:05 AM, Jochem van Dieten wrote: > > On Fri, Feb 26, 2010 at 2:01 PM, Andrew Scott wrote: > > Have you tried talking to your hosting provider, and switch case > sensitivity > > off for your database? > > That is not a per-database setting. Once you have it set to some > setting and have clients on it, a hosting provider is committed and > can not change it anymore. > > But why not do the conversion on the Access side before migrating to > MySQL? In Access you can simply rename the tables to the case you use > in your queries before you convert them. > > Jochem > > -- > Jochem van Dieten > http://jochem.vandieten.net/ > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331173 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
On Fri, Feb 26, 2010 at 2:01 PM, Andrew Scott wrote: > Have you tried talking to your hosting provider, and switch case sensitivity > off for your database? That is not a per-database setting. Once you have it set to some setting and have clients on it, a hosting provider is committed and can not change it anymore. But why not do the conversion on the Access side before migrating to MySQL? In Access you can simply rename the tables to the case you use in your queries before you convert them. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331171 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Need to convert all CFquery tags to lowercase
Have you tried talking to your hosting provider, and switch case sensitivity off for your database? -Original Message- From: phil phil [mailto:arantxa...@gmail.com] Sent: Friday, 26 February 2010 11:40 PM To: cf-talk Subject: Re: Need to convert all CFquery tags to lowercase It seems that the table name is case sensitive but not the columns. For e.g if I change the name of the table to 'company' from 'Company' then it cannot find the table because in MYSQL it is referred as 'Company'. BTW, we are on Linux which I understand is case sensitive. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331168 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
yes. REReplace. The pattern your trying to match is the first line. It says that we're looking to capture something that starts with a cfquery tag, has content, and then ends with a closing cfquery tag. The replace has a \L before the content returned from the pattern (the query tag). \L means that everything that comes after it will be lower cased. End result is a whole cfquery tag along with body converted to lower case. -- Michael Dinowitz On Fri, Feb 26, 2010 at 7:07 AM, phil phil wrote: > > It cost me to make the below code to work. Should I use a Rereplace > expression? > (]+>.+?) > \L\1 > > Thanks in advance for your feedbacks > > >>Do you need all of the query lowercased or is there part that you want >>to keep uppercased? If you don't care then this regex will do the job: >> >>(]+>.+?) >>\L\1 >> >>The first line grabs all cfquery tags and their content and the second >>like converts it to lower case. I'd test the regex against all of your >>templates first to see if there are any cases that you want to avoid. >> >>You can use just about any regex tool to do the job, including writing >>a fast tool in CF to do the job. >> >>-- >>Michael Dinowitz >> >> >> >> >> >>> > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331167 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
It seems that the table name is case sensitive but not the columns. For e.g if I change the name of the table to 'company' from 'Company' then it cannot find the table because in MYSQL it is referred as 'Company'. BTW, we are on Linux which I understand is case sensitive. >If I recall right, you have to switch case sensitivity on, are you saying >that is what your host has done? > > > > >Hello, > >I do need to migrate from Access to MySQL on a Windows and shared server >environment. >All existing queries (approx 2000) are failing because they are not >lowercased. > >Does anyone know of a script that would look into every files for the >CFquery tag and lowercase its content between? > >This is the only solution that comes to me but maybe there is a easier way? > >Thanks in advance for any feedbacks. > >Phil ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331166 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need to convert all CFquery tags to lowercase
If I recall right, you have to switch case sensitivity on, are you saying that is what your host has done? -Original Message- From: phil phil [mailto:arantxa...@gmail.com] Sent: Friday, 26 February 2010 8:58 PM To: cf-talk Subject: Need to convert all CFquery tags to lowercase Hello, I do need to migrate from Access to MySQL on a Windows and shared server environment. All existing queries (approx 2000) are failing because they are not lowercased. Does anyone know of a script that would look into every files for the CFquery tag and lowercase its content between? This is the only solution that comes to me but maybe there is a easier way? Thanks in advance for any feedbacks. Phil ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331164 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
It cost me to make the below code to work. Should I use a Rereplace expression? (]+>.+?) \L\1 Thanks in advance for your feedbacks >Do you need all of the query lowercased or is there part that you want >to keep uppercased? If you don't care then this regex will do the job: > >(]+>.+?) >\L\1 > >The first line grabs all cfquery tags and their content and the second >like converts it to lower case. I'd test the regex against all of your >templates first to see if there are any cases that you want to avoid. > >You can use just about any regex tool to do the job, including writing >a fast tool in CF to do the job. > >-- >Michael Dinowitz > > > > > >> ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331163 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
>As far as I know there is no requirement in MySQL for queries to be >lowercase. I just ran one in all uppercase and it worked fine. There >has to be something else causing this problem. > > >> Are you on Linux? It is not the case for Windows with my hosting company which is unable to change system variables in the shared environment. Or Am I missing something? I would love to be proved wrong... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331162 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
Thanks Michael, Ideally I would like to keep the comments in Uppercase but can do without it. > >You can use just about any regex tool to do the job, including writing >a fast tool in CF to do the job. I am not sure how to use it but will play with it. (]+>.+?) >\L\1 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331161 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need to convert all CFquery tags to lowercase
As far as I know there is no requirement in MySQL for queries to be lowercase. I just ran one in all uppercase and it worked fine. There has to be something else causing this problem. On Fri, Feb 26, 2010 at 1:57 AM, phil phil wrote: > > Hello, > > I do need to migrate from Access to MySQL on a Windows and shared server > environment. > All existing queries (approx 2000) are failing because they are not > lowercased. > > Does anyone know of a script that would look into every files for the CFquery > tag and lowercase its content between? > > This is the only solution that comes to me but maybe there is a easier way? > > Thanks in advance for any feedbacks. > > Phil > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331160 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need to convert all CFquery tags to lowercase
Do you need all of the query lowercased or is there part that you want to keep uppercased? If you don't care then this regex will do the job: (]+>.+?) \L\1 The first line grabs all cfquery tags and their content and the second like converts it to lower case. I'd test the regex against all of your templates first to see if there are any cases that you want to avoid. You can use just about any regex tool to do the job, including writing a fast tool in CF to do the job. -- Michael Dinowitz On Fri, Feb 26, 2010 at 4:57 AM, phil phil wrote: > > Hello, > > I do need to migrate from Access to MySQL on a Windows and shared server > environment. > All existing queries (approx 2000) are failing because they are not > lowercased. > > Does anyone know of a script that would look into every files for the CFquery > tag and lowercase its content between? > > This is the only solution that comes to me but maybe there is a easier way? > > Thanks in advance for any feedbacks. > > Phil > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331159 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Need to convert all CFquery tags to lowercase
Hello, I do need to migrate from Access to MySQL on a Windows and shared server environment. All existing queries (approx 2000) are failing because they are not lowercased. Does anyone know of a script that would look into every files for the CFquery tag and lowercase its content between? This is the only solution that comes to me but maybe there is a easier way? Thanks in advance for any feedbacks. Phil ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331158 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF9 cfquery not giving same insert results as CF7
> As for the SET NOCOUNT ON and SET NOCOUNT OFF, they have no > effect on > these. The only thing that does is prevent extra > network traffic by > stopping the extra reporting of how many rows were affected > for each > piece of the query. In CF8 it did have an effect on cfquery's in some situations. Specifically the simple INSERT followed by SELECT scope_identity() scenario. I do not about CF9. Though I will say the results in CF8 varied greatly depending on a number of conditions (statement used, driver version, etcetera). A lot of the issues were related to how the generated keys feature was implemented. http://www.mischefamily.com/nathan/index.cfm/2008/1/28/Problems-with-CF-8s-Generated-Keys-Feature ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330259 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF9 cfquery not giving same insert results as CF7
I don't know what CF9 is doing under the hood. What I do is put all the queries in stored procedures. At the beginning of the procedure I always put SET NOCOUNT ON and I always end with SET NOCOUNT OFF. If you have a procedure that is doing a lot of stuff it can reduce a lot of back and forth and network traffic. I had one procedure that did a lot of data pulls and combining of data, without using the nocount, the amount of messages about xx rows affected was way larger than the actual data that was returned. Sometimes it's the other way around. When I'm testing something I leave them off so I can see what is happening. Think of it sort of like turning off debug in production for cf. It's not really critical data, but if you don't do it you get a lot of overhead. Steve -Original Message- From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] Sent: Friday, January 29, 2010 9:37 AM To: cf-talk Subject: Re: CF9 cfquery not giving same insert results as CF7 Thanks. This gives me a bit of extra information on top of the research I've done on the topic. Now comes a few extended questions. :) 1. When a cfquery has a result attribute defined, it will return data about the query and in the case of an insert, the id of the inserted item. The assumption is that under the hood CF is using scope_identity() (for MSSQL). Do you have any idea if this is the case? Just trivia and something I should test anyway. 2. From what you say of NoCount, I assume that there would be a minor performance benefit for sql insert, update, and delete operations to use it, correct? Thanks -- Michael Dinowitz ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330256 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
Thanks. This gives me a bit of extra information on top of the research I've done on the topic. Now comes a few extended questions. :) 1. When a cfquery has a result attribute defined, it will return data about the query and in the case of an insert, the id of the inserted item. The assumption is that under the hood CF is using scope_identity() (for MSSQL). Do you have any idea if this is the case? Just trivia and something I should test anyway. 2. From what you say of NoCount, I assume that there would be a minor performance benefit for sql insert, update, and delete operations to use it, correct? Thanks -- Michael Dinowitz On Fri, Jan 29, 2010 at 9:24 AM, DURETTE, STEVEN J (ATTASIAIT) wrote: > > Use SCOPE_IDENTITY(). @@Identity returns the id for the last record > inserted. If an another insert into that table occurs between your > insert and the select @@identity (2 different people making inserts into > the db for example) you will get the id for the other record that was > inserted. > > Scope identity only pulls the last record for the current scope. Sort of > like putting something in a transaction. It all sticks together. > (however putting a query in a transaction with @@identity will not solve > the problem above). > > As for the SET NOCOUNT ON and SET NOCOUNT OFF, they have no effect on > these. The only thing that does is prevent extra network traffic by > stopping the extra reporting of how many rows were affected for each > piece of the query. > > Of course I'm assuming that this is MSSQL. > > Ste ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330255 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF9 cfquery not giving same insert results as CF7
Use SCOPE_IDENTITY(). @@Identity returns the id for the last record inserted. If an another insert into that table occurs between your insert and the select @@identity (2 different people making inserts into the db for example) you will get the id for the other record that was inserted. Scope identity only pulls the last record for the current scope. Sort of like putting something in a transaction. It all sticks together. (however putting a query in a transaction with @@identity will not solve the problem above). As for the SET NOCOUNT ON and SET NOCOUNT OFF, they have no effect on these. The only thing that does is prevent extra network traffic by stopping the extra reporting of how many rows were affected for each piece of the query. Of course I'm assuming that this is MSSQL. Steve -Original Message- From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] Sent: Thursday, January 28, 2010 9:10 PM To: cf-talk Subject: Re: CF9 cfquery not giving same insert results as CF7 I've found a query that used Select @@Identity as NewAgentID without having a Set NoCount on or off. This query still worked and did not have to be altered at all. That brings up the question if NoCount is needed and if it is needed, do both the @@identity and the SCOPE_IDENTITY() methods need it? What is the difference between the two methods? Select NewAgentID = SCOPE_IDENTITY() Select @@Identity as NewAgentID Thanks -- Michael Dinowitz On Wed, Jan 27, 2010 at 8:19 PM, Brook Davies wrote: > > I am curious, does the query use the NOCOUNT and @@identity SQL commands?? > > > SET NOCOUNT ON > insert into users_printprofiles > ( > ) > values( > ) > select newid=@@identity > SET NOCOUNT OFF > > > Brook > > > -Original Message- > From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] > Sent: January-27-10 1:26 PM > To: cf-talk > Subject: Re: CF9 cfquery not giving same insert results as CF7 > > > OK, so it turns out that this is a known issue that came into effect between > CF 7 and CF 8. I can do one of three things here. > > 1. replace the name attribute with the result attribute: > becomes > > 2. add a result attribute of the same name to the cfquery > becomes result="AddAgent"> > > 3. use a 'generic' result value to the cfquery > becomes result="QueryResult"> > > The problem that I see is that I don't know everywhere that the query > results will be used. > Is anything returned to the 'name' variable of the query? If not, then will > removing it cause some other unforeseen effect? (experiments so far say no) > Will the use of both a name and a result conflict? (experiments so far say > no) > How will all this interact with Fusebox? Is the results of a query in an > act_ page supposed to be 'exposed' to the dsp_ page? > > If anyone has a clue or two on which way I should go on this, please let me > know. I've got to fix this in almost 700 places (the client site is a royal > mess of backed up and copied code) and a single, standard approach is what I > need. Luckily, the RegEx to find all of the locations took 60 seconds to > write. Literally. > > The hard is fast, the easy is slow. How standard for me. :) > > -- > Michael > >>I just ran into this and while it's new to me, I'm sure it's old news >>to others here. I'm looking at someones code from CF 7 where they have >>a cfquery inserting a record. The cfquery tag only has a name and a >>datasource. Immediately after the tag, there is a cfset that makes use >>of the query's name and the name of the identity field of the table >>that the data was inserted into. >> >> >>insert ... >> >> >> >>In CF 7, the newid would be the id of the newly inserted record. In CF >>9 all I get is an error. If I use a result attribute rather than a >>name attribute, I get a structure with the identity result buried in >>it. >> >>So here's the question. If it worked in 7 and not in 9, is there a >>simple fix to make it work like it did in 7 again? I'd rather not have >>to recode a whole slew of insert statements. >> >>Thanks >> >>-- >>Michael > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330254 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
I've found a query that used Select @@Identity as NewAgentID without having a Set NoCount on or off. This query still worked and did not have to be altered at all. That brings up the question if NoCount is needed and if it is needed, do both the @@identity and the SCOPE_IDENTITY() methods need it? What is the difference between the two methods? Select NewAgentID = SCOPE_IDENTITY() Select @@Identity as NewAgentID Thanks -- Michael Dinowitz On Wed, Jan 27, 2010 at 8:19 PM, Brook Davies wrote: > > I am curious, does the query use the NOCOUNT and @@identity SQL commands?? > > > SET NOCOUNT ON > insert into users_printprofiles > ( > ) > values( > ) > select newid=@@identity > SET NOCOUNT OFF > > > Brook > > > -Original Message- > From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] > Sent: January-27-10 1:26 PM > To: cf-talk > Subject: Re: CF9 cfquery not giving same insert results as CF7 > > > OK, so it turns out that this is a known issue that came into effect between > CF 7 and CF 8. I can do one of three things here. > > 1. replace the name attribute with the result attribute: > becomes > > 2. add a result attribute of the same name to the cfquery > becomes result="AddAgent"> > > 3. use a 'generic' result value to the cfquery > becomes result="QueryResult"> > > The problem that I see is that I don't know everywhere that the query > results will be used. > Is anything returned to the 'name' variable of the query? If not, then will > removing it cause some other unforeseen effect? (experiments so far say no) > Will the use of both a name and a result conflict? (experiments so far say > no) > How will all this interact with Fusebox? Is the results of a query in an > act_ page supposed to be 'exposed' to the dsp_ page? > > If anyone has a clue or two on which way I should go on this, please let me > know. I've got to fix this in almost 700 places (the client site is a royal > mess of backed up and copied code) and a single, standard approach is what I > need. Luckily, the RegEx to find all of the locations took 60 seconds to > write. Literally. > > The hard is fast, the easy is slow. How standard for me. :) > > -- > Michael > >>I just ran into this and while it's new to me, I'm sure it's old news >>to others here. I'm looking at someones code from CF 7 where they have >>a cfquery inserting a record. The cfquery tag only has a name and a >>datasource. Immediately after the tag, there is a cfset that makes use >>of the query's name and the name of the identity field of the table >>that the data was inserted into. >> >> >>insert ... >> >> >> >>In CF 7, the newid would be the id of the newly inserted record. In CF >>9 all I get is an error. If I use a result attribute rather than a >>name attribute, I get a structure with the identity result buried in >>it. >> >>So here's the question. If it worked in 7 and not in 9, is there a >>simple fix to make it work like it did in 7 again? I'd rather not have >>to recode a whole slew of insert statements. >> >>Thanks >> >>-- >>Michael > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330250 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF9 cfquery not giving same insert results as CF7
I am curious, does the query use the NOCOUNT and @@identity SQL commands?? SET NOCOUNT ON insert into users_printprofiles ( ) values( ) select newid=@@identity SET NOCOUNT OFF Brook -Original Message- From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] Sent: January-27-10 1:26 PM To: cf-talk Subject: Re: CF9 cfquery not giving same insert results as CF7 OK, so it turns out that this is a known issue that came into effect between CF 7 and CF 8. I can do one of three things here. 1. replace the name attribute with the result attribute: becomes 2. add a result attribute of the same name to the cfquery becomes 3. use a 'generic' result value to the cfquery becomes The problem that I see is that I don't know everywhere that the query results will be used. Is anything returned to the 'name' variable of the query? If not, then will removing it cause some other unforeseen effect? (experiments so far say no) Will the use of both a name and a result conflict? (experiments so far say no) How will all this interact with Fusebox? Is the results of a query in an act_ page supposed to be 'exposed' to the dsp_ page? If anyone has a clue or two on which way I should go on this, please let me know. I've got to fix this in almost 700 places (the client site is a royal mess of backed up and copied code) and a single, standard approach is what I need. Luckily, the RegEx to find all of the locations took 60 seconds to write. Literally. The hard is fast, the easy is slow. How standard for me. :) -- Michael >I just ran into this and while it's new to me, I'm sure it's old news >to others here. I'm looking at someones code from CF 7 where they have >a cfquery inserting a record. The cfquery tag only has a name and a >datasource. Immediately after the tag, there is a cfset that makes use >of the query's name and the name of the identity field of the table >that the data was inserted into. > > >insert ... > > > >In CF 7, the newid would be the id of the newly inserted record. In CF >9 all I get is an error. If I use a result attribute rather than a >name attribute, I get a structure with the identity result buried in >it. > >So here's the question. If it worked in 7 and not in 9, is there a >simple fix to make it work like it did in 7 again? I'd rather not have >to recode a whole slew of insert statements. > >Thanks > >-- >Michael ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330198 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
Depending on the database, you could also try just adding: SELECT identityfield = SCOPE_IDENTITY() to the end of the insert queries, this one is SQL Server specific though. ~Mahcsig On Wed, Jan 27, 2010 at 1:25 PM, Michael Dinowitz < mdino...@houseoffusion.com> wrote: > > OK, so it turns out that this is a known issue that came into effect > between CF 7 and CF 8. I can do one of three things here. > > 1. replace the name attribute with the result attribute: > becomes > > 2. add a result attribute of the same name to the cfquery > becomes result="AddAgent"> > > 3. use a 'generic' result value to the cfquery > becomes result="QueryResult"> > > The problem that I see is that I don't know everywhere that the query > results will be used. > Is anything returned to the 'name' variable of the query? If not, then will > removing it cause some other unforeseen effect? (experiments so far say no) > Will the use of both a name and a result conflict? (experiments so far say > no) > How will all this interact with Fusebox? Is the results of a query in an > act_ page supposed to be 'exposed' to the dsp_ page? > > If anyone has a clue or two on which way I should go on this, please let me > know. I've got to fix this in almost 700 places (the client site is a royal > mess of backed up and copied code) and a single, standard approach is what I > need. Luckily, the RegEx to find all of the locations took 60 seconds to > write. Literally. > > The hard is fast, the easy is slow. How standard for me. :) > > -- > Michael > > >I just ran into this and while it's new to me, I'm sure it's old news > >to others here. I'm looking at someones code from CF 7 where they have > >a cfquery inserting a record. The cfquery tag only has a name and a > >datasource. Immediately after the tag, there is a cfset that makes use > >of the query's name and the name of the identity field of the table > >that the data was inserted into. > > > > > >insert ... > > > > > > > >In CF 7, the newid would be the id of the newly inserted record. In CF > >9 all I get is an error. If I use a result attribute rather than a > >name attribute, I get a structure with the identity result buried in > >it. > > > >So here's the question. If it worked in 7 and not in 9, is there a > >simple fix to make it work like it did in 7 again? I'd rather not have > >to recode a whole slew of insert statements. > > > >Thanks > > > >-- > >Michael > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330197 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
OK, so it turns out that this is a known issue that came into effect between CF 7 and CF 8. I can do one of three things here. 1. replace the name attribute with the result attribute: becomes 2. add a result attribute of the same name to the cfquery becomes 3. use a 'generic' result value to the cfquery becomes The problem that I see is that I don't know everywhere that the query results will be used. Is anything returned to the 'name' variable of the query? If not, then will removing it cause some other unforeseen effect? (experiments so far say no) Will the use of both a name and a result conflict? (experiments so far say no) How will all this interact with Fusebox? Is the results of a query in an act_ page supposed to be 'exposed' to the dsp_ page? If anyone has a clue or two on which way I should go on this, please let me know. I've got to fix this in almost 700 places (the client site is a royal mess of backed up and copied code) and a single, standard approach is what I need. Luckily, the RegEx to find all of the locations took 60 seconds to write. Literally. The hard is fast, the easy is slow. How standard for me. :) -- Michael >I just ran into this and while it's new to me, I'm sure it's old news >to others here. I'm looking at someones code from CF 7 where they have >a cfquery inserting a record. The cfquery tag only has a name and a >datasource. Immediately after the tag, there is a cfset that makes use >of the query's name and the name of the identity field of the table >that the data was inserted into. > > >insert ... > > > >In CF 7, the newid would be the id of the newly inserted record. In CF >9 all I get is an error. If I use a result attribute rather than a >name attribute, I get a structure with the identity result buried in >it. > >So here's the question. If it worked in 7 and not in 9, is there a >simple fix to make it work like it did in 7 again? I'd rather not have >to recode a whole slew of insert statements. > >Thanks > >-- >Michael ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330196 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CF9 cfquery not giving same insert results as CF7
I just ran into this and while it's new to me, I'm sure it's old news to others here. I'm looking at someones code from CF 7 where they have a cfquery inserting a record. The cfquery tag only has a name and a datasource. Immediately after the tag, there is a cfset that makes use of the query's name and the name of the identity field of the table that the data was inserted into. insert ... In CF 7, the newid would be the id of the newly inserted record. In CF 9 all I get is an error. If I use a result attribute rather than a name attribute, I get a structure with the identity result buried in it. So here's the question. If it worked in 7 and not in 9, is there a simple fix to make it work like it did in 7 again? I'd rather not have to recode a whole slew of insert statements. Thanks -- Michael ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330190 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery timeout doesn't work out on jdbc driver
Or figure out why its running slowly, and make it run faster. =) Rick On Thu, Oct 8, 2009 at 12:01 PM, Adam Haskell wrote: > > Run it in a thread with CFthread and put a timeout on the cfjoin. > > Adam > > > On Thu, Oct 8, 2009 at 8:47 AM, Vamsi Pappu wrote: > >> >> Hi All, >> >> I have a situation here where cfquery doesn't seem to be performing well on >> jdbc driver, when I read in forums it was clearly mentioned that cfquery >> timeout doesn't support some drivers, Is there any another alternate to >> this. >> >> My requirement is no matter what happens, if query is not responding in >> given amount of time then it should timeout.Can anyone suggest a good >> advice. >> >> Thanks, >> Vamsi >> >> > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327052 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery timeout doesn't work out on jdbc driver
Run it in a thread with CFthread and put a timeout on the cfjoin. Adam On Thu, Oct 8, 2009 at 8:47 AM, Vamsi Pappu wrote: > > Hi All, > > I have a situation here where cfquery doesn't seem to be performing well on > jdbc driver, when I read in forums it was clearly mentioned that cfquery > timeout doesn't support some drivers, Is there any another alternate to > this. > > My requirement is no matter what happens, if query is not responding in > given amount of time then it should timeout.Can anyone suggest a good > advice. > > Thanks, > Vamsi > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327036 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfquery timeout doesn't work out on jdbc driver
How about installing SeeFusion and enabling one of its rules to kill threads that run for a certain about of time? If there is a running query it will kill it. It may be a hammer where you wanted a scalpel, but it would work. Since requests who area waiting for external I/O can't monitor themselves, often the only way to ensure such a timeout is with a second monitor thread. ~Brad Original Message Subject: cfquery timeout doesn't work out on jdbc driver From: Vamsi Pappu Date: Thu, October 08, 2009 7:47 am To: cf-talk My requirement is no matter what happens, if query is not responding in given amount of time then it should timeout.Can anyone suggest a good advice. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327034 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfquery timeout doesn't work out on jdbc driver
Hi All, I have a situation here where cfquery doesn't seem to be performing well on jdbc driver, when I read in forums it was clearly mentioned that cfquery timeout doesn't support some drivers, Is there any another alternate to this. My requirement is no matter what happens, if query is not responding in given amount of time then it should timeout.Can anyone suggest a good advice. Thanks, Vamsi ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327028 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need help with cfquery for a sort and a group
Traci, For the recordset that you already have. Pipe_id File_id File_title = 989910proposalrevision.doc 989910tableofcontents.doc 989911visio.vsd 98991208fmla.doc 928740allergies.ppt 871711 statementofwork.doc 456632 debrief0999.pdf You can just output it like so, without grouping in the query itself. #MyQry.Pipe_id# #MyQry.File_title# HTH Chuck ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326246 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Need help with cfquery for a sort and a group
Hello. I'm hoping someone here can help me. I have a search form that is submitted (with a number of criteria but for this purpose) with a proposal number (Pipe_id) and what type of file the user is looking for (proposal (chkProposal), solicitation (chkSolicitation), debrief (chkDebrief)). They have to select one file_type to get to my query (otherwise it uses another query). In the results they want to see the proposal number (*one time) and all associated documentation (field_type). There will be a number of other joins but that will follow after I work this out. Sample of records in the table of files: (You'll see pipe_id can be duplicated here but it is a unique value in its parent table) table: vSPDR_docs Pipe_id File_id File_title = 989910proposalrevision.doc 989910tableofcontents.doc 989911visio.vsd 98991208fmla.doc 928740allergies.ppt 871711 statementofwork.doc 456632 debrief0999.pdf File_id key: 0 is proposal, 1 is solicitation, 2 is debriefing Desired Output Example (if they selected all 3 file_types or proposals or solicitations) and btw 2 is a bit of a wildcard: Records MUST have 0 AND 1 file_ids (and can have 2 - but doesn't have to) == 98991 Records with 0 file_id (and possibly 2) == 92874 Records with 1 file_id (and possibly 2) == 87171 Records with just 2 == 45663 Desired Output (if they just selected 2 - debriefings): Records with just 2 == 45663 I hope this makes sense to you. I appreciate ANY help. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326244 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery
select iif(lm.user_id = s.lead_mgr, (lm.first + ' ' + lm.last) , s.lead_note) as leadMgr, iif(lm.user_id = s.AREA_MGR_RDO, lm.first + ' ' + lm.last , s.Area_Note) as director , iif(lm.user_id = s.sdir_ndir, lm.first + ' ' + lm.last , s.sdir_note) as sndir , iif(lm.user_id = s.vice_pre, lm.first + ' ' + lm.last , s.vice_note) as vicePre , iif(lm.user_id = s.fast_mgr, lm.first + ' ' + lm.last , s.fast_note) as fastMgr , from sourcebook_1 s,user_info lm where s.active = 1 and (lm.user_id = s.lead_mgr or lm.user_id = s.AREA_MGR_RDO or lm.user_id = s.sdir_ndir or lm.user_id = s.vice_pre or lm.user_id = s.fast_mgr) and s.office_id not in(36,37,38,73) order by s.office_number i am writing like this, it is working fine, thanks for your response >I think you want it this way, using a CASE statement for the leadmgr >column. I also moved all your subselects into left joins, which should >give a bit better performance. > > >select >s.address + ' ' + s.address2 + ' ' + s.address3 + ',' + s.city + ',' + >s.state + ',' + s.city as office_address, >leadmgr = case >when (lm.user_id is null) then lm.lead_note >else lm.first + ' ' + lm.last >end, >amr.first + ' ' + amr.last as director, >sn.first + ' ' + sn.last as SDIRNDIR, >vp.first + ' ' + vp.last as VICEPRE, >fm.first + ' ' + fm.last as FASTMGR >from sourcebook_1 s left outer join >user_info lm on s.lead_mgr = lm.user_id left outer join >user_info amr on s.area_mgr_rdo = amr.user_id left outer join >user_info sn on s.sdir_ndir = sn.user_id left outer join >user_info vp on s.vice_pre = vp.user_id left outer join >user_info fm on s.fast_mgr = fm.user_id >where active = 1 >and office_id not in (36,37,38,73) >order by office_number > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: cfquery
I think you want it this way, using a CASE statement for the leadmgr column. I also moved all your subselects into left joins, which should give a bit better performance. select s.address + ' ' + s.address2 + ' ' + s.address3 + ',' + s.city + ',' + s.state + ',' + s.city as office_address, leadmgr = case when (lm.user_id is null) then lm.lead_note else lm.first + ' ' + lm.last end, amr.first + ' ' + amr.last as director, sn.first + ' ' + sn.last as SDIRNDIR, vp.first + ' ' + vp.last as VICEPRE, fm.first + ' ' + fm.last as FASTMGR from sourcebook_1 s left outer join user_info lm on s.lead_mgr = lm.user_id left outer join user_info amr on s.area_mgr_rdo = amr.user_id left outer join user_info sn on s.sdir_ndir = sn.user_id left outer join user_info vp on s.vice_pre = vp.user_id left outer join user_info fm on s.fast_mgr = fm.user_id where active = 1 and office_id not in (36,37,38,73) order by office_number ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324792 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfquery
select address + ' ' + address2 + ' ' + address3 + ',' + city + ',' + state + ',' + city as office_address, (select first + ' ' + last as full from user_info where user_id =lead_mgr ) as leadmgr, (here if user_id neq lead_mgr i have to return lead_note column) how to do this...please help me (select first + ' ' + last as full from user_info where user_id = AREA_MGR_RDO ) as director, (select first + ' ' + last as full from user_info where user_id = SDIR_NDIR) as SDIRNDIR, (select first + ' ' + last as full from user_info where user_id = VICE_PRE ) as VICEPRE, (select first + ' ' + last as full from user_info where user_id = FAST_MGR ) as FASTMGR from sourcebook_1 where active = 1 and office_id not in(36,37,38,73) order by office_number ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324789 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFLOOP inside a CFQuery
After seeing how malicious and how effective hackers were with automated sql injection last year I vowed to never leave an integer field unturned without using cfqueryparameters it's just too easy to use it and the benefit is unquestionable especially for integer fields. I am also seeing some addtl benefits with other field types now as well but integers are required. -- Ryan LeTulle On Tue, Jun 9, 2009 at 9:51 PM, Gabriel wrote: > > Will, > > Justin is right, just because your form is behind a username/password it by > no means guarantees that the people accessing that form aren't malicious. > As > for whether there are scenarios where you shouldn't use queryparam? That's > probably open for debate (it's certainly been debated before), however I've > not come across a situation (except the caching issue as mentioned) where > it's been of any benefit to NOT use queryparam. > > - Gabriel > > -Original Message- > From: Justin Scott [mailto:jscott-li...@gravityfree.com] > Sent: Wednesday, 10 June 2009 12:42 PM > To: cf-talk > Subject: RE: CFLOOP inside a CFQuery > > > > WOW! Thanks for all the feedback! One question about CFQUERYPARAM, I > > use this when I accept anything that is available to the general > > public, but is it necessary to use this when the form is only > > accessible via username/password? > > I would use it regardless of who is going to be hitting those queries. You > never know when some nefarious person is going to break into an admin > account and start probing around. > > > Is there ever a reason not to use CFQUERYPARAM? > > Before ColdFusion 8 was released, you could not use CFQUERYPARAM in > conjunction with a cached query. CF8 now allows that (yea!). > > > -Justin > > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323313 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
