Database query panel
Overview
The Database query panel is an administrative tool accessible through the Tools menu that provides direct access to the workflow engine's database. This panel allows administrators to execute SQL queries for troubleshooting, data analysis, and maintenance tasks. It is particularly valuable in installations where database access is restricted or the database is located on a separate server.
The Database query panel is only accessible to administrators who are members of the ROLE_ADMIN role. This tool provides direct database access and should be used with extreme caution. Incorrect queries can affect workflow engine data integrity and system stability.
Panel Layout
The Database query panel is structured with three main areas:
- Top navigation bar: Contains the main menu options (Home, Designer, Users, Tools, Admin)
- Query editor section: Upper section with SQL editor and execution controls
- Results section: Lower section displaying query results in tabular format
Query Editor Section
The upper section provides a SQL editor for composing and executing database queries:
SQL Editor
| Feature | Description |
|---|---|
| Multi-line editor | Textarea with line numbers for writing SQL statements. Supports multi-line queries with proper formatting. |
| Syntax highlighting | SQL keywords are highlighted for better readability (SELECT, FROM, WHERE, etc. appear in distinct colors) |
| Multiple statements | Allows execution of one or more SQL statements simultaneously. Separate statements with semicolons (;) |
| Line numbering | Line numbers displayed on the left side help reference specific lines in complex queries |
Editor Controls
| Button | Description |
|---|---|
| EXECUTE | Executes the SQL query or queries written in the editor. Results appear in the Query Results section below. The button is styled in green to indicate it is the primary action. |
| CLEAR QUERY | Clears all content from the SQL editor, allowing you to start fresh with a new query |
Query Results Section
The lower section displays the results of executed SQL queries in a tabular format:
Results Display
| Feature | Description |
|---|---|
| Row count indicator | Displays the total number of rows returned by the query (e.g., "121 rows"). This appears as a badge next to the "Query Results" heading. |
| Dynamic columns | The number and names of columns adapt based on the query. Column headers display the field names returned by the SELECT statement. |
| Data table | Results are presented in a scrollable table format with alternating row colors for better readability. Each row represents one record from the query result. |
| CLEAR RESULTS button | Removes the current query results from the display, allowing you to clear the view before executing a new query |
Supported SQL Operations
The Database query panel supports standard SQL operations:
Query Operations (SELECT)
The primary use case is executing SELECT queries to retrieve information:
-- View all workflow nodes
SELECT * FROM WF_NODE;
-- Find specific process definitions
SELECT * FROM WF_PROCESS_DEFINITION WHERE PD_NAME = 'document_validation';
Data Modification Operations
The panel also supports UPDATE, INSERT, and DELETE operations, though these should be used with extreme caution:
UPDATE and DELETE operations directly modify the workflow engine database and cannot be undone. Always create a database backup before executing data modification queries. Consider consulting with support before performing destructive operations.
Best Practices
- Start with SELECT queries: Always begin with SELECT queries to understand data before attempting modifications
- Use WHERE clauses: Include WHERE clauses to limit the scope of queries and avoid accidentally affecting the entire database
- Test on development first: Test queries on a development or staging environment before executing on production
- Create backups: Always create database backups before running UPDATE or DELETE operations
- Limit result sets: Use LIMIT clauses for large tables to avoid overwhelming the interface with results
- Document queries: Save important queries with comments explaining their purpose for future reference
- Consult support: Contact support before performing complex or potentially destructive operations
- Understand the schema: Familiarize yourself with the workflow engine database schema before writing queries
- Use transactions: For multi-statement modifications, consider using transactions (BEGIN, COMMIT, ROLLBACK)
- Monitor query performance: Be aware that complex queries can impact system performance
Tip: Use the semicolon (;) to separate multiple SQL statements in the editor. The Database query panel will execute them sequentially and display all results.
Limitations and Considerations
- No query validation: The panel does not validate SQL syntax before execution - errors will only appear after execution
- No rollback mechanism: Data modifications cannot be automatically undone
- Performance impact: Complex queries can impact system performance for all users
- Result size limits: Very large result sets may cause browser performance issues
- No query history: Previously executed queries are not saved automatically
- Limited error messages: Database errors may not provide detailed troubleshooting information
The Database query panel is a powerful administrative tool that should be used only when necessary and with full understanding of the implications. When in doubt, consult with the support team or a database administrator before executing queries.
Troubleshooting
Query Returns No Results
- Verify the table name and column names are correct
- Check if the WHERE clause is too restrictive
- Ensure the data you're looking for exists in the database
Error: Table Does Not Exist
- Confirm the table name matches the workflow engine schema
- Check if you're querying the correct database
- Table names may be case-sensitive depending on the database
Query Times Out
- Simplify the query or add more specific WHERE conditions
- Add indexes to frequently queried columns
- Break complex queries into smaller parts
The Database query panel connects to the same database configured for the workflow engine. Database connection settings are defined in the workflow engine configuration files.
