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.

This is a vendor support question (for SigmaTek) for a third-party application, and is not appropriate for StackOverflow. However, the fact that there are flags for `1 = Paradox, 2 = MSSQLServer or MSDE` indicates that it has to be one of those three - MySQL <> MSSQLServer/MSDE (MSSQLServer/MSDE are Microsoft products, MySQL isn't, and they are by no means direct replacements of each other). — Ken White, Jan 23 '13 at 18:41

score 0 · Answer 1 · answered Jan 24 '13 at 09:05

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 ..

I'm lifting this thread once more. Starting to have problems again with this old app and looking on paradox replacement. The app can handle paradox and ms sql. The best bet is firebird but having problem to understand how it works and how to move the dataset. I just wonder is there are something you can do in MySQL to change the type of the variable on the fly? I mean convert between boolean and tinyint when it's requested by the select statement from the app? — pernils, Aug 26 '13 at 13:36

score 0 · Answer 2 · answered Dec 29 '21 at 01:42

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.

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

2 Answers2