We are connected to the CUBRID DBMS through PHP
So you, I am sure, already know how to connect and work with certain relational database systems in PHP. In this blog, I will tell you how to get started in PHP with the CUBRID DBMS , what is its difference from other systems, as well as provide 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.
Immediately make a reservation that we will be talking about PHP version 5.2 and higher, because this is the minimum requirement for working with the CUBRID DBMS in PHP.
Installing a PHP driver for CUBRID is typical. In Windows, you can use the automatic uninstaller, or you can manually register everything you need in the php.ini configuration file . On Linux, the instructions are pretty much the same: you can use the PECL installer, or you can modify php.ini yourself.
1. Download the latest version of CUBRID PHP API Installer from the official site (<870KB). This installer has all versions of the PHP driver for all kinds of combinations of PHP and CUBRID.
2. Make sure that the PHP engine and the CUBRID database engine are installed.
3. Run the installer.
4. Everything is like in a typical Windows installer:
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 version of the CUBRID DBMS, as well as the folder in which PHP stores its drivers by default (usually it is C: \ Program Files \ PHP \ ext ). Based on this, the installer will copy the necessary php_cubrid.dll to this folder with all other drivers. He will then add the following two lines to the php.ini configuration file. Everything is standard, but everything happens automatically without your participation. You can look at the same method, but already in English and with screenshots here .
The same thing that CUBRID PHP API Installer does, you can do 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.
Installing CUBRID PHP driver in RPM based Linux OS.
Done! Do not forget to restart the web server.
You can 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 the Oracle and PostgreSQL databases are similar in some way. Working with them, you are directly connected to a specific database, rather than to the 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 to the host, then the second step - select the base to which you need to connect.
Working with the CUBRID DBMS in this sense is similar to Oracle and PostgreSQL. You are directly connected to the database with which you want to work. 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 connected, 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 monitor further actions in case of errors in transactions (i.e., to issue some specific messages, etc.). If you prefer to commit all transactions, you can enable auto_commit either immediately upon connection, or already during operation. In the first case, connect via cubrid_connect_with_url . Thus, all requests will be committed automatically. In the second case, call the cubrid_set_autocommit function at the right time, for example: You can also disable it the same way.
auto_commit . You can call cubrid_get_autocommit to find out the status of auto_commit during operation .
All other queries in CUBRID are very similar to MySQL syntax.
To enter a new or update an old value, you can use the same functions as in MySQL, but with the prefix “cubrid_”.
To get the ID generated during the last INSERT query, given that the table has a column with an AUTO_INCREMENT constraint, you can call cubrid_insert_id . This function differs from mysql_insert_id only in that in CUBRID the function returns a string, while MySQL is an int number. The convenience of the string is that this function can return an ID of type BIGINT, while the MySQL function cannot be used for large numbers. If the table does not have AUTO_INCREMENT columns, cubrid_insert_id will return 0.
After entering new values or updating existing ones, as in MySQL, you can call cubrid_affected_rowsto find out how many rows were changed as a result of the specified parameter. If the result resource is not specified, then the result of the last query will be implied. You can see the full list of all CUBRID PHP functions associated with the corresponding MySQL PHP functions here , where you will notice that almost all functions are identical, given the replacement of the mysql_ prefix with cubrid_ .
In the CUBRID PHP library, persistent connections are not implemented for the sole reason that CUBRID itself supports persistent connections. Those. You can set the CCI_PCONNECT [link to the manual] parameter in CUBRID Broker configurations 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 persistent connection pool can contain a maximum of 256 connections. All other connections will open as normal until any of the permanent connections are released. You can also set KEEP_CONNECTION .
You can also set prepared statements in the CUBRID PHP library. They can include both "?" And name placeholders. For example:
Application? as a placeholder. Using a placeholder. Everything is pretty simple. In CUBRID Broker configurations, there is one more parameter STATEMENT_POOLING , which is responsible for pooling prepared queries. If you enable this parameter (ON, OFF, by default ON ), then all prepared requests will be saved in a special pool, which allows you to reuse them. If you disable, then prepared requests will be closed immediately after the transaction.
So, as you have noticed, almost everything that works with MySQL will work with CUBRID. And if something doesn’t work out of the box for you, write in the comments. Let's figure it out. Or maybe, thanks to you, there will be something new in the CUBRID PHP API.
Immediately make a reservation that we will be talking about PHP version 5.2 and higher, because this is the minimum requirement for working with the CUBRID DBMS in PHP.
Installation
Installing a PHP driver for CUBRID is typical. In Windows, you can use the automatic uninstaller, or you can manually register everything you need in the php.ini configuration file . On Linux, the instructions are pretty much the same: you can use the PECL installer, or you can modify 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 the PHP driver for all kinds of combinations of PHP and CUBRID.
2. Make sure that the PHP engine and the CUBRID database engine are installed.
3. Run the installer.
4. Everything is like in a typical Windows installer:
- specify the installation path;
- confirm the folder name for the main Start 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 version of the CUBRID DBMS, as well as the folder in which PHP stores its drivers by default (usually it is C: \ Program Files \ PHP \ ext ). Based on this, the installer will copy the necessary php_cubrid.dll to this folder with all other drivers. He will then add the following two lines to the php.ini configuration file. Everything is standard, but everything happens automatically without your participation. You can look at the same method, but already in English and with screenshots here .
[PHP_CUBRID]
extension=php_cubrid.dll
Manual php.ini configuration on Windows
The same thing that CUBRID PHP API Installer does, you can do yourself.
- Download the driver you need from the official site (<40KB). Both thread-safe and non-thread-safe versions are available for Apache and IIS.
- Unzip the archive.
- Copy php_cubrid.dll to the folder where your other PHP drivers are located.
- Open the php.ini configuration file and insert the above two lines at the end of the file.
- Save the changes and restart your web server.
All is ready!
Installing PHP Drivers on Linux using the PECL Uninstaller
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 during installation as follows:sudo pecl install cubrid-8.3.0.0005
If at this moment the prompt "CUBRID base install dir [autodetect]:" appears , enter the full path of the directory in which CUBRID is installed. For example, if CUBRID is set to / home / cubridtest / CUBRID , enter / home / cubridtest / CUBRID . - At the end, you need to modify the php.ini configuration file by adding the following two lines at the end of the file.
[CUBRID]
extension=cubrid.so
Installing CUBRID PHP driver in RPM based Linux OS.
- Install phpize
yum install php-devel - Download the PEAR package
wget pear.php.net/go-pear.phar - Run the 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 during installation as follows:pecl install cubrid-8.3.0.0005
If at this moment the prompt "CUBRID base install dir [autodetect]:" appears , enter the full path of the directory in which CUBRID is installed. For example, if CUBRID is set to / home / cubridtest / CUBRID , enter / home / cubridtest / CUBRID . - At the end, you need to modify the php.ini configuration file by 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 php.ini configuration on Linux
You can install the CUBRID PHP driver without phpize and PEAR manually. See instructions below.
- Download the driver you need from the official site (<80KB). Both x64 and x86 versions are available.
- 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 change depending on the version of PHP).
- Open the php.ini configuration file (usually located in /etc/php5/apache2/php.ini ) and insert 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.
Working with the CUBRID PHP Driver
As you must know, connecting and working with the Oracle and PostgreSQL databases are similar in some way. Working with them, you are directly connected to a specific database, rather than to the 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 to the host, then the second step - select the base to which you need to connect.
Working with the CUBRID DBMS in this sense is similar to Oracle and PostgreSQL. You are directly connected to the database with which you want to work. 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 connected, 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 monitor further actions in case of errors in transactions (i.e., to issue some specific messages, etc.). If you prefer to commit all transactions, you can enable auto_commit either immediately upon connection, or already during operation. In the first case, connect via cubrid_connect_with_url . Thus, all requests will be committed automatically. In the second case, call the cubrid_set_autocommit function at the right time, for example: You can also disable it the same way.
cubrid_commit($conn);
$conn_url = "cci:CUBRID:127.0.0.1:33088:demodb:dba:123456:?autocommit=off"
$conn = cubrid_connect_with_url($conn_url);
if (cubrid_set_autocommit($conn, true)){
echo "Теперь все автоматом коммитится";
}
auto_commit . You can call cubrid_get_autocommit to find out the status of auto_commit during operation .
All other queries in CUBRID are very similar to MySQL syntax.
// The query syntax is the same
$ sql = "select sports, count (players) as players from event group by sports";
// Just 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 ("");
// column names in MySQL syntax query
for ($ i = 0; $ i <$ num_fields; ++ $ i) {
echo ("");
echo (cubrid_field_name ($ result, $ i));
echo ("");
}
echo ("");
// the same can be done in the style of CUBRID, but with only one request
$ columns = cubrid_column_names ($ result);
echo ("");
while (list ($ key, $ colname) = each ($ columns)) {
echo ("$ colname");
}
echo ("");
// Get a 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
// Everything is like in MySQL
echo ("");
for ($ i = 0; $ i <$ num_fields; ++ $ i) {
echo ("");
echo ($ row [$ i]);
echo ("");
}
echo ("");
}
}
cubrid_close ($ conn);
To enter a new or update an 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 during the last INSERT query, given that the table has a column with an AUTO_INCREMENT constraint, you can call cubrid_insert_id . This function differs from mysql_insert_id only in that in CUBRID the function returns a string, while MySQL is an int number. The convenience of the string is that this function can return an ID of type BIGINT, while the MySQL function cannot be used for large numbers. If the table does not have AUTO_INCREMENT columns, cubrid_insert_id will return 0.
After entering new values or updating existing ones, as in MySQL, you can call cubrid_affected_rowsto find out how many rows were changed as a result of the specified parameter. If the result resource is not specified, then the result of the last query will be implied. You can see the full list of all CUBRID PHP functions associated with the corresponding MySQL PHP functions here , where you will notice that almost all functions are identical, given the replacement of the mysql_ prefix with cubrid_ .
$affected_num = cubrid_affected_rows();
echo "Всего изменений: " . $affected_num;
Permanent Connections
In the CUBRID PHP library, persistent connections are not implemented for the sole reason that CUBRID itself supports persistent connections. Those. You can set the CCI_PCONNECT [link to the manual] parameter in CUBRID Broker configurations 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 persistent connection pool can contain a maximum of 256 connections. All other connections will open as normal until any of the permanent connections are released. You can also set KEEP_CONNECTION .
[%BROKER1]
SERVICE = ON
BROKER_PORT = 33000
...
KEEP_CONNECTION = ON
CCI_PCONNECT = ON
STATEMENT_POOLING = ON
Prepared Requests
You can also set prepared statements in the CUBRID PHP library. They can include both "?" And name placeholders. For example:
Application? as a placeholder. Using a placeholder. Everything is pretty simple. In CUBRID Broker configurations, there is one more parameter STATEMENT_POOLING , which is responsible for pooling prepared queries. If you enable this parameter (ON, OFF, by default ON ), then all prepared requests will be saved in a special pool, which allows you to reuse them. If you disable, then prepared requests will be closed immediately after the transaction.
// ? в качестве заполнителя
$sql = "SELECT * FROM game WHERE host_year = ?;
$req = cubrid_prepare($conn, $sql);
// порядковое связывание значений, начиная с 1
cubrid_bind($req, 1, 2004);
cubrid_execute($req);
// именной заполнитель
$sql2 = "SELECT * FROM game WHERE host_year = :host_year";
$req = cubrid_prepare($conn, $sql);
// также именное присвоение
cubrid_bind($req, ':host_year', 2004);
cubrid_execute($req);
So, as you have noticed, almost everything that works with MySQL will work with CUBRID. And if something doesn’t work out of the box for you, write in the comments. Let's figure it out. Or maybe, thanks to you, there will be something new in the CUBRID PHP API.