Last updated
In the DQL View, you can execute single DQL queries.
To execute a DQL query, do one of the following:
Press F9 or Shift+Enter.
Go to the Edit menu>Run.
Click Run on the context menu.
Click the Run button on the toolbar.
Other actions you can perform in the DQL View:
Double-click a cell in the result grid to copy the value on the clipboard. Press Ctrl when double-clicking to insert the cell value into the code editor at the current cursor position. The latter can also be performed on the result grid's column headers to insert the respective column names as attribute names at the cursor position.
Click and hold the left mouse button for 1 second on any value representing a valid Documentum r_object_id to load the respective object into the Object Navigator (see Object Navigator).
Double-click the messages in the status bar to copy them to the clipboard. Move the mouse cursor over the status bar to display the current message in a tooltip, if it does not fit into the message bar area.
You can see information about the latest executed action and the query result in the Messages area.
The status bar displays the complete execution time of the query, including the transfer of the query results to dqMan. The pure server response time without data transfer is labeled as Server response time.
You can enter one or several DQL queries in the SQL Query input area. When executing the query, all the content in the input area is regarded as one query and transferred to the Documentum server for execution. However, if only a part of a query is selected, then only this part is executed.
Use semicolons as separators for two or more queries. Setting the text cursor on one query selects this query for execution.
Moreover, you can add comments. Every line beginning with the # or // characters is regarded as a comment line.
A number of syntax assistance functions supports the creation of DQL queries:
Command syntax: Click a DQL command and wait for the hint to view the correct command syntax.
Function syntax: The syntax of functions, such as Dateadd, Datediff and so on, is displayed automatically while typing the DQL command. You can activate syntax assistance by pressing F1 on your keyboard.
Auto completion: Several dynamic values, such as object names, group names and so on, or allowed function values are faded in automatically in the form of a selection list. You can copy the values from selection lists by pressing Enter.
You can manually activate the selection list anywhere in the command area by pressing Ctrl+Space. The list is filled context dependent with the likely values at the current position. If none of the pre-defined lists applies, the list is filled with the possible attribute names.
Some of the selection lists are read dynamically from the repository when opening the session. The values in these lists can be subject to changes by other activities that will not be recognized by dqMan. In this case, you can reload and refresh the lists by clicking Session menu>Refresh Valuelists.
In the DQL View, you can use queries the attribute labels of any locale instead of the original attribute name. dqMan will replace labels with the attribute names at execution. Attribute labels must be placed in square brackets within the query.
Select r_object_id,object_name,[ACL Name],[Application type]
from dm_document where …
The Results list contains the results of the DQL queries.
Use your mouse to resize the columns and rows and reposition the columns in the Results list.
To search through the entire table, go to Edit menu>Search. Typing one or several initial letters within one column will scroll to the first matching value.
You can right-click the desired Results list column head and select to sort it alphabetically. Clicking an item to scroll the column into the visible range of the list.
On the data area, a list of functions is faded in for object processing. Available functions are:
Edit Starts the Results List Inline Editor. (!!!Make this a hyperlink: 3.1.5 The Results List Inline Editor!!!)
Copy This function copies the selected values to the clipboard. Rows will be separated with a linefeed and a carriage return character; columns will be separated with tabs.
Copy with Header This function copies the selected values to the clipboard, including the header names of the selected columns in the first line. Rows will be separated with a linefeed and a carriage return character; columns will be separated with tabs.
Duplicate Duplicate copies all selected values in a separate data grid.
Add to compare list The selected objects are added to the Object Compare list.
Sort Sorts the result data ascending or descending by the selected column.
Goto... Enables the fast change to a line or a column for the Results list. Entering a number enables vertical scroll, entering a column header or selecting one from the will enables horizontal scroll.
Marks Used to select different objects in the Results list. You can toggle or scroll marks to the next or previous marked object. Marks are mainly used by the Compare Data function, but can also be set manually.
Object functions Thisis where user-defined functions for objects in the Results list are provided, such as Change ACL, Dump Object, Last SQL statement, Request PDF rendition and so on. You can manage object functions in the Options Dialog. Object functions are available depending on the selected type of document and the individual configuration.
Export to Exports the selected cells of the Results list to CSV or Microsoft Excel (you need to have Excel installed to be able to export to this format). If the amount of exported data exceeds the Excel limitations of 65535 lines and 256 columns, more than one Excel tab sheet will be used. You cannot export data to Excel if a remote Office 365 installation is used; if this is the case, use the Copy with Header command.
Generate Script Opens the Script Generator that lets you generate DQL or API scripts from the selected data. Additional type dependent script templates will also be available, if the appropriate object type is selected.
Plugin Functions If dqMan Plugins that supply object related functions are installed, these functions will be displayed at the end of the context menu.
You can edit values directly in the Results list, if the results contain the r_object_id attribute.
Press Enter to save any changes.
Pressing Esc or moving the focus to any other control will close the editor without saving.
Single Attributes For single attributes, a standard input field will be created in the corresponding cell of the grid. Some attributes display a list with possible values. This list is filled in either with the values of a data dictionary value assistance or with manually configured values from the file dqManValuelists.xml.
Repeating Attributes You can modify Repeating Attributes within a small sub grid. As DQL results do not show the correct order of repeating attribute values, the contents of this list can differ from the displayed values in the Results list. The sub grid offers an own context menu for inserting, deleting or reordering values of the repeating attribute.
Multiple Single Attributes If the Results list contains more than one result, you can edit a single attribute of multiple objects simultaneously. Select more than one value in a column and start the editor. The values you enter are applied to all the selected objects.
Linked Repeating Attributes
You can modify linked repeating attributes of one object such as r_accessor_name, r_accessor_permit and r_accessor_xpermit of dm_acl in a multicolumn sub grid. Select more than one column of repeating attributes and start the editor.
Multiple standalone or linked repeating attributes You can also modify standalone or linked repeating attributes of more than one object at the same time, if the selection is extended to more than one object (line) in the result grid. The values you enter are applied to all the selected objects.0
You can filter query results by the queried values using the attribute filter, which can be open for each column. You can combine filters and use them for multiple columns using an “and” operation.
To open an attribute filter for a column, click the button in the column header and then, in the filter dialog box, you can see the filter options depending on the column data type.
Click Apply Filter to activate the filter after selecting the filter options. If a filter is active, the filter button in the column header and the row numbers are highlighted.
Additionally, all filter buttons in column headers have a context menu.
| Function | What It Does |
|---|---|
Modify Filter | Opens the filter dialog box. |
Clear Filter | Clears the selected filter and updates the list. |
Filtered Columns | Displays the list of all active filters in the list. Select a column to scroll the list down to the corresponding column. |
Show marked objects only | Limits the filter to the marked objects, for example after using Compare Data function. |
Clear all OTHER Filter | Clears all other filters except for the selected one. |
Clear ALL Filter | Clears all filters. |
If at least one filter is active, the top left cell of the result grid also displays a filter button with an active context menu with one additional function: “Generate Predicate”. Executing this function generates a predicate (part of a "where" clause) from the current filter settings and copies it into the clipboard, enabling you to add it to the current query to replace the filter settings. Example:
(Upper(object_name) like 'DM%') and
(globally_managed in (0)) and
(acl_class < 3)
The Message Area logs the executed DQL commands and their results. The context menu offers some settings to influence the behavior:
| Function | What It Does |
|---|---|
Copy | Copies selected text to clipboard |
Clear | Clears message Area |
Word Wrap | Toggles word wrap |
Settings:
| Function | What It Does |
|---|---|
Auto Clear Log | Clears the Message Area before executing a query (this setting is ignored for auto repeated query). |
Log Query Results | Besides messages, DQL results will also be logged in the Message area. |
CSV Format for Query Results | Results of DQL queries will be logged in CSV format. |
Fixed Width for Query Results | Results of DQL queries will be logged as list with fixed width. Values can be truncated if too long. |
You can activate the Message Area from the View menu.
You can enter the Describe command in the DQL input window and it displays all attributes and selected characteristics for an object type.
Syntax:
Describe <type> [all|custom] [with name='<name>'][,locale=’<locale>{,<locale>}|all’]
Describe <table> [name='<identifier>']
Arguments:
| Function | What It Is |
|---|---|
<type> | Object type, such as |
[all|custom] |
|
<table> | Name of a registered table, such as |
name='<name>' | Part of or complete attribute name and/or column name. Wildcards are "*" or "%". |
locale=’locale’ | Locale name for the attribute labels. Multiple locales can be separated by comma. |
You can modify attribute labels directly in the Results list, using the edit function from the context menu. Changed labels are published immediately. To delete labels, simply enter an empty value; in this case, the inherited label from the supertype is displayed.
Examples:
| Function | What It Does |
|---|---|
Describe | Lists all |
Describe | Lists all |
Describe | Lists all customized |
Describe | List column definitions of the registered table |
The Describe command is also available from the context menu of the input area. The function is activated if the context menu is open on the name of an object type or a registered table. This call corresponds to the Describe <type or table> all command.
Describe options: The Describe options enable you to customize the columns and their order of the describe Results list. Available columns are predefined and you can select them by selecting the respective checkbox. Moving columns upwards or downwards alters the default order.
The column “owner” is only displayed if the argument all or custom is used and attributes from different type levels in the hierarchy are displayed. In this case, also the type owned attributes can be marked by selecting the Mark owned attribute labels checkbox. This functions gives a good overview whether a type uses the inherited label or overwrites it with own values.
You can save any DQL queries to the Favorites view for easy, quick access.
You can open Favorites from the Edit menu. To store a query, go to Edit menu>Save to Favorites, in which case either the whole content of the input area or only your selected content (if any) will be saved as favorite. When saving a query to Favorites, enter the following details:
Name Name of the query.
Category Select a category for your query, such as Workflow, ACL, Users and groups. Your favorited query will be grouped under the mandatory category you select at this point.
Query type Select the desired query type from the list, for example Select, Update, Execute etc.
Object type Select the desired object type from the list.
Show in Favorites (If Applicable) Select if you want your query to be displayed in the Favorites section.
Show in Repositories Click the Login history button and decide whether or not this future favorite will be displayed in the current repository or not.
Description Enter a meaningful description of the query.
DQL Query Displays the DQL query that you want to add to Favorites.
Favorite Menu To add a query to the Favorite menu, select the Add to Favorites Menu checkbox.
Docbase List enables the allocation of queries to one or more repositories. An exclamation mark placed in front of the repository name, e.g. „!testdocbase“, hides this query if this repository is active.
Repositories are stored by their repository ID. If you are working with clustered content servers or use fully qualified repository names, every repository needs to be listed only once.
To load a query from the favorites, click Edit menu>Load from Favorites.
A search filter is located at the top of the window, enabling you to freely combine your search criteria. Searchtext does a full_text search in all available favorite attributes. All queries that match the filter criteria are displayed in the center of the window. Double-click the query to copy it to the DQL View input area. If a description exists for a query, it will be displayed in the lower part of the window if the query is selected.
Additionally, you can manage your favorites in this dialog box by clicking the Manage button and display the additional button list:
Click Import to import existing favorites from your file system.
Click Export to export all selected queries to the file system.
Click Edit to modify the selected query.
Click Delete Query to remove all selected queries.
Click Save to preserve your changes.
You can add your most frequently used DQL queries to the Favorite menu by selecting the Add to Favorites checkbox in the Store to Favorites function.
All queries in this menu are grouped by the entered categories that in each case represent a submenu. From there the appropriate queries can be selected by their names. Selected queries are copied to the DQL command input area.
Pressing <Ctrl> when selecting a query will insert the query at the current cursor position. (Note however that template field options will not be merged automatically). Otherwise, the DQL command input area will be cleared before inserting the query.
Template Queries are DQL queries that contain placeholders for values. When executing a Template Query, you need to enter values for the placeholders.
Placeholders consist of a freely selectable name, enclosed by { } characters. You can insert placeholders either manually or with a function available in the context menu of the input window. Template Queries can contain any number of placeholders.
If placeholders appear in a query more than once, they always represent the same value.
Placeholder names are case sensitive!
Also, if for any reason an opening curved clip is needed in the DQL query for another purpose than for beginning a placeholder, mask it with a double opening curved clip, like … where object_name like ‘{{%’.
You can improve the behavior of placeholders by adding placeholder options, using the Edit template field option from the context menu.
| Name | Function |
|---|---|
Name | Name of the placeholder |
Label | Caption displayed in the input dialog. If no label is available, the name will be displayed. |
Default Value | The default value displayed in the input control. It remains valid until entering or selecting another value. |
Mandatory | Select this checkbox if the field is mandatory. |
Use cached query | If selected, the result of the DQL query used to fill the value assistance will be cached to improve performance, but will ignore the changes to the underlying values. Select Refresh Value lists from the Session menu to clear the cache. |
Various types of value lists are available in Value List Type, as follows:
| Name | What It Does |
|---|---|
None | No value list. |
Fixed List | A list with fixed values that must be predefined in the control Values. |
DQL Query | A DQL query that fills in the list dynamically. |
Date Picker | Displays a date picker control. |
File Open Dialog | Opens a File>Open dialog box. You can define a list of available file types in the control Values. |
Directory Selector | Enables you to select a directory from the local file system. |
Cabinet/Folder Selector | Enables you to select a cabinet or folder from the currently connected repository. |
If the result of a DQL query contains more than one attribute, the value of the first column will be displayed in the value list and the corresponding value of the second column will be inserted in the query.
In a fixed value list, you can make a distinction between the displayed value and the inserted value using the || separators (for example, Label||Value).
The query Select object_name, r_object_id from dm_documentwill fill the list with the object_name attribute of all dm_document objects. Selecting a name from the list returns the r_object_id for the placeholder value.
Using Check Syntax, you can verify the syntax of the current DQL query. This feature is only available, if a valid session exists.
You can format DQL queries automatically. The associated format templates are defined in the file dqManFormatTemplates.xml.
To format a query, click on the query in the DQL input area and select the function Auto Format Query from the context menu of the DQL input area.
The Query:
Select r_object_id, object_name, acl_name from dm_document
where object_name like 'dm_%' or object_name like 'xy_%' order by r_object_name, acl_name
Will be formatted to:
SELECT
r_object_id,
object_name,
acl_name
FROM
dm_document
WHERE
object_name like 'dm_%' or
object_name like 'xy_%'
ORDER BY
object_name, acl_name
To clear the formatting of a query (unformat), use the Unformat Query function from the context menu. Calling this function removes all tabs, multiple spaces, line feeds and carriage returns from the query, leaving a plain single line query.
The third formatting function Extract Query can be used to extract the DQL part out of a piece of program code. From this code:
String query = "Select * from pl_produkt (all) where i_chronicle_id = '" +
chronicle_id + "' and " +
"valid_from <= Date (Today) and " +
"Dateadd(day,1,valid_to) > Date(Today) and " +
"any r_version_label = '_NEW_'";
This query will be extracted:
Select * from pl_produkt (all) where i_chronicle_id = '' and valid_from <= Date (Today) and Dateadd(day,1,valid_to) > Date(Today) and any r_version_label = '_NEW_';
Note that the variable chronicle_id is removed, leaving an empty value to be filled …where i_chronicle_id = '' and…
DQL queries can be executed repeatedly and automatically in a selectable or freely definable interval, causing the results to be refreshed accordingly.
The function is available in the context menu of the DQL editor. (Repeat Query every…). After selecting the interval, a yellow notification bar is displayed at the top of the DQL editor counting down to the next query execution. Automatic execution can be stopped at any time by selecting Stop Repeat Query from the context menu or from the notification bar.
In case of automatic execution the message area will not be deleted and will display the full log containing all the executions.
To stop a running query, click the Stop button on the toolbar.
You can add repositories either manually or you can select them from a repository list by clicking the Run button. Multiple repository names must be comma separated.
To open the Favorite menu, go to the Extras menu and select Favorites or click the Favorites button on the toolbar.
