Prevent TParams duplication-CodePudding

I have a TParams instance (FParams) and when I'm calling the ParseSQL() it duplicates params if the given SQL contains more than one reference to the same param name.

For example:

FParams.ParseSQL(
  'select * from user_relations where user1 = :p_logged_user or user2 = :p_logged_user', 
  True);

It generates two TParams' items with the same name, which casuses a problem later in my code. I know the DB acces components (TUniQuery) eliminates this problem and dont duplicate params when parsing their SQL text, but I was unable to locate the relevant code, how to do this easily.

Is there any built-in way to prevent the TParams to store/parse duplicated parameters?

I already wrote code to get rid of them immediately after the ParseSQL(), I just want to know if I missed some "out-of-the-box" solution.

updated the sample code to make clear why I want the same name for both params. my opinion is the same name make the code more straightforward.

CodePudding user response：

If you are using SQL Server, you can replace your SQL with:

Declare @P1 varchar(10)
set @P1 = :p_loggeduser
select * from user_relations where user1 = @P1 or user2 = @P1

I don't know which other database platforms will support this syntax though.

CodePudding user response：

For some databases what actually gets sent uses ? parameters. For some data access components the parameter list is ordered and has the same number of entries as ? parameters in this version of the query.

select * from user_relations where user1 = ? or user2 = ?

Delphi expanded named parameter support in SQL to all databases and does the extra work behind the scenes. One artifact of this is the parameter list can be larger than one would expect depending on the data access components being used.