PDO::ATTR_EMULATE_PREPARES no longer properly causing type casting from string to int/bool

[scottfriebel]

I have been using atk4/dsql and just migrated from that to the current version (5.2) of atk4/data today. After making the other necessary syntax changes I'm still running into an issue where the type (casting?) from string to int is no longer working. Any help with what I can change to get back to the properly casted types would be great.

My connection process used to look like this with the /dsql package

$dsn = 'mysql:host=' . $_ENV['DATABASE_HOST'] . ';dbname=' . $_ENV['DATABASE_NAME'] . ';port=' . $_ENV['DATABASE_PORT'] . ';charset=utf8mb4;';

self::$db = Connection::connect(
  $dsn,
  $_ENV['DATABASE_USERNAME'],
  $_ENV['DATABASE_PASSWORD']
);
self::$db->connection()->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

and it now looks like this with the /data package

$dsn = 'pdo_mysql:host=' . $_ENV['DATABASE_HOST'] . ';dbname=' . $_ENV['DATABASE_NAME'] . ';port=' . $_ENV['DATABASE_PORT'] . ';charset=utf8mb4;';

self::$db = Connection::connect(
  $dsn,
  $_ENV['DATABASE_USERNAME'],
  $_ENV['DATABASE_PASSWORD'],
);
self::$db->getConnection()->getNativeConnection()->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

Adding some very simple query such as the following immediately after the connection code (so nothing else could've run) will return the obviously different data

$q = self::$db->dsql()->table('listings');
$q->field('id')->field('title');
$q->where('id', 1);
$res = $q->getRow();
echo '<pre>';
var_dump($res);
exit();

array(2) {
  ["id"]=>
  int(1)
  ["title"]=>
  string(38) "XXXXX"
}

array(2) {
  ["id"]=>
  string(4) "7333"
  ["title"]=>
  string(5) "XXXXX"
}

[mvorisek]

Please reproduce your issue as a failing testcase. Preferably fork the atk4/data, push the testcase into a new git branch and send a link to it here - full CI will be run on your fork.

[scottfriebel]

So I guess I might have made a change to the atk4/dsql package myself and since that change obviously isn't reflected in the atk4/data package, my query results are different. Which means, this isn't an issue with the PDO::ATTR_EMULATE_PREPARES flag..

However, I am curious then about why the castGetValue function only returns null or a string, and not a null/int/float value if that's what's returned by the DB engine itself? Couldn't that function definition look more like this?

if ($v === null) {
  return null;
} elseif (is_bool($v)) {
  return $v ? '1' : '0';
} elseif (is_int($v)) {
  return $v;
} elseif (is_float($v)) {
  return $v;
} elseif (is_double($v)) {
  return $v;
}

return (string) $v;

[mvorisek]

However, I am curious then about why the castGetValue function only returns null or a string, and not a null/int/float value if that's what's returned by the DB engine itself? Couldn't that function definition look more like ...

The reason is not all DB returns int, so we "normalize" the resulting values into string|null solely.

For most select queries you should use Model which is always hard typed, so all select fields will be always casted to the correct type, even object ones like \DateTime.