[TYPO3-core] RFC #15457: Add support for prepared queries

[Ernesto Baschny [cron IT]]

Jigal van Hemert schrieb am 17.08.2010 11:53:

> On 16-8-2010 22:21, Xavier Perseguers wrote:
>> - We want to provide a PDO-like syntax but we don't aim at having a
>> PDO-compatible implementation. We can provide enhancements and we should
>> not fear having some goodies. We want to learn from Extbase (this is why
>> I added some chaining feature for instance)

> I could be wrong here, but the main reason to provide a PDO-like
> interface would be to take advantage of PDO in the end? This will
> simplify the tasks of DBAL considerably when it comes to communicating
> with the database(s).
> We could build our own prepared-query-interface but this will be a PHP
> construction which will always be slower than PDO. To take advantage of
> the features and the speed of PDO it needs to be as compatible as possible.
> Any time we create an incompatible feature we should ask ourselves if it
> is worth the resources it will cost.

True. But I guess our implementation is very in line with the PDO
feature set already.

> For example, the PARAM_AUTO feature tries to guess the value type. Why
> should we have this. IMO it will cause more problems than it might
> solve. If you try to store a phone number and rely on the default
> PARAM_AUTO it will be put as a numerical value in the query and so it's
> interpreted in e.g. MySQL before storing it in a string type column.
> The PDO default PARAM_STR would store it correctly, whether it's stored
> in a numerical or a string column in the database.
> I'm not sure about how other databases handle this.

In PDO no data_type means casting to a string. If you have a WHERE
clause with "uid='1'" (comparing a integer with a string) it will
probably fail on more strict DB-systems, because uid is an integer.
Correct me if I am wrong Xavier.

And expecting the majority of our users developing extensions use MySQL
backend, one would never suspect that his application is not DBAL
compatible, because of not explicitly telling the DB layer that the
integer is an integer.

So having a shorthand way of pushing integers to the prepared statement
with:

  bindValues(array(1, 'string');

instead of:

  bindValue(1, 1, PARAM_INT);
  bindValue(2, 'string');

I expect that more extensions will be DBAL (and PDO) compatible at the
end. After all, there *are* types in PHP, why shouldn't we rely on them? :)

>> - Removed bindParam() method (borrowed from PDO) because we do not like
>> this way of programming and it hardly make sense in TYPO3. In PDO it is
>> used mainly for getting back parameters. We do not have that in TYPO3.
> 
> For getting back parameters you would use bindColumn(). bindParam() is
> used for binding variables which might change between executing the same
> prepared statement and for binding variables which are supposed to be
> modified by the query (PDO::PARAM_INPUT_OUTPUT).

We can add bindParam() later if there is the need for it or if we at
some time have a PDO DBAL backend which might make speedy use of it.

> The fetching styles PDO::FETCH_CLASS and PDO::FETCH_INTO can also be
> useful to get data from the database into objects.

Also might be added later on without any drawbacks now.

I will post my follow-up suggestion based on Xavier's v3 in a couple of
minutes (v4).

Cheers,
Ernesto