Now that results are present, the VirtualSpace Table can be created and connected to the columns using the tool:
Wizard to create the vsTable, the columns and the connections. Click on the table creation wizard to open a prompt to create the table with 4 columns named as the database columns; if asked to open the VirtualSpace, reply No. The database editor tree now shows the connection of the result to the table and columns instead of the VirtualSpace.
Database connector editor tree view
Now select the populate table tool:
Populates the connected vsTable with the rows of last result set.
The tool will ask you to populate twenty-three rows in the vsTable, and although it prompts you to go to the vsTable, select “No”. Hover the mouse over the table or over the columns in the VirtualSpace reference column to display the tooltip. The tooltip for the vsTable also displays the first five rows and the connections. By carefully moving the mouse into the tooltip – to avoid dismissing it – you can select a column header in the table. This will open the VirtualSpace Editor and focus the selected column.
VirtualSpace table editor
The SQL Builder supports the SQL Query Statement types SELECT, INSERT, UPDATE and DELETE. If the statement syntax does not match these types, the SQL builder helper parts are disabled. The SQL syntax is always verified and errors are shown with red squiggly underlines. Hover the mouse over this location to display a tooltip which will indicate the current problem. To re-enable the SQL Builder, correct the SQL syntax and make sure to have the correct types that are listed above.
Select the context menu item Omit Current Schema, followed by selecting the checkbox for SQL statement generation:
Statement generation for database connector
SELECT
EMPLOYEES.EMPLOYEENUMBER,
EMPLOYEES.LASTNAME,
EMPLOYEES.FIRSTNAME,
EMPLOYEES.JOBTITLE
FROM EMPLOYEES
JOIN OFFICES
ON EMPLOYEES.OFFICECODE = OFFICES.OFFICECODE
Database connector editor
As the statement builds up, the left side will show the result set columns with the SQL datatypes.
The Database Editor consists of an SQL Builder that helps you build your query statement. On the left of the Database Editor, you define the properties for the Input and the Result and the connections to a VirtualSpace.
Database Connector editor
SQL Builder
The top-right entry field is used to input or build the SQL query statement. The right-middle area is used to specify the tables and columns and to create join’s. The right-bottom part specifies sorting, conditions, grouping, etc.