mysqldatabasedelphidata-access-layerdbexpress

Delphi: Return database names from MySQL using Metadata


I want to know if there is a way to return database names from MySQL using Delphi object TSQLConnection, I know that there is some methods that return table names or field names: TSQLConnection.getTableNames, TSQLConnection.GetFieldNames

but I can't find a way to get the databases on a specific server.

There s a method called OpenSchema in the TADOconnection object: TADOconnection.Openschema, that can return database names but in the TSQLConnection the method -protected not public- can't return database names.

P.S. I don't want to execute a query like 'show databases' or 'select * from information_schema.schemata'.

any body can help, thanks.


Solution

  • I tried this code and it worked, not sure if it will work for all MySQL, MariaDB versions and all Delphi versions but for me it workes, I am using delphi 6 and MySQL 4.0.25:

    function GetMySQLDatabaseNames(AUserName, APassword, AHostName, APort: string; var 
    AErrorMessage: String): TStrings;
    var SQLConnection: TSQLConnection;
        ObjectCursor: ISQLCursor;
        Status: SQLResult;
        Counter: Integer;
        Precision: Smallint;
        Value: Pointer;
        IsBlank: LongBool;
    begin
      Result:= TStringList.Create;
      SQLConnection:= TSQLConnection.Create(nil);
    
      with SQLConnection do
      begin
        ConnectionName:='dbnames';
        DriverName := 'mysql';
        Params.Clear;
        Params.Values['User_Name'] := AUserName;
        Params.Values['Password'] := APassword;
        Params.Values['HostName'] := AHostName;
        Params.Values['Database'] := 'mysql';
        Params.Values['Port'] := APort;
        LibraryName :='dbexpmda.dll';
        VendorLib := 'not used';
        GetDriverFunc :='getSQLDriverMySQLDirect';
        LoginPrompt :=False;
        try
          Connected := True;
    
          Status:= MetaData.getObjectList(eObjTypeDatabase, ObjectCursor);
          while Status = SQL_SUCCESS do
          begin
            Status:= ObjectCursor.getColumnPrecision(4, Precision);
            if Status = SQL_SUCCESS then
            begin
              Value:= AllocMem(Precision);
              Status:= ObjectCursor.getString(4, Value, IsBlank);
              if Status = SQL_SUCCESS then
                if not IsBlank then
                  Result.Add(PChar(Value));
            end;
            Status:= ObjectCursor.Next;
          end;
          Connected := False;
          Free;
        except
          on E: Exception do
          begin
            AErrorMessage:= AErrorMessage + E.Message+ sLineBreak;
          end;
        end;
      end;
    end;