Access Firebird Database from YII2
It so happened that I needed to transfer the user interface from the Delphi + Firebird bundle to WEB. In order not to stop the work of the program complex, it was decided to transfer the forms gradually, and for this it was necessary to save the work of the Firebird DBMS and connect to it from a web application. When developing a web application, I decided to use the YII2 framework. At the development stage, I do not need a full-fledged HTTP server, Denwer or XAMMP, or something else, will work fine. I chose XAMMP because in Denver, the PHP version has not been updated for a long time, and you don’t feel like working with it for updating it, we are on the path of least resistance. Initially, neither in XAMMP PHP nor in YII2 it is possible to work with the Firebird DBMS, but with certain dances with a tambourine it is quite possible to do this. So, I will assume that you have already installed: Windows 7 x64, XAMMP and YII2, Firebird 2.5.
Ways to install packages for me (for yourself, enter the appropriate corrections):
Now you can start.
To install Firebird support, you must stop the Apache server in XAMMP. To do this, open the XAMMP console and check that the Start button is opposite the Apache service ie the service is not running, otherwise click “Stop”.
Open any configuration file PHP D: \ xampp \ php \ php.ini with any editor and find the line in it:
We remove the ";" at the beginning i.e. uncomment and save. The PDO driver used by YII2 to access the DBMS is enabled. However, it just won’t work, now you need to copy the standard database access library fbclient.dll . Here you must be careful because we have installed Windows x64 and Firebird x64 server, and XAMMP is a 32-bit application. Accordingly, the fbclient.dll library must be compiled for that version of the client (in this case, XAMMP, or rather PHP as part of XAMMP), which is used to access the database. The 32-bit version of the library is located in the WOW64 directory of the Firebird installation directory i.e. take the file C: \ Program Files \ Firebird \ Firebird_2_5 \ WOW64 \ fbclient.dll and copy it to the following paths:
Some sources advise copying to D: \ xampp \ apache \ bin \ , but in my case it didn’t affect anything. Yes, and, in theory, could not affect because Apache server runs PHP and he himself does not need any additional access libraries.
I also recommend checking the availability of the PDO library for accessing Firebird, the file D: \ xampp \ php \ ext \ php_pdo_firebird.dll .
We start the Apache server (see the figure above), click "Start" in the XAMMP console. At the moment, it’s nice to make sure that the PDO driver for Firebird appeared on the system, I spent the whole day until I realized that the driver is not connected. The YII2 error handling system turned out to be a weak helper in this matter. reported another error elsewhere. The list of tables in Model Generator gii did not load, and before that there was an error installing the extension to access Firebird, so it was not obvious to associate the error with an unconnected PDO driver.
We create the test-fb.php file in the working directory of the YII2 D: \ xampp \ htdocs \ yii \ framework , in it we have the code:
This code lists the PDO drivers plugged into PHP. We launch the browser and type in the address bar URL localhost / yii / test-fb.php , in response we get a page with a list of connected drivers. Be sure to make sure that there is a firebird driver in the list. MySQL and SQLite drivers are always connected by default.
There are two extensions for connecting to Firebird in YII2.
I will describe the installation of both extensions in sequence. I'll start with the second extension of yii2-firebird since it has a stable status .
Open the file D: \ xampp \ htdocs \ yii \ composer.json and find a piece of code in it:
Add to the end, separated by a comma line, "edgardmessias / yii2-firebird": "*" , we get:
Please note that after the added line, the comma should not be because it is added at the end of the array and the presence of a comma when running Composer will give an error. Although PHP itself allows a comma at the end of the list of array elements before the closing bracket, Composer does not.
So, we pointed out to Composer that the yii2-firebird extension needs to be downloaded . We launch the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in succession: Carefully examine the output of Composer for errors, check the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ edgardmessias \ yii2-firebird \ . Extension installed.
Set up access to the database. The yii2-firebird documentation says that you need to change the configuration. If you carefully examine the configuration file D: \ xampp \ htdocs \ yii \ config \ web.php , we find the line (about the 40th line):
from which it becomes clear that you need to edit the database connection settings in the file D: \ xampp \ htdocs \ yii \ config \ db.php . We open it and bring it to the form:
I want to pay attention to some points:
As I indicated above, yii2-firebirddb has the status dev , therefore there are some minor features of installing this extension.
Now, in the file D: \ xampp \ htdocs \ yii \ composer.json, in the “require” section, add the download path of the yii2-firebirddb extension
We told Composer to download the yii2-firebirddb extension . We start the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in succession: Check for the absence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . Extension installed.
The documentation for yii2-firebirddb says that you still need to specify the path to the repository to download the extension. In my case, this, in addition to the error, did not work, but just in case, I will describe how to do it.
Install GIT for Windows first . You can download it at https://git-scm.com/download/win . Restart the command line console so that the PATH environment variable is updated. Check on the command line that the command runs in response, a prompt should be displayed on using the utility on the command line. In the file D: \ xampp \ htdocs \ yii \ composer.json after the closing bracket of the “require” section
write the path to the repository, it turns out:
Pay attention to the last 4 lines. Then we start the command line standardly, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in succession: Check for the presence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . Extension installed. Open the database connection configuration file D: \ xampp \ htdocs \ yii \ config \ db.php , make changes, get:
The easiest way to verify the correct installation of extensions, drivers, and database connection parameters is to connect. Launch the gii utility from YII2 and open the Model Generator . Link to open gii http: // localhost / yii / web /? R = gii , select the "Start" button in the Model Generator section , or use the direct link http: //localhost/yii/web/index.php? R = gii / default / view & id = model
If the Model Generator opens without errors, then everything is installed and working correctly since Model Generatorloads a list of database tables i.e. connects to Firebird. If you get a page loading error, most likely you do not have a single table in the database and the list of tables is returned empty. Create at least one table and try again. Also check the correctness of the database paths in the configuration (file D: \ xampp \ htdocs \ yii \ config \ db.php ).
Ways to install packages for me (for yourself, enter the appropriate corrections):
- Firebird 2.5 x64 version (server type does not matter) in C: \ Program Files \ Firebird \ Firebird_2_5 \
- XAMMP is installed in D: \ xampp \
- YII2 is installed in D: \ xampp \ htdocs \ yii \
- The database is located in the path D: \ WORK \ database \ TBOT.GDB
Now you can start.
Installing Firebird Support for PHP
To install Firebird support, you must stop the Apache server in XAMMP. To do this, open the XAMMP console and check that the Start button is opposite the Apache service ie the service is not running, otherwise click “Stop”.
Open any configuration file PHP D: \ xampp \ php \ php.ini with any editor and find the line in it:
;extension=php_pdo_firebird.dll
We remove the ";" at the beginning i.e. uncomment and save. The PDO driver used by YII2 to access the DBMS is enabled. However, it just won’t work, now you need to copy the standard database access library fbclient.dll . Here you must be careful because we have installed Windows x64 and Firebird x64 server, and XAMMP is a 32-bit application. Accordingly, the fbclient.dll library must be compiled for that version of the client (in this case, XAMMP, or rather PHP as part of XAMMP), which is used to access the database. The 32-bit version of the library is located in the WOW64 directory of the Firebird installation directory i.e. take the file C: \ Program Files \ Firebird \ Firebird_2_5 \ WOW64 \ fbclient.dll and copy it to the following paths:
- to the PHP working directory D: \ xampp \ php \
- in SYSTEM32 C: \ Windows \ System32 \ . In theory, copying here is not necessary, but I use the library in other projects and in order not to produce copies throughout the system, we copy here.
Some sources advise copying to D: \ xampp \ apache \ bin \ , but in my case it didn’t affect anything. Yes, and, in theory, could not affect because Apache server runs PHP and he himself does not need any additional access libraries.
I also recommend checking the availability of the PDO library for accessing Firebird, the file D: \ xampp \ php \ ext \ php_pdo_firebird.dll .
We start the Apache server (see the figure above), click "Start" in the XAMMP console. At the moment, it’s nice to make sure that the PDO driver for Firebird appeared on the system, I spent the whole day until I realized that the driver is not connected. The YII2 error handling system turned out to be a weak helper in this matter. reported another error elsewhere. The list of tables in Model Generator gii did not load, and before that there was an error installing the extension to access Firebird, so it was not obvious to associate the error with an unconnected PDO driver.
We create the test-fb.php file in the working directory of the YII2 D: \ xampp \ htdocs \ yii \ framework , in it we have the code:
";
}
?>
This code lists the PDO drivers plugged into PHP. We launch the browser and type in the address bar URL localhost / yii / test-fb.php , in response we get a page with a list of connected drivers. Be sure to make sure that there is a firebird driver in the list. MySQL and SQLite drivers are always connected by default.
firebird
mysql
sqlite
Installing YII2 Extensions to Access Firebird
There are two extensions for connecting to Firebird in YII2.
- yii2-firebirddb by Sergey Rusakov . Judging by the description is an adaptation of the extension from YII1. The disadvantage is that it is a dev assembly , not a stable one , so in the future you will have to change the “minimum-stability” in composer.json , which will lead to the loading of unstable versions of other YII2 extensions when updating through Composer .
- yii2-firebird by Edgard Lorraine Messias . Judging by the sources of this extension and the same bugs, this is yii2-firebirddb from Sergey Rusakov , transferred from the dev branch to the stable branch.
I will describe the installation of both extensions in sequence. I'll start with the second extension of yii2-firebird since it has a stable status .
Install yii2-firebird
Open the file D: \ xampp \ htdocs \ yii \ composer.json and find a piece of code in it:
...
"require": {
"php": ">=5.4.0",
"yiisoft/yii2": ">=2.0.5",
"yiisoft/yii2-bootstrap": "*",
"yiisoft/yii2-swiftmailer": "*"
...
Add to the end, separated by a comma line, "edgardmessias / yii2-firebird": "*" , we get:
...
"require": {
"php": ">=5.4.0",
"yiisoft/yii2": ">=2.0.5",
"yiisoft/yii2-bootstrap": "*",
"yiisoft/yii2-swiftmailer": "*",
"edgardmessias/yii2-firebird": "*"
...
Please note that after the added line, the comma should not be because it is added at the end of the array and the presence of a comma when running Composer will give an error. Although PHP itself allows a comma at the end of the list of array elements before the closing bracket, Composer does not.
So, we pointed out to Composer that the yii2-firebird extension needs to be downloaded . We launch the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in succession: Carefully examine the output of Composer for errors, check the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ edgardmessias \ yii2-firebird \ . Extension installed.
composer update
composer install
Set up access to the database. The yii2-firebird documentation says that you need to change the configuration. If you carefully examine the configuration file D: \ xampp \ htdocs \ yii \ config \ web.php , we find the line (about the 40th line):
'db' => require(__DIR__ . '/db.php'),
from which it becomes clear that you need to edit the database connection settings in the file D: \ xampp \ htdocs \ yii \ config \ db.php . We open it and bring it to the form:
'edgardmessias\db\firebird\Connection',
'dsn' => 'firebird:dbname=localhost:D:/WORK/database/TBOT.GDB;charset=WIN1251',
'username' => 'SYSDBA',
'password' => 'masterkey'
];
I want to pay attention to some points:
- the first line of the configuration shows the Connection class with a namespace
- in the line 'dsn' the firebird access driver is indicated, also the encoding of the database should be indicated in the line 'dsn'
- the path to the database is specified through a forward slash, while in Windows it is customary to specify paths through backslashes. In fact, there is no difference how to indicate since in D: \ xampp \ htdocs \ yii \ vendor \ edgardmessias \ yii2-firebird \ src \ PdoAdapter.php in the class constructor (line 37) there is code:
... public function __construct($dsn, $username, $password, $driver_options = []) { // Windows OS paths with backslashes should be changed $dsn = str_replace("\\", "/", $dsn); ...
those. the dsn line always replaces backslashes with lines. - When using backslashes, remember to escape characters in PHP, so backslashes should be repeated 2 times. those. dsn line should look like this:
'dsn' => 'firebird:dbname=localhost:D:\\WORK\\database\\TBOT.GDB;charset=WIN1251', - when connecting to a local database, it is not necessary to specify localhost in dsn , I just showed how to connect to an external server using TCP / IP
Install yii2-firebirddb
As I indicated above, yii2-firebirddb has the status dev , therefore there are some minor features of installing this extension.
Now, in the file D: \ xampp \ htdocs \ yii \ composer.json, in the “require” section, add the download path of the yii2-firebirddb extension
"srusakov/firebirddb": "*" . And since the status library is dev , then above the “require” section we find and change "minimum-stability": "stable",to "minimum-stability": "dev",. We get this view (pay attention to the first and last lines):...
"minimum-stability": "dev",
"require": {
"php": ">=5.4.0",
"yiisoft/yii2": ">=2.0.5",
"yiisoft/yii2-bootstrap": "*",
"yiisoft/yii2-swiftmailer": "*",
"edgardmessias/yii2-firebird": "*"
...
We told Composer to download the yii2-firebirddb extension . We start the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in succession: Check for the absence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . Extension installed.
composer update
composer install
Comment
The documentation for yii2-firebirddb says that you still need to specify the path to the repository to download the extension. In my case, this, in addition to the error, did not work, but just in case, I will describe how to do it.
Install GIT for Windows first . You can download it at https://git-scm.com/download/win . Restart the command line console so that the PATH environment variable is updated. Check on the command line that the command runs in response, a prompt should be displayed on using the utility on the command line. In the file D: \ xampp \ htdocs \ yii \ composer.json after the closing bracket of the “require” section
git
write the path to the repository, it turns out:
...
"require": {
"php": ">=5.4.0",
"yiisoft/yii2": ">=2.0.5",
"yiisoft/yii2-bootstrap": "*",
"yiisoft/yii2-swiftmailer": "*",
"edgardmessias/yii2-firebird": "*",
"srusakov/firebirddb": "*"
},
"repositories":[{
"type":"git",
"url":"http://github.com/srusakov/yii2-firebirddb"
}],
...
Pay attention to the last 4 lines. Then we start the command line standardly, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in succession: Check for the presence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . Extension installed. Open the database connection configuration file D: \ xampp \ htdocs \ yii \ config \ db.php , make changes, get:
composer update
composer install
'yii\db\Connection',
'dsn' => 'firebird:dbname=localhost:D:/WORK/database/TBOT.GDB;charset=WIN1251',
'username' => 'sysdba',
'password' => 'masterkey',
'charset' => 'utf8', //вот тут надо еще разбираться с кодировками, но это тема для другого материала
'pdoClass' => 'srusakov\firebirddb\PDO',
'schemaMap' => [
'firebird' => 'srusakov\firebirddb\Schema' // FireBird
]
];
Health Check
The easiest way to verify the correct installation of extensions, drivers, and database connection parameters is to connect. Launch the gii utility from YII2 and open the Model Generator . Link to open gii http: // localhost / yii / web /? R = gii , select the "Start" button in the Model Generator section , or use the direct link http: //localhost/yii/web/index.php? R = gii / default / view & id = model
If the Model Generator opens without errors, then everything is installed and working correctly since Model Generatorloads a list of database tables i.e. connects to Firebird. If you get a page loading error, most likely you do not have a single table in the database and the list of tables is returned empty. Create at least one table and try again. Also check the correctness of the database paths in the configuration (file D: \ xampp \ htdocs \ yii \ config \ db.php ).
Conclusion
- I want to note the difference in the approach to the work of extensions. The yii2-firebirddb extension works with the Connection class from the standard YII2 namespace, but it uses its own PDO driver to access Firebird. The yii2-firebird extension extends the standard Connection with its own.
- Both extensions do not work with BLOB fields. The description of this bug is here https://bugs.php.net/bug.php?id=61183 . Since this is not very relevant for me, I did not deal with this issue, I propose to create views in the database in which to convert the blob field to varchar . Moreover, varchar allows text size up to 32Kb. For media (pictures, views, etc.) this is not enough, but I work mainly with text data and this is quite enough. The second option is to read the bug description, so they suggest applying a patch that should fix it. If someone does this, unsubscribe in the comments, I will make changes to the article.
- For those programmers who have been using PHP and YII2 for a long time, this material may seem unnecessarily detailed. For example, often it’s enough to say “change the configuration” and that’s it. However, for those who start migrating from Delphi to PHP and YII2, such a phrase will say little. it is also necessary to find the files in which the configuration lies. The same applies to commands for updating extensions.