Skip to content

What's the correct way to read several SQL statements? #18

@MonadMania

Description

@MonadMania

I have a database dump in "some-file.sql". It's about 1000 lines of proper SQL written for SQLite. Here's how I'm processing this file:

;;; Execute each SQL statement in sequence. (defun sql-statements (db sql-stream) "Execute SQL statements from the stream SQL-STREAM." (do* ((next-char (read-char sql-stream nil :eof) (read-char sql-stream nil :eof)) (next-statement (make-array 1000 :element-type 'character :initial-element (code-char 0) :fill-pointer 0 :adjustable t))) ((eql :eof next-char) next-statement) (if (eql #\; next-char) (progn (sqlite:execute-non-query db next-statement) (print next-statement) (setf (fill-pointer next-statement) 0)) (vector-push-extend next-char next-statement))))

Here's how I'm invoking the above function:
;;; The SQL file is a dump, so it establishes a transaction. I don't do so here. (sqlite:with-open-database (db *db-path*) (with-open-file (sql-stream *sql-path*) (sql-statements db sql-stream)))

This works fine until statement 17, where I get an error in SQLITE.CACHE:POP-FROM-CACHE:

The value
NIL
is not of type
VECTOR
[Condition of type TYPE-ERROR]

Restarts:
0: [RETRY] Retry SLIME REPL evaluation request.
1: [*ABORT] Return to SLIME's top level.
2: [ABORT] abort thread (#<THREAD "repl-thread" RUNNING {10040D8473}>)

Backtrace:
0: (SQLITE.CACHE::POP-FROM-CACHE #<SQLITE.CACHE:MRU-CACHE {1004ADE133}>)
Locals:
CACHE = #<SQLITE.CACHE:MRU-CACHE {1004ADE133}>
1: (SQLITE.CACHE:PUT-TO-CACHE #<SQLITE.CACHE:MRU-CACHE {1004ADE133}> "
..)
Locals:
CACHE = #<SQLITE.CACHE:MRU-CACHE {1004ADE133}>
ID = "\r\nINSERT INTO "episodes" VALUES(14, 2, 'The Statue')"
OBJECT = #<SQLITE:SQLITE-STATEMENT {1004AEB313}>
2: ((FLET "CLEANUP-FUN-4" :IN SQLITE:EXECUTE-NON-QUERY)) [cleanup]
[No Locals]
3: (SQLITE:EXECUTE-NON-QUERY #<SQLITE:SQLITE-HANDLE {1004ADDFC3}> "
..)
4: (SQLITE:EXECUTE-NON-QUERY #<SQLITE:SQLITE-HANDLE {1004ADDFC3}> "
..) [more]
5: (SQL-STATEMENTS #<SQLITE:SQLITE-HANDLE {1004ADDFC3}> #<SB-SYS:FD-STREAM for "file f:\portacle\projects\sqlite\data\foods.sql" {1004ADF6C3}>)
6: ((LAMBDA ()))
7: (SB-INT:SIMPLE-EVAL-IN-LEXENV (SQLITE:WITH-OPEN-DATABASE (DB DB-PATH) (WITH-OPEN-FILE (SQL-STREAM SQL-PATH) (SQL-STATEMENTS DB SQL-STREAM))) #)
8: (EVAL (SQLITE:WITH-OPEN-DATABASE (DB DB-PATH) (WITH-OPEN-FILE (SQL-STREAM SQL-PATH) (SQL-STATEMENTS DB SQL-STREAM))))

Since the cache has size 16 and the error occurs on statement 17, I assume that I'm bumping into the cache size. What's the correct way to execute a batch of SQL statements stored in a text file? I'm running SBCL on Windows 10.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions