Update SQL Wizard

SQL Wizard

SQL statements can be easily created with the Wizard .

Start a SQL Wizard (Updating the SQL statement)

To start the SQL wizard, enter [SQL Wizard] in the properties settings dialog in Execute SQL Wizard (Updating SQL Statement) in the database adapter.

Screen Structure of the SQL Wizard (Updating the SQL statement)

Data update setting screen (INSERT)



Numbering in the figure Name Description Remarks
(1) Change SQL statement type Specifies SQL statement type.
  • [INSERT]:(default)
    Insert data.
  • [UPDATE]:
    Update data.
  • [DELETE]:
    Delete data.
 
(2) Table list view Display a list of tables.
  • The maximum number of tables listed is determined by Global Resource settings.
(3) Table Information pane Displays the information on the selected table.
  • Displays table structures, table data, and table related information
  • For information displayed in the [Table structure] tab, refer to Table structure.
  • For information displayed in the [Table contents] tab, refer to Table contents.
  • For information displayed in the [Table relation information], refer to Table relation information.
(4) Add Column/Table Add the column selected in the [Table structure] tab in the Table Information Pane to the Insert Column Editor.
  • By selecting the table in the table list view and clicking [Add Column/Table], you can set all columns read-only.
  • Multiple tables cannot be added to the insert column editor.
(5) Insert Column Editor Set an insert value of the write column.
  • Depending on the data type of the write column, single quotes are automatically added to [Value to set].
    For information on the data types applicable to automatic addition, refer to "Automatic Addition of Single Quotes".
  • If a SQL parameter is specified, no single quote will be added.
(6) Up Move the write column up.  
(7) Down Move the write column down.  
(8) Remove Delete the write column.  
(9) Generate SQL Statement Automatically generate a SQL statement based on settings.  

Data update setting screen (UPDATE)



Numbering in the figure Name Description Remarks
(1) Change SQL statement type Specifies SQL statement type.
  • [INSERT]:(default)
    Insert data.
  • [UPDATE]:
    Update data.
  • [DELETE]:
    Delete data.
 
(2) Table list view Display a list of tables.
  • The maximum number of tables listed is determined by Global Resource settings.
(3) Table Information pane Displays the information on the selected table.
  • Displays table structures, table data, and table related information
  • For information displayed in the [Table structure] tab, refer to Table structure.
  • For information displayed in the [Table contents] tab, refer to Table contents.
  • For information displayed in the [Table relation information], refer to Table relation information.
(4) Add Column/Table Add the column selected in the [Table structure] tab in the Table Information Pane to the Insert Column Editor.
  • By selecting the table in the table list view and clicking [Add Column/Table], you can set all columns read-only.
  • Multiple tables cannot be added to the update column editor.
(5) Update column editor Set the update value of the write column.
  • Depending on the data type of the read column, single quotes are automatically added to [Value to set].
    For information on the data types applicable to automatic addition, refer to "Automatic addition of Single Quotes".
  • If a SQL parameter is specified, no single quote will be added.
(6) Up Move the write column up.  
(7) Down Move the write column down.  
(8) Remove Delete the write column.  
(9) Generate SQL Statement Automatically generate a SQL statement based on the settings.  

Data update setting screen (DELETE)



Numbering in the figure Name Description Remarks
(1) Change SQL statement type Specifies SQL statement type.
  • [INSERT]:(default)
    Insert data.
  • [UPDATE]:
    Update data.
  • [DELETE]:
    Delete data.
 
(2) Table list view Displays a list of tables.
  • The maximum number of tables listed is determined by Global Resource settings.
(3) Table Information pane Displays the information on the selected table.
  • Displays table structures, table data, and table related information
  • For information displayed in the [Table structure] tab, refer to Table structure.
  • For information displayed in the [Table contents] tab, refer to Table contents.
  • For information displayed in the [Table relation information], refer to Table relation information.
(4) Add Column/Table Add the column selected in the [Table structure] tab in the Table Information Pane to the Insert Column Editor.
  • By selecting the table from the table list view and clicking [Add Column/Table], you can add all columns to the delete column editor.
  • Multiple tables cannot be added to the delete table editor.
(5) Delete Table Editor Display the tables to be deleted.  
(6) Up Since it is not possible to specify several tables to be deleted, it cannot be used if [DELETE] is selected.  
(7) Down Since it is not possible to specify several tables to be deleted, it cannot be used if [DELETE] is selected.  
(8) Remove Delete the table from the delete table editor  
(9) Generate SQL Statement Automatically generate a SQL statement based on the settings.  

Condition Settings(UPDATE and DELETE)



Numbering in the figure Name Description Remarks
(1) Write Condition List View Display a list of update conditions or delete conditions for the write table.
  • SQL statements are generated in the listed order.
(2) Up Move the write condition up.  
(3) Down Move the write condition down.  
(4) Write Condition Editor Configure the write conditions for a write table.
  • The write conditions are shown in the write condition list view.
(5) Edit a write condition Add, edit and delete a write condition.
  • To edit a write condition, select the write condition in the write condition list view and click [Edit].
  • To add a new write condition, click [Add].
  • To delete a write condition, select the write condition in the write condition list view and click [Delete].
