enaio® editor-for-reports: SQL Queries

enaio® 11.10

SQL queries are queries that allow users to access data in the database by using enaio® client:

regardless of the access rights defined for the archive objects; and
regardless of the filing system of the archive objects.

You can, for example, query all registers where a specific document type is not yet available. You can query the number of pages scanned at a workstation within a specified time period in order to optimize the use of scanners. Access to precisely defined index data can be granted to users.

The results of a query can be passed to a VB script macro which is executed automatically or by the user.

The consulting team at OPTIMAL SYSTEMS will help you to set up SQL queries if you wish.

In the profile administration area, you can provide users access to SQL queries or send them to users from the object search. SQL queries are sent in encrypted form.

SQL queries can be used to modify SQL statements and VB script data and delete them from the database. If you want to prevent this, change the write permissions in the Server properties > Category: Data > ADO database access area of enaio® enterprise-manager.

SQL queries can only be set up by users who have the 'Client: Edit SQL queries' system role in the security system and the 'OSM' license key installed on their workstation.

Users who have the 'OSE' license key on their workstation and who have the 'Client: Execute SQL queries' system role can execute SQL queries. You can assign SQL queries to these users in the profile administration area.

The 'Run SQL command' automatic action also allows you to execute SQL statements and to send the results as record sets to VB scripts. Unlike SQL queries, this action can be used to modify data in the database.

Setting Up SQL Queries

SQL queries are set up in enaio® client.

Locate the Desktop folder in the Object search area, open the folder context menu, and select SQL query in the menu.

Use this entry to open the SQL query window.

Enter a name for the extended query.

You can choose whether the results of the query should be an SQL hit list or a list of DMS objects.

Results List as DMS Objects

Unlike the results list containing SQL hits, a results list containing DMS objects is a hit list that provides all standard functions for editing and organizing hits in enaio® client. Actions are not configured, nor are headers. The columns of the hit list will vary based on the settings for hit lists in enaio® client.

As an SQL command, only statements in the 'select id from object' format are allowed. As with queries with results lists as SQL hits, the queries may contain variables which are queried via a dialog. The variables can be set as defaults.

Example:

The query detects all documents of a type which are shared with a user who is specified via a preset variable containing his or her user name.

select d.id from object28 d, osdoccollaboration c, benutzer b
where d.id=c.doc_id and b.id=c.to_user and b.benutzer like '$Benutzer,C30@#BENUTZER#$'

Results List as SQL Hits

The SQL command is added to the SQL command area.

In the Header area, you can optionally specify names to be used in the header of the table in the hit list instead of the database's internal names.

The checkboxes can be used to add VB scripts which can be called from the hit list.

After clicking Save, the SQL query will be displayed with the given name in the Desktop folder.

SQL Command

SQL queries enable users to query data from the database using SQL commands.

To do so, you will need to enter the SQL command in the respective area of the SQL query window.

Note the following:

The SQL command must begin with the 'select' statement.
As a result, data cannot be deleted or modified.
Tables and columns must be queried using their internal database names.
You can use variables.
When running the query, the user enters values in a dialog that are used to query the variables.

You can view the table and column names in the database area of enaio® editor.

You will find the entry SQL statement in the title bar menu of a hit list in enaio® client. Use this entry to open a window containing the SQL statement used to create the hit list. You can also find the table and column names in this SQL statement. In expert mode, you can also view the SQL statements of queries.

SQL statements from hit lists and expert mode, as well as SQL statements from logs, may differ from the SQL statements that must be used for SQL queries. These SQL statements are sent directly to the database. Therefore, date fields must be specified in the language of the database user.

Only users who are assigned the system role 'Client: Edit SQL queries' are permitted to access the SQL statements.

Variables

You can use variables within the SQL command. The user assigns a value for the query to the variables via a dialog.

The syntax of a variable looks as follows:

$Name,C30$

Variables are marked with the $ sign at the beginning and end. Name is the variable name you have chosen to use in the user dialog. The name is followed by a comma and the comma by a field type.

The following types are available:

C	String with '%' at the end; wildcards will be replaced
S	String; wildcards will be replaced
N	String without replacements
D	Date

An integer is added, specifying the length of the variable's text field in the user dialog.

Subject to the field type and the database you are using, it may be necessary to place the variable inside single quotation marks.

Variables may be preset. To this effect, the respective value must be previously entered in the dialog. The user can either confirm or underwrite the value.

Sample syntax for a variable with preset value:

$Name,C30@Hellmer$

