Forum > Databases
How do I access the full sql+params in a TSQLQuery?
rvk:
--- Quote from: MarkMLl on May 20, 2022, 09:47:37 pm ---There's plenty of bugs in Format(). Can you guarantee the reliability of that code?
--- End quote ---
I don't see where this could go wrong. Please point to where it could be a problem.
--- Quote from: MarkMLl on May 20, 2022, 09:47:37 pm ---I don't like bloating code with hooks and callbacks, but I think there's justification here for adding a debugging facility at the Sqldb level... something that works identically with all the backends.
--- End quote ---
Sure... some form of TSQLQuery.GetExpandedSQL where the executed SQL is retrieved from the server would be nice and preferable. But does that always work? How does a server handle binary fields for example? Can all SQL statements be represented by a SQL string with all values?
But that's why I also asked for what it would be used. If it's just to view some SQL during development, a simple string replacement could be sufficient for now.
MarkMLl:
--- Quote from: rvk on May 20, 2022, 09:53:24 pm ---Sure... some form of TSQLQuery.GetExpandedSQL where the executed SQL is retrieved from the server would be nice and preferable. But does that always work? How does a server handle binary fields for example? Can all SQL statements be represented by a SQL string with all values?
--- End quote ---
No, a hook/event immediately before the query went to the server... allowing that queries may be dozens or hundreds of lines long particularly if machine-generated (complex joins, or in particular function definitions).
And I'm not sure that blobs are a problem here: I think that SQL queries are by definition printable so a blob is either going to be quoted/escaped in some form or is going to be represented by a named file which has already been transferred to the server by some other protocol (scp etc.).
MarkMLl
rvk:
--- Quote from: MarkMLl on May 20, 2022, 10:20:42 pm ---No, a hook/event immediately before the query went to the server... allowing that queries may be dozens or hundreds of lines long particularly if machine-generated (complex joins, or in particular function definitions).
...
I think that SQL queries are by definition printable so a blob is either going to be quoted/escaped in some form or is going to be represented by a named file which has already been transferred to the server by some other protocol (scp etc.).
--- End quote ---
But the parameters are transferred as binary to the server, along with the SQL.
There is no 'complete' SQL send to the server from the client. That's the whole idea of parameterizing.
So what would you want to catch in that case with a hook or event?
SQLite3 does have the sqlite3_expanded_sql call which returns a representation of a possible expanded SQL string. But that's not at client level (it's also not what is executed but a representation of a SQL string that could be executed). It could be retrieved from the server (which might be the same as the client with SQLite3 but isn't guaranteed). And I'm not aware of such a call for other servers (Firebird/Interbase, MSQL, MySql, Oracle). And so that would not be just before the SQL is send to the server, but after it has been send to the server.
petejones:
I wrote a routine to do it myself - just replaced each param holder with the value, then saved the sql to a file - that's good enough for sql logging and debugging, which is what I wanted it for.
MarkMLl:
--- Quote from: rvk on May 20, 2022, 10:46:06 pm ---But the parameters are transferred as binary to the server, along with the SQL.
--- End quote ---
Not necessarily, that depends on the server API. For example using pqconnection.pp as an example, a query can be submitted via PQExec() or via PQexecParams() etc. which prepares the parameters in PostgreSQL-specific form.
From the Pascal POV, it can be useful to use parameterisation. But at that point there should- IMO- at least be the option of having the database libraries build a parameterless query themselves, which is available for inspection before being sent to the server. That would make it easier to debug complex queries, and if the action of switching from client to server parameterisation broke something it would be comparatively easy to track down using server logs... while still having a working fallback position.
In practice, I'm not even sure to what extent server-omplemented parameterisation improves efficiency these days, particularly if a server is told which subqueries will always return the same result for the same parameters.
MarkMLl
Navigation
[0] Message Index
[#] Next page
[*] Previous page