Advertisement
Buy and Sell your new and used items in Nouhata.com
Visit Nouhata.com →
Advertisement
Project and team management with CSMS.work
Visit CSMS.work →

Manual 1 of 11

Customer Manual

Internet SQL AI 1.25 Customer Manual

Applies to: InternetSQL 1.25 AI release

Audience: database users, application developers, administrators, and support engineers

Scope: features implemented and shipped in the current source/build

1. Product overview

Internet SQL AI adds persistent vector collections and exact similarity search

to the existing InternetSQL relational database. It operates alongside normal

tables, joins, transactions, storage fields, VINSERT, and VECTOR; it does

not replace or reinterpret those features.

The current release can:

  • create persistent AI/vector collections;
  • insert application-generated numeric embeddings with IDs and metadata;
  • search by COSINE, L2, or DOT ranking;
  • return ranked results through normal embedded and TCP query results;
  • report collection availability and remove AI collections;
  • provide a standalone vector command-line utility;
  • provide a deterministic offline embedding API for development tests;
  • generate source-backed answers with structured citations through ASK AI;
  • expose read-only and policy-controlled MCP tools;
  • trace AI execution stages from every shipped shell and server.

The isolated provider-host layer implements OpenAI embeddings and Responses

generation. SQL provider DDL, SEARCH AI ... TEXT, provider-backed sync,

database-native RAG/ASK AI, and MCP are implemented. IVF/HNSW indexes are not

implemented. See InternetSQL_OpenAI_Provider_Manual.md,

InternetSQL_AI_RAG_Manual.md, and InternetSQL_MCP_Manual.md.

2. Programs with AI support

The AI SQL command path is compiled into the existing isqldll.dll and

isqllib.lib. No separate AI DLL or library is required.

The following shipped programs share the AI command and trace facilities:

| Program | Engine mode | Typical use |
|---|---|---|
| `isqlsh.exe` | Embedded or TCP client | Scripts, automation, regression, interactive commands |
| `shell.exe` | Existing DLL family | General SQL shell |
| `shelldev.exe` | Existing DLL family | Development shell |
| `locshell.exe` | Static engine | Local/embedded shell |
| `shellonlib.exe` | Static engine | Static-library shell |
| `isqlserver.exe` | TCP server | Windowed server |
| `isqlserverd.exe` | TCP server | Console/headless server |

All continue to use their established startup and connection conventions. AI

commands are ordinary commands entered wherever that shell accepts SQL.

3. Starting isqlsh

3.1 Embedded mode


isqlsh.exe -dbhome C:\InternetSQLData

For a script:


isqlsh.exe -q -e -dbhome C:\InternetSQLData -cmd ai-demo.sql

The database home must contain its normal db directory. AI collection files

are placed below the selected database's table directory.

3.2 TCP client mode

Start an InternetSQL server using the deployment's normal database home and

port, then connect with:


isqlsh.exe -ip 127.0.0.1 -port 1973 -u kamranga -pwd manager

For unattended production use, do not place passwords directly in shared

scripts or process listings. Use the credential mechanism appropriate to the

deployment.

AI search results use the normal query/cursor transport. An equivalent query

therefore has the same fields and values in embedded and TCP modes.

4. Five-minute example

Save this as ai-demo.sql:


SHOW AI DEBUG ON;

CREATE AI TABLE product_embeddings DIM 4 METRIC COSINE;

INSERT VECTOR INTO product_embeddings ID 1001
VALUES [0.90,0.10,0.00,0.20]
META 'category=database';

INSERT VECTOR INTO product_embeddings ID 1002
VALUES [0.82,0.18,0.02,0.25]
META 'category=database';

INSERT VECTOR INTO product_embeddings ID 1003
VALUES [0.00,0.05,0.95,0.10]
META 'category=network';

SEARCH AI product_embeddings
QUERY [0.90,0.12,0.05,0.22]
TOP 2;

SHOW AI STATUS product_embeddings;
SHOW AI TRACE LAST 30;
SHOW AI DEBUG OFF;

Run it:


isqlsh.exe -q -e -dbhome C:\InternetSQLData -cmd ai-demo.sql

Search results contain:

| Column | Meaning |
|---|---|
| `RANK` | One-based position after metric ordering |
| `VECTOR_ID` | ID supplied by `INSERT VECTOR` |
| `SOURCE_KEY` | Stable source-row key returned for relational joins |
| `SCORE` | Metric-specific distance or score |
| `METADATA` | Stored metadata when requested by the engine path |

5. AI command reference

5.1 CREATE AI TABLE


CREATE AI TABLE collection_name DIM dimension METRIC metric;

Related collection:


CREATE AI TABLE article_ai FOR TABLE articles KEY article_id
TEXT (title,body) DIM 384 METRIC COSINE;

The table and fields are validated and stored as relationship metadata without

altering the relational table.

Supported metrics:

  • COSINE: smaller distance is closer; identical direction is normally 0.
  • L2: smaller Euclidean distance is closer; an identical vector is 0.
  • DOT: larger dot-product score is better.

Examples:


CREATE AI TABLE support_articles DIM 384 METRIC COSINE;
CREATE AI TABLE image_features DIM 512 METRIC L2;
CREATE AI TABLE recommendations DIM 128 METRIC DOT;

Collection names must pass the engine's collection-name validation. Creating

an existing collection is an error. Dimension and metric are permanent for

the collection; use a new collection to migrate to another embedding model or

dimension.

5.2 INSERT VECTOR


INSERT VECTOR INTO collection_name ID integer
SOURCE_KEY 'stable-key'
VALUES [number, number, ...]
[META 'text'];

For an explicit multi-insert vector transaction:


BEGIN AI TRANSACTION;
INSERT VECTOR INTO support_articles ID 42 VALUES [0.1,0.2,0.3];
INSERT VECTOR INTO support_articles ID 43 VALUES [0.2,0.3,0.4];
COMMIT;

Use ROLLBACK instead of COMMIT to discard the staged vectors. Searches in

the same session can see staged vectors. Disconnecting without a commit rolls

them back. Without BEGIN AI TRANSACTION, INSERT VECTOR retains its original

autonomous behavior.

An AI transaction is limited to one collection; commit or roll back before

starting work on another collection.

Example:


INSERT VECTOR INTO support_articles ID 42
VALUES [0.10,-0.05,0.40,0.01]
META 'topic=recovery,language=en';

Rules:

  • The ID is an unsigned stable vector identifier and must be unique within the collection.
  • The number of values must exactly match the collection dimension.
  • Values must be valid finite floating-point input.
  • Metadata is currently an opaque text payload.
  • Reinserting an existing ID is an error; there is no SQL update/upsert command in this preview.

Collections created with FOR TABLE ... KEY ... require a non-empty stable

SOURCE_KEY on every inserted vector.

5.3 SEARCH AI


SEARCH AI collection_name
QUERY [number, number, ...]
TOP k
[METRIC COSINE|L2|DOT]
[WHERE metadata_predicate [AND metadata_predicate ...]]
[INTO TEMP result_table];

Examples:


SEARCH AI support_articles
QUERY [0.10,-0.05,0.40,0.01]
TOP 5;

SEARCH AI support_articles
QUERY [0.10,-0.05,0.40,0.01]
TOP 5
WHERE topic=recovery;

WHERE supports up to 16 metadata predicates joined by AND. Operators are

=, !=, <, <=, >, >=, IS NULL, and IS NOT NULL. Values that both

parse as numbers use numeric comparison; other values use text comparison.

Quoted text is supported. For compatibility, a filter containing no operator

performs the original case-sensitive metadata substring match. This is a

bounded metadata grammar, not the complete SQL expression grammar.


SEARCH AI support_articles QUERY [0.10,-0.05,0.40,0.01]
TOP 10 WHERE year>=2024 AND language='en' INTO TEMP current_english_hits;

The SQL and C API boundaries limit TOP to 256, dimensions to 4096, metadata

to 2048 bytes, stable source keys to 255 bytes, and filter text to 511 bytes.

Search is exact FLAT search. Results are deterministic and ordered according

to the selected metric. Do not compare scores produced by different embedding

models, dimensions, normalization choices, or metrics.

Hybrid join example:


SEARCH AI article_ai QUERY [0.10,-0.05,0.40,0.01]
TOP 10 INTO TEMP ai_hits;
SELECT ai_hits.RANK,ai_hits.SCORE,articles.title
FROM ai_hits,articles
WHERE ai_hits.SOURCE_KEY=articles.article_id
ORDER BY ai_hits.RANK;

The result table belongs to the current session and is removed automatically

when its query object is released or the session logs out. An application may

drop it earlier when it no longer needs the rows. A session may own at most 16

AI result tables at one time.

5.4 SHOW AI STATUS


SHOW AI STATUS support_articles;

This verifies that the named collection can be opened. The current preview

returns availability rather than detailed storage/index/job statistics.

5.5 DROP AI TABLE


DROP AI TABLE support_articles;

This removes the AI collection artifact. It does not drop or rewrite an

ordinary relational table. Back up required collection files before dropping.

5.6 Reserved LIST AI TABLE


LIST AI TABLE;

This syntax is recognized but intentionally returns not implemented yet in

the current release.

6. Debugging and trace support in every shell

6.1 Commands


SHOW AI DEBUG ON;
SHOW AI DEBUG STATUS;
SHOW AI TRACE;
SHOW AI TRACE LAST 50;
SHOW AI DEBUG OFF;

SHOW AI TRACE defaults to at most the last 200 events. LAST accepts 1

through 200. The trace file retains earlier events; the shell display is a

bounded tail for safe interactive inspection. Shell rows are truncated to 254

characters to fit the legacy query-result record limit; AI_TRACE.LOG retains

the complete event line.

SHOW AI DEBUG STATUS reports:

  • whether tracing is on or off;
  • the active AI_TRACE.LOG path;
  • that sensitive payload logging is redacted.

6.2 Trace from process startup

Set the environment variable before starting any shell or server:


set ISQL_AI_DEBUG=1
isqlsh.exe -dbhome C:\InternetSQLData

PowerShell example:


$env:ISQL_AI_DEBUG = '1'
.\isqlserverd.exe -dbhome C:\InternetSQLData -port 1973

This enables tracing before the first AI command. It is useful with windowed

shells, automated scripts, and server processes. Remove the environment

variable or issue SHOW AI DEBUG OFF when the diagnostic session ends.

6.3 Events currently traced

  • AI command routing;
  • database/vector initialization begin and success;
  • collection create parsing, validation, and commit;
  • collection drop begin and commit;
  • insert parsing, dimension validation, collection open, and commit;
  • search parsing, dimension/TOP/filter validation, collection open, completion, and emitted row count;
  • AI failures with bounded error text;
  • debug enable and disable events.

Each line includes local timestamp, process ID, thread ID, and stage name.

Example:


2026-08-16T22:30:15.120 pid=4100 tid=928 stage=create.validate collection=docs dim=3 metric=COSINE
2026-08-16T22:30:15.124 pid=4100 tid=928 stage=create.commit collection=docs
2026-08-16T22:30:15.137 pid=4100 tid=928 stage=search.complete collection=docs result_count=2

6.4 Redaction and model-internal limits

The trace does not record:

  • vector values;
  • metadata values;
  • document or prompt text;
  • passwords, API keys, authorization headers, or credential aliases' values.

When external providers are implemented, traceable information will include

provider/model configuration identity, request correlation, batch size, input

byte counts, provider-reported token usage, embedding dimension and safe

statistics/hash, latency, retries, retrieval/context counts, output-token

usage, and tool-call lifecycle.

Hosted OpenAI APIs do not expose their model weights, attention matrices,

hidden states, or internal transformer activations. No shell can display data

that the provider does not return. A future local provider may expose deeper

telemetry when its runtime supports it and an operator explicitly enables it.

7. Producing embeddings and synchronizing source tables

SQL supports numeric vectors, provider-generated text-query embeddings, and

durable source-table synchronization. External provider calls remain isolated

in isql-ai-provider-host.exe.

Operational rules:

  1. Select one model/configuration for a collection.
  2. Record its provider, model/version, dimension, preprocessing, and normalization.
  3. Apply exactly the same process to stored documents and search queries.
  4. Validate dimensions and finite values before submission.
  5. Batch and cache external embedding requests where appropriate.
  6. Rebuild into a new collection when changing models or dimensions.

OpenAI calls do not run inside the database engine. They run only in

isql-ai-provider-host.exe, which resolves a credential alias

from protected host configuration. Do not put an API key in SQL, metadata,

KAMRANGA.CFG, command arguments, or an AI trace.

8. Deterministic embedding API for tests

The existing AI code implements the compatibility embedding functions declared

in ivec_public.h through provider ABI version 1:


float vector[384];
unsigned int dim = 0;

if (ivec_embedding_init("deterministic") == IVEC_OK) {
    if (ivec_embedding_text_to_vector("test document",
                                      vector,
                                      384,
                                      &dim) == IVEC_OK) {
        /* Use vector only for repeatable development tests. */
    }
    ivec_embedding_shutdown();
}

debug is accepted as a compatibility alias for deterministic. The same

text and dimension produce the same values; different text normally produces

different values. These values have no semantic meaning and must not be used

as a production embedding model.

An unknown or missing provider fails explicitly with `embedding provider not

configured`. The implementation performs no network access.

Advanced embedded applications can use iaip_provider.h for provider

registration, health, batch embedding, bounded deterministic generation,

cancellation, and structured errors. ivec_search_text() embeds a query through

the explicitly selected provider and uses the normal filtered exact-search

path. See InternetSQL_AI_Provider_Reference.md.

The engine exposes durable SYNC AI TABLE, SHOW AI JOBS FOR, and

RETRY AI JOB commands using canonical SHA-256 content/chunk hashes,

pending/leased/ready/stale/failed states, bounded retries, expired-lease repair,

and idempotent stable vector IDs. See InternetSQL_AI_Synchronization_Jobs.md.

The isolated provider-host transport is also implemented. It uses an

ACL-restricted local named pipe, versioned framing, HELLO negotiation,

correlation validation, bounded deadlines, cancellation, supervised process

lifecycle, and structured errors. It supports deterministic offline fixtures

and the isolated OpenAI embeddings/Responses provider. SQL provider DDL and

SEARCH AI ... TEXT are available. See InternetSQL_AI_Provider_Host_Manual.md and

InternetSQL_OpenAI_Provider_Manual.md.

9. Relational relationship and hybrid-search pattern

Define the source relationship in the collection so InternetSQL validates the

table, key, text fields, source authorization, and stable mapping:


CREATE TABLE articles (
  article_id number(10),
  title char(120),
  body char(1000),
  embedding_state char(16)
);

CREATE AI TABLE article_ai FOR TABLE articles KEY article_id
TEXT (title,body) DIM 384 METRIC COSINE PROVIDER production_embeddings;

SYNC AI TABLE article_ai;
SHOW AI JOBS FOR article_ai;

Supported workflow:

  1. Create a related AI collection with FOR TABLE, KEY, and TEXT fields.
  2. Configure a protected embedding provider or use numeric application vectors.
  3. Run SYNC AI TABLE; monitor durable work with SHOW AI JOBS FOR.
  4. Retry bounded failed work with RETRY AI JOB after correcting its cause.
  5. Search into a bounded session INTO TEMP table.
  6. Join ranked SOURCE_KEY values to authorized relational source rows.

Ordinary relational files and vector collections do not claim a shared

two-phase commit. For one business operation that changes both stores, use the

documented retry/outbox pattern. Explicit BEGIN AI TRANSACTION, COMMIT, and

ROLLBACK provide atomic multi-vector changes within one collection.

10. Backup, restore, and removal

  • Stop writers or otherwise establish a consistent maintenance window.
  • Back up the normal database home and AI companion artifacts together.
  • Preserve file names, directory relationships, and permissions.
  • Test restoration into a separate database home.
  • Verify representative vector searches after restore.
  • Never delete an unknown .ivec file while the server is running.

Vector pages use checksummed WAL prepare/commit records, checkpoint/replay,

before/after images, torn-tail handling, and idempotent crash recovery.

isqlvec.exe supports verified EXPORT and atomic IMPORT workflows. These

facilities do not replace a coordinated whole-home backup: preserve relational

files, vector/catalog/WAL/job companions, configuration, policies, and audit

state together, and always perform a test restore.

11. Security guidance

  • Require authenticated server access and least-privilege accounts.
  • Restrict database-home filesystem permissions.
  • AI creation honors the existing createtable revocation; AI drop honors

droptable; vector insertion honors insert; searches and related

collection creation honor existing source-table and source-field select

revocations. Superuser behavior remains the existing InternetSQL behavior.

  • Protect TCP traffic at the deployment boundary according to organizational policy.
  • Treat embeddings and metadata as potentially sensitive derived data.
  • Do not log vector, metadata, prompt, or credential payloads.
  • Apply retention and secure deletion policies to AI_TRACE.LOG.
  • Validate application-generated SQL and never concatenate untrusted IDs or metadata.
  • Set resource limits appropriate to expected dimensions, result counts, and clients.

Debug trace files are operational records. They contain collection names, IDs,

dimensions, counts, process/thread IDs, timing, and error descriptions even

though payloads are redacted.

12. Troubleshooting

