PHP Classes
elePHPant
Icontem

PHP MariaDB Execute Compound Statement: Execute compound statements embedded in PHP code

Recommend this page to a friend!
  Info   View files Documentation   View files View files (4)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2019-10-22 (9 days ago) RSS 2.0 feedNot yet rated by the usersTotal: 38 This week: 8All time: 9,676 This week: 49Up
Version License PHP version Categories
mysql-mariadb-embedd 1.0.0GNU General Publi...5PHP 5, Databases, Parsers
Description Author

This class can execute compound statements embedded in PHP code.

It can parse the PHP script that is calling the class to extract compound SQL statements that are embedded in the script as PHP comments with certain delimiting marks inside the comments.

The class can execute the extracted SQL statements and returns an array of possible query result sets that may be returned by the MySQL MariaDB server.

Innovation Award
PHP Programming Innovation award nominee
October 2019
Nominee
Vote
The MySQL MariaDB server allows executing compound statements that are made of multiple SQL statements.

Usually an application would have to install these compound statments before they can be run by the database server.

This package simplifies the installation of and the execution of compound SQL statements by providing means to parse PHP scripts that call this package to extract the SQL statements to be extracted from the script files.

Then the package can execute those statements and return one more result sets that are returned by the server.

Manuel Lemos
  Performance   Level  
Name: Santo Nuzzolillo <contact>
Classes: 3 packages by
Country: Venezuela Venezuela
Innovation award
Innovation award
Nominee: 2x

 

Details

Let's start with a very simple example:

	<?php
	##-----------------------------------------------------------------------------------
	## MARIADB Programmatic and Compound Statements
	##-----------------------------------------------------------------------------------
	
	##-- REQUIRED TO EXECUTE PARSE MariaDB (from v10.1.1) Programmatic and Compound Statements
	define("RelativePath", ".."); #-- RELATIVE TO ROOT OF CUURENT FILE
	require_once(RelativePath."/db_mariadbparser.php");
	##-----------------------------------------------------------------------------------
	
	#-----------------------------------------------------------------
	# Instance creation execute the precompiler
	#-----------------------------------------------------------------
	$parser = new clsDBParser("test");
	#-----------------------------------------------------------------
	
	## ---------------------------------------------------------------
	## CASE1 : SQL Programmatic and Compound Statements  EMBEDDED
	## a very simple example
	## ---------------------------------------------------------------
	
	/*<MARIADB ANONYMOUS CASE1>
	  SET :maria_db_version = @@version;
	  show variables   like 'auto%';
	  SHOW FULL PROCESSLIST;
	<END>*/
	
	$resultDataSet = $parser->doCode('CASE1');
	# Check for Error
	if ($___SQLCODE === 0 ) {
	  print 'DATABASE VERSION=' . $maria_db_version . PHP_EOL;
	
	  # Use $resultDataSet[0] because using SQL Procedure can get multiple DataSet Results
	  print('<pre>');
	  print_r((isset($resultDataSet[0]) ? $resultDataSet[0] : "no result DataSet"));
	  print('</pre>');
	} else {
	  print('<pre>'
		.'Error on CASE1 (USING GLOBALS ERROR VARIABLES): '
		.$___SQLCODE.' - '.$___SQLERRM.PHP_EOL
		.'</pre>');
	  # Which Statement
	  $parser->printForDebug('CASE1');
	}
	?>

What is this?: >>>

/*<MARIADB ANONYMOUS CASE1>
SET :maria_db_version = @@version;
show variables  like 'auto%';
SHOW FULL PROCESSLIST;
<END>*/

It is an embedded code inside the PHP Script and is written in MariaDB sentences. This code was interpreted when we created the instance of the class "clsDBParser" in the php instruction:

$parser = new clsDBParser("test");

The interpreted code is ready for use. In order to execute this specific code we use the "doCode" method of the class "clsDBParser"

$resultDataSet = $parser->doCode('CASE1');

Executing the PHP Script

When we execute the PHP script example above, we will get these the results shown here for each "print":

print 'DATABASE VERSION=' . $maria_db_version . PHP_EOL;
OUTPUT: DATABASE VERSION=10.1.29-MariaDB

The $maria_db_version PHP variable took the value assigned to :maria_db_version inside the MariaDB code:

SET :maria_db_version = @@version;

: maria_db_version is what we call a Bind Variable. These variables establish a direct relationship between an anfritrion language variable (that is PHP) with an embedded language variable (that is SQL), the correspondence is made by "variable name".

Then, in the PHP Script, the execution continues:

# Use $resultDataSet[0] because using SQL Procedure can get multiple DataSet Results
print('<pre>');
print_r((isset($resultDataSet[0]) ? $resultDataSet[0] : "no result DataSet"));
print('</pre>');
OUTPUT: 
Array
(
	[0] => Array
		(
			[Variable_name] => auto_increment_increment
			[Value] => 2
		)

	[1] => Array
		(
			[Variable_name] => auto_increment_offset
			[Value] => 2
		)

	[2] => Array
		(
			[Variable_name] => autocommit
			[Value] => OFF
		)

	[3] => Array
		(
			[Variable_name] => automatic_sp_privileges
			[Value] => ON
		)

	[4] => Array
		(
			[Id] => 2
			[User] => system
			[Host] => localhost:60694
			[db] => 
			[Command] => Sleep
			[Time] => 1
			[State] => 
			[Info] => 
			[Progress] => 0.000
		)

	[5] => Array
		(
			[Id] => 58
			[User] => system
			[Host] => localhost:56370
			[db] => test
			[Command] => Query
			[Time] => 0
			[State] => Unlocking tables
			[Info] => SHOW FULL PROCESSLIST
			[Progress] => 0.000
		)
)

