Where to specify data type when binding value to column?

[designermonkey]

I have a column BINARY(16) and I need to insert data. There doesn't seem to be any way for me to set the type to PDO::PARAM_LOB.

Have I missed something somewhere?

[harikt]

If I am correct there is no binding you can apply here. Either you do it on PDO or use Aura.Sql.

What you can do is retrieve the query statement and use PDO, and use type according to what you need or Aura.Sql can make use of the Parser to get the proper bindings.

Let me know if I am wrong.

I have not converted string to binary here.

$ cat sql.php 

<?php
require __DIR__ . '/vendor/autoload.php';

use Aura\SqlQuery\QueryFactory;

$queryFactory = new QueryFactory('mysql');

$insert = $queryFactory->newInsert();

$bar = "Binary";

$insert
    ->into('test')
    ->cols([
        'bar',
        'baz',
    ])
    ->bindValue('bar', $bar)
    ->bindValues([
        'baz' => 'foo',
	]);
	

use Aura\Sql\ExtendedPdo;

$pdo = new ExtendedPdo(
	'mysql:host=localhost;dbname=aura',
	'root',
	'password',
	[],
	[]
);

$sth = $pdo->prepare($insert->getStatement());
$sth->execute($insert->getBindValues());


$sth = $pdo->prepare("SELECT * FROM test");
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_ASSOC);

var_dump($result);

Result ::

php sql.php
array(2) {
  [0]=>
  array(3) {
    ["id"]=>
    string(1) "8"
    ["bar"]=>
    string(100) "Binary"
    ["baz"]=>
    string(3) "foo"
  }
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "9"
    ["bar"]=>
    string(100) "Binary"
    ["baz"]=>
    string(3) "foo"
  }
}

Table ::

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bar` binary(100) NOT NULL COMMENT 'Binary field',
  `baz` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[designermonkey]

I'm storing a binary form of a uuid to make it useable as a unique key without causing huge indexing issues, so the data has to go into the db as binary. Yes, you can pass a string into a binary field, but it is just stored as the string, not binary.

I ended up binding all the binary values using PDO as you have also suggested, but it feels quite wrong to build the query using Aura, then bind the values separately; I may as well just write plain SQL in this case :(

[designermonkey]

Also, I have learned that passing bind values to execute only uses PDO::PARAM_STR anyway, so I would have to bind values separately.

[harikt]

Hey @designermonkey ,

SqlQuery is only a builder, it doesn't know how the internal works for PDO. So I think that is out of scope for the library. If this was also having a way to execute via PDO then I agree with the same.

So the binding goes to either Aura.Sql or PDO itself.

[designermonkey]

Cool, thanks @harikt.