Detailed explanation of how to connect and use dm database in php7 (picture and text)

This article is introduced by the tutorial column of PHP7 on how to connect and use Dameng (dm) database in php7.2. I hope it will be helpful to friends in need!

Foreword

---

The company wants to engage in localized development. The database uses Dameng database. The Dameng version is dm8 and the PHP version is 7.2. The early stage is carried out on Windows. Develop.

The database is migrated from mysql to Dameng database. I will not go into the migration method and the PHP extension installation method. There is quite a lot of information on the Internet in this regard.

However, there is relatively little information on how to use PHP to connect to Dameng database. Everyone knows the customer service technology. All the information depends on reading documents. I will briefly provide you with the pitfalls I have stepped on and the methods. It is to directly operate the database, and then you can encapsulate it according to your own preferences.

Briefly explain Dameng's information directory. The "doc" under Dameng's installation directory mainly contains various usage documents. "drivers" contains extensions for various languages. PHP's so extension is Taken from here, I mainly use the extension of php_dm. There is too little information on pdo_dm, so I didn’t do much research.

There are two points that need to be reminded when instantiating the database. Character sets and string case sensitivity must be remembered in advance Set up well and don’t dig yourself into a hole.

My experience is that the data table names and field names migrated from MySQL are all lowercase, which results in various pitfalls when used later. Later I found that the case sensitivity can be removed.

Here we will use the CITY table in the official sample library to demonstrate various usage methods of php7.2.

##Query

---

Without further ado, let’s start with the code

//连接数据库
$link = dm_connect("localhost", "SYSDBA", "SYSDBA");
if(!$link){
    var_dump(@dm_error());
    var_dump(iconv("GBK","UTF-8",@dm_errormsg()));
}
dm_setoption($link,1,12345,1);//设置 dm 连接和语句的相关属性，设置UTF8

$query = "select * from DMHR.CITY";
$result = dm_exec($link,$query);

print " 查询结果:";
while ($line = dm_fetch_array($result)){
    print_r($line);
    echo '<br>';
}

/* 释放资源 */
dm_free_result($result);

/* 断开连接 */
dm_close($link);

The usage methods of Dameng's php official extension php5 and php7 are very different. Many methods in php5 cannot be used in php7. Some methods are replaced by other methods in php7. Most of the information on the Internet is php5. Yes, it is completely unusable in php7.

The problem of garbled Chinese characters in query statements is a sinkhole. I tried this method after checking the documentation for a long time. If there are other more elegant methods, please teach me, and everyone can learn from each other.

Insert

//连接数据库
$link = dm_connect("localhost", "SYSDBA", "SYSDBA");
if(!$link){
    var_dump(@dm_error());
    var_dump(iconv("GBK","UTF-8",@dm_errormsg()));
}
dm_setoption($link,1,12345,1);//设置 dm 连接和语句的相关属性，设置UTF8

$query = "INSERT INTO DMHR.CITY (CITY_ID,CITY_NAME,REGION_ID) VALUES ('JL','吉林','1')";
$result = dm_exec($link,$query);

if($result){
    echo "插入成功";
    //曲线查询插入id
    /*$query = "SELECT @@IDENTITY as insert_id";
    $result = dm_exec($link,$query);
    $line = dm_fetch_array($result);
    echo ',ID:';
    print_r($line);*/
}

/* 释放资源 */
dm_free_result($result);

/* 断开连接 */
dm_close($link);

---

The officially provided dm_insert_id() function seems to be only available in php5, php7 does not have this function, and can only be incremented through curve query The id value. Of course, the table in the demonstration does not have an auto-increment ID. At the same time, SELECT @@IDENTITY as insert_id must be successfully queried. Even if the insertion fails, the self-increment ID of the previous successful insertion will be returned. 千Never use the auto-increment ID to determine whether the statement is inserted successfully.

Update

//连接数据库
$link = dm_connect("localhost", "SYSDBA", "SYSDBA");
if(!$link){
    var_dump(@dm_error());
    var_dump(iconv("GBK","UTF-8",@dm_errormsg()));
}
dm_setoption($link,1,12345,1);//设置 dm 连接和语句的相关属性，设置UTF8

$query = "UPDATE  DMHR.CITY SET REGION_ID='2' WHERE CITY_ID='JL'";
$result = dm_exec($link,$query);

if($result){
    echo "更新成功";
}

