I'm rewriting a database interface that our company currently has.
Currently it's using the Pear::DB interface, but we found that that was
introducing a bit too much overhead. I'm rewriting the interface to use
mysqli. Most of what the interface does is to simplify getting results in
the form of arrays (ordered and associative). Most of the code using the
interface used sql queries with placeholders and parameters. For that
reason I'd like to use prepared statements in mysqli, but I'm running into
a few problems.
The mysql_result object has a fetch_array method that I'd like to use, but
as far as I can tell it doesn't appear that prepared statements (the
mysqli_stmt class) even uses the mysql_result class. It appears that,
with prepared statements, you must bind variables to the mysqli_stmt
object with bind_result, which causes the statement to set those variables
when you use it's fetch() method. The mysqli_result object can only be
obtained (as far as I can see) using the mysqli_query method with an
unprepared query. (Please let me know if I'm mistaken on this.)
That's were I ran into a more significant problem. When variables are
bound to the statements results, mysqli attempts to convert them to the
appropriate php data types, and I can't find any way to prevent it from
doing so. This makes it impossible to select large integers (larger than
the php integer size) into string variables. mysqli always tries to
convert them to integers and this of course wraps. The existing code
using my interface simply has to have the ability to do this...changing
the underlying SQL to cast columns or the like isn't an option.
Interestingly, the mysqli_result fetch_array method appears to select
everything as strings, much like the old mysql interface. I would prefer
that, as I have code in my interface to do data conversions if and when I
need to. But again, I see no way of using this with prepared statements.
Unless I'm mistaken on this I may have to scrap using prepared statements
altogether and emulate prepared statements by parsing and replacing the
queries placeholders myself (much like pear does) which I'd rather avoid.
Thanks in advance for any suggestions!
Tom
Currently it's using the Pear::DB interface, but we found that that was
introducing a bit too much overhead. I'm rewriting the interface to use
mysqli. Most of what the interface does is to simplify getting results in
the form of arrays (ordered and associative). Most of the code using the
interface used sql queries with placeholders and parameters. For that
reason I'd like to use prepared statements in mysqli, but I'm running into
a few problems.
The mysql_result object has a fetch_array method that I'd like to use, but
as far as I can tell it doesn't appear that prepared statements (the
mysqli_stmt class) even uses the mysql_result class. It appears that,
with prepared statements, you must bind variables to the mysqli_stmt
object with bind_result, which causes the statement to set those variables
when you use it's fetch() method. The mysqli_result object can only be
obtained (as far as I can see) using the mysqli_query method with an
unprepared query. (Please let me know if I'm mistaken on this.)
That's were I ran into a more significant problem. When variables are
bound to the statements results, mysqli attempts to convert them to the
appropriate php data types, and I can't find any way to prevent it from
doing so. This makes it impossible to select large integers (larger than
the php integer size) into string variables. mysqli always tries to
convert them to integers and this of course wraps. The existing code
using my interface simply has to have the ability to do this...changing
the underlying SQL to cast columns or the like isn't an option.
Interestingly, the mysqli_result fetch_array method appears to select
everything as strings, much like the old mysql interface. I would prefer
that, as I have code in my interface to do data conversions if and when I
need to. But again, I see no way of using this with prepared statements.
Unless I'm mistaken on this I may have to scrap using prepared statements
altogether and emulate prepared statements by parsing and replacing the
queries placeholders myself (much like pear does) which I'd rather avoid.
Thanks in advance for any suggestions!
Tom
Comment