Page 1 of 1
CreateTable function?
Posted: Fri 14 May 2010 06:56
by 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.
Posted: Fri 14 May 2010 07:29
by 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 ...', []);
Posted: Tue 18 May 2010 03:10
by 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?
Posted: Wed 19 May 2010 07:50
by 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);