If Database Login Error - try again? Exit app? 1

[lespaul]

In my MainForm FormCreate procedure I have a query that I activate at which point the database login appears. The user logs in and the query runs (if the query returns a date that was 15 days ago do something). If however, the user puts in an invalid username or password, the query doesn't run, but the application still opens (without doing the date check query). How can I verify that the user has successfully logged in to the database and present the log-in again or just exit the program? I tried Application.Terminate but it still continues to open the application!! Thanks for all assistance! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[JediDan]

Use a try..finally structure:

try
   --log onto database
finally
   --if database.connected then
     --do whatever

You could also do try..except inside the top of the try..finally to encapsulate errors.

The database.connected property is from DOA (Direct Oracle Access), which is what I use but I belive ADO and BDE have similar items.

Hope this helps!

 

[lespaul]

Nope that doesn't work either! I guess what I'm really looking for is a 'LoginSuccess' or 'LoginFailure' to check and I can't seem to find anything!

Thanks! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[lespaul]

OK, my problem is worse than I thought! If I put in a wrong userid or password or press Cancel, I get a 'Can't connect to database metro'. If however, I press OK without entering anything (no user login information at all) the database still connects and the query runs!!! That's a bad situation! There is no verification at all! Maybe I need to set up the TDatabase connections manually rather than letting the TQuery do them automatically? Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[JediDan]

How is the session getting the password? Do you have it hardcoded somewhere? (A remnant of testing, perhaps...)

Your connect string, if you're using ADO should have something like

Connection.ConnectionString = "Provider=MSDAORA.1; Password=" & LoginPassword.Text & _ 
";User ID=" & LoginUserName.Text & ";Data Source =" & _ LoginDatabase.Text & ";Persist Security Info=True"

This way, you know that the logon script needs values from the text box, or else there's no way for the query to run since it won't have a database to connect to.

Good luck!

 

[lespaul]

When the program gets to this line of code

JMSData.qryReminders.Active := True;

The database login prompt appears. If I press cancel or put in a bogus UserName or invalid password, an error:
"Cannot connect to database metro" is returned. If I leave the login prompt empty (both user id and password) the connection is still made! I have added a Database component to my Datamodule, and have searched for some way to see if the login was successful, but have had no luck.

Thanks!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[JediDan]

It still seems as if you have the login information hardcoded somewhere in the code. Somewhere in your program is a connnection string that is telling the database your name and password, what does this code look like?

 

[lespaul]

All I have in my program are queries and datasets. I have not hard coded the login information anywhere in the program. The queries have the DatabaseName set to 'metro' (our database server). I have recently added a Database component to the DataModule, and in the default information the username and password are BLANK. And I don't think that I would even know how to set the connection string (without a lot of searching through the help) much less do it and not know where it was! When I step through the code, I get to the frmMainMenu.CreateForm procedure, set my query to Active and get the Database Login Prompt (not a form I built or call, it's an automatic thing). If I press Cancel or put in bad information I get the Cannot connect message, if I leave it blank, it connects to the database and continues without verifing any login information. Since I have added the Database component, I have checked it's properties and have LoginPrompt = True. But nowhere does it allow me to check the LoginPrompt for success or failure!

Thanks
Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[svanels]

Leslie your TDatabase is the central point for all your queries and tables. In other words your alias is defined in the Tdatabase only.

The alias of the TDatabase is the one defined in the BDE. I suspect that this one called metro
Set the DtabaseName property to my_Metro_db

All your tables and queries must then have their databaseName property set to my_Metro_db

For connecting I use a modal form with 2 editboxes, 2 buttons and labels. This modal form has a global function called GetLoginParms



type
TLoginFrm = class(TForm)
lblUsername: TLabel;
lblPassword: TLabel;
edtUserName: TEdit;
edtPassword: TEdit;
bbtnOK: TBitBtn;
bbtnCancel: TBitBtn;
private
{ Private declarations }
public
{ Public declarations }
end;
function GetLoginParams(ALoginParams: TStrings): Boolean;

function GetLoginParams(ALoginParams: TStrings): Boolean;
var
  LoginForm: TLoginFrm;
begin
  Result := False;
  LoginForm := TLoginFrm.Create(Application);
  try
    if LoginForm.ShowModal = mrOk then
    begin
      ALoginParams.Values['USER NAME'] :=
      LoginForm.edtUsername.Text;
      ALoginParams.Values['PASSWORD'] :=  
      LoginForm.edtPassWord.Text;
      Result := True;
    end;
  finally
    LoginForm.Free;
  end;
end;

---------------------------------
The database, queries etc. are put on a datamodule called datamod with 4 public functions/procedures

