ID: 38334
User updated by: maximkh at yahoo dot com
Reported By: maximkh at yahoo dot com
Status: Open
Bug Type: Feature/Change Request
Operating System: Linux
PHP Version: 5.1.4
New Comment:
A small correction to the code above... The request has more to do with
the bindParam and bindValue methods. If you use PDO::query like I do
above, numbers 123 and 123.45 actually DO get stored in the native data
type (INT and REAL in this case), but when retrieved again, they are
converted into strings. I've confirmed this using a HEX editor by
viewing the database file.
So that's two separate issues there. First, is that if you use
bindParam and bindValue to insert data, that data will always be
converted into text prior to being sent to the database. Second, if you
DON'T use bindParam or bindValue and instead use the traditional method
of manually escaping data and concatenating it directly with the SQL
query, in this case the values will be stored using native data types,
but will not be retrieved as such when a select is performed.
Previous Comments:
------------------------------------------------------------------------
[2006-08-04 19:34:22] maximkh at yahoo dot com
Description:
------------
Hello, I was going through the source for PDO_SQLITE extension, and it
looks to me as though the only supported data types are text and null.
The native types for SQLite such as INTEGER, REAL, and BLOB are all
converted into strings, which not only take up more space, but also
take away one of the advantages of SQLite, namely being able to store
different data types in the same column. Given that SQLite doesnt even
have column types, this kind of implementation effectively takes away
all data-type support altogether.
We can use various is_* functions and convert the data ourselves, but
that only solves half the problem. If the data is stored in the
database as strings, it still takes up a lot more space than it needs
to (with ints, and reals). In addition, if BLOB data is converted into
utf-8 strings or similar, storing binary data might prove to be a
problem, though I havent looked into this one yet.
Would like to request added support for native SQLite data-types if
possible.
Reproduce code:
---------------
@unlink('example.db');
$db = new PDO('sqlite:example.db');
$db->query('CREATE TABLE my_table (my_column BLOB NULL)');
$db->query('INSERT INTO my_table VALUES (\'text\')');
$db->query('INSERT INTO my_table VALUES (123)');
$db->query('INSERT INTO my_table VALUES (123.45)');
$db->query('INSERT INTO my_table VALUES (NULL)');
$result = $db->query('SELECT * FROM my_table');
foreach ($result as $row)
var_dump($row['my_column'])."\n";
Expected result:
----------------
string(4) "text"
int(123)
float(123.45)
NULL
Actual result:
--------------
string(4) "text"
string(3) "123"
string(6) "123.45"
NULL
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=38334&edit=1
