Delphi + SQLite + DB express select with parameters error

Question

I use Delphi XE3 and use SQLite database with DB express.

this is my user validation code:

function validateUser(UserName, Password: string): Boolean;
var
  AParams: TParams;
  SQLTxt: string;
  MD5 : TIdHashMessageDigest5;
  RecCount: integer; 
begin
  AParams := TParams.Create(nil);
  MD5 := TIdHashMessageDigest5.Create;
  try
    Result := False;
    AParams.CreateParam(ftString, 'username', ptInput).Value := UserName;
    AParams.CreateParam(ftString, 'password', ptInput).Value :=
      MD5.HashBytesAsHex(MD5.HashString(Password));
    SQLTxt := 'SELECT login_id FROM login WHERE '+
    'login_username = :username AND login_password = :password ;';
    with Form1.sqlqry1 do
    begin
      SQL.Clear;
      SQL.Text :=  SQLTxt;
      Params := AParams;
      //Params.Items[0].Value := AParams.Items[0].Value;
      //Params.Items[1].Value := AParams.Items[1].Value;
      Prepared := true;
      Open;
    end;
    RecCount := Form1.sqlqry1.RecordCount;// I have error here
    if RecCount = 0 then
      Result := False
    else
      Result := True;
    // end
  finally
    AParams.Free;
    MD5.Free;
  end;
end;

Application show me [0x0005]: Operation Not Supported error.

What is the problem? why?

That's a known problem. I would workaround it by executing SELECT COUNT(*) FROM... query and taking the value from the field. — TLama
– TLama, Commented Feb 28, 2014 at 20:29
It should. SQLite supports COUNT function so if you execute such query, you should get the count from the first field SQLQuery1.Fields[0].AsInteger. And that will be the replacement for that problematic RecordCount property. — TLama
– TLama, Commented Feb 28, 2014 at 20:59
Hey man I haven't fb acc to like it, I'm sorry. My problem was trim password I can't found it without your guide. — A1Gard
– A1Gard, Commented Feb 28, 2014 at 21:08

Ken White · Accepted Answer · 2014-02-28 20:29:59Z

1

You also don't need to use RecordCount (which is where you indicate the error is occurring). Use TDataSet.IsEmpty instead.

Also, from the documentation (emphasis mine):

Params is a collection of TParam objects that represent the parameters of a query or stored procedure specified by the SQL dataset. When you specify a query by setting the CommandText property (or the SQL property in TSQLQuery), the SQL dataset automatically parse the query and fills Params with a TParam object for every parameter in the query.

You don't need to manually create the Params; the dataset will do that for you automatically when you assign the SQL.

function validateUser(UserName, Password: string): Boolean;
var
  SQLTxt: string;
  MD5 : TIdHashMessageDigest5;
  RecCount: integer; 
begin
  MD5 := TIdHashMessageDigest5.Create;
  try
    Result := False;
    SQLTxt := 'SELECT login_id FROM login WHERE '+
    'login_username = :username AND login_password = :password ;';
    with Form1 do
    begin
      // Clear not needed when setting SQL.Text directly.
      sqlqry1.SQL.Text :=  SQLTxt; 
      sqlqry1.Params.ParamByName('username').AsString := UserName;
      sqlqry1.Params.ParamByName('password').AsString :=
               MD5.HashBytesAsHex(MD5.HashString(Password));

      sqlqry1.Open;
      Result := not sqlqry1.IsEmpty;
    end;
  finally
    MD5.Free;
  end;
end;

answered Feb 28, 2014 at 20:29

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

A1Gard Over a year ago

This is always return false, Even if username and password are true.

Ken White Over a year ago

Um, no it doesn't. IsEmpty returns True if the query result has no rows; the not says "if the query is not empty, set the result to True". "not false" = "true". It's a shorthand way for if sqlQry1.IsEmpty <> False then Result := True;.

A1Gard Over a year ago

This query is always empty,My problem is here.

Ken White Over a year ago

If the query is empty, it's not finding a match for the username/password. Have you confirmed that there is a match in the database? I don't have your data, but I use parameterized queries quite often in my applications with the same logic. I clearly don't have your data, username and password to test against, but the logic itself is correct.

Ken White Over a year ago

@MahdiParsa: It's usually not necessary to trim, but that may be a specific issue with SQLite or your database schema. I'm hesitant to add it to my answer, because if it's not needed for most cases then it would be incorrect in all those cases.

Collectives™ on Stack Overflow

Delphi + SQLite + DB express select with parameters error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related