utf8なclientから euc_jpな postgres への機種依存文字登録には、SET NAMES 'UTF8' と、mb_convert_encoding($org_char,'CP51932','UTF-8' ) (php版)

以下の通りで、ポイントは

• SET NAMES 'UTF8'
• mb_convert_encoding($org_char,'CP51932','UTF-8' ) によるsanitize?

DB & DB TABLE

db_rear_hon=> \l db_rear_hon
                           List of databases
    Name     | Owner  | Encoding | Collate | Ctype | Access privileges 
-------------+--------+----------+---------+-------+-------------------
 db_rear_hon | xxuser | EUC_JP   | C       | C     | 
(1 row)

db_rear_hon=> \d test_tbl
                     Table "public.test_tbl"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null | 
 val    | character varying(256) |           |          | 
Indexes:
    "test_tbl_pkey" PRIMARY KEY, btree (id)

php

<?php

const COMMON_CONF = [
    "db" =>[
        "host"=>"pgsql:dbname=db_rear_hon".
        " host=localhost".
        " port=5432",
        "user"=>"bbuser",
        "pass"=>"bbuser" ],
    "postgres_client_encoding"=>"UTF-8"
];

main();


function main(){
    my_ini_set();
    
    $func_name = __CLASS__." ".__FUNCTION__;
    write_log( $func_name );

    $dbh = connect_db();
    write_log( print_r($dbh,true) );

    del_all($dbh);
    
    $add_chars = [
        "あ","髙","㎡","m²","～","〜",
        'Ⅰ','Ⅱ','Ⅲ','Ⅳ','Ⅴ','Ⅵ','Ⅶ','Ⅷ','Ⅸ','Ⅹ',
        '①','②','③','④','⑤','⑥','⑦','⑧','⑨',
        '㈱','㈲','㈳','㈵','㈶','㎡','m²',
        '髙','﨑','神','靑','黑','羽','福','館','朗','隆',
        '塚','晴','﨔','凞','凞','猪','益','祥','靖','精',
        '諸','逸','都','飯','飼','館','鶴','塚','增','寬',
        '晴','敎','朗','橫','淸','瀨','猪','靖','精','緖',
        '薰','諸','賴','郞','都','鄕','閒'];

    $i= 0;
    foreach($add_chars as $org_char){
        // sanitize for EUC_JP ≒ CP51932
        //   refer to https://www.softel.co.jp/blogs/tech/archives/5145
        $add_char = mb_convert_encoding($org_char,'CP51932','UTF-8' );
        $add_char = mb_convert_encoding($add_char,'UTF-8', 'CP51932' );
        add_row($dbh, $i++,$add_char);
    }
    
    $tbl_rows = get_all($dbh);
    write_log( print_r($tbl_rows,true) );
}


function add_row($dbh, $atri_id,$atri_val){
    $func_name = __CLASS__." ".__FUNCTION__;
    write_log( "$func_name '$atri_id':'$atri_val'" );

    $sql = "insert into test_tbl (id,val) values(?,?)";

    try {
        $sth = $dbh->prepare($sql);
        $sth->execute([$atri_id,$atri_val]);
    } catch (PDOException $e) {
        $tmp_msg = join(" ",
                        ['ERROR fail sql ',
                         $e->getMessage(),
                         $sql,
                         "'$atri_id':'$atri_val'"]);
        write_log($tmp_msg);
        return false;
    }
    return true;
}

function get_all($dbh){
    $func_name = __CLASS__." ".__FUNCTION__;
    write_log( $func_name );

    $sql = "select * from test_tbl";

    try {
        $sth = $dbh->prepare($sql);
        $sth->execute();
    } catch (PDOException $e) {
        write_log('ERROR fail sql '. $e->getMessage(). " $sql");
        return [];
    }

    $rows = $sth->fetchAll(PDO::FETCH_ASSOC);
    return $row;
}


function del_all($dbh){
    $func_name = __CLASS__." ".__FUNCTION__;
    write_log( $func_name );

    $sql = "delete from test_tbl";

    try {
        $sth = $dbh->prepare($sql);
        $sth->execute();
    } catch (PDOException $e) {
        write_log('ERROR fail sql '. $e->getMessage(). " $sql");
        return false;
    }
    return true;
}


function connect_db(){
    $func_name = __CLASS__." ".__FUNCTION__;
    write_log( $func_name );
        
    $common_conf = COMMON_CONF;

    try {
        $dbh = new PDO($common_conf['db']['host'],
                       $common_conf['db']['user'],
                       $common_conf['db']['pass'],
                       [PDO::ATTR_ERRMODE =>PDO::ERRMODE_EXCEPTION]);
        
    } catch (Exception $e) {
        $tmp_msg = "ERROR fail $func_name " . $e->getMessage();
        write_log($tmp_msg);
        exit($tmp_msg);
    }
    
    if($common_conf["postgres_client_encoding"]){
        set_postgres_client_encoding($dbh,'UTF8');
    }
    
    return $dbh;
}

// refer to https://www.postgresql.jp/document/12.0/html/multibyte.html
function set_postgres_client_encoding($dbh,$encoding){
    $func_name = __CLASS__." ".__FUNCTION__;
    write_log( $func_name );

    $sql = "SET NAMES '$encoding'";

    try {
        $sth = $dbh->prepare($sql);
        $sth->execute();
    } catch (PDOException $e) {
        $this->write_log('ERROR fail sql '. $e->getMessage(). " $sql");
        return false;
    }
    return true;
}


function write_log($msg){
    error_log($msg);
}


function my_ini_set(){
    ini_set("log_errors",     "On" );
    ini_set("error_reporting","E_ALL");
    
    ini_set("date.timezone","Asia/Tokyo");
    
    mb_language('Japanese');
    mb_internal_encoding('UTF-8');

    ini_set('error_log', 'php://stderr');
}