View are available in most adapters and usually allow data changes such as UPDATE, DELETE and INSERT operations.
The simplest way to define and manage views is through the Enzo Manager interface, although it is possible to create views programmatically using SQL commands. To see the list of views available in an adapter, click on the adapter and select the VIEW tab. Once a view has been created, its name cannot be modified.
The view shown here points to a SharePoint List called 'US States'. The view defines a schema of columns with specific SQL Server data types that should match the remote object data types as closely as possible. In some cases remote systems may contain data types that are not supported by SQL Server, in which case you can try to use the string data type.
Each column can store a comment and options flags (option flags are discussed later).
Create a View
To create a view, select the New icon, or right-click on the list and choose New from the context menu.
Depending on the adapter, the user interface may vary slightly; however all views will require a unique name, and selecting or typing a remote object name. In some cases, you will also be able to type a SQL command, although most adapters will require you to build a SQL command using an editor.
Views are created in the context of the selected Enzo login and configuration setting.
SQL Command Editor
When available, you will be able to click on ellipsed next to the SQL Command field, which will open up a window in which you can enter an SQL command.
Many adapters support fetching a list of fields automatically for you to choose from. To create a SQL quickly, simply select the fields you would like to include from the list and click on Build SQL. If you would like to insert a field in an existing SQL, place the cursor where you would like to add the field, choose the field to add, and click on Insert. To add all available columns you can simply type a SELECT * FROM... command.
Most adapters also allow you to run the command to test it. Sometimes a SQL command can retrieve thousands of rows, in which case it could take a long time to execute. The checkbox at the bottom of the screen allows you limit the number of rows returned by automatically applying a TOP or LIMIT operator on the command. Click on Run Query to try the command.
Clicking on OK builds the list of columns with the correct data type and closes the query window.
You can edit columns individually using the column editor. This editor allows you to enter comments and optional flags. Note that if you recreate the columns using the SQL Editor, all comments and flags will be lost.
This editor allows you to edit the column name, enter an optional comment, change the data type and specify optional flags. You can also specify a default value if the column returns a NULL value from the source system.
Two optional flags are available: required and searchable. The required flag requires that the column be used in the WHERE clause of all SELECT, UPDATE and DELETE commands against this view. The searchable flag allows the column to be used as part of a WHERE clause but is not required. If the Searchable flag is not set on a field, it cannot be used to filter data from the view.
By default the searchable flag is set to true when building a new view
Calling a View
Once a view has been created on an adapter, it is now made available as a handler. For example, if a view called vUSStates has been created on the SharePoint adapter, the following command can be sent to Enzo:
SELECT * FROM SharePoint.vUSStates
And if the ID column of that view is marked as Searchable, the following command can be executed:
SELECT * FROM SharePoint.vUSStates WHERE ID=1
When executing a SQL command from a Linked Server, the fully qualified name is required:
SELECT * FROM [localhost,9550].bsc.SharePoint.vUSStates WHERE ID=1