Binary type for PDO

[NattyNarwhal]

Description

As part of supporting multiple users, I've found that they have difficulty with dealing with binary columns with PDO drivers.

• One was using PDO_DBLIB; I've filed GH-10312 and its associated PR. (Details in there, but tl;dr binary data needs to be hex-encoded in a TDS stream to avoid damage as TDS doesn't have protocol level prepared statements.)
• One was using PDO_ODBC, with an ODBC driver that had different semantics with SQL_C_CHAR shaped bindings on binary columns versus binding it as a binary on the C side. (Specifically, it would hex-encode the column. Useful perhaps, but unexpected for the user.)
  • As a workaround, the user ended up using procedural ODBC, which has odbc_result work by accident by binding it as a binary on the C side, even though I don;'t think the procedural ODBC binding interface can represent that.

Notably, these aren't necessarily LOBs - they can be (relatively) small, users don't (want to) deal with them in terms of streams on the PHP side (as PHP strings can represent binary data just fine), etc. Treating them as strings isn't right, because they can be subject to unwanted and inappropriate encoding conversions or an inefficient representation over the wire.

Proposal

Extend the PDO constants to have a PARAM_BINARY binding type, and use it in drivers. For things like ODBC (where I'm most familiar), this would map to an SQL_C_BINARY binding type, with the equivalent in other drivers when possible.

User code could look something like:

<?php

$SQL1 = "select id, my_blob from calvin.blob_stream";

$connstring = 'odbc:*LOCAL';
$dbconn = new PDO($connstring);
$stmt = $dbconn->prepare($SQL1);
$stmt->execute();
$lob = "";
$stmt->bindColumn(2, $lob, PDO::PARAM_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);

echo "PDO: $lob\n";

Basically looks like using PARAM_STR, and would maintain a string interface unlike PARAM_LOB.

Possible problems/alternatives

I'm not committed to the proposed interface; I just know there is a problem dealing with binaries. If there's another way to solve this, I'd like to hear it.

• Making it a flag: PARAM_STR_NATL or PARAM_STR_INTL could be set on a PARAM_STR, so why not PARAM_STR_BINARY?
• Driver support: Binary columns are common on other databases (ODBC standardized it), but not necessarily always. If they do, they might not have the same problems. I don't know how the semantics would map in the case it lacks support - treat it as a string?
  • PARAM_STMT has even sketchier support (specifically, it's in none of them) and is also a constant, so there is precedent for types not supported by all drivers.
• Overlap with PARAM_LOB: The intent of a LOB is somewhat similar, but with different semantics. Also note a LOB doesn't necessarily have to be binary data (blob), it can be a large string (clob), and (var)binary columns aren't blobs anyways. GH-10343 overrides the semantics of PARAM_LOB for binary non-stream content, but I'm not sure if this is appropriate.
• Making it work with PARAM_STR: For the TDS case, the driver could automatically detect binary data, but the semantics issue exposed by i.e. ODBC remains.
• RFC: This might be a big enough change to merit an RFC. Nothing would be changed/removed, just added, but it is public PDO surface.

[acerbis]

This feature is extremely important. I've seen this behaviour in production, where I just wanted to fetch a BLOB on and IBMi via PDO/ODBC and an hex string was returned (sometimes even with a weird truncation behaviour). Resorted to using straight ODBC interface to more idiomatically fetch the binary.