Hi Bill
I post this back to fw-general list because others might find interest in your
answer.
> Say you do this for an INSERT
> statement, and then you execute the prepared SQL statement 500,000 times
> to load a large set of data. Which set of values should be reported
> when you ask for the SQL string? The last one? The first one? All
> 500,000 sets of values?
Yes, In that case I would like to get all 500,000 sets of values. As I'm
knowing what I'm doing in my application this is not a problem and there won't
ever be more than 20 SQL statements per action. Also it's a very small amount
of INSERT/UPDATE/DELETE statements and I definitely want to get hold of it at
least in the first couple of months for debugging & tracking purposes (I know
this is not best the way of doing it).
I understand the way of passing values to a prepared statement at execution
time. But shouldn't it be the idea of Zend_Db_Profiler, to interact here and
store some meta data, remember the string representation of the SQL statement
and fill it with the values? This wouldn't affect the late binding of
placeholders.
It would be wonderful to have something like:
$db->getProfiler()->setHumanQueriesEnabled(true);
and then get all "human" query profiles with:
$profiler->getQueryProfiles()
Or, if that would make more sense, after enabling the optional query data
reporting:
$profiles = $profiler->getQueryProfiles();
foreach ($profiles as $profile) {
echo $profile->getQuery();
print_r($profile->getQueryData(); // representing all passed data as an
array
}
I would greatly appreciate such an option. I might dig into Zend_Db_Profiler if
I find some time but currently I have no idea how to implement that.
Cheers,
philip
Bill Karwin wrote:
> Philip Iezzi wrote:
>> $profile->getQuery() doesn't give me the true original INSERT
>> statement here. I'm looking for the statement with its values, not the
>> ":variable"'s.
>> Is this somehow possible or is it planned?
>>
>
> Consider for example a scenario in which you prepare a SQL statement and
> then execute it multiple times, passing different values for the
> placeholders on each execution. Say you do this for an INSERT
> statement, and then you execute the prepared SQL statement 500,000 times
> to load a large set of data. Which set of values should be reported
> when you ask for the SQL string? The last one? The first one? All
> 500,000 sets of values?
>
> The SQL statement, as far as the RDBMS knows, includes placeholders, not
> values. The SQL statement is typically compiled into some internal
> representation during the prepare(), and it stops being in a string form
> anymore. When the app passes values to substitute for the placeholders,
> it is at a later time. The string representation of the SQL statement
> is never combined with the values you give for the placeholders --
> instead, the values are inserted into that internal representation in
> the RDBMS engine.
>
> Regards,
> Bill Karwin
>
