LassoScript Utility
Basics Browse Detail

[Database_ChangeField]

Tag Link [Database_ChangeField] Category Database
Type Process Source Available Yes
Support Preferred 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_ChangeField] changes an existing field in a MySQL database table. The syntax is very similar to [Database_CreateField] with the addition of an -Original parameter that specifies the name of the field whose definition is being changed.

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 a -Database and a -Table parameters that specify in which database and table the field should be changed. 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 -Original parameter specifies the name of the field which should be modified. The remainder of the parameters must be specified in order to fully define the new characteristics of the field. Any parameters which are not specified will be reset to their default.

The -Field parameter specifies the new name of the field to be changed and the -Type parameter specifies the type for the 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 modified in place by default. The -BeforeFirst parameter can be optionally specified to move the field to the start of the table. Or, the -AfterField parameter can be specified to name an existing field after which the field should be moved.

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_ChangeField-Database='Database Name',
  -Table='Table Name',
  -Original='Original Field Name',
  -Field='Field Name',
  -Type='MySQL Type',
  -Default='Default Value']

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

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

Parameters

Required Parameters
-Database The name of the database in which the field should be modified.
-Table The name of the table in which the field should be modified.
-Field The new name of the field being modified.
-Original The name of the field which should be modified.
-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 change an existing field in a Lasso MySQL Table:

Use the [Database_ChangeField] tag. The following code changes the CreationDate field in the MyPreferences table of the Site database by adding the -NotNull parameter. Notice that the name and type of the field are stated again in full.

[Database_ChangeField-Database='Site',
  -Table='MyPreferences',
  -Original='CreationDate',
  -Field='CreationDate',
  -Type='DATETIME',
  -NonNull]