Error "no such table" when creating temp tables about go-sqlite3 HOT 3 OPEN

As discussed in #933, there is currently a bug in this library where passing multiple statements to Query does not work - only the last will actually be executed. I suggest splitting these statements and using db.Begin or db.Conn.

Also, as a reminder, you should always check rows.Err() after iterating.

	c, err := db.Conn(ctx)
	if err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer c.Close()

	if _, err := c.ExecContext(ctx, "DROP TABLE IF EXISTS temp.deleted_watchtimes"); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}

	if _, err := c.ExecContext(ctx, "CREATE TEMP TABLE deleted_watchtimes (row_id INTEGER)"); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer c.ExecContext(ctx, "DROP TABLE temp.deleted_watchtimes")

	if _, err := c.ExecContext(ctx, `
		WITH cte AS (SELECT value FROM JSON_EACH(:names))
		INSERT
		INTO temp.deleted_watchtimes
		SELECT *
		FROM cte`,
		sql.Named("names", `["a","b"]`),
	); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}

	rows, err := c.QueryContext(ctx, "SELECT * FROM temp.deleted_watchtimes")
	if err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var res string
		err := rows.Scan(&res)
		if err != nil {
			fmt.Printf("error: %v\n", err)
			return
		}
		fmt.Println(res)
	}

	if err := rows.Err(); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}

from go-sqlite3.

Thank you for the good response. Sadly I'm currently using https://github.com/qustavo/dotsql in place of stored procedures since I don't want sql statements scattered throughout my code. I'll keep an eye on this issue so I know when it's resolves so I can add it back in because I would rather use this package than the other, since I feel this one is faster and more stable.

Also what is the reason for the rows.Err()/row.Err() check? The docs say If this error is not nil, this error will also be returned from Scan, so I would assume that my error checking on scans would catch the same error.

from go-sqlite3.

rows.Next will return false if it encounters an error, at which point rows.Scan is not called.

Next prepares the next result row for reading with the Scan method. It returns true on success, or false if there is no next result row or an error happened while preparing it. Err should be consulted to distinguish between the two cases.

from go-sqlite3.