/* 释放资源 */
dm_free_result($result);

/* 断开连接 */
dm_close($link);

---

Update is very simple

Delete

//连接数据库
$link = dm_connect("localhost", "SYSDBA", "SYSDBA");
if(!$link){
    var_dump(@dm_error());
    var_dump(iconv("GBK","UTF-8",@dm_errormsg()));
}
dm_setoption($link,1,12345,1);//设置 dm 连接和语句的相关属性，设置UTF8

$query = "DELETE FROM DMHR.CITY WHERE (CITY_ID='JL')";
$result = dm_exec($link,$query);

if($result){
    echo "删除成功";
}

/* 释放资源 */
dm_free_result($result);

/* 断开连接 */
dm_close($link);

---

Deletion is also very simple, there is nothing special to pay attention to

Transaction

---

According to the official documentation: "DM does not provide a statement that explicitly defines the start of a transaction. The first executable SQL statement (except the login statement) implicitly defines the start of the transaction. Start", this is the reason why there is no transaction start method defined, but when we want to start a transaction from a certain program, we can use the dm_autocommit() function to turn off the automatic commit of the transaction, and turn on the automatic commit after the program ends

//连接数据库
$link = dm_connect("localhost", "SYSDBA", "SYSDBA");
if(!$link){
    var_dump(@dm_error());
    var_dump(iconv("GBK","UTF-8",@dm_errormsg()));
}
dm_setoption($link,1,12345,1);//设置 dm 连接和语句的相关属性，设置UTF8

$query = "INSERT INTO DMHR.CITY (CITY_ID,CITY_NAME,REGION_ID) VALUES ('JL','吉林','1')";
$result = dm_exec($link,$query);

if($result){
    echo "插入成功。";
}

$result = dm_autocommit($link,false);//事务自动提交关闭
$query = "UPDATE  DMHR.CITY SET CITY_NAME='辽宁' WHERE (CITY_ID='SY')";
$result = dm_exec($link,$query);
if($result){
    echo "更新成功，回滚。";
}


dm_rollback($link);//回滚
//dm_commit($link);//提交

$result = dm_autocommit($link,true);//开启事务自动提交，结束事务

/* 断开连接 */
dm_close($link);

Pits that have been stepped on

---

1. Obtain the time in the timestamp format from the Dameng database

select DATEDIFF(s, '1970-01-01 00: 00:00', GETUTCDATE());

2. If the database used before is mysql, pay attention to the scale behind the two types of time formats DATETIME and TIMESTAMP, if it is not 0 The time accuracy will be longer

3. Some fields will become uppercase when queried, such as "count"

Solution: Change the fields Use double quotes to quote the above example:

select count(1) as "count" from "DMHR"."CITY";

四、group by语句的使用很严格（或者说mysql的group by过于放松），select中除聚合函数之外出现的所有字段必须要在group by里面。

比方举一个错误的例子：

select EMPLOYEE_NAME,JOB_ID from "DMHR"."EMPLOYEE" group by JOB_ID;
EMPLOYEE_NAME和字段没在group by 中，执行必定失败
提供一种解决思路：

select * from "DMHR"."EMPLOYEE" where EMPLOYEE_ID in (select min(EMPLOYEE_ID) as minid from "DMHR"."EMPLOYEE" group by JOB_ID)

同样select中如果有聚合函数之外的字段，需要加入group by。错误的例子：

select min(EMPLOYEE_ID),EMPLOYEE_NAME,JOB_ID from "DMHR"."EMPLOYEE";
select中有min()函数外还有其他字段，执行必定失败。
如果一定要在其他很多字段里面加入聚合函数，提供一种思路：

select t1.EMPLOYEE_NAME,t1.JOB_ID,t2.minid from "DMHR"."EMPLOYEE" t1
left join ( select min(EMPLOYEE_ID) as minid,JOB_ID from "DMHR"."EMPLOYEE" group by JOB_ID ) t2 on t2.JOB_ID=t1.JOB_ID
where t1.EMPLOYEE_ID in (select min(EMPLOYEE_ID) as minid from "DMHR"."EMPLOYEE" group by JOB_ID);

结语

---

目前踩过的坑就这些了，希望能够帮助到大家。
对于其他问题还是要多翻阅官方文档了。

The above is the detailed content of Detailed explanation of how to connect and use dm database in php7 (picture and text). For more information, please follow other related articles on the PHP Chinese website!