PHP如何将OCI(Oracle)文件上传功能代码迁移适配至PostgreSQL

原有Oracle实现代码 #

$file = basename($_SERVER['PHP_SELF']); //get index.php
if ($file!='index.php') include_once "nologin.php";
   
   
$result = 0;
if(isset($_POST['submitBtn']) && $_FILES['myfile']['size'] > 0)
{
    $fileName = $_FILES['myfile']['name'];
    $tmpName  = $_FILES['myfile']['tmp_name'];
    $fileSize = $_FILES['myfile']['size'];
    $fileType = $_FILES['myfile']['type'];
    $no_kp_baru   = $_POST['no_kp_baru'];
    $latihan_id = $_POST['latihan_id'];
    $tkh_mula1 = $_POST['tkh_mula'];
    $masa_mula = $_POST['masa_mula'];
    
    list($d, $m, $y) = explode('/', $tkh_mula1);//'dd/mm/yyyy' format
    $mk=mktime(0, 0, 0, $m, $d, $y);
    $tkh_mula=strftime('%d-%b-%Y',$mk);

    if(!get_magic_quotes_gpc())
    {
        $fileName = addslashes($fileName);
    }

    include_once "../db_conn.php";
    
    $sql = "
    update log_latihan set
        PENGESAHAN = NULL,
        TKH_PENGESAHAN = NULL,
        ID_PENGESAH = NULL
    where
        NO_KP_BARU = :NO_KP_BARU
        and LATIHAN_ID = :LATIHAN_ID
        and TKH_MULA = :TKH_MULA
        and MASA_MULA = :MASA_MULA
        ";
    
    // Update using bind variables
    $parse = oci_parse($conn, $sql);
    oci_bind_by_name($parse, ":NO_KP_BARU", $no_kp_baru);
    oci_bind_by_name($parse, ":LATIHAN_ID", $latihan_id);
    oci_bind_by_name($parse, ":TKH_MULA", $tkh_mula);
    oci_bind_by_name($parse, ":MASA_MULA", $masa_mula);
    oci_execute($parse);
    //echo oci_num_rows($parse);
    if (oci_num_rows($parse))
    {
    
        $stmt = oci_parse($conn, "INSERT INTO log_latihan_sijil (no_kp_baru,latihan_id,tkh_mula,masa_mula,nama_fail,type,saiz_fail,tkh_upload,sijil) 
        VALUES(:NO_KP_BARU,:LATIHAN_ID,to_date(:TKH_MULA,'DD/MM/YYYY'),:MASA_MULA,:NAMA_FAIL,:TYPE,:SAIZ_FAIL,sysdate,EMPTY_BLOB()) RETURNING sijil INTO :FILE_CONTENT");
        
        $lob = oci_new_descriptor($conn, OCI_D_LOB);
        oci_bind_by_name($stmt, ':FILE_CONTENT', $lob, -1, OCI_B_BLOB);
        oci_bind_by_name($stmt, ":NO_KP_BARU", $no_kp_baru);
        oci_bind_by_name($stmt, ":LATIHAN_ID", $latihan_id);
        oci_bind_by_name($stmt, ":TKH_MULA", $tkh_mula1);
        oci_bind_by_name($stmt, ":MASA_MULA", $masa_mula);
        oci_bind_by_name($stmt, ":NAMA_FAIL", $fileName);
        oci_bind_by_name($stmt, ":TYPE", $fileType);
        oci_bind_by_name($stmt, ":SAIZ_FAIL", $fileSize);
        oci_execute($stmt, OCI_DEFAULT);
        //var_dump(oci_error($stmt));

        if ($lob->savefile($tmpName)) {
            oci_commit($conn);
            $result=1;
        }
        $lob->free();
        oci_free_statement($stmt);
    }
    oci_free_statement($parse);
}

前置准备 #

• 确认PHP环境已开启pgsql扩展
• 调整PostgreSQL表结构：将log_latihan_sijil表的sijil字段类型设置为bytea（对应Oracle的BLOB类型）
• 替换db_conn.php中的Oracle连接逻辑为PostgreSQL连接，示例代码：

// db_conn.php 示例
$conn = pg_connect("host=数据库地址 port=5432 dbname=库名 user=用户名 password=密码");
if (!$conn) {
    die("数据库连接失败: " . pg_last_error());
}

适配后完整代码 #

$file = basename($_SERVER['PHP_SELF']); //get index.php
if ($file!='index.php') include_once "nologin.php";

$result = 0;
if(isset($_POST['submitBtn']) && $_FILES['myfile']['size'] > 0)
{
    $fileName = $_FILES['myfile']['name'];
    $tmpName  = $_FILES['myfile']['tmp_name'];
    $fileSize = $_FILES['myfile']['size'];
    $fileType = $_FILES['myfile']['type'];
    $no_kp_baru   = $_POST['no_kp_baru'];
    $latihan_id = $_POST['latihan_id'];
    $tkh_mula1 = $_POST['tkh_mula'];
    $masa_mula = $_POST['masa_mula'];
    
    list($d, $m, $y) = explode('/', $tkh_mula1);//'dd/mm/yyyy' format
    $mk=mktime(0, 0, 0, $m, $d, $y);
    $tkh_mula=strftime('%d-%b-%Y',$mk);

    include_once "../db_conn.php";
    
    // 开启事务保证操作原子性
    pg_query($conn, "BEGIN");
    
    $sql = "
    update log_latihan set
        PENGESAHAN = NULL,
        TKH_PENGESAHAN = NULL,
        ID_PENGESAH = NULL
    where
        NO_KP_BARU = $1
        and LATIHAN_ID = $2
        and TKH_MULA = $3
        and MASA_MULA = $4
    ";
    
    // 预编译执行更新语句
    $parse = pg_prepare($conn, "", $sql);
    $executeRes = pg_execute($conn, "", array($no_kp_baru, $latihan_id, $tkh_mula, $masa_mula));
    
    if (pg_affected_rows($executeRes) > 0)
    {
        // 读取转义文件内容
        $fileContent = file_get_contents($tmpName);
        $escapedFile = pg_escape_bytea($conn, $fileContent);
        
        $insertSql = "INSERT INTO log_latihan_sijil (no_kp_baru,latihan_id,tkh_mula,masa_mula,nama_fail,type,saiz_fail,tkh_upload,sijil) 
        VALUES($1,$2,to_date($3,'DD/MM/YYYY'),$4,$5,$6,$7,CURRENT_TIMESTAMP,$8)";
        
        $stmt = pg_prepare($conn, "", $insertSql);
        $insertRes = pg_execute($conn, "", array(
            $no_kp_baru,
            $latihan_id,
            $tkh_mula1,
            $masa_mula,
            $fileName,
            $fileType,
            $fileSize,
            $escapedFile
        ));

        if ($insertRes) {
            pg_query($conn, "COMMIT");
            $result=1;
        } else {
            pg_query($conn, "ROLLBACK");
        }
    } else {
        pg_query($conn, "ROLLBACK");
    }
    // 释放资源
    pg_free_result($parse);
    if(isset($stmt)) pg_free_result($stmt);
}

核心适配改动说明 #

• 参数占位符：PostgreSQL原生驱动使用$N格式的位置占位符，替换原有Oracle的:命名参数语法
• 大文件存储：无需单独初始化LOB描述符，直接读取文件内容后通过pg_escape_bytea转义即可插入bytea类型字段
• 系统函数替换：Oracle的sysdate替换为PostgreSQL的CURRENT_TIMESTAMP，日期转换函数to_date语法兼容可直接使用
• 事务控制：手动开启事务保证更新和插入操作的原子性，操作成功提交、失败回滚
• 函数映射：oci_parse→pg_prepare、oci_execute→pg_execute、oci_num_rows→pg_affected_rows

内容的提问来源于stack exchange，提问作者Syahmie