Tables Created in SQLiteStudio Not Visible in Other Applications

Database Visibility Issues Across SQLite Management Tools and Applications

Core Symptoms and Environmental Context

The primary issue involves tables created in SQLiteStudio failing to appear in other applications such as DB Browser for SQLite, Delphi programs, or CLI tools. Key symptoms include:

1. "No such table" errors in applications despite confirmed table creation in SQLiteStudio.
2. Inconsistent visibility of database objects (tables, schemas) across tools.
3. Commit-related ambiguities, where explicit "Commit Changes" actions in SQLiteStudio appear necessary to persist modifications.
4. Platform migration issues, such as moving databases from Windows 7 to Windows 10, leading to permission conflicts or path resolution failures.
5. External exceptions (e.g., "Unable to open database file") in Delphi programs, often accompanied by cryptic hex codes.

The problem is exacerbated by:

• Use of multiple SQLite management tools (SQLiteStudio, DB Browser, CLI).
• Delphi applications relying on SQLite3.dll bindings.
• Windows file system permissions and UAC virtualization.
• Ambiguities in transaction handling and database file locking.

Root Causes of Visibility and Access Failures

1. File System Path Mismatches and Virtualization

SQLite databases are referenced via file paths. When tools or applications resolve paths differently, they may access entirely different files or folders. Common causes include:

• Relative vs. Absolute Paths: Applications may resolve paths relative to their working directory, which varies between tools. For example, SQLiteStudio might save a database to C:\Project\data.db, while a Delphi program looks for data.db in C:\Program Files\App\.
• UAC Virtualization (Windows): Older applications without execution manifests may trigger UAC file virtualization. Attempts to write to protected directories (e.g., Program Files) redirect to user-specific virtual stores (e.g., AppData\Local\VirtualStore). This creates "phantom" database files that are invisible to other applications.
• Case Sensitivity and Typos: While Windows paths are case-insensitive, tools like Delphi or CLI may fail to resolve paths with inconsistent casing or typos (e.g., MyData.db vs. mydata.db).

2. Transaction Handling and Implicit Commits

SQLiteStudio and other GUI tools often automate transaction management, leading to confusion about when changes are persisted:

• Uncommitted Transactions: SQLiteStudio may leave transactions open (e.g., during schema edits), requiring explicit "Commit" actions. Until committed, changes exist only in the tool’s session memory.
• Auto-Commit Discrepancies: Applications like Delphi might disable auto-commit, assuming manual control. If a transaction is not finalized, other tools will not see the changes.
• Journaling Modes: Write-Ahead Logging (WAL) or DELETE journaling modes affect how and when changes are flushed to disk. Mismatched journaling configurations between tools can cause visibility delays.

3. File Permissions and Read-Only States

Windows file system permissions and attributes often interfere with database accessibility:

• Read-Only Flags: Folders or files marked as read-only prevent SQLite from creating/writing to journals or WAL files. This breaks transaction atomicity, causing "attempt to write a readonly database" errors.
• Anti-Virus Interference: Real-time scanners may lock database files temporarily, blocking access from other processes.
• Network/Removable Media: Databases on USB drives or network shares may inherit restrictive permissions, especially after OS upgrades.

4. SQLite3.dll Version and Bitness Conflicts

Delphi applications linking to SQLite3.dll must match the host system’s architecture:

• 32-bit vs. 64-bit Mismatches: A 32-bit Delphi app using a 64-bit SQLite3.dll (or vice versa) causes silent failures or external exceptions.
• DLL Search Order: Windows prioritizes DLLs in the application directory, system folders, or PATH. Mismatched DLL versions (e.g., legacy 3.20 vs. modern 3.45) introduce compatibility issues.

5. Schema Corruption or Tool-Specific Artifacts

GUI tools sometimes create auxiliary files or modify schemas in ways that confuse other applications:

• Tool-Specific Metadata: SQLiteStudio may store UI preferences (e.g., table layouts) in the database, which other tools ignore.
• Schema Validation Failures: Corrupted sqlite_master tables or invalid indices can cause tools to "see" incomplete schemas.

Comprehensive Troubleshooting and Resolution Workflow

1. Validate Absolute File Paths and UAC Virtualization

Step 1: Use Absolute Paths in All Tools

Ensure all applications reference the database using absolute paths:

