Page 1 of 1

Timeout ODBC Exec()

Posted: Wed Apr 21, 2010 5:40 am
by zg2pro
Hi,

The size of my data is giant, and sometimes, regarding the query, odbc loops infintely and totally blocks navigation on the website, until I restart my MSSQL server.

Hence, I want to create a timeout for odbc_exec(), able to stop the process on the DBMS after some time.

I couldn't find many solutions, but right now, my code looks like this:

Code: Select all

function run_in_bg($cmd, $winStyle = 0, $waitOnReturn = false) {
    	$WshShell = new COM("WScript.Shell");
    	$oExec = $WshShell->Run($cmd, $winStyle, $waitOnReturn);
    	$WshShell->Release();
    
    	return $oExec;
} 
	
function query_database($query, $criteria = null){
	$connection = DB_api::get_connection();
 
	$pid = odbc_prepare ($connection, $query);
	$pid2 = run_in_bg('c:\wamp\bin\php\php.5.2.11\php.exe sleep_and_rollback.php?pid='.$pid);
    		
	$result = odbc_execute($res, null) or $this->logger->err('DB_API: ODBC failed executing query');
		
	//if odbc_execute is correct, then we stop $pid2 before it rollbacks the execution.
	run_in_bg("TASKKILL /PID ".$pid2);
 
     //(...end of method to treat result set..)
}
with sleep_and_rollback.php:

Code: Select all

<?php 
$pid = $_GET['pid'];
sleep(10);
odbc_rollback();
?>
My goal is to have two theads, one to execute the query and another one to count 10 seconds. When query execution is up, we cut out time counting ; when time count is up, we cut out query execution using the rollback.

But of course, it doesn't work. Obviously, if another solution could let me limit execution time directly at SQL Server level, it'd be valid.

Thanks a lot for any of your time and help.

Re: Timeout ODBC Exec()

Posted: Mon May 31, 2010 2:21 am
by zg2pro
I got a feedback from another website:
Dimitri I.
Php should give you an attribute to set the timeout, then you include it in a "try catch" and voila!
but this is not normal, your method odbc_execute should necessarily have a default timeout.

My answer:
Gregory A.:
That's true, when the query execution is long, PHP cuts the "communication" with the SQL Server and then the same PHP server attempts to continue using the website as if nothing had happened. Unfortunately Macrosuck designed SQL Server so well, process on the db keeps running in circles and we need to send him a kill signal.
the site works well globally, although today I have still not solved the trick.