Doctrine DBAL prepared statement troubles

[AlfredoRamos]

I vote for Doctrine DBAL :+1:

- Prepared statements
- Named parameters are much easier to maintain.
- More fetch modes
- Class mapping

Only positional parameters are supported by all drivers

The Oracle driver does not support positional parameters, only named parameters, that's why the added their own parser.

It's even stated in the docs.

- https://github.com/doctrine/dbal/blob/f ... #L126-L149
- https://www.doctrine-project.org/projec ... statements

[DavidIQ]

In favor of dropping our DBAL for Doctrine's as well. Less code to maintain, load and worry about.

[CHItA]

The Oracle driver does not support positional parameters, only named parameters, that's why the added their own parser.

mysqli doesn't support named params. Hence their drivers don't support both.

[AlfredoRamos]

mysqli doesn't support named params. Hence their drivers don't support both.

:thinking:

Sorry, I don't understand the issue

Doctrine DBAL is built on top of PDO not mysqli, and pdo_mysql supports named parameters on prepared statements.

[JoshyPHP]

I'm not familiar with Doctrine DBAL. Is is possible you're talking about different versions, 2.x vs 3.0?

Also, is there anyone on Earth that runs phpBB on Oracle?

[CHItA]

Doctrine DBAL is built on top of PDO not mysqli, and pdo_mysql supports named parameters on prepared statements.

That is incorrect.

[AlfredoRamos]

That is incorrect.

Indeed, but just the part that Doctrine DBAL was built on top of PDO.

I've been using that for years and just found out that's not true 🤦‍♂️

So, to use named parameters on MySQL it just need to use the pdo_mysql driver.

Code: Select all

<?php

require_once __DIR__ . '/vendor/autoload.php';

$conn = Doctrine\DBAL\DriverManager::getConnection([
	'driver' => 'pdo_mysql',
	'host' => 'localhost',
	'port' => 3306,
	'user' => 'notroot',
	'password' => 'asupersecurepassword'
]);

$sql = 'SELECT ENGINE, TABLE_ROWS, VERSION() FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = :table_name AND TABLE_SCHEMA = :database_name';
$stmt = $conn->prepare($sql);
$stmt->bindValue(':table_name', 'phpbb_posts');
$stmt->bindValue(':database_name', 'dev_phpbb');
$stmt->execute();
$result = $stmt->fetch();

echo '<pre><code>';
var_dump($result);
echo '</code></pre>';

[Marc]

I'm also very much in favor of migrating to Doctrine DBAL and I would also opt for switching to PDO in the process.

phpBB's DBAL does currently work. However, there are existing issues with regards to compatibility. I also remember there being and issue with MSSQL in addition to the Oracle issues mentioned here.

Prepared statements are inherently safer to use and will require less thinking about the types of parameters passed to them. This is also the de-facto standard nowadays.
Then there is also that this will IMHO be easier to use for anyone wanting to get started with database queries in phpBB.

[hubaishan]

How about https://github.com/joomla-framework/database, it think it is easier to migrate to it.

[3Di]

^^ I don't feel the need to change our DBAL to another one (our current one doesn't have any major problems) which, moreover, will remove Oracle in the near future, as called for here. Also doesn't have MSSQL, btw. So no.