HLI: Database

Database

Currently only MySQL, MySQLi and PDO are supported, but it would be easy to add more.

IMPORTANT: Prepared statements are executed by this plugin! Note that result sets are exhausted after dumping them.

PDO can display the SQL statement as well, but this information cannot be retrieved from the older ways of querying DB's.

visit HLI#X

1. pre($result) type: PDOStatement (object)

in builder\plugins\dbDump.php on line 24

*** SQL statement ***
SELECT * FROM help_topic LIMIT 0,5
*** Row ****** help_topic_id ****** name ****** help_category_id ****** description ****** example ****** url ***
*** 0 ***0JOIN6MySQL supports the following JOIN syntaxes for the *** length: 849 ***MySQL supports the following JOIN syntaxes for the
table_references part of SELECT statements and multiple-table
DELETE and UPDATE statements:

table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference
    ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference is defined as:

tbl_name [[AS] alias]
    [[USE INDEX (key_list)]
      | [IGNORE INDEX (key_list)]
      | [FORCE INDEX (key_list)]]

join_condition is defined as:

ON conditional_expr | USING (column_list)
mysql> SELECT table1.* FROM table1
    ->        L *** length: 127 ***mysql> SELECT table1.* FROM table1
    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL; 
*** 1 ***1HEX23   HEX(N_or_S)

If N_OR_S is a number, returns a s *** length: 337 ***   HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal
value of N, where N is a longlong (BIGINT) number.
This is equivalent to CONV(N,10,16).

From MySQL 4.0.1 and up,
if N_OR_S is a string, returns a hexadecimal string of N_OR_S
where each character in N_OR_S is converted to two hexadecimal digits.
mysql> SELECT HEX(255);
        -> 'FF'
mysql> SEL *** length: 124 ***mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT 0x616263;
        -> 'abc'
mysql> SELECT HEX('abc');
        -> 616263 
*** 2 ***2REPLACE23   REPLACE(str,from_str,to_str)
Returns the string *** length: 130 ***   REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string
from_str replaced by the string to_str.
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com' 
*** 3 ***3REPEAT23   REPEAT(str,count)
Returns a string consisting o *** length: 168 ***   REPEAT(str,count)
Returns a string consisting of the string str repeated count
times. If count <= 0, returns an empty string. Returns NULL if
str or count are NULL.
mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL' 
*** 4 ***4CONTAINS11   Contains(g1,g2)
Returns 1 or 0 to indicate whether or not g1 completely contains
g2.
  

* Row *	* help_topic_id *	* name *	* help_category_id *	* description *	* example *
* 0 *	0	JOIN	6	MySQL supports the following JOIN syntaxes for the * length: 849 *	mysql> SELECT table1.* FROM table1 -> L * length: 127 *
* 1 *	1	HEX	23	HEX(N_or_S) If N_OR_S is a number, returns a s * length: 337 *	mysql> SELECT HEX(255); -> 'FF' mysql> SEL * length: 124 *
* 2 *	2	REPLACE	23	REPLACE(str,from_str,to_str) Returns the string * length: 130 *	mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww'); -> 'WwWwWw.mysql.com'
* 3 *	3	REPEAT	23	REPEAT(str,count) Returns a string consisting o * length: 168 *	mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL'
* 4 *	4	CONTAINS	11	Contains(g1,g2) Returns 1 or 0 to indicate whether or not g1 completely contains g2.

*** global scope ***

Note that simply passing the result object/resource will not display error messages comming from DB as the result set will be false if the statement could not be executed (this is not true for PDO prepared statements). But you can pass the connection object/resource to get information about that, including any error message.

visit HLI#X

2. pre($result) type: boolean

in builder\plugins\dbDump.php on line 35

false

*** global scope ***

visit HLI#X

3. pre($pdo) type: PDO (object)

