Recent

Author Topic: How do I access the full sql+params in a TSQLQuery?  (Read 589 times)

petejones

  • New Member
  • *
  • Posts: 13
How do I access the full sql+params in a TSQLQuery?
« on: May 20, 2022, 07:50:51 pm »
How do I access the full sql+params in a TSQLQuery?

I tried Query.SQL.Text (Query being my variable), but the parameters were the placement markers (:Param1, etc), not the actual values.

Cheers
Pete

MarkMLl

  • Hero Member
  • *****
  • Posts: 4494
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #1 on: May 20, 2022, 08:02:44 pm »
I'm not sure you can, recent thread at https://forum.lazarus.freepascal.org/index.php/topic,59135.msg440881.html#msg440881 might have some relevant stuff in it.

I've not really investigated this recently, but in the past I've concluded that I was better off building queries entirely manually since that means that I can examine exactly what's being passed to the backend.

Alternatively, it's reasonable to expect some (but not all) backends to log queries as they receive them.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

petejones

  • New Member
  • *
  • Posts: 13
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #2 on: May 20, 2022, 08:09:45 pm »
Thanks Mark, yes that's what I want it for - I may go down that route.

rvk

  • Hero Member
  • *****
  • Posts: 4702
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #3 on: May 20, 2022, 09:41:03 pm »
How do I access the full sql+params in a TSQLQuery?
There are several (good) reasons for using TParams for a SQL query.

So why do you want the full SQL? And what do you mean by full SQL?
For example, you could use Params to insert BLOB, images, RTF, binary, Integers etc. Not all these things could just be inserted into one SQL string.
TSQLQuery.GetExpandedSQL sounds nice but isn't always possible.

If it's just for debugging you could do something like this;
Code: Pascal  [Select][+][-]
  1. S := IBQuery1.SQL.Text + #13;
  2. for I := 0 to IBQuery1.Params.Count -1 do
  3.   S := S + #13 + Format('%s: %s', [IBQuery1.Params[I].Name, IBQuery1.Params[I].Text]);
  4. ShowMessage(S);

It'll output your SQL followed by all the params (IF they can be converted to a string).
You can also expand this into some form of TSQLQuery.GetExpandedSQL yourself by replacing all ':'+IBQuery1.Params[I].Name with IBQuery1.Params[I].Text.

Code: Pascal  [Select][+][-]
  1. S := IBQuery1.SQL.Text;
  2. for I := 0 to IBQuery1.Params.Count -1 do
  3.   S := StringReplace(S, ':' + IBQuery1.Params[I].Name, QuotedStr(IBQuery1.Params[I].Text), [rfReplaceAll, rfIgnoreCase]);
Note: I used QuotedStr here but for integer you shouldn't quote. So if you wanted to use this SQL in a sql manager it would need to be perfected somewhat.
This was just a very simple example.

MarkMLl

  • Hero Member
  • *****
  • Posts: 4494
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #4 on: May 20, 2022, 09:47:37 pm »
There's plenty of bugs in Format(). Can you guarantee the reliability of that code?

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.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

rvk

  • Hero Member
  • *****
  • Posts: 4702
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #5 on: May 20, 2022, 09:53:24 pm »
There's plenty of bugs in Format(). Can you guarantee the reliability of that code?
I don't see where this could go wrong. Please point to where it could be a problem.

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.
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

  • Hero Member
  • *****
  • Posts: 4494
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #6 on: May 20, 2022, 10:20:42 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?

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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

rvk

  • Hero Member
  • *****
  • Posts: 4702
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #7 on: May 20, 2022, 10:46:06 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.).
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

  • New Member
  • *
  • Posts: 13
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #8 on: May 21, 2022, 12:10:38 am »
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

  • Hero Member
  • *****
  • Posts: 4494
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #9 on: May 21, 2022, 09:34:51 am »
But the parameters are transferred as binary to the server, along with the SQL.

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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

rvk

  • Hero Member
  • *****
  • Posts: 4702
Re: How do I access the full sql+params in a TSQLQuery?
« Reply #10 on: May 22, 2022, 05:21:57 pm »
But the parameters are transferred as binary to the server, along with the SQL.
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.
Even then... in PQexecParams() the parameters are still transferred separate from the SQL.

(1) 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 (2) before being sent to the server.
I agree with the first part. It can be useful for debugging. But it doesn't need to be sent to the server (2). At least not standard. Maybe for debugging you could do that one to see if it has the same results as with the params but that's just for debugging.

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.
I'm sure this is still (and can be) vastly more efficient. Especially in cases of bulk inserts where the SQL/PLAN is prepared (up to 100% more efficient).

So, although I agree a debugging function could be useful to replace parameters in a TSQLQuery to a parameterless string, I'm not sure this really needs to be a part of the (deeper) library. This could just be a more simple helper function.

TSQLQuery.GetExpandedSQL and TSQLQuery.ExecuteExpandedSQL for example.
And this would still be done with a form of ReplaceString I showed earlier and only used when you want to debug something.

But that's just my opinion.

 

TinyPortal © 2005-2018