| Symptom | Likely cause | Action |
|---|---|---|
| `dimension mismatch` | Query/record vector length differs from collection | Verify the model and count values |
| Collection already exists | Reused name | Open/use it or select a migration name |
| Collection does not exist | Wrong database/name or missing artifact | Verify current database and backup |
| Duplicate vector ID | ID already stored | Use a unique ID; update/upsert is not available |
| No relevant results | Wrong model, metric, preprocessing, or data | Compare model configuration and evaluation queries |
| Trace unavailable | Debug has not emitted an event or path is not writable | Run `SHOW AI DEBUG ON`, an AI command, then `SHOW AI DEBUG STATUS` |
| Trace is empty after restart | New process/debug disabled | Set `ISQL_AI_DEBUG=1` before startup |
| `LIST AI TABLE is not implemented yet` | Reserved preview command | Track collection names operationally |
| `embedding provider not configured` | No usable provider is selected/configured for this operation | Create the required deterministic or OpenAI provider definition, verify its alias/model/credential configuration, and retry; use the deterministic provider only for offline tests |

Diagnostic sequence:


SHOW AI DEBUG ON;
SHOW AI STATUS collection_name;
SEARCH AI collection_name QUERY [/* correct dimension */] TOP 3;
SHOW AI TRACE LAST 50;
SHOW AI DEBUG OFF;

13. Compatibility and current limits

  • AI routing applies only to explicit AI command prefixes.
  • Legacy CREATE TABLE, INSERT, SELECT, VINSERT, and VECTOR remain on their existing paths.
  • Existing database homes do not require AI migration.
  • Numeric vector search continues to work with no embedding provider configured.
  • TOP is limited to 256 in the SQL adapter.
  • Dimension is limited to 4096 at both SQL and C API boundaries.
  • Metadata is limited to 2048 bytes, source keys to 255 bytes, filter text to

511 bytes, typed filters to 16 predicates, and session AI temp tables to 16.

  • Search is exact FLAT; IVF and HNSW are not implemented.
  • Metadata is opaque text with basic current filtering.
  • AI results support bounded session INTO TEMP tables and relational joins.
  • Provider-host integration, SQL provider administration, durable source

synchronization, MCP stdio/HTTP, and RAG/ASK AI are implemented and

qualified within the limits documented by their respective manuals.

For architectural roadmap and production gates, see

isql-AI\InternetSQL_MCP_OpenAI_RAG_Implementation_Plan.md and

isql-AI\InternetSQL_AI_Vector_Architecture_Completion_Plan.md in the release

workspace.

Back to contents

Manual 2 of 11

AI SQL User Manual

Internet SQL AI User Manual

Version 1.25 integrated vector-search milestone

1. What Internet SQL AI provides

Internet SQL AI adds persistent vector collections and similarity search beside the existing Internet SQL relational engine. It does not replace or reinterpret existing tables, transactions, joins, server operation, storage fields, or SQL syntax. Existing applications continue to use the same interfaces. AI commands are a separate command family and may be used in the same shell session as ordinary SQL.

This release supports:

  • persistent vector collections stored inside the selected database's table directory;
  • vector insertion with an unsigned integer ID and optional text metadata;
  • exact similarity search using cosine distance, Euclidean (L2) distance, or dot-product distance;
  • the same commands through an embedded shell or a remote Internet SQL server;
  • normal query-result rows for searches, so existing shell and remote result handling can display them;
  • a standalone isqlvec.exe utility for direct vector-store administration and diagnostics.

Provider-backed text embeddings, synchronization, and source-backed ASK AI

generation are available in embedded and TCP sessions. Numeric embeddings are

also accepted directly. Approximate HNSW/IVF indexes are not implemented; the

current search implementation is exact. See InternetSQL_AI_RAG_Manual.md for

the RAG security and result contract.

2. Start the shell

Embedded mode

The engine runs in the shell process. Replace the path with your database home:


x64bin\isqlsh.exe -dbhome C:\isqldbhome

For scripts, use quiet mode, stop on errors, and a command file:


x64bin\isqlsh.exe -q -e -dbhome C:\isqldbhome -cmd examples\ai_demo.sql

Remote mode

Start the server using its normal configuration or explicit database home and port:


x64bin\isqlserverd.exe -dbhome C:\isqldbhome -port 1973

Connect from another shell:


x64bin\isqlsh.exe -ip 192.0.2.10 -port 1973 -u kamranga -pwd manager

Use a non-default account and protected password in production. Network authentication and permissions remain those of the existing server.

3. Five-minute example

Enter the following statements. Every statement ends in a semicolon.


CREATE AI TABLE product_embeddings DIM 4 METRIC COSINE;

INSERT VECTOR INTO product_embeddings ID 1001
  VALUES [0.92, 0.10, 0.04, 0.20]
  META 'category=database;name=Internet SQL';

INSERT VECTOR INTO product_embeddings ID 1002
  VALUES [0.85, 0.18, 0.08, 0.25]
  META 'category=database;name=Vector Store';

INSERT VECTOR INTO product_embeddings ID 1003
  VALUES [0.02, 0.90, 0.70, 0.05]
  META 'category=hardware;name=Network Switch';

SEARCH AI product_embeddings QUERY [0.90, 0.12, 0.05, 0.22] TOP 2;
SHOW AI STATUS product_embeddings;
DROP AI TABLE product_embeddings;

Search returns four columns:

| Column | Meaning |
|---|---|
| `RANK` | One-based position after sorting by best match |
| `VECTOR_ID` | ID supplied when the vector was inserted |
| `SCORE` | Metric score: smaller is closer for COSINE/L2; larger is better for DOT |
| `SOURCE_KEY` | Stable key used to join a related relational row |
| `METADATA` | Optional text stored with the vector |

For cosine search, an identical vector has score 0. Interpret scores relative to other results produced with the same model, normalization, dimensions, and metric. Do not compare scores from unrelated embedding models.

4. Command reference

CREATE AI TABLE


CREATE AI TABLE collection_name DIM dimension METRIC metric;

Related collection syntax:


CREATE AI TABLE article_ai FOR TABLE articles KEY article_id
  TEXT (title,body) DIM 384 METRIC COSINE;

The source table, key, and listed text fields must already exist. InternetSQL

stores the relationship in the AI catalog without changing the source table.

  • collection_name identifies the persistent collection.
  • dimension is the exact number of floating-point values in every vector and must be greater than zero. The integrated adapter currently accepts at most 4,096 values per command.
  • metric is COSINE, L2, or DOT.

Examples:


CREATE AI TABLE support_articles DIM 384 METRIC COSINE;
CREATE AI TABLE image_features DIM 512 METRIC L2;
CREATE AI TABLE recommendations DIM 128 METRIC DOT;

Creating an existing collection is an error. Choose the dimension and metric to match the embedding model; changing either requires creating a new collection and reinserting vectors.

INSERT VECTOR


INSERT VECTOR INTO collection_name ID integer
  SOURCE_KEY 'stable relational key'
  VALUES [number, number, ...]
  META 'text';

To group vector inserts atomically, issue BEGIN AI TRANSACTION, perform the

inserts, and finish with the normal COMMIT or ROLLBACK command. The session

reads its own staged vectors, and an uncommitted disconnect rolls them back.

Standalone inserts remain autonomous for compatibility.

Each AI transaction is limited to one collection.

META is optional. IDs should be unique within your application. The number of values must match the collection dimension.


INSERT VECTOR INTO support_articles ID 42
  VALUES [0.12, -0.07, 0.44, 0.03]
  META 'article_id=42;language=en;area=backup';

Metadata is stored as bounded text. Semicolon-separated key=value pairs make

the fields usable by typed search predicates. Collections created with

FOR TABLE require a non-empty SOURCE_KEY on every new vector.

SEARCH AI


SEARCH AI collection_name QUERY [number, number, ...] TOP k
  [METRIC COSINE|L2|DOT]
  [WHERE text]
  [INTO TEMP table];
  • TOP must be from 1 through 256.
  • The query dimension must match the collection dimension.
  • Omitting METRIC uses the collection's configured metric.
  • WHERE accepts up to 16 metadata predicates joined by AND: =, !=,

<, <=, >, >=, IS NULL, and IS NOT NULL. Numeric-looking operands

compare numerically; other operands compare as text. A no-operator value

retains the original case-sensitive substring-filter behavior.

  • Put INTO TEMP after WHERE when both are used.

Examples:


SEARCH AI support_articles QUERY [0.10,-0.05,0.40,0.01] TOP 5;

SEARCH AI support_articles QUERY [0.10,-0.05,0.40,0.01]
  TOP 10 METRIC COSINE WHERE year>=2024 AND language='en';

The search is an exact scan. It favors correctness and predictable behavior over large-scale approximate-index performance.

Materialize hits and join them to the relational source:


SEARCH AI article_ai QUERY [0.10,-0.05,0.40,0.01]
  TOP 5 INTO TEMP ai_hits;
SELECT ai_hits.RANK,articles.title
  FROM ai_hits,articles
  WHERE ai_hits.SOURCE_KEY=articles.article_id
  ORDER BY ai_hits.RANK;

The named result uses the existing InternetSQL INTO TEMP table path. It is

session-scoped and is removed when its query object is released or the session

logs out. You may drop it earlier. A session can own at most 16 AI result tables.

SHOW AI STATUS


SHOW AI STATUS support_articles;

This verifies that the named collection can be opened. It does not perform a deep integrity scan or return row counts in this milestone.

DROP AI TABLE


DROP AI TABLE support_articles;

This permanently removes the collection file. Back it up before dropping it. Dropping an AI collection does not drop a relational table with the same name.

LIST AI TABLE is reserved but is not implemented in this milestone; it returns an explicit error.

AI debug trace

The interactive and scriptable shells can enable stage-by-stage diagnostics for

the current server process:


SHOW AI DEBUG ON;
SHOW AI DEBUG STATUS;
SHOW AI TRACE LAST 50;
SHOW AI DEBUG OFF;

The same commands work through all seven shipped shells and servers because the

facility is compiled into both existing engine artifacts (isqldll.dll and

isqllib.lib). Set the process environment variable ISQL_AI_DEBUG=1 before

starting any shell or server to enable tracing from its first AI command. This is

useful for GUI shells and unattended servers.

Trace events cover AI command routing, database/vector initialization, parsing,

validation, collection open/create/drop, insert commit, search execution, result

counts, result emission, and errors. The trace is stored as AI_TRACE.LOG in the

current database table directory. SHOW AI DEBUG STATUS reports the exact path.

The trace deliberately does not record vector values, metadata values, prompt or

document text, API credentials, or authorization headers. Provider integration

adds safe telemetry for provider/model configuration, batch sizes, input byte

counts, provider-reported token usage, embedding dimension/statistics/hash,

request timing, retrieval counts/scores, context sizing, generated-token usage,

and tool-call lifecycle.

Hosted model APIs do not expose model weights, attention matrices, hidden states,

or internal transformer activations. InternetSQL therefore cannot trace those

internals. A future local-model provider may emit provider-specific transformer

telemetry only when its runtime exposes it and the operator explicitly enables

that potentially sensitive, high-volume mode.

5. Using AI with relational SQL

AI collections and relational tables are deliberately parallel. Join them in your application by ID until a future table-valued search operator is available.


CREATE TABLE articles(
  article_id NUMBER(10) PRIMARY KEY,
  title CHAR(100),
  body TEXT(20000)
);

INSERT INTO articles VALUES(42,'Restoring a backup','...');
INSERT VECTOR INTO article_embeddings ID 42
  VALUES [0.12,-0.07,0.44,0.03]
  META 'language=en';

Application flow:

  1. Convert the user's text to an embedding with the same model used for stored vectors.
  2. issue SEARCH AI article_embeddings ... TOP 5;.
  3. Read the returned VECTOR_ID values.
  4. Fetch the corresponding relational rows, for example SELECT * FROM articles WHERE article_id=42;.
  5. Optionally supply those documents to a language model to produce a grounded answer.

This keeps existing relational semantics unchanged and makes AI adoption additive.

5a. Searching text and stored files: CONTAINS

CONTAINS is a WHERE-clause predicate that finds a piece of text anywhere in

a column, ignoring case. It works on char, varchar and text columns and

on storage columns, where it searches the file behind the key.


SELECT id FROM docs WHERE title CONTAINS 'engineer';
SELECT id FROM docs WHERE body CONTAINS 'kubernetes' AND title CONTAINS 'backend';
SELECT id FROM docs WHERE f CONTAINS 'rtos scheduler';    -- f is a storage column

For a storage column the whole file is read and searched:

  • a PDF is searched by its words. The engine extracts the text itself -

object streams, Flate-compressed content streams and ToUnicode font maps

are handled, which covers PDFs written by Word, Google Docs, browsers,

macOS and LaTeX. Encrypted PDFs and scanned images yield no text.

  • any other file is searched as bytes, so a marker inside a binary is

found, and a PDF the extractor cannot read still gets a byte scan.

CONTAINS never uses an index; it reads every row it is asked about. Put a

selective indexed condition before it when a table is large.

The word is recognised inside a WHERE clause only, the way LIKE is, so an

existing column named contains still works everywhere else.

The extractor is also exported from the engine library as

PdfExtractText(data, length, &outlen) / PdfFreeText(text) so an

application can index a PDF's words as it stores one. The Python API exposes

it as Connection._native.extract_pdf_text(bytes).

6. Producing embeddings

Internet SQL AI currently stores and searches numeric vectors; it does not choose or run an embedding model. Your application should:

  • select one embedding model and record its name/version;
  • use the same model for documents and queries;
  • verify its output dimension matches DIM;
  • preserve vector element order and floating-point signs;
  • avoid mixing normalized and non-normalized vectors unless the model documentation permits it;
  • batch or cache embedding calls outside the database when appropriate.

Pseudocode:


vector = embedding_model.embed(document_text)
assert length(vector) == configured_dimension
sql = "INSERT VECTOR INTO docs ID " + document_id +
      " VALUES [" + comma_separated(vector) + "] META 'source=manual'"
execute(sql)

Treat generated SQL as data-bound input in application code. Validate IDs, vector counts, finite numeric values, and metadata; escape or reject quote characters according to your client API.

7. Metric selection

| Metric | Typical use | Score behavior |
|---|---|---|
| `COSINE` | Text embeddings and direction-based similarity | `0` is identical direction; smaller is closer |
| `L2` | Spatial or feature vectors where magnitude matters | `0` is identical; smaller is closer |
| `DOT` | Models explicitly trained for inner-product ranking | Larger dot product is ranked first |

Use the metric recommended by the model provider. Relevance thresholds are model- and dataset-specific; establish them with representative evaluation queries rather than copying a universal cutoff.

8. Standalone isqlvec utility

isqlvec.exe directly exercises the same storage modules and is useful for diagnostics. Display its exact syntax for the current binary:


x64bin\isqlvec.exe --help

Prefer SQL commands for normal integrated and remote operation. Do not modify the same collection concurrently through isqlvec and a running server.

For offline operations, select an explicit database home and optionally pass a

command script:


x64bin\isqlvec.exe --db C:\InternetSQL\data operations.txt

The operational commands are LIST, INSPECT, VERIFY, REPAIR, EXPORT,

and IMPORT. REPAIR opens the collection through normal WAL recovery and

reconciles its catalog definition; it does not guess or fabricate vector data.


LIST
INSPECT support_docs
VERIFY support_docs
REPAIR support_docs
EXPORT support_docs TO "D:\backups\support docs.ivecexport"
IMPORT "D:\backups\support docs.ivecexport" AS support_docs_restored
GET support_docs_restored ID 1001

Export writes a versioned, durable text interchange file containing the

collection definition, record IDs, vectors, source keys, and metadata. It

publishes only after flushing the complete temporary file and refuses to

overwrite an existing destination. Import validates bounded file and line

sizes, dimensions, metrics, metadata and numeric values, then inserts every

record in one AI transaction. Any malformed record rolls back the transaction

and removes the newly created collection. Paths containing spaces must be

double quoted. Protect exports as database data because metadata and vectors

may contain sensitive derived information.

9. Persistence, backup, and recovery

Each collection is stored as a .ivec file below the selected database's table directory. Include these files in database backups. For a consistent backup in this milestone, stop writers—and preferably the server—before copying files.

The current vector store does not participate in legacy SQL COMMIT and ROLLBACK. A relational write and vector insertion are therefore not one atomic transaction. Applications that dual-write should use an ingestion state column or job queue so incomplete work can be detected and retried.

Example pattern:


1. Insert/update relational row with embedding_state='pending'.
2. Commit the relational transaction.
3. Generate and insert the vector.
4. Mark embedding_state='ready'.
5. Periodically retry rows still marked pending.

10. Errors and troubleshooting

| Symptom | Likely cause | Action |
|---|---|---|
| `collection already exists` | Name is already present | Open/search it or choose a new name |
| `dimension mismatch` | Vector length differs from `DIM` | Count values and confirm the embedding model |
| `unknown metric` | Metric is not COSINE, L2, or DOT | Correct the command |
| `file does not exist or cannot be opened` | Wrong database, missing file, or permissions | Confirm selected database and filesystem access |
| No relevant results | Wrong model/metric or unsuitable data | Verify model consistency and evaluate embeddings |
| Remote command rejected | Login, permissions, protocol, or server binary mismatch | Verify credentials and deploy matching DLL/server binaries |
| `LIST AI TABLE is not implemented yet` | Reserved future syntax | Track collections in deployment metadata for now |

