siara-cc/esp32-idf-sqlite3

INSERT multiple rows in a statement results on last item only

alessandroraffa opened this issue · 5 comments

alessandroraffa commented

Platform: esp-idf 3.3-beta1-506-gebdcbe8c6-dirty

Using either one of the following statements:

INSERT INTO tableName (value) 
VALUES (12.00),(24.00),(36.00),(48.00),(60.00),(72.00),(84.00),(96.00);
INSERT INTO tableName (value) 
SELECT (12.00) 
UNION ALL SELECT (24.00) 
UNION ALL SELECT (36.00)
UNION ALL SELECT (48.00)
UNION ALL SELECT (60.00) 
UNION ALL SELECT (72.00) 
UNION ALL SELECT (84.00) 
UNION ALL SELECT (96.00);

Results in only the last row 96.00 actually in the database.

Is there any known issue or am I doing something wrong?

Thanks in advance.

julliermedias commented

I have been having exactly the same problem. Any updates?

siara-cc commented

I tried this on my side with the same result. I don't have an answer at this time. Why not just run multiple insert statements or use a Prepared statement?

renatomotorline commented

@alessandroraffa @julliermedias @siara-cc
Has anyone found a solution to this problem?

renatomotorline commented

This way works:

INSERT INTO table VALUES (1, 1);
INSERT INTO table VALUES (2, 2);
...
COMMIT;

https://stackoverflow.com/questions/1609637/how-to-insert-multiple-rows-in-sqlite

siara-cc commented

@renatomotorline So far no. You could make it faster by using prepared statements like INSERT INTO table VALUES (?, ?). This also prevents Out of memory issues because of making lot of strings. I think I made an example sketch in the repo if you need a sample.