ID: 38334
User updated by: maximkh at yahoo dot com
Reported By: maximkh at yahoo dot com
Status: Open
-Bug Type: Feature/Change Request
+Bug Type: PDO related
Operating System: Linux
PHP Version: 5.1.4
New Comment:
.
Previous Comments:
------------------------------------------------------------------------
[2006-08-07 20:54:02] maximkh at yahoo dot com
Looks like it's back in "Feature/Change Request" section... Fine,
though I don't think supporting proper data-types is an extra
"feature".
Ok moving on, I figured out a way to get passed both problems, but it
basically eliminates all advantages of PDO, and actually makes things a
lot worse. As stated above, if you manually escape the data and don't
use variable binding, the data is inserted as proper data types.
When performing a select on that data, PDO_SQLITE will always convert
it into a string, however, PDOStatement::getColumnMeta does return the
correct native_type field (null, double, string, or integer). This
means that if you want data types to be preserved when selecting, you
need to loop over every row using PDOStatement::fetch(), use the
getColumnMeta for every column in that row, do the conversion, and
store the result in a PHP array (2-dimensional). As you can imagine,
that is one slow process.
That sounds like an absolutely terrible solution to me, which is why I
again ask for at least some sort of a response from one of the
developers on this issue (not that I don't enjoy talking to myself).
------------------------------------------------------------------------
[2006-08-05 16:35:25] maximkh at yahoo dot com
Switching this bug from "Feature/Change Request" to "SQLite related"
issues. This seems to be a really big oversight on the developer's part
since it eliminates all data-type support from SQLite.
------------------------------------------------------------------------
[2006-08-05 16:29:12] maximkh at yahoo dot com
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.
------------------------------------------------------------------------
[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
