Get fieldinfo

oz8hp · Post by **oz8hp** » Mon 08 Mar 2010 08:13

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

Challenger · Post by **Challenger** » Tue 09 Mar 2010 09:26

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.

oz8hp · Post by **oz8hp** » Tue 09 Mar 2010 09:49

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

oz8hp · Post by **oz8hp** » Wed 24 Mar 2010 09:28

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

Dimon · Post by **Dimon** » Wed 24 Mar 2010 14:34

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

Code: Select all

UniMetaData.Restrictions.Add('TABLE_NAME=tblsetup');

oz8hp · Post by **oz8hp** » Wed 24 Mar 2010 14:44

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)

oz8hp · Post by **oz8hp** » Mon 29 Mar 2010 08:12

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;

oz8hp · Post by **oz8hp** » Mon 29 Mar 2010 08:55

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

Dimon · Post by **Dimon** » Mon 29 Mar 2010 08:55

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

Code: Select all

UniMetaData.Restrictions.Add('TABLE_CATALOG=' + Settings.DB_Database);

oz8hp · Post by **oz8hp** » Mon 29 Mar 2010 09:01

OK - is there anyway to use UniScript here?

Dimon · Post by **Dimon** » Mon 29 Mar 2010 09:59

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

I did not fully understand what you mean?

oz8hp · Post by **oz8hp** » Mon 29 Mar 2010 10:10

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

oz8hp · Post by **oz8hp** » Mon 29 Mar 2010 10:15

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

Dimon · Post by **Dimon** » Mon 29 Mar 2010 13:25

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