BYTEA retrieval via Fat Free Framework SQL data mapper

Question

I am trying to retrieve stored binary data (bytea) from a PostgreSQL database using the \DB\SQL\Mapper in the Fat Free Framework.

However for some reason I can't seem to get anything other than a "Resource ID" string. For example.

    // Establish database communication
    $this->db = new \DB\SQL(
        $f3->get("database.type") .
        ":host=" . $f3->get("database.host") .
        ";port=" . $f3->get("database.port") .
        ";dbname=" . $f3->get("database.name"),
        $f3->get("database.user"),
        $f3->get("database.password"),
        array(
            \PDO::ATTR_PERSISTENT => TRUE,
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
        )
    );

    // Firmware Object-Relational-Mapper
    $this->firmware = new \DB\SQL\Mapper(
        $this->db,
        "firmware"
    );

    // Selection parameters 
    $this->select = array(
          "id=:id",
          ":id" => array(
              $f3->get("PARAMS.id"),
              \PDO::PARAM_INT
          )
    );

    // Map to record
    $this->firmware->load($this->select);

    // Error: expects parameter 1 to be resource, string given
    // $this->firmware->binary is "Resource id #23"
    fpassthru($this->firmware->binary); 

However if I execute a prepared statement against the \DB\SQL PDO class I can retrieve the binary data without issue. i.e.

    $record = $this->db->exec(
        'SELECT "binary" FROM "firmware" WHERE id=?',
         $f3->get("PARAMS.id")
    );

    fpassthru($record[0]['binary']); //spits out the binary data

So my question is; is there a way to retrieve the binary data from the table whilst still using the ORM in Fat Free?

Answer 1

I think this is because of the type conversion, which is applied here. The ORM was probably not able to find the correct pdo type for your binary field in PostgreSQL.

You can try to overwrite it like this:

$this->firmware = new \DB\SQL\Mapper(
   $this->db,
   "firmware"
);       
$schema = $this->firmware->schema();
$schema['binary']['pdo_type'] = \PDO::PARAM_LOB;
$this->firmware->schema($schema);

It could be that you also need to adjust the db->value method and add default case which just returns what went into, so the value is passed through return $val.