Conn.PrepareContext doesn't support multiple statements (separated by semi-colon)

[yurivish]

Hello, I noticed that sqlite3-go does not allow multiple statements separated by a semi-colon when directly preparing statements against a single Conn:

package main

import (
	"context"
	"database/sql"
	"fmt"

	// _ "modernc.org/sqlite"
	_ "github.com/mattn/go-sqlite3"
)

// You can only run a single statement inside a stmt on a conn with go-sqlite3.
func main() {
	// note: use "sqlite" for testing modernc.org/sqlite and "sqlite3" for go-sqlite3
	db, _ := sql.Open("sqlite3", ":memory:")
	conn, _ := db.Conn(context.Background())
	// One statement executes and the second is ignored; prints "4"
	stmt, _ := conn.PrepareContext(context.Background(), `select 2 + 2; select 4 + 4;`)
	rows, _ := stmt.Query()
	for rows.Next() {
		var result string
		rows.Scan(&result)
		fmt.Println(result)
	}

	// Both statments execute and the result of the last one is returned; prints "8"
	rows, _ = conn.QueryContext(context.Background(), `select 2 + 2; select 4 + 4;`)
	for rows.Next() {
		var result string
		rows.Scan(&result)
		fmt.Println(result)
	}
}

When stmt.Query runs, only the first statement executes, which is inconsistent with the behavior of conn.QueryContext and conn.Exec. I found an old issue about this behavior with Conn.Exec which was fixed in 2014.

Is this something that could be changed/fixed, or is it an intentional design choice? For a comparison, running this code against modernc.org/sqlite returns the same results (8 and 8) in both cases.

Thank you!

[rittneje]

To take a step back, can you expand upon the use case for this?

SQLite itself doesn't really allow for multiple statements at once. So this definitely causes some confusion/headache whenever index-based parameters enter the mix. https://github.com/mattn/go-sqlite3/issues/584#issuecomment-451705090

In this specific situation, you could just do SELECT 2 + 2 UNION ALL SELECT 4 + 4. But to support semicolon-delimited statements in general would require separate result sets, as each query could be selecting a different number of columns. (Only returning the results from the last statement kind of seems like a bug to me.)

[yurivish]

Hi @rittneje, sure.

I'm working on a small learning project and came across this behavior during the initialization step for my database. Right now each run of the program creates a new SQLite database and populates it by running a set of statements to create tables and indices. The statements live in a file, and are executed as a single block when the program begins.

This worked until I wrote a small wrapper around sql.Conn in order to maintain a reader pool of size n and writer pool of size 1, rather than delegating connection management to the sql package. This specific set of queries doesn't need to be run multiple times (unless I want a dynamic "reset" option to drop and re-create all tables), but that's the story of how I found this behavior. Running the initialization statements was only executing the first statement and ignoring the rest.

A more general use case I can imagine is wanting to make inserts to a few tables in one go, or creating a single stmt for a transaction, which consists of multiple SQL statements. These could be run as multiple individual Stmts but the code would express the intent more cleanly with a single block (e.g. you would not need to check for errors separately for every single intermediate query). For my use cases, returning the results from the last statement seems like a logical choice and would be consistent with conn.QueryContext, though I can see the argument for doing otherwise.

[FlyingOnion]

// Both statments execute and the result of the last one is returned; prints "8"

@yurivish I don't think both are executed.

If I run select 1; select 2, 3; then both drivers return 2, 3, as expected.

But if I tried insert into t values (...); select * from t;

There are no new records in the table.

Switch to "modernc.org/sqlite" and both statements execute normally.

[yurivish]

@FlyingOnion It sounds like you're right, my mistake. Thanks for checking and clarifying.

I would consider using the modernc sqlite package, but I think there are some performance differences and despite the impressive level of testing I get the sense that this package is still the most mature and stable of the current options for SQLite in Go.

(thanks for the response!)

[FlyingOnion]

@FlyingOnion It sounds like you're right, my mistake. Thanks for checking and clarifying.

I would consider using the modernc sqlite package, but I think there are some performance differences and despite the impressive level of testing I get the sense that this package is still the most mature and stable of the current options for SQLite in Go.

(thanks for the response!)

Hi, @yurivish

Actually I don't think it's a big problem.

For an exec (insert, update) stmt with its result information (last id, rowcount, etc.) returned, you can use returning (no need to split into 2 stmts like mysql select LAST_INSERT_ID, ROWCOUNT).

For multiple querys. I don't think the first n-1 results matter.

For multiple execs in a stmt string, maybe I will be fired if this code is merged 😅.

I found this bug just in my experiment, but it is surely better if mattn could fix 😀.

[abishekmuthian]

Just noting that multiple statements via SQLB().Prepare() doesn't work either, Only the first statement gets executed. Should it be a separate issue?

[FlyingOnion]

Just noting that multiple statements via SQLB().Prepare() doesn't work either, Only the first statement gets executed. Should it be a separate issue?

Better not do this in production I think.