Solved

How to get connect via ODBC and get list of DSN?

Posted on 2003-11-25
10
522 Views
Last Modified: 2010-04-05
I am using ADO right now and I like a way to connect to ODBC database as well.  How would I go about to doing this?  
Also, how can I get a list of DSN?

Examples appreciated.

TIA

K
0
Comment
Question by:Khaki
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 17

Expert Comment

by:geobul
Comment Utility
Hi,

Use 'Microsoft OLE DB Provider for ODBC Drivers' in your connection string. The basic connection string looks like:
Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files
where 'dBASE Files' is your DSN.

Regards, Geo
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
About getting a list of DSN entries from the registry:

User DSN: read subkeys in HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI
System DSN: read values in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

Regards, Geo
0
 
LVL 17

Accepted Solution

by:
geobul earned 100 total points
Comment Utility
One correction:
User DSN: read values in HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

The code is:

type
  TForm1 = class(TForm)
    lbDSN: TListBox;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

uses Registry;

// get list of user and system DSN
procedure DSNList(DSNList: TStrings);
var
  i: integer;
  sl: TStringList;
begin
 sl := TStringList.Create;
 try
  DSNList.Clear;
  with TRegistry.Create do begin
    try
      // System DSN
      RootKey := HKEY_LOCAL_MACHINE;
      if OpenKey('\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources', False) then begin
        GetValueNames(sl);
        if sl.Count > 0 then begin
          for i := 0 to sl.Count - 1 do begin
            DSNList.Add(sl[i]);
          end;
        end;
      end;
      // User DSN
      sl.Clear;
      RootKey := HKEY_CURRENT_USER;
      if OpenKey('SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources', False) then begin
        GetValueNames(sl);
        if sl.Count > 0 then begin
          for i := 0 to sl.Count - 1 do begin
            DSNList.Add(sl[i]);
          end;
        end;
      end;
    finally
      Free;
    end;
  end;
 finally
   sl.Free;
 end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  DSNList(lbDSN.Items);
end;

end.

Regards, Geo
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
I forgot to close the keys:

...
    if OpenKey('\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources', False) then begin
        GetValueNames(sl);
        if sl.Count > 0 then begin
          for i := 0 to sl.Count - 1 do begin
            DSNList.Add(sl[i]);
          end;
        end;
        CloseKey; // add this line here and one for the other key also
      end;

Regards, Geo
0
 
LVL 2

Expert Comment

by:TheLeader
Comment Utility
"geobul" you did not leave a gap in your answer ;)
good work
[not tested, just Mind_Compiler]
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Expert Comment

by:wilmsoft
Comment Utility
Give this a try... it  uses DBTables (at least in version 6)


try
  { fill a list box with alias names for the user to select from }
  Session.GetAliasNames(ComboBox1.Items);
except
exit;
end;//try.. execept
0
 

Author Comment

by:Khaki
Comment Utility
geobul and all,
 Thanks to all who answer.  I am just going to test out the answer first.

K
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
Thank you TheLeader :-)

wilmsoft,
TSession requires BDE to be installed and working ! Khaki is using ADO (i.e. no BDE), so that will not be a desired solution I think.

Regards, Geo
0
 
LVL 1

Expert Comment

by:wilmsoft
Comment Utility
I guess I miss understood the question... I thought he DIDN'T want to use the ADO....

SO, here is how you do connect to a list of DSN via ODBC using the ADO.

try
ADOConnection1.ConnectionString := PromptDataSource(Handle, Edit1.Text);
ADOConnection1.GetTableNames(Listbox1.Items,false);
execpt
end;
0
 

Author Comment

by:Khaki
Comment Utility
Thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now