ADOdb Library for PHP Manual
Prev LogSQL method, fnExecute and fnCacheExecute properties Next

LogSQL($enable=true)

Call this method to install a SQL logging and timing function (using fnExecute). Then all SQL statements are logged into an adodb_logsql table in a database. If the adodb_logsql table does not exist, ADOdb will create the table if you have the appropriate permissions. Here are samples of the DDL for selected databases:

		mysql:
		CREATE TABLE adodb_logsql (
		  created datetime NOT NULL,
		  sql0 varchar(250) NOT NULL,
		  sql1 text NOT NULL,
		  params text NOT NULL,
		  tracer text NOT NULL,
		  timer decimal(16,6) NOT NULL
		)
		
		postgres:
		CREATE TABLE adodb_logsql (
		  created timestamp NOT NULL,
		  sql0 varchar(250) NOT NULL,
		  sql1 text NOT NULL,
		  params text NOT NULL,
		  tracer text NOT NULL,
		  timer decimal(16,6) NOT NULL
		)
		
		mssql:
		CREATE TABLE adodb_logsql (
		  created datetime NOT NULL,
		  sql0 varchar(250) NOT NULL,
		  sql1 varchar(4000) NOT NULL,
		  params varchar(3000) NOT NULL,
		  tracer varchar(500) NOT NULL,
		  timer decimal(16,6) NOT NULL
		)
		
		oci8:
		CREATE TABLE adodb_logsql (
		  created date NOT NULL,
		  sql0 varchar(250) NOT NULL,
		  sql1 varchar(4000) NOT NULL,
		  params varchar(4000),
		  tracer varchar(4000),
		  timer decimal(16,6) NOT NULL
		)
Usage:
	$conn->LogSQL(); // turn on logging
	  :
	$conn->Execute(...);
	  :
	$conn->LogSQL(false); // turn off logging
	
	# output summary of SQL logging results
	$perf = NewPerfMonitor($conn);
	echo $perf->SuspiciousSQL();
	echo $perf->ExpensiveSQL();

One limitation of logging is that rollback also prevents SQL from being logged.

Since ADOdb 3.91. Also see Performance Monitor.

fnExecute and fnCacheExecute properties

These two properties allow you to define bottleneck functions for all sql statements processed by ADOdb. This allows you to perform statistical analysis and query-rewriting of your sql. For example, to count all cached queries and non-cached queries, you can do this:

# $db is the connection object
function &CountExecs($db, $sql, $inputarray)
{
global $EXECS;
	
	if (!is_array(inputarray)) $EXECS++;
	# handle 2-dimensional input arrays
	else if (is_array(reset($inputarray))) $EXECS += sizeof($inputarray);
	else $EXECS++;
	
	$null = null;
	return $null;
}

# $db is the connection object
function CountCachedExecs($db, $secs2cache, $sql, $inputarray)
{
global $CACHED; $CACHED++; }
$db = NewADOConnection('mysql'); $db->Connect(...); $db->fnExecute = 'CountExecs'; $db->fnCacheExecute = 'CountCachedExecs'; : :
# After many sql statements:` printf("<p>Total queries=%d; total cached=%d</p>",$EXECS+$CACHED, $CACHED);

The fnExecute function is called before the sql is parsed and executed, so you can perform a query rewrite. If you are passing in a prepared statement, then $sql is an array (see Prepare). The fnCacheExecute function is only called if the recordset returned was cached. The function parameters match the Execute and CacheExecute functions respectively, except that $this (the connection object) is passed as the first parameter.

Since ADOdb 3.91, the behaviour of fnExecute varies depending on whether the defined function returns a value. If it does not return a value, then the $sql is executed as before. This is useful for query rewriting or counting sql queries.

On the other hand, you might want to replace the Execute function with one of your own design. If this is the case, then have your function return a value. If a value is returned, that value is returned immediately, without any further processing. This is used internally by ADOdb to implement LogSQL() functionality.


Prev Home Next
MetaPrimaryKeys Up Bind

Sponsored by phpLens