connect to oracle without prompting for login/password

tbrown12
tbrown12 used Ask the Experts™
on
This is my first delphi program and I am using
TDBGrid,TQuery and TDatasource to create a spreadsheet populated from the Oracle database.

The GOOD NEWS is that it works.
The BAD NEWS is that I want to hardcode the userid/password into the program so the user doesn't see it and I can't figure out how to do it.

Here is the simple program:

begin
Query1.Close;{close the query}
//assign new SQL expression
Query1.SQL.Clear;
Query1.SQL.Add ('select * from table1');
Query1.Open; {open query + display data}
end;



Unsuccessfully I have tried:
1) to add a connect statement in the SQL to connect to the oracle database so that there is no prompt for userid/password@aliasname, it generated an out of memory error.

2) incorporate a TSQLConnection object but I still get prompted for the userid/password, even though I have login set to false. Perhaps it is not connected correctly to the TQuery object; I looked and looked for how to do this couldn't get it.

3) insert parameters into Query1.params
    0 USER NAME=
    1 PASSWORD=
    2 LoginPrompt=

but it still prompts for username/password.

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
VGR

Commented:
yes, the idea is to fix solution 1)

it MUST work
Top Expert 2004

Commented:
you need a tdatabase, where you set

   0 USER NAME=
   1 PASSWORD=
   2 LoginPrompt=

use the tdatabase as databasename of your query
Top Expert 2004

Commented:
btw. by dbexpress just replace TDataBase with TSQLConnection
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Commented:
if you use BDE, you can create in BDE Administrator alias
for Oracle database. Then in Delphi you must use TDatabase in which select created alias. In Params write
USERNAME=you user name
Password=you password
Then in TDatabase set LoginPromt property to False.

Also you can use ADO connection or other third party components as DirectOracle. I recommend to you
Direct Oracle.
You can see this components at www.crlab.com



Author

Commented:
I have set the Params of Query1 as
  0 USERNAME=
  1 Password=
with the correct values for these.

I have an alias for the oracle database already; it shows up for the DatabaseName of DataSource1

I have created SQLConnection1 and set it's Params also, but I still get the connection prompt.


When I substitute 'SQLConnection1' for the DatabaseName of DataSource1; an exception is thrown and the program halts.

Then I have setup

Commented:
Try this code :

begin
 Query1.Close;{close the query}

 Query1.Database.Params.Add('Username <you user name>);
 Query1.Database.Params.Add('Password <you password>);
 Query1.Database.LoginPrompt:=False;

//assign new SQL expression
 Query1.SQL.Clear;
 Query1.SQL.Add ('select * from table1');
 Query1.Open; {open query + display data}
end;

Commented:
I am sorry it's don't work
in any case you need TDatabase.
You must select alias in TDatabase and
in TQuery select this Database.
Mohammed NasmanSoftware Developer

Commented:
Hello

as all suggest to you, you have to use TDatabase, here's the code i use in my projects, to prevent the oracle login dialog to show to the users

  try
    Database1.Connected := False;
    Database1.LoginPrompt := False;
    Database1.Params.Values['user name'] := Edit1.Text;
    Database1.Params.Values['password'] :=  Edit2.Text;
    Database1.Connected := true;
  except
    MessageDlg('Error login to database', mtError, [mbYes],0);
  end;

Commented:
Hai tbrown12,

  The code is slighlty to be changesd. Please find below sample code.

  DataB.Connected      := False;
  DataB.LoginPrompt    := False;
  DataB.KeepConnection := True;
  DataB.AliasName      := AliasName
  with DataB.Params do begin {These params are used for auto login}
      Clear;
      Add('USERNAME='+ Trim(Edit1.Text));
      Add('PASSWORD='+ Trim(Edit2.Text));
    end;

    try  { all parameters are set, now we can connect}
      DataB.Connected := True;
    except
      On E:Exception do {give it a second chance!}
      try
        DataB.LoginPrompt := True;
        DataB.Connected   := True;
        DataB.LoginPrompt := False;
      except
        On E:Exception do
        Raise Exception.Create( aLoginName+ ' could not connect to database. Reason: '+E.Message);
      end;
    end;

Please obey the uppercase for the username and the password.

Hope the above help u.

Regards
Vensan

Author

Commented:
When I add the below code after the Query1.close statement in the existing code, I immediately get an access violation at the first line. When I remove it, the program works but prompts for the id/password.


Query1.Database.Connected := False;
Query1.Database.LoginPrompt:=False;
Query1.Database.Params.Add('Username=user1');
Query1.Database.Params.Add('Password=password1');
try  { all parameters are set, now we can connect}
     Query1.Database.Connected := True;
   except
     On E:Exception do {give it a second chance!}
     try
       Query1.Database.LoginPrompt := True;
       Query1.Database.Connected   := True;
       Query1.Database.LoginPrompt := False;
     except
       On E:Exception do
       Raise Exception.Create( ' Could not connect to database. Reason: '+E.Message);
     end;
   end;

Author

Commented:
When I add the below code after the Query1.close statement in the existing code, I immediately get an access violation at the first line. When I remove it, the program works but prompts for the id/password.


Query1.Database.Connected := False;
Query1.Database.LoginPrompt:=False;
Query1.Database.Params.Add('Username=user1');
Query1.Database.Params.Add('Password=password1');
try  { all parameters are set, now we can connect}
     Query1.Database.Connected := True;
   except
     On E:Exception do {give it a second chance!}
     try
       Query1.Database.LoginPrompt := True;
       Query1.Database.Connected   := True;
       Query1.Database.LoginPrompt := False;
     except
       On E:Exception do
       Raise Exception.Create( ' Could not connect to database. Reason: '+E.Message);
     end;
   end;
Commented:
you get an access violation because you haven't TDatabase.
Can you put it in your project ?????
And work with it ?
You have access violation because you don't assign TDatabase to you Query1!!!
If you check Query1.Datatabase property in run-time you can see that it's nil
and then
begin
Query1.Close;{close the query}

Query1.Database.Params.Add('Username <you user name>);
Query1.Database.Params.Add('Password <you password>);
Query1.Database.LoginPrompt:=False;

//assign new SQL expression
Query1.SQL.Clear;
Query1.SQL.Add ('select * from table1');
Query1.Open; {open query + display data}
end;

or you can one time
Database1.Params.Add('Username <you user name>);
Database1.Params.Add('Password <you password>);
Database1.LoginPrompt:=False;

and then simple connect with one or more queries


Mohammed NasmanSoftware Developer

Commented:
as all the commect before, you should use TDatabase component, simply add TDatabase, and try the code I gave you above, or anycode from the comment, and everything will work fine

Author

Commented:
http://cobfaculty.stcloudstate.edu/rheath/ClientServer/Readings/15%20Aliases%20ODBC.pdf

Take a look at the above link.
Because I am new to Delphi, I needed the LONG EXPLAINATION provided at the above website on Aliases/DatabaseName.
The main problem was that I did not know how to connect TDatabase to TQuery

Mastadon,kretzschmar,mnasman all provided the correct answer but becuase I am VERY new to Delphi, I couldn't implement what they said without more details.

Thank you all for your input. It helped me alot.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial