Quicknavi |
|
Class reference table - MySQLHandler
The MySQL-Handler serves abstraction of a MySQL database against the application.
It takes care of the database specific issues like configuration, connecting and standardises
execution of statements. Besides, two methods are available for the latter:
On the one hand executeTextStatement() can execute any SQL statement. If the developer
would like to use the statements in his application several times and at different places, statements
can also be stored in external statement files and be executed by the executeStatement()
method. The parameter description of these two methods can be found in the
API documentation.
1. General usage
To be able to use the MySQLHandler it must be imported into the currend application
using
import('core::database','MySQLHandler');
Further the code fragment
$SQL = &$this->__getServiceObject('core::database','MySQLHandler');
creates the service layer and gives back a reference on it. Due to the fact, that this code snippet
makes use of the private method __getServiceObject() the developer can be save, that the
service is created just once within the application. This has not only an administrative advantages,
but in the case of database access, good performance impact. Inside, for example, database
connections must be produced only once and configurations must be read only once.
To provide configuration to the component, the configuration file
/apps/config/core/database/{CONTEXT}/{ENVIRONMENT}_connections.ini
must be present. The place holder {CONTEXT} indicates the current application's
context and {ENVIRONMENT} must be filled with the value of the environment variable
stored in the global registry. Details on configuration can be read about in the
configuration chapter. This file defines
which server to use, which credentials provide successful login and which database should be utilized
by the MySQLHandler. Typically the configuration file contains the following content:
[MySQL]
DB.Host = ""
DB.User = ""
DB.Pass = ""
DB.Name = ""
DB.DebugMode = "true|false"
DB.DebugMode indicates if the MySQLHandler creates a logfile with debug
information if an error occures or not. Default value is false. Set to
true the logfile will be created.
To read records from a database the following lines can be copied to an application:
$select = 'SELECT somefield, anotherfield
FROM mytable
WHERE somefield = \'somevalue\';';
$result = $SQL->executeTextStatement($select);
The records selected by the statement above can now be fetched using a while loop:
while($data = $SQL->fetchData($result)){
// ... //
// end while
}
2. Statement outsourcing
To use the statement posted above by an other application or at another place the method
executeStatement() can be used for this task. For this reason the statement must be
stored in a file named
{ENVIRONMENT}_{StatementDateiName}.sql
The MySQLHandler class expects this file to be stored in the folde
{Namespace}/{Context}/statements/
Under the acceptance that the statement "belongs" to a module under the namespace
modules::testmodule, the environment variable was not adjusted and the context of the
application is "sites::demosite" the file has to be named
DEFAULT_mystatement.sql
and stored in the folder
/apps/config/modules/testmodule/sites/demosite/statements
To run this statement the method executeStatement() must be called as follows:
$params = array(
'somefield' => 'somevalue'
);
$result = $SQL->executeStatement('modules::testmodule','mystatement',$params);
The third parameter of this method aims to configure the place holders provided within the statement.
Each place holder is marked with a leading "[", a name without blanks and a tailing "]" to end the
place holder. The name between the two brackets is the name of the place holder and must be used to
specify the value of this place holder. If one continues the example presented above the file
DEFAULT_mystatement.sql must contain the following SQL code:
SELECT somefield, anotherfield
FROM mytable
WHERE somefield = '[somefield]';
An application example is found in the
contact form tutorial under
chapter 4.3.
3. More features
The MySQLHandler provides some more methods to assist implementation. During inserts the LAST_INSERT_ID
is stored automatically. After execution the value can be gathered by calling the
getLastID() method.
Moreover methods like getNumRows() and getAffectedRows() provide
access to the amount of rows that are affected during selects or updates of MySQL tables.
fetchData() was implemented to fetch the selected data from the database.
To be able to backup and restor a MySQL database the methods backupDatabase()
and restoreDatabase() can be used. They are based on the command line programms
mysql and mysqldump. Hence they require a privileged user to execute
dump or restore actions.
Comments
Do you want to add a comment to the article above, or do you want to post additional hints? So please click here. Comments already posted can be found below.
There are no comments belonging to this article.
|
