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

[Xavier Perseguers]

Hi,

>> 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."

Done locally.

> 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.

OK.

>>> 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.

Great.

>>> 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. :)

This is what I think too.

>>> 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.

OK.

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

Good. As such a new version does not need to be published here. 
Documentation change will be made upon commit.

> 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.

Yes. If anybody is willing to test it to give the missing +1 by testing, 
I still may commit it today, including the DBAL part and I'll take care 
of providing a new version of DBAL as part of Core before the alpha1 
release.

Xavier