The bytea field using PostgreSQL keep reading the file and read the error proble

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

  The PostgreSQL bytea field types can be in the form of data stored in binary, the benefits of doing so is can be originally stored in the site directory files stored in the database, the downside is if the file is too much, too large, will cause the database data quantity big increase, backup and restore will waste a lot of time time, and the data can be faulty. Personally think that, in a small amount of paper case, the use of this storage method is very convenient.

  Hark back to the subject, the following introduces the concrete implementation method of how to use bytea to read the file field. The first is to store files on storage in the bytea field, the main use of PHP in the pg_escape_bytea method, the code as follows:

<?php
$dbconn = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'")
    OR DIE('Could not connect:' . pg_last_error());
$fileUrl = iconv("utf-8", "gbk", $fileUrl);//$FileName is a file path address, Chinese characters may be garbled, here to handle it
$fileContents = file_get_contents($fileUrl);
$escapeBytea = pg_escape_bytea($fileContents);//Binary string escape bytea data types
$insertStr = "INSERT INTO tableName(id, contents)
            VALUES(DEFAULT, '{$escapeBytea}')";
pg_query($dbconn, $insertStr);
?>

   From the bytea field restoring files is used pg_unescape_bytea method in PHP, the code is as follows:

<?php
    $dbconn = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'")
                    OR DIE('Could not connect: ' . pg_last_error());
    $selectStr = "SELECT contents
                FROM tableName
                WHERE id = " . $fileId;//$FileId file ID
    $query = pg_query($dbconn, $selectStr);
    while($row = pg_fetch_array($query, null, PGSQL_ASSOC)){
        $escapeBytea = $row['contents'];
        $fileContents = pg_unescape_bytea($escapeBytea); //For binary data
        file_put_contents($fileName, $fileContents); //$FileName is the name with the suffix file name, such as hello.pdf
}
?>

   The export file, if the binary data conversion error occurs file not open phenomenon, such as errors in the PDF file open error as shown below:

  Such errors are particularly prone to occur in the database migration (I is migrating from PostgreSQL 8.4 to 9.1), The solution is to modify the PostgreSQL configuration file
Postgresql.conf, Will set the bytea_output output type for escape type (escape) output, Bytea_output ='escape'(if there is #, Delete the open configuration), Then the reload PostgreSQL configuration for the changes to take effect, So the binary data can be normal decoding and output to a file.  


Blog statement:

  All the articles in this blog, in addition to the title “ reprinted ” words, all articles are original works or finish summary in the data access, refers to non reprint articles please specify this statement. — — blog Park -pallee


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Carl at October 23, 2013 - 10:33 PM