*** error info ***
Table 'mysql.somewhere' doesn't exist, 1146 (driver), 42S02 (SQLSTATE)
*** last insert ID ***
'0'
*** autocommit ***
1
*** case ***
0
*** client version ***
'5.0.27'
*** connection status ***
'localhost via TCP/IP'
*** driver name ***
'mysql'
*** errmode ***
0
*** oracle nulls ***
0
*** persistent ***
false
*** prefetch ***
false
*** server stat ***
'Uptime: 68402  Threads: 4  Questions: 95  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 1  Queries per second avg: 0.001'
*** server version ***
'5.0.27-community-nt'
*** timeout ***
false

It's easier with prepared statements:

visit HLI#X

4. pre($stmt) type: PDOStatement (object)

in builder\plugins\dbDump.php on line 42

*** SQL statement ***
SELECT something FROM somewhere
*** SQL error ***
Table 'mysql.somewhere' doesn't exist, 1146 (driver), 42S02 (SQLSTATE)

*** global scope ***

The same querys done with mysql-extension and a persistent connection:

visit HLI#X

5. pre($result) type: mysql result (resource)

in builder\plugins\dbDump.php on line 52

*** Row ****** help_topic_id ****** name ****** help_category_id ****** description ****** example ****** url ***
*** 0 ***0JOIN6MySQL supports the following JOIN syntaxes for the *** length: 849 ***MySQL supports the following JOIN syntaxes for the
table_references part of SELECT statements and multiple-table
DELETE and UPDATE statements:

table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference
    ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference is defined as:

tbl_name [[AS] alias]
    [[USE INDEX (key_list)]
      | [IGNORE INDEX (key_list)]
      | [FORCE INDEX (key_list)]]

join_condition is defined as:

ON conditional_expr | USING (column_list)
mysql> SELECT table1.* FROM table1
    ->        L *** length: 127 ***mysql> SELECT table1.* FROM table1
    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL; 
*** 1 ***1HEX23   HEX(N_or_S)

If N_OR_S is a number, returns a s *** length: 337 ***   HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal
value of N, where N is a longlong (BIGINT) number.
This is equivalent to CONV(N,10,16).

From MySQL 4.0.1 and up,
if N_OR_S is a string, returns a hexadecimal string of N_OR_S
where each character in N_OR_S is converted to two hexadecimal digits.
mysql> SELECT HEX(255);
        -> 'FF'
mysql> SEL *** length: 124 ***mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT 0x616263;
        -> 'abc'
mysql> SELECT HEX('abc');
        -> 616263 
*** 2 ***2REPLACE23   REPLACE(str,from_str,to_str)
Returns the string *** length: 130 ***   REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string
from_str replaced by the string to_str.
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com' 
*** 3 ***3REPEAT23   REPEAT(str,count)
Returns a string consisting o *** length: 168 ***   REPEAT(str,count)
Returns a string consisting of the string str repeated count
times. If count <= 0, returns an empty string. Returns NULL if
str or count are NULL.
mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL' 
*** 4 ***4CONTAINS11   Contains(g1,g2)
Returns 1 or 0 to indicate whether or not g1 completely contains
g2.
  

* Row *	* help_topic_id *	* name *	* help_category_id *	* description *	* example *
* 0 *	0	JOIN	6	MySQL supports the following JOIN syntaxes for the * length: 849 *	mysql> SELECT table1.* FROM table1 -> L * length: 127 *
* 1 *	1	HEX	23	HEX(N_or_S) If N_OR_S is a number, returns a s * length: 337 *	mysql> SELECT HEX(255); -> 'FF' mysql> SEL * length: 124 *
* 2 *	2	REPLACE	23	REPLACE(str,from_str,to_str) Returns the string * length: 130 *	mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww'); -> 'WwWwWw.mysql.com'
* 3 *	3	REPEAT	23	REPEAT(str,count) Returns a string consisting o * length: 168 *	mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL'
* 4 *	4	CONTAINS	11	Contains(g1,g2) Returns 1 or 0 to indicate whether or not g1 completely contains g2.

*** global scope ***

