MonetDB-PHP Error on parameterized query with timestamp

Question

I'm using MonetDB with MonetDB-PHP library.

I have followed the documentation from: https://github.com/MonetDB/MonetDB-PHP

Everything is working fine, but if I set parameter for timestamp type column I get the following error:

Error from MonetDB: 42000!Wrong type for argument 1 of function call: char, expected timestamp

For example,

this is working:

$result = $connection->Query(<<<EOF
    select
        *
    from
        "cats"
    where
        "name" = ?
        and "weight_kg" > ?
    limit
        10
EOF
, [ "D'artagnan", 5.3 ]);

but this will not work:

$result = $connection->Query(<<<EOF
    select
        *
    from
        "cats"
    where
        "name" = ?
        and "weight_kg" > ?
        and "birthdate" < ?
    limit
        10
EOF
, [ "D'artagnan", 5.3, "2020-02-28 12:37:16" ]);

The error occurs for any column that is of timestamp type and only when passing value as parameter.

What am I doing wrong?

Edit: This is the actual table I'm working with

CREATE TABLE sys.url_data (
    userid varchar(50),
    groupid varchar(50),
    category varchar(50),
    url varchar(1024),
    datetime timestamp
);

"datetime" is the column I'm using in the query

I haven't tried this using the PHP driver, but does it work to use [ "D'artagnan", 5.3, "timestamp '2020-02-28 12:37:16'" ] as the argument? — Jennie, Dec 06 '20 at 19:22
@Jennie Again same error. It always expects timestamp data type, I don't know how to pass timestamp if it detects all string values as a char type. I also tried sending integer just to see what will happen and got similar error: Error from MonetDB: 42000!Wrong type for argument 1 of function call: int, expected timestamp — Dositej, Dec 08 '20 at 10:23
Reproduced it. The prepared statement execution `EXECUTE 11('2020-02-28 12:37:16')` fails with the above error. This will probably require a library change. Look back tomorrow morning. — Crouching Kitten, Dec 08 '20 at 11:02

score 0 · Accepted Answer · answered Dec 08 '20 at 19:29

A fix has been deployed. Update to version 1.1.3. The timestamp values have to be passed as DateTime objects.

With your example table that would look like:

$dt = DateTime::createFromFormat("Y-m-d H:i:s", "2020-02-28 12:37:16");

$result = $connection->Query(<<<EOF
    select
        *
    from
        sys.url_data
    where
        datetime > ?
EOF
, [ $dt ]);

To use the microseconds too:

$dt = DateTime::createFromFormat("Y-m-d H:i:s.u", "2020-02-28 12:37:16.123456");

Also updated the documentation on prepared statements:

https://github.com/MonetDB/MonetDB-PHP#example-3-parameterized-query-with-prepared-statement

MonetDB-PHP Error on parameterized query with timestamp

1 Answers1