As you know, new data types were added in Firebird 4.0:
-
DECFLOAT(16)
and DECFLOAT(34)
;
-
TIME WITH TIME ZONE
and TIMESTAMP WITH TIME ZONE
;
-
INT128
, which is the basis of NUMERIC(38, x)
and DECIMAL(38, x)
.
The first two data types are not problematic, they are rarely used and are intended mainly for precise scientific calculations.
Things are more complicated with TIME WITH TIME ZONE
and TIMESTAMP WITH TIME ZONE
. The fact is that before these data types were introduced, the CURRENT_TIME
and CURRENT_TIMESTAMP
context variables returned values of the TIME WITHOUT TIME ZONE
and TIMESTAMP WITHOUT TIME ZONE
types, although this contradicted the SQL standard, but now they return TIME WITH TIME ZONE
and TIMESTAMP WITH TIME ZONE
. The LOCALTIME
and LOCALTIMESTAMP
context variables are provided to return values without time zone information. Therefore, the queries that previously worked correctly return new data types that are unknown to the pdo_firebird driver.
Try running the following example:
Script index_ts.php
namespace MyApp;
use PDO;
use PDOException;
use PDOStatement;
const TS_SQL = <<<'SQL'
SELECT
CURRENT_TIMESTAMP AS TS_TZ,
LOCALTIMESTAMP AS TS,
CURRENT_TIME AS T_TZ,
LOCALTIME AS T
FROM RDB$DATABASE
SQL;
try {
echo 'PHP version: ' . phpversion() . "\n";
$dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
$sth = $dbh->prepare(TS_SQL);
$sth->execute();
$row = $sth->fetch(PDO::FETCH_ASSOC);
echo "Result: \n";
var_dump($row);
echo "\n";
$sth->closeCursor();
}
catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . "\n";
}
The result will be the following:
PHP version: 8.3.11
Result:
array(4) {
["TS_TZ"]=>
NULL
["TS"]=>
string(19) "2024-09-11 15:50:21"
["T_TZ"]=>
NULL
["T"]=>
string(8) "15:50:21"
}
That is, for types with time zones, the value null
is always returned, since the driver does not know anything about the new data types.
The situation is even worse when using NUMERIC
and DECIMAL
. The fact is that when calculating the aggregate functions SUM
and AVG
, as well as when multiplying, the result with the widest data type is returned. Before Firebird 4.0, the most capacious type for NUMERIC
and DECIMAL
was BIGINT
, which allowed such numbers to be calculated with an accuracy of up to 18 digits. Starting with Firebird 4.0, the most capacious type for NUMERIC
and DECIMAL
is INT128
. This allows numbers to be calculated with an accuracy of up to 38 digits. Thus, if your original data types were NUMERIC(18, x)
, then after these operations they are expanded to NUMERIC(38, x)
. But pdo_firebird does not know anything about the new data types.
Try running the following example:
Script index_n.php
namespace MyApp;
use PDO;
use PDOException;
use PDOStatement;
const TS_SQL = <<<'SQL'
SELECT
SUM(SALARY) AS SUM_SALARY,
CAST(SUM(SALARY) AS NUMERIC(18, 2)) AS SUM_SALARY_2
FROM EMPLOYEE
SQL;
try {
echo 'PHP version: ' . phpversion() . "\n";
$dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
$sth = $dbh->prepare(TS_SQL);
$sth->execute();
$row = $sth->fetch(PDO::FETCH_ASSOC);
echo "Result: \n";
var_dump($row);
echo "\n";
$sth->closeCursor();
}
catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . "\n";
}
The result will be the following:
PHP version: 8.3.11
Result:
array(2) {
["SUM_SALARY"]=>
string(4) "0.01"
["SUM_SALARY_2"]=>
string(11) "16203468.02"
}
In this case, instead of null
, an incomprehensible number is displayed, which is even worse. It should be noted that the SUM_SALARY_2
field, which was artificially converted to the NUMERIC(18, 2)
type, the result is correct.
As mentioned earlier, the DECFLOAT
type itself will not appear in your database, but if it is still required, the same sad picture will occur.
Script index_df.php
namespace MyApp;
use PDO;
use PDOException;
use PDOStatement;
const TS_SQL = <<<'SQL'
SELECT
QUANTIZE(12354.678, 123.54) AS DF,
CAST(QUANTIZE(12354.678, 123.54) AS DOUBLE PRECISION) AS D
FROM RDB$DATABASE
SQL;
try {
echo 'PHP version: ' . phpversion() . "\n";
$dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
$sth = $dbh->prepare(TS_SQL);
$sth->execute();
$row = $sth->fetch(PDO::FETCH_ASSOC);
echo "Result: \n";
var_dump($row);
echo "\n";
$sth->closeCursor();
}
catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . "\n";
}
The result will be the following:
PHP version: 8.3.11
Result:
array(2) {
["DF"]=>
NULL
["D"]=>
string(8) "12354.68"
}
What to do in this case? In fact, the Firebird 4.0 developers were well aware that the new types would not appear in the drivers immediately, and therefore made sure that this problem could be solved in one of the following ways:
-
set the DataTypeCompatibility
parameter to '3.0' in firebird.conf
or database.conf
;
-
set the binding of the new data types to one of those supported by the driver using the SET BIND OF
operator;
-
set the binding of the new data types to one of those supported by the driver using the isc_dpb_set_bind
tag.
The pdo_firebird driver does not allow you to construct a connection parameter buffer yourself, so the third option is not suitable. Let’s consider the remaining two.
DataTypeCompatibility parameter
The essence of this parameter is simple, it allows you to set the binding of new data types to the data types that existed in the specified version of Firebird and are closest in properties.
To date, it can take two values "2.5" and "3.0". All requests on the server will work with native data types and only when transmitting data to the client will the following transformations occur:
Parameter value |
Native type |
Legacy type |
2.5
|
BOOLEAN
|
CHAR(5)
|
2.5 or 3.0
|
DECFLOAT
|
DOUBLE PRECISION
|
2.5 or 3.0
|
INT128
|
BIGINT
|
2.5 or 3.0
|
TIME WITH TIME ZONE
|
TIME WITHOUT TIME ZONE
|
2.5 or 3.0
|
TIMESTAMP WITH TIME ZONE
|
TIMESTAMP WITHOUT TIME ZONE
|
Let’s try setting DataTypeCompatibility = 3.0
and see the results of executing our scripts.
Results of executing the first script index_ts.php
:
PHP version: 8.3.11
Result:
array(4) {
["TS_TZ"]=>
string(19) "2024-09-11 16:51:24"
["TS"]=>
string(19) "2024-09-11 16:51:24"
["T_TZ"]=>
string(8) "16:51:24"
["T"]=>
string(8) "16:51:24"
}
As you can see, the result is no different for types with and without time zones.
Results of executing the second script index_n.php
:
PHP version: 8.3.11
Result:
array(2) {
["SUM_SALARY"]=>
string(11) "16203468.02"
["SUM_SALARY_2"]=>
string(11) "16203468.02"
}
Here the sum is displayed correctly.
Results of the third script index_df.php
:
PHP version: 8.3.11
Result:
array(2) {
["DF"]=>
string(8) "12354.68"
["D"]=>
string(8) "12354.68"
}
This solution is good because it is the easiest to make your old projects work correctly without any code changes, but it has significant drawbacks:
What if tomorrow you still need information about the time zone? What if the amount exceeds the capacity of NUMERIC(18, x)
? This problem can be solved using the SQL statement SET BIND OF
.
Let’s remove DataTypeCompatibility = 3.0
from the configuration file and look at the second solution.
Using the SET BIND OF statement
The syntax of the SET BIND OF
statement is as follows:
The syntax of the SET BIND OF
statement
SET BIND
OF { | TIME ZONE}
TO { | LEGACY | EXTENDED | NATIVE }
Table 1. Parameters of the SET BIND OF
statement
Parameter |
Description |
type-from
|
The data type for which the conversion rule is specified.
|
type-to
|
The data type to convert to.
|
This statements allows you to specify rules for describing types returned to the client in a non-standard way — the type-from type is automatically converted to the type-to type.
If an incomplete type definition is used (e.g. CHAR
instead of CHAR(n)
) on the left side of a SET BIND OF
cast, the conversion will be performed for all CHAR
columns, not just CHAR(1)
.
The special incomplete type TIME ZONE
stands for all types, namely {TIME | TIMESTAMP} WITH TIME ZONE
.
When an incomplete type definition is used on the right side of the statement (the TO
part), the server will automatically determine the missing details of that type based on the source column.
Changing the binding of any NUMERIC
and DECIMAL
type does not affect the corresponding underlying integer type. In contrast, changing the binding of an integer data type also affects the corresponding NUMERIC
and DECIMAL
.
The LEGACY
keyword in the TO
part is used when a data type not present in the previous version of Firebird must be represented in a way understandable to older client software (some data loss may occur). The following conversions to LEGACY
types exist:
Table 2. Conversions to legacy types
Native type |
Legacy type |
BOOLEAN
|
CHAR(5)
|
DECFLOAT
|
DOUBLE PRECISION
|
INT128
|
BIGINT
|
TIME WITH TIME ZONE
|
TIME WITHOUT TIME ZONE
|
TIMESTAMP WITH TIME ZONE
|
TIMESTAMP WITHOUT TIME ZONE
|
Using EXTENDED
in the TO
part forces Firebird to use the extended form of the type in the FROM part. Currently, it only works for {TIME | TIMESTAMP} WITH TIME ZONE
— they are forced to EXTENDED {TIME | TIMESTAMP} WITH TIME ZONE
.
Setting NATIVE
means that the type will be used as if there were no previous conversion rules for it.
Let’s see the use of the SET BIND OF
statement in one of our examples. First, we’ll cast all new data types to the corresponding LEGACY types.
Script index_ts_bind_legacy.php
namespace MyApp;
use PDO;
use PDOException;
use PDOStatement;
const COERCE_SQL = <<<'SQL'
EXECUTE BLOCK
AS
BEGIN
SET BIND OF TIME ZONE TO LEGACY;
SET BIND OF INT128 TO LEGACY;
SET BIND OF DECFLOAT TO LEGACY;
END
SQL;
const TS_SQL = <<<'SQL'
SELECT
CURRENT_TIMESTAMP AS TS_TZ,
LOCALTIMESTAMP AS TS,
CURRENT_TIME AS T_TZ,
LOCALTIME AS T
FROM RDB$DATABASE
SQL;
try {
echo 'PHP version: ' . phpversion() . "\n";
$dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
$dbh->exec(COERCE_SQL);
$sth = $dbh->prepare(TS_SQL);
$sth->execute();
$row = $sth->fetch(PDO::FETCH_ASSOC);
echo "Result: \n";
var_dump($row);
echo "\n";
$sth->closeCursor();
}
catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . "\n";
}
The result will be the following:
PHP version: 8.3.11
Result:
array(4) {
["TS_TZ"]=>
string(19) "2024-09-11 17:26:33"
["TS"]=>
string(19) "2024-09-11 17:26:33"
["T_TZ"]=>
string(8) "17:26:33"
["T"]=>
string(8) "17:26:33"
}
As you can see, the result is the same as when setting DataTypeCompatibility = 3.0
. For the other examples, it will be the same.
But the SET BIND OF
statement is much more powerful. We can convert any data type to any other compatible type. Since PHP does not have native data types to represent Firebird 4.0 data types, it is most logical to output their string representation. Let’s try to do this.
Script index_ts_bind.php
namespace MyApp;
use PDO;
use PDOException;
use PDOStatement;
const COERCE_SQL = <<<'SQL'
EXECUTE BLOCK
AS
BEGIN
SET BIND OF TIME ZONE TO VARCHAR;
SET BIND OF INT128 TO VARCHAR;
SET BIND OF DECFLOAT TO VARCHAR;
END
SQL;
const TS_SQL = <<<'SQL'
SELECT
CURRENT_TIMESTAMP AS TS_TZ,
LOCALTIMESTAMP AS TS,
CURRENT_TIME AS T_TZ,
LOCALTIME AS T
FROM RDB$DATABASE
SQL;
try {
echo 'PHP version: ' . phpversion() . "\n";
$dbh = new PDO('firebird:dbname=inet://localhost/employee;charset=utf8', 'SYSDBA', 'masterkey');
$dbh->exec(COERCE_SQL);
$sth = $dbh->prepare(TS_SQL);
$sth->execute();
$row = $sth->fetch(PDO::FETCH_ASSOC);
echo "Result: \n";
var_dump($row);
echo "\n";
$sth->closeCursor();
}
catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . "\n";
}
The result will be the following:
PHP version: 8.3.11
Result:
array(4) {
["TS_TZ"]=>
string(38) "2024-09-11 17:33:23.9400 Europe/Moscow"
["TS"]=>
string(19) "2024-09-11 17:33:23"
["T_TZ"]=>
string(27) "17:33:23.0000 Europe/Moscow"
["T"]=>
string(8) "17:33:23"
}
Excellent result! Everything is fine for the other two examples as well. It is enough to perform an additional query for binding data types immediately after the connection and you can output new data types without losses. But this method also has disadvantages:
-
the data type binding must be set for each connection, and this is an additional query to Firebird. In addition, if the creation of your database connection is not centralized, you will have to change the code of your application in each of these places.
-
the date and time output format depends on the current locale, so when transferring to another environment, the output format may change.