Connect to the CUBRID DBMS via PHP
So you, I am sure, already know how to connect and work in PHP with certain relational database systems. In this blog I will tell you how to get started in PHP with the CUBRID DBMS , how it differs from other systems, and also give examples for a quick start. The good news is that the latest version of CUBRID 8.4.0 is 90% compatible with MySQL, both in terms of SQL syntax and in terms of PHP functions.
At once I will make a reservation that we will be talking about PHP version 5.2 and higher, because this is the minimum requirement for working with CUBRID DBMS in PHP.
Installing a PHP driver for CUBRID is typical. On Windows, you can use an automatic installer, or you can manually set everything you need in the php.ini configuration file. In Linux, the instructions are almost the same: you can use the PECL installer, or you can change php.ini yourself.
1. Download the latest version of CUBRID PHP API Installer from the official site (<870KB). This installer has all versions of PHP drivers for various combinations of PHP and CUBRID.
2. Make sure that the PHP engine and the CUBRID DBMS are installed.
3. Run the installer.
4. Everything is as in a typical Windows printer:
Done! Do not forget to restart your Web server. As a result of this installation, the installer will automatically determine the version of your PHP engine, the CUBRID version of the DBMS, as well as the folder in which PHP stores its drivers by default (usually C: \ Program Files \ PHP \ ext ). Based on this, the installer will copy the required php_cubrid.dll to this folder with all the other drivers. Then he will add the following two lines to the php.ini configuration file.
')
Everything is standard, but everything happens automatically without your participation. The same method, but in English and with screenshots can be viewed here .
You can do the same thing as the CUBRID PHP API Installer yourself.
All is ready!
Here we show how to install the CUBRID PHP driver in Linux based on DEBIAN. For Linux based RPMs, see the instructions below.
Install CUBRID PHP drivers in Linux based on RPM.
Done! Do not forget to restart the Web server.
Install the CUBRID PHP driver without phpize and PEAR - manually. See instructions below.
Done! If you have problems installing the driver, write in the comments.
As you must know, connecting and working with Oracle databases and PostgreSQL is similar in some way. Working with them, you directly connect to a specific database, rather than to a host or server as a whole, as you usually do in MySQL and MSSQL. In the case of MySQL and MSSQL, you create a connection with the host, then the second step is to select the database to which you need to connect.
Working with the CUBRID DBMS is in this sense similar to Oracle and PostgreSQL. You directly connect to the database you want to work with. Therefore, in CUBRID PHP, as in Oracle and PostgreSQL, there are no functions that allow you to create, delete, or select databases, with the exception of those functions that provide information about the working database. This is where they differ from the MySQL and MSSQL libraries.
By default, the auto_commit parameter is disabled when connecting, i.e. in all of the following transactions, you must confirm the transaction yourself. For example:
Such an implementation is very convenient if you want to programmatically control further actions in case of errors in transactions (that is, to issue some specific messages, etc.). If you prefer to fix all transactions, you can turn on auto_commit either immediately upon connection or during operation. In the first case, connect via cubrid_connect_with_url .
Thus, all requests will be recorded automatically. In the second case, call the cubrid_set_autocommit function at the required moment, for example:
Similarly, you can disable auto_commit . To find out the status of auto_commit while running, you can call cubrid_get_autocommit .
All other queries in CUBRID are very similar to MySQL syntax.
To enter a new or update the old value, you can use the same functions as in MySQL, but with the prefix "cubrid_".
To get the ID generated by the last INSERT query, given that there is a column with an AUTO_INCREMENT constraint in the table, you can call the cubrid_insert_id function. This function differs from mysql_insert_id only in that in CUBRID the function returns a string, while MySQL - the number int. The convenience of the string is that this function can return a BIGINT type ID, while the MySQL function cannot be used for large numbers. If there are no AUTO_INCREMENT columns in the table, cubrid_insert_id will return 0.
After entering new values ​​or updating existing ones, as in MySQL, you can call cubrid_affected_rows to find out how many rows have been changed as a result, specified as a parameter. If the result resource is not specified, the result of the last query will be implied.
A complete list of all CUBRID PHP functions associated with the corresponding MySQL PHP functions can be found here , where you will notice that almost all functions are identical with the replacement of the mysql_ prefix with cubrid_ .
Permanent connections are not implemented in the CUBRID PHP library for the sole reason that CUBRID itself supports persistent connections. Those. In CUBRID Broker configurations, you can set the value of the CCI_PCONNECT parameter [link to manual] as ON and restart the broker, after which all connections between the client application and the CUBRID database will be permanent even if you close them using cubrid_close .
A pool of persistent connections can contain a maximum of 256 connections. All other connections will open as normal until one of the permanent connections is released. You can also set KEEP_CONNECTION .
In the CUBRID PHP library, you can also set prepared statements. They can include both “?” And named placeholders. For example:
Application? as a placeholder
Using a named placeholder
It's pretty simple. In CUBRID Broker configurations, there is one more parameter STATEMENT_POOLING , which is responsible for the pooling of prepared requests. If you enable this option (ON, OFF, the default ON ), then all prepared requests will be saved in a special pool, which allows them to be reused. If disabled, the prepared requests will be closed immediately after the completion of transactions.
So, as you noticed, almost everything that works with MySQL will work with CUBRID. And if something does not work out of your box, write in the comments. We will understand. And maybe even thanks to you, there will be something new in the CUBRID PHP API.
At once I will make a reservation that we will be talking about PHP version 5.2 and higher, because this is the minimum requirement for working with CUBRID DBMS in PHP.
Installation
Installing a PHP driver for CUBRID is typical. On Windows, you can use an automatic installer, or you can manually set everything you need in the php.ini configuration file. In Linux, the instructions are almost the same: you can use the PECL installer, or you can change php.ini yourself.
Installing PHP Drivers using Windows Installer
1. Download the latest version of CUBRID PHP API Installer from the official site (<870KB). This installer has all versions of PHP drivers for various combinations of PHP and CUBRID.
2. Make sure that the PHP engine and the CUBRID DBMS are installed.
3. Run the installer.
4. Everything is as in a typical Windows printer:
- specify the path to install;
- confirm the folder name for the Start main menu;
- Click on " Install ".
Done! Do not forget to restart your Web server. As a result of this installation, the installer will automatically determine the version of your PHP engine, the CUBRID version of the DBMS, as well as the folder in which PHP stores its drivers by default (usually C: \ Program Files \ PHP \ ext ). Based on this, the installer will copy the required php_cubrid.dll to this folder with all the other drivers. Then he will add the following two lines to the php.ini configuration file.
')
[PHP_CUBRID]
extension=php_cubrid.dll
Everything is standard, but everything happens automatically without your participation. The same method, but in English and with screenshots can be viewed here .
Manual configuration of php.ini in Windows
You can do the same thing as the CUBRID PHP API Installer yourself.
- Download the driver you need from the official site (<40KB). Both thread-safe and non-thread-safe versions for Apache and IIS are available.
- Unzip the archive.
- Copy php_cubrid.dll to the folder where your other PHP drivers are located.
- Open the php.ini configuration file and paste the above two lines at the end of the file.
- Save the changes and restart your Web server.
All is ready!
Installing PHP Drivers in Linux with the PECL Installer
Here we show how to install the CUBRID PHP driver in Linux based on DEBIAN. For Linux based RPMs, see the instructions below.
- Install phpize
sudo apt-get install php5-dev - Install the PEAR package that provides the PECL commands
sudo apt-get install php-pear - Now install the CUBRID PHP driver itself using the PECL command.
sudo pecl install cubrid
This will install the latest cubrid.so driver. If you need one of the previous versions, then specify the version when installing as follows:sudo pecl install cubrid-8.3.0.0005
If the query “install install dir [autodetect]:” appears at this point, enter the full path of the directory where CUBRID is installed. For example, if CUBRID is set to / home / cubridtest / CUBRID , enter / home / cubridtest / CUBRID . - At the end you need to change the configuration file php.ini, adding the following two lines at the end of the file.
[CUBRID]
extension=cubrid.so
Install CUBRID PHP drivers in Linux based on RPM.
- Install phpize
yum install php-devel - Download the package PEAR
wget pear.php.net/go-pear.phar - Run PEAR package in PHP
php go-pear.phar - Now install the CUBRID PHP driver itself using the PECL command.
pecl install cubrid
This will install the latest cubrid.so driver. If you need one of the previous versions, then specify the version when installing as follows:pecl install cubrid-8.3.0.0005
If the query “install install dir [autodetect]:” appears at this point, enter the full path of the directory where CUBRID is installed. For example, if CUBRID is set to / home / cubridtest / CUBRID , enter / home / cubridtest / CUBRID . - At the end you need to change the configuration file php.ini, adding the following two lines at the end of the file.
[CUBRID]
extension=cubrid.so
Done! Do not forget to restart the Web server.
Manual configuration of php.ini in Linux
Install the CUBRID PHP driver without phpize and PEAR - manually. See instructions below.
- Download the driver you need from the official site (<80KB). Versions are available for both x64 and x86.
- Unzip the archive.
- Copy cubrid.so to the directory where your other PHP drivers are located (usually / usr / lib / php5 / 20090626 for PHP 5.3.3, where 20090626 is the name of the directory, which may vary depending on the PHP version).
- Open the php.ini configuration file (usually found in /etc/php5/apache2/php.ini ) and paste the following two lines at the end of the file.
[CUBRID]
extension=cubrid.so - Save the changes and restart your Web server.
Done! If you have problems installing the driver, write in the comments.
Using the CUBRID PHP Driver
As you must know, connecting and working with Oracle databases and PostgreSQL is similar in some way. Working with them, you directly connect to a specific database, rather than to a host or server as a whole, as you usually do in MySQL and MSSQL. In the case of MySQL and MSSQL, you create a connection with the host, then the second step is to select the database to which you need to connect.
Working with the CUBRID DBMS is in this sense similar to Oracle and PostgreSQL. You directly connect to the database you want to work with. Therefore, in CUBRID PHP, as in Oracle and PostgreSQL, there are no functions that allow you to create, delete, or select databases, with the exception of those functions that provide information about the working database. This is where they differ from the MySQL and MSSQL libraries.
Syntax
resource cubrid_connect(string $host, int $port, string $dbname [, string $userid [, string $passwd ]] )Example
$host_ip = "localhost";
$host_port = 33000;
$db_name = "demodb";
$conn = cubrid_connect($host_ip, $host_port, $db_name)
or die("Could not connect: " . cubrid_error());
print ("Connected successfully");
cubrid_close($conn);
By default, the auto_commit parameter is disabled when connecting, i.e. in all of the following transactions, you must confirm the transaction yourself. For example:
cubrid_commit($conn);
Such an implementation is very convenient if you want to programmatically control further actions in case of errors in transactions (that is, to issue some specific messages, etc.). If you prefer to fix all transactions, you can turn on auto_commit either immediately upon connection or during operation. In the first case, connect via cubrid_connect_with_url .
$conn_url = "cci:CUBRID:127.0.0.1:33088:demodb:dba:123456:?autocommit=off"
$conn = cubrid_connect_with_url($conn_url);
Thus, all requests will be recorded automatically. In the second case, call the cubrid_set_autocommit function at the required moment, for example:
if (cubrid_set_autocommit($conn, true)){
echo " ";
}
Similarly, you can disable auto_commit . To find out the status of auto_commit while running, you can call cubrid_get_autocommit .
All other queries in CUBRID are very similar to MySQL syntax.
// The syntax is the same.
$ sql = "select sports counting players
// Replace with just the prefix "mysql_" with "cubrid_"
$ result = cubrid_query ($ conn, $ sql);
if ($ result) {
// number of columns in the query
$ num_fields = cubrid_num_fields ($ result);
echo ("<tr>");
// column names in MySQL syntax request
for ($ i = 0; $ i <$ num_fields; ++ $ i) {
echo ("<td align = center>");
echo (cubrid_field_name ($ result, $ i));
echo ("</ td>");
}
echo ("</ tr>");
// the same can be done in the CUBRID style, but only with one request
$ columns = cubrid_column_names ($ result);
echo ("<tr>");
while (list ($ key, $ colname) = each ($ columns)) {
echo ("<td align = center> $ colname </ td>");
}
echo ("</ tr>");
// Get the numerical and associative array
while ($ row = cubrid_fetch_array ($ result)) {
// you can also use cubrid_fetch_assoc, cubrid_fetch_field,
// cubrid_fetch_lengths, cubrid_fetch_row and cubrid_fetch_object
// Just like in MySQL
echo ("<tr>");
for ($ i = 0; $ i <$ num_fields; ++ $ i) {
echo ("<td align = center>");
echo ($ row [$ i]);
echo ("</ td>");
}
echo ("</ tr>");
}
}
cubrid_close ($ conn);
To enter a new or update the old value, you can use the same functions as in MySQL, but with the prefix "cubrid_".
$ sql = "insert into olympic (host_year, host_nation, host_city,"
. "opening_date, closing_date) values ​​(2008, 'China', 'Beijing',"
. "to_date ('08-08-2008 ',' mm-dd-yyyy '), to_date ('08-24-2008', 'mm-dd-yyyy'));"
$ result = cubrid_query ($ cubrid_con, $ sql);
if ($ result) {
// If auto_commit is not enabled by default, then confirm the transaction
if (! cubrid_get_autocommit ($ cubrid_con)) {
cubrid_commit ($ cubrid_con);
}
echo ("Inserted successfully");
}
else {
echo (cubrid_error ());
cubrid_rollback ($ cubrid_con);
}
cubrid_disconnect ($ cubrid_con);
To get the ID generated by the last INSERT query, given that there is a column with an AUTO_INCREMENT constraint in the table, you can call the cubrid_insert_id function. This function differs from mysql_insert_id only in that in CUBRID the function returns a string, while MySQL - the number int. The convenience of the string is that this function can return a BIGINT type ID, while the MySQL function cannot be used for large numbers. If there are no AUTO_INCREMENT columns in the table, cubrid_insert_id will return 0.
After entering new values ​​or updating existing ones, as in MySQL, you can call cubrid_affected_rows to find out how many rows have been changed as a result, specified as a parameter. If the result resource is not specified, the result of the last query will be implied.
$affected_num = cubrid_affected_rows();
echo " : " . $affected_num;
A complete list of all CUBRID PHP functions associated with the corresponding MySQL PHP functions can be found here , where you will notice that almost all functions are identical with the replacement of the mysql_ prefix with cubrid_ .
Permanent connections
Permanent connections are not implemented in the CUBRID PHP library for the sole reason that CUBRID itself supports persistent connections. Those. In CUBRID Broker configurations, you can set the value of the CCI_PCONNECT parameter [link to manual] as ON and restart the broker, after which all connections between the client application and the CUBRID database will be permanent even if you close them using cubrid_close .
A pool of persistent connections can contain a maximum of 256 connections. All other connections will open as normal until one of the permanent connections is released. You can also set KEEP_CONNECTION .
[%BROKER1]
SERVICE = ON
BROKER_PORT = 33000
...
KEEP_CONNECTION = ON
CCI_PCONNECT = ON
STATEMENT_POOLING = ON
Prepared requests
In the CUBRID PHP library, you can also set prepared statements. They can include both “?” And named placeholders. For example:
Application? as a placeholder
// ?
$sql = "SELECT * FROM game WHERE host_year = ?;
$req = cubrid_prepare($conn, $sql);
// , 1
cubrid_bind($req, 1, 2004);
cubrid_execute($req);
Using a named placeholder
//
$sql2 = "SELECT * FROM game WHERE host_year = :host_year";
$req = cubrid_prepare($conn, $sql);
//
cubrid_bind($req, ':host_year', 2004);
cubrid_execute($req);
It's pretty simple. In CUBRID Broker configurations, there is one more parameter STATEMENT_POOLING , which is responsible for the pooling of prepared requests. If you enable this option (ON, OFF, the default ON ), then all prepared requests will be saved in a special pool, which allows them to be reused. If disabled, the prepared requests will be closed immediately after the completion of transactions.
So, as you noticed, almost everything that works with MySQL will work with CUBRID. And if something does not work out of your box, write in the comments. We will understand. And maybe even thanks to you, there will be something new in the CUBRID PHP API.
Source: https://habr.com/ru/post/119514/
All Articles