procedure Logout;
function Login: boolean;
function Connect: boolean;
procedure disconnect;

//the login event of the tdatabase
procedure TDtaMod.my_Metro_DBLogin(Database: TDatabase;
LoginParams: TStrings);
begin
{ Calls method below to populate the loginParams strings list
GetLoginParams(LoginParams);
end;


Procedure TDtaMod.Logout;
begin
disconnect;
end;

procedure TDtaMod.disconnect;
begin
//disconnect from database
my_Metro_DB.Connected := false;
end;

function TMainDtaMod.Connect: Boolean;
 { Connects the user to the Database. When my_metro_db is set to True, its 
   OnLogon event handler will be invoked which will invoke the login 
   dialog defined in LoginForm.pas }
 begin
   try
    dbTraps.Connected := true;
    qry1.Active := true;
    qry2.Active := true;
     ....activate all queries and table....
    Result := true;
   except
    MessageDlg('Invalid Password or Login information,   
    cannot login', mtError, [mbok],0);
    my_Metro_db.Connected := false;
    Result := false;
   end;
 end;

The only things needed in your program are login & logout which can be easily corporated in a menu


By the way this code I have from Delphi 4 Developer's guide

Best Regards Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

Steven, You ROCK!!! I knew there had to be some way to prevent logging in with invalid information, but I couldn't figure it out! I'll try this code today and post back if I have any questions! Thanks!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[lespaul]

Steven,

A couple of questions:

In the Connect function you say to activate all queries, why is that? Do you mean all the queries I need to run in my startup? Or all of them? If all of them, do I need to go back through my code and remove all the Active := True statements?

In the GetLoginParams function, what does the line do that reads:
LoginForm := TLoginFrm.Create(Application);

Thanks!
la
Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[svanels]

If during start up one of the queries is active, it will force a login into the database. This is the source of the annoying pop-up windows, asking for username etc.

In this case I use a login menu, that creates a custom login form (dynamically) LoginForm := TLoginFrm.Create(Application); This calls your custom made loginForm and the connection to the physical database is made only once. The connected property of the tdatabase must be set to false

By the way, which database are you using? Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

We have an AS400 (withholding disparaging comments here). Do you have a similar problem with blank login prompt allowing database access? What database do you use?

While investigating this problem I have had some conversations with my co-worker and we found that in a program he wrote the following appears to happen:

If a blank login prompt is submitted to the database, the USERNAME paramater is filled in with 'QUSER' (sort of an anonomous user) and an programmer generated error returns 'Invalid User Name. Program ending.' Then if you restart the application and enter a "real" user, somewhere the old user has been cached! The opposite is also true, if you sign on with a valid id & password, then exit and restart, login with a blank id & password, the valid one is still cached and it logs in properly. No where in the code does this cache occur, do you know if it's something that the BDE does?

So, I have decided to use your suggestion (co-worker thinks it's much more straight forward than anything he's done!). Another question though, when I create the 'LoginForm', do I need to do anything special to the Project Source Code (ie - the Application.CreateForm section?). And yes, you are right, I have a query that Active := True in the MainForm CreateForm procedure and that's where the LoginPrompt appears. I want to make sure the Login is successful so that the processes that happen in the CreateForm run each time and are not bypassed due to a bad login.

Thanks for all your help!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[lespaul]

Steven,

Thanks for the code, but I'm still having some problems. I added a check for '' text in the edtBoxes, but then I get a second login box! This second box doesn't do anything when the OK button is pressed, but on cancel gives me the error from the Connect function and then continues to open the program. If I put in bad information, I get the Connect function error and again the program opens. Thanks for any assistance!

function GetLoginParams(ALoginParams: TStrings): Boolean;
var
LoginForm: TLoginFrm;
begin
Result := False;
LoginForm := TLoginFrm.Create(Application);
try
if LoginForm.ShowModal = mrOK then
begin
if LoginForm.edtUserName.Text = '' Then Result := False
else if LoginForm.edtPassword.Text = '' Then Result := False
else begin
ALoginParams.Values['USER NAME'] := LoginForm.edtUserName.Text;
ALoginParams.Values['PASSWORD'] := LoginForm.edtPassword.Text;
Result := True;
end;
end;
finally
LoginForm.Free;
end;
end;

procedure TJMSData.DBmetroLogin(Database: TDatabase;
LoginParams: TStrings);
begin
GetLoginParams(LoginParams);
end;

procedure TJMSData.Logout;
begin
disconnect;
end;

procedure TJMSData.disconnect;
begin
DBmetro.Connected := false;
end;

function TJMSData.Connect: Boolean;
begin
try
DBmetro.Connected := true;
Result := true;
except
MessageDlg('Invalid Password or Login information, cannot LogIn.',mtError, [mbOk],0);
DBmetro.Connected := false;
Result := false;
end;
end;

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[svanels]

I do not know AS400 but I suspect that the username and password are defined somewhere inside the database.


You do not need to put the if then construction inside the loginform, this form is only to obtain the username and password. The MrOK result is only to verify if these parameters were submitted. You could capture in a global variable the user connected, xUser := LoginForm.edtUserName.Text;
and display it in a statusbar after a succesful login.


try
DBmetro.Connected := true;
Result := true;

tries to make the contact with your databaser server, and the login params are part of your connection string. If the information send is invalid, the connection will not be made and the message box will be called.
Once the connection is established, all the queries and tables will make use of this opening to the database, that is why they only are activated after the conntrolled login.

This whole story counted here will be in a blink of an eye, when the fuction login is called.

By the way the Loginform must be removed from the list of auto created forms in Project --> Options

Steven van Els
SAvanEls@cq-link.sr

 

[svanels]

I forgot the Login function

function DtaMod.Login: boolean;
begin
result := connect;
end;

My main program starts of with TabControl that is hidden from te user, once the connection to the database is established I enable things



example

procedure TMainFrm.mmiLogonClick(Sender: TObject);
begin
// Log the user onto the system
if MainDtaMod.Login then
begin
tcMain.Align := alClient;
tcMain.Visible := True;
tcMain.TabIndex := 0;

..etc ....

mmiScreen.Enabled := True;
mmiLogon.Enabled := False;
mmiLogoff.Enabled := True;
mmiAdministration.Enabled := True;

end;
end;


tc = TabControl
mmi = MainMenuItem

Hope this helpes you out Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

Is there also a subsequent LogOff mmi procedure? (I figure probably, but just thought I'd make sure!)

I may have also found another way (stole some code from a different co-worker) that does a repeat..until (don't have the code at home). So the Database Login boxes continues until it's successful. Basically,

If DB.Connected = True then exit
else if DB.Connected = False then
begin
repeat
begin
try
DB.Connected := True;
except on DBEError //(can't remember the exact variable)
ShowMessage('Invalid UserName/Password')
end;
until
DB.Connected := True;
end;
end;

I still haven't figured out why the second database login prompt appears if a blank username/password is submitted in the first database login. And it's weird, the OK button on the second login doesn't do anything if the fields are blank. If I press cancel, it still loads the rest of my program (but the Db isn't connected). But almost all of our systems require a username and password, everyone is used to having to log in to do things, so I really don't expect someone to submit a blank login prompt (I only 'discovered' it by accident trying to do error testing!). And even if someone does accidently submit a blank one, the second one will open! Thanks for all your help, everyday I realize how little I know about how much Delphi can do! But of course that means I learn something new every day! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[svanels]

Maybe putting a default user in edtUserName could get rid of that 2^nd login form, but I always thought that an AS400 was a sort of IBM computer.

The database, how do you make the connecton? through ODBC?

You are right about the logout procedure, I use it to clean things up and disable some menus and hide the tabcontrol again

But about the repeat -- until, you do not give the user a change to get out of the program (only the power button or Ctrl-Alt-Delete). Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

Yes the database connection is ODBC (and yes AS400 is IBM). The repeat..until, you're right about the no exit, I'm working on that today. Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[lespaul]

So here's what I ended up with:

function AS400 : boolean;
var
KeepTrying : boolean;
begin
If JMSData.DBmetro.Connected = True then Exit
else if JMSData.DBmetro.Connected = False Then
begin
KeepTrying := True;
While KeepTrying do
begin
try
JMSData.DBmetro.Connected := True;
KeepTrying := False;
except on EDBEngineError do
If MessageDlg('User/Password for AS400 sign on is invalid. Try again?',
mtError, [mbYes, mbNo],0) = mrNo then
begin
KeepTrying := False;
end;
end;
end;
end;
If JMSData.DBmetro.Connected = True Then Result := True
else if JMSData.DBmetro.Connected = False Then
begin
ShowMessage('Exiting JMS Program');
Result := False;
end;
end;

I then modified the Project Source (Options, only Datamodule is autocreate):

begin
Application.Initialize;
Application.CreateForm(TJMSData, JMSData);
If AS400 then
begin
Application.CreateForm(TfrmMainMenu, frmMainMenu);
//plus all other forms
end
else
Application.Terminate;
end.

Thanks for all your help, even though I ended up with nothing like yours, you helped point me in the right direction and gave me lots of information!

la
Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned