What happens if I call ParamByName for a parameter that doesn't exist?

Question

I'm very new to Delphi and received the following piece of code (left out some irrelevant parts) for which I'm trying to understand what it does:

object SelectCosts: TIBQuery
    SQL.Strings = (
      'SELECT * FROM costs '
      'WHERE code = :code')
    ParamData = <
      item
        DataType = ftUnknown
        Name = 'code'
        ParamType = ptUnknown
      end>
  end

In another file, that query is used, but a parameter that is not defined in the query is added.

DM_HRV.SelectCosts.ParamByName('part').Value := 1;

Does this parameter 'part' change anything about the selection made? In other words: is the SQL query automatically changed into the following?

'SELECT * FROM costs '
  'WHERE code = :code'
  'AND part = :part'

I strongly recommend setting the SQL dynamically, setting it in the component is prone to many future errors. — , Jan 16 '12 at 13:27
@DorinDuminica it is not my own code, but code I need to review to check what it does. Thanks for the information though, it might be helpful in the future! — Maza89, Jan 16 '12 at 13:45
@DorinDuminica: Haven't noticed that so far! However, I realise that I may still be to encounter them in the future. In view of that, it would be really interesting to hear even about a few of the ‘many’ possible errors you are talking about. — Andriy M, Jan 16 '12 at 20:23
@AndriyM well, the sh** hits the fan when you're trying to replace components with others(UniDAC, AnyDAC, etc.), also, copy-paste is a very common practice, therefore you'll copy the query with it's settings and try to use it for other purpose, therefore when you change your SQL bad things can happen due to bugs in the IDE, I'm mostly talking about D2010, I have to kill and start it about twice/day due to IDE bugs and exceptions. While my issues might not be so common, it's still a good practice(IMHO) to set SQL and params dynamically to avoid many headaches. — , Jan 17 '12 at 12:33
@DorinDuminica: Your particular examples of problematic situations seem to mark my relative inexperience even stronger. I haven't had to work on replacing data components so far. Also, as presently I mostly work with projects written in earlier versions of Delphi than D2010, the latter is still more or less terra incognita to me as well, with regard to its set of bugs at least. I'm a happy man, you see, I've got so much yet to discover! :) Thanks for sharing, really appreciated! — Andriy M, Jan 17 '12 at 14:56
@AndriyM well, this situations happen to me, it doesn't mean that it necessarily has to happen to you. We've switched from D7 to D2010 on my insistence, so I'm not aware of what bugs are in between this versions, with D7 the IDE would have simply kill itself, you coding and all of the sudden the IDE is no more ((: with D2010, many times the IDe screws up my components and stuff, so in order to overcome any future issues, I've disciplined myself to write code more than putting components and not tie business logic to the UI <- very hard to port it and/or upgrade. Anyways, hope it helps. — , Jan 17 '12 at 16:26

score 8 · Accepted Answer · edited May 29 '17 at 10:35

8

That means that the SQL statement might be changed at run-time. so when that query is used the SQL already contains AND part = :part.

If the SQL statement does not contain this extra parameter part, an Exception will be raised when assigning ParamByName('part').Value := 1.

I'm assuming you didn't confuse SelectCosts reference (which is in DM_HRV and not other DM).

edited May 29 '17 at 10:35

Sнаđошƒаӽ

16,753
12
73
90

answered Jan 16 '12 at 12:50

kobik

21,001
4
61
121

1

Or, though less likely, the query might be changed in a completely different place, you never know. – Andriy M Jan 16 '12 at 12:53
@kobik Thanks, exactly what I thought already. @AndriyM Though it seemed less likely to you, I found that it was indeed changed in a completely different place. The query text was replaced with `SELECT * FROM costs WHERE code = :code AND part = :part AND year = :year'` – Maza89 Jan 16 '12 at 13:49

score 5 · Answer 2 · answered Jan 16 '12 at 13:26

The following statement in your post does not add a parameter, it sets its value:

DM_HRV.SelectCosts.ParamByName('part').Value := 1;

To add a parameter at runtime, use CreateParam as follows:

if DM_HRV.SelectCosts.Params.FindParam('Part') = nil then
   DM_HRV.SelectCosts.Params.createParam(ftString, 'Part', ptInput);

The query doesn't get modified automatically, you have to do that yourself.

In your first snip, the ParamType and InputType are not defined, you can change that in the IDE by access the parameter list (Params) property editor, and updating those values.

score 0 · Answer 3 · answered Jan 16 '12 at 12:34

0

Adding a new parameter does not change the query. You have to do that yourself.

answered Jan 16 '12 at 12:34

Mikael Eriksson

136,425
22
210
281

What happens if I call ParamByName for a parameter that doesn't exist?

3 Answers3