Hexagon EAM SQL Validator

Publisher: Sequels.nl

A Visual Studio Code extension that validates SQL code against a Hexagon EAM cloud tenant using the SOAP API.

Features

Validation

• Validates SQL code in the current file against your Hexagon EAM tenant
• Validates FlexSQL code for Hexagon EAM flex-triggers
• Secure credential management (password stored in memory only)
• Real-time validation with detailed error messages
• Keyboard shortcut support for quick validation
• Supports both standard SQL queries and FlexSQL trigger validation

SQL Compression

• SQL Compression - Removes comments and unnecessary whitespace (saves as .min.sql)
• Simple and fast compression algorithm
• Works with selected text or entire file
• Shows compression statistics (original size, compressed size, savings %)
• Automatically creates compressed file alongside original

SQL Snippets

• Flex Trigger Snippets - Quick templates for Hexagon EAM flex triggers
• flextrigger - Complete metadata block with YAML notation
• flextriggersql - SQL SELECT template with metadata
• flextriggerplsql - PL/SQL template with metadata
• Tab through all fields with IntelliSense support

Installation

1. Clone or download this repository
2. Open the folder in VS Code
3. Run npm install to install dependencies
4. Press F5 to run the extension in debug mode

Configuration

Before using the extension, you need to configure your Hexagon EAM connection:

1. Open the Command Palette (Ctrl+Shift+P / Cmd+Shift+P)
2. Type "Hexagon EAM: Configure Connection"
3. Enter your:
   • SOAP API endpoint URL (e.g., https://your-tenant.eam.hexagoncloud.com/axis/services/EAMWebService)
   • Username in format userid@tenant (e.g., john.doe@company)
   • Password (stored securely in memory only)

Finding Your Endpoint

Your Hexagon EAM SOAP endpoint typically follows this format:

https://[tenant].eam.hexagoncloud.com/axis/services/EAMWebService

Replace [tenant] with your organization's tenant name.

Usage

Validate SQL

1. Open a SQL file in VS Code
2. Option A: Select the SQL code you want to validate (or leave nothing selected to validate entire file)
3. Either:
   • Press Ctrl+Shift+V (Windows/Linux) or Cmd+Shift+V (Mac)
   • Or use Command Palette: "Hexagon EAM: Validate SQL"
4. View the validation result in a popup

Selection Behavior:

• Text selected → Validates only the selected SQL code
• Nothing selected → Validates the entire file

Validate FlexSQL (Trigger)

1. Open a SQL file containing FlexSQL trigger code
2. Option A: Select the FlexSQL code you want to validate (or leave nothing selected to validate entire file)
3. Either:
   • Press Ctrl+Shift+F (Windows/Linux) or Cmd+Shift+F (Mac)
   • Or use Command Palette: "Hexagon EAM: Validate FlexSQL (Trigger)"
4. View the validation result in a popup

Selection Behavior:

• Text selected → Validates only the selected FlexSQL code
• Nothing selected → Validates the entire file

Note: FlexSQL validation uses the MP6046 TestFlexSql service specifically for validating flex-trigger SQL statements.

Compress SQL

1. Open a SQL file in VS Code
2. Option A: Select the SQL code you want to compress (or leave nothing selected to compress entire file)
3. Either:
   • Press Ctrl+Shift+M (Windows/Linux) or Cmd+Shift+M (Mac)
   • Or use Command Palette: "Hexagon EAM: Compress SQL"
4. A new file will be created with .min.sql extension
5. View compression statistics in the notification
6. The compressed file opens with word wrap enabled for easy reading

What it does:

• Removes -- single-line comments
• Removes /* */ multi-line comments
• Collapses multiple spaces to single space
• Removes spaces around operators: ,;()=<>!+-*/
• Shows savings: original size → compressed size

Example: query.sql → query.min.sql

Selection Behavior:

• Text selected → Compresses only the selected SQL code
• Nothing selected → Compresses the entire file

Note: This is a straightforward compression that removes comments and whitespace. Test the compressed SQL before using in production.

Use Snippets

The extension includes snippets for quickly creating Hexagon EAM flex trigger templates.

Available Snippets

1. flextrigger - Complete flex trigger with metadata

   /*
   @FLEX-TRIGGER
   table: R5EVENTS
   sequence: 10
   trigger_type: POST-INSERT
   ...
   */
   
   -- SQL code here
   

2. flextriggersql - Flex trigger with SQL SELECT template

   /*
   @FLEX-TRIGGER
   ...
   */
   
   SELECT column
   FROM table
   WHERE condition;
   

3. flextriggerplsql - Flex trigger with PL/SQL template

   /*
   @FLEX-TRIGGER
   ...
   */
   
   DECLARE
     v_count NUMBER := 0;
   BEGIN
     -- PL/SQL code here
   END;
   

How to Use Snippets

1. Open a .sql file
2. Type flex and press Ctrl+Space for IntelliSense
3. Select one of the snippets
4. Tab through the fields to fill in your values
5. Use arrow keys to select from dropdown options (true/false, trigger types)

Validation Results

• ✅ Success: Shows a confirmation that the SQL/FlexSQL is valid
• ❌ Failure: Shows the response code and error message from Hexagon EAM

SOAP API Details

The extension uses two Hexagon EAM SOAP web services:

1. MP6035 ValidateQuery (Standard SQL)

• Namespace: http://schemas.datastream.net/MP_functions/MP6035_001
• Operation: MP6035_ValidateQuery_001
• Element: <mp1:QUERYTEXT>
• Use: General SQL query validation

2. MP6046 TestFlexSql (FlexSQL Triggers)

• Namespace: http://schemas.datastream.net/MP_functions/MP6046_001
• Operation: MP6046_TestFlexSql_001
• Element: <mp1:SQLSTATEMENT>
• Use: Flex-trigger SQL validation

Common Configuration

• Authentication: WS-Security UsernameToken with format userid@tenant
• Organization: * (all organizations)
• Session: Terminates after each request

Response Handling

The extension interprets responses as follows:

Valid SQL:

• Response without SOAP Fault
• Contains MP6035_ValidateQuery_001 element

Invalid SQL:

• SOAP Fault with soapenv:Fault
• Contains ExceptionInfo with InvalidSQLException
• Error message in <ns1:Message> tag (e.g., "De SQL-instructie is ongeldig: ORA-00942: table or view does not exist")
• Reason code in <ns1:ReasonCode> tag

SOAP Errors:

• Authentication/authorization failures
• Network or service errors
• Extracted from <faultstring> element

Customization

If you need to modify the SOAP structure, edit the callSoapAPI and parseSOAPResponse functions in src/extension.ts.

Development

Build

npm run compile

Watch Mode

npm run watch

Package Extension

npm install -g vsce
vsce package

This creates a .vsix file that can be installed in VS Code.

Requirements

• Visual Studio Code 1.80.0 or higher
• Node.js and npm
• Access to a Hexagon EAM cloud tenant
• Valid Hexagon EAM credentials

Security Notes

• Passwords are stored in memory only during the VS Code session
• Credentials are never written to disk or logged
• Always use HTTPS endpoints for production

Troubleshooting

"No response from server"

• Check your endpoint URL is correct
• Verify network connectivity to the Hexagon EAM tenant
• Ensure firewall/proxy settings allow SOAP requests

"SOAP API error"

• Verify your credentials are correct
• Check the SOAP envelope format matches your API version
• Review the response in the VS Code Developer Console

"SQL validation failed"

• Check the error message for specific SQL syntax issues
• Verify the SQL dialect matches Hexagon EAM requirements

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.

License

MIT License - see LICENSE file for details