Everything

php

PDO Parameter types // PDO::PARAM_AUTO

php.internals

Dorin Marcoci

9 years ago

Hello Everyone!

Currently PDOStatement::bindValue is declared as

public bool PDOStatement::bindValue ( mixed $parameter , mixed $value [, int
$data_type = PDO::PARAM_STR ] )

where third parameter is data type, string type as default.

It may be useful in terms of providing meta info and handle it accordingly,
like quote, cast or do some additional stuff.

But there are DBMSes where this "feature" is more a burden than a helper.

It's because after PDO::prepare, driver and server already knows all
statement parameters and expected types.

Yes, it can be "forced", saying to pass for a integer parameter a string
value, server will convert it to required type.

It is convenient in most cases to pass them as strings, for example by
simply assign all params in a cycle before execute.

But when implementing FB Boolean data type parameters and doing ->bindValue
('bool_param', false) I get an empty string internally.

Yes, true ZVAL is converted as string as '1', but false as an empty string,
not so consistent here ;)

That's why I think, it will be great to have a special type like
PDO::PARAM_AUTO and a config flag to set it as default instead of PARAM_STR.

Any thoughts?

-

Dorin

Matteo Beccati

9 years ago

Hi Dorin,

On 15/05/2017 18:22, Dorin Marcoci wrote:

> Currently PDOStatement::bindValue is declared as
>
> public bool PDOStatement::bindValue ( mixed $parameter , mixed $value [, int
> $data_type = PDO::PARAM_STR ] )
>
> where third parameter is data type, string type as default.
>
> It may be useful in terms of providing meta info and handle it accordingly,
> like quote, cast or do some additional stuff.
>
> But there are DBMSes where this "feature" is more a burden than a helper.

Please elaborate.

> It's because after PDO::prepare, driver and server already knows all
> statement parameters and expected types.
>
> Yes, it can be "forced", saying to pass for a integer parameter a string
> value, server will convert it to required type.
>
> It is convenient in most cases to pass them as strings, for example by
> simply assign all params in a cycle before execute.

The thing is: the server (with non-emulated prepares) knows the data
type it's expecting. PHP has no knowledge of it, so you are expected to
do so if the default (string) is not ok.

> But when implementing FB Boolean data type parameters and doing ->bindValue
> ('bool_param', false) I get an empty string internally.
>
> Yes, true ZVAL is converted as string as '1', but false as an empty string,
> not so consistent here ;)

That's not true.

$p = new PDO("pgsql:dbname=postgres");
$p->prepare("SELECT ?::bool");
$s->bindValue(1, false);
$s->execute();
var_dump($s->fetchColumn());'

outputs:

bool(false)

What are you referring to?

> That's why I think, it will be great to have a special type like
> PDO::PARAM_AUTO and a config flag to set it as default instead of PARAM_STR.

Config flags are evil as it's one more thing you'd need to be aware of
and could change depending on the environment you run your script or
library on.


Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Dorin Marcoci

9 years ago

Hello Matteo,

-----Original Message-----
From: Matteo Beccati [mailto:php@beccati.com]
Sent: Thursday, May 18, 2017 8:13 AM

>> But when implementing FB Boolean data type parameters and doing
>> ->bindValue ('bool_param', false) I get an empty string internally.
>>
>> Yes, true ZVAL is converted as string as '1', but false as an empty
>> string, not so consistent here ;)

>That's not true.

>$p = new PDO("pgsql:dbname=postgres");
>$p->prepare("SELECT ?::bool");
>$s->bindValue(1, false);
>$s->execute();
>var_dump($s->fetchColumn());'

>outputs:
>bool(false)

>What are you referring to?

I wrote "internally", meaning in C driver code, not in PHP.
When a bool ZVAL variable is converted to a string ZVAL.
Your example is irrelevant.

>> That's why I think, it will be great to have a special type like
>> PDO::PARAM_AUTO and a config flag to set it as default instead of PARAM_STR.

>Config flags are evil as it's one more thing you'd need to be aware of and could change depending on the environment you run your script or library on.

