CreateTable function?

[Gigasoft]

I'm in the process of converting from another set of MySQL components to MyDAC (Very impressed with the demo version, and I plan to purchase a lisense soon!).
I've noticed that there doesn't appear to be an simple way to create a new table from the fields defined in a TMyTable component. Previously there has been a CreateTable function to do this.
I realise I could just use a query to achieve the same outcome, but I was wondering if there was an existing function.

Thanks.

[Dimon]

MyDAC allows to create tables only using SQL statementes. For example, to create a table you should use the 'CREATE TABLE' statement, like this:
MyConnection.ExecSQL('CREATE TABLE table_name ...', []);

[Gigasoft]

Thanks, that's what I thought.
So I've created the following function to do just that:

Code: Select all

procedure CreateTable(aTable: TMyTable; Drop : Boolean = TRUE);
var
  MyQuery : TMyQuery;
  f,i : Integer;
begin
  MyQuery := TMyQuery.Create(nil);
  MyQuery.Connection := aTable.Connection;
  MyQuery.SQL.Clear;
  if Drop then begin
    MyQuery.SQL.Add('DROP TABLE IF EXISTS `' + aTable.TableName + '`');
    MyQuery.Execute;
    MyQuery.SQL.Clear;
  end;
  MyQuery.SQL.Add('CREATE TABLE `' + aTable.TableName + '` (');
  for f := 0 to aTable.FieldCount-1 do begin
    if aTable.Fields.Fields[f].FieldKind = fkData then begin
      MyQuery.SQL.Add('`' + aTable.Fields.Fields[f].Name + ' `' + GetFieldType(aTable.Fields.Fields[f]) + ' ' + iif(aTable.Fields.Fields[f].Required,'NOT NULL','') + ' default ' + iif(aTable.Fields.Fields[f].DefaultExpression  '', '''' + aTable.Fields.Fields[f].DefaultExpression + '''', 'NULL') + ',');
    end;
  end;
  for i := 0 to aTable.IndexDefs.Count -1 do begin
    if ixPrimary in aTable.IndexDefs.Items[i].Options then
      MyQuery.SQL.Add('PRIMARY KEY (' + aTable.IndexDefs.Items[i].Fields + ')')
    else if ixUnique in aTable.IndexDefs.Items[i].Options then
      MyQuery.SQL.Add('UNIQUE INDEX (' + aTable.IndexDefs.Items[i].Fields + ')')
    else
      MyQuery.SQL.Add('INDEX (' + aTable.IndexDefs.Items[i].Fields + ')');
  end;
  MyQuery.SQL.Add(') ENGINE=INNODB');
  MyQuery.Execute;
  MyQuery.Free;
end;

But apparently MyTable.IndexDefs can't be accessed if the table doesn't exist.
Why is this so?

[Dimon]

If a table doesn't exist you can use the TMyTable.IndexDefs object to create your own index description, like this:

Code: Select all

  MyTable.IndexDefs.Add(Name, Fields, Opt);