Run the integrated regression tests from the source root:


powershell -NoProfile -File tests\regress_ai_sql.ps1 -Bin .\x64bin
powershell -NoProfile -File tests\regress_ai_sql.ps1 -Bin .\x64bin -Remote

11. Operational limits and security

  • Maximum adapter dimension: 4,096 floats.
  • Maximum TOP: 256.
  • Search is an exact scan; latency grows with collection size.
  • Metadata returned through integrated SQL is currently limited to 255 displayed characters.
  • Stored metadata is limited to 2048 bytes, stable source keys to 255 bytes,

filter text to 511 bytes, filters to 16 predicates, TOP to 256, and

dimensions to 4096.

  • AI create/drop/insert reuse the existing create-table/drop-table/insert

revocations. Related collection creation and search enforce existing

source-table and source-field SELECT revocations.

  • Collection state is process-global in the current vector core. Avoid simultaneous direct utility access and server access.
  • Vector pages use checksummed write-ahead logging, recovery, checkpoints, and

explicit AI transaction commit/rollback. This release does not provide

vector-file encryption at rest, per-tenant storage quotas, or a durable

approximate-nearest-neighbor index; exact scan remains authoritative.

  • Treat embeddings and metadata as potentially sensitive data. Apply filesystem access controls, authenticated server access, encrypted transport at the deployment boundary, backups, and retention policies appropriate to the source data.

12. Biometric enrollment, verification, and identification

InternetSQL 1.25 includes biometric provider support inside the existing

database engine artifacts. No additional product DLL or library is required.

The SDK includes the public native header at

include\biometric\isql_biometric.h, a runnable SQL template at

examples\biometric\biometric_quickstart.sql, and the detailed

InternetSQL_Biometric_User_Guide.md.

The basic fingerprint workflow is:


create biometric provider sourceafis modality fingerprint;
create biometric index employee_fingerprint on fingerprint;
biometric enroll employee0001 modality fingerprint provider sourceafis sample 'C:\samples\enroll.png';
biometric_verify employee0001 modality fingerprint provider sourceafis sample 'C:\samples\probe.png';
biometric identify modality fingerprint provider sourceafis sample 'C:\samples\unknown.png' top 5;
  • biometric enroll extracts and stores a template for a subject, modality,

and provider. Re-enrollment updates that enrollment.

  • biometric_verify performs one-to-one comparison and reports the match

decision, score, and threshold information.

  • biometric identify compares a probe with enrolled candidates and returns

the highest-ranked results up to the requested top limit.

  • Native C/C++ callers can use the public extraction, comparison, cleanup, and

status functions declared in isql_biometric.h through the existing

isqldll.lib/isqldll.dll or supported isqllib.lib integration.

Biometric samples and templates are sensitive personal data. Restrict database

homes and backups, use authenticated access and application authorization, and

define consent, retention, deletion, auditing, and incident procedures. Test

capture devices, image quality, thresholds, false-match rates, and

false-nonmatch rates on the intended population before deployment. The shipped

software regression is not a biometric-performance or regulatory certification.

13. Compatibility promise for this architecture

The AI implementation is additive. The legacy parser handles all prior SQL commands. Only statements beginning with the explicit AI command prefixes are routed to the AI adapter. Existing relational tables, inner and outer joins, transactions, local operation, parallel clients, remote servers, storage, and APIs remain on their established paths.

Future work should preserve that boundary: new AI capabilities should be introduced as additional commands, functions, or table-valued result sources, with compatibility regressions proving that existing behavior has not changed.

Back to contents

Manual 3 of 11

RAG Manual

InternetSQL Database-Native RAG Manual

InternetSQL 1.25 provides source-backed retrieval-augmented generation through

the existing isqldll.dll and isqllib.lib. No additional database DLL or

static library is required. The command works in embedded shells and through

the TCP server.

Complete example


CREATE TABLE support_articles
  (article_id char(20), title char(80), body char(255));

INSERT INTO support_articles (article_id,title,body)
VALUES ('KB-100','Reset a password',
        'Use the account page to request a password reset link.');

CREATE AI PROVIDER support_embed
TYPE 'openai' ENDPOINT 'default'
MODEL '<approved-embedding-model>'
CREDENTIAL 'OPENAI_PRODUCTION' TIMEOUT 60;

CREATE AI PROVIDER support_answer
TYPE 'openai' ENDPOINT 'default'
MODEL '<approved-generation-model>'
CREDENTIAL 'OPENAI_PRODUCTION' TIMEOUT 60;

CREATE AI TABLE support_ai
FOR TABLE support_articles KEY article_id TEXT (title,body)
DIM 1536 METRIC COSINE PROVIDER support_embed
CHUNK TOKENS 400 OVERLAP 40;

SYNC AI TABLE support_ai;

ASK AI USING support_ai
QUESTION 'How can I reset my password?'
TOP 5 GENERATOR support_answer WITH SOURCES;

Use models and vector dimensions approved for your deployment. Credentials are

referenced by alias and are resolved by the isolated provider host; never put an

API key in SQL, a provider definition, a command line, or a trace.

For offline installation tests, replace both provider types with

deterministic, use dimension 3, and insert or synchronize fixture data. The

deterministic provider is not intended to produce customer-facing prose.

Command contract


ASK AI USING ai_table
QUESTION 'quoted question'
TOP 1..32
GENERATOR provider_configuration
WITH SOURCES;

The AI table supplies the embedding provider and source relationship. The

named generator is an existing CREATE AI PROVIDER configuration. InternetSQL

uses the built-in, versioned grounded-v1 prompt template. Question size is

limited to 8192 bytes, selected context to 65536 bytes, and answer text to 255

bytes in this release. The answer limit matches the existing character-field

result format.

The result is a normal query table. Its columns are:

| Column | Meaning |
|---|---|
| `ROW_TYPE` | `ANSWER` for generated text or `SOURCE` for a citation |
| `ANSWER` | Generated text on the answer row |
| `REQUEST_ID` | Correlation identifier shared by all rows |
| `PROVIDER`, `MODEL` | Generator configuration and model |
| `TEMPLATE_VERSION` | Currently `grounded-v1` |
| `SOURCE_KEY`, `CHUNK_ID`, `RANK`, `SCORE` | Retrieval provenance on source rows |
| `INPUT_TOKENS`, `OUTPUT_TOKENS` | Provider telemetry on the answer row |
| `STATUS` | `COMPLETE` or `CONTEXT_TRUNCATED` |

Applications should display ANSWER and render each SOURCE row as a

clickable or auditable citation using the source key. Treat an answer as

untrusted model output even when citations are present.

Security behavior

InternetSQL checks SELECT permission on the configured source key and text

fields before embedding or generation. A denied user cannot cause source data

to be sent to a provider. Source text is marked as untrusted data, control

characters and prompt-like delimiters are neutralized, and generated text is

returned only as data. It is never executed as SQL and receives no MCP tools,

filesystem access, credentials, or policy authority.

Set ISQL_AI_DISABLE_PROVIDERS=1 (or the deployment-wide

ISQL_MCP_DISABLE_PROVIDERS=1) in the shell/server environment to disable

provider calls. Ordinary SQL and numeric SEARCH AI ... QUERY [...] remain

available. A server reads the variable from its own process environment, so

restart it after changing the setting.

Debugging and audit


SHOW AI DEBUG ON;
ASK AI USING support_ai QUESTION 'How do I reset my password?'
TOP 5 GENERATOR support_answer WITH SOURCES;
SHOW AI TRACE LAST 100;

RAG traces include routing, embedding start, generation start/completion,

request ID, byte counts, source count, model, template version, token counts,

and completion status. Questions, source bodies, generated answers,

credentials, and API keys are not written to the trace.

Common failures are explicit: missing relationship/provider, invalid TOP,

source permission denial, provider kill switch, missing generator, no matching

authorized source context, provider failure, or memory/resource limits.

Qualification

Run both supported paths from the source tree:


powershell -ExecutionPolicy Bypass -File tests\regress_ai_rag.ps1
powershell -ExecutionPolicy Bypass -File tests\regress_ai_rag.ps1 -Remote

The suite verifies deterministic answers and citations, malformed requests,

stored prompt injection non-execution, provider isolation, redacted traces,

embedded/TCP parity, and authorization before provider access.

Back to contents

Manual 4 of 11

MCP Manual

InternetSQL MCP Stdio Server Manual

For authenticated network deployment of the same tool core, see

InternetSQL_MCP_HTTP_Deployment.md. HTTP is an optional mode of the existing

isql-mcp.exe; stdio remains the default local transport.

Current scope

isql-mcp.exe is the policy-constrained Model Context Protocol adapter for InternetSQL.

It targets stable MCP revision 2025-11-25, communicates as newline-delimited

UTF-8 JSON-RPC on standard input/output, and connects to InternetSQL through

the existing isqldll.dll client API. It creates no additional DLL or static

library.

Without a named policy it retains the qualified M8 read-only behavior. With a

valid M9 policy it can further restrict reads and optionally expose one

separately classified, approval-controlled write tool.

Required environment

Run the adapter as a dedicated operating-system identity and supply its

least-privilege InternetSQL connection through the process environment:


$env:ISQL_MCP_SERVER = '127.0.0.1'
$env:ISQL_MCP_PORT = '1973'
$env:ISQL_MCP_DATABASE = 'support'
$env:ISQL_MCP_USER = 'mcp_support_reader'
$env:ISQL_MCP_PASSWORD = '<protected value>'
$env:ISQL_MCP_CLIENT_ID = 'support-assistant-production'
$env:ISQL_MCP_AUDIT = 'C:\InternetSQL\logs\mcp-audit.jsonl'
& 'C:\InternetSQL\bin\isql-mcp.exe'

Do not place the password in MCP tool arguments, an MCP client configuration

committed to source control, SQL files, trace logs, or command-line arguments.

The adapter does not support embedded owner-access mode. It requires an

authenticated TCP server session so normal InternetSQL authorization remains

authoritative.

Validate deployment configuration before starting an MCP client:


# Parses the policy, checks launch-identity bindings and reports enabled classes.
& 'C:\InternetSQL\bin\isql-mcp.exe' --check-policy

# Also opens and authenticates the configured InternetSQL TCP profile.
& 'C:\InternetSQL\bin\isql-mcp.exe' --check-config

Success prints one MCP_CONFIG_OK line and exits zero. Invalid policy,

identity mismatch, or failed database authentication prints

MCP_CONFIG_INVALID to standard error and exits 2. The report contains policy

capabilities and counts, but never the password, approval secret, provider

credential, SQL, or retrieved data. --check-policy is suitable for offline

CI validation; use --check-config on the deployed host as a readiness probe.

Available tools

  • isql_list_databases
  • isql_list_tables
  • isql_describe_table
  • isql_query
  • isql_vector_search
  • isql_text_search
  • isql_hybrid_search
  • isql_ai_status

isql_execute is absent by default. It is discovered only when a valid named

policy enables at least one write type and table and the write kill switch is

off. See [InternetSQL_MCP_Policy_Approval.md](InternetSQL_MCP_Policy_Approval.md)

for the complete format, identity binding, approval flow, and ACL guidance.

Named policy and approved write


$env:ISQL_MCP_POLICY = 'C:\InternetSQL\config\support-policy.json'
$env:ISQL_MCP_APPROVAL_SECRET = '<protected 32-byte-or-longer secret>'

The policy must match ISQL_MCP_CLIENT_ID, ISQL_MCP_DATABASE, and

ISQL_MCP_USER. Store the policy read-only for the MCP runtime identity. Give

the approval-state directory create-file permission but do not give ordinary

MCP clients permission to delete replay markers or read the approval secret.

Submit an allowed write without a token to receive its normalized digest:


{"name":"isql_execute","arguments":{"sql":"INSERT INTO ticket_feedback (ticket_id,rating) VALUES ('T-10','5')"}}

An operator or approval service running under the separate approval identity

passes that digest on standard input to the same executable:


$token = $digest | & 'C:\InternetSQL\bin\isql-mcp.exe' --issue-approval

The client resubmits the unchanged SQL with approval_token. The token is

expiring, single-use, HMAC protected, and bound to the policy, client,

database, and exact operation digest. Approval is consumed before the one

database attempt; failures are never retried as ambiguous writes.

Emergency controls:


$env:ISQL_MCP_DISABLE_WRITES = '1'
$env:ISQL_MCP_DISABLE_PROVIDERS = '1'

The provider switch leaves relational reads and offline numeric-vector search

operational. Neither switch is mutable through MCP.

isql_ai_status accepts a required collection and an optional configured

provider name. It returns collection status, provider health without a

credential alias, and the collection's bounded durable job rows.

Read-only query


{"name":"isql_query","arguments":{"sql":"SELECT ticket_id,subject FROM tickets ORDER BY ticket_id","max_rows":100}}

The adapter tokenizes and classifies exactly one SELECT statement. It rejects

stacked statements, comments, SELECT-INTO, DDL, DML, transaction, login,

provider, synchronization, file/import/export, and administrative keywords

before calling InternetSQL. Database table and row authorization is then

applied normally by the server.


{"name":"isql_vector_search","arguments":{"collection":"ticket_ai","vector":[0.1,0.2,0.3],"top":10}}

{"name":"isql_text_search","arguments":{"collection":"ticket_ai","text":"customer cannot reset password","top":10}}

Text search invokes only the provider explicitly bound to the collection.

Provider credentials remain in the isolated provider host.


{
  "name":"isql_hybrid_search",
  "arguments":{
    "collection":"ticket_ai",
    "text":"customer cannot reset password",
    "top":10,
    "source_table":"tickets",
    "key_field":"ticket_id",
    "columns":["subject","status"]
  }
}

Hybrid search creates a uniquely named session temporary hit table, joins its

stable SOURCE_KEY to the requested source key, applies normal InternetSQL

SELECT authorization to the source projection, serializes the bounded result,

and drops the temporary table. All object and column names must be simple

InternetSQL identifiers.

Enforced limits

  • one JSON-RPC object per line;
  • 1 MiB input message;
  • 4096 JSON tokens and nesting depth 128;
  • one SQL SELECT up to 32 KiB;
  • 256 result rows;
  • 64 result columns;
  • 16 KiB per cell;
  • 1 MiB serialized database result;
  • 4096 numeric vector dimensions;
  • 8192 bytes of text-search input;
  • 16 projected hybrid source columns.
  • one active database tool per MCP stdio parent; each tool is process-isolated.

Unknown JSON and tool fields are rejected. Duplicate keys, reused request IDs,

JSON-RPC batches, malformed UTF-8/JSON, non-finite vectors, unsafe identifiers,

and unknown methods/tools fail before database execution.

When ISQL_MCP_AUDIT is set, each tool call appends a flushed JSON-line event

containing UTC time, request correlation ID, MCP client identity, database,

tool, normalized object label, duration, row/byte counts, status, and bounded

error class. It excludes raw SQL, search text, retrieved cells, vectors,

metadata values, passwords, provider credentials, and approval tokens.

Verification

Run the offline protocol suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp.ps1

Run the configuration/readiness diagnostic suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp_config.ps1

Run the disposable TCP integration suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp_database.ps1

Run the named-policy, privilege, approval, replay, and kill-switch suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp_policy.ps1

The integration suite proves list/describe/SELECT, numeric search, provider

text search, hybrid joins, temporary cleanup, unknown-field rejection, and

that direct or stacked DDL through isql_query never executes. It also starts

a real long-running database query, cancels it with

notifications/cancelled, checks the bounded cancellation result, and proves

that the same MCP parent executes another authenticated tool afterward.

Back to contents

Manual 5 of 11

API-Compatible Provider Manual

InternetSQL OpenAI Provider Manual

Applies to: InternetSQL 1.25 source milestone M6

Audience: administrators, application developers, security reviewers, and support engineers

What is implemented

isql-ai-provider-host.exe contains an OpenAI provider for:

  • batched embeddings through POST /v1/embeddings;
  • text generation through POST /v1/responses;
  • configurable model identifiers and output dimensions;
  • strict bounded JSON/UTF-8 parsing and finite-float validation;
  • token usage and x-request-id telemetry returned over provider IPC;
  • authentication, configuration, rate-limit, transient, permanent, timeout,

cancellation, and protocol error classes;

  • bounded retry of retry-safe embedding calls, honoring a numeric

Retry-After value up to eight seconds;

  • TLS endpoint/host enforcement, explicit custom-host allowlisting, bounded

response sizes, and redacted errors.

The database engine never receives the API key and never makes the HTTP call.

It sends only a credential alias, provider/model identity, bounded content, and

operation limits over the local named pipe. The provider host resolves the

credential from its own process environment. OpenAI's current embeddings API

accepts one string or an array of strings and returns indexed float vectors;

the optional dimensions field is supported by text-embedding-3 and later

