mysqli: prepared statements and fetch_array method?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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!


Re: mysqli: prepared statements and fetch_array method?

Quoted text here. Click to load it

 This isn't a direct answer, it's one of those annoying "you're doing X with Y
but have you looked at Z" answers: if overhead was the main issue, have you
compared it with ADOdb? It fares quite well in benchmarks, particularly when
compared with PEAR which seems to do quite badly.

 ADOdb emulates prepared statements for older versions of MySQL, but also has
mysqli support which from a glance at the code does use mysqli_stmt_bind_param,
so is using native prepared statements.

 Can't comment as to whether it would solve the other issues, though.

Andy Hassall :: :: :: disk and FTP usage analysis tool

Site Timeline