Timestamp parameters with PDO

[Matthias Hanft]

Hi,

since PHP 7.4 won't contain the ibase_... functions any more,
I'm converting all my database stuff to PDO.

It's not very difficult (nearly 1:1 rewriting), with one
exception: TIMESTAMPs as query parameters.

Retrieving the reocrd count within a certain TIMESTAMP period,
with ibase_..., I did the following:

--- cut here ---

public function GetCountByAccount($aKunde, $aVon, $aBis) {
$stmt='select count(*) as ANZAHL from HTTP '.
'where KUNDE=? and DATETIME>=? and DATETIME<?;';
$trans=ibase_trans(IBASE_DEFAULT, $this->IBDatabaseWddx);
$prep=ibase_prepare($trans, $stmt);
$res=ibase_execute($prep, $aKunde, $aVon, $aBis);
if ($row=ibase_fetch_assoc($res)) // was gefunden
$myResult=intval($row['ANZAHL']);
else // nix gefunden
$myResult=0;
ibase_free_result($res);
ibase_free_query($prep);
ibase_commit($trans);
return $myResult;
} // GetCountByAccount

--- cut here ---

where $aVon and $aBis are Unix timestamps which I had constructed
by PHP's "mktime". Works well.

Now, with PDO, I guess it should read something like

--- cut here ---

public function GetCountByAccount($aKunde, $aVon, $aBis) {
$stmt='select count(*) as ANZAHL from HTTP '.
'where KUNDE=? and DATETIME>=? and DATETIME<?;';
$this->IBDatabaseWddx->beginTransaction();
$prep=$this->IBDatabaseWddx->prepare($stmt);
$res=$prep->execute(array($aKunde, $aVon, $aBis));
if ($row=$prep->fetch(PDO::FETCH_ASSOC)) // was gefunden
$myResult=intval($row['ANZAHL']);
else // nix gefunden
$myResult=0;
$prep->closeCursor();
$prep=NULL;
$this->IBDatabaseWddx->commit();
return $myResult;
} // GetCountByAccount

--- cut here ---

but no records are found.

Investigating further I found that I can *get* TIMESTAMPs from
the database by

$myPDO->setAttribute(PDO::FB_ATTR_TIMESTAMP_FORMAT, '%s')); // as Unix Time - though it's a number in a string

or

$myPDO->setAttribute(PDO::FB_ATTR_TIMESTAMP_FORMAT, '%Y-%m-%d %H:%M:%S')); // as string like '2021-07-06 17:56:34'

but whichever I choose, still no records are found. I have also
tried bindValue(2, $aVon, PDO::PARAM_INT) and date(...), PARAM_STR
and much more - but still no records found.

How can I use TIMESTAMP parameters in a query (preferably without
modifying the SQL query itself)?

Thanks,

-Matt

[Matthias Hanft]

Matthias Hanft wrote:
>
> How can I use TIMESTAMP parameters in a query (preferably without
> modifying the SQL query itself)?

Ok, after much more tries (and googling), I found that this works:

$stmt='select count(*) as ANZAHL from HTTP '.

'where KUNDE=? and datediff(second, timestamp \'1970-01-01 01:00:00\', datetime)>=? '.
'and datediff(second, timestamp \'1970-01-01 01:00:00\', datetime)<?;';

(rest as above). But it's f***ing slow, and is this the way to go at all?
(and why did the ibase_... functions do all this by themselves?)

-Matt

[Emil Totev]

You may have missed this - the php_interbase driver is still alive and well, even if not in standard PHP distributions. Check https://github.com/FirebirdSQL/php-firebird, there are binaries for php 7.4 and 8.0, they even support the 3.0 boolean data type and maybe more. I wouldn't switch to PDO yet.

[Matthias Hanft]

Emil Totev wrote:
> You may have missed this - the php_interbase driver is still alive and well, even if not in standard PHP distributions. Check https://github.com/FirebirdSQL/php-firebird, there are binaries for php
> 7.4 and 8.0, they even support the 3.0 boolean data type and maybe more. I wouldn't switch to PDO yet.

Thanks for the hint - I had already found that link at
http://firebirdsql.org/en/php-driver/

But apart from the fact that I'd need a 32-bit Linux driver, I've made
bad experience with self-installed drivers (and other software) - you
often get problems when there are system updates, and some self-
installed driver/add-on/extension/etc. won't work any more (I had
this problem, among others, with some PDF software).

So I'd like to keep to the original package manager as long as possible.

I guess timing can be improved by creating some index... will try.

-Matt

[Milan Dvorak]

Maybe to late, but...

Try this: select .... cast (datum as varchar(25)) as datum.

Dne úterý 6. července 2021 v 19:33:21 UTC+2 uživatel goo...@hanft.de napsal: