Good day all,

I got 3 rows in the table login that I used as credentials for authenticate.

the last usernameLogin field in the table as erickom and the first 2 ajm and ajm1.

on the authentication form, when I entered erickom and its password, everything goes well, when I entered an unknown username everything goes well as well like 'Failed to connect using: ' + getUsername; but when I entered the 2 first usernames that are in the table (ajm & ajm1) its not responding as expected, the if condition jump after display the message Retrieving information for: ' + retUsername to Failed to connect using: ' + getUsername. Abnormal behave.

Please view below my code and Thank in advance:

if MySQL55.Connected=True then begin;
         //SQLQueryMySQL.SQL.Text := 'SELECT usernameLogin,
passwordLogin, fullNameLogin FROM scholarly.login WHERE usernameLogin =
'':getUsername'' AND passwordLogin = '':getPasswordHash''';
         SQLQueryMySQL.SQL.Text := 'SELECT usernameLogin, passwordLogin,
fullNameLogin FROM scholarly.login';
         SQLTransactionMySQl.StartTransaction;
         SQLQueryMySQL.Open;
      while not SQLQueryMySQL.EOF do begin
         //Retrieved data fields and assigned to a string variables
         retUsername := SQLQueryMySQL.Fields[0].AsString;
         retPassword := SQLQueryMySQL.Fields[1].AsString;
         retFullname := SQLQueryMySQL.Fields[2].AsString;
         if (getUsername = retUsername) AND (getPasswordHash =
retPassword) then begin
            ShowString('Retrieving information for: ' + retUsername);
            StatusBarLogin.Update;
            sleep(2000);
            ShowString('Connected to server as: ' + retUsername + ',
known as ' + retFullname);
         end
         else
            ShowString('Failed to connect using: ' + getUsername);
         SQLQueryMySQL.Next;
        end;
         SQLQueryMySQL.Close;
         SQLTransactionMySQL.Commit;
      end;
  except
   on D: EDatabaseError do
   ShowString('Connection to database as failed, please check your
network.');
  end;

You don't leave the while loop when you found the correct user.

For each record there will be a showstring call processed.

It should be something like:

var found: boolean;
....
      found := false;
      while not SQLQueryMySQL.EOF and not found do begin
         //Retrieved data fields and assigned to a string variables
         retUsername := SQLQueryMySQL.Fields[0].AsString;
         retPassword := SQLQueryMySQL.Fields[1].AsString;
         found :=  (getUsername = retUsername) AND
            (getPasswordHash = retPassword);
         SQLQueryMySQL.Next;
        end;
      if found then begin
            retFullname := SQLQueryMySQL.Fields[2].AsString;
            ShowString('Retrieving information for: ' + retUsername);
            StatusBarLogin.Update;
            sleep(2000);
            ShowString('Connected to server as: ' + retUsername +
		', known as ' + retFullname);
      end
      else
            ShowString('Failed to connect using: ' + getUsername);

Luk

Apart from Luk's remarks,

I wouldn't use quotes around those parameters in the WHERE clause. The parameters are meant to work around the quote problems. I'd focus on getting the code above fixed as it's much more elegant: it let's the DB engine focus on the query...

Groete,
Reinier

Luk Vandelaer wrote

You don't leave the while loop when you found the correct user.

Thanks again Luk,

By putting the retFullname := SQLQueryMySQL.Fields[2].AsString; statement after the SQLQueryMySQL.Next; statement, this last statement makes the field fullname to jump to the next row, by selecting a wrong fullname for the credentials on check.

anyway thanks again for your debugging.

I was trying to affect the variable value to a field. I did confuse them with the single quote whether they are 1, 2 or 3

ok... so you only want to jump to the next record if not found, right?

       found := false;
       while not SQLQueryMySQL.EOF and not found do begin
          //Retrieved data fields and assigned to a string variables
          retUsername := SQLQueryMySQL.Fields[0].AsString;
          retPassword := SQLQueryMySQL.Fields[1].AsString;
          found :=  (getUsername = retUsername) AND
             (getPasswordHash = retPassword);
          if not found then SQLQueryMySQL.Next;
         end;
       if found then begin
             retFullname := SQLQueryMySQL.Fields[2].AsString;
             ShowString('Retrieving information for: ' + retUsername);
             StatusBarLogin.Update;
             sleep(2000);
             ShowString('Connected to server as: ' + retUsername +
		', known as ' + retFullname);
       end
       else
             ShowString('Failed to connect using: ' + getUsername);

;)

Eric Kom wrote

Please view below my code and Thank in advance:

I'd do something like the following (untested):
var
  LoginSuccess: boolean;
....
  LoginSuccess:=false;
  if MySQL55.Connected=True then
  try
    SQLQueryMySQL.SQL.Text := 'SELECT fullNameLogin FROM scholarly.login '+
      'WHERE usernameLogin = :getUsername AND passwordLogin =
:getPasswordHash';
    SQLQueryMySQL.ParamByName('getUsername').AsString:=getUsername;
    SQLQueryMySQL.ParamByName('getPasswordHash').AsString:=getPasswordHash;
    SQLTransactionMySQl.StartTransaction;
    SQLQueryMySQL.Open;
    if not SQLQueryMySQL.EOF then
    begin
      LoginSuccess := true;
      retFullname := SQLQueryMySQL.Fields[2].AsString;
      ShowString('Retrieving information for: ' + getUsername);
      StatusBarLogin.Update;
      sleep(2000);
      ShowString('Connected to server as: ' + getUsername + ', known as
' + retFullname);
    end
    else
    begin
      ShowString('Failed to connect using: ' + getUsername);
    end;
    SQLQueryMySQL.Close;
    SQLTransactionMySQL.Commit;
   except
    on D: EDatabaseError do
    ShowString('Connection to database as '+getUsername+' failed, please
check your network.');
   end;
  //now use LoginSuccess to see if user could log in etc

//while not SQLQueryMySQL.EOF //and not found do begin
while (not SQLQueryMySQL.EOF) and (not found) do begin