Executing SQL Stored Procedures via HTTP: A Data Access Game-Changer in the Age of AI

In the evolving landscape of data management and AI-driven applications, executing SQL stored procedures via HTTP emerges as a transformative capability. It facilitates seamless integration and interoperability across diverse platforms, significantly enhancing data accessibility and operational efficiency, all without accumulating technical debt.

Why is this Important?

In today’s AI-centric ecosystem, the integration of SQL stored procedures with HTTP-based interfaces has profound implications. Coupled with OpenAPI-compliant YAML or JSON descriptions, these procedures can be directly leveraged by large language models (LLMs) via external function-calling mechanisms. This approach dramatically streamlines how AI systems interact with complex data operations.

A prime example of this integration is the OpenLink AI Layer (OPAL). OPAL harnesses HTTP-executable stored procedures as its engine, enabling declarative querying through standardized JSON schemas that interface seamlessly with any OpenAI-compatible LLM.

Critically, this capability is what enables declarative functionality across SQL, SPARQL, and GraphQL. Each language processor (SQL, SPARQL, GraphQL) is implemented as a Virtuoso Stored Procedure published and exposed via HTTP as described herein.

Step-by-Step Guide: Publishing SQL Stored Procedures for HTTP

1. Creating the Stored Procedure

Begin with a straightforward stored procedure:

CREATE PROCEDURE HELLO_WORLD (IN name VARCHAR)
{
  RETURN CONCAT('Hello, ', name, '!');
};
        

Modify it for HTTP accessibility:

DROP PROCEDURE HELLO_WORLD;
CREATE PROCEDURE HELLO_WORLD (IN name VARCHAR) RETURNS VARCHAR array __soap_http 'application/json' 
{
  RETURN CONCAT('Hello, ', name, '!');
};
        

2. Granting Execution Privileges

Assign appropriate permissions to enable external HTTP invocation:

GRANT EXECUTE ON HELLO_WORLD TO SOAP;
        

3. Testing Your Procedure

You can verify your procedure locally:

SELECT HELLO_WORLD('Bob');
        

Or test it externally via HTTP:

Using cURL:

curl -k https://{CNAME}/SOAP/Http/HELLO_WORLD?name=Bob && echo
        

Example Call:

curl -k https://demo.openlinksw.com/SOAP/Http/HELLO_WORLD?name=Bob && echo
        

4. Publishing OpenAPI Specifications

Make your stored procedures discoverable by creating OpenAPI specification endpoints:

JSON Endpoint:

DROP PROCEDURE WS.WS."openapi.json";
CREATE PROCEDURE WS.WS."openapi.json" () __SOAP_HTTP 'application/openapi+json'
{
  RETURN DB.DBA.OBJ2JSON (OAS_SERVICES_JSON_OBJ (
           'https://demo.openlinksw.com', '/SOAP/Http', user,
           VECTOR ('WS.WS.openapi.json', 'WS.WS.openapi.yaml')), 20);
};
GRANT EXECUTE ON WS.WS."openapi.json" TO SOAP;
        

YAML Endpoint:

DROP PROCEDURE WS.WS."openapi.yaml";
CREATE PROCEDURE WS.WS."openapi.yaml" () __SOAP_HTTP 'application/openapi+yaml'
{
  RETURN DB.DBA.JSON2YAML (OAS_SERVICES_JSON_OBJ (
           'https://demo.openlinksw.com', '/SOAP/Http', user,
           VECTOR ('WS.WS.openapi.json', 'WS.WS.openapi.yaml')));
};
GRANT EXECUTE ON WS.WS."openapi.yaml" TO SOAP;
        

Access Templates

HTTP

curl https://{CNAME}/SOAP/Http/openapi.yaml
curl https://{CNAME}/SOAP/Http/openapi.json
        

HTTPS

curl -k https://{CNAME}/SOAP/Http/openapi.yaml
curl -k https://{CNAME}/SOAP/Http/openapi.json
        

Usage Examples

HTTP

curl https://demo.openlinksw.com/SOAP/Http/openapi.yaml
        

curl https://demo.openlinksw.com/SOAP/Http/openapi.json
        

HTTPS

curl -k https://demo.openlinksw.com/SOAP/Http/openapi.yaml
        

curl -k https://demo.openlinksw.com/SOAP/Http/openapi.json
        

Impact and Future Applications

By enabling SQL stored procedures for HTTP-based execution and linking them through OpenAPI to LLMs, organizations can:

• Rapidly integrate advanced data operations into AI-driven workflows.
• Significantly reduce the complexity and overhead traditionally associated with inter-system communication.
• Leverage AI to dynamically invoke database operations, enhancing responsiveness and accuracy in decision-making systems.

This approach positions organizations to capitalize on agile data management strategies, powering innovative solutions in an AI-dominated era.

Related Resources

• About OpenLink AI Layer (OPAL)
• OPAL Assistants Github Repo
• About Virtuoso