Differences

This shows you the differences between two versions of the page.

--- dictionary:changetablesql [2015/09/16 01:15] – created mnewnham
+++ dictionary:changetablesql [2015/09/16 01:19] – [Description] mnewnham
@@ Line 1: / Line 1: @@
+====== changeTableSql ======
+~~NOTOC~~
+<WRAP right box>
+== See Also ==
+[[dictionary:column_attributes|Column Attributes Reference]]\\
+[[dictionary:executesqlarray|executeSqlArray()]]
+== Syntax ==
+  array changeTableSql (
+      string $tableName,
+      string $fields,
+      optional array $tableOptions=false,
+      optional bool $dropColumns=false
+      )
+</WRAP>
+===== Description =====
+The method ''changeTableSql()'' creates the SQL statement necessary to make basic changes to a table in the database and populates it with columns defined in ''$fields'' option. If the table does not exist, it behaves like [[dictionary:createtablesql|createTableSql()]]. In order to change the table, the output of the command must be executed with the [[dictionary:executesqlarray|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.
+---------
+<WRAP right tip 200px>
+$fields is a string, not an array
+</WRAP>
+==== $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 [[dictionary:addcolumnsql|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.
+==== $dropColumns ====
+By default, any columns not specified in the ''$fields'' parameter are not changed. If the value of ''$dropColumns'' is set to true, then any column not specified in that value will be dropped.
+===== Usage =====
+<code php>
+/*
+* create a data dictionary object, using the database connection
+*/
+$dict = NewDataDictionary($db);
+$tabname = "LCTABLE";
+/*
+ * Change the length of COL1 from 32 chars to 60
+ */
+$flds = "
+   COL1 C(60) NOTNULL DEFAULT 'abc',
+  ";
+/*
+* create the SQL statement necessary to create the table and its columns
+*/
+$sqlarray = $dict->changeTableSQL($tabname, $flds);
+/*
+ * This returns (for DB2)
+Array
+(
+    [0] =>  ALTER TABLE LCTABLE ALTER COLUMN COL1 SET DATA TYPE VARCHAR(60) DEFAULT 'abc'
+)
+*/
+/*
+* This statement actually creates the table
+*/
+$dict->executeSqlArray($sqlarray);
+</code>