The preset value is specified with a leading @. It is also possible to use the #USER# variable as the value for the current user or #DATE# for the current date. In addition, the #COMPUTER-IP#, #COMPUTER-GUID#, and #COMPUTER-NAME# variables are available.

If you do not want to preset a variable, do not use the '@' sign.

Conditions are established using '=' and '!='. Placeholders are not appended during execution. If the user leaves a string empty, an empty – and therefore unindexed – field will be searched for.

Example:

The SQL query creates a hit list with anonymized patient data for statistical evaluations. Instead of displaying all index data from the patient folder, only the contents of three index data fields are shown: date of birth, gender, and place of residence. The user can specify the gender using a variable that is not preset. If the field is left empty, all patients will be listed.

select datum1,feld5,feld7 from stamm6 where feld5 LIKE '$gender,C1$'

The internal database names of the table and the three required columns can be viewed in the properties in enaio® editor.

In enaio® client, a hit list without a constraint pertaining to a gender would look like this:

The header contains the internal database names of the columns if no alternative name has been entered in the Header area.

Header

The hit list generated from an extended query contains the internal database names of the columns in the header. In the SQL queries window, you can enter names for the columns in the Header area.

It is also possible to assign values for column formatting to the names.

The syntax is as follows:

name,value;

You can enter the following values:

-1

The column is as wide as the longest entry.

This is the default setting, which means that the entry can be omitted.

The column is not displayed.

VB scripts may require data that will be queried but not displayed, e.g., the ID of an archive object.

Width in pixels

Enter a value for the width of the columns in pixels.

Example: Place of residence,50;

Example:

In the example above, three columns are requested. Names are assigned to these columns in the Header field.

DOB,50;Gender,50;Place of residence,-1;

The hit list will look as follows in enaio® client:

Instead of the internal database names for the columns, the header contains the names entered in the specified column format.

VB Scripts

You can assign VB scripts to the hit list of an extended query.

Tick the required checkboxes in the SQL query window, click the respective action button, and enter the VB script in the open script editor.

Add action button for entire hit list

Tick this checkbox if you want all data from the hit list to be sent to a VB script. Enter a name for the button in the following field via which the script will be executed. Click the Action 1 button to enter the VB script in the window.

Do not open the hit list (run action 1 only)

Activate this checkbox if you want the VB script assigned to the entire list to run immediately.

Allow multi-selection of hits

Activate this checkbox if you want to transfer the data of the hits that the user selects to a VB script. If you do not enter any script here, the Action 1 script will be run. It is run by pressing the 'Action 1' button.

Add action button for individual hits

Tick this checkbox to send all data of a selected hit to a VB script. Enter a name for the button in the following field via which the script will be executed. Click the Action 2 button to enter the VB script in the window.

Run action by double-clicking a hit

Tick this checkbox in order to send all data of a hit to a VB script by double-clicking the hit. Click the Action 3 button to enter the VB script in the window. If no script is entered, the script stored for Action 2 will be run.

The buttons are also enabled if no hits can be determined. Please keep this in mind when scripting.

Examples:

The following script transfers all data from the hit list in an Excel table:

sub ExportToExcel()
dim excel	'Object variable for Excel
dim row, col	'Control variables

if recordset.recordcount = 0 then	'Query whether the hit list is empty
msgbox "Empty record set", vbCritical,"optimal_AS®"
else
recordset.movefirst	'Cursor to the start of the record set
set excel = CreateObject("excel.application")	'Create Excel object
excel.workbooks.add	'Create new workbook
for col = 0 to recordset.fields.count - 1	'Process all fields of the recordset
excel.activesheet.cells(1, col + 1).value = recordset.fields(col).Name	'and the field names in the first row
next	'next Field
excel.activesheet.cells(2,1).copyfromrecordset recordset	'Copy whole recordset from 2nd line
excel.visible = true	'Show Excel
set excel = nothing	'Invalidate the object variable
end if
end sub
ExportToExcel

To enable multiple selection, the selrecords variable provides the Action 1 script with a list of indices, separated by commas, for the selected entries in the record set. The transferred index for the first line of the hit list is '0'. If no hits are selected, selrecords is empty.

if recordset.recordcount > 0 then msgbox "The following entries were selected: " & selrecords

As Action 2 or Action 3, the following script opens an individually selected hit from the hit list via the 'ID'. The cursor is positioned at the corresponding position and may not be placed at the beginning via recordset.movefirst. The selrecords variable is not initialized before Action 2 or Action 3 is run.

set a = CreateObject( "optimal_AS.Application")

objecttype = a.FindObjectType( RecordSet.Fields.Item("id").Value)

a.OpenObjectID RecordSet.Fields.Item("id").Value, objecttype,0