createTableSql

Syntax

array createTableSql (
    string $tableName,
    string $fields,
    optional array $tableOptions
    )

Description

The method createTableSql() creates the SQL statement necessary to create a table in the database and populates it with columns defined in $fields option. In order to create the table, the output of the command must be executed with the executeSqlArray() method.

Parameters

$tableName

The table name must not yet exist in the database. Names must conform to any database specific requirements such as length limitations and name casing.

$fields is a string, not an array

$fields

A string holding information regarding the columns to create in the new table. For a detailed description of the format of this string, see addColumnSql().

$tableOptions

An optional array of information that adds information regarding the table creation. This array may also contain field specific information, for example information pertaining to auto-increment fields. Another feature of this option is that it can be used to provide database specific options to the table itself, e.g.

$tabname = 'TEST';
$flds    = 'column definitions....';
$opts    = array('MYSQL'=>"ENGINE INNODB,COMPRESSION 'ZLIB'",
                 'DB2'=>'TABLESPACE LARGE_SPACE');
 
$sql = $db->createTableSql($tabname,$flds,$opts);

In the example above, the values provided in $opts will be added to the table if the data provider matches that of the currently attached database.

Usage

/*
* create a data dictionary object, using the database connection
*/
$dict = NewDataDictionary($db);
 
$tabname = "UCTABLE";
 
$flds = " 
   COL1 C(32) NOTNULL DEFAULT 'abc',
   COL2 I  DEFAULT 0,
   COL3 N(12.2)
  ";
 
/*
* create the SQL statement necessary to create the table and its columns
*/
$sqlarray = $dict->createTableSQL($tabname, $flds);
 
/*
 * This returns (for DB2)
Array
(
    [0] => CREATE TABLE UCTABLE (
           COL1         VARCHAR(32) DEFAULT 'abc' NOT NULL,
           COL2         INTEGER DEFAULT 0,
           COL3         DECIMAL(12,2)
           )
)
*/
 
 
/*
* This statement actually creates the table
*/
$dict->executeSqlArray($sqlarray);

ADOdb