Timeout ODBC Exec()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zg2pro
Forum Newbie
Posts: 3
Joined: Wed Apr 21, 2010 5:08 am

Timeout ODBC Exec()

Post 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.
zg2pro
Forum Newbie
Posts: 3
Joined: Wed Apr 21, 2010 5:08 am

Re: Timeout ODBC Exec()

Post 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.
Post Reply