Recommend this page to a friend! |
Download |
Info | Documentation | Files | Install with Composer | Download | Reputation | Support forum | Blog | Links |
Ratings | Unique User Downloads | Download Rankings | ||||
Not yet rated by the users | Total: 76 | All time: 10,170 This week: 455 |
Version | License | PHP version | Categories | |||
mysql-mariadb-embedd 1.0.0 | GNU General Publi... | 5 | PHP 5, Databases, Parsers |
<?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');
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;
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.|
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 (4) |
File | Role | Description | ||
---|---|---|---|---|
doc (2 files) | ||||
db_mariadbparser.php | Class | Class source | ||
README.md | Example | Example script |
Files (4) | / | doc |
File | Role | Description |
---|---|---|
TOPIC_01.md | Doc. | Example script |
TOPIC_02.md | Doc. | Documentation |
The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page. |
Install with Composer |
mysql-mariadb-embedd-2019-10-22.zip 17KB | |
mysql-mariadb-embedd-2019-10-22.tar.gz 16KB | |
Install with Composer |
Version Control | Unique User Downloads | Download Rankings | |||||||||||||||
100% |
|
|
Applications that use this package |
If you know an application of this package, send a message to the author to add a link here.