Doctrine DBAL prepared statement troubles

General discussion of development ideas and the approaches taken in the 4.x branch of phpBB. The next feature release of phpBB 4 will be 4.0/Triton.
Forum rules
Please do not post support questions regarding installing, updating, or upgrading phpBB 4.x. If you need support for phpBB 3.3.x please visit the 3.3.x Support Forum on phpbb.com.

If you have questions regarding writing extensions please post in Extension Writers Discussion to receive proper guidance from our staff and community.
User avatar
AbaddonOrmuz
Registered User
Posts: 10
Joined: Wed Jul 02, 2014 9:44 pm
Location: /dev/null
Contact:

Re: Doctrine DBAL prepared statement troubles

Post by AbaddonOrmuz »

I vote for Doctrine DBAL :+1:

- Prepared statements
- Named parameters are much easier to maintain.
- More fetch modes
- Class mapping
CHItA wrote: Tue Jan 12, 2021 8:19 pm 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
Some of my phpBB extensions:
Image Imgur | :chart_with_upwards_trend: SEO Metadata | Image Markdown | :lock: Auto-lock Topics
:trophy: Check out all my validated extensions :trophy:

:penguin: Arch Linux user :penguin:
User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1872
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: Doctrine DBAL prepared statement troubles

Post by DavidIQ »

In favor of dropping our DBAL for Doctrine's as well. Less code to maintain, load and worry about.
Image
CHItA
Development Team
Development Team
Posts: 169
Joined: Thu Mar 12, 2015 1:43 pm
Location: Budapest, Hungary

Re: Doctrine DBAL prepared statement troubles

Post by CHItA »

AbaddonOrmuz wrote: Wed Jan 13, 2021 7:52 pm 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.
User avatar
AbaddonOrmuz
Registered User
Posts: 10
Joined: Wed Jul 02, 2014 9:44 pm
Location: /dev/null
Contact:

Re: Doctrine DBAL prepared statement troubles

Post by AbaddonOrmuz »

CHItA wrote: Wed Jan 13, 2021 8:31 pm 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.
User avatar
JoshyPHP
Registered User
Posts: 381
Joined: Fri Jul 08, 2011 9:43 pm

Re: Doctrine DBAL prepared statement troubles

Post by 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
Development Team
Development Team
Posts: 169
Joined: Thu Mar 12, 2015 1:43 pm
Location: Budapest, Hungary

Re: Doctrine DBAL prepared statement troubles

Post by CHItA »

AbaddonOrmuz wrote: Wed Jan 13, 2021 9:31 pm Doctrine DBAL is built on top of PDO not mysqli, and pdo_mysql supports named parameters on prepared statements.
That is incorrect.
User avatar
AbaddonOrmuz
Registered User
Posts: 10
Joined: Wed Jul 02, 2014 9:44 pm
Location: /dev/null
Contact:

Re: Doctrine DBAL prepared statement troubles

Post by AbaddonOrmuz »

CHItA wrote: Thu Jan 14, 2021 9:19 am 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>';
Image
Image
Some of my phpBB extensions:
Image Imgur | :chart_with_upwards_trend: SEO Metadata | Image Markdown | :lock: Auto-lock Topics
:trophy: Check out all my validated extensions :trophy:

:penguin: Arch Linux user :penguin:
User avatar
Marc
Development Team Leader
Development Team Leader
Posts: 168
Joined: Thu Sep 09, 2010 11:36 am
Location: Munich, Germany

Re: Doctrine DBAL prepared statement troubles

Post by 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
Registered User
Posts: 16
Joined: Fri Oct 21, 2011 11:29 am

Re: Doctrine DBAL prepared statement troubles

Post by hubaishan »

How about https://github.com/joomla-framework/database, it think it is easier to migrate to it.
User avatar
3Di
Registered User
Posts: 884
Joined: Tue Nov 01, 2005 9:50 pm
Location: Milano 🇮🇹 Frankfurt 🇩🇪
Contact:

Re: Doctrine DBAL prepared statement troubles

Post by 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.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Post Reply