The sqlApiDBQuery extension sends a SQL query to a backend SQL API endpoint from inside a Virtuoso journey.

This is useful when your test needs to fetch database values through an API and reuse the returned response in later journey steps.

Parameters:

• url required, the base URL of the backend service that handles SQL database requests. The extension appends /SQLInterface to this value before sending the request;
• query required, the SQL query that should be sent to the backend SQL interface.

NLP usage

Use the extension in a journey by calling sqlApiDBQuery with the execute command. Pass each value to the matching extension input using as inputName.

To run a SQL query through the API:

execute "sqlApiDBQuery" using "https://api.example.com/v1/query" as url, "SELECT * FROM Users WHERE Age > 30" as query returning $response

To run the same query using Virtuoso variables:

execute "sqlApiDBQuery" using "$Url" as url, "$query" as query returning $response

Example setup using variables before calling the extension:

store value "https://api.example.com/v1/query" in $Url
store value "SELECT * FROM Users WHERE Age > 30" in $query
execute "sqlApiDBQuery" using "$Url" as url, "$query" as query returning $response

For example, the final request URL used by the extension will be:

https://api.example.com/v1/query/SQLInterface

When the request is successful, the extension returns the API response as a JSON string in the output variable.

This extension requires the following resource:

• https://cdnjs.cloudflare.com/ajax/libs/axios/0.21.1/axios.min.js 

The extension should be configured as:

• Run asynchronously: Yes
• Scope: Global

Add the extension to your Virtuoso instance

Select the domain that matches your Virtuoso account.

const makeRequest = async (url, query) => {
  try {
    const { data } = await axios.post(
      url,
      '"' + query + '"',
      {
        headers: {
          'Content-Type': 'application/json'
        }
      }
    );

    if (data.error) {
      console.log(data.error);
      doneError(JSON.stringify(data));
    }

    done(JSON.stringify(data));
  } catch (e) {
    console.log(e.message);
    doneError(e);
  }
};

if (!url) {
  throw new Error('URL parameter is missing');
}

url += "/SQLInterface";

if (!query) {
  throw new Error('Query parameter is missing');
}

makeRequest(url, query);