Parameter Not Found

[royyu]

Hi,

ZEOS 7.2.0-beta on Delphi 2009 / Windows XP, PosgreSQL 8

It cause a problem, when i using this SQL

ZQuery1.Close;
ZQuery1.SQL.add('select * from sys_user = :user_id');
ZQuery1.ParamByName('user_id').AsInteger := 1; //Parameter 'user_id' not found
ZQuery1.Open;


No problem when using ZEOS 7.0.0-dev.

So i check the code, The ZTokenizer NextToken function has some different between 7.0.0 and 7.2.0

7.0.0 can separate by 'SPACE', but 7.2.0 can't

plz help for that, Thanks

[miab3]

Where is WHERE (and TableName)?

Michal

[royyu]

Hi Michal,

Sorry for typing wrong:
ZQuery1.Close;
ZQuery1.SQL.add('select * from sys_user where user_id = :user_id');
ZQuery1.ParamByName('user_id').AsInteger := 1; //Paramrter 'user_id' not found
ZQuery1.Open;

Table name is 'sys_user'

Best Regards,

[marsupilami]

Hello royyu,

I didn't have the time yet to check this in depth. What happens if you remove the _ from your parameter name? So have userid instead of user_id?
With best regards,

Jan

[miab3]

For me it works:

Code: Select all

 ZQuery2.Close;
 ZQuery2.SQL.Clear;
 ZQuery2.SQL.add('select * from kontra where "N_SKROC" = :n_skroc');
 ZQuery2.ParamByName('n_skroc').AsString := 'KIM';
 ZQuery2.Open;

ZEOS 7.2 svn 3910:
http://sourceforge.net/p/zeoslib/code-0 ... sting-7.2/
Delphi XE2-Win32
PostgreSQL 9.3
Windows 10-64

Michal

[kgizmo]

I have the same problem with version 7.2 3929. Or maybe not the same but I have the same error.
For "and (I.nazwa iLike :nazwaFiltr)" everything works fine, but for "'and ((B.nazwa iLike :nazwaFiltr) or (B.skrot iLike :nazwaFiltr)) '" I've got "Parameter not found". Maybe it's all about nasted brackets?

[kgizmo]

Found it. It's not about nested brackets. It's a problem with escape character "\".
Examples (for PostgreSQL):

SQL.Text:='select field1||''\\n''||field2 from tab where 1=1 and (field3 iLike :param)';
Everything is OK with Params but I get "\n" string in the result text.

SQL.Text:='select field1||''\n''||field2 from tab where 1=1 and (field3 iLike :param)';
Parameter not found.

SQL.Text:='select field1||E''\n''||field2 from tab where 1=1 and (field3 iLike :param)'; \\the original (working before) code and suitable for PostgreSQL
Parameter not found.

I was using 7.2 3648 and there wasn't problems with that. Now my existing programs don't work.

Kamil

[kgizmo]

OK, here is a solution for PostgreSQL. There have to be made changes in ZPostgreSqlToken.pas:

Code: Select all

procedure TZPostgreSQLQuoteState.GetQuotedString(Stream: TStream; QuoteChar: Char;
  EscapeSyntax: Boolean; var Result: String);
const BackSlash = Char('\');
var
  ReadChar: Char;
  LastChar: Char;
  QuoteCount: Integer;
  LastWasEscapeChar: Boolean;
begin
  LastChar := #0;
  Result := '';
  InitBuf(QuoteChar);
  QuoteCount := 1;

  LastWasEscapeChar := False;
  while Stream.Read(ReadChar{%H-}, SizeOf(Char)) > 0 do
  begin
    if ReadChar = QuoteChar then
      Inc(QuoteCount, Ord((not EscapeSyntax) or (not LastWasEscapeChar)))
    else
      LastWasEscapeChar :=(ReadChar=BackSlash) and (not LastWasEscapeChar); //False; //Kamil Giza comment False;

    if (LastChar = QuoteChar) and (ReadChar <> QuoteChar) then
      if QuoteCount mod 2 = 0 then begin
        Stream.Seek(-SizeOf(Char), soFromCurrent);
        Break;
      end;
    ToBuf(ReadChar, Result);
    if (LastChar = BackSlash) and EscapeSyntax then begin
      LastChar := #0;
      //LastWasEscapeChar := True; //Kamil Giza add comment
      //Dec(QuoteCount); nope that doesnt' work @all see the tests
    end
    else if (LastChar = QuoteChar) and (ReadChar = QuoteChar) then
      LastChar := #0
    else
      LastChar := ReadChar;
  end;
  FlushBuf(Result);
end;

[marsupilami]

Hello Kamil,

The patch has been applied. See Zeos 7.2 Rev. 3930. Thanks for your help.
With best regards,

Jan

[Gallemar]

I have a similar problem on mysql, can you help?

[ertank]

I have a similar problem on mysql, can you help?

Hello Gallemar,

Information you provided is not enough to help you. Would you post your SQL statement you are having problem and your TZQuery.ParamChar value, please.

Thanks.
Ertan