Subject | PHP issue - Firebird CHAR field with charset UTF8 and ANY COLLATION PHP is loading white spaces |
---|---|
Author | |
Post date | 2016-11-04T19:52:51Z |
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?