phpsqlmonetdb

MonetDB-PHP Error on parameterized query with timestamp

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

Solution

  • 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: