TSQLConnection to a user chosen DSN at runtime

Question

Hi,

I want to use dbExpress and a TSQLConnection object to allow the user of my application to connect to any of their ODBC dsn and run a SQL query against it.

I have it so the user can select a DSN, (by DSN i mean all the configured data sources listed in control panel - administrative tools - Data Sources (ODBC)), but once they have selected one I am not sure how to configure the TSQLConnection object.

I think I need to set :

SQLCon.connectionName

SQLCon.driverName

SQLCon.getDriverFunc

In the registry settings for a given DSN I have a 'DRIVER' entry that points to a DLL but nothing to indicate the 'getDriverFunc' value.

Does anyone have any advice or an example of setting up a TSQLConnection (or any other delphi db connection) to an ODBC DSN at runtime ?

Thanks

Answer 1

I have found that the best way to do it is to use a TADOConnection and simply specify the DSN in the connection string.

Answer 2

1. If you have to work with ODBC DSN's through dbExpress technologie, then you have to download and use "Kylix / Delphi open source DbExpress driver for ODBC" from http://open-dbexpress.sourceforge.net. There you can find connection examples, the function to list DSN. etc.
2. If you have something more simple, then you can use TADOConnection and MSDASQL OLEDB provider, which is OLEDB -> ODBC bridge provider. T
3. If you need an advanced ODBC support from Delphi, then you should look to 3d party products.

So, decide what you need, then I will be able to provide you some samples.

Answer 3

Havn't used DBexpress, but if you use ADB there is a premade method in the ADO unit.
I think you can use is and if needed you maybe change som part of the connection string it returns.

The method is ADODB.PromptDataSource;

Answer 4

Here how to do this in TADOConnection:

this is the Connection string for ODBC provider (system DSN):

Provider=MSDASQL;DSN=mySystemDSN;Uid=myUsername;Pwd=myPassword

and here is demo shows how you can use it to connect to DSN named 'SQLServerDSN' with user 'sa' and pasword 'pass':

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOConnection1.ConnectionString := 'Provider=MSDASQL;DSN='+
  'SQLServerDSN;Uid=sa;Pwd=pass';
  ADOConnection1.Connected := true;
end; 

I hope this will help.

Answer 5

Probably the best resource for what connection string to use is connectionstrings.com. For using ODBC with dbExpress, you will need an ODBC driver.