models. See the official [OpenAI embeddings API

reference](https://developers.openai.com/api/reference/resources/embeddings/methods/create).

Generation uses the Responses API and extracts output_text content from

structured message output. Model names deliberately have no compiled default.

See the official [OpenAI Responses API

reference](https://developers.openai.com/api/reference/resources/responses/methods/create).

Protected configuration

Configure the provider-host service identity, not an interactive database

session. For alias OPENAI_PRODUCTION, the host reads:


ISQL_AI_CREDENTIAL_OPENAI_PRODUCTION=<secret API key>

Alias rules are deliberately narrow: uppercase ASCII letters, digits, and

underscore. A credential value must never be placed in SQL, provider IPC,

KAMRANGA.CFG, database files, command-line arguments, trace output, test data,

or support bundles.

Validate the model name, alias syntax, and credential presence from the

provider-host service environment before enabling a provider:


isql-ai-provider-host.exe --check-openai text-embedding-3-small OPENAI_PRODUCTION

The command performs no network request. Success prints a single redacted

PROVIDER_CONFIG_OK line and exits zero. Missing/invalid configuration prints

PROVIDER_CONFIG_INVALID and exits 2. It reports only the model, alias,

presence, and implementation—never the credential value. Run it under the

same operating-system identity and protected environment as the provider-host

service. SHOW AI PROVIDER <name> then verifies the complete engine-to-host

IPC configuration after the SQL provider definition is created.

Supported provider-host environment settings:

| Setting | Default | Rule |
|---|---:|---|
| `ISQL_AI_OPENAI_ENDPOINT` | `https://api.openai.com/v1` | Full API base URL |
| `ISQL_AI_OPENAI_ALLOWED_HOSTS` | empty | Comma-separated additional HTTPS hostnames |
| `ISQL_AI_OPENAI_TIMEOUT_MS` | `30000` | 1,000-300,000 ms |
| `ISQL_AI_OPENAI_MAX_RETRIES` | `2` | 0-4; embeddings only |
| `ISQL_AI_OPENAI_ALLOW_HTTP_LOOPBACK` | disabled | Test-only `127.0.0.1`, `localhost`, or `::1` HTTP mock access |

api.openai.com is accepted only over HTTPS. Additional production hosts must

be named explicitly and also use HTTPS. Plain HTTP is rejected except for a

loopback mock when the test-only switch is exactly 1.

On Windows, place these values in the protected service configuration for the

account that runs isql-ai-provider-host.exe; restrict read and service-control

permissions to administrators and that identity. Rotate a key by replacing the

protected value and restarting the provider host. Never send the old or new

value through an SQL statement.

Engine-side configuration

The host-provider adapter uses a configuration like this at the C boundary:


IAIP_HostProviderConfig cfg;
const IAIP_ProviderV1 *provider = 0;
IAIP_Error error;

memset(&cfg, 0, sizeof(cfg));
strcpy(cfg.registry_name, "support_embeddings");
strcpy(cfg.provider_name, "openai");
strcpy(cfg.model_name, "<operator-approved-embedding-model>");
strcpy(cfg.credential_alias, "OPENAI_PRODUCTION");
strcpy(cfg.transport.pipe_name, "isql-ai-support");
strcpy(cfg.transport.host_executable, "isql-ai-provider-host.exe");
cfg.transport.connect_timeout_ms = 5000;
cfg.transport.request_timeout_ms = 60000;
cfg.transport.start_if_missing = 1;

if (iaip_host_provider_create(&cfg, &provider, &error) != IVEC_OK)
    report_redacted_error(error.status, error.message);

The host process command line contains only the local pipe leaf and parent PID.

Provider, model, alias, content, and credentials are not command-line values.

SQL provider administration, SEARCH AI ... TEXT, and provider-bound

SYNC AI TABLE are available in embedded and TCP sessions. A provider

configuration can also be named by ASK AI ... GENERATOR; see

InternetSQL_AI_RAG_Manual.md for the grounded prompt and citation contract.

Runtime behavior

Embeddings

The adapter sends a JSON array in one API request, includes the requested

dimension, requests float encoding, and validates every returned index.

Returned objects may arrive in a different order; InternetSQL restores caller

order. Missing, duplicate, or out-of-range indices, wrong dimensions,

non-finite values, malformed JSON, invalid UTF-8, or oversized responses are

protocol failures and no vector is committed.

HTTP 408, 429, and 5xx embedding responses are retryable. The provider uses a

bounded attempt count and exponential delay, or a bounded numeric

Retry-After value when supplied. Other calls are not silently repeated.

Responses generation

The adapter sends optional system text and required user text as distinct input

messages, sets store to false, and applies a bounded output-token request.

It concatenates validated output_text content from message output into the

caller-owned bounded buffer. Generated text remains data; it is never executed

as SQL.

Generation is not automatically retried after an HTTP response because a

timeout can be ambiguous and a repeated call can create duplicate provider

usage. A retryable classification is returned for policy-controlled callers.

Telemetry and redaction

Successful provider responses can return:

  • provider-reported input and output token counts;
  • external x-request-id;
  • vector count/dimension and bounded output sizes through normal AI tracing.

The implementation does not log or return authorization headers, API keys, raw

vectors, full prompts, response bodies, or provider error bodies. A remote

failure reports its HTTP class and request ID so an administrator can correlate

it with provider support without exposing content.

Hosted APIs do not expose model weights, attention matrices, hidden states, or

internal transformer activations. InternetSQL traces observable request,

retrieval, token, dimension, latency, status, and output stages only.

Offline contract test

Run:


internetsql_ai\test\run_all_tests.bat

The test starts a loopback HTTP mock and the real isolated provider host. It

validates authorization placement, request paths and JSON, out-of-order vector

indices, response text, usage, request IDs, 429 classification, redaction,

strict JSON, invalid UTF-8, excessive nesting, and all earlier provider-host

fault isolation. It uses the literal test credential only; it makes no Internet

request.

Opt-in live smoke

Live tests are disabled unless every required setting is explicit:


set ISQL_AI_OPENAI_LIVE=1
set ISQL_AI_OPENAI_EMBED_MODEL=<approved embedding model>
set ISQL_AI_OPENAI_RESPONSE_MODEL=<approved Responses model>
set ISQL_AI_CREDENTIAL_LIVE_OPENAI=<protected disposable test key>
internetsql_ai\test\run_openai_live_smoke.bat

Use a disposable project/key with a small spend limit and no customer data.

The smoke sends one short embedding request and one short response request. It

prints only pass/fail and redacted diagnostics. Remove the credential from the

environment after the test. Normal CI must leave ISQL_AI_OPENAI_LIVE unset.

Troubleshooting

| Result | Meaning | Action |
|---|---|---|
| Configuration error | Missing alias/model or disallowed endpoint | Validate names and protected host environment |
| Authentication | HTTP 401/403 | Rotate or correct the alias value; do not paste it into logs |
| Rate limit | HTTP 429 | Observe retry/budget policy and provider limits |
| Transient | HTTP 408/5xx or transport failure | Retry only through the bounded policy |
| Timeout | WinHTTP deadline expired | Check network/TLS and adjust the bounded host timeout |
| Protocol | Malformed/oversized JSON, wrong indices/dimensions, invalid floats | Capture request ID and safe diagnostics; reject the result |

OpenAI recommends securing API access and planning production capacity and

limits; see the official [production best-practices

guide](https://developers.openai.com/api/docs/guides/production-best-practices)

and error-code guide.

Back to contents

Manual 6 of 11

Provider Host Manual

InternetSQL AI Provider Host Manual

Architecture

isql-ai-provider-host.exe is the isolated execution boundary for model

providers. Database-side client, framing, validation, and provider adapter code

remain compiled into the existing isqldll.dll and isqllib.lib; no new DLL

or static/import library is introduced.

The host is a separate executable because provider networking, credentials,

timeouts, and third-party failures must not share the ordinary SQL engine

process. The host supplies the deterministic offline fixture and an isolated

OpenAI embeddings/Responses adapter. See

InternetSQL_OpenAI_Provider_Manual.md for protected configuration and tests.

Local transport

  • Windows local named pipes only; remote pipe paths are rejected.
  • Version-1 frames have a fixed 28-byte explicitly encoded header and bounded

TLV payload.

  • Pipe names are restricted to letters, digits, dot, underscore, and hyphen.
  • The host creates one pipe instance with a protected ACL for SYSTEM,

administrators, and the object owner.

  • HELLO/ABI negotiation is mandatory before provider operations.
  • Request/response opcode and 64-bit correlation ID must match.
  • Responses require RESPONSE and FINAL flags, a single structured status,

valid payload length, and bounded fields.

TCP is not a provider-host transport. Network model access belongs inside the

host adapter, never between the database engine and provider host.

Process supervision

When explicitly configured with start_if_missing, the engine starts the host

hidden with only:


isql-ai-provider-host.exe --pipe <validated-local-leaf> --parent-pid <pid>

No credential, model input, prompt, vector, or authorization header is placed

in process arguments. The host monitors the parent handle and exits when the

engine disconnects or dies. A client-owned host that does not exit promptly is

terminated during bounded cleanup.

Deadlines and cancellation

Client reads and writes use overlapped I/O with an overall request deadline.

Timeout cancels the pending OS operation and marks that connection unusable.

Cancellation is also a versioned provider operation identified by correlation

ID. Provider calls are serialized per connection; concurrent/multiplexed

provider execution is reserved for adapters that require it.

After a timeout, protocol violation, correlation mismatch, or broken pipe, the

caller discards the connection and opens a fresh supervised host. Ordinary SQL

and numeric vector search do not depend on host availability.

Provider ABI adapter

iaip_host_provider_create() returns an IAIP_ProviderV1 implementation, so

database code uses the same health, batch embedding, generation, cancellation,

and shutdown API for in-process test providers and isolated providers.

The configuration stores:

  • registry/provider/model names;
  • a credential alias, never a credential value;
  • local pipe leaf and host executable path;
  • bounded connect and request deadlines;
  • whether supervised startup is permitted.

Embedding responses are rejected unless item count and dimension match and

every float is finite. Generation responses are rejected unless there is one

bounded output within both request and caller capacities. Usage counts,

external request IDs, and retryable status are parsed as bounded typed fields.

The OpenAI adapter additionally enforces strict JSON/UTF-8/depth checks,

response-index uniqueness, endpoint allowlists, TLS verification, response

limits, and redacted HTTP errors.

Error isolation

Automated fixtures verify rejection and cleanup for:

  • bad magic, version, opcode, flags, reserved fields, and truncated TLVs;
  • oversized frames and payload-length mismatch;
  • mismatched response correlation;
  • malformed response header;
  • host crash/broken pipe;
  • slow host/deadline expiry;
  • recovery by connecting to a new healthy host after all fault cases.

The OpenAI mock suite also covers request construction, credential placement,

out-of-order embedding indices, structured Responses output, usage and request

ID telemetry, strict JSON, HTTP 429 classification, and error-body redaction.

These failures produce structured status classes without exposing request

payloads. A host error does not disable embedded numeric search or legacy SQL.

Current commands and packaging

The executable is built by

internetsql_ai\provider_host\build_provider_host_x64.bat and placed in

x64bin. It is also included in build_all_x64.bat.

The executable is not intended to be launched manually. The C host-provider

adapter creates and supervises it; SQL provider DDL will expose validated

administration in M7. Do not put an API key on its command line, in SQL, in

KAMRANGA.CFG, or in the database.

Back to contents

Manual 7 of 11

Python API Manual

InternetSQL Python API Manual

Status: supported M13 binding for InternetSQL 1.25 Windows x64.

What is shipped

release_sdk/python/internetsql.py is pure Python source. It loads the existing

isqldll.dll with ctypes; it does not build or require a Python extension,

another DLL, static library, or import library. Python 3.10 or newer is

recommended.

Embedded connection


from internetsql import Connection

with Connection.embedded(
    r"C:\InternetSQLHome",
    dll_path=r"C:\InternetSQLSDK\bin\isqldll.dll",
) as db:
    result = db.execute("SELECT id,name FROM customers;")
    for row in result.rows:
        print(row["ID"], row["NAME"])

The database home must already contain a db directory. Embedded connections

use owner access by default; set owner_access=False when the application will

perform its own login/authorization workflow.

TCP connection


with Connection.tcp(
    "127.0.0.1", 1973, "appuser", "protected-password",
    dll_path=r"C:\InternetSQLSDK\bin\isqldll.dll",
    database="default",
) as db:
    print(db.execute("SHOW AI STATUS support_vectors;").status)

Do not put credentials in source control. Obtain them from the application's

protected configuration or operating-system secret facility. Login values

containing protocol delimiters are rejected.

Results and errors

execute() returns an immutable QueryResult containing:

  • command_code — the native positive command result;
  • columns — engine column names, normally uppercase;
  • rows — an immutable tuple of string mappings;
  • status — the engine's bounded status text;
  • scalar(default) — the first cell or a supplied default.

Native failures raise InternetSQLError. Invalid local arguments raise

ValueError or TypeError. Using a closed connection raises

ConnectionClosedError.

InternetSQL 1.25 does not export a native bind-parameter API. Passing

parameters raises ParameterBindingNotSupported instead of pretending that

string interpolation is parameter binding. Keep object names fixed and

allowlisted; quote_literal() is supplied for data literals and escapes single

quotes. Never use it to construct table, field, provider, or collection names.

AI/vector operations


db.execute("CREATE AI TABLE products_ai DIM 3 METRIC COSINE;")
db.insert_vector("products_ai", 1001, [1, 0, 0],
                 metadata="category=laser;year=2026")
hits = db.search_vector("products_ai", [0.9, 0.1, 0], top=5,
                        metadata_filter="year>=2025")

The convenience methods validate collection names, finite vectors, metrics,

TOP bounds, and filter length. Provider-backed text search, synchronization,

RAG, trace inspection, and MCP-related administration use the same documented

SQL through execute():


answer = db.execute(
    "ASK AI USING support_ai QUESTION 'How do I restore a backup?' "
    "TOP 5 GENERATOR support_generator WITH SOURCES;"
)
print(answer.rows)

Vector transactions


db.begin_ai()
try:
    db.insert_vector("products_ai", 1002, [0, 1, 0])
    db.insert_vector("products_ai", 1003, [0, 0, 1])
    print(db.search_vector("products_ai", [0, 1, 0], top=2).rows)
    db.commit()
except Exception:
    db.rollback()
    raise

One AI transaction is limited to one vector collection. Standalone vector

inserts remain autonomous. An uncommitted close or disconnect rolls back.

There is no claim of two-phase atomic commit between legacy relational files

and vector collection files.

Threading, memory, and limits

  • A Connection serializes its calls but is not a shared worker pool. Create

one connection per independent worker/session.

  • Results are copied into Python strings before the next native call.
  • max_cell_bytes defaults to 1 MiB and rejects a copied native cell beyond

that application boundary.

  • Text decodes as strict UTF-8 by default; supply the database's actual

encoding when using legacy data.

  • Always use a context manager or call close(). Embedded process-wide engine

logs/dump handles may remain owned by the loaded DLL until Python exits, so

do not delete an active database home from within that process.

Run examples and tests


python release_sdk\python\examples\embedded_ai.py C:\InternetSQLHome C:\InternetSQLSDK\bin\isqldll.dll
powershell -ExecutionPolicy Bypass -File release_sdk\python\run_tests.ps1

The executable tests cover relational rows, typed results, AI creation,

read-your-own-writes, vector commit/rollback, validation, exception behavior,

unsupported parameter binding, idempotent close, and closed-session errors.

Back to contents

Manual 8 of 11

Release Qualification

InternetSQL 1.25 AI Release Qualification and Rollback

This document defines the reproducible release gate for the InternetSQL AI,

vector, provider, MCP, hybrid-search, RAG, shell, and SDK additions. It does

not claim that InternetSQL replaces every Oracle deployment. Qualification is

limited to the capabilities, workloads, platforms, and bounds actually tested.

Release artifact policy

Build production artifacts with:


build_all_x64.bat release
powershell -ExecutionPolicy Bypass -File tests\stage_ai_sdk.ps1 -SkipBuild
powershell -ExecutionPolicy Bypass -File tests\test_ai_sdk_package.ps1

The first command builds the existing isqldll.dll, isqldll.lib, and

isqllib.lib with the release CRT and optimization settings. AI features are

compiled into those existing engine artifacts. The package gate fails if any

other DLL, static library, or import library appears in the SDK archive.

Run the complete matrix and retain a timestamped transcript with one command:


powershell -ExecutionPolicy Bypass -File tests\run_ai_release_qualification.ps1

Use -SkipBuild only when the immediately preceding recorded operation was a

successful build_all_x64.bat release. Evidence is written under

tests\evidence; do not place credentials or customer data in that directory.

Mandatory evidence matrix

| Area | Reproducible evidence |
|---|---|
| Native vectors and recovery | `internetsql_ai\test\run_all_tests.bat` |
| Embedded and TCP AI SQL | `tests\regress_ai_sql.ps1`, also with `-Remote` |
| RAG, citations, injection containment | `tests\regress_ai_rag.ps1`, also with `-Remote` |
| Transaction isolation and termination | `tests\regress_ai_transaction_isolation.ps1`, also with `-Remote` |
| MCP protocol/fuzz bounds | `internetsql_ai\mcp\test_mcp.ps1` |
| MCP database, policy, approval, audit | `test_mcp_database.ps1`, `test_mcp_policy.ps1` |
| MCP HTTP/session/rate/cancellation | `test_mcp_http.ps1`, `test_mcp_http_rate.ps1`, `test_mcp_http_database.ps1` |
| Provider alias readiness | `internetsql_ai\provider_host\test_provider_config.ps1` |
| Legacy relational/storage compatibility | `regress_groupby*.ps1`, `regress_storage.ps1`, `regress_product_identity.ps1` |
| Endurance/performance/privacy | `tests\regress_ai_release_quality.ps1` |
| Fresh archive and examples | `tests\test_ai_sdk_package.ps1` |

The native suite covers checksummed WAL truncation/replay/checkpoint,

multi-page atomicity, crash points before/after prepare/data/commit,

create/drop recovery, lock timeout, malformed provider frames and JSON,

provider crash/slow/oversize isolation, OpenAI mock contracts, retry classes,

redaction, job lease recovery, poison jobs, and concurrent claims.

Qualification workload and supported interpretation

The default bounded quality workload persists 2,000 16-dimensional vectors,

runs 200 exact top-five filtered searches, verifies the collection, rejects a

malformed import atomically, and checks diagnostic output for fixture secrets.

Its release thresholds are 90 seconds per load/search phase and 512 MiB peak

working set. On the 2026-08-17 qualification host it measured approximately

7.4 seconds load, 0.3 seconds for 200 searches, and 3.7 MiB observed peak

working set.

These numbers are a regression baseline for that small bounded fixture, not a

customer capacity promise. Search is exact. Customers must benchmark their

own dimensions, record counts, metadata filters, storage, concurrency, source

tables, network latency, and provider service-level limits before sizing a

production deployment.

Privacy gate

Release tests use unmistakable fixture credentials and fail if their exact

values appear in provider/MCP diagnostic or vector workload output. Provider

and MCP audits additionally test that raw SQL, retrieved values, prompts,

vectors, API keys, passwords, approval secrets, and approval tokens are absent.

This is defense-in-depth evidence, not a substitute for restricting log and

service-account permissions or reviewing customer data-retention obligations.

Upgrade and rollback procedure

  1. Stop writers and provider/MCP services; confirm no isqlserverd, shell,

isqlvec, provider-host, or MCP process is using the selected database home.

  1. Back up the complete database home, including relational files, .ivec

collections, catalogs, WAL/job journals, configuration, policies, and audit

state. Hash and test-read the backup before upgrading.

  1. Preserve the previous signed binaries and configuration separately. Never

overwrite the only known-good package.

  1. Install the new release into a new program directory. Run provider

--check-openai, MCP --check-policy/--check-config, isqlvec VERIFY,

relational smoke tests, numeric search, and one grounded RAG test.

  1. If validation fails before writes resume, stop the new processes and restore

the previous binaries/configuration. The untouched database home remains

authoritative.

  1. If writes occurred, stop every process and restore the complete pre-upgrade

database-home snapshot as one unit before starting the previous binaries.

Do not mix old relational files with new vector/catalog/WAL files and do not

delete or hand-edit WAL pages to force a downgrade.

  1. Re-run VERIFY, relational integrity/smoke queries, user authorization,

provider/MCP diagnostics, and backup verification. Record the incident and

retain failed files for support analysis without including secrets.

AI transaction rollback protects one active collection transaction; it is not

a substitute for a whole-database pre-upgrade backup or point-in-time restore.

Release decision

Do not label a build qualified merely because it compiles. The release is

eligible only when the release build, every applicable matrix command, the

fresh archive gate, documentation review, and artifact hashes all succeed with

no unresolved critical or high-severity finding. Live OpenAI smoke testing is

optional and requires a disposable protected key; the deterministic and mock

provider gates remain mandatory and network-independent.

Back to contents

Manual 9 of 11

Biometric Support Guide

InternetSQL Biometric Support User Guide

InternetSQL 1.25 provides biometric enrollment, one-to-one verification, and

one-to-many identification for fingerprint, iris and face, through the existing

database engine DLL and static library. It does not add another product DLL or

library. See Modalities below for which providers exist; face, voice and

palm are not supported.

Quick start

Run examples\biometric\biometric_quickstart.sql after changing its sample

paths to absolute paths on the target computer:


bin\isqlsh.exe -e -dbhome C:\isqldbhome -cmd examples\biometric\biometric_quickstart.sql

The SQL workflow is:


create biometric provider sourceafis modality fingerprint;
create biometric index employee_fingerprint on fingerprint;
biometric enroll employee0001 modality fingerprint provider sourceafis sample 'C:\samples\enroll.png';
biometric_verify employee0001 modality fingerprint provider sourceafis sample 'C:\samples\probe.png';
biometric identify modality fingerprint provider sourceafis sample 'C:\samples\unknown.png' top 5;

biometric_verify reports whether the probe matches the enrolled subject.

biometric identify returns the highest-ranked candidates up to the requested

top limit. Re-enrolling the same subject/provider/modality updates the stored

enrollment rather than creating another catalog entry.

Modalities

Two modalities ship, and they are not equivalent in provenance.

| Modality | `modality` | Provider name | Matcher |
|---|---|---|---|
| Fingerprint | `fingerprint` | `sourceafis` or `sourceafis_fp` | SourceAFIS, third-party |
| Iris | `iris` | `iris` or `isql_iris` | InternetSQL's own, `polar-gradient-mask` v1 |
| Face | `face` | `face` or `isql_face` | InternetSQL's own, `lbp-uniform-grid` v1 (from 2026-10-01) |

Any other modality or provider name returns PROVIDER_UNAVAILABLE. There is

no voice or palm support; nothing in the engine accepts them. Face is

newer than the other two; the table says which release it arrived in.

Iris


create biometric provider isql_iris modality iris;
create biometric index employee_iris on iris;
biometric enroll employee0001 modality iris provider isql_iris sample 'C:\samples\iris_enroll.png';
biometric_verify employee0001 modality iris provider isql_iris sample 'C:\samples\iris_probe.png';
biometric identify modality iris provider isql_iris sample 'C:\samples\unknown_iris.png' top 5;

The template is a polar iris code of 32 radial by 256 angular samples - 8192

bits of code plus an 8192-bit occlusion mask, so 2 KB plus a header carrying

the detected centre, the pupil and iris radii, a quality figure and a checksum

over both planes.

Comparison is a masked Hamming distance over the bits both templates mark

valid, searched across rotations of -12 to +12 angular steps - about 17 degrees

either way, since one step is 360/256 of a turn. The reported score is that

distance, so lower is a better match, which is the opposite sense to the

fingerprint score. The default threshold is 0.32; pass a different one to

override it.

A sample is rejected rather than enrolled badly when the image is smaller than

96 by 72, when the iris boundary would fall outside the frame, when fewer than

a fifth of the code bits survive the mask, or when the unwrapped texture has

too little variance to carry information. Those return LOW_QUALITY or

DECODE_FAILED rather than a weak template.

Provenance matters here. Fingerprint matching is SourceAFIS, which has a

public history and independent evaluations. The iris matcher is ours. It

implements the standard approach and its regression covers extraction,

enrollment, genuine and impostor verification, identification, corruption,

rotation and reenrollment - but no independent body has measured its error

rates. Treat the default threshold as a starting point for your own

measurement, not as a calibrated operating point.

Face

Availability. Face support ships in ISQL_1.25_x64_2026-10-01 and
later. On ISQL_1.25_x64_2026-09-20.3 and earlier, MODALITY FACE is
rejected with Expected MODALITY IRIS|FINGERPRINT.

create biometric provider isql_face modality face;
create biometric index employee_face on face;   -- see the note below
biometric enroll employee0001 modality face provider isql_face sample 'C:\samples\face_enroll.png';
biometric_verify employee0001 modality face provider isql_face sample 'C:\samples\face_probe.png';

CREATE BIOMETRIC INDEX ... ON <modality> does not check the modality name - ON voice is accepted and creates an index nothing can use. The modality is validated where it matters, on ENROLL, BIOMETRIC_VERIFY and IDENTIFY, which reject anything but the three in the table above.

The matcher is Local Binary Pattern Histograms. The frame is scaled to a

96 by 96 canonical crop and contrast-stretched; each interior pixel becomes an

8-bit code comparing it with its eight neighbours; codes are folded into the 59

uniform classes; and a histogram is taken over each cell of a 6 by 6 grid. The

template is therefore 36 cells of 59 bins, about 4 KB with its header.

Comparison is a cell-wise chi-square distance averaged over the cells that

carry counts in both templates. As with iris, lower is a better match - the

opposite sense to the fingerprint score. The default threshold is 0.22.

A frame is refused rather than enrolled weakly when it is smaller than 48 by 48,

when its standard deviation is under 8 grey levels, or when fewer than 35% of

its codes are uniform. Those return LOW_QUALITY or DECODE_FAILED.

Two limits decide whether this is usable for your purpose.

*There is no face detector.* The sample must already be a crop of one face,

roughly upright and roughly filling the frame. Given a group photo it will

compute a histogram of the group and compare that, perfectly happily, and tell

you nothing. Cropping and alignment are the caller's job.

*This is not a deep face embedding.* Modern face recognition uses a trained

neural network and this does not. LBP is robust to monotonic illumination

change, which is why the method is still worth having, but it is not robust to

pose, expression or age, and its error rates are far worse than a current

embedding model. It is appropriate for verifying a cooperative subject against

their own enrolment under similar capture conditions. It is not appropriate

for identification against a large gallery, for surveillance, or for any

decision with a serious consequence. The 0.22 default is a starting point, not

a calibrated operating point: no independent body has measured this matcher.

Face recognition also attracts specific legal duties that fingerprint and iris

may not, in several jurisdictions, including rules on notice, consent and

retention, and outright restrictions on some identification uses. Those are

deployment questions, not database questions, and they are yours.

Native API

Include include\biometric\isql_biometric.h. The public ABI exposes template

extraction, comparison, template cleanup, and status-name functions. Link with

the existing isqldll.lib and deploy isqldll.dll, or link with the existing

isqllib.lib for the supported static-engine configuration.

Deployment and security

  • Treat biometric samples and derived templates as sensitive personal data.
  • Restrict database-home and backup access with operating-system ACLs.
  • Use authenticated network access and application-level authorization.
  • Establish retention, deletion, consent, audit, and incident procedures that

meet the laws and contracts applicable to the deployment.

  • Validate capture devices, image quality, thresholds, false-match rates, and

false-nonmatch rates on the actual user population before production use.

  • Do not use the reference defaults as a safety, identity, legal, or regulatory

certification. High-consequence decisions require independent validation and

an appropriate human review or fallback process.

The included regression exercises provider extraction, enrollment, genuine and

impostor verification, identification, corruption handling, rotation, and

reenrollment. It is a software regression, not a biometric performance study.

Back to contents

Manual 10 of 11

Triggers and Stored Procedures

InternetSQL Triggers and Stored Procedures

InternetSQL 1.25 runs triggers and stored procedures. A trigger is SQL the

database runs for you whenever a table is written; a stored procedure is SQL you

save under a name and call later. Both are stored in the database, run inside the

engine, and need no application code.

This guide is for people writing SQL. It says what works, what is refused, and

what is not in this release — including two behaviours that will surprise anyone

coming from Oracle or PostgreSQL, in Error handling and Limits.

Runnable versions of everything here are in examples/triggers/.

Quick start


bin\isqlsh.exe -h C:\isqldbhome -f examples\triggers\04_row_triggers_old_and_new.sql

-h and -dbhome mean the same thing, as do -f and -cmd.


create table employee (empno number(6,0), ename char(30), salary number(10,2));
create table salary_log (empno number(6,0), was number(10,2), now_ number(10,2));

CREATE TRIGGER log_pay AFTER UPDATE OF salary ON employee FOR EACH ROW
BEGIN
  insert into salary_log (empno, was, now_) values (OLD.empno, OLD.salary, NEW.salary);
END;

insert into employee (empno, ename, salary) values (1, 'Anis', 5000.00);
update employee set salary = 5500.00 where empno = 1;

select empno, was, now_ from salary_log;
--  1 | 5000.00 | 5500.00

Nothing in the application asked for that row to be written. That is the whole

idea.

---

1. Why use them

1.1 A rule that cannot be bypassed

An application can enforce "every pay change is logged" only in the code paths

that remember to. A trigger is attached to the table, so it also covers the

batch job nobody documented, the correction somebody typed into isqlsh at

midnight, and the import written three years from now by someone who never read

your code. There is no path to the table that avoids it.

1.2 The database already knows what changed

Logging a change from the application means reading the row first, keeping the

old values, writing the new ones, then writing the log — three statements and a

race between them. A row trigger gets OLD. and NEW. handed to it:


insert into salary_log (empno, was, now_) values (OLD.empno, OLD.salary, NEW.salary);

One statement, no re-read, and nothing to get out of step.

1.3 Less traffic, and less to deploy

The body runs inside the engine, on the same connection, in the same

transaction. A rule that would be four round trips from a client is none. And

when the rule changes you issue one CREATE TRIGGER — you do not rebuild and

redeploy an application.

1.4 One definition for many clients

InternetSQL is reached from isqlsh, C, Python, ODBC, MCP and ICPP pages. A rule

written in the application has to be written again in each. A trigger or

procedure is written once and every client gets it, including the ones that did

not exist when you wrote it.

1.5 Procedures name a sequence so it stays correct

"Opening a pay run" might be four inserts in a required order. Named once:


CREATE PROCEDURE open_payrun
BEGIN
  insert into payrun (status) values('open');
  insert into audit_log (note) values('pay run opened');
END;

call open_payrun;

Callers no longer need to know the order, and a correction to the order reaches

every caller at once.

1.6 An audit trail that is evidence

Because the trigger cannot be bypassed and sees both sides of the change, its

output is worth something to an auditor. The contrast is the argument — a

statement trigger can only say *somebody updated employee*, while a row trigger

says *5000.00 became 5500.00, for Anis, at this time*. Section 4.2 shows both

recorded side by side from the same UPDATE.

Where a trigger is the wrong tool: anything that must *refuse* a write.

BEFORE triggers do not run in this release, so a trigger cannot veto. Keep

validation that must reject in the application or in column attributes. See

section 8.

---

2. Stored procedures

2.1 Syntax


CREATE PROCEDURE <name>
BEGIN
  <statements>
END;

CALL <name>;
RUN STORED PROCEDURE <name>;      -- the same thing
DROP PROCEDURE <name>;
LIST PROCEDURES;
| Rule | Behaviour |
|---|---|
| Name | up to 255 characters, unique per database |
| Body | up to 7999 characters, stored verbatim with its newlines |
| Parameters | **not supported** in this release |
| Re-creating an existing name | **replaces** it |
| Dropping one that does not exist | an error |
| `CALL` inside a body | allowed — a procedure may call another |

Re-creating replaces rather than adding, deliberately: two bodies under one name

would both be reachable and DROP would remove whichever the catalogue scan

reached first, so "create it again with the fix" would quietly leave the broken

one in place.

2.2 A body's SELECT does not return rows to the caller

CALL reports what the body did; it is not a query. A SELECT inside a body

executes, and its rows are not forwarded to you. To get data out, have the body

write a table and select from that table afterwards.

2.3 Example


CREATE PROCEDURE seed_employees
BEGIN
  insert into employee (empno, ename, salary) values (1, 'Anis',  5000.00);
  insert into employee (empno, ename, salary) values (2, 'Zahra', 6200.00);
END;

call seed_employees;
select empno, ename from employee;

examples/triggers/02_stored_procedures.sql covers nesting, replacement and

reading a body back out of the catalogue.

---

3. Triggers

3.1 Syntax


CREATE TRIGGER <name>
  {BEFORE | AFTER} {INSERT | UPDATE [OF <col>[, <col>...]] | DELETE}
  ON <table>
  [FOR EACH ROW | FOR EACH STATEMENT]
BEGIN
  <statements>
END;

DROP TRIGGER <name>;
LIST TRIGGERS [ON <table>];
| Clause | Required | Notes |
|---|---|---|
| `<name>` | yes | up to 255 characters, unique per database |
| `BEFORE` / `AFTER` | yes | **`BEFORE` is stored and does not run** — section 8 |
| `INSERT` / `UPDATE` / `DELETE` | yes | one action per trigger |
| `OF <col>, ...` | no | `UPDATE` only; without it, every column fires it |
| `ON <table>` | yes | must exist, must not be a system table |
| `FOR EACH ROW` / `FOR EACH STATEMENT` | no | **`ROW` when omitted** |
| `BEGIN … END` | yes | up to 7999 characters, at least one statement |

CREATE TRIGGER tells you what it stored and when it will fire:


Trigger 'log_pay' stored on employee: after update, for each row.
It will fire once per affected row.

Read that line. It is the cheapest way to catch a trigger armed on the wrong

action or the wrong scope.

3.2 OF <column> is worth using

Without OF, an update trigger fires on every UPDATE to the table,

including ones that did not touch the column you care about. `AFTER UPDATE OF

salary fires only when salary` is in the statement's column list.

3.3 Row scope and statement scope

| | `FOR EACH ROW` | `FOR EACH STATEMENT` |
|---|---|---|
| Body runs | once per affected row | once per statement |
| `OLD.` / `NEW.` | available | **not** available |
| An `UPDATE` touching 3 rows | 3 times | once |
| Use it for | recording what changed, per-row arithmetic | marking that a batch happened |

Both kinds can sit on one table and both fire. The status line counts them:


3 records updated.  [1 statement + 3 row trigger run]
1 records deleted.  [1 row trigger run]

3.4 OLD. and NEW.

Inside a FOR EACH ROW body, OLD.<column> is the row before the statement and

NEW.<column> is the row after. Which exists depends on the action, and not

arbitrarily:

| Action | `OLD.col` | `NEW.col` | Because |
|---|---|---|---|
| `INSERT` | — | yes | there is no previous row |
| `UPDATE` | yes | yes | both sides exist |
| `DELETE` | yes | — | there is no new row |

Referring to one that does not exist is an error naming it, not a blank:


Trigger 'bad_old': OLD.ename is not available here.
An INSERT has no OLD row and a DELETE has no NEW row.

Trigger 'bad_col': NEW.enmae cannot be resolved -
there is no column 'enmae' in this table.

Substituting nothing would turn values(OLD.ename) into values() and hand you

a syntax error pointing at a line you did not write.

Inside a quoted string, OLD. and NEW. are text. A body that mentions a

column name in a message keeps the message as written:


insert into audit_log (note) values('the words NEW.ename stay as text');

---

4. Worked examples

4.1 An audit trail with the before and after


create table audit_row (empno number(6,0), ename char(30),
                        was number(10,2), now_ number(10,2), what char(10));

CREATE TRIGGER row_level AFTER UPDATE OF salary ON employee FOR EACH ROW
BEGIN
  insert into audit_row (empno, ename, was, now_, what)
       values (OLD.empno, OLD.ename, OLD.salary, NEW.salary, 'update');
END;

update employee set salary = salary + 500.00;    -- one statement, three rows
select empno, ename, was, now_ from audit_row;

EMPNO | ENAME | WAS     | NOW_
1     | Anis  | 5000.00 | 5500.00
2     | Zahra | 6200.00 | 6700.00
3     | Karim | 4100.00 | 4600.00

4.2 The same event, both ways, so the difference is visible


CREATE TRIGGER stmt_level AFTER UPDATE ON employee FOR EACH STATEMENT
BEGIN
  insert into audit_statement (note) values('somebody updated employee');
END;

After the same three-row UPDATE, audit_statement holds one row saying

something happened, and audit_row holds three rows saying what. If your log has

to answer "who was paid what, and when", statement scope cannot do it.

4.3 Keeping a deleted row


CREATE TRIGGER del_row AFTER DELETE ON employee FOR EACH ROW
BEGIN
  insert into audit_row (empno, ename, was, what)
       values (OLD.empno, OLD.ename, OLD.salary, 'delete');
END;

delete from employee;        -- fires once per row removed

A delete trigger without OLD. would have nothing to record, which is most of

the reason row scope exists.

4.4 A derived table the database maintains


CREATE TRIGGER count_hire AFTER INSERT ON employee FOR EACH ROW
BEGIN
  insert into headcount (n, note) values (NEW.empno, NEW.ename);
END;

Nothing in the application has to remember to keep headcount in step.

4.5 A trigger and a procedure together

A trigger body may CALL a procedure, which is how one rule stays in one place

when several triggers need it:


CREATE PROCEDURE note_change
BEGIN
  insert into audit_log (note) values('employee changed');
END;

CREATE TRIGGER on_ins AFTER INSERT ON employee FOR EACH STATEMENT
BEGIN
  call note_change;
END;

CREATE TRIGGER on_del AFTER DELETE ON employee FOR EACH STATEMENT
BEGIN
  call note_change;
END;

4.6 The example files

| File | Shows |
|---|---|
| `01_audit_trail.sql` | the simplest useful trigger, end to end |
| `02_stored_procedures.sql` | create, call, nest, replace, drop, read back |
| `03_triggers_and_procedures_together.sql` | a trigger body calling a procedure |
| `04_row_triggers_old_and_new.sql` | row scope, `OLD.`/`NEW.`, and what is refused |

02 and 03 exit 1 on purpose: some of their statements are meant to be

refused, since that is what they demonstrate, so isqlsh reports how many failed.

Each file says so at the top. 01 and 04 exit 0.

---

5. Reading back what is defined


LIST TRIGGERS;                  -- every trigger in the database
LIST TRIGGERS ON employee;      -- just this table's
LIST PROCEDURES;

TRIGNAME   | TABLENAME | TIMING | ACTION | SCOPE     | COLUMNS | ENABLED | OWNER  | CREATED_UTC
stmt_level | employee  | after  | update | statement |         | 1       | system | 2026-10-06 17:29:19
row_level  | employee  | after  | update | row       | salary  | 1       | system | 2026-10-06 17:29:19

LIST TRIGGERS is the reliable way to see what will and will not fire. The

definitions also live in ordinary tables you can query:


select trigname, tablename, timing, action, scope, columns, enabled from trigdefs;
select body from trigdefs where trigname = 'row_level';
select procname, body from procdefs;

---

6. Error handling — read this one

A failed trigger does not fail the statement, and does not undo the write.

This engine has no statement rollback: there is no BEGIN TRANSACTION, and

uncommitted data is designed to survive. By the time a trigger body runs, the row

is written and cannot be taken back. Reporting the statement as failed would

therefore be untrue — the INSERT did insert. What failed is the trigger.

So a trigger failure:

  • leaves the write in place
  • reports on its own line, beginning TRIGGER FAILED
  • lets the statement report success

TRIGGER FAILED - the write was NOT undone: Trigger 'bad_col': NEW.enmae
cannot be resolved - there is no column 'enmae' in this table.

What this costs you, stated plainly:

  • isqlsh -e will not stop on a trigger failure.
  • A caller checking only the return code will not see it.
  • The message is the only signal. Check it.

Practical consequence: write trigger bodies that can be re-run safely, and treat

the TRIGGER FAILED line as an alert in anything that reads shell output.

A body's own earlier statements are not undone either. If a body does three

inserts and the third fails, the first two stand.

---

7. Limits

| Limit | Value | What happens at the edge |
|---|---|---|
| Nesting depth | 8 | refused, with a message naming the table and the depth |
| Rows captured per statement for row triggers | 50,000 | the row triggers are **refused for that statement** |
| Body length | 7999 characters | `CREATE` is refused, naming the actual length |
| Name length | 255 characters | `CREATE` is refused |
| Triggers per table | no fixed limit | all matching ones fire |

A body over the limit is never truncated:


The trigger body is 25202 characters; the limit is 7999. Nothing was stored -
a body cut off at the limit would look stored and behave differently.

A trigger whose body writes its own table fires itself; that is what the depth

limit is for. Rows written before the limit was reached stay.

The 50,000 figure is a refusal rather than a truncation on purpose: a rule

applied to the first 50,000 rows and not the rest is worse than one that plainly

did not run. It says so:


Trigger 'on employee': the statement affected more than 50000 rows, which is
more than row triggers can hold, so NO row trigger ran for it. The statement
itself completed.

Statement triggers have no such limit — they run once regardless of the row

count, which is one reason to use statement scope for "a batch happened" and row

scope for "this row changed".

Row bodies run after the whole statement, not interleaved with it. A body that

reads the table being modified sees the finished statement. For recording what

happened this makes no difference; for reading back mid-statement it does.

---

8. Not in this release

| Not supported | What happens if you write it |
|---|---|
| `BEFORE` triggers | **stored and skipped**; each write reports how many it passed over |
| A trigger that refuses a write (veto) | not possible — `BEFORE` does not run |
| `WHEN (<condition>)` | refused, nothing stored |
| `REFERENCING OLD AS o` | refused, nothing stored |
| `INSTEAD OF` | refused — *Expected 'BEFORE' or 'AFTER', found 'INSTEAD'* |
| `AFTER SELECT` | refused — *Invalid trigger action 'select'* |
| `AFTER CREATE` / `DROP` / `ALTER` / `LOGIN` | refused as *reserved and not implemented* |
| `AFTER INSERT OF <col>` | refused — `OF` applies to `UPDATE` only |
| A trigger on a system table | refused |
| `BEGIN END` with no statements | refused — a trigger that does nothing is just a name |
| Procedure parameters | refused, nothing stored |
| `ALTER TRIGGER … ENABLE / DISABLE` | not implemented; `DROP` and re-create |
| ICPP as the body language | bodies are SQL in this release |

Everything in that table is refused with nothing stored, and the message says

what to do instead where there is something to say:


WHEN is not implemented in this build. Nothing was stored. Put the condition
in the trigger body instead, as an IF around the statements.

REFERENCING is not implemented in this build. Nothing was stored. The row is
reached as OLD.<column> and NEW.<column>, which cannot be renamed.

Procedure parameters are not implemented in this build; nothing was stored.
There is no SQL variable in this dialect for an argument to bind to. Write the
values into the body, or read them from a table.

WHEN and REFERENCING are refused rather than ignored for a specific reason:

accepting either would mean silently dropping it, and a trigger that fires

unconditionally when its author wrote a condition is the dangerous outcome.

BEFORE is stored and skipped rather than quietly run early, because a BEFORE

trigger that cannot refuse a write is only an AFTER that runs at a different

time — and silently pretending otherwise is the failure this feature exists to

avoid. Every write tells you how many triggers it skipped.

One display caveat: the skipped-trigger notice travels in the status line, and a

client with rows to print shows the rows instead. It is visible after UPDATE

and DELETE, and easy to miss after INSERT. LIST TRIGGERS always tells you.

---

9. Troubleshooting

| Symptom | Likely cause |
|---|---|
| Trigger never fires | it is `BEFORE` — section 8. Check `LIST TRIGGERS` |
| Fires once when you expected per row | `FOR EACH STATEMENT` was written, or the trigger was re-created without `FOR EACH ROW` |
| `OLD.` errors on an `INSERT` | there is no previous row — section 3.4 |
| `NEW.x cannot be resolved` | column name typo, or the column is not in that table |
| Fires on updates you did not care about | add `OF <column>` |
| Body ran partly | a statement in it failed; nothing is undone — section 6 |
| `-e` did not stop a failing script | trigger failures do not fail the statement — section 6 |
| Two triggers, only one error reported | a statement carries one message; the first failure wins |
| `drop trigger` says it does not exist | names are per database — check `use` and the spelling |

---

10. Where things are

| | |
|---|---|
| Examples | `examples/triggers/` |
| Trigger catalogue | `trigdefs` table, every database |
| Procedure catalogue | `procdefs` table, every database |

Both catalogue tables are created in every database, including databases made by

earlier releases — they are added on the next use.

Back to contents

Manual 11 of 11

Internet SQL User Manual (SQL, server speed modes)

Internet SQL 1.25 User Manual

The core SQL and shell language: every command the engine accepts, what it

actually does, and a worked example of each.

For the vector and AI commands — CREATE AI TABLE, INSERT VECTOR,

SEARCH AI — see InternetSQL_AI_User_Manual.md. This manual covers the

relational engine underneath them.

How to read this manual

**Every SQL example here was executed against the engine that ships in this

release** (build 2026-09-10, isqldll.dll sha256 dbe49dd1…), embedded, and

the output shown is what came back. Where a documented command did not work,

this manual says so in place rather than repeating the syntax as though it did;

section 17 collects those.

That matters because the built-in help text describes the language as

designed over the engine's lifetime, and parts of it are ahead of what the

current build implements.

---

1. Starting the shell

isqlsh is the shell. It runs the engine in-process (embedded) or talks to a

server over TCP.

Embedded

mkdir C:\isqlhome\db

isqlsh -dbhome C:\isqlhome

The database home must already exist and must contain a db subdirectory.

Nothing creates it; without it every start fails with

cannot start the engine on '<path>'.

-dbhome must be an absolute path. A relative one is rejected even when the

directory is right there.

Useful flags:

| Flag | Meaning |
|---|---|
| `-dbhome <dir>` | database home (absolute) |
| `-cmd <file>` | run a script file, then exit |
| `-f <file>` | same, alternate spelling |
| `-q` | quiet: suppress the banner |

A script run with -cmd returns a non-zero exit status if any statement failed,

which is what to test in automation — errors are printed to stdout, not stderr.

Over TCP

isqlserverd # on the server, reads kamranga.cfg from the cwd

isqlsh -dbhome C:\isqlhome -h <ip> -port <port>

The server accepts either kamranga.cfg or KAMRANGA.CFG. See the package

README for ISQL_COMM_POLL and worker sizing, and section 22 for

[ServerMode] - the speed and concurrency settings (default: unchanged).

Statement syntax

Statements end with ;. Keywords are case-insensitive. Identifiers are folded to

upper case in result metadata. A table or index name containing . must be

quoted: 'invoice.tbl'.

---

2. Data types

| Type | Notes |
|---|---|
| `int`, `long` | integers; `int(n)` fixes the digit count |
| `number(n)` | numeric, `n` total digits |
| `number(n,d)` | numeric, `n` total digits, `d` after the point |
| `char`, `char(n)` | fixed-width text |
| `varchar(n)` | variable text, max `n` |
| `text(n)` | long text |
| `enum('a','b',…)` | one of a fixed set |
| `date` | see the literal format below |
| `time` | time of day |
| `storage` | files, blobs, images — see section 11 |
| `dir`, `subdir`, `file` | virtual filesystem columns |

Date literals are MM-DD-YYYY

This is the single most common source of a puzzling error. A date is written

month-first and is stored and displayed as YYYYMMDD:

create table t(a long, dt date);

insert into t values(1,'01-15-2020');

select * from t;

1 | 20200115

'2020-01-15' and '15-01-2020' are both rejected with

Field:'DT': Character to number conversion error.

Column attributes, and why order matters

id long primary key auto increment -- accepted

id long auto increment primary key -- ERROR: Missing ')' ... found 'primary'

The attributes must appear in the order the help text lists them:

[key]|[primary|foreign key] then [not null] then [auto increment] then

default or formula. The error for the wrong order mentions a parenthesis,

not the ordering, so it misleads.

key auto increment and a bare auto increment are also accepted.

---

3. Five minutes end to end


create database sales;
use sales;

create table customer(
  id      long primary key auto increment,
  name    varchar(40) not null,
  city    varchar(30),
  balance number(12,2) default '0.00'
);

create index idx_city on customer(city);

insert into customer (name,city,balance) values ('Ada','Dhaka',1200.50);
insert into customer (name,city,balance) values ('Grace','Chittagong',300.00);
insert into customer (name,city,balance) values ('Linus','Dhaka',75.25);

select * from customer;

Output:

1 | Ada | Dhaka | 1200.50

2 | Grace | Chittagong | 300.00

3 | Linus | Dhaka | 75.25

1 | Ada | Dhaka | 1200.50

2 | Grace | Chittagong | 300.00

3 | Linus | Dhaka | 75.25

Each row appears twice, and that is not a duplicate. INSERT echoes the row

it wrote, so the first three lines are the three inserts and the last three are

the select. This surprises everyone once. A script of nothing but inserts

still prints a row per insert.

---

4. CREATE

CREATE DATABASE dbname;

CREATE TABLE name (field type [attributes], … );

CREATE TABLE name AS select … ;

CREATE INDEX name ON table(field,…);

CREATE VIEW name AS select … ;

CREATE CURSOR name AS select … ;

CREATE SUPERUSER username, password;

CREATE DATABASE then USE selects it. Only a superuser should create

databases.

A database name has at most 31 characters. The name travels in the network

message, so a longer one could never be reached through isqlserverd;

CREATE DATABASE and USE refuse it, embedded and remote alike, with

"Database name '…' is too long; database names have at most 31 characters."

Tables


create table emp(
  id     long primary key auto increment,
  name   varchar(20),
  dept   varchar(10),
  salary number(10,2),
  hired  date
);

Indexes


create index idx_city on customer(city);
create index idx_multi on customer(city,name);

Up to 48 fields per index; the summed field widths must be under 2048 bytes.

Indexes are ascending only in this release — the [asc|desc] in the help

text is not honoured. The usual workaround is a negated computed column:


create table f(a long, b long formula '-a');
create index idx_desc on f(b);

order by b then walks a downwards. Note the two formula limitations in

section 17 before relying on it.

Views


create view v as select a,b from t where a > 1;
select * from v;

2 | y

Table from a query


create table t2 as select a,b from t;

Creates and populates in one step. **Do not follow a two-column

CREATE TABLE … AS SELECT with OPEN CURSOR in the same session — the process

crashes.** See section 17.

---

5. ALTER

ALTER TABLE name ADD (field type [attributes], …);

ALTER TABLE name DROP (field, …);

ALTER TABLE name MODIFY (field newtype, …);

ALTER TABLE name RENAME (field = newfield, …);


create table t(a long, b varchar(10));
insert into t values(1,'x');
alter table t add (c long);
alter table t modify (b varchar(30));
alter table t rename (b = label);
alter table t drop (c);
select a,label from t;

1 | x

All four forms work. Several columns can be renamed in one statement:


alter table t rename (a = id, c = qty);

The rename is written to the table definition, so a later process sees the new

name and the old one is gone:

select a,label from t; -- 1 | x

select b from t; -- ERROR: Field 'b' not found

One caution: ALTER TABLE … ADD with a numeric default fails with the

unrelated message Invalid date string!!. A string default is accepted, and

CREATE TABLE takes numeric defaults normally — it is ALTER that is affected.

ALTER TABLE … RENAME killed the process in every release up to and including
ISQL_1.25_x64_2026-09-10. It is fixed in the build this manual ships with. If
you are on 2026-09-10 or earlier, use the add/copy/drop workaround instead:
alter table t add (label varchar(10)); update t set label = b; alter table t drop (b);

---

6. INSERT

INSERT INTO table [(field,…)] VALUES (value,…);


insert into customer (name,city) values ('Ada','Dhaka');
insert into t values(1,'x');

Naming the columns is optional; without the list, values are positional across

every column. A column with a formula is computed for you and must be left out

of the column list — supplying it explicitly stores your value and skips the

formula.

Remember that each INSERT echoes its row.

---

7. SELECT

SELECT [DISTINCT|UNIQUE] item, … FROM table [alias], …

[join clauses]

[WHERE condition]

[GROUP BY item,…]

[HAVING condition]

[ORDER BY item,… [ASC|DESC]]

[INTO [TEMP] table];

Items may be *, a field, table.field, a constant, an expression, or a

function, each with an optional alias.

Projection, filtering, ordering


select name,salary from emp where salary > 4500 order by salary desc;

Grace | 6200.00

Ada | 5000.00


select distinct dept from emp;

ENG

OPS

WHERE operators

| Operator | Meaning |
|---|---|
| `= < <= > >= !=` | comparison |
| `LIKE 'pat'`, `NOT LIKE` | `%` and `_` wildcards |
| `IN (v,…)`, `NOT IN` | set membership, literal list |
| `BETWEEN a AND b` | inclusive range |
| `=[n]`, `!=[n]` | match first `n` characters |
| `MATCH(s) IN (f,…)` | pattern against several fields |
| `AND`, `OR` | combination |

select name from emp where name like 'A%';          -- Ada
select name from emp where salary between 4000 and 5000;
select name from emp where dept in ('ENG');

IN takes a literal list only. **IN (subquery) and EXISTS (subquery) are

not implemented in this build** — section 17.

Aggregates

count(*), sum(), avg(), max(), min(), len() all work:


select count(*) from t;              -- 2.000000000000
select sum(sal) from t;              -- 11200.000000000000
select max(sal), min(sal) from t;    -- 6200.000000000000 | 5000.000000000000
select len(name) from t;             -- 3.000000000000

Aggregates come back as floating point with twelve decimal places, including

count(*). Format them in the application.

recno() is listed in the help text but is rejected by the parser

(Factor: Syntax Error: ().

GROUP BY


select dept, sum(sal) from emp group by dept;

ENG | 11200.000000000000

OPS | 4100.000000000000


select dept, count(*) from emp group by dept;
select dept, avg(sal), max(sal), min(sal) from emp group by dept;
select dept, sum(sal) from emp group by dept order by dept desc;

all work. HAVING with an aggregate does not — having sum(sal) > 8000

fails with Factor: Invalid Expression: (. Filter the grouped result in the

application, or select into a temp table and filter that.

Joins


select e.name, d.dname from emp e, dept d where e.dno = d.dno;
select e.name, d.dname from emp e inner join dept d on e.dno = d.dno;
select e.name, d.dname from emp e left outer join dept d on e.dno = d.dno;

All three give:

Ada | Engineering

Linus | Operations

Implicit joins, INNER JOIN … ON and LEFT OUTER JOIN … ON are verified.

INTO TEMP


select name from emp into temp t2;
select * from t2;

Ada

Linus

The result becomes a real table you can query again — the practical

substitute for the subquery and HAVING forms that are missing.

Set operators

UNION, INTERSECT and MINUS parse but return no rows in this build,

even between two tables of identical shape. Section 17.

---

8. UPDATE

UPDATE table [rowid selector] SET field = expr, … [WHERE condition];

Row selectors: rowid = n, rowid in (…), rowid between a and b,

rowid within (a .. b), each optionally negated with not.


update t set b='Q' where a=2;
update t rowid = 1 set b='R';
select * from t;

1 | R

2 | Q

3 | z

SET ALL = expr assigns every column.

---

9. DELETE and UNDELETE

DELETE FROM table [rowid selector] [WHERE condition];

UNDELETE FROM table [rowid selector] [WHERE condition];

Deletion marks rows rather than removing them, so it is reversible until you

PACK:


delete from t where a=3;
select * from t;        -- 1 | R    2 | Q
undelete from t where a=3;
select * from t;        -- 1 | R    2 | Q    3 | z

DELETE also has object forms — DELETE TABLE, DELETE INDEX, DELETE VIEW,

DELETE CURSOR, DELETE DATABASE — which are synonyms for the DROP forms.

---

10. DROP and RECOVER

DROP TABLE|VIEW|INDEX|USER|GROUP|HOST|DATABASE name;

RECOVER TABLE|VIEW|INDEX name;

DROP marks the object deleted; the catalogue still lists it with the deleted

flag set. RECOVER brings it back with its data:


create table t(a long);
insert into t values(1);
drop table t;
recover table t;
select * from t;        -- 1

PACK is what makes a drop permanent.

---

11. Storage columns — UPLOAD, DOWNLOAD, READSTORAGE

A storage column holds files, blobs and images, keyed by a string.

UPLOAD binary TO table t fieldname = f value = 'key' filename = 'path';

DOWNLOAD binary FROM table t fieldname = f value = 'key' filename = 'path';

READSTORAGE table field key offset length mode;


create table docs(id long, st storage);
insert into docs values(1,'');
upload binary to table docs fieldname = st value = 'doc1' filename = 'C:/tmp/in.txt';
readstorage docs st doc1 0 19 0;
download binary from table docs fieldname = st value = 'doc1' filename = 'C:/tmp/out.txt';

readstorage returns the bytes as a one-row result:

hello storage world

and the downloaded file matches the uploaded one byte for byte. Verified.

Notes:

  • Only the binary form exists. upload file … and upload proc … were never

implemented and the parser rejects them.

  • UPLOAD handles up to 65536 bytes per call. For larger values use

READSTORAGE to read back in pieces — it has no size limit.

  • WRITESTORAGE takes its data after the ;, so it cannot be typed at a shell

prompt (the shell splits statements at ;). Use UPLOAD, or

ExecSqlWriteStorage from C.

---

12. Transactions

COMMIT;

ROLLBACK;


insert into t values(1);
commit;
insert into t values(2);
rollback;
select * from t;        -- 1

For AI transactions (BEGIN AI TRANSACTION) see the AI manual: a transaction is

owned by the session — the user and database — so two connections authenticated

as the same user on the same database share one.

---

13. Housekeeping — PACK, ZAP, REINDEX

PACK TABLE t; | PACK DATABASE d; | PACK DATABASE ALL; | PACK DBLIST;

ZAP TABLE t;

REINDEX indexname;

PACK discards rows marked deleted and makes dropped objects permanent — after

it, UNDELETE and RECOVER have nothing to work with. ZAP empties a table

but keeps its definition.


delete from t where a=1;
pack table t;
select * from t;        -- 2
zap table t;
select * from t;        -- (no rows)

---

14. Users, groups and privileges

ADD USER name, password, privilege; -- privilege: a | m | o

ADD GROUP groupname, managername;

ADD HOST name, ip, port;

ADD DATABASE name;

ADD BACKGROUND COMMAND name, interval, 'sql;';

CHANGE PASSWORD newpassword;

CHANGE GROUP username newgroup;

CHANGE MANAGER username newmanager;

CHANGE USER PRIVILEGE username newprivilege;

CHANGE SUPERUSER newname newpassword;

LOGIN username, password; LOGOUT;

CONNECT dbname; DISCONNECT;

Privileges are a administrator, m manager, o ordinary.


add user alice, secret1, o;
list user all;

| kamranga | | kamranga | A | system | … | Admin | d |

| | | alice | o | system | … | user | d |

GRANT and REVOKE did not work in embedded mode in this build. Both the

grant select * from t to alice and grant insert into t to alice forms failed

(OpenDf: Can't open dbf file and Invalid Revoke command. respectively). The

full documented grammar is in help grant; treat it as unavailable here until

retested against a server. Section 17.

LOCK and UNLOCK cover tables (with INSERTLOCK, READLOCK, ALTERLOCK,

TABLELOCK, UPDATELOCK, DELETELOCK, optional field lists and record

ranges), plus databases, groups, background commands and SYSTEM.

---

15. Catalogue and settings — LIST, SHOW, SET

LIST returns rows


list table;

systable | T | N | system | 20260911 | 09485200

relation | T | N | system | 20260911 | 09485200

trigger | T | N | system | 20260911 | 09485200

t | T | N | system | 20260911 | 09485200

The third column is the deleted flag — Y after a DROP, until PACK.


list database;

default | | | | system | N | | N | 0 | F

d | | | | system | N | | N | 0 | F

Other forms: list user all, list user <name>, list privilege <user>,

list index on <table>|all, list index key <index>, list index info <index>,

list lock all|<table>, list table attributes, list host, list team,

list logged users, list background command, list primary relation <table>,

list foreign relation <table>.

SHOW writes to the status buffer, not to rows

SHOW VERSION; DATABASE; HOME; HOST; USER; DATE; TIME; MODE;

SHOW USER ACCOUNT SUMMARY;

These print nothing in the shell. They place their answer in the query

status buffer, which a C program reads with GetSqlQueryStat(). This is by

design — the API help documents it — but it looks like a broken command from a

prompt.

SET

SET CURSOR MODE ON|OFF;

SET TABLE PERMISSION t (groupread=y, allwrite=n, …);

SET DEBUG ON|OFF; SET ERROR ON|OFF;

SET COMPRESSION ON|OFF; SET ENCRYPTION ON|OFF;

SET MONITOR ON|OFF; SET MONITOR OUTPUT SCREEN|LOGFILE;

SET LOGFILESIZE megabytes;

SET TABLE GROUP t groupname; SET TABLE OWNER t ownername;

SET USER INFO userid (firstname=…, lastname=…, contactinfo=…);

SET BACKGROUND COMMAND ATTRIBUTE name (…);

SET BACKGROUND TIMER MINUTE = n;

Permission keys are group… and all… crossed with

read|write|alter|lock|drop|revoke|delete.

SET XBASE MODE, SET NETWORK MODE and SET USEINDEX MODE are obsolete and no

longer supported.

---

16. Scripts, import and export

INCLUDE TABLE t; INCLUDE DATABASE d;

RUN COMMAND SCRIPT 'file'; RUN ISPL 'file';

EXPORT FROM t TO t2 mode=append|overwrite format=xBase|ISQL;

IMPORT FROM t TO t2;

SERVER 'ip','port';

ZAP TABLE t;

SERVER retargets the shell at a different host without restarting it.

Quote the filename, and give a full path. The SQL lexer ends a bare word at

a . or a :, so RUN ISPL proc.ispl tries to open proc and

RUN COMMAND SCRIPT C:/tmp/s.sql tries to open C. In quotes the whole string

arrives intact. The path is resolved against the engine's working directory,

which is not the caller's, so a relative name generally will not be found.

There is no RUN STORED PROCEDURE. Earlier editions of this manual listed

RUN STORED PROCEDURE p args; no such form exists in the grammar and

run stored procedure p1; answers Run command syntax error. A procedure is an

ISPL file run by name with RUN ISPL. The engine creates a proctable system

table for procedures stored by name, but nothing writes to it or reads it, so

that half is not usable either.

---

17. What this build does not do

Everything here was observed on the 2026-09-10 build. Each is either absent or

defective, and none of it is guesswork.

Crash — avoid this sequence.

| Sequence | Result |
|---|---|
| two-column `CREATE TABLE … AS SELECT`, then `CREATE CURSOR`, then `OPEN CURSOR` | process dies (SIGSEGV / 0xC0000005); a one-column `AS SELECT` is safe |

Fixed since 2026-09-10: ALTER TABLE … RENAME used to kill the process

unconditionally. It works now — see section 5.

Fixed on 2026-10-02: CREATE TRIGGER, DROP TRIGGER, RUN ISPL and

RUN COMMAND SCRIPT each killed the process every time they were used. Three

separate defects: two uninitialised char * locals written through by strcpy

in the trigger statements; a compile-slot handle declared long, which is four

bytes in the x64 build, so the engine held a heap pointer with its top half cut

off (this is why ISPL worked in the x86 build and not this one); and a NULL

engine object passed to the script runner. None of them corrupted a database —

the fault came before any write. RUN ISPL and RUN COMMAND SCRIPT work now;

triggers do not, see below.

Triggers are not implemented. CREATE TRIGGER now checks the syntax and

then says so by name, rather than appearing to succeed or killing the process.

There is nowhere to keep a trigger — the system table called trigger holds

foreign keys, not trigger definitions — the body parser was never written, and

no INSERT, UPDATE or DELETE path looks for one. DROP TRIGGER likewise

reports that there is nothing to drop instead of returning a false success.

An ISPL script is parsed, not run. RUN ISPL 'file' reads and parses the

file and returns success. It does not execute main(), it reports no syntax

errors in the script, and the one builtin the language has — printf — produces

no output, because the compiler's messages are routed to a slot neither shell

drains. A script that is wrong and a script that is right are therefore

indistinguishable from the outside. Treat RUN ISPL as a syntax gate, not as a

way to run code, until that is finished.

Not implemented, though the help text describes them.

| Feature | What happens |
|---|---|
| `IN (subquery)` | `ParseInNotInSet: Missing ')'` |
| `EXISTS (subquery)` | `User defined functions are not allowed in this version.` |
| `HAVING` with an aggregate | `Factor: Invalid Expression: (` |
| `UNION` / `INTERSECT` / `MINUS` | parse, return no rows |
| `recno()` | `Factor: Syntax Error: (` |
| `OPEN CURSOR` | `Could not open cursor '<name>'` |
| `CLOSE CURSOR` | `Unrecognized command: close` |
| `GRANT` / `REVOKE` (embedded) | `OpenDf: Can't open dbf file` / `Invalid Revoke command.` |
| `CREATE INDEX … DESC` | accepted, but indexes are ascending only |
| `ALTER TABLE … ADD` with numeric default | `Invalid date string!!` |

formula columns. Fixed in this build — the first INSERT into a table with

a formula column used to kill the process. Two limits remain: a formula on a

text column evaluates to empty, and a numeric result is formatted as a float,

so a long column reads -100.000000. The value is correct and sorts and

compares correctly as a number.

Cosmetic. Aggregates return twelve decimal places, count(*) included.

---

18. ICPP and HTML pages

ICPP is the scripting side of the KCPP compiler suite. The same binary serves

three roles:

| Program | Role |
|---|---|
| `kcpp.exe` | compiler and ICPP front end — `kcpp -icpp s.c` runs, `kcpp -rtl s.c` emits Verilog, plain `kcpp s.c` compiles |
| `icpp.exe` | the same binary renamed; defaults to ICPP **execution**, so `icpp s.c` == `kcpp -icpp s.c` |
| `icpptpl.exe` | template processor for `.icpp.html` pages |

Keep them in one directory. icpp.exe and icpptpl.exe are small front ends

that contain no interpreter; they find kcpp.exe by explicit path, then

KCPP_EXE, then beside themselves, then PATH.

A script


int main()
{
  int a = 6;
  int b = 7;
  printf("%d times %d is %d\n", a, b, a*b);
  return 0;
}

> icpp.exe hello.c

kcpp: interpreting 'hello.c'

Total errors:0, Total warnings:0

kcpp: executing main() ....

6 times 7 is 42

Verified. Note ICPP prints its compile chatter before your output; redirect or

filter it when the output is a web page.

An HTML page

icpptpl.exe runs each <?icpp … ?> block and splices its iprint() output

into the surrounding HTML.


<html><body>
<h1>ICPP in an HTML page</h1>
<?icpp
   $a = 6;
   $b = 7;
   iprint("<p>", $a, " times ", $b, " is ", $a*$b, "</p>");
   iprint("<p>Hello, ", icpp_getparam("name"), "!</p>");
?>
</body></html>

icpptpl.exe hello.icpp.html -> 6 times 7 is 42

icpptpl.exe hello.icpp.html "name=Ada" -> Hello, Ada!

Serve it through Apache as CGI; APACHE_ISQL_ICPP.md has the httpd.conf.

Output is not escaped. iprint() writes its arguments verbatim and

icpp_getparam() returns the query string with its HTML meaning intact, so a

page that echoes a parameter reflects whatever the caller sent:

?name=<script>alert(1)</script> -> <p>Hello, <script>alert(1)</script>!</p>

That is measured, not theoretical. Escape <, >, & and " in the page's own

code and treat every icpp_getparam() value as untrusted.

Two smaller notes: a rendered page has \n written as CRLF and one extra

trailing newline, so a byte-exact comparison against a golden file needs newline

normalisation. And do not put a literal <?icpp inside an HTML comment — the

scanner treats it as a real block wherever it appears.

ICPP system functions

| Function | Returns | Purpose |
|---|---|---|
| `printf(fmt, …)` | — | C-style formatted output to stdout |
| `iprint(a, b, …)` | — | writes arguments into the page, verbatim, no separator |
| `icpp_getparam(name)` | string | one query-string parameter, undecoded |
| `icpp_sql_connect(dbhome)` | startid, 0 = failure | open the engine on a database home |
| `icpp_sql_query(startid, sql)` | cursor, 0 = failure | run a statement |
| `icpp_sql_count(cursor)` | row count | rows the query returned |
| `icpp_sql_next(cursor)` | — | advance to the next row |
| `icpp_sql_get(cursor, field)` | string | one field of the current row |
| `icpp_sql_free(cursor)` | — | release a cursor |
| `icpp_sql_disconnect(startid)` | — | close the connection |

printf is the only entry in the interpreter's system-function table; the rest

are built into the ICPP front end.

A complete use of the bridge:


int main()
{
  $home = "C:/isqlhome";
  $sid = icpp_sql_connect($home);
  if ($sid == 0) { iprint("cannot connect to ", $home, "\n"); return 1; }

  icpp_sql_query($sid, "use shop;");
  $cur = icpp_sql_query($sid, "select custid,name,city from customers;");
  $n = icpp_sql_count($cur);

  iprint("=== Customers (", $n, ") ===\n");
  $i = 0;
  while ($i < $n) {
    icpp_sql_next($cur);
    iprint(icpp_sql_get($cur,"custid"), " ",
           icpp_sql_get($cur,"name"), " ",
           icpp_sql_get($cur,"city"), "\n");
    $i = $i + 1;
  }

  icpp_sql_free($cur);
  icpp_sql_disconnect($sid);
  return 0;
}

Use forward slashes in ICPP path literals. The string lexer does not preserve

\\, and both Windows and the engine accept /.

**icpp_sql_connect() returns 0 from icpp.exe in this build, so the bridge
cannot reach a database from a script.** Confirmed again on 2026-09-10: the
interpreter initialises the engine once at startup, so the script's connect is
a second init and gets a null start-id back. Until it is fixed, do database
work in the web tier through PHP (php_isql) or a C CGI — both reach the same
engine through isqldll.dll and are verified working. The example above is
the reference for the call shape.

---

19. Java

Java reaches the engine through a small JNI shim that forwards to the ISQL C API

in isqldll.dll, using the embedded-owner pattern. The example lives in

examples/jni/.

| File | What it is |
|---|---|
| `IsqlJNI.java` | Java class with the `native` methods and a `main` |
| `isql_jni.c` | the shim, built to `isql_jni.dll` |

public class IsqlJNI {
    static { System.loadLibrary("isql_jni"); }

    private native long    open(String dbHome);           // handle, 0 = fail
    private native boolean exec(long obj, String sql);
    private native boolean next(long obj);
    private native String  data(long obj, String field);
    private native void    close(long obj);

    public static void main(String[] args) {
        String home = (args.length > 0) ? args[0] : "C:/isqlhome";
        IsqlJNI db = new IsqlJNI();

        long obj = db.open(home);
        if (obj == 0) { System.out.println("cannot open ISQL home " + home); return; }

        if (!db.exec(obj, "select custid,name,city from customers order by name;")) {
            System.out.println("query failed");
            db.close(obj);
            return;
        }
        while (db.next(obj)) {
            System.out.printf("%-6s %-16s %s%n",
                db.data(obj,"custid"), db.data(obj,"name"), db.data(obj,"city"));
        }
        db.close(obj);
    }
}

Build and run — match your JVM's bitness; a 64-bit JVM needs the x64 tools

and the x64 isqldll.lib:


javac -h . IsqlJNI.java

cl /LD /I "%JAVA_HOME%\include" /I "%JAVA_HOME%\include\win32" /I <isql>\h ^
   isql_jni.c /Fe:isql_jni.dll /link <isql>\isqldll\msvc2017_build_x64\isqldll.lib

java -Djava.library.path=. IsqlJNI C:/isqlhome

isql_jni.dll and isqldll.dll must both be on java.library.path / PATH.

Unlike the SQL, Python and ICPP examples in this manual, **this one was not

executed during writing** — there is no JDK on the build machine. It is

reproduced from the shipped, previously verified example rather than re-tested.

---

20. Python

internetsql.py wraps isqldll.dll with ctypes. It ships in the SDK under

python/, with its own manual (InternetSQL_Python_API_Manual.md), examples and

tests.

Connecting


from internetsql import Connection

# embedded
db = Connection.embedded(r"C:\isqlhome", dll_path=r"C:\isql\bin\isqldll.dll")

# over TCP
db = Connection.tcp("10.0.0.5", 7777, "kamranga", "manager",
                    dll_path=r"C:\isql\bin\isqldll.dll")

Connection is a context manager, so with closes it for you.

Querying


import sys
sys.path.insert(0, r"C:\isql\python")
from internetsql import Connection

with Connection.embedded(HOME, dll_path=DLL) as db:
    db.execute("create database pydemo;")
    db.execute("use pydemo;")
    db.execute("create table customer("
               "id long primary key auto increment,"
               "name varchar(40), city varchar(30));")
    db.execute("insert into customer (name,city) values ('Ada','Dhaka');")
    db.execute("insert into customer (name,city) values ('Grace','Chittagong');")

    for row in db.execute("select id,name,city from customer order by name;").rows:
        print(row)

    print("count:", db.execute("select count(*) from customer;").scalar())

Output, verified:

{'ID': '1', 'NAME': 'Ada', 'CITY': 'Dhaka'}

{'ID': '2', 'NAME': 'Grace', 'CITY': 'Chittagong'}

count: 2.000000000000

Two things to note from that output. **Rows are dicts keyed by upper-case field

name, and every value is a string** — convert in your own code. And scalar()

hands back the aggregate in the engine's twelve-decimal form.

API

| Member | Purpose |
|---|---|
| `Connection.embedded(home, *, dll_path)` | open in-process |
| `Connection.tcp(host, port, user, password, *, dll_path)` | open over TCP |
| `execute(sql)` | run a statement, return a `QueryResult` |
| `QueryResult.rows` | list of dicts |
| `QueryResult.scalar(default=None)` | first column of the first row |
| `close()`, `closed` | lifecycle |
| `begin_ai()`, `commit()`, `rollback()` | transactions |
| `insert_vector(...)`, `search_vector(...)` | AI helpers — see the AI manual |
| `quote_literal(s)` | escape a string for embedding in SQL |

There is no parameter binding. Passing parameters to execute() raises

ParameterBindingNotSupported. Build statements with quote_literal() rather

than string formatting, or you have an injection hole.

Errors raise InternetSQLError; using a closed connection raises

ConnectionClosedError.

---

21. The C API

The engine's C and C++ interfaces are listed by help api and help object in

the shell, and documented in HOWTO_BUILD_C_APP.md. In outline:


long startid = ISQL_StartInternetSql(dbhome, serverip, serverport);
INTERNET_SQL_OBJECT *o = NewInternetSqlObject(startid);

o->Exec("select * from employee;");
while (o->Next())
    printf("employee=%s\n", o->Data("empname"));

if (!o->Exec("insert into employee (empname) values('rahat');"))
    printf("error=%s\n", o->Status());

FreeInternetSqlObject(o);
ISQL_StopInternetSql(startid);

LIST, SELECT, DESC, DESCRIBE, OPEN and READSTORAGE produce query

result objects; SHOW and the GET commands put their answer in the status

buffer, read with GetSqlQueryStat(). Free every result object you obtain.

---

22. Server performance settings — [ServerMode]

One setting chooses how the engine trades its original, conservative behaviour

for speed and concurrency. **The default is classic: with no setting the

engine and server behave exactly as before.** Nothing changes until you ask.

| `[ServerMode]` | Meaning |
|---|---|
| `classic` | the behaviour of every earlier build (default; also any unknown value) |
| `fast` | the speed-ups marked "fast" in the table below |
| `custom` | only the speed-ups named in `[ServerFast]` |

Set it in kamranga.cfg, inside ENTRY … END:

ENTRY

[DatabaseHome] /srv/isqlhome

[ServerPortNo] 21975

[ServerName] localhost

[ServerIpAddress] 127.0.0.1

[ServerMode] fast

END

or in the environment, which wins over the file:

ISQL_SERVER_MODE=fast classic | fast | custom

ISQL_SERVER_FAST=keepalive,... the list for custom; for fast, extras to add

ISQL_LOGIN_CACHE_SECONDS=600 see logincache

The setting is read by every program that loads the engine — the server,

isqlsh, kcpp/ICPP pages, your own C program — because several speed-ups are

on the client side, and readshare applies to embedded programs too. Give a

client the same mode as its server; section 22.4 says what happens when they

differ.

Older builds reject a kamranga.cfg that contains [ServerMode] (they

refuse any key they do not know, with Error '[ServerMode]' at line …). If the

same file is shared with an older engine, use the environment variables instead.

22.1 The speed-ups

Names for [ServerFast] / ISQL_SERVER_FAST, separated by commas or spaces.

In fast mode a name adds to the set and -name removes one.

| Name | What it does | Where | In `fast` |
|---|---|---|---|
| `readshare` | Read-only statements (`SELECT` without `INTO`, `DESCRIBE`, `SHOW`, `LIST`) take the home's statement lock **shared**, so reads in different processes run at the same time; any other statement still runs alone. Writers are not starved: a waiting writer stops new readers from starting. | engine (server and embedded) | yes |
| `unlockstream` | The server queues a statement's result rows in memory and sends them **after** leaving the statement lock, so a slow client no longer holds every other statement up. Over 64 MB the rest is sent directly, as in classic. | server | yes |
| `loginunlock` | A login's key derivation (Argon2id, 64 MiB, see `ENCRYPT` columns) runs **before** the statement lock is taken, so other statements are not held up by it. It is also skipped when its result could only be thrown away (no user directory to keep the key file in). | server and embedded | yes |
| `slotfix` | The server's workers stop sharing three reply flags and the held-connection table without a lock. | server | yes |
| `reuseport` | Several `isqlserverd` processes may listen on **the same port** (Linux only); the kernel spreads connections across them. With `readshare` their reads run side by side. | server | yes |
| `keepalive` | A client keeps **one connection per session** instead of opening a new one for every statement; the server's accept loop waits for kept and new connections in one `poll()`. | client and server, negotiated | yes |
| `batchrows` | Result rows travel many to a message instead of one message (about 1.1 KB of header) per row. | client and server, negotiated | yes |
| `fastio` | A message is sent with one `send()` instead of one per 496-byte frame; a waiting receive sleeps in `poll()` instead of 1 ms (15 ms on Windows) at a time; the client reads replies through a buffer. Bytes on the wire are unchanged. | both | yes |
| `catcache` | The server keeps each database's catalogue lookup, done before every statement, and uses it again only while `dblist` and that database's `systable` are unchanged (size and modification time to the nanosecond). | server | yes |
| `leanselect` | A client `SELECT` skips directory work that only served a client dump table this build no longer writes. | client | yes |
| `logincache` | The unlocked field-protection key is kept in memory for `[LoginCacheSeconds]` (default 600, maximum 86400), keyed by user, password and key file, so repeated logins skip Argon2. | server and embedded | **no** |
| `supercheck` | The server **checks a client's "superuser" claim** against `default/kmgaccss` instead of trusting it (see 22.5). | server | **no** |

logincache and supercheck are not in fast because each changes something

other than speed: one keeps key material in memory longer, the other refuses

requests classic accepted. Turn them on by name.

22.2 Examples

1. Nothing set — classic. Every earlier kamranga.cfg keeps working and

means exactly what it meant:

ENTRY

[DatabaseHome] /srv/isqlhome

[ServerPortNo] 21975

[ServerName] localhost

[ServerIpAddress] 127.0.0.1

END

2. A busy website on one machine. Server and pages on the same box; turn on

everything in fast, check superuser claims, and run the server with the poll

agent:

ENTRY

[DatabaseHome] /srv/isqlhome

[ServerPortNo] 21975

[ServerName] localhost

[ServerIpAddress] 127.0.0.1

[ServerMode] fast

[ServerFast] supercheck

END

cd /srv/isqlhome && ISQL_COMM_POLL=1 ISQL_COMM_WORKERS=4 isqlserverd &

and give the web pages (kcpp) the same mode, for example in the web server's

environment:

ISQL_SERVER_MODE=fast

3. Reads in parallel: several servers on one port (Linux). Each server is a

separate process with its own engine; reuseport lets them share the port and

readshare lets their reads run together. Writes still take turns.

[ServerMode] fast

cd /srv/isqlhome

for i in 1 2 3 4; do ISQL_COMM_POLL=1 ISQL_COMM_WORKERS=2 isqlserverd & done

Start them one after another (each waits for its port). Measured with 8 clients

on 4 CPUs, 200 queries each: one server in fast 952 queries/s, four servers

1133 queries/s, against 341 queries/s for one server in classic.

4. Custom: only the wire speed-ups. For a cautious first step — nothing that

changes locking:

[ServerMode] custom

[ServerFast] keepalive,batchrows,fastio

5. Fast, but without one speed-up, plus the login cache for an hour:

[ServerMode] fast

[ServerFast] -unlockstream logincache

[LoginCacheSeconds] 3600

6. One run only, from the environment (the file is left alone):

ISQL_SERVER_MODE=custom ISQL_SERVER_FAST=readshare isqlsh -dbhome /srv/isqlhome -cmd report.sql

7. An embedded program with parallel reads. Several processes opening the

same home directly (CGI pages, batch jobs) read side by side with readshare;

no server involved:

ISQL_SERVER_MODE=custom ISQL_SERVER_FAST=readshare kcpp -icpp report.c

22.3 Checking which mode is running

The server prints one line when it starts listening:

isql: server speed mode: classic

isql: server speed mode: fast: readshare unlockstream loginunlock slotfix reuseport keepalive batchrows fastio catcache leanselect

isql: server speed mode: custom: keepalive batchrows fastio

A name the engine does not know is reported once on stderr and ignored:

isql: warning: unknown speed-up 'keepalve' in [ServerFast] / ISQL_SERVER_FAST - ignored

ISQL_FAST_DIAG=1 additionally reports where each login key came from (derived

before the lock, from the login cache, or under the lock).

22.4 Old and new programs together

keepalive and batchrows are negotiated: a client offers them in each

request, and the server uses only what both sides have. A server or client of an

earlier build never offers anything, so the other side falls back to classic

behaviour for that conversation. Measured, 50 queries per connection:

| Server | Client | Result |
|---|---|---|
| new, `fast` | new, `fast` | correct |
| new, `fast` | new, `classic` | correct |
| new, `classic` | new, `fast` | correct |
| new, `fast` | engine of 2026-10-20 | correct |
| new, `fast` | engine of 2026-10-10 | correct |
| engine of 2026-10-20 | new, `fast` | correct |
| engine of 2026-10-10 | new, `classic` | correct |
| engine of 2026-10-10 | new, `fast` | correct, apart from that server's own hangs |

A client uses its own speed-ups only after the server has said it runs a speed

mode, so against any earlier server a fast client behaves exactly as a

classic one. The 2026-10-10 server hung once in three rounds here, and fails

with its own client of the same date; it is the build whose hangs and lost rows

the 2026-10-20 build fixed.

22.5 Superuser claims (supercheck)

Every request a client sends carries its identity: user, the stored password

hash it received at login, and whether it is a superuser. **A classic server

accepts the superuser mark without checking it** — a request that says

"superuser" skips the password check entirely, whatever user and password it

names. This was verified by replaying a captured request with a made-up user and

password: a classic server returned the rows.

With supercheck the server honours the mark only when default/kmgaccss holds

that user as a superuser with that stored password; otherwise it checks the

request as an ordinary user, which a forged request fails. The answer is kept

while kmgaccss is unchanged, so the check costs a file status call per

statement. It is off unless named, because it is a behaviour change; **turn it

on for any server that other machines can reach.** Note that isqlserverd

listens on all interfaces whatever [ServerIpAddress] says.

22.6 What fast measured

Linux container, 1 CPU, the same database and pages, medians of 10 runs:

| | Direct (embedded) | Server, `classic` | Server, `fast` |
|---|---|---|---|
| connect and disconnect | 171 ms | 389 ms | 172 ms |
| connect + 200 queries of 16 rows | 597 ms | 2409 ms | 471 ms |
| a page of 14 queries | 284 ms | 1045 ms | 254 ms |

Windows, isqlserverd.exe and a kcpp client, 50 queries per connection:

classic 710 ms, fast 340 ms.

22.7 Limits

  • Statements of one process still run one at a time. The server's workers

share engine state that the statement lock protects, so readshare gives

parallel reads between *processes* — embedded programs, several servers

(reuseport) — not between the threads of one server.

  • readshare decides from the statement text. SELECT … INTO and anything

naming a cursor count as writes; so does anything it cannot classify.

  • catcache trusts file times. On a file system whose modification times

are only to the second, a change to dblist in the same second with the same

size could go unseen until the next change; Linux and NTFS keep finer times.

  • logincache keeps a decrypted key in the server's memory for up to

[LoginCacheSeconds]. Use it where repeated logins (a web page per request)

cost more than that exposure.

Back to contents