Maybe I was not so explicit, I meant an option flag in PDO constructor in order to not break existing code, not a hardcoded global config in php.ini
Here: public PDO::__construct ( string $dsn [, string $username [, string $password [, array $options ]]] )
something like PDO::PARAM_AUTO => true or "server param data types", we should think for a good name.

But I'm with all hands up for making PDO::PARAM_AUTO default in bindValue, just it may require some small adjustments in all PDO drivers.
If server type params are not supported, treat them as strings. So it should work.


-
D.

Matteo Beccati

9 years ago

On 18/05/2017 10:06, Dorin Marcoci wrote:

>> What are you referring to?
>
> I wrote "internally", meaning in C driver code, not in PHP.
> When a bool ZVAL variable is converted to a string ZVAL.
> Your example is irrelevant.

Ok, I think I got it. But the example shows that the pgsql driver is
working, so maybe you could have a look at how it works.

e.g.

https://github.com/php/php-src/blob/master/ext/pdo_pgsql/pgsql_statement.c#L382

Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Dorin Marcoci

9 years ago

>>! When a bool ZVAL variable is converted to a string ZVAL.
>> Your example is irrelevant.

>Ok, I think I got it. But the example shows that the pgsql driver is working, so maybe you could have a look at how it works.
>e.g.
>https://github.com/php/php-src/blob/master/ext/pdo_pgsql/pgsql_statement.c#L382

Yup, it is working, I didn’t said it does not work.

I meant the case when a bool is passed as string, like bindValue('boolparam', false);
Here it comes as an empty string, not "0", while true comes as "1".

Matteo Beccati

9 years ago

Hi Dorin,

On 18/05/2017 11:41, Dorin Marcoci wrote:

>>> ! When a bool ZVAL variable is converted to a string ZVAL.
>>> Your example is irrelevant.
>
>> Ok, I think I got it. But the example shows that the pgsql driver is working, so maybe you could have a look at how it works.
>> e.g.
>> https://github.com/php/php-src/blob/master/ext/pdo_pgsql/pgsql_statement.c#L382
>
> Yup, it is working, I didn’t said it does not work.
>
> I meant the case when a bool is passed as string, like bindValue('boolparam', false);
> Here it comes as an empty string, not "0", while true comes as "1".

Please have a look again at my example and the code I linked. How could
it work if what you say is true?

Please note that the code is in the PDO_PARAM_EVT_EXEC_PRE case.

Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Matteo Beccati

9 years ago

Hi Dorin,

On 18/05/2017 12:17, Matteo Beccati wrote:

> Hi Dorin,
>
> On 18/05/2017 11:41, Dorin Marcoci wrote:
>> I meant the case when a bool is passed as string, like bindValue('boolparam', false);
>> Here it comes as an empty string, not "0", while true comes as "1".
>
> Please have a look again at my example and the code I linked. How could
> it work if what you say is true?
>
> Please note that the code is in the PDO_PARAM_EVT_EXEC_PRE case.

I'll try again, maybe it's clearer this way:

Breakpoint 1, pgsql_stmt_param_hook (stmt=0x7ffff1681000,
param=0x7ffff16801e0, event_type=PDO_PARAM_EVT_EXEC_PRE)
at .../ext/pdo_pgsql/pgsql_statement.c:383
383
S->param_values[param->paramno] = Z_TYPE_P(parameter) == IS_TRUE ? "t" :
"f";
(gdb) printzv parameter
[0x7ffff16801e0] bool: false

It's not an empty string.


Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Matteo Beccati

9 years ago

On 19/05/2017 14:35, Matteo Beccati wrote:

> [0x7ffff16801e0] bool: false
>
> It's not an empty string.

My mistake, I ran the wrong example test script through the debugger. It
is an empty string, sorry.


Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Dorin Marcoci

9 years ago

> > It's not an empty string.
>
> My mistake, I ran the wrong example test script through the debugger. It is
> an empty string, sorry.

That's why I'm trying to preach this :) really annoying to guess/detect and put once again param type while driver know it!