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