The "doCode" method returns a multi-dimensional array (3 dimensions) which in PHP mysqli are called "Results". The first dimension of the array has an index for each "Result" each of them we will call a DataSet, the second dimension of the array are rows of that DataSet and the third dimension are columns of those rows. Normally we will always get 1 Unique DataSet. The output shown is the content of the PHP $resultDataSet[0] variable. When a MariaDB statement generates an output of one or more rows, they will create a DataSet. For example, if you run "select * from table;" The output of the select can be retrieved as a DataSet.

The first 4 rows of the $resultDataSet[0] array (indexed by 0..3) contain the values resulting from the MariaDB statement:

show variables  like 'auto%';

The following rows (indexed by 4 and 5) are the result of the MariaDB statement:

SHOW FULL PROCESSLIST;

Error Handling

During the execution of the embedded code, errors may occur, but these errors will not interrupt the execution of the host language (PHP). SQL errors, whether runtime or syntax errors, are captured and handled internally by the precompiler. In order for the host language to handle embedded language errors, error variables are used for the host language to handle.

In our example, the output will be shown if there was no error, otherwise we show the error, let's see.

	if ($___SQLCODE === 0 ) {
	  print 'DATABASE VERSION=' . $maria_db_version . PHP_EOL;

	  # Use $resultDataSet[0] because using SQL Procedure can get multiple DataSet Results
	  print('<pre>');
	  print_r((isset($resultDataSet[0]) ? $resultDataSet[0] : "no result DataSet"));
	  print('</pre>');
	} else {
	  print('<pre>'
		.'Error on CASE1 (USING GLOBALS ERROR VARIABLES): '
		.$___SQLCODE.' - '.$___SQLERRM.PHP_EOL
		.'</pre>');
	  # Which Statement
	  $parser->printForDebug('CASE1');
	}

The "doCode" method handles 3 variable inside PHP's global context these are:

|Variable| Description| |--|--| |\$___SQLCODE|Contains the error code of the last SQL statement. If there was no error, the code is 0 (zero). | |\$___SQLERRM|Contains the error message of the last SQL statement. If there was no error the content is empty.| |\$___LASTSQL|It contains the last SQL statement executed.|

What if an Error occurs

We will change the code to generate an error. We will put an invalid SQL statement. For example:

	/*<MARIADB ANONYMOUS CASE1>
	  SET :maria_db_version = @@version;
	  show variables   like 'auto%';
	  SHOW FULL PROCESSLIST;
	  @any_variable = 'any value';
	<END>*/

After execute the PHP scritpt, there will be an SQL syntax error, then this PHP code will be executed:

	else {
	  print('<pre>'
		.'Error on CASE1 (USING GLOBALS ERROR VARIABLES): '
		.$___SQLCODE.' - '.$___SQLERRM.PHP_EOL
		.'</pre>');
	  # Which Statement
	  $parser->printForDebug('CASE1');
	}

After execute the PHP scritpt we will get this OUTPUT:

	OUTPUT:
	Error on CASE1 (USING GLOBALS ERROR VARIABLES): 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@any_variable = 'any value';
	-- -------------------------------------
	-- end emb' at line 15
	
	   1 BEGIN NOT ATOMIC
	   2    -- - Start Bind Variables 
	   3    	SET @MARIA_DB_VERSION = NULL;
	   4  
	   5    -- - End Bind Variables 
	   6    SET @___autocommit = @@autocommit;
	   7    SET @@autocommit = 0;
	   8    BEGIN 
	   9 -- -------------------------------------
	  10 -- start embedded code
	  11 -- -------------------------------------
	  12 SET @MARIA_DB_VERSION = @@version; 
	  13   show variables   like 'auto%'; 
	  14   SHOW FULL PROCESSLIST; 
	  15   @any_variable = 'any value';
	  16 -- -------------------------------------
	  17 -- end embedded code
	  18 -- -------------------------------------
	  19   
	  20 	SELECT 'OUTPUT BIND' as ___action___,@MARIA_DB_VERSION as maria_db_version ; 
	  21 END; 
	  22 COMMIT; 
	  23    SET @@autocommit = @___autocommit;
	  24 END; -- END OF BEGIN NOT ATOMIC 
  Files folder image Files  
File Role Description
Files folder imagedoc (2 files)
Plain text file db_mariadbparser.php Class Class source
Accessible without login Plain text file README.md Example Example script

  Files folder image Files  /  doc  
File Role Description
  Accessible without login Plain text file TOPIC_01.md Doc. Example script
  Accessible without login Plain text file TOPIC_02.md Doc. Documentation

 Version Control Unique User Downloads Download Rankings  
 100%
Total:38
This week:8
All time:9,676
This week:49Up