Hi, I've tried without success to set a timeout for SQL queries on a MySQL server (version 4.0.12 - so far the latest production release) running on a Windows XP SP1 box. The calling program is a Web site developed in ASP (going through ADO to access MyODBC...).
I tried many different methods but there is no way I can STOP a SQL query that takes too long to execute (like... more than 5 minutes) and RAISE AN ERROR to the calling application (in that case an ASP page). METHOD 1: ADODB.Connection.CommandTimeout ========= <% var strConnectionString = "DSN=MyDSN"; var strSQLRequest = "SELECT * FROM MyHugeTable"; // Create connection object var objConnection = Server.CreateObject("ADODB.Connection"); // Set CommandTimeout to 30 seconds (on the Connection object) objConnection.CommandTimeout = 30; // Open the connection objConnection.Open(strConnectionString); // Create recordset object var objRecordset = Server.CreateObject("ADODB.recordset"); // Execute SQL request objRecordset.Open(strSQLRequest, objConnection); // The above line is very long to execute and NEVER TIMES OUT!!! // Just check if it's open and not empty Response.Write("objRecordset.EOF = "+objRecordset.EOF+"<br>"); %> METHOD 2: ADODB.Command.CommandTimeout ========= <% var strConnectionString = "DSN=MyDSN"; var strSQLRequest = "SELECT * FROM MyHugeTable"; // Create connection object var objConnection = Server.CreateObject("ADODB.Connection"); // Open the connection objConnection.Open(strConnectionString); var objCommand = Server.CreateObject("ADODB.Command"); objCommand.ActiveConnection = objConnection; objCommand.CommandText = strSQLRequest; // Set CommandTimeout to 30 seconds (this time on the Command object) objCommand.CommandTimeout = 30; // Execute SQL request objRecordset = objCommand.Execute(); // The above line is very long to execute and NEVER TIMES OUT!!! // Just check if it's open and not empty Response.Write("objRecordset.EOF = "+objRecordset.EOF+"<br>"); %> METHOD 3: ADODB.Connection.Properties("General Timeout") ========= <% var strConnectionString = "DSN=MyDSN"; var strSQLRequest = "SELECT * FROM MyHugeTable"; // Create connection object var objConnection = Server.CreateObject("ADODB.Connection"); // Set General Timeout to 30 (seconds?) objConnection.Properties("General Timeout").Value = 30; // Open the connection objConnection.Open(strConnectionString); // Create recordset object var objRecordset = Server.CreateObject("ADODB.recordset"); // Execute SQL request objRecordset.Open(strSQLRequest, objConnection); // The above line is very long to execute and NEVER TIMES OUT!!! // Just check if it's open and not empty Response.Write("objRecordset.EOF = "+objRecordset.EOF+"<br>"); %> Nothing works... Has anybody ever tried this? Am I the only one with this problem? Is there any known solution to that problem? Note: I tried this on a Windows XP SP1 box as well as on a Windows 2000 Server SP3, with MySQL 4.0.12 and MyODBC 3.51.05 (all production releases), and MDAC 2.7 Emmanuel KARTMANN Web Consultant Replay Software Development Company Email: [EMAIL PROTECTED] Phone: +33 (0)6 60 41 68 83 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]