Database Scripts

Page ID: (AU209000)

On this page, you can manage the Sql and Table items of the customization project.

Tip:

An Sql item contains a custom database table definition or a custom SQL script that has to be executed while the customization project is published. A Table item contains a description of the custom columns added to a table for bound custom fields created in the appropriate data access class.

When you create a custom table in the database, we recommend that you add the table schema to the customization project, as described in To Add a Custom Table to a Project. To create other database objects or insert data into the tables, you have to compose the corresponding SQL script and add the script to the customization project, as described in To Add a Custom SQL Script to a Project.

Tip:

You open the Database Scripts page by clicking Database Scripts in the navigation pane of the Customization Project Editor.

Page Toolbar and More Menu

The page toolbar includes standard buttons and page-specific buttons and commands. For the list of standard buttons, see Page Toolbar. The page-specific commands can be shown as buttons on the page toolbar, as commands on the More menu, or in both places. These commands are listed in the following table in alphabetical order.


Command	Description
Add Custom Column to the Table	Opens the Add Custom Column to Table dialog box, in which you select a column of an existing table to be added to the customization project.
Add Custom Table Schema	Opens the Add Custom Table Schema dialog box, in which you select the table to be added to the customization project, and then click OK.
Add Script	Opens the Edit SQL Script dialog box, which you use to add and edit a custom SQL script.
Increase Column Length	Opens the Column Length Increase dialog box, in which you can specify the new length for the column.
Reload from Database	Updates the Table items in the customization project if the business events that correspond to these items have been modified by using a database administration tool, such as SQL Server Management Studio.

Add Custom Column to Table Dialog Box

This dialog box opens when you click Add Custom Column to Table on the More menu. In the dialog box, you can add custom columns of an existing table of MYOB Acumatica to the customization project.


Element	Description
The dialog box contains the following elements.
Table	The name of the table to which the column will be added.
Field Name	The name of the column to be added.
Data Type	The type of the column to be added.
This dialog box has the following buttons.
OK	Adds the Table item to the customization project and closes the dialog box.
Cancel	Closes the dialog box without making any changes.

Edit SQL Script Dialog Box

This dialog box opens when you click Add Script on the page toolbar or More menu. You can also open it by clicking the name of the script in the Object Name column of the table on the page, or by selecting the row with the script and clicking Edit on the page toolbar. The dialog box contains the following elements.


Element	Description
The dialog box contains the following elements.
Script Name	The name of the SQL script.
Priority	The priority of the script. The priority can have a value that ranges from 0 to 100. By default, the priority is 0.
Unlabeled text area	A text area that you can use to view and edit a custom SQL script.
This dialog box has the following buttons.
Specify Database Engine	Opens the Specify Database Engine dialog box. In this dialog box, you select the attribute of the script that indicates on which database servers the script can be executed (Microsoft SQL Server, MySQL Server, or both), and then click OK. The following options are available: Skip Batch on MySQL Server: Adds the `--[mysql: Skip]` attribute to the end of the text in the text area of the script editor. Skip Batch on Microsoft SQL Server: Adds the `--[mssql: Skip]` attribute to the end of the text in the text area of the script editor. Use Interpreter: Adds the `--[VmSql]` attribute to the end of the text in the text area of the script editor. For more information about the database script attributes, see Using the SQL Script Attributes.
OK	Saves the custom SQL script to the customization project and closes the dialog box.
Cancel	Closes the dialog box without making any changes.

Column Length Increase Dialog Box

This dialog box opens when you click Increase Column Length on the More menu. You use the dialog box to increase the size of a text column of an existing table of MYOB Acumatica.


Element	Description
The dialog box contains the following elements.
Table	The table in which the column should be modified.
Field Name	The name of the column that should be modified. Tip: Only text fields are available in the list because only their length can be changed.
Data Type	Read-only. The current type and size of the specified column.
New Length	The new length of the text field.
This dialog box has the following buttons.
OK	Creates a script to alter the column in the application database, adds the script to the table on page, and closes the dialog box.
Cancel	Closes the dialog box without making any changes.

Edit Table Columns Dialog Box

This dialog box opens when you click the name of an item of the Custom Columns type in the Object Name column in the table on the page, or when you select the row with the name of the custom column and click Edit on the page toolbar. You use the dialog box to edit the custom table.

The dialog box contains the following elements.

Table 1. Edit Table Columns Dialog Box: Table Toolbar
The table toolbar includes standard buttons and buttons that are specific to this table. For the list of standard buttons, see Table Toolbar. The table-specific buttons are listed below.
Button	Description
Add	Provides the following menu commands: Add New Column: Opens the Add Custom Column to Table dialog box, in which you can add custom columns of the selected table. Column Length Increase: Opens the Column Length Increase dialog box, in which you can increase the size of a text column of the selected table.

Table 2. Edit Table Columns Dialog Box: Table ColumnsThe columns of the table are described below.
Element	Description
Field Name	The name of the field of the database table.
Script Type	The type of the database script applied to the table.
Data Type	The type of the field of the database table.

Table 3. Edit Table Columns Dialog Box: ButtonThe dialog box contains the following button.
Button	Description
Done	Saves the custom columns of the table to the customization project and closes the dialog box.

Table

The table on the page contains the following columns.


Column	Description
Object Name	The name of the item added to the customization project.
Type	The type of the item added to the customization project. This column can contain one of the following options: Custom Columns Custom Table Schema Script
Priority	The priority of the SQL script. This is the value specified in the Edit SQL Script dialog box. The column can contain a value that ranges from 0 to 100.