LassoScript Utility
Basics Browse Detail

[Database_CreateColumn]

Tag Link [Database_CreateColumn] Category Database
Type Process Source Available Yes
Support Synonym Version 5.0
Change Unchanged Data Source Lasso MySQL
Output Type None Security Tag
Implementation LDML Sets Lasso 8.5, Lasso 8.0, Lasso 7.0, Lasso 6.0, Lasso 5.0

Description

[Database_CreateColumn] creates a new field in an existing MySQL database table. By calling the [Database_CreateTable] tag and a series of [Database_CreateField] tags a new table can be created entirely in LassoScript.

This tag is certified to work with Lasso MySQL and MySQL data sources. It may also work with third party data sources that support the -SQL command tag.

This tag requires -Database and -Table parameters that specify in which database and table the field should be created. The database and table must already exist and the current user must have permission to execute -SQL actions. The database and table must be specified using the proper capatilization in order for this tag to work properly.

The -Field parameter specifies the name of the field to be created and the -Type parameter specifies the type for the new field. The -Type parameter must be set to a valid MySQL type. The valid types are summarized below.

An optional default value for the field can be specified in the -Default parameter. If the field is not set to an explicit value when a new record is added then this value will be used for the field.

The field is added to the end of the table by default. The -BeforeFirst parameter can be optionally specified to create the field at the start of the table. Or, the -AfterField parameter can be specified to name an existing field after which the field should be added.

Key fields can be created by specifying the option -Key parameter. In general only one key field can be created. Similarly, an integer field can be set to auto increment using the -AutoIncrement parameter. In general only one field can be set to auto increment.

A field can be set to accept NULL values by specifying the -Null parameter or can be set not to accept NULL values by specifying the -NotNull parameter.

The valid MySQL data types are as follows:

Integers: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Each integer type can also be unsigned as in TINYINT UNSIGNED.

Decimals: FLOAT, DOUBLE, DECIMAL(Length, Precision).

Strings: CHAR(Length), VARCHAR(Length), TINYTEXT, MEDIUMTEXT, TEXT, LONGTEXT.

Binary: CHAR(Length) BINARY, VARCHAR(Length) BINARY, TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB.

Dates: DATETIME, TIMESTAMP, DATE, TIME, YEAR.

Syntax

[Database_CreateColumn-Database='Database Name',
  -Table='Table Name',
  -Field='Field Name',
  -Type='MySQL Type',
  -Default='Default Value']

[Database_CreateColumn-Database='Database Name',
  -Table='Table Name',
  -Field='Field Name',
  -Type='MySQL Type',
  -BeforeFirst,
  -Null]

[Database_CreateColumn-Database='Database Name',
  -Table='Table Name',
  -Field='Field Name',
  -Type='MySQL Type',
  -AfterField='Field Name',
  -Key,
  -AutoIncrement,
  -NotNull]

Parameters

Required Parameters
-Database The name of the database to which the field should be added.
-Table The name of the table to which the field should be added.
-Field The name of the field which should be added.
-Type The MySQL type of the field which should be added.
-NotNull Specifies that null values should not be allowed in the field. Cannot be used in conjunction with -Null.
Optional Parameters
-Default The default value the field should be set to on new records if no specific value for the field is defined.
-AutoIncrement If specified, the field will auto-increment each time a new record is created. Only one auto-increment field can exist per table. Usually used only on primary key fields.
-Key If specified, the field will be set as the primary key for the table. Only one primary key can exist per table.
-Null Specifies that null values are allowed in the field. The default.
-AfterField The name of the field after which this field should be created. Defaults to adding the field to the end of the table. Cannot be used in conjunction with -BeforeFirst.
-BeforeFirst Specifies that the field should be inserted before the first field in the table. Cannot be used in conjunction with -AfterField.

Examples

To create a new field in a Lasso MySQL table:

Use the [Database_CreateColumn] tag. The following code creates a new field in the MyPreferences table of the Site database. The new field is named CreationDate and is set to data type DATETIME.

[Database_CreateColumn-Database='Site',
  -Table='MyPreferences',
  -Field='CreationDate',
  -Type='DATETIME']