(6) Table, Column Set a name for the table and the column in which you specify write conditions.  
(7) Condition Type Configures the types of extraction conditions.
  • [(Enter directly)]
  • [Equal to]
  • [Not equal to]
  • [Greater than]
  • [Less than]
  • [Greater than or equal to]
  • [Less than or equal to]
  • If [(Enter directly)] is selected, the contents entered in the [Value] are directly added in a SQL statement.
  • If [Equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] =[Value]" format.
  • If [Not equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] !=[Value]" format.
  • If [Greater than] is selected, conditions are added to the SQL statement in the "[Table].[Column] >[Value]" format.
  • If [Less than] is selected, conditions are added to the SQL statement in the "[Table].[Column] <[Value]" format.
  • If [Greater than or equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] <=[Value]]" format.
  • If [Less than or equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] >=[Value]" format.
(8) Value Specify the condition for update and delete.
  • Depending on the data type of the read column, single quotation mark is automatically added to [Value].
    For information on the data types applicable to automatic addition, refer to "Automatic addition of Single Quotes".
  • If SQL parameter is specified, no single quote will be added.
  • If [(Enter directly)] is selected for [Condition type], please use the consistent format for each database.
    Example:
    • If you want to read the records in which the value of the condition column is "Product A", set as "='Product A'"
    • If you want to read the records in which the value of the condition column is "Product A" or "Product B", set as "IN('Product A', 'Product B'".
(9) AND/OR Specify the logic operator of multiple conditions.
  • [AND]
  • [OR]
 

Generated SQL statement and execution test



Numbering in the figure Name Description Remarks
(1) SQL statement tab Display an automatically generated SQL statement.  
(2) Execution result tab Display the result of test execution.  
(3) Error tab If an error occurs during test execution, the error is displayed in the error tab.  
(4) SQL Statement Editor You can edit an automatically generated SQL Statement.
Also possible to write SQL functions which cannot be generated in the SQL Wizard.
  • If a SQL Statement is edited manually, it is not possible to edit in the Wizard.
(5) Set a formal parameter If a script variable is used in the write condition, you can replace the script value to the set [Value] and execute a test to the SQL statement.
Enabled if [Specify the temporary values to arguments for execution test] is checked.
  • It is not possible to specify a formal parameter to an environmental variable or a SQL parameter.
(6) Execution test Execute a SQL statement.  

Generate SQL statement

To generate a SQL Statement, follow the steps below.

INSERT

  1. Select [INSERT] from the [Change SQL statement type] list.
    If already selected, no need to reselect DML.

  2. Select the writing destination table in the table list view and click [Add Column/Table]. (To specify a write column, select the column in the table information pane.)

  3. Set the insert value in the insert column editor.

  4. To generate a SQL statement with above settings, click [Generate SQL Statement].

  5. A SQL statement is displayed in the [SQL statement] tab.
    Click [Submit] to save the SQL statement and end the SQL wizard.

  6. To execute the SQL statement, click [Execution test].

  7. The results are shown in the [Execution result] tab.

  8. If an error occurs during test execution, the error is displayed in the [Error] tab.

  9. Click [Submit] to save the SQL statement and end the SQL wizard.

  10. To edit a SQL statement, click [SQL Wizard] in the properties settings dialog in the Execute SQL (Specifying SQL Statement).
    When SQL statement created by the SQL wizard has been manually edited, it cannot be re-edited by the wizard.

UPDATE

  1. Select [UPDATE] from the [Change SQL statement type] list.
    If already selected, no need to reselect DML.

  2. Select a writing destination table in the table list view and click [Add Column/Table]. (To specify a write column, select the column in the table information pane.)

  3. Set the update value in the update column editor.

  4. To generate a SQL statement with above settings, click [Generate SQL Statement].
    To specify the update condition, click [Next] and refer to "Specify Condition".

  5. A SQL statement is displayed in the [SQL Statement] tab.
    Click [Submit] to save the SQL statement and end the SQL wizard.

  6. To execute the SQL statement, click [Execution test].

  7. The results are shown in the [Execution result] tab.

  8. If an error occurs during test execution, the error is displayed in the [Error] tab.

  9. Click [Submit] to save the SQL statement and end the SQL wizard.

  10. To edit the SQL statement, click [SQL Wizard] in the properties settings dialog in Execute SQL (Specifying SQL Statement).
    When SQL statement created by the SQL wizard has been manually edited, it cannot be re-edited by the wizard.

DELETE

  1. Select [DELETE] from the [Change SQL statement type] list.
    If already selected, no need to reselect DML.

  2. Select a writing destination table from the table list view and click [Add Column/Table].

  3. To generate a SQL statement with above settings, click [Generate SQL Statement].
    To specify the delete condition, click [Next] and refer to "Specify Condition".

  4. A SQL statement is displayed in the [SQL statement] tab.
    Click [Submit] to save the SQL statement and end the SQL wizard.

  5. To execute the SQL statement, click [Execution test].

  6. The results are shown in the [Execution result] tab.

  7. If an error occurs during test execution, the error is displayed in the [Error] tab.

  8. Click [Submit] to save the SQL statement and end the SQL wizard.

  9. To edit a SQL statement, click [SQL Wizard] in the properties settings dialog in the Execute SQL (Specifying SQL Statement).
    When SQL statement created by the SQL wizard has been manually edited, it cannot be re-edited by the wizard.

Condition settings

To set conditions for update and delete, follow the steps below.
  1. In the [Condition settings] screen, click [Add].

  2. In the write condition editor, specify the column as [Table][Column].

  3. In the write condition editor, specify [condition type] and [Value].

  4. If conditions for multiple columns are required, set logic operators (AND/OR).
    Select from [AND] and [OR] from the [AND/OR] column list.

Automatic Addition of Single Quotes

If a column data type falls into one of the below, single quotes are automatically added to the [Value] set in the insert column editor, the update column editor, and the write condition editor.

Specification Limits