Devart Forums

Is there an 'easy' way to get 2 lists from a db
List 1 = list of all found tables
List 2 = list of all found fields (with type, default value...)

Please take a look at the TUniMetaData component. You can try the "Tables" and "Columns" values for the MetaDataKind property. Please note that the TUniMetaData component does not support retrieving of default values.

OK - I will give it a try (do you know of any samplecode that demonstrates the functions)

I have tried
procedure TfrmMain.btnMetadataClick(Sender: TObject);
var
Field: TStringField;
UniMetaData: TUniMetaData;
begin
UniMetaData := TUniMetaData.Create(nil);
try
UniMetaData.Connection := frmDB_Conn.conDBserver;
UniMetaData.Restrictions.Add('TABLE_NAME = tblsetup');
UniMetaData.MetaDataKind := 'Columns';
UniMetaData.Open;
while not UniMetaData.Eof do
begin
Field := UniMetaData.FieldByName('COLUMN_NAME') as TStringField;
lstFields.Items.Add(Field.Value);
UniMetaData.Next;
end;
finally
UniMetaData.Free;
end;
end;

But this gives me a list of all fieldnames in every db on the server.
How do I restrict it to one db and one table

To solve the problem delete spaces in the restriction string, like this:

OK - that was a small error

Now I will try and se if I can get it to show a list of tables in a db also.

(have been playing a bit with tha demo application supplied with uniDac - there are some errors when using MySQL on a Linux device. The tablenames are casesensitive and the name that the demo creates on the server and those used in code doesn't match. Just a comment)

I use the following code to show a list of tables in an MySQL db. But it doesn't work on MS SQL. What am I missing?

frmDB_Conn.conDBserver is my connection from my datamodule and Settings.DB_Database is a string containing the database name read from inifile.

procedure TfrmMain.FillTables;
var
Field: TStringField;
UniMetaData: TUniMetaData;
InfoList: TStringList;
begin
InfoList := TStringList.Create;
UniMetaData := TUniMetaData.Create(nil);
try
UniMetaData.Connection := frmDB_Conn.conDBserver;
UniMetaData.Restrictions.Add('TABLE_SCHEMA=' + Settings.DB_Database);
UniMetaData.MetaDataKind := 'Tables';
UniMetaData.Open;
while not UniMetaData.Eof do
begin
Field := UniMetaData.FieldByName('TABLE_NAME') as TStringField;
InfoList.Add(Field.Value);
UniMetaData.Next;
end;
cboTableNames.Items.Assign(InfoList);
finally
UniMetaData.Free;
InfoList.Free;
end;
cboTableNames.ItemIndex := 0;
end;

Actually the serverlist isn't available either if the server doesn't use named pipes as far as I can see.

For MS SQL Server you should use the TABLE_CATALOG restriction instead of TABLE_SCHEMA, like this:

OK - is there anyway to use UniScript here?

oz8hp wrote:OK - is there anyway to use UniScript here?

I did not fully understand what you mean?

I just thought if it was possible to do something like when using uniscript for SQL

SQL.Add(' {IF SQLServer} ');
SQL.Add(' ALTER TABLE ' + aTable + ' ADD ' + aField );
SQL.Add(' {ELSE} ');
SQL.Add(' ALTER TABLE ' + aTable + ' ADD COLUMN ' + aField);
SQL.Add(' {endIF}');

but I use a Settings property wher I have the db type

But the named pipes is still an issue I think - I never turn that on unless I absolutly have to

Yes, you can use TUniScript this way. You can find detailed information about macros usage in the "Unified SQL" topic of the UniDAC help.