Search code examples
phpblobfirebird

How do I convert from BLOB SUB_TYPE 0 to TEXT in Firebird?

I have a field in my Firebird database table with data type as BLOB SUB_TYPE 0 (default). How can I view the content as text/string using a SELECT query in a PHP webservice SQL query?

I'm trying this:

<?php 
  
$str_conn="firebird:host=localhost;dbname=C:\Temporal\POLICLINICA.GDB;charset=utf8";
     
$dbh = new PDO($str_conn, "SYSDBA", "masterkey");

$res=$dbh->query("SELECT COD_PACIENTE, CONVERT(TRATAMIENTO USING utf8) AS TREAT, SESIONES FROM GES_FISOTERAPIA");

$datos = array();

foreach ($res as $row) {
    $datos[] = $row;
}

echo json_encode($datos);

?>

The column 'Tratamiento' containing the blob type, but return error.

I must read the column in the webservice because I am not authorized to change the format in the database to TEXT or VARCHAR.

Solution

  • A blob sub_type 0 is a blob sub_type binary, and contains binary data. If you are sure it is actually character data, and if the character set used is UTF8, then you can explicitly cast it:

    cast(TRATAMIENTO as blob sub_type text character set utf8)

    If instead you need varchar, you need to do either

    cast(cast(binval as blob sub_type text character set utf8) as varchar(8191))

    or

    cast(cast(binval as varchar(8191) character set binary) as varchar(8191) character set utf8)

    This seems convoluted, but I needed to do this, because it looks direct conversion from binary blob to varchar with character set doesn't work (at least, I got invalid results in Firebird 3 for non-ASCII characters).

    I think that is a bug, and use of cast(binval as varchar(8191) character set utf8) should work (but right now it doesn't).

    Be aware that when converting to varchar this way, the maximum length of the value is limited: 32765 for single byte character sets, and 8191 for (max) 4 byte character sets like utf8.

    You may need to actually use the blob, and explicitly read the blobs.