[TYPO3-core] RFC #15457: Add support for prepared queries [summary and pending questions]

[Ernesto Baschny [cron IT]]

Xavier Perseguers schrieb am 19.08.2010 09:13:

> I summarize this message because a +1 by testing is still missing (from
> anybody). In addition/in regard to that point, there are a few decisions
> that should be agreed on.
> (...)

>> Remove method bindValues()
> 
> I do not see the downside of having it if we default to PARAM_STR
> instead of the would-not-exist-anymore PARAM_AUTOTYPE. This way, it
> would behave as when providing parameters in method execute(). But I
> find it a bit cleaner than forcing user to pass multiple parameters to
> execute() (which s/he could still do).

Yes, I love it too. But if we default to PARAM_STR, how can we pass a
NULL? Currently possible:

bindValues(array(NULL, 'string', 123));

=> NULL = PARAM_NULL
=> 'string' = PARAM_STRING
=> 123 = PARAM_INT

without "auto" we would get:

=> '' = PARAM_STRING
=> 'string' = PARAM_STRING
=> '123' = PARAM_STRING

I think having bindValues with a documented auto-detection of types
(using the PHP type!!) is very nice and fine. Instead of documenting:

"Data type will be set to t3lib_db_PreparedStatement::PARAM_AUTOTYPE."

We document it as:

"The PHP type of each array value will be used to decide which PARAM_*
type to use (int, string, boolean, null), so make sure your variables
are properly casted, if needed."

Having that in place, I don't see a drawback to use the PHP type as the
type of the value in bindValue() too. So I am still +1 for keeping
PARAM_AUTO.

>> Named markers are only replaced once in PDO.
> 
> The remarks is to allow or not such query:
> 
> $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('uid, title',
> 'pages', 'uid = :id OR pid = :id');
> $statement->bindValue(':id', 43);
> 
> where :id is replaced twice. In PDO this is not the case. Still, I find
> it handy and as such would like to keep it.

I don't see why we wouldn't want that.

>> fetch* functions in PDO default to FETCH_BOTH; is there a reason to do
> something else?
> 
> Have you real use-cases where you rely on having both indexed- and
> named-based keys in your resultset? How is your code using
> $GLOBALS['TYPO3_DB'] currently written to work like that?

Also I don't see why we need indexed keys. I guess this was just made so
in PDO for backwards compatibility reasons, which we don't need, as we
have are adding a new feature. :)

>> In PDO this is not possible to support both named and positionned
> markers as it results in exception SQLSTATE[HY093] being thrown.
> 
> => We have no way in native MySQL to detect that both types are used in
> query. What we could do is see which type is used with first call to
> bindValue() and stick to that type for all subsequent calls...

Difficult to handle right now, I would skip that check and leave it as
it is now.

> (...)

Thanks a lot for your feedback and work on that, Xavier. My +1 still
counts, maybe just adapting the above mentioned documentation.

It would be cool if more people could test it. If we could have this in
4.5alpha1, we will try to get even more people testing it "on the
field", and maybe adding more features (INSERT, DELETE) in futher alpha's.

Cheers,
Ernesto