cfquery and mysql help
Hi, im sure there must be a better way of doing the following code, and much quicker. i have a query which is built up from data in excel - so i used a excel2query function on it i want to check through the database to see if any of the items within my query exist in the database. so i am looping through the first query checking each row to see if it exists and then doing a cfif statement that then goes on to do further processing if a record does not exist in the database i am sure there must be a better way than this. alot of my code is structured in this way so i think i will benefit hugely by any suggestions that you have about a more efficient way of doing this i was thinking that it could be possible to combine the 2 queries on certain columns and then get it to give me a result of only the rows which are not found in both sides. im not too sure how to achieve this though i would appreciate your feedback thanks richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303879 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 and mysql help
Do you have the query? -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Monday, April 21, 2008 10:53 AM To: CF-Talk Subject: cfquery and mysql help Hi, im sure there must be a better way of doing the following code, and much quicker. i have a query which is built up from data in excel - so i used a excel2query function on it i want to check through the database to see if any of the items within my query exist in the database. so i am looping through the first query checking each row to see if it exists and then doing a cfif statement that then goes on to do further processing if a record does not exist in the database i am sure there must be a better way than this. alot of my code is structured in this way so i think i will benefit hugely by any suggestions that you have about a more efficient way of doing this i was thinking that it could be possible to combine the 2 queries on certain columns and then get it to give me a result of only the rows which are not found in both sides. im not too sure how to achieve this though i would appreciate your feedback thanks richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303881 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 and mysql help
hi andy query 1 has 2 columns firstName and lastName then i want to query the database to ensure that each firstName and lastName combination exists in the database, if not then i want it to return me a list of those that dont. i have achieved this through a loop but i am thinking there must be a quicker way of doing this directly in a cfquery, as it is taking too long when there are thousands of names it has to check thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303889 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 and mysql help
You could try something like cfquery name=combined dbtype=query SELECT firstName, lastName FROM query1 WHERE NOT EXISTS ( SELECT firstName, lastName FROM query2 WHERE firstName = query1.firstName AND lastName = query1.lastName ) /cfquery No guarantees - this is standard SQL and would work with MySQL, but I'm not sure it will work with a query-of-queries. Matt hi andy query 1 has 2 columns firstName and lastName then i want to query the database to ensure that each firstName and lastName combination exists in the database, if not then i want it to return me a list of those that dont. i have achieved this through a loop but i am thinking there must be a quicker way of doing this directly in a cfquery, as it is taking too long when there are thousands of names it has to check thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303908 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 and mysql help
thanks Matt, i will see if this works in query on query You could try something like cfquery name=combined dbtype=query SELECT firstName, lastName FROM query1 WHERE NOT EXISTS ( SELECT firstName, lastName FROM query2 WHERE firstName = query1.firstName AND lastName = query1.lastName ) /cfquery No guarantees - this is standard SQL and would work with MySQL, but I'm not sure it will work with a query-of-queries. Matt hi andy query 1 has 2 columns firstName and lastName then i want to query the database to ensure that each firstName and lastName combination exists in the database, if not then i want it to return me a list of those that dont. i have achieved this through a loop but i am thinking there must be a quicker way of doing this directly in a cfquery, as it is taking too long when there are thousands of names it has to check thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303920 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFQuery and MySQL
I have a new MySQL connection set up in CF7 - verifies correctly (called mysqldb). Within this connection I have a db called ABC, and then tables within this db. I can't get the cfquery to work correctly: If I use: cfquery name=insertA datasource=mysqldb I get the error: No database selected If I use: cfquery name=insertA datasource=abc I get the error: Datasource ABC could not be found I suspect the first error is closer to the solution. How do I get a cfquery to point to the right db? THANKS! Mark ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282696 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 and MySQL
This may help with the set up (but I'm assuming as it verifies it probably ok): http://www.oxalto.co.uk/index.cfm/2007/7/2/Setting-up-a-MySQL- database-with-Unicode--properly-in-Coldfusion-7 T On 2 Jul 2007, at 13:18, Mark Leder wrote: I have a new MySQL connection set up in CF7 - verifies correctly (called mysqldb). Within this connection I have a db called ABC, and then tables within this db. I can't get the cfquery to work correctly: If I use: cfquery name=insertA datasource=mysqldb I get the error: No database selected If I use: cfquery name=insertA datasource=abc I get the error: Datasource ABC could not be found I suspect the first error is closer to the solution. How do I get a cfquery to point to the right db? THANKS! Mark ~| CF 8 â Scorpio beta now available, easily build great internet experiences â Try it now on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282699 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 and MySQL
That worked, thanks for clearing that up for me. -Original Message- From: Tom King [mailto:[EMAIL PROTECTED] Sent: Monday, July 02, 2007 8:40 AM To: CF-Talk Subject: Re: CFQuery and MySQL This may help with the set up (but I'm assuming as it verifies it probably ok): http://www.oxalto.co.uk/index.cfm/2007/7/2/Setting-up-a-MySQL- database-with-Unicode--properly-in-Coldfusion-7 T On 2 Jul 2007, at 13:18, Mark Leder wrote: I have a new MySQL connection set up in CF7 - verifies correctly (called mysqldb). Within this connection I have a db called ABC, and then tables within this db. I can't get the cfquery to work correctly: If I use: cfquery name=insertA datasource=mysqldb I get the error: No database selected If I use: cfquery name=insertA datasource=abc I get the error: Datasource ABC could not be found I suspect the first error is closer to the solution. How do I get a cfquery to point to the right db? THANKS! Mark ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282703 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cfquery crashes MySQL
Someone using my blog has reported that posting a comment crashes his
MySQL server.
When you post a comment, you get the following error, which I'm unable
to duplicate myself, but it occurs every time on his setup, which is
Coldfusion MX 6.1 (I think) on Windows with MySQL 4.0.x (current)
running on the same box. Subsequent requests result in a Mysql not
found.. error, because mysql crashed.
The error message is followed by the actual query
--
Error Message
--
Communication link failure: java.net.SocketException
The error occurred in D:\home\sites\ctsix.org\wwwroot\cfcs\blogcfm.cfc:
line 967
Called from D:\home\sites\ctsix.org\wwwroot\entry.cfm: line 54
Called from D:\home\sites\ctsix.org\wwwroot\entry.cfm: line 9
Called from D:\home\sites\ctsix.org\wwwroot\entry.cfm: line 1
Called from D:\home\sites\ctsix.org\wwwroot\1\2005\05\A-Bug.cfm: line 2
Called from D:\home\sites\ctsix.org\wwwroot\cfcs\blogcfm.cfc: line 967
Called from D:\home\sites\ctsix.org\wwwroot\entry.cfm: line 54
Called from D:\home\sites\ctsix.org\wwwroot\entry.cfm: line 9
Called from D:\home\sites\ctsix.org\wwwroot\entry.cfm: line 1
Called from D:\home\sites\ctsix.org\wwwroot\1\2005\05\A-Bug.cfm: line 2
965 : #CreateODBCDateTime(Now())#,
966 : cfqueryparam cfsqltype=cf_sql_char
value=#arguments.details.IP#,
967 : cfqueryparam cfsqltype=cf_sql_longvarchar
value=#arguments.details.COMMENT_TEXT#
968 : )
969 : /cfquery
ERROR MESSAGE
SQLINSERT INTO BLOG_COMMENTS ( BLOG_ID, ENTRY_ID, COMMENT_ID,
USER_ID, UNAME, EMAIL, WEBSITE, ENTRY_DATE, IP, COMMENT_TEXT ) VALUES (
(param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) ,
(param 7) , {ts '2005-06-01 14:40:38'}, (param 8) , (param 9) )
DATASOURCE ctsix_data
SQLSTATE 08S01
ACTUAL QUERY CODE
cfquery name=qry datasource=#this.primarydb#
INSERT INTO BLOG_COMMENTS
(
BLOG_ID,
ENTRY_ID,
COMMENT_ID,
USER_ID,
UNAME,
EMAIL,
WEBSITE,
ENTRY_DATE,
IP,
COMMENT_TEXT
) VALUES (
cfqueryparam cfsqltype=cf_sql_char value=#arguments.BLOG_ID#,
cfqueryparam cfsqltype=cf_sql_char
value=#arguments.details.ENTRY_ID#,
cfqueryparam cfsqltype=cf_sql_integer value=#retVal.COMMENT_ID#,
cfqueryparam cfsqltype=cf_sql_char
value=#arguments.details.USER_ID#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#arguments.details.UNAME#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#arguments.details.EMAIL#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#arguments.details.WEBSITE#,
#CreateODBCDateTime(Now())#,
cfqueryparam cfsqltype=cf_sql_char value=#arguments.details.IP#,
cfqueryparam cfsqltype=cf_sql_longvarchar
value=#arguments.details.COMMENT_TEXT#
)
/cfquery
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208274
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: cfquery crashes MySQL
Someone using my blog has reported that posting a comment crashes his MySQL server. You might suggest they update their JDBC drivers to the ones designed for MySQL 4. http://www.mysql.com/ has the gory. -- Damien McKenna - Web Developer - [EMAIL PROTECTED] The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 #include stdjoke.h ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208285 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Using COMMIT inside cfquery with MySQL give error
Hi all after insert a user record I need to show that same record to that user. But using SELECT statement in other cfquery don't show that user record. Please look this simple example: cfquery datasource=#application.dsn# name=insertuser INSERT INTO resourcebank ( userID,corpID,statusID,age1 ) VALUES (8EFACE46-795D-1267-34CB31AA6276B84F,17 ,1,37 ) /cfquery Insert works fine. By now I need to show user info: cfquery datasource=#application.dsn# name=getuserinfo SELECT userID,statusID FROM resourcebank WHERE userID = 8EFACE46-795D-1267-34CB31AA6276B84F /cfquery This query returns ZERO records inserting COMMIT after INSERT query don't work. How to refresh that connection to work like a charm? Cheers Marco ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185791 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Using COMMIT inside cfquery with MySQL give error
Works now I insert this query after the first INSERT query cfquery name=commituser datasource=#application.dsn# COMMIT; /cfquery Cheers Hi all after insert a user record I need to show that same record to that user. But using SELECT statement in other cfquery don't show that user record. Please look this simple example: cfquery datasource=#application.dsn# name=insertuser INSERT INTO resourcebank ( userID,corpID,statusID,age1 ) VALUES (8EFACE46-795D-1267-34CB31AA6276B84F,17 ,1,37 ) /cfquery Insert works fine. By now I need to show user info: cfquery datasource=#application.dsn# name=getuserinfo SELECT userID,statusID FROM resourcebank WHERE userID = 8EFACE46-795D-1267-34CB31AA6276B84F /cfquery This query returns ZERO records inserting COMMIT after INSERT query don't work. How to refresh that connection to work like a charm? Cheers Marco ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185795 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Using COMMIT inside cfquery with MySQL give error
Works now I insert this query after the first INSERT query cfquery name=commituser datasource=#application.dsn# COMMIT; /cfquery Cheers Hi all after insert a user record I need to show that same record to that user. But using SELECT statement in other cfquery don't show that user record. Please look this simple example: cfquery datasource=#application.dsn# name=insertuser INSERT INTO resourcebank ( userID,corpID,statusID,age1 ) VALUES (8EFACE46-795D-1267-34CB31AA6276B84F,17 ,1,37 ) /cfquery Insert works fine. By now I need to show user info: cfquery datasource=#application.dsn# name=getuserinfo SELECT userID,statusID FROM resourcebank WHERE userID = 8EFACE46-795D-1267-34CB31AA6276B84F /cfquery This query returns ZERO records inserting COMMIT after INSERT query don't work. How to refresh that connection to work like a charm? Cheers Marco ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185794 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Multiple Statements in a cfquery with mySQL
Just a quickie, Running multiple statements at once works fine in mySQL, but not from a cfquery (cf5), I assume it's an ODBC driver issue, or is it cf? Anyone know for sure? Craig Dudley Senior Developer Netstep Corporate Communications Ltd Direct Line: +44(0) 1422 319712 Phone: +44(0) 1422 200308 Fax: +44(0) 1422 200306 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] MS Messanger: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] www: www.netstep.co.uk ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Multiple Statements in a cfquery with mySQL
I do believe it depends on what you are doing in the SQL statements. As long as you are only bringing back one recordset it should not be a problem. -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 6:37 AM To: CF-Talk Subject: Multiple Statements in a cfquery with mySQL Just a quickie, Running multiple statements at once works fine in mySQL, but not from a cfquery (cf5), I assume it's an ODBC driver issue, or is it cf? Anyone know for sure? Craig Dudley Senior Developer Netstep Corporate Communications Ltd Direct Line: +44(0) 1422 319712 Phone: +44(0) 1422 200308 Fax: +44(0) 1422 200306 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] MS Messanger: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] www: www.netstep.co.uk ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Multiple Statements in a cfquery with mySQL
With SQL server that's true yes, no issues there. But not with mySQL or so it seems, the ODBC drivers chokes the instant you start a 2nd statement no matter what it is (or so it seems). Just wondering why really and if it's ever likely to change with future realeases of mySQL ODBC or CF. -Original Message- From: Ken Wilson [mailto:[EMAIL PROTECTED] Sent: 24 June 2003 11:49 To: CF-Talk Subject: RE: Multiple Statements in a cfquery with mySQL I do believe it depends on what you are doing in the SQL statements. As long as you are only bringing back one recordset it should not be a problem. -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 6:37 AM To: CF-Talk Subject: Multiple Statements in a cfquery with mySQL Just a quickie, Running multiple statements at once works fine in mySQL, but not from a cfquery (cf5), I assume it's an ODBC driver issue, or is it cf? Anyone know for sure? Craig Dudley Senior Developer Netstep Corporate Communications Ltd Direct Line: +44(0) 1422 319712 Phone: +44(0) 1422 200308 Fax: +44(0) 1422 200306 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] MS Messanger: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] www: www.netstep.co.uk ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Cfquery in mysql part 2
Ok, so I got that, and a few others to work, now I have a bit of an issue with my delete statements cfquery DELETE * FROM PPD WHERE ID = #url.id# /cfquery Now I tried using cfqueryparam for the url.id but none worked! the field type is tinyint(4)... Help!!! Ryan ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cfquery in mysql part 2
Remove the asterisk. ~Todd At 05:59 PM 1/17/2003 +, you wrote: Ok, so I got that, and a few others to work, now I have a bit of an issue with my delete statements cfquery DELETE * FROM PPD WHERE ID = #url.id# /cfquery Now I tried using cfqueryparam for the url.id but none worked! the field type is tinyint(4)... Help!!! Ryan -- Todd Rafferty ([EMAIL PROTECTED]) - http://www.web-rat.com/ Team Macromedia Volunteer for ColdFusion http://www.macromedia.com/support/forums/team_macromedia/ http://www.devmx.com/ -- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
