1

I was trying to figure out how TQuery is creating the final SQL String by correctly Replacing the values specified in the Params Property.

That is let the SQL string be SELECT * FROM tablename WHERE username= :Name and i give the value of :Name as 'abc' in Params option of TQuery. but the final SQL string is formed as SELECT * FROM tablename WHERE username='abc'. I just want to know how that is done.

I tried to Debug the application line by line to find the function that do the above specified job, But i couldn't. Someone please help.

Anoop Abraham
  • 57
  • 1
  • 8
  • Why would you need to know this, or care about how it is implemented? Surely the point is that the framework takes care of this. Do you have a concern that it is handling parameters incorrectly? – David Heffernan May 31 '17 at 09:40
  • 2
    That's what a DB server does. You send them the command and parameter values and it takes care of the rest internally. Command can then stay prepared and you can send only the values to request a specific resultset. That's actually the major performance advantage of this technique. – Victoria May 31 '17 at 09:48
  • @DavidHeffernan I was trying to create a function which does the similar job. So is it done by Delphi or the SQL Server.? – Anoop Abraham May 31 '17 at 09:57
  • 2
    To gain the performance benefits that @Victoria describes, the substitution must occur server side. It's very likely that if you try to write such a function yourself, you will get it wrong and open yourself up to SQL injection. – David Heffernan May 31 '17 at 10:01
  • Even if it would do it - you would not find it. TQuery is a wrapper around BDE library, which is closed-sourced. However, it does not. Only fragile and lazily coded libraries use "full SQL text". Converting parameters into in-text values is both dangerous ( http://bobby-tables.com/ ) and unreliable ( https://stackoverflow.com/questions/44266141/ ). Additionally, BDE (and thus TQuery and TTable and TDatabase and other components of the same bush) are long obsoleted and are not to be used – Arioch 'The May 31 '17 at 12:13
  • "So is it done by Delphi or the SQL Server.? " As @Arioch'The has pointed out, TQuery works with the BDE, so there isn't necessarily any Sql Server involved, just a BDE DLL (unless, e.g. you are using it to access a server via an ODBC alias). – MartynA May 31 '17 at 12:24
  • Thank you all for the help. – Anoop Abraham May 31 '17 at 12:28

1 Answers1

0

A DB client (TQuery here) does not form the full SQL command. Instead it sends the parametrized command to the DB engine (server) where it's prepared for execution and client then sends only the parameter values.

That's why this technique has its performance advantage, the DB engine does not need to prepare the same command over and over again. So you won't most probably find such implementation in any DB client component.

Victoria
  • 7,822
  • 2
  • 21
  • 44
  • "A DB client does not ..." It does in the BDE. – Alex James May 31 '17 at 13:35
  • @Alex, by DB client I mean the component itself. Are you sure the component itself forms the query? Isn't that the BDE library (server) that does this job? – Victoria May 31 '17 at 13:40
  • At run time, the BDE is part of the client, in that it is a DLL loaded into the client app's process. Whether the query is actually executed by the BDE or handed on to a server depends on the configuration of the BDE Alias(es) in use. The BDE can do what Borland called "heterogenous" joins, between tables from different DB types, which necessarily means that part of the query is actually executed by the BDE, not any separate server. – Alex James May 31 '17 at 13:53
  • @Alex, if it's part of the application does not change much on that. It is a server, so as we call e.g. SQLite server even if its linked in the application. Obviously, `TQuery` since can be used with remote servers is not the one who forms the full query so what I wrote is not incorrect. – Victoria May 31 '17 at 14:04
  • @Alex, better now? The point stands unchanged. It's not the `TQuery` who forms the command that is executed by the DB engine (it's the engine's job). – Victoria May 31 '17 at 14:23
  • @Alex, I'd be glad if you could elaborate. Is there something wrong with this post (after update), please? – Victoria Jun 04 '17 at 23:20