Forum > Databases
How do I access the full sql+params in a TSQLQuery?
petejones:
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:
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
petejones:
Thanks Mark, yes that's what I want it for - I may go down that route.
rvk:
--- Quote from: petejones on May 20, 2022, 07:50:51 pm ---How do I access the full sql+params in a TSQLQuery?
--- End quote ---
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---S := IBQuery1.SQL.Text + #13;for I := 0 to IBQuery1.Params.Count -1 do S := S + #13 + Format('%s: %s', [IBQuery1.Params[I].Name, IBQuery1.Params[I].Text]);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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---S := IBQuery1.SQL.Text;for I := 0 to IBQuery1.Params.Count -1 do 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:
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
Navigation
[0] Message Index
[#] Next page