CONNX Demo

CONNX Demo is a small Flask application that turns natural-language questions into SQL, runs the generated SQL against a CONNX-accessible database, and displays the results in a browser.

The application currently uses Azure OpenAI for SQL generation and ODBC/CONNX for database access. It also includes safety checks so model-generated SQL is validated before it reaches the database.

What The App Does

1. A user enters a question in the web form, for example: "show employee ids".
2. The app sends the question plus the allowed database schema to Azure OpenAI.
3. Azure OpenAI returns SQL.
4. The app validates that the SQL is a single read-only SELECT against allowed tables.
5. The app executes the query through ODBC.
6. The result page shows the generated SQL, returned rows, timing, and token usage.

There is also a JSON API endpoint:

POST /api/natural_language_query

with a JSON body like:

{
  "question": "show employee ids"
}

Sample Questions

• Show a list of employees that own GREEN coloured vehicles.

Questions work best when they use table concepts and values present in table_definitions.json. The model is instructed not to invent unknown tables, columns, or relationships.

Project Layout

.
|-- app.py                    # Flask routes, web form, JSON API
|-- config.py                 # Runtime configuration loaded from environment variables
|-- sql_functions.py          # Azure OpenAI, SQL validation, DB execution helpers
|-- table_definitions.json    # Allowed tables/columns used in prompts and SQL safety checks
|-- templates/                # Flask HTML templates
|-- tests/                    # pytest tests
|-- scripts/                  # Explicit API, ODBC, JDBC, and environment smoke tests
|-- backups/                  # Archived old scripts/templates, ignored by git
|-- requirements.txt          # Python dependencies
|-- .env.example              # Safe example environment configuration
`-- TODO.md                   # Cleanup and modernization checklist

Important Concepts For New Python Developers

Flask

Flask is the web framework. In this project, app.py defines:

• / for the browser UI.
• /api/natural_language_query for JSON clients.
• Basic error handling.

The module exposes create_app(...) for tests and other WSGI-compatible runtimes. Running python app.py creates the default app from .env and starts Flask's development server.

Flask templates live in templates/. The route functions call render_template(...) to return HTML.

Environment Variables

Secrets and machine-specific settings should not be hardcoded in Python files. This app reads settings from .env using python-dotenv.

Use .env.example as the template, then create your own .env.

Do not commit .env. It is ignored by .gitignore.

Azure OpenAI Deployment Name

For Azure OpenAI, the value passed to the API is the deployment name, not necessarily the base model name.

Find it in Azure AI Foundry / Azure OpenAI Studio under your resource's deployments.

Example:

AZURE_OPENAI_DEPLOYMENT=gpt-5-nano-2

SQL Safety

Generated SQL is treated as untrusted. The app validates it in two places:

• Before route handlers execute the query.
• Again inside execute_sql_query(...).

Current rules are intentionally conservative:

• Only one SQL statement.
• Must be a SELECT.
• Blocks write/admin keywords such as INSERT, UPDATE, DELETE, DROP, and EXEC.
• Only allows tables listed in table_definitions.json.

Setup

Prerequisites

• Python 3.10 or newer.
• Access to an Azure OpenAI resource and a deployed model.
• A CONNX/ODBC DSN or SQL Server-compatible ODBC connection.
• A matching 64-bit ODBC driver when using 64-bit Python.

On Windows, list the ODBC drivers visible to Python with:

python -c "import pyodbc; print(pyodbc.drivers())"

1. Create And Activate A Virtual Environment

From the project folder:

python -m venv .venv
.venv\Scripts\Activate.ps1

If PowerShell blocks script execution, you may need to adjust your execution policy for the current user.

2. Install Dependencies

python -m pip install -r requirements.txt

3. Create .env

Copy .env.example to .env:

Copy-Item .env.example .env

Then edit .env.

Required Flask setting:

SECRET_KEY=replace-with-a-random-secret

Generate a local value with:

python -c "import secrets; print(secrets.token_urlsafe(32))"

Put the generated value in .env as SECRET_KEY. Do not commit .env, and do not leave SECRET_KEY set to the placeholder value.

Optional Flask runtime settings (these defaults are safe for local use):

FLASK_HOST=127.0.0.1
FLASK_PORT=4999
FLASK_DEBUG=false
TABLE_DEFINITIONS_PATH=table_definitions.json

Set FLASK_HOST=0.0.0.0 only when the development server intentionally needs to accept connections from other machines. Do not expose Flask's development server directly to the internet.

Required Azure OpenAI settings:

AZURE_OPENAI_API_KEY=replace-with-your-azure-openai-key
AZURE_OPENAI_ENDPOINT=https://your-resource-name.openai.azure.com/
AZURE_OPENAI_DEPLOYMENT=replace-with-your-azure-deployment-name
AZURE_OPENAI_API_VERSION=2024-02-15-preview
AZURE_OPENAI_MAX_COMPLETION_TOKENS=500

The endpoint must be the Azure OpenAI resource endpoint and should resolve from the machine running Flask.

The Azure API key, endpoint, and deployment are validated when SQL generation is requested. The app can start without contacting Azure, but a query cannot succeed until these settings and network access are valid.

4. Configure Database Access

The database connection is built in this order:

1. DB_CONNECTION_STRING
2. DB_DSN plus optional DB_USER / DB_PASSWORD
3. Direct SQL Server settings: DB_SERVER, DB_NAME, DB_USER, DB_PASSWORD

Preferred CONNX DSN setup:

DB_DSN=CONNXEmployeeMfDemo64
DB_USER=replace-with-db-user
DB_PASSWORD=replace-with-db-password
CONNX_DISABLE_SERVER_TRACE=true

When the connection string appears to be a CONNX connection, the app runs the CONNX-documented select 1 {fn disableservertrace} statement before executing the generated query. This keeps CONNX server-side tracing disabled for the active connection and avoids noisy driver log attempts such as \CONNX.LOG. Set CONNX_DISABLE_SERVER_TRACE=false only when you intentionally need CONNX trace output while troubleshooting.

If CONNX still prints a log-file warning during the initial connection handshake, check the local CONNX Configuration Manager settings. The installed CONNX help says DEBUG=0 disables CONNX debug tracing and LSNDEBUG=0 disables listener tracing; nonzero values enable progressively more logging.

Alternative full connection string:

DB_CONNECTION_STRING=DSN=CONNXEmployeeMfDemo64;UID=replace-with-db-user;PWD=replace-with-db-password

Alternative direct SQL Server setup:

DB_SERVER=localhost
DB_NAME=DemoDB
DB_USER=replace-with-db-user
DB_PASSWORD=replace-with-db-password

Run The App

python app.py

With the default settings, open:

http://localhost:4999

After changing .env, restart Flask. Environment variables are loaded at startup.

For a quick first-run check:

1. Open http://localhost:4999 and confirm the form and schema appear.
2. Submit one of the sample questions.
3. Confirm the result page shows generated SQL and database rows.

The first step needs only a valid SECRET_KEY and readable schema file. The submitted query additionally needs working Azure OpenAI and database settings.

Use The JSON API

From PowerShell, after starting the app:

$body = @{ question = "Show a list of employees that own GREEN coloured vehicles" } | ConvertTo-Json
Invoke-RestMethod -Method Post -Uri http://localhost:4999/api/natural_language_query -ContentType application/json -Body $body

A successful response contains the generated sql_query, token and timing metadata, and results as an array of objects. Invalid input or failed query generation returns an error field. The API executes the generated query; it is not a SQL-preview-only endpoint.

Run Tests

Run the self-contained unit tests with:

python -m pytest tests\test_app_config.py tests\test_api_routes.py tests\test_db_config.py tests\test_index_template.py tests\test_openai_sql_generation.py tests\test_sql_safety.py tests\test_connx.py

These tests mock Azure OpenAI and database calls and should not require live services. The explicit scripts under scripts/ are not collected by pytest and may require a running app, live database, or optional JDBC dependencies.

Run a live API smoke test after starting the app with:

python scripts\api_smoke_test.py

Run ODBC and JDBC connection checks explicitly with:

python scripts\odbc_smoke_test.py
python -m pip install JayDeBeApi JPype1  # Optional; JDBC test only
python scripts\jdbc_smoke_test.py

Updating The Schema

The app only allows tables and columns listed in table_definitions.json.

When adding or changing tables:

1. Update table_definitions.json.
2. Make sure table names match the fully qualified names used by the database.
3. Add or update tests if query validation behavior changes.
4. Restart Flask.

To use a schema file elsewhere, set TABLE_DEFINITIONS_PATH in .env. Each entry must provide a fully qualified table_name and a columns object. A table entry may also provide a relationship string used to guide join generation.

The schema file is used for both prompting Azure OpenAI and validating generated SQL.

Troubleshooting

Azure OpenAI is not configured

Check .env for:

• AZURE_OPENAI_API_KEY
• AZURE_OPENAI_ENDPOINT
• AZURE_OPENAI_DEPLOYMENT
• AZURE_OPENAI_API_VERSION

Make sure AZURE_OPENAI_DEPLOYMENT is the deployment name from Azure, not a placeholder.

DNS Or Connection Errors To Azure

Verify the endpoint:

AZURE_OPENAI_ENDPOINT=https://your-resource-name.openai.azure.com/

If the host does not resolve, the resource name may be wrong or network/DNS access may be blocked.

Unsupported Token Parameter

Some Azure models require max_completion_tokens instead of max_tokens. The app uses max_completion_tokens by default and has a fallback for older clients.

Tune output length with:

AZURE_OPENAI_MAX_COMPLETION_TOKENS=500

Database Login Errors

Check the database connection precedence:

1. If DB_CONNECTION_STRING is set, it wins.
2. Else, if DB_DSN is set, DSN mode is used.
3. Else, direct SQL Server settings are used.

If the app tries the wrong database, check for leftover settings in .env. Also confirm that the DSN is visible to the same Windows account and Python architecture used to run the app.

App Fails Before Starting

• If the error mentions SECRET_KEY, replace the placeholder with a generated value.
• If the schema file cannot be found, start the app from the project directory or set an absolute TABLE_DEFINITIONS_PATH.
• If FLASK_PORT is invalid or already in use, choose another numeric port and use that port in the browser/API URL.

Generated SQL Is Rejected

The SQL validator is conservative. It rejects:

• Multiple statements.
• Non-SELECT statements.
• Unknown tables.
• Write/admin keywords.

If you need CTEs, derived tables, quoted identifiers, or more SQL Server-specific syntax, update the validator and tests together.

Security Notes

• Do not commit .env.
• Rotate exposed keys if they were ever pasted into logs, chat, screenshots, or commits.
• Generated SQL must remain validated before execution.
• Run database users with the least privileges possible. Read-only credentials are strongly preferred for this app.
• Avoid logging prompts, connection strings, API keys, raw provider errors, or database driver messages.

Current Cleanup Notes

The app is functional, and remaining maintenance work is tracked in TODO.md. Current themes include:

• Consolidating templates and moving inline frontend assets into static files.
• Converting or isolating legacy script-style integration tests.
• Expanding route-level error and result coverage.
• Finishing prompt-construction cleanup and repository hygiene.