visit HLI#X

6. pre($result) type: boolean

in builder\plugins\dbDump.php on line 55

false

*** global scope ***

visit HLI#X

7. pre($mysql) type: mysql link persistent (resource)

*** statement info ***
false
*** error info ***
Table 'mysql.somewhere' doesn't exist, 1146 (driver)
*** last insert ID ***
0
*** client version ***
'5.0.27'
*** client encoding ***
'latin1'
*** host ***
'localhost via TCP/IP'
*** protocol ***
10
*** persistent ***
true
*** server stat ***
'Uptime: 68402  Threads: 4  Questions: 99  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 1  Queries per second avg: 0.001'
*** server version ***
'5.0.27-community-nt'

The same querys done with mysqli-extension:

visit HLI#X

8. pre($result) type: mysqli_result (object)

in builder\plugins\dbDump.php on line 64

*** Row ****** help_topic_id ****** name ****** help_category_id ****** description ****** example ****** url ***
*** 0 ***0JOIN6MySQL supports the following JOIN syntaxes for the *** length: 849 ***MySQL supports the following JOIN syntaxes for the
table_references part of SELECT statements and multiple-table
DELETE and UPDATE statements:

table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference
    ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference is defined as:

tbl_name [[AS] alias]
    [[USE INDEX (key_list)]
      | [IGNORE INDEX (key_list)]
      | [FORCE INDEX (key_list)]]

join_condition is defined as:

ON conditional_expr | USING (column_list)
mysql> SELECT table1.* FROM table1
    ->        L *** length: 127 ***mysql> SELECT table1.* FROM table1
    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL; 
*** 1 ***1HEX23   HEX(N_or_S)

If N_OR_S is a number, returns a s *** length: 337 ***   HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal
value of N, where N is a longlong (BIGINT) number.
This is equivalent to CONV(N,10,16).

From MySQL 4.0.1 and up,
if N_OR_S is a string, returns a hexadecimal string of N_OR_S
where each character in N_OR_S is converted to two hexadecimal digits.
mysql> SELECT HEX(255);
        -> 'FF'
mysql> SEL *** length: 124 ***mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT 0x616263;
        -> 'abc'
mysql> SELECT HEX('abc');
        -> 616263 
*** 2 ***2REPLACE23   REPLACE(str,from_str,to_str)
Returns the string *** length: 130 ***   REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string
from_str replaced by the string to_str.
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com' 
*** 3 ***3REPEAT23   REPEAT(str,count)
Returns a string consisting o *** length: 168 ***   REPEAT(str,count)
Returns a string consisting of the string str repeated count
times. If count <= 0, returns an empty string. Returns NULL if
str or count are NULL.
mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL' 
*** 4 ***4CONTAINS11   Contains(g1,g2)
Returns 1 or 0 to indicate whether or not g1 completely contains
g2.
  

* Row *	* help_topic_id *	* name *	* help_category_id *	* description *	* example *
* 0 *	0	JOIN	6	MySQL supports the following JOIN syntaxes for the * length: 849 *	mysql> SELECT table1.* FROM table1 -> L * length: 127 *
* 1 *	1	HEX	23	HEX(N_or_S) If N_OR_S is a number, returns a s * length: 337 *	mysql> SELECT HEX(255); -> 'FF' mysql> SEL * length: 124 *
* 2 *	2	REPLACE	23	REPLACE(str,from_str,to_str) Returns the string * length: 130 *	mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww'); -> 'WwWwWw.mysql.com'
* 3 *	3	REPEAT	23	REPEAT(str,count) Returns a string consisting o * length: 168 *	mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL'
* 4 *	4	CONTAINS	11	Contains(g1,g2) Returns 1 or 0 to indicate whether or not g1 completely contains g2.

*** global scope ***

visit HLI#X

9. pre($result) type: boolean

in builder\plugins\dbDump.php on line 67

false

*** global scope ***

visit HLI#X

10. pre($mysqli) type: mysqli (object)

