Lazarus Pascal: Parameter pp not found

Question

I have been using SQL queries with parameters on Lazarus for some time now but I'm stumped with this error. It seems pretty basic but I'm just lost.

I have the following Lazarus Pascal code:

   strSQL:= 'Select "fCo",  "fcFarmID", "fcFlockCode", ' +
            '"fcWeekEnding","fcAge", ' +
            '(:"pDate" - "fcWeekEnding")/7 as "AgeWeeks" ' +
            'From (select "fCo", "FarmID", "FarmName" from "tblFarms" Where "fCo" = :"pp") as f Left Join "tblFlockCodes" ' +
            'On "FarmID" = "fcFarmID" ';
   dbQuery_FlockCodes.SQL.Text:= strSQL;       
   dbQuery_FlockCodes.Params.ParamByName('pDate').AsDate := dtWeekEndingDate.Date;
   dbQuery_FlockCodes.Params.ParamByName('pp').AsString:= lstCo.Text;

The program reports a run-time error when the last line (with parameter pp) is run. The error is:

Paramater pp not found.

I have queries with multiple params but there's no problem. It's just this one. I even tried renaming the parameter name but I get the same error.

If I use a string literal in place of the parameter or insert a concatenated string like the following, it works just fine.

   strSQL:= 'Select "fCo",  "fcFarmID", "fcFlockCode", ' +
            '"fcWeekEnding","fcAge", ' +
            '(:"pDate" - "fcWeekEnding")/7 as "AgeWeeks" ' +
            'From (select "fCo", "FarmID", "FarmName" from "tblFarms" Where "fCo" = ' + QuotedStr(lstCo.Text) + ' ) as f Left Join "tblFlockCodes" ' +

In the last example above, lstCo is a drop-down list box. In the whole code, dtWeekEndingDate is a date control.

Perhaps I'm overseeing something. Any idea? Thanks!

If you're trying to insert a table name as a parameter, you'll probably find that this is not supported, for the reasons given here (it's talking about a completely different language, but the principles still apply): http://stackoverflow.com/a/15990488/157957 — IMSoP, Nov 10 '13 at 18:08
(And if you're not, why are you using double quotes, which are used by PostgreSQL only to delimit an identifier such as a table name?) — IMSoP, Nov 10 '13 at 18:14
@IMSoP With PG, I must enclose every field and table name within double quotes as they contain mixed cases. In a similar manner we use the back-tick to enclose fields in mysql. This is basic stuff. This is not the FIRST time I'm doing this. That's what I cannot figure out :/ — itsols, Nov 11 '13 at 03:22
I understand that that's what double quotes do, but you can't use table or column names as parameters in a prepared statement, as it makes no sense, for the reasons I gave in the linked answer. — IMSoP, Nov 11 '13 at 14:51
@IMSoP Thank you for your inputs... Perhaps I'm overseeing something very simple but important. But as far as I know, I am NOT using table or field names as parameters. Those parameters are set through Lazarus (not that the language matters). In fact the FIRST parameter pDate is accepted and it works. Only the parameter pp fails. That's what puzzles me. — itsols, Nov 11 '13 at 16:15
In that case, I don't understand what the double quotes are doing. Are they making the parameter itself case sensitive? If so, why not just use lower case with underscores to name your parameters (same goes for table and column names, but those are generally harder to change)? Sounds like it's unrelated to your problem, though, just rather odd. — IMSoP, Nov 11 '13 at 18:28

score 1 · Answer 1 · answered Nov 10 '13 at 18:02

1

Probably the query parser searches for :pp, so the colon should be inside the quotes like ":pp", not :"pp"

answered Nov 10 '13 at 18:02

Marco van de Voort

25,628
5
56
89

1

Or possibly, there shouldn't be quotes at all (just `:pp` instead of `":pp"` or `:"pp"`) – Nov 10 '13 at 18:05
In Postgres, we must enclose every field within double quotes if it uses mixed cases. Furthermore, I'm using this same method in other parts of the project as well. – itsols Nov 11 '13 at 03:19

score 1 · Answer 2 · answered Nov 10 '13 at 18:10

1

Remove the quotes from around both of the parameters. Using AsDate and AsString will properly format them for you.

strSQL:= 'Select "fCo",  "fcFarmID", "fcFlockCode", ' +
          '"fcWeekEnding","fcAge", ' +
          '(:pDate - "fcWeekEnding")/7 as "AgeWeeks" ' +
          'From (select "fCo", "FarmID", "FarmName" from "tblFarms" Where "fCo" = :pp) as f Left Join "tblFlockCodes" ' +
          'On "FarmID" = "fcFarmID" ';
dbQuery_FlockCodes.SQL.Text:= strSQL;       
dbQuery_FlockCodes.Params.ParamByName('pDate').AsDate := dtWeekEndingDate.Date;
dbQuery_FlockCodes.Params.ParamByName('pp').AsString:= lstCo.Text;

answered Nov 10 '13 at 18:10

Ken White

123,280
14
225
444

In fact tt is a hassle to use the quotes but I am forced to use them to keep the field names consistent. If I don't enclose them in quotes, the fields are taken as lower case an there have been instances when the language reports that the field cannot be found. I'm now using PG + Pascal but I've seen these issues with PG+ C++. – itsols Nov 11 '13 at 03:28
I didn't say to remove them from the **field names**. I said remove them from around the **parameters**. The values set for those parameters will be properly quoted when `AsDate` or `AsString` is set. – Ken White Nov 11 '13 at 03:32
Thank you for your inputs as well. Strangely though I have the same error: 'parameter pp not found'. I'm starting to think if this is an internal bug in Lazaurs. – itsols Nov 11 '13 at 11:42

Lazarus Pascal: Parameter pp not found

2 Answers2