Hello,
I think your're referring to this (which I've now read for the first
time and basically contains what I'd have expected there):
https://sqlite.org/c3ref/column_blob.html
You fetch the type and then call the appropriate read function. When
calling an "inappropriate" function, the value will be converted
accordingly. PDO seems to not care about the type check and fetch
everything as string for simplicity.
However, I never found this to be an issue, particularly if
you're echoing values back to the web page.
If you don't care about localisation, then yes, go ahead. Not everywhere
is a point a recognised decimal separator. For example where I live.
And yes, PDO is absolutely
the best way to address a SQLite database (or any other for that
matter).
I'd rather rate it as "PDO is the only abstraction layer we have, so it
must be the 'best' way". But it's certainly not "good" like this. Or
sometimes even unusable. I fetch the values I've written and compare
them with new values. A different type will ask for trouble here.
Meanwhile I found out that the SQLite3 classes are very similar,
conversion of my code went very quickly. And they work correctly. So
I'll keep those.
-Yves
-------- Ursprüngliche Nachricht --------
Von: <paulf@xxxxxxxxxxxxxxxxx>
Gesendet: Montag, 3. April 2023, 22:17 MESZ
Betreff: PDO returns all data as strings?!
On Mon, 3 Apr 2023 21:31:29 +0200
"Yves Goergen via php-general" <php-general@xxxxxxxxxxxxx> wrote:
Hello,
I just noticed a weird behaviour of PDO for my SQLite database and
found a hint online. Just wanted to check here.
Is it correct that PDO will return all queried values from the fetch
method as string, no matter the actual data type? I couldn't find
that notice by glancing over the documentation. (Such an unexpected
note should be very well visible.)
If that's the case, can it be fixed immediately (for PHP 7.4 and
later)? Or what alternatives to PDO could I use for SQLite only? I
need prepared statements and parameter binding.
I know how SQLite handles data types, and it includes remembering the
type that was given to it. If PHP/PDO then destroys it, it's of very
limited use.
My code looks like this:
$ps = $this->connection->prepare('select * from table');
$ps->execute();
$record = $ps->fetch(PDO::FETCH_ASSOC);
-Yves
I can't speak to how PDO can be made to return certain types of data.
However, I can say that for the C API of SQLite (which I'm sure PHP
relies upon), the default return type for results is strings. In C, you
must add an additional set of calls to get SQLite data into integers,
floats, etc. That said, with PHP's fluid typing, and its ability to
cast to different types, you can achieve the result you want, albeit
with more tedium.
It's been my experience that PHP/PDO does in fact return strings by
default. However, I never found this to be an issue, particularly if
you're echoing values back to the web page. And yes, PDO is absolutely
the best way to address a SQLite database (or any other for that
matter).
Paul
