CreateTable method
Previous  Top  Next

See Also Properties Methods Events Example
Unit
MySQLDataset

Applies to
TMySQLDataset TMySQLTable and TMySQLQuery components

Declaration
function TMySQLDatasetBase.CreateTable(const AExecSQL: boolean=False): string;

Description
If called, this method will recreate the underlying tablestructure in the current database. The TableName, FieldDefs and IndexDefs are used to form/build a complete 'create table if not exists ..' SQL statement which is sent to the MySQL server if AExecSQL is True, if false (default) then you can use the SQL result returned to do with as you please. This is also the method called if you have the doAutoCreate option set and which is also used by the TMySQLServer's CreateDatabase and soAutoCreate option. If this dataset's Options also includes the doDropIfExists option, then the compiled SQL will be prefixed with a "drop table if exists..", and similarly if doCreateTemp is enabled then a temporary mysql table will be created, thus a table visible only to your connection and only persisting at the server while your connection is alive.

NOTE: For this method to work the dataset must be of simple format, i.e. you can't use this method if you specified a JOIN in the SQL property which pulls data and subsequently FieldDefs and IndexDefs from multiple tables. You must also explicitly specify a TableName, this is the name that will be used for the new table. If no FieldDefs or IndexDefs are defined for this dataset, then a table, TableName, will be created containing only one AUTOINC column, ID, which is also the primary key.

The CreateTable with the doAutoCreate option and the TMySQLServer's CreateDatabase with the soAutoCreate option presents you with huge possibilities:
·For one thing, you can now design your complete mysql database at designtime in Delphi/Kylix. When you connect these, the whole database and tablestructures will be created for you (if the appropriate options have been selected)  
·Another possibility is that you can now distribute your application without having to worry about the target installation database structure, the MyComponents will create the whole database structure for you if it doesn't exist on the target mysql server, exactly as your application expects it.  

FieldDefs and IndexDefs convertion to MySQL, optional [not null] is added if you have specified faRequired in the Attributes of any TFieldDef:
TFieldDef.DataType
MySQL column types equivalent
ftAutoInc
int not null auto_increment
ftSmallInt
smallint [not null]
ftWord,ftInteger
int [not null]
ftLargeInt
bigint [not null]
ftFloat
float [not null]
ftCurrency
decimal(Precision,Size) [not null] // Parameters replaced with TFieldDef properties
ftDate
date [not null]
ftTime
time [not null]
ftDateTime
datetime [not null]
ftTimestamp
timestamp [not null]
ftString,ftWideString,ftFixedChar,ftMemo,ftMemo,ftVariant,ftUnknown
Size<=255 - If ftFixedChar or faFixed then "char(Size) [not null]" Else "varchar(Size) [not null]"
255<Size<=65535 - text [not null]
65535<Size<=16777215 - mediumtext [not null]
Size>16777215 - longtext [not null]
ftBlob,ftGraphic,ftBytes,ftVarBytes
Size<=255 - tinyblob [not null]
255<Size<=65535 - blob [not null]
65535<Size<=16777215 - mediumblob [not null]
Size>16777215 - longblob
ftBoolean
tinyint(1) [not null]
All other types
varchar(255) [not null]


IndexDefs convertion to MySQL is straightforward:
·if ixPrimary is specified for the TIndexDef, then this becomes " primary key ({Fields})"  
·if ixUnique is specified for the TIndexDef, then this becomes " unique index {Name} ({Fields})"  
·Else it becomes " index {Name} ({Fields semicolon/comma seperated})"  
where {Name} is the TIndexDef.Name and the {Fields} detonates the TIndexDef.Fields, semicolon or comma delimited