Form Name: EQuIS SQL Form
File Name: EarthSoft.Forms.Library.42804.dll
Software: EQuIS Professional
Source: Available for the EarthSoft Primary Point of Contact (POC) on the EarthSoft Community Center downloads dashboard > Products > Additional Resources > v7.0
Description: The EQuIS SQL Form allows users to query the database using T-SQL. The form allows select statements, but cannot be used to delete, create or update records.
Installation Instructions: Download the EQuIS SQL Form from the EarthSoft Community Center, unblock the *.zip file and extract the DLL to the EQuIS Professional installation folder. For default installations this directory is C:\Program Files\EarthSoft\EQuIS or (for per-user installation) %localappdata%\Programs\EarthSoft\EQuIS\. The form will be available for selection once Professional has been relaunched.
Overview:
•Setup Instructions for Non-Admin User
The form allows you to write database queries for viewing data. If you do not have direct access to the database or to Microsoft SQL Server Management Studio, the form allows you to quickly find certain records in the database or to combine records from multiple tables using a join statement. For example, the form supports UNION and EXCEPT operators, along with TOP, ORDER BY and COUNT commands. Note that certain functions, such as deleting or updating, are disabled in the form.
Note: Only one instance of the EQuIS SQL Form at a time should be used. To perform and display results from multiple queries, return to the original EQuIS SQL Form tab and enter each new query there. |
The SQL Form query output window also contains all the standard report functions, such as crosstab reports, xy charts, and export options.
Additionally, the SQL Form query can be published to the database and utilized in EQuIS just as any other EQuIS grid report, e.g. as a crosstab in the Enterprise Data Grid or EZView Widget.
Note: This form is not available for an Application Level Security login, except "admin", unless set up specifically for such. |
SQL for EQuIS Query
Warning: The EQuIS SQL Form reads anything that contains the @ symbol as a parameter, even if it should be read as a string. The only impact of this current behavior is that the form then displays the parameter selector screen. Continuing past that screen by clicking the Go button will still show correct results from the form. Another possible workaround is to replace the @ with CHAR(64), the number that represents the ASCII character. As an example, the SQL would look like this:
select user_id from st_user where email_address = 'username' + CHAR(64) + 'domain.com'; |
SQL for EQuIS Results
Setup Instructions for Non-Admin User
Note: "VIEWER" permission to ALL facilities in the database is required, and use of the form enables viewing data (query results) which are typically hidden from non-admin ALS users (i.e. certain system tables [ST_*]). |
The following instructions must be followed so that a non-admin ALS user can use the EQuIS SQL Form:
1.Publish the EQuIS SQL Form DLL to the database (i.e., EarthSoft.Forms.Library.42804.dll. (Once published, the DLL does not need to be installed on the computer running Professional).
2.Using SQL Server Management Studio (or other similiar tool), execute the script.
Right-click the image above and select Save link as. Once downloaded, unblock the file and change the file extension from .txt to .sql. When executed, this script does the following:
a.Creates a special role named "EQuIS SQL Form" (used for assigning permissions).
b.Grants the appropriate permissions to "EQuIS SQL Form" role.
3.Using EQuIS Enterprise, associate the users (ALS non-admin) that will use the form to the "EQuIS SQL Form" role.
For example, assign roles to a non-admin ALS as shown below.
If after following the steps above (in order), a user is not able to use the EQuIS SQL Form, it is likely that they do not have "VIEWER" permission to EVERY facility in the database. To find facilities that a user does not have "VIEWER" permission to, execute the script.
Right-click the image above and select Save link as. Once downloaded, unblock the file and change the file extension from .txt to .sql.
4.Make all facilities visible to this non-admin ALS as shown below.
Note: If a new version of the EQuIS SQL Form is published, the CreateSqlFormRoleGrantViewer script (from step 2 above) may need to be run again in order for non-admin users to continue to be able to access the EQuIS SQL Form. |
Comments in Code
Comments can be included in SQL code used in the EQuIS SQL Form with the following caveats:
1.Comments before the code do not work.
2.Comments within the code must start with a forward slash and an asterisk and end with an asterisk and a forward slash, i.e. /*comment*/.
3.Comments after the code must be noted with a double dash, i.e. --comment.
Note: The EQuIS SQL Form interprets anything that contains the @ symbol as a parameter. The impact is that the form displays this part of the text on the parameter selector screen, even if the query included no other parameters. Ignore this added parameter when clicking the Go button on the parameter selector screen, and the correct results will still be displayed from the form. Another possible workaround is to replace the @ with CHAR(64), which is the character that represents the '@' ASCII character, e.g.:
select user_id from st_user where email_address = 'username' + CHAR(64) + 'domain.com'; |