5

Could not find anything on this-- seems like it should be straight forward though.

So the example the Doctrine2 docs give for type conversion on bound parameters looks like this:

$date = new \DateTime("2011-03-05 14:00:21");
$stmt = $conn->prepare("SELECT * FROM articles WHERE publish_date > ?");
$stmt->bindValue(1, $date, "datetime");
$stmt->execute();

What I want to do is specify the type conversion for one of the columns, but there is nothing in the documents or on StackOverflow that I could find. A pseudo-example of what this might look like:

$stmt = $conn -> prepare("SELECT datetime FROM articles WHERE id = 1");
$stmt -> setType(0, "date_type"); // 0 being the column position, "date_type" being the PHP type to convert to

If anybody knows how to do this, (this is SQL not DQL), I would greatly appreciate. Thank you.

rink.attendant.6
  • 44,500
  • 61
  • 101
  • 156
Peter M. Elias
  • 1,204
  • 10
  • 22
  • May I ask how did you resolve the issue? I'm using Silex with plain DBAL adaptor and all fetched columns are returned as strings. Did you find any short solution rather than manual iteration + conversion? – VisioN Mar 06 '15 at 10:18

1 Answers1

7

This is not something that works at DBAL level. If you are using NativeSQL Queries in ORM, you can get that kind of conversion through hydration (see the NativeSQL section in the Doctrine ORM documentation) by using the HYDRATE_ARRAY mode and mapping some of the fetched fields to an entity. The fastest solution (if you don't intend to use ORM) is to iterate over the results and applying the type conversion manually by accessing Doctrine\DBAL\Types\Type::getType($someType)->convertToPhpValue($fetchedValue). It could be a valuable addition to ORM to be able to specify a third parameter stating the fetched type in Doctrine\ORM\Query\ResultSetMapping#addScalarResult.

Ocramius
  • 25,171
  • 7
  • 103
  • 107
  • 1
    Thanks for the clarification. I hate the ResultSetMapping feature. I find it incredibly useless and overbearing to the point of using an ORM. I mainly only use the ORM features for INSERT / UPDATE operations. I believe for simple data retrieval you should keep things as simple as possible. – Peter M. Elias Jul 16 '12 at 00:35
  • Well, the fact is that you need a hydrator in the middle, and a hydrator needs a configuration (in this case the `ResultSetMapping`). This also for TableGateway and ActiveQuery or whatever you want to use :) – Ocramius Jul 18 '12 at 11:45
  • 2
    In my case, I did not want/need the hydration step because all I was trying to do was get a bunch of scalar values that would be properly type converted. My hope was to be able to manually specify the type conversion and bypass the ORM altogether for simple retrieval of scalar values. As far as the RSM feature goes, I realize it's necessity if you want to turn a custom query into an object graph result but I still hate it. – Peter M. Elias Jul 19 '12 at 18:07
  • `convertToPhpValue` requires 2 parameters, second one is platform. One can get it from `$connection->getDatabasePlatform()` – BartBiczBoży Jul 26 '16 at 12:27