Using TCL: Sqlite syntax error when creating table in memory temp table

Question

using tcl and sqlite3 I would like to create a temporary table in memory. Trying this:

package require sqlite3
sqlite3 DB  [file normalize X:\memdbtest.db]

DB eval {
    ATTACH DATABASE ':memory:' AS memdb;
    CREATE TEMP TABLE memdb.values (val TEXT);
}

Gives me an error: near "values": syntax error This I gues has to do with that "values" is a reserved keyword in sqlite. Changing the above code to:

    DB eval {
        ATTACH DATABASE ':memory:' AS memdb;
        CREATE TEMP TABLE memdb.things (val TEXT);
    }

gives me the error "temporary table name must be unqualified"

But skipping the memdb. in front of things would put the new table in the regular on disk database.... What is it I am doing wrong here?

Answer 1

Temporary tables go into the temporary database (which is named temp). While that database is stored in a disk file, the file is not actually written to until the cache overflows (because the temporary database is not required to be durable through a crash).

If you want to put the table into some other database, do not use CREATE TEMP TABLE but the normal CREATE TABLE.

CREATE TABLE things([...]);        -- creates the table in the DB "main"
CREATE TABLE memdb.things([...]);  -- creates the table in the specified DB
CREATE TEMP TABLE things([...]);   -- creates the table in the DB "temp"
CREATE TABLE temp.things([...]);   -- creates the table in the DB "temp"