SQL Expert

Javapackage: com.etendoerp.copilot.dbquerytool

Overview

This Assistant is designed to help users read information from the database. It allows users to ask questions in natural language and get the SQL query that retrieves the information they need. Use a webhook to get the necessary information to generate the SQL query. The user can ask questions about the database, and the Assistant will determine whether to return a SQL query or execute the query and return the obtained result.

Components

The SQL Expert Assistant is composed of the following components:

API Call Tool
DBQueryExec - Webhook: This Webhook allows the Assistant to execute SQL queries in the database. The assistant call the Webhook to execute the query in the database and returns the result to the Assistant. This is a secure way to execute SQL queries in the database, as Etendo handles the security of the database connection and query execution.

Assistant Setup

This module is included in the Copilot Extensions Bundle

Info

To be able to include this assistant, the Copilot Extensions Bundle must be installed. To do that, follow the instructions from the marketplace: Copilot Extensions Bundle. For more information about the available versions, core compatibility and new features, visit Copilot Extensions - Release notes.
Check the necessary ETENDO_HOST variable in the gradle.properties file.
gradle.properties
```
ETENDO_HOST=http://localhost:8080/etendo
```
Warning

Replace http://localhost:8080/etendo with the real url of the Etendo system.
A new assistant named SQL Expert has been created in the Application > Service > Copilot > Assistant window.
Run Sync Assistant process, loged as System Administrator role.
Restart Docker image using ./gradlew resources.up tasks.
Configure access to the agent from the Assistant Access window.
You can use the SQL Expert assistant as a System Administrator
Warning

In case of using this assistant with other roles:
1. It must be verified that the role has webhooks execution enabled:
2. Access must be given to the corresponding role in the WebHooks window, in the Role tab, for the WebHook DBQueryExec. This webhook is automatically added when the module is installed.

Examples

Examples of questions for information retrieval

What is the amount of the last F&B España - Región Sur organization sales invoice?
Can you tell me which is the invoice with the highest registered amount?

3.

Examples of SQL query generation

Can you execute the sum of the orders in the last month?
Query to know the name of the best 5 customer of junuary 2011?
Query to obtain which is the invoice with the highest registered amount?