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]
