Re: How to close Excel ODBC connection at end of query
CFSET CFUSION_DISABLE_DBCONNECTIONS(excel, 1) Ray At 08:45 PM 4/18/2002 +0100, you wrote: Hi, Further to my problem of aparently retrieving cached query results when using an excel datasource (see earlier email titled Strange behaviour with excel datasource), I've discovered that CF seems to be keeping the ODBC connection alive across requests. Does anybody know how to force a shutdown of excel? I've tried a few different methods to date. Namely: CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 1) CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 0) to disable and enable write access to the datasource OR using CFOBJECT !--- Try to connect to the Excel application object --- CFTRY !--- If it exists, connect to it --- CFOBJECT ACTION=CONNECT CLASS=Excel.Application NAME=objExcel TYPE=COM CFCATCH !--- The object doesn't exist, so create it --- CFOBJECT ACTION=CREATE CLASS=Excel.Application NAME=objExcel TYPE=COM /CFCATCH /CFTRY CFSCRIPT // Quit Excel objExcel.Quit(); // Release the object objExcel = Nothing; /CFSCRIPT to try to shut down the connection. This is really frustrating because all I am trying to do is a simple update query (to insert variables to a formula), followed by a select statement to retrieve the result of a formula contained in the excel file. This is analogous to doing an insert, followed by a select to retrieve a new ID numnber using any other RDBMS ODBC drivers. I've even created and triggered (using cfexecute)a DTS package to import the data into SQL 7 after having carried out the update query, but this gives me an error message indicating that the excel file is already in use, despite my various attempts at forcing closure of the file. Any help would be much appreciated, Dave __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: How to close Excel ODBC connection at end of query
I've already tried this and although it does remove the datasource connection, it doesnt resolve my problem of being returned a cached result in my select query. -Original Message- From: Ray Bujarski [mailto:[EMAIL PROTECTED]] Sent: 18 April 2002 20:53 To: CF-Talk Subject: Re: How to close Excel ODBC connection at end of query CFSET CFUSION_DISABLE_DBCONNECTIONS(excel, 1) Ray At 08:45 PM 4/18/2002 +0100, you wrote: Hi, Further to my problem of aparently retrieving cached query results when using an excel datasource (see earlier email titled Strange behaviour with excel datasource), I've discovered that CF seems to be keeping the ODBC connection alive across requests. Does anybody know how to force a shutdown of excel? I've tried a few different methods to date. Namely: CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 1) CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 0) to disable and enable write access to the datasource OR using CFOBJECT !--- Try to connect to the Excel application object --- CFTRY !--- If it exists, connect to it --- CFOBJECT ACTION=CONNECT CLASS=Excel.Application NAME=objExcel TYPE=COM CFCATCH !--- The object doesn't exist, so create it --- CFOBJECT ACTION=CREATE CLASS=Excel.Application NAME=objExcel TYPE=COM /CFCATCH /CFTRY CFSCRIPT // Quit Excel objExcel.Quit(); // Release the object objExcel = Nothing; /CFSCRIPT to try to shut down the connection. This is really frustrating because all I am trying to do is a simple update query (to insert variables to a formula), followed by a select statement to retrieve the result of a formula contained in the excel file. This is analogous to doing an insert, followed by a select to retrieve a new ID numnber using any other RDBMS ODBC drivers. I've even created and triggered (using cfexecute)a DTS package to import the data into SQL 7 after having carried out the update query, but this gives me an error message indicating that the excel file is already in use, despite my various attempts at forcing closure of the file. Any help would be much appreciated, Dave __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: How to close Excel ODBC connection at end of query
You might try an intentionally bad SQL statement, and wrap it with a cftry/cfcatch to keep it quiet. cftry cfquery... select Mr_T from YoMomma where PityDaFool = WhatchooTalkinBoutWillis /cfquery cfcatch !--- do nothin' --- /cfcatch /cftry I think bad SQL statements terminate the connection. Worth a shot... Jamie On Thu, 18 Apr 2002 21:17:16 +0100, in cf-talk you wrote: I've already tried this and although it does remove the datasource connection, it doesnt resolve my problem of being returned a cached result in my select query. -Original Message- From: Ray Bujarski [mailto:[EMAIL PROTECTED]] Sent: 18 April 2002 20:53 To: CF-Talk Subject: Re: How to close Excel ODBC connection at end of query CFSET CFUSION_DISABLE_DBCONNECTIONS(excel, 1) Ray At 08:45 PM 4/18/2002 +0100, you wrote: Hi, Further to my problem of aparently retrieving cached query results when using an excel datasource (see earlier email titled Strange behaviour with excel datasource), I've discovered that CF seems to be keeping the ODBC connection alive across requests. Does anybody know how to force a shutdown of excel? I've tried a few different methods to date. Namely: CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 1) CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 0) to disable and enable write access to the datasource OR using CFOBJECT !--- Try to connect to the Excel application object --- CFTRY !--- If it exists, connect to it --- CFOBJECT ACTION=CONNECT CLASS=Excel.Application NAME=objExcel TYPE=COM CFCATCH !--- The object doesn't exist, so create it --- CFOBJECT ACTION=CREATE CLASS=Excel.Application NAME=objExcel TYPE=COM /CFCATCH /CFTRY CFSCRIPT // Quit Excel objExcel.Quit(); // Release the object objExcel = Nothing; /CFSCRIPT to try to shut down the connection. This is really frustrating because all I am trying to do is a simple update query (to insert variables to a formula), followed by a select statement to retrieve the result of a formula contained in the excel file. This is analogous to doing an insert, followed by a select to retrieve a new ID numnber using any other RDBMS ODBC drivers. I've even created and triggered (using cfexecute)a DTS package to import the data into SQL 7 after having carried out the update query, but this gives me an error message indicating that the excel file is already in use, despite my various attempts at forcing closure of the file. Any help would be much appreciated, Dave __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: How to close Excel ODBC connection at end of query
Perhaps you could build a COM object to access the data? (Since ADO gives you more control over when the connection is opened/closed) Or if you're good with COM in C++, you could build a CFX (gives you the advantage of being able to build a cf recordset) --- Billy Cravens -Original Message- From: Dave Wilson [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 2:45 PM To: CF-Talk Subject: How to close Excel ODBC connection at end of query Hi, Further to my problem of aparently retrieving cached query results when using an excel datasource (see earlier email titled Strange behaviour with excel datasource), I've discovered that CF seems to be keeping the ODBC connection alive across requests. Does anybody know how to force a shutdown of excel? I've tried a few different methods to date. Namely: CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 1) CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 0) to disable and enable write access to the datasource OR using CFOBJECT !--- Try to connect to the Excel application object --- CFTRY !--- If it exists, connect to it --- CFOBJECT ACTION=CONNECT CLASS=Excel.Application NAME=objExcel TYPE=COM CFCATCH !--- The object doesn't exist, so create it --- CFOBJECT ACTION=CREATE CLASS=Excel.Application NAME=objExcel TYPE=COM /CFCATCH /CFTRY CFSCRIPT // Quit Excel objExcel.Quit(); // Release the object objExcel = Nothing; /CFSCRIPT to try to shut down the connection. This is really frustrating because all I am trying to do is a simple update query (to insert variables to a formula), followed by a select statement to retrieve the result of a formula contained in the excel file. This is analogous to doing an insert, followed by a select to retrieve a new ID numnber using any other RDBMS ODBC drivers. I've even created and triggered (using cfexecute)a DTS package to import the data into SQL 7 after having carried out the update query, but this gives me an error message indicating that the excel file is already in use, despite my various attempts at forcing closure of the file. Any help would be much appreciated, Dave __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: How to close Excel ODBC connection at end of query
What about the Maintain database connections setting for the DSN under ColdFusion Administrator? This should take off the lock place on the XLS file. I am not sure if this will work for your problem, but you might want to give it a shot. +--+---+ | SCOTT VAN VLIET | SBC SERVICES, INC.| | Senior Analyst | ITO Enterprise Tools | | Tel: 858.886.3878| 7337 Trade St. Room 4000 | | Pgr: 858.536.0070| San Diego, CA 92121 | | Email: [EMAIL PROTECTED]| http://www.sbc.com| +--+---+ -Original Message- From: Dave Wilson [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 12:45 PM To: CF-Talk Subject: How to close Excel ODBC connection at end of query Hi, Further to my problem of aparently retrieving cached query results when using an excel datasource (see earlier email titled Strange behaviour with excel datasource), I've discovered that CF seems to be keeping the ODBC connection alive across requests. Does anybody know how to force a shutdown of excel? I've tried a few different methods to date. Namely: CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 1) CFSET CFUSION_DISABLE_DBCONNECTIONS(exceldsn, 0) to disable and enable write access to the datasource OR using CFOBJECT !--- Try to connect to the Excel application object --- CFTRY !--- If it exists, connect to it --- CFOBJECT ACTION=CONNECT CLASS=Excel.Application NAME=objExcel TYPE=COM CFCATCH !--- The object doesn't exist, so create it --- CFOBJECT ACTION=CREATE CLASS=Excel.Application NAME=objExcel TYPE=COM /CFCATCH /CFTRY CFSCRIPT // Quit Excel objExcel.Quit(); // Release the object objExcel = Nothing; /CFSCRIPT to try to shut down the connection. This is really frustrating because all I am trying to do is a simple update query (to insert variables to a formula), followed by a select statement to retrieve the result of a formula contained in the excel file. This is analogous to doing an insert, followed by a select to retrieve a new ID numnber using any other RDBMS ODBC drivers. I've even created and triggered (using cfexecute)a DTS package to import the data into SQL 7 after having carried out the update query, but this gives me an error message indicating that the excel file is already in use, despite my various attempts at forcing closure of the file. Any help would be much appreciated, Dave __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
