Make a ODBC connection from a delphi-7 application throu ADOConnectionstring

Question

Background :

Application written in Delphi-7 or -6 (have search through the .exe file). Called Sigmanest.

I have moved to new server and that's left is SigmaNest database running under SQL server. After many hours of troubleshooting for migrate that database to new server. The trouble have covered all aspect of this SQL server app. services not started at default, non working wizard, lack of full-text something, ridiculous many places to right click etc .. The lost goes on and on.

At the moment I have a working SQL server with the database SNDBase (sigmanest) at the new hardware but not be able to connect from client.

Na this can't be right I thought and searched for alternatives.. 4-5 click in MySQL workbench and I have it up an running on test linux box... Fine ..

But now it comes to problem.

SigmaNest uses a ini files for its config.

So inside one ini file I found

; 1 = Paradox, 2 = MSSQLServer or MSDE
ADOConnectionString=Provider=SQLOLEDB.1;Data Source=ODIN\SIGMANEST;User ID=sigmanest;Password="";Persist Security Info=True;Initial Catalog=SNDBase;

Okej .. installed Mysql odbc driver on the client and made the connection . All working so far ..

Turned to google and found the ADOConnectionsstring for mysql ...

ADOConnectionString=DRIVER={MySQL ODBC 5.2a Driver};SERVER=192.168.100.19;PORT=3306;DATABASE=SNDBase;UID=sigmanest;PASSWORD=;OPTION=4;

But the app will not start . just return to sigmanest's config tool for db connection.

Have posted this to SigmaTek but the only response I have been giving is a mail with ADOConnection string for MS sql server through SQLOLEDB.

Have searched the drive and haven't found anny dbex*.dll files witch means that they don't uses dbExpress component (my guess).

So is there some missing dll files that didn't come with the app. Or is this kind of thing hardcoded inside the program?

Anyone having a idea how to proceed ?

Or should I drop the mysql dream and go for the waste of space sql server backend.

Per Nils

PS. The SigmaNest.exe have a time stamp 2006-05-19 DS.

Answer 1

ODBC can be configured at runtime, below is an example of configuring ODBC to connect to SQL Server using BDE.

unit uBDEConnectionSqlServer;

interface

uses
    DBTables, Windows, Classes, SysUtils;

type TBDEConnectionSqlServer = class(TComponent)
  private
    { Private declarations }
    function CreateOBDCConnection(dataBase : string; server: string):Boolean;


  public
    { Public declarations }
    Function CreateBDEConnection(dataBase: TDatabase; dataBaseName : string; server:string; userName:string; password:string): Boolean;

  end;
  
  const
      ODBC_ADD_DSN = 1; // Add data source
      ODBC_CONFIG_DSN = 2; // Configure (edit) data source
      ODBC_REMOVE_DSN = 3; // Remove data source
      ODBC_ADD_SYS_DSN = 4; // add a system DSN
      ODBC_CONFIG_SYS_DSN = 5; // Configure a system DSN
      ODBC_REMOVE_SYS_DSN = 6; // remove a system DSN
      ODBC_REMOVE_DEFAULT_DSN = 7; // remove the default DSN
      
  function SQLConfigDataSource(
        hwndParent: HWND;
        fRequest: WORD;
        lpszDriver: LPCSTR;
        lpszAttributes: LPCSTR): BOOL; stdcall; external 'ODBCCP32.DLL';

implementation

Function TBDEConnectionSqlServer.CreateBDEConnection(dataBase: TDatabase; dataBaseName : string; server:string; userName:string; password:string): Boolean;
var
  retorno: TDatabase;
Begin
  result := false;

  if (CreateOBDCConnection(dataBaseName, server) = true) then
  begin
    dataBase.AliasName := 'testedelphi';
    dataBase.LoginPrompt := False;
    dataBase.DatabaseName := 'testedelphi';
    dataBase.Params.Values['DATABASE NAME']       := dataBaseName;
    dataBase.Params.Values['USER NAME']           := userName;
    dataBase.Params.Values['ODBC DSN']            := dataBaseName;
    dataBase.Params.Values['OPEN MODE']           := 'READ/WRITE';
    dataBase.Params.Values['BATCH COUNT']         := '200';
    dataBase.Params.Values['LANGDRIVER']          := '';
    dataBase.Params.Values['MAX ROWS']            := '-1';
    dataBase.Params.Values['SCHEMA CACHE DIR']    := '';
    dataBase.Params.Values['SCHEMA CACHE SIZE']   := '8';
    dataBase.Params.Values['SCHEMA CACHE TIME']   := '-1';
    dataBase.Params.Values['SQLPASSTHRU MODE']    := 'SHARED AUTOCOMMIT';
    dataBase.Params.Values['SQLQRYMODE']          := '';
    dataBase.Params.Values['ENABLE SCHEMA CACHE'] := 'FALSE';
    dataBase.Params.Values['ENABLE BCD']          := 'FALSE';
    dataBase.Params.Values['ROWSET SIZE']         := '20';
    dataBase.Params.Values['BLOBS TO CACHE']      := '64';
    dataBase.Params.Values['BLOB SIZE']           := '32';
    dataBase.Params.Values['PASSWORD']            := password;
    result := true;
  end;
  
end;

function TBDEConnectionSqlServer.CreateOBDCConnection(dataBase : string; server: string):Boolean;
var
  resultado: BOOL;
begin
  Result := False;
  resultado := SQLConfigDataSource(
  0,
  ODBC_ADD_DSN,
  'SQL Server',
  PChar(
    'DSN='+dataBase+#0 +
    'SERVER='+server+#0 +
    'ADDRESS='+server+#0 +
    'NETWORK=dbmssocn'#0 +
    'DATABASE='+dataBase+#0 +
    'DESCRIPTION='+server+dataBase+#0 +
    #0
  )
  );

  if(StrToInt(BoolToStr(resultado)) <> 0) then
    Result := True;
end;

end.

Answer 2

Sorry but I didn't know where to go with this issue. And stackoverflow seems to have gadder ed all the talent people in the world under the same roof so to speak.

Ken White : Yes you are right I can't use MySQL on this application (after hours of googling and testing).

Anyway I managed to make the connection like this (maybe something is usefully for others some of it is Delphi related)

• First you have to download the mysql connector http://dev.mysql.com/downloads/connector/odbc/

• Make a ODBC conenction from control panel-> administration tools -> Data sources (ODBC) under tab "User DSN"

• The you make a new text file with notepad.

• Rename the file with extension .udl

• Double click on it and fill in the dialog boxes ...

• Open the file in notepad and there you have your adoconnection string.

But your problem is not over.. Difference in SQL vs MySQL will make your app to halt .. for example boolean in MySQL is declared as a tinyint (0=false 1=true)

A workaround is mention here http://www.i-logic.com/utilities/MySQL.htm

So my struggle gave nothing in return but some of above could be a interested for others.

Per Nils ..