PHP PDO将一个文件夹的文件装载到ORACLE数据库BLOB列,并将BLOB列下载到另一个文件夹

秒速五厘米 2023-09-29 10:29 14阅读 0赞

<?php
    
    //OR connect using the Oracle Instant Client
    $dsn = 'oci:dbname=//192.168.50.128:1521/orcl';
    
    $username = 'scott';
    $password = 'tiger';
    
    try {
        $pdo = new PDO($dsn, $username, $password);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
        exit;
    }
    
    
    
    $arrFiles = array();
    $iterator = new FilesystemIterator("InputFiles");
    
    foreach ($iterator as $entry) {
        $arrFiles[] = $entry->getFilename();
        echo ( $entry->getFilename());
        echo ( $entry->getPathname());
        $name = $entry->getFilename();
        $blob = fopen($entry->getPathname(), 'rb');
        $sql = "INSERT INTO bindata(name,data) VALUES(?,EMPTY_BLOB()) RETURNING data INTO ? ";
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(1, $name);
        $stmt->bindParam(2, $blob, PDO::PARAM_LOB);
        $pdo->beginTransaction();
        $stmt->execute();
        $pdo->commit();
    }
    ?>

<?php
    
    //OR connect using the Oracle Instant Client
    $dsn = 'oci:dbname=//192.168.50.128:1521/orcl';
    
    $username = 'scott';
    $password = 'tiger';
    
    $pdo = new PDO($dsn, $username, $password);
    
    $sql = "SELECT name,data FROM bindata";
    $stmt = $pdo->query($sql);
    while ($row = $stmt->fetch()) {
        $filename = "DownLoadFiles/" . $row['NAME'];
        $content = stream_get_contents($row['DATA']);
        file_put_contents($filename, $content);
    }
    ?>

CREATE TABLE "SCOTT"."BINDATA" (
        "ID"   NUMBER(10, 0),
        "NAME" VARCHAR2(512 BYTE),
        "DATA" BLOB
    )
     
    ALTER TABLE bindata ADD (
        CONSTRAINT bindata_pk PRIMARY KEY ( id )
    );
     
    CREATE SEQUENCE bindata_sequence;
     
    CREATE OR REPLACE TRIGGER bindata_on_insert BEFORE
        INSERT ON bindata
        FOR EACH ROW
    BEGIN
        SELECT
            bindata_sequence.NEXTVAL
        INTO :new.id
        FROM
            dual;
     
    END;