// Delphi Example: Explicit Absolute Path
SQLConnection1.Params.Values['Database'] = 'C:\Projects\Data\app.db';  

In SQLiteStudio:

1. Navigate to Database > Add Database.
2. Set Database Type to SQLite 3.
3. Under File, click Browse and select C:\Projects\Data\app.db (avoid typing paths manually).

Step 2: Detect UAC Virtualization

1. Open Process Monitor (Sysinternals).
2. Set a filter for Path contains app.db.
3. Run the Delphi application. Observe file access attempts.
4. If accesses redirect to VirtualStore, disable UAC or relocate the database to a user-writable directory (e.g., Documents).

Step 3: Verify Cross-Tool Consistency

1. Create a table in SQLiteStudio:

   CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);  
   

2. In DB Browser for SQLite:
   • Open C:\Projects\Data\app.db.
   • Execute SELECT * FROM sqlite_master WHERE type='table';.
3. If the table is missing, proceed to transaction checks.

2. Enforce Explicit Transactions and Commits

Step 1: Disable Auto-Commit in SQLiteStudio

1. Navigate to Tools > Preferences > SQL Execution.
2. Uncheck Auto-commit DDL/DML statements.
3. After executing schema changes, click Commit (✔️ icon) or press Ctrl+Enter.

Step 2: Inspect Transaction State via CLI

1. Open the database in the SQLite CLI:

   sqlite3 C:\Projects\Data\app.db  
   

2. Check for open transactions:

   SELECT * FROM sqlite_master WHERE type='table';  
   PRAGMA journal_mode;  -- Ensure WAL/DELETE is consistent  
   

3. Manually commit if needed:

   COMMIT;  
   

Step 3: Align Journaling Modes

1. In SQLiteStudio, execute:

   PRAGMA journal_mode=WAL;  
   

2. In Delphi, set the same pragma after connecting:

   SQLQuery1.SQL.Text := 'PRAGMA journal_mode=WAL';  
   SQLQuery1.ExecSQL;  
   

3. Resolve File Permissions and Anti-Virus Conflicts

Step 1: Audit File and Folder Permissions

1. Right-click C:\Projects\Data, select Properties > Security.
2. Ensure the user account has Full Control.
3. Uncheck Read-Only at the folder level (applies to children).

Step 2: Disable Anti-Virus Temporarily

1. Disable real-time scanning (varies by vendor).
2. Test database accessibility in Delphi.

Step 3: Use Process Explorer for File Locks

1. Launch Process Explorer (Sysinternals).
2. Search for app.db or app.db-wal.
3. Terminate processes holding locks (e.g., antivirus, backup tools).

4. Align SQLite3.dll Bitness and Version

Step 1: Validate Delphi and DLL Architectures

1. In Delphi, navigate to Project > Options > Building > Target Platform.
   • Ensure 32-bit or 64-bit matches the SQLite3.dll.
2. Check DLL bitness using Dependency Walker or:

   dumpbin /headers sqlite3.dll | findstr "machine"  
   

   • x86 = 32-bit, x64 = 64-bit.

Step 2: Standardize DLL Deployment

1. Place the correct sqlite3.dll in:
   • The Delphi executable directory.
   • C:\Windows\System32 (64-bit) or C:\Windows\SysWOW64 (32-bit).
2. Update the Delphi project’s LibraryName property:

   SQLConnection1.LibraryName := 'C:\Projects\Libs\sqlite3.dll';  
   

5. Repair Schema Corruption and Validate Integrity

Step 1: Dump and Rebuild the Database

1. Export the schema and data via CLI:

   sqlite3 app.db .dump > backup.sql  
   

2. Create a new database:

   sqlite3 new.db < backup.sql  
   

Step 2: Check for Orphaned Journals

1. Delete app.db-wal, app.db-shm, or app.db-journal files.
2. Reopen the database to regenerate clean journals.

Step 3: Validate SQLite Master Table

SELECT name, sql FROM sqlite_master WHERE type='table';  
-- Look for malformed entries or missing tables  

By methodically addressing path resolution, transaction states, permissions, DLL conflicts, and schema integrity, developers can resolve visibility issues across SQLite tools and applications. Always validate configurations using the SQLite CLI, as it bypasses GUI-specific abstractions and provides direct insight into database states.