Subject PHP issue - Firebird CHAR field with charset UTF8 and ANY COLLATION PHP is loading white spaces
Author

PHP issue - Firebird CHAR field with charset UTF8 and ANY COLLATION UCS_BASIC or UNICODE or UNICODE_CI or UNICODE_CI_AI or UTF8 PHP is loading white spaces

Example
'S   ' instead of 'S'

Environment test
Windows 10
PHP 7.1.0RC5 x64 VC14 TS
Apache Lounge 2.4.23 x64 VC14
firebird 3.0.1 x64

configuration
php.ini
    default_charset = "UTF-8"
connection to the database
    ibase_connect ( "localhost:" DB, user, pw, "UTF8".
connection PHP script file in UTF-8
PHP script file with the test in UFT-8
response header
   Content-Type: text / html; charset = UTF-8
file.html
    <meta charset="UTF-8">


Example
CHAR SIZE 1
in the database
    'S'
In php results
    'S   ' instead of 'S'
vardump (char_field)
string (4) "S   "

(Both ibase_query as PDO)

IMPORTANT
To isolate de problem, all create and insert were executed by isql


ALL DDL

************************** DDL FOR COLLATION UCS_BASIC **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UCS_BASIC;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UCS_BASIC,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UCS_BASIC,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UCS_BASIC,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UCS_BASIC
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





************************** DDL FOR COLLATION UNICODE **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UNICODE,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





************************** DDL FOR COLLATION UNICODE_CI **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UNICODE_CI,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE_CI
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





************************** DDL FOR COLLATION UNICODE_CI_AI **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





************************** DDL FOR COLLATION UTF8 **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UTF8,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UTF8,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UTF8,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UTF8
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);




************************** INSERT **************************

INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (1, 'TESTE1', 'A', NULL, 'S', '1');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (2, 'TESTE2', 'A', 5406, 'N', '2');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (3, 'TESTE3', 'A', 5406, 'S', '3');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (4, 'TESTE4', 'A', 5406, 'S', '4');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (5, 'TESTE5', 'A', 5406, 'S', '5');
COMMIT;





script test php
<?php
    define("DB", "D:/MYDB_UTF8.FDB");
    define("USER", "SYSDBA");
    define("PW", "A");

    $con = ibase_connect("localhost:".DB, USER, PW, "UTF8", 0, 3) or die ("Erro ao Tentar Conectar com o Banco de Dados: Código do erro: ".ibase_errcode()." Mensagem do erro: ".ibase_errmsg());

    $q = ibase_query($con, "SELECT * FROM USUARIO");

    while($row = ibase_fetch_object($q)){
        echo $row->CODIGO."=";
        echo $row->ACESSOSISTEMA.'=';
        var_dump($row->ACESSOSISTEMA);
        echo "<br>";
    }
    ibase_free_result($q);
?>




script test php with PDO
<?php

    define("DB", "D:/MYDB_UTF8.FDB");

    define("USER", "SYSDBA");
    define("PW", "A");

    try {

        $dsn = "firebird:dbname=localhost/3050:".DB.";charset=UTF8"; //dbname=host/porta:path
        $pdoconn = new PDO($dsn, USER, PW);
        $pdoconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdoconn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

        //$q = $pdoconn->query("SELECT * FROM USUARIO");

        $q = $pdoconn->prepare("SELECT * FROM USUARIO");
        $q->execute();

        while($row = $q->fetch(PDO::FETCH_OBJ)){
            echo $row->CODIGO."=";
            echo $row->ACESSOSISTEMA.'=';
            var_dump($row->ACESSOSISTEMA);
            echo "<br>";
        }

    } catch (PDOException $e) {
        echo "código do erro: ". $e->getCode() ."<br>". $e->getMessage();
    }
?>



Someone with the same environment could reproduce to confirm if it's a php bug?