Need simple example to use TAdoConnection to connect to MySql default databasse

Question

Final answer:

This was not a Delphi problem, just configuration.

I use Xampp to provide the MySql server.

C:\xampp\mysql\bin>mysql.exe  --version
mysql.exe  Ver 14.14 Distrib 5.1.41, for Win32 (ia32)

so, the correct connection string is

'Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mysql;User=root; Password=;Option=3;'

(and don't forget to load the MySql ODBC 5.1 driver!

Short code to do it :

procedure TForm1.Button1Click(Sender: TObject);
  var AdoConnection : TAdoConnection;
      DataBase : String;
begin
  Try
    AdoConnection := TADOConnection.Create(nil);

    if AdoConnection.Connected then  // already connected?
    begin
      MessageDlg('Already connected', mtInformation, [mbOK], 0);
      Exit;
    end;

    begin
      DataBase := 'mysql';

      AdoConnection.LoginPrompt:=False;//dont ask for the login parameters
      AdoConnection.ConnectionString := 'Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=' + Database + ';User=root; Password=;Option=3;';

      AdoConnection.Connected := True; //open the connection

      MessageDlg('Connected to databse "' + DataBase + '".', mtInformation, [mbOK], 0);
    end;

  Except
    On E: Exception do
    begin
      MessageDlg('Cannot connect to databse "' + DataBase + '"!.' + #13 + #10 + 'Please report this problem (is MySql running?)', mtError, [mbOK], 0);
    end;
  end;    
end;

---

I found some old code and am trying to figure it out.

Here's some code to attempt to the default MySql d/b calld mysql as user root with no password..

const MYSQL_CONNECT_STRING_FROM_DELPHI =
  'Driver={MySQL ODBC 3.51 Driver};Server=%s;Database=%s;User=%s; Password=%s;Option=3;'; 

var AdoConnection : TAdoConnection;
AdoConnection.ConnectionString := 
       Format(MYSQL_CONNECT_STRING_FROM_DELPHI,['localhost',DataBase,'root','']);
AdoConnection.Connected := True;

(I could have used TADOCommand, but prefer just to use TAdoConnection throughout my code)

When I run it I get ELoException [Microsoft][ODBC Driver manager] Data source name not found and no default driver specified.

I have recently reinstalled both windows & delphi 7. Did I perhaps forget to install something else, or is it the code which is at fault?

---

Edit/update:

I just realized that I need to Get MySql ODBC connector from http://dev.mysql.com/downloads/mirror.php?id=367506

I did that and the error is now Unknown MySql Server host "localhost" (I have Xampp running an Apache server, so localhost should be fine)

---

Edit 2: This code previously worked on the development PC, but not on another. And, now after fresh install it does not work on the development machine. Maybe more a question of configuration than code, but does anyone have some code that I can compare, just in case?

Answer 1

Maybe you have Windows Vista or Windows 7, with IPv6 protocol enabled (that is default), and this is fine, but it seems that IPv6 has priority when localhost is resolved to an IP, so localhost = ::1 instead of localhost = 127.0.0.1

Try replacing localhost with 127.0.0.1 in your connection definition (ODBC or anything else).

The problem could be that MySQL server doesn't listen over IPv6, or is listening but the firewall is blocking. If you want to try to connect from another computer (another host) and if you can't solve that IPv6 issue (tweaking your Firewall or MySQL configuration) then you always could try to reach your server using its IP address instead of the host name.