最近花了不少时间把项目数据库从oracle迁移到达梦8,迁移过程中碰上了不少问题,后面有时间我整理一下心得。
今天先发一下php使用dm_pdo操作达梦数据库的示例代码,里面包括了常规的绑定变量查询,存储过程调用,clob类型操作等。
使用的是达梦提供的pdo_dm驱动,相关配置信息请参考达梦的官方帮助文档(达梦8安装时自带)
ini_set("display_errors","On");
ini_set("log_errors", 1);
ini_set("error_log", "/usr/local/apache2/logs/dm_error.log");
error_reporting(E_ALL)
//获取变量值字符串
function strHt($var)
{
return print_r($var, true);
}
function mySubFile($str)
{
if ($str == "")
{
return "";
}
return strstr($str, "inc_chk");
}
//日志打印函数
function dbgLogHt(string $format, ...$args)
{
$log = "%s:%s %s";
$backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
#$strHt = sprintf($log, "[dbg]", trim($backtrace[1]["file"]), $backtrace[1]["line"], sprintf($format, ...$args));
#($strHt, 0);
error_log(sprintf($log, trim($backtrace[0]["file"]), $backtrace[0]["line"], sprintf($format, ...$args)));
//输出调用堆栈 方便调试
error_log(sprintf("from %s:%s %s:%s %s:%s %s:%s %s:%s %s:%s %s:%s %s:%s" . PHP_EOL,
mySubFile($backtrace[1]["file"]), $backtrace[1]["line"], mySubFile($backtrace[2]["file"]), $backtrace[2]["line"],
mySubFile($backtrace[3]["file"]), $backtrace[3]["line"], mySubFile($backtrace[4]["file"]), $backtrace[4]["line"],
mySubFile($backtrace[5]["file"]), $backtrace[5]["line"], mySubFile($backtrace[6]["file"]), $backtrace[6]["line"],
mySubFile($backtrace[7]["file"]), $backtrace[7]["line"], mySubFile($backtrace[8]["file"]), $backtrace[8]["line"])
);
# error_log(strHt($backtrace));
}
function testPdo()
{
try {
dbgLogHt(""); //dm:代表使用达梦pdo驱动
$pdo = new PDO("dm:host=127.0.0.1", "user_hch", "pass:kingstarer");
//设置报错方式为抛出异常
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$rs = $pdo->query("SELECT * FROM V$VERSION WHERE ROWNUM = 1;");
$rs->setFetchMode(PDO::FETCH_ASSOC);
$result_arr = $rs->fetchAll();
error_log(strHt($result_arr));
$arr = []; //测试绑定变量 包括入参和出参
$arr[":iCount"] = "5";
$arr[":rn"] = "hhc";
$strSql = "select :rn rn, table_name from user_tables where rownum < :iCount";
$stmt = $pdo->prepare($strSql);
$stmt->bindParam(":rn", $arr[":rn"]);
$stmt->bindParam(":iCount", $arr[":iCount"]);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { // $row是一行,使用while依次输出下一行
dbgLogHt("while");
dbgLogHt(strHt($row));
error_log(strHt($row));
}
dbgLogHt("");
// 测试存储过程调用
$strSql = "begin :ret := f_prod_demo(:num, "UPD", 123, :o_err_code, :o_err_msg, :o_call_stack); end;";
$stmt = $pdo->prepare($strSql);
$arr = [];
$arr[":num"] = "123";
$arr[":ret"] = "o_err_code";
$arr[":o_err_code"] = "o_err_code";
$arr[":o_err_msg"] = "o_err_msg";
$arr[":o_err_msg"] = "o_call_stack";
$stmt->bindParam(":num", $arr[":num"]);
$stmt->bindParam(":ret", $arr[":ret"]);
$stmt->bindParam(":o_err_code", $arr[":o_err_code"]);
$stmt->bindParam(":o_err_msg", $arr[":o_err_msg"]);
$stmt->bindParam(":o_call_stack", $arr[":o_call_stack"]);
$stmt->execute();
error_log(strHt($arr));
$stmt->closeCursor();
//测试达梦插入clob数据
$strSql = "INSERT into HCH_TEST values(1, "333")";
$stmt = $pdo->prepare($strSql);
$stmt->execute();
error_log("insert clob ok");
$stmt->closeCursor();
//测试使用绑定变量插入clob类型数据
$strSql = "INSERT into HCH_TEST values(:id, :mess)";
error_log("");
$stmt = $pdo->prepare($strSql);
error_log("");
$arr[":id"] = "123";
$stmt->bindParam(":id", $arr[":id"]);
error_log("");
$arr[":mess"] = "123";
$stmt->bindParam(":mess", $arr[":mess"]);
error_log("");
$stmt->execute();
error_log("");
$stmt->closeCursor();
error_log("insert clob param ok");
error_log("");
$pdo = null;
//更多达梦数据库开发经验请参考: https://www.cnblogs.com/kingstarer/
} catch (PDOException $e) {
error_log("Error: " . $e->getMessage());
error_log("");
//die();
}
//exit(1);
}
网上关于达梦的资料比较少,希望这篇心得能起抛砖引玉的效果。