mssql.connector

Overview

The mssql.connector is a component designed to integrate Microsoft SQL Server (MSSQL) databases with the Vantage analytics and data platform. This connector allows users to execute SQL queries against MSSQL databases, retrieve the results, and handle any errors that may arise. It requires user authentication and the proper configuration of third-party services and credentials to function effectively.

Settings

The mssql.connector component accepts the following settings:

1. User Authentication

• Name: session
• Input Type: Object
• Description: This object contains session details retrieved via the the server session function. It ensures that the user is authenticated before allowing access to the component's functionality. Specifically, it checks for the presence of the clientId, which is necessary for retrieving MSSQL service credentials.
• Default Value: N/A (it is dynamically fetched during execution).

2. SQL Query

• Name: inputs.query
• Input Type: String
• Description: This setting expects a valid SQL query string. It is mandatory for executing a read-only operation against the MSSQL database. The absence of this query will result in an error being thrown.
• Default Value: N/A (must be provided by the user).

3. Service Configuration

• Name: service
• Input Type: Object
• Description: This object is retrieved via a database query to fetch configuration details of the MSSQL service linked with the client. Its presence verifies that an MSSQL service is configured for the current client.
• Default Value: N/A (retrieved dynamically based on clientId).

4. Credentials Retrieval

• Name: creds
• Input Type: Object
• Description: This object contains the encrypted credentials required to connect to the MSSQL database. It is fetched from the database corresponding to the clientId. If no credentials are found, an error will inform the user that none exist.
• Default Value: N/A (retrieved based on the existence of the service and client).

5. Error Handling

• Name: Error handling block
• Input Type: N/A (internal logic)
• Description: This mechanism catches any exceptions raised during the execution of the SQL query, ensuring that users receive a clear error message and do not experience disruptive failures in the user interface.
• Default Value: N/A (configured as part of the component's logic).

How It Works

1. The component begins by retrieving the user session to ensure that the user is authenticated.
2. It checks that the inputs.query parameter is provided; if not, it throws an error indicating that the SQL query is required.
3. Next, the connector attempts to find a configured MSSQL service associated with the authenticated user.
4. The connector retrieves the encrypted credentials for the MSSQL service. If no credentials are found, the component returns an indication of failure.
5. Upon successfully obtaining a valid service and credentials, the connector creates an integration instance using the node.
6. It executes the provided SQL query in a read-only mode and returns the results.
7. Any errors during execution are caught and displayed in a user-friendly format.

Data Expectations

The mssql.connector expects the following data inputs:

• A valid SQL query in the form of a string that conforms to MSSQL query syntax.
• User authentication data that includes a valid clientId.

Use Cases & Examples

Use Case 1: Data Analysis

A data analyst needs to run reports based on historical sales data stored in an MSSQL database. They must query multiple records efficiently for their analysis.

Use Case 2: Data Migration

A database administrator is tasked with migrating data from an old application’s database to a new system. They can execute complex queries to extract the necessary data directly from the MSSQL database to facilitate this process.

Example Configuration

Assume a data migration use case where the administrator needs to extract a list of users from the MSSQL database to be imported into a new system. The specific SQL query might look like this:

{
    "inputs": {
        "query": "SELECT * FROM Users WHERE Status = 'Active'"
    },
    "config": {}
}

Breakdown

• SQL Query: SELECT * FROM Users WHERE Status = 'Active' – This query retrieves all active users from the Users table.
• ChatGPT Integration: Not applicable in this specific instance but could be explored for generating dynamic queries or translating user queries into SQL.
• Billing Impact: The billing for executing queries may depend on the volume of data retrieved and processed, which should be monitored as excess data retrieval could accrue additional costs on the platform.

In conclusion, the mssql.connector serves as a robust interface for integrating and querying Microsoft SQL Server databases, providing essential features for authentication, error handling, and detailed configurations necessary for database interactions within the Vantage platform.