*** statement info ***
NULL
*** error info ***
Table 'mysql.somewhere' doesn't exist, 1146 (driver), 42S02 (SQLSTATE)
*** last insert ID ***
0
*** client version ***
'5.0.27'
*** client encoding ***
'latin1'
*** host ***
'localhost via TCP/IP'
*** protocol ***
10
*** server stat ***
'Uptime: 68403  Threads: 5  Questions: 101  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 1  Queries per second avg: 0.001'
*** server version ***
'5.0.27-community-nt'

As prepared statements in mysqli work differently from PDO, it is not possible to have the nice error messages here. Also, I haven't found a way to display the column names. Any idea would be appreciated.

visit HLI#X

11. pre($stmt) type: mysqli_stmt (object)

in builder\plugins\dbDump.php on line 78

*** Row ****** 0 ****** 1 ****** 2 ****** 3 ****** 4 ****** 5 ***
*** 0 ***1HEX23   HEX(N_or_S)

If N_OR_S is a number, returns a s *** length: 337 ***   HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal
value of N, where N is a longlong (BIGINT) number.
This is equivalent to CONV(N,10,16).

From MySQL 4.0.1 and up,
if N_OR_S is a string, returns a hexadecimal string of N_OR_S
where each character in N_OR_S is converted to two hexadecimal digits.
mysql> SELECT HEX(255);
        -> 'FF'
mysql> SEL *** length: 124 ***mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT 0x616263;
        -> 'abc'
mysql> SELECT HEX('abc');
        -> 616263 
*** 1 ***2REPLACE23   REPLACE(str,from_str,to_str)
Returns the string *** length: 130 ***   REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string
from_str replaced by the string to_str.
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com' 
*** 2 ***3REPEAT23   REPEAT(str,count)
Returns a string consisting o *** length: 168 ***   REPEAT(str,count)
Returns a string consisting of the string str repeated count
times. If count <= 0, returns an empty string. Returns NULL if
str or count are NULL.
mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL' 
*** 3 ***9CONCAT23   CONCAT(str1,str2,...)
Returns the string that r *** length: 385 ***   CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments.  Returns
NULL if any argument is NULL.  May have one or more arguments.
If all arguments are non-binary strings, the result is a non-binary string.
If the arguments include any binary strings, the result is a binary string.
A numeric argument is converted to its equivalent binary string form.
mysql> SELECT CONCAT('My', 'S', 'QL');
        ->  *** length: 159 ***mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3' 
*** 4 ***11CHAR FUNCTION23   CHAR(N,...)
CHAR() interprets the arguments as  *** length: 178 ***   CHAR(N,...)
CHAR() interprets the arguments as integers and returns a string
consisting of the characters given by the code values of those
integers. NULL values are skipped.
mysql> SELECT CHAR(77,121,83,81,'76');
        ->  *** length: 110 ***mysql> SELECT CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
        -> 'MMM' 

* Row *	* 0 *	* 1 *	* 2 *	* 3 *	* 4 *
* 0 *	1	HEX	23	HEX(N_or_S) If N_OR_S is a number, returns a s * length: 337 *	mysql> SELECT HEX(255); -> 'FF' mysql> SEL * length: 124 *
* 1 *	2	REPLACE	23	REPLACE(str,from_str,to_str) Returns the string * length: 130 *	mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww'); -> 'WwWwWw.mysql.com'
* 2 *	3	REPEAT	23	REPEAT(str,count) Returns a string consisting o * length: 168 *	mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL'
* 3 *	9	CONCAT	23	CONCAT(str1,str2,...) Returns the string that r * length: 385 *	mysql> SELECT CONCAT('My', 'S', 'QL'); -> * length: 159 *
* 4 *	11	CHAR FUNCTION	23	CHAR(N,...) CHAR() interprets the arguments as * length: 178 *	mysql> SELECT CHAR(77,121,83,81,'76'); -> * length: 110 *

*** global scope ***