Binding in multiple statements within a query

[MigFerreira]

Getting a binding error with following deletes within transaction I had this working in the past with another sqlite3 package. Unsure what is failing.

IF I run each of the statements within the transaction individually it works fine.

return DB.sql`
                BEGIN TRANSACTION;
                    DELETE FROM BudgetTransactions WHERE budget_id IN (SELECT id FROM Budget WHERE id=${id} OR p_id=${id});
                    DELETE FROM BudgetCategories WHERE budget_id IN (SELECT id FROM Budget WHERE id=${id} OR p_id=${id});
                    DELETE FROM Budget WHERE id=${id} OR p_id=${id};
                COMMIT;
 `;

Error:

Uncaught (in promise) Error: Bind index 3 is out of range.

[DallasHoff]

From a cursory glance, I think the issue is this limitation of sqlite-wasm: "[Bindings] are only applied to the first non-empty statement in the SQL which has any bindable parameters. (Empty statements are skipped entirely.)". Since your third statement is the second statement with bindings, sqlite-wasm errors on that third statement ("index 3").

Does it work if you remove the bindings from the other 2 statements?

[MigFerreira]

Yes, I tried in multiple orders. It seems to only allow bindings in one statement.

[DallasHoff]

Ok, that tracks. I'm not sure why sqlite-wasm has that limitation, but it means you should separate each statement. Your example is a perfect use-case for SQLocal's transaction method.

[DallasHoff]

I've added a paragraph noting this to the docs page for sql:

---

Multiple statements can be passed in the query, but note that the results returned will only include results from the first value-returning statement. Also, only one statement in the query can have parameter bindings. Because of these restrictions, it is recommended to pass only one SQL statement per call to sql.

// Warning: only returns the row with id 1.
const result = await sql`
    SELECT * FROM foo WHERE id = 1; 
    SELECT * FROM foo WHERE id = 2;
`;

// Recommended: one statement per query
const result1 = await sql`SELECT * FROM foo WHERE id = 1;`;
const result2 = await sql`SELECT * FROM foo WHERE id = 2;`;