esp32_arduino_sqlite3_lib
esp32_arduino_sqlite3_lib copied to clipboard
Published
20 hours ago •
siara-cc
siara-cc
Sqlite3 Arduino library for ESP32
Results
53
esp32_arduino_sqlite3_lib issues
Sort by
recently updated
recently updated
newest added
SD Card mount failure
[{"_id":"6359591d3056137e265e2960","body":"Hi, no what is the error you are facing?","issue_id":1660803919273,"origin_id":1210209707,"user_origin_id":12986780,"create_time":1660112225,"update_time":1660112225,"id":1666799901228,"updated_at":"2022-10-26T15:58:21.228000Z","created_at":"2022-10-26T15:58:21.228000Z"},{"_id":"6359591d3056137e265e2961","body":"Still learning C++ and Arduino. I made a bunch of rookie errors. All cleared up now. Thanks for the great library.","issue_id":1660803919273,"origin_id":1231064451,"user_origin_id":83699317,"create_time":1661826036,"update_time":1661826036,"id":1666799901232,"updated_at":"2022-10-26T15:58:21.232000Z","created_at":"2022-10-26T15:58:21.232000Z"}]
Hiya, I have been trying to get your SQLite console demo code running most of today. I have an SD Card reader that I can mount and use without your...
SQLite Database Disk Image is Malformed
[{"_id":"63594d92ea01ec786e7ba2b4","body":"This is an issue with the SPIFFS file system. I started out using this library on SPIFFS and was getting Disk Image Malformed errors all over the place. I switched to LITTLEFS, which is a drop in replacement, and the errors just went away.\r\n\r\nhttps:\/\/github.com\/lorol\/LITTLEFS\r\n","issue_id":1660803919278,"origin_id":916045739,"user_origin_id":59926104,"create_time":1631190386,"update_time":1631190386,"id":1666796946711,"updated_at":"2022-10-26T15:09:06.710000Z","created_at":"2022-10-26T15:09:06.710000Z"},{"_id":"63594d92ea01ec786e7ba2b6","body":"If it helps, my db kept getting Malformed error when deleting data, and to fix i had to go down back to the esp 1.0.6 board lib. The 2.4 lib seemed to have caused it on SD card management side.","issue_id":1660803919278,"origin_id":1208130069,"user_origin_id":110826599,"create_time":1659965244,"update_time":1659965244,"id":1666796946716,"updated_at":"2022-10-26T15:09:06.715000Z","created_at":"2022-10-26T15:09:06.715000Z"}]
i am using https://github.com/siara-cc/esp32_arduino_sqlite3_lib for store my data in SPIFFS of ESP32 but after some insertion of data i got error of SQLite Database Disk Image is Malformed .........................please give...
VACUUM result disk I/O error
[{"_id":"635951f9ea01ec786e7ba637","body":"Did you manage to resolve this? I'm getting the same issue.","issue_id":1660803919283,"origin_id":1056052065,"user_origin_id":66259201,"create_time":1646185903,"update_time":1646185903,"id":1666798073345,"updated_at":"2022-10-26T15:27:53.345000Z","created_at":"2022-10-26T15:27:53.345000Z"},{"_id":"635951f9ea01ec786e7ba638","body":"Hi,\r\n\r\nno. still same issue.\r\ni am waiting for response from the author.","issue_id":1660803919283,"origin_id":1056413377,"user_origin_id":81647459,"create_time":1646204675,"update_time":1646204675,"id":1666798073348,"updated_at":"2022-10-26T15:27:53.347000Z","created_at":"2022-10-26T15:27:53.347000Z"},{"_id":"635951f9ea01ec786e7ba639","body":"Hi, not sure what is causing this, but please check this out: https:\/\/github.com\/siara-cc\/esp32_arduino_sqlite3_lib\/issues\/50#issuecomment-916045739","issue_id":1660803919283,"origin_id":1056595780,"user_origin_id":12986780,"create_time":1646210929,"update_time":1646210929,"id":1666798073351,"updated_at":"2022-10-26T15:27:53.351000Z","created_at":"2022-10-26T15:27:53.351000Z"},{"_id":"635951f9ea01ec786e7ba63a","body":"Hi,\r\n\r\nI am experiencing the same issue but through the SPI bound SD card. All other functions are fine, only VACUUM causing this issue.","issue_id":1660803919283,"origin_id":1208053574,"user_origin_id":110826599,"create_time":1659961117,"update_time":1659961117,"id":1666798073354,"updated_at":"2022-10-26T15:27:53.354000Z","created_at":"2022-10-26T15:27:53.354000Z"},{"_id":"663a5cfafa3686d6a1161929","body":"I have looked into this and its solvable:\r\n\r\n1.) a temporary file is opened. this is not implemented currently -> filename is 0 and the doc says you have to come up with a filename yourself. -> first thing that needs to be fixed in ESP32Open like this: \r\n```\r\n\r\nif( zName==0 ) {\r\n\t\t\/\/return SQLITE_IOERR;\r\n\t\tzName = \"\/spiffs\/.tmp\";\r\n}\r\n\r\n```\r\n\r\n2.) next the temp file needs to be initialized with some data as when writing out the seek somehow fails as it trys to seek to a position that is outside of the files size. \r\ni have not solved this correctly but this helps somehow: \r\n\r\n```\r\n\r\n\t\tFILE* fp = fopen(zName, \"w+\");\r\n\t\tuint8_t buff[128] = { 0 };\r\n\t\tmemset(buff, 0, sizeof(buff));\r\n\t\tfor(int i = 0; i < 4096 * 2 \/ 128; i++)\r\n\t\t\tfwrite(buff, 1, sizeof(buff), fp);\r\n\t\tfclose(fp);\r\n\t\t\r\n```\r\n\t\t\r\n\r\nlastly, truncate is not implemented and thus the operation sometimes fails. ESP32 for an unknown reason refuses to implement truncate in there filesystem. you can work around this by (moving the file -> creating a new file with the name of the original file with the correct truncated size -> copy data from the original file into new file)\r\n\r\n___\r\n\r\nits not a step by step fix but helping hints for somebody how wants to fix this. my code has at this point deviated to far so that i can publish a pullrequest. \r\n\r\nalso some other problems might also be fixed by implementing the temporary file and trucate like the issue with dropping tables or auto increment primary key","issue_id":1660803919283,"origin_id":1542178334,"user_origin_id":11009455,"create_time":1683723889,"update_time":1683723929,"id":1715100922287,"updated_at":"2024-05-07T16:55:22.287000Z","created_at":"2024-05-07T16:55:22.287000Z"},{"_id":"663a5cfafa3686d6a116192a","body":"@savejeff Thanks for sharing this!! I will check.","issue_id":1660803919283,"origin_id":1542608115,"user_origin_id":12986780,"create_time":1683742014,"update_time":1683742014,"id":1715100922291,"updated_at":"2024-05-07T16:55:22.291000Z","created_at":"2024-05-07T16:55:22.291000Z"},{"_id":"663a5cfafa3686d6a116192b","body":"i found out the problem with seek. sqlite expects that when a seek is above the file size and a write is started that the file is extended to the seek position with zeros. so in direct write when the start offset is above the file size, the file must be extended with zeros until file.size == iOfst like this:\r\n\r\n```\r\nESP32DirectWrite(\r\n[....]\r\n\r\n\tif(iOfst > p->fp->size()) {\r\n\t\tLogXD(\">>> WRITE SEEK OUTSIDE OF FILE\");\r\n\t\t\r\n\t\t\/\/ write zeros to file until target offset is reached\r\n\t\tuint8_t buff[128] = { 0 };\r\n\t\tmemset(buff, 0, sizeof(buff));\r\n\t\twhile(p->fp->size() < iOfst)\r\n\t\t\tp->fp->write(buff, MIN(128, iOfst - p->fp->size()));\r\n\r\n\t}\r\n[....]\r\n```\r\n\r\nHere is what chatgpt has to say about it:\r\n```\r\n\r\nWhen you use fseek() to seek to a position higher than the current file size and subsequently perform a write operation, the behavior depends on the mode in which the file was opened:\r\n\r\nIf the file was opened in a mode that allows writing (such as \"w\", \"a\", or \"r+\"), the write operation will extend the file to the specified position, filling any gaps with zero bytes if necessary. In other words, the file size will be increased to accommodate the write operation, and any intermediate positions will be filled with zeros.\r\n\r\nFor example, if the file size is 100 bytes, and you use fseek(file, 1000, SEEK_SET) to move to position 1000, and then perform a write operation, the file size will increase to 1000 bytes, with the bytes between 100 and 999 being filled with zeros.\r\n\r\nIf the file was opened in a read-only mode (such as \"r\"), attempting to perform a write operation after seeking to a position beyond the current file size will result in undefined behavior. The write operation may fail, produce an error, or lead to unexpected consequences. In general, it is not valid to write beyond the end of a file that was opened in read-only mode.\r\n\r\nIt's important to ensure that the file is opened in a mode that allows writing if you intend to extend the file size and perform write operations beyond the current end of the file. Additionally, always check for errors returned by file operations to handle any potential issues that may arise during file manipulation.\r\n\r\n```\r\n\r\n\r\nIm getting Operation successful with Vacum now","issue_id":1660803919283,"origin_id":1543640550,"user_origin_id":11009455,"create_time":1683796707,"update_time":1683796758,"id":1715100922296,"updated_at":"2024-05-07T16:55:22.296000Z","created_at":"2024-05-07T16:55:22.296000Z"},{"_id":"663a5cfafa3686d6a116192c","body":"i think i have now fixed most of the problems that caused the IO Disk errors others reported. @siara-cc i can send you the my implementation. its based on arduinos FS classes and not on the basic fwrite, etc functions but it demonstrates the needed general behaviour","issue_id":1660803919283,"origin_id":1543677229,"user_origin_id":11009455,"create_time":1683798117,"update_time":1683798117,"id":1715100922299,"updated_at":"2024-05-07T16:55:22.299000Z","created_at":"2024-05-07T16:55:22.299000Z"},{"_id":"663a5cfafa3686d6a116192d","body":"Hi, thank you for the detailed explanation. If a PR is not possible, I suggest attaching the zip file here so I can check and modify the library.","issue_id":1660803919283,"origin_id":1546150586,"user_origin_id":12986780,"create_time":1683917728,"update_time":1683917728,"id":1715100922302,"updated_at":"2024-05-07T16:55:22.302000Z","created_at":"2024-05-07T16:55:22.302000Z"},{"_id":"663a5cfafa3686d6a116192e","body":"@savejeff ive posted an issue with a fix for esp32open\r\nhttps:\/\/github.com\/siara-cc\/esp32_arduino_sqlite3_lib\/issues\/81","issue_id":1660803919283,"origin_id":1847036289,"user_origin_id":98483683,"create_time":1702035729,"update_time":1702035729,"id":1715100922306,"updated_at":"2024-05-07T16:55:22.306000Z","created_at":"2024-05-07T16:55:22.306000Z"}]
Hi, I try to use "VACUUM" but i get this error disk I/O error. Other sqlite command work well. I use ESP32 and SPIFFS. i use the command sqlcommand="VACUUM"; "db_exec(db1,...
Can i use it with IDF instead of arduino?
[{"_id":"635957e5cecf4e081a206e21","body":"Hi please see https:\/\/github.com\/siara-cc\/esp32-idf-sqlite3","issue_id":1660803919291,"origin_id":1200999344,"user_origin_id":12986780,"create_time":1659348755,"update_time":1659348755,"id":1666799589106,"updated_at":"2022-10-26T15:53:09.105000Z","created_at":"2022-10-26T15:53:09.105000Z"},{"_id":"635957e5cecf4e081a206e22","body":"> Hi please see https:\/\/github.com\/siara-cc\/esp32-idf-sqlite3\r\n\r\nThank you.","issue_id":1660803919291,"origin_id":1222825342,"user_origin_id":26469677,"create_time":1661196871,"update_time":1661196871,"id":1666799589112,"updated_at":"2022-10-26T15:53:09.112000Z","created_at":"2022-10-26T15:53:09.112000Z"}]
I know this has been asked before. But it's been some time. The installation steps talk about finding the esp folder inside the arduino folder.
I saw a promise to have an exmple using sdFat lik: https://github.com/siara-cc/esp_arduino_sqlite3_lib/blob/master/examples/sqlite3_console_sd/sqlite3_console_sd.ino On ESP32, it would greatly improve the speed with large data. Many thanks
Can anyone help me to set database timing using ntp server. Every time esp32 reboots sets to 1970-01-01 00:00:01
LittleFS support ?
[{"_id":"635952503056137e265e243d","body":"I've had success running this on LittleFS. \r\n\r\nThere is one main behavior that is different than in SPIFFS that I've found: In SPIFFS, if you try to open a database file that doesn't exist, sqlite will create it for you. I find this is not the case with LittleFS. The db and journal files need to be created before trying to open it.\r\n\r\nI modified the example code below to run on LittleFS\r\n\r\n```\r\n#include <stdio.h>\r\n#include <stdlib.h>\r\n#include <sqlite3.h>\r\n#include <SPI.h>\r\n#include <FS.h>\r\n#include <LITTLEFS.h>\r\n\r\n#define FORMAT_LITTLEFS_IF_FAILED true\r\n\r\nconst char* data = \"Callback function called\";\r\nstatic int callback(void *data, int argc, char **argv, char **azColName) {\r\n int i;\r\n Serial.printf(\"%s: \", (const char*)data);\r\n for (i = 0; i<argc; i++){\r\n Serial.printf(\"%s = %s\\n\", azColName[i], argv[i] ? argv[i] : \"NULL\");\r\n }\r\n Serial.printf(\"\\n\");\r\n return 0;\r\n}\r\n\r\nint db_open(const char *filename, sqlite3 **db) {\r\n int rc = sqlite3_open(filename, db);\r\n if (rc) {\r\n Serial.printf(\"Can't open database: %s\\n\", sqlite3_errmsg(*db));\r\n return rc;\r\n } else {\r\n Serial.printf(\"Opened database successfully\\n\");\r\n }\r\n return rc;\r\n}\r\n\r\nchar *zErrMsg = 0;\r\nint db_exec(sqlite3 *db, const char *sql) {\r\n Serial.println(sql);\r\n long start = micros();\r\n int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);\r\n if (rc != SQLITE_OK) {\r\n Serial.printf(\"SQL error: %s\\n\", zErrMsg);\r\n sqlite3_free(zErrMsg);\r\n } else {\r\n Serial.printf(\"Operation done successfully\\n\");\r\n }\r\n Serial.print(F(\"Time taken:\"));\r\n Serial.println(micros()-start);\r\n return rc;\r\n}\r\n\r\nvoid setup() {\r\n\r\n Serial.begin(115200);\r\n sqlite3 *db1;\r\n int rc;\r\n\r\n if (!LITTLEFS.begin(FORMAT_LITTLEFS_IF_FAILED)) {\r\n Serial.println(\"Failed to mount file system\");\r\n return;\r\n }\r\n\r\n \/\/ list LITTLEFS contents\r\n File root = LITTLEFS.open(\"\/\");\r\n if (!root) {\r\n Serial.println(\"- failed to open directory\");\r\n return;\r\n }\r\n if (!root.isDirectory()) {\r\n Serial.println(\" - not a directory\");\r\n return;\r\n }\r\n File file = root.openNextFile();\r\n while (file) {\r\n if (file.isDirectory()) {\r\n Serial.print(\" DIR : \");\r\n Serial.println(file.name());\r\n } else {\r\n Serial.print(\" FILE: \");\r\n Serial.print(file.name());\r\n Serial.print(\"\\tSIZE: \");\r\n Serial.println(file.size());\r\n }\r\n file = root.openNextFile();\r\n }\r\n\r\n \/\/ remove existing file so we start clean for this test.\r\n LITTLEFS.remove(\"\/test1.db\");\r\n LITTLEFS.remove(\"\/test1.db-journal\");\r\n\r\n \/\/ Create the db file before trying to open it.\r\n if (!LITTLEFS.exists(\"\/test1.db\")){\r\n File file = LITTLEFS.open(\"\/test1.db\", FILE_WRITE); \/\/ \/littlefs is automatically added to the front \r\n file.close();\r\n }\r\n\r\n \/\/ We also need to create the journal file.\r\n if (!LITTLEFS.exists(\"\/test1.db-journal\")){\r\n File file = LITTLEFS.open(\"\/test1.db-journal\", FILE_WRITE); \/\/ \/littlefs is automatically added to the front \r\n file.close();\r\n }\r\n\r\n sqlite3_initialize();\r\n\r\n if (db_open(\"\/littlefs\/test1.db\", &db1))\r\n return;\r\n\r\n rc = db_exec(db1, \"CREATE TABLE test1 (id INTEGER, content);\");\r\n if (rc != SQLITE_OK) {\r\n sqlite3_close(db1);\r\n return;\r\n }\r\n\r\n rc = db_exec(db1, \"INSERT INTO test1 VALUES (1, 'Hello, World from test1');\");\r\n if (rc != SQLITE_OK) {\r\n sqlite3_close(db1);\r\n return;\r\n }\r\n\r\n rc = db_exec(db1, \"SELECT * FROM test1\");\r\n if (rc != SQLITE_OK) {\r\n sqlite3_close(db1);\r\n return;\r\n }\r\n\r\n sqlite3_close(db1);\r\n\r\n}\r\n\r\nvoid loop() {\r\n}\r\n```\r\n","issue_id":1660803919308,"origin_id":774480022,"user_origin_id":59926104,"create_time":1612619226,"update_time":1612619226,"id":1666798160026,"updated_at":"2022-10-26T15:29:20.026000Z","created_at":"2022-10-26T15:29:20.026000Z"},{"_id":"635952503056137e265e243e","body":"Thanks a lot for sharing your experience @mr-wiggle !!\r\nDoes it work for you @tobozo ? If not I will try and get back to you.","issue_id":1660803919308,"origin_id":774491171,"user_origin_id":12986780,"create_time":1612624086,"update_time":1612624086,"id":1666798160030,"updated_at":"2022-10-26T15:29:20.029000Z","created_at":"2022-10-26T15:29:20.029000Z"},{"_id":"635952503056137e265e243f","body":"@mr-wiggle @siara-cc thanks for your quick replies!\r\n\r\nI can't seem to get it working even after creating an empty file.\r\n\r\n```log\r\n\r\n17:07:53.412 -> [W][DB.h:879] createDB(): Creating file \/blemacs.db prior to db creation\r\n17:07:53.445 -> [E][DB.h:703] error(): SQL error: unable to open database file\r\n17:07:54.505 -> [E][DB.h:703] error(): SQL error: no such table: blemacs\r\n\r\n```\r\n\r\nbut I have other SPI issues when using LittleFS, which puzzles me as I don't have those problems with SPIFFS, so maybe it's a consequence of that situation rather than a library problem, I'll keep researching and post some updates\r\n","issue_id":1660803919308,"origin_id":774501241,"user_origin_id":1893754,"create_time":1612628206,"update_time":1612628206,"id":1666798160033,"updated_at":"2022-10-26T15:29:20.032000Z","created_at":"2022-10-26T15:29:20.032000Z"},{"_id":"635952503056137e265e2440","body":"update: I fixed the other SPI issue so it's either a LITTLEFS problem or a sqlite3 problem, here's what I've tried so far:\r\n\r\n- PSRam disabled (132kb heap free at worst)\r\n- 3.6MB or 7MB partitions (using a TTGO-TWatch so I don't have any other choice)\r\n- Different SQL create statements and database names\r\n- Uploading an empty file using LittleFS sketch data uploader\r\n\r\nI got the dreadful `SQL Error: unable to open database file` error after executing the `CREATE TABLE` statement in all situations and now I'm out of ideas to pinpoint the origin of the problem.\r\n","issue_id":1660803919308,"origin_id":774684305,"user_origin_id":1893754,"create_time":1612708557,"update_time":1612708557,"id":1666798160036,"updated_at":"2022-10-26T15:29:20.036000Z","created_at":"2022-10-26T15:29:20.036000Z"},{"_id":"635952503056137e265e2441","body":"Can you post your code? \r\n\r\none thing you can check too is to have the \"\/littlefs\" prefix included in all the sqlite calls, but don't include it when you're working with littleFS directly (eg. LITTLEFS.remove(\"\/test1.db\"); ) It's implied in the LITTLEFS calls, but not the sqlite calls.","issue_id":1660803919308,"origin_id":774684731,"user_origin_id":59926104,"create_time":1612708727,"update_time":1612708727,"id":1666798160038,"updated_at":"2022-10-26T15:29:20.038000Z","created_at":"2022-10-26T15:29:20.038000Z"},{"_id":"635952503056137e265e2442","body":"The POSIX path translation is already taken care of in my code, `BLEMacsDbFSPath` is the path for fs::FS and `BLEMacsDbSQLitePath` is the same path prefixed by `\/littlefs`.\r\n\r\n\r\n```C\r\n void createDB()\r\n {\r\n\r\n if( strcmp( BLE_FS_TYPE, \"littlefs\" ) == 0 ) {\r\n \/\/ LittleFS Exception: create file first or sqlite3 won't see it\r\n if( ! BLE_FS.exists( BLEMacsDbFSPath ) ) {\r\n log_w(\"Creating empty file %s (FS path)\", BLEMacsDbFSPath );\r\n fs::File tmp = BLE_FS.open( BLEMacsDbFSPath, FILE_WRITE );\r\n tmp.close();\r\n }\r\n }\r\n\r\n if( open(BLE_COLLECTOR_DB, false) ) {\r\n log_e(\"Could not open database\");\r\n return;\r\n }\r\n\r\n if( DBExec( BLECollectorDB, createTableQuery ) == SQLITE_OK ) {\r\n log_i(\"created %s : %s\", BLEMacsDbSQLitePath, createTableQuery);\r\n } else {\r\n log_e(\"CRITICAL: Failed to create db, halting system\");\r\n }\r\n\r\n close(BLE_COLLECTOR_DB);\r\n }\r\n```","issue_id":1660803919308,"origin_id":774706997,"user_origin_id":1893754,"create_time":1612716541,"update_time":1612716541,"id":1666798160041,"updated_at":"2022-10-26T15:29:20.040000Z","created_at":"2022-10-26T15:29:20.040000Z"},{"_id":"635952503056137e265e2443","body":"Looks like the issue is that you're not creating the journal file. That will allow you to open the database, but not do anything to it, which appears to be happening here.","issue_id":1660803919308,"origin_id":774709452,"user_origin_id":59926104,"create_time":1612717381,"update_time":1612717381,"id":1666798160043,"updated_at":"2022-10-26T15:29:20.042000Z","created_at":"2022-10-26T15:29:20.042000Z"},{"_id":"635952503056137e265e2444","body":"Found a workaround but it's not portable across filesystems:\r\n\r\n```C\r\nLITTLEFS.begin( false, \"\/spiffs\" ); \/\/ default for second argument is \"\/littlefs\"\r\n```\r\n\r\nLooks like the `partition_label` and `base_path` properties of the VFS configuration (type `esp_vfs_littlefs_conf_t`) are being confused somewhere in the code.\r\n","issue_id":1660803919308,"origin_id":774715131,"user_origin_id":1893754,"create_time":1612719244,"update_time":1612719244,"id":1666798160045,"updated_at":"2022-10-26T15:29:20.045000Z","created_at":"2022-10-26T15:29:20.045000Z"},{"_id":"635952503056137e265e2445","body":"I have not been able to create the LITTLEFS since last week to figure out the above issue. I get the following when trying to create LittleFS using ESP32 Sketch data upload tool.\r\n\r\nThere was already SPIFFS partition in it and I have done a full chip erase. Any idea why I am unable to create the LittleFS?\r\n\r\n```\r\nChip : esp32\r\nUsing partition scheme from Arduino IDE.\r\nStart: 0x150000\r\nSize : 0xb0000\r\nmklittlefs : \/Users\/arun\/Library\/Arduino15\/packages\/esp8266\/tools\/mklittlefs\/2.5.0-4-69bd9e6\/mklittlefs\r\n\r\nesptool : \/Users\/arun\/Library\/Arduino15\/packages\/esp32\/hardware\/esp32\/1.0.4\/tools\/esptool.py\r\n\r\n[LittleFS] data : \/Users\/arun\/Library\/Arduino15\/packages\/esp32\/hardware\/esp32\/1.0.4\/libraries\/esp32_arduino_sqlite3_lib\/examples\/sqlite3_spiffs\/data\r\n[LittleFS] offset : 0\r\n[LittleFS] start : 1376256\r\n[LittleFS] size : 704\r\n[LittleFS] page : 256\r\n[LittleFS] block : 4096\r\n->\/babyname.db\r\n->lfs warn:472: No more free space 263\r\n_>lfs_write error(-28): File system is full.\r\n_>\r\n_>error adding file!\r\n\r\nLittleFS Create Failed!\r\n```","issue_id":1660803919308,"origin_id":778729594,"user_origin_id":12986780,"create_time":1613280489,"update_time":1613280489,"id":1666798160048,"updated_at":"2022-10-26T15:29:20.048000Z","created_at":"2022-10-26T15:29:20.048000Z"},{"_id":"635952503056137e265e2446","body":"looks like you're currently using the LittleFS Uploader from esp8266 on an esp32\r\n\r\nhttps:\/\/github.com\/lorol\/arduino-esp32fs-plugin\/releases << this should be used\r\n\r\n\r\n\r\n","issue_id":1660803919308,"origin_id":778751478,"user_origin_id":1893754,"create_time":1613294790,"update_time":1613294790,"id":1666798160050,"updated_at":"2022-10-26T15:29:20.050000Z","created_at":"2022-10-26T15:29:20.050000Z"},{"_id":"635952503056137e265e2447","body":"@tobozo I noticed that. But I am using the release you have pointed to. Not sure how it is using `mklittlefs` from esp8266 folder.","issue_id":1660803919308,"origin_id":778767471,"user_origin_id":12986780,"create_time":1613303809,"update_time":1613303809,"id":1666798160052,"updated_at":"2022-10-26T15:29:20.052000Z","created_at":"2022-10-26T15:29:20.052000Z"},{"_id":"635952503056137e265e2448","body":"hmm could it be related to the Partition Scheme choice in your tools menu? what's your ESP32 model?\r\n\r\n[edit] downloaded the [latest `mklittlefs` binary](https:\/\/github.com\/earlephilhower\/mklittlefs\/releases) and copied here: `~\/.arduino15\/packages\/esp32\/hardware\/esp32\/1.0.4\/tools\/` \r\n\r\nThis fixed the sdk mismatch for me\r\n\r\n\r\n\r\n\r\n\r\n","issue_id":1660803919308,"origin_id":778768817,"user_origin_id":1893754,"create_time":1613304572,"update_time":1613314645,"id":1666798160054,"updated_at":"2022-10-26T15:29:20.054000Z","created_at":"2022-10-26T15:29:20.054000Z"},{"_id":"635952503056137e265e2449","body":"@tobozo I am using a ESP-WROOM-32. Your suggestion did change it from esp8266 to esp32 folder, but the rest are still the same:\r\n\r\n```\r\nChip : esp32\r\nUsing partition scheme from Arduino IDE.\r\nStart: 0x150000\r\nSize : 0xb0000\r\nmklittlefs : \/Users\/arun\/Library\/Arduino15\/packages\/esp32\/hardware\/esp32\/1.0.4\/tools\/mklittlefs\r\n\r\nesptool : \/Users\/arun\/Library\/Arduino15\/packages\/esp32\/hardware\/esp32\/1.0.4\/tools\/esptool.py\r\n\r\n[LittleFS] data : \/Users\/arun\/Library\/Arduino15\/packages\/esp32\/hardware\/esp32\/1.0.4\/libraries\/esp32_arduino_sqlite3_lib\/examples\/sqlite3_spiffs\/data\r\n[LittleFS] offset : 0\r\n[LittleFS] start : 1376256\r\n[LittleFS] size : 704\r\n[LittleFS] page : 256\r\n[LittleFS] block : 4096\r\n->\/babyname.db\r\n->lfs warn:475: No more free space 291\r\n_>lfs_write error(-28): File system is full.\r\n_>\r\n_>error adding file!\r\n\r\nLittleFS Create Failed!\r\n```\r\nProbably the existing SPIFFS partition is leaving very little space for LITTLEFS. I will have to check later. I don't actively work on these during weekdays.","issue_id":1660803919308,"origin_id":778811237,"user_origin_id":12986780,"create_time":1613324178,"update_time":1613324178,"id":1666798160057,"updated_at":"2022-10-26T15:29:20.057000Z","created_at":"2022-10-26T15:29:20.057000Z"},{"_id":"635952503056137e265e244a","body":"Looks like you are still using the older mklittlefs 2.5.0-4 and all its bugs, maybe don't recycle mklittlefs from and older esp8266 sdk folder but use the latest from the release pages instead ?\r\n\r\nhttps:\/\/github.com\/earlephilhower\/mklittlefs\/releases\r\n\r\n\r\n","issue_id":1660803919308,"origin_id":778817516,"user_origin_id":1893754,"create_time":1613326573,"update_time":1613326573,"id":1666798160059,"updated_at":"2022-10-26T15:29:20.059000Z","created_at":"2022-10-26T15:29:20.059000Z"},{"_id":"635952503056137e265e244b","body":"@tobozo I am sure I copied the latest one to the esp32 folder. Further I moved the esp8266 one out of its tools folder, restarted my machine, still getting the same issue. I have been facing issues with these partitions since someone mentioned LITTLEFS support long time ago. I will figure it out soon.","issue_id":1660803919308,"origin_id":778820598,"user_origin_id":12986780,"create_time":1613327613,"update_time":1613327613,"id":1666798160062,"updated_at":"2022-10-26T15:29:20.061000Z","created_at":"2022-10-26T15:29:20.061000Z"},{"_id":"635952503056137e265e244c","body":"If it can be of some help, I don't have these three lines when running the filesystem uploader:\r\n\r\n```log\r\nUsing partition scheme from Arduino IDE.\r\nStart: 0x150000\r\nSize : 0xb0000\r\n```\r\n\r\nI'm not sure where these lines are coming from in your situation, the other thing I can think of is that there is a difference between the MacOS implementation and the Linux implementation.\r\n\r\nWhat happens when the data folder is empty, do you get a dialog like this one and the same error ?\r\n\r\n\r\n\r\nAlso what's the size of the `\/babyname.db` file ?\r\n\r\n[edit] trying to fit a 1.52Mb file on a 704Kb partition sure won't work, use a smaller file or choose a larger partition :-)\r\n\r\n\r\n\r\n","issue_id":1660803919308,"origin_id":778822020,"user_origin_id":1893754,"create_time":1613328191,"update_time":1613328298,"id":1666798160064,"updated_at":"2022-10-26T15:29:20.064000Z","created_at":"2022-10-26T15:29:20.064000Z"},{"_id":"635952503056137e265e244d","body":"@tobozo I deleted the babyname.db and I got the same error. Not sure what the issue is, but I was sure it is something to do with the existing SPIFFS partition.\r\n\r\nSo then I used this https:\/\/github.com\/joltwallet\/esp_littlefs component for esp-idf and this demo repo https:\/\/github.com\/wreyford\/demo_esp_littlefs to create the partition.\r\n\r\nNow I could run the program provided by @mr-wiggle successfully. I will see if I can get the console program working.\r\n\r\nThank you for the inputs.","issue_id":1660803919308,"origin_id":782595075,"user_origin_id":12986780,"create_time":1613813569,"update_time":1613813569,"id":1666798160067,"updated_at":"2022-10-26T15:29:20.066000Z","created_at":"2022-10-26T15:29:20.066000Z"},{"_id":"635952503056137e265e244e","body":"I could get the console program working too when I set `#define SPIFFS LITTLEFS` at the beginning.\r\n\r\nI will see if it can be integrated into the Library.","issue_id":1660803919308,"origin_id":782608254,"user_origin_id":12986780,"create_time":1613819577,"update_time":1613819577,"id":1666798160069,"updated_at":"2022-10-26T15:29:20.069000Z","created_at":"2022-10-26T15:29:20.069000Z"},{"_id":"635952503056137e265e2450","body":"Hi guys, if you're still trying to figure out how to use the SQlite3 along with LITLLEFS I have found out the following:\r\n1- Set the basePath of the LITTLEFS as suggested by @tobozo.\r\n> ```c\r\n> LITTLEFS.begin( false, \"\/spiffs\" ); \/\/ default for second argument is \"\/littlefs\"\r\n> ```\r\n\r\n2 - The files are not automatically created when openning the database because the stat call in the function ESP32Open located at the source file esp32.cpp returns -2, instead of the expected -1. Don't know why, maybe a LITLLEFS peculiarity?\r\n So it is assumed that the file already exists and it is openned with the flag \"r+\" which does not create the file if not exists. This is a possible fix:\r\n\r\n ```cpp\r\n\/*\r\n** Open a file handle.\r\n*\/\r\nstatic int ESP32Open(\r\n sqlite3_vfs *pVfs, \/* VFS *\/\r\n const char *zName, \/* File to open, or 0 for a temp file *\/\r\n sqlite3_file *pFile, \/* Pointer to ESP32File struct to populate *\/\r\n int flags, \/* Input SQLITE_OPEN_XXX flags *\/\r\n int *pOutFlags \/* Output SQLITE_OPEN_XXX flags (or NULL) *\/\r\n){\r\n static const sqlite3_io_methods ESP32io = {\r\n 1, \/* iVersion *\/\r\n ESP32Close, \/* xClose *\/\r\n ESP32Read, \/* xRead *\/\r\n ESP32Write, \/* xWrite *\/\r\n ESP32Truncate, \/* xTruncate *\/\r\n ESP32Sync, \/* xSync *\/\r\n ESP32FileSize, \/* xFileSize *\/\r\n ESP32Lock, \/* xLock *\/\r\n ESP32Unlock, \/* xUnlock *\/\r\n ESP32CheckReservedLock, \/* xCheckReservedLock *\/\r\n ESP32FileControl, \/* xFileControl *\/\r\n ESP32SectorSize, \/* xSectorSize *\/\r\n ESP32DeviceCharacteristics \/* xDeviceCharacteristics *\/\r\n };\r\n\r\n ESP32File *p = (ESP32File*)pFile; \/* Populate this structure *\/\r\n int oflags = 0; \/* flags to pass to open() call *\/\r\n char *aBuf = 0;\r\n\tchar mode[5];\r\n \/\/Serial.println(\"fn: Open\");\r\n\r\n\tstrcpy(mode, \"r\");\r\n if( zName==0 ){\r\n return SQLITE_IOERR;\r\n }\r\n\r\n if( flags&SQLITE_OPEN_MAIN_JOURNAL ){\r\n aBuf = (char *)sqlite3_malloc(SQLITE_ESP32VFS_BUFFERSZ);\r\n if( !aBuf ){\r\n return SQLITE_NOMEM;\r\n }\r\n }\r\n\r\n\tif( flags&SQLITE_OPEN_CREATE || flags&SQLITE_OPEN_READWRITE \r\n || flags&SQLITE_OPEN_MAIN_JOURNAL ) {\r\n struct stat st;\r\n memset(&st, 0, sizeof(struct stat));\r\n int rc = stat( zName, &st );\r\n \r\n \/\/Serial.print(zName);\r\n \/\/Serial.print(\":\");\r\n \/\/Serial.println(rc);\r\n\r\n\/* FIX FOR THE ISSUE: Check if less than 0, if so file does not exist *\/\r\n\t\tif (rc < 0) { \r\n strcpy(mode, \"w+\");\r\n \/\/int fd = open(zName, (O_CREAT | O_RDWR | O_EXCL), S_IRUSR | S_IWUSR);\r\n \/\/close(fd);\r\n \/\/oflags |= (O_CREAT | O_RDWR);\r\n \/\/Serial.println(\"Create mode\");\r\n } else\r\n strcpy(mode, \"r+\");\r\n\t}\r\n\r\n memset(p, 0, sizeof(ESP32File));\r\n \/\/p->fd = open(zName, oflags, 0600);\r\n \/\/p->fd = open(zName, oflags, S_IRUSR | S_IWUSR);\r\n p->fp = fopen(zName, mode);\r\n if( p->fp<=0){\r\n if (aBuf)\r\n sqlite3_free(aBuf);\r\n \/\/Serial.println(\"Can't open\");\r\n return SQLITE_CANTOPEN;\r\n }\r\n p->aBuffer = aBuf;\r\n\r\n if( pOutFlags ){\r\n *pOutFlags = flags;\r\n }\r\n p->base.pMethods = &ESP32io;\r\n \/\/Serial.println(\"fn:Open:Success\");\r\n return SQLITE_OK;\r\n}\r\n```\r\n\r\nI could create a pull request, but since it's a very minor issue, it should take no time for @siara-cc to fix it.\r\n\r\n\r\n\r\n\r\n\r\n","issue_id":1660803919308,"origin_id":951087900,"user_origin_id":51972053,"create_time":1635178652,"update_time":1635178713,"id":1666798160073,"updated_at":"2022-10-26T15:29:20.073000Z","created_at":"2022-10-26T15:29:20.073000Z"},{"_id":"635952503056137e265e2451","body":"https:\/\/github.com\/muratdemirtas\/sqlite3-esp32-littlefs-sdcard-sdspi","issue_id":1660803919308,"origin_id":1168978666,"user_origin_id":9981092,"create_time":1656434980,"update_time":1656434980,"id":1666798160077,"updated_at":"2022-10-26T15:29:20.077000Z","created_at":"2022-10-26T15:29:20.077000Z"},{"_id":"635952503056137e265e2452","body":"I fixed this long pending one. Thanks @wesee-dev \r\n@muratdemirtas If possible could you please include a description of the link you provided? It seems not the fix discussed here.","issue_id":1660803919308,"origin_id":1174069785,"user_origin_id":12986780,"create_time":1656959960,"update_time":1656959960,"id":1666798160081,"updated_at":"2022-10-26T15:29:20.080000Z","created_at":"2022-10-26T15:29:20.080000Z"},{"_id":"635952503056137e265e2453","body":"> I fixed this long pending one. Thanks @wesee-dev\r\n> @muratdemirtas If possible could you please include a description of the link you provided? It seems not the fix discussed here.\r\n\r\ni'll do when implementation is done. that was an example who want to use with littlefs.\r\n\r\nI'm testing latest pull request.\r\n\r\nhttps:\/\/github.com\/raldone01\/esp_littlefs\/blob\/pullReq\/CMakeLists.txt","issue_id":1660803919308,"origin_id":1174079325,"user_origin_id":9981092,"create_time":1656960808,"update_time":1656960808,"id":1666798160083,"updated_at":"2022-10-26T15:29:20.083000Z","created_at":"2022-10-26T15:29:20.083000Z"},{"_id":"635952503056137e265e2454","body":"I have wrote example code for testing littlefs over sdspi mmc interface of ESP32. But this was an example.\r\n\r\nI have forked and implemented esp32 littlefs component with latest pull request provided by @raldone01\r\n\r\nhttps:\/\/github.com\/raldone01\/esp_littlefs\/\r\n\r\nwhen you install esp littlefs component for esp-idf from link (https:\/\/github.com\/joltwallet\/esp_littlefs) then you have to merge your component with @raldoneo1 pull request(https:\/\/github.com\/raldone01\/esp_littlefs\/)\r\n\r\nafter that users have to create littlefs mount point in sdmmc example...\r\n \r\n\r\n> \t\/\/ Use settings defined above to initialize and mount LittleFS filesystem.\r\n\t\/\/ Note: esp_littlefs_flash_create creates the little fs and mounts it.\r\n\tesp_err_t ret = esp_littlefs_sd_create(&handle, &conf);\r\n\/\/ init lfs with a backend here\r\n\tesp_littlefs_vfs_mount_conf_t conf= ESP_LITTLEFS_VFS_MOUNT_CONFIG_DEFAULT();\r\n\tconf.lfs = handle;\r\n\/\/ set config here\r\n\tESP_ERROR_CHECK(esp_littlefs_vfs_mount(&conf));\r\n\r\nhttps:\/\/gist.github.com\/muratdemirtas\/fe10ca3dbde1558ff0f42f337f3b9fc7\r\n\r\nafter that virtual file system operations is esp32.c works perfect which means sqlite3 can be used over sd cards.\r\n \r\nI have tested and working good.\r\n\r\ndo not forget to add \"\/littlefs\/\" path for your database for example \"\/littlefs\/Database.db\"\r\n\r\n@siara-cc ","issue_id":1660803919308,"origin_id":1174222961,"user_origin_id":9981092,"create_time":1656964800,"update_time":1656965359,"id":1666798160086,"updated_at":"2022-10-26T15:29:20.085000Z","created_at":"2022-10-26T15:29:20.085000Z"},{"_id":"635952503056137e265e2455","body":"@siara-cc one insert operation takes 1667 ms. omg. \r\n\r\n\r\n[01:12:54:306] <0x1b>[0;32mI (22:12:54.328) SQLite3DataProvider: Time taken: 1667<0x1b>[0m\u240d\u240a\r\n\r\n**edit: fixed now takes 235 ms absolutely very fast.**","issue_id":1660803919308,"origin_id":1174425973,"user_origin_id":9981092,"create_time":1656972827,"update_time":1656976910,"id":1666798160088,"updated_at":"2022-10-26T15:29:20.088000Z","created_at":"2022-10-26T15:29:20.088000Z"},{"_id":"635952503056137e265e2456","body":"Mhh if you are using my code make sure too initialize the sdmmc correctly to use the maximum available speed.\r\nI use it in my projects to stream audio and store files with no issues. (Also take a look at the buffersizes and menuconfig options.)\r\n\r\nPs: I plan to rebase to upstream in a month or so and improve docs.","issue_id":1660803919308,"origin_id":1174430752,"user_origin_id":27293675,"create_time":1656973298,"update_time":1656973384,"id":1666798160091,"updated_at":"2022-10-26T15:29:20.091000Z","created_at":"2022-10-26T15:29:20.091000Z"},{"_id":"635952503056137e265e2457","body":"@raldone01 yes i'm using your code. i really appreciate your work. I fixed my code. Currently;\r\n\r\none insert operation takes 223 ms.\r\n\r\n[01:12:54:306] <0x1b>[0;32mI (22:12:54.328) SQLite3DataProvider: Time taken: 223<0x1b>[0m\u240d\u240a\r\n\r\nthanks.\r\n","issue_id":1660803919308,"origin_id":1174454032,"user_origin_id":9981092,"create_time":1656976839,"update_time":1656977570,"id":1666798160093,"updated_at":"2022-10-26T15:29:20.093000Z","created_at":"2022-10-26T15:29:20.093000Z"},{"_id":"635952503056137e265e2458","body":"@raldone01 i have a question, if i use 1gb sd card(512 byte sector) then everything is ok but when i plug 16gb sd card(512 byte sector size) then this error throws. is this 24 bit\/32 bit addressing issue?\r\n\r\n> [02:21:53:372] <0x1b>[0;31mE (23:21:53.316) sdmmc_cmd: sdmmc_read_sectors_dma: sdmmc_send_cmd returned 0x107<0x1b>[0m\u240d\u240a\r\n[02:21:53:372] <0x1b>[0;31mE (23:21:53.316) LFS_FLASH: failed to read block 29520438, size 512, err 263<0x1b>[0m\u240d\u240a\r\n[02:21:53:372] <0x1b>[0;32mI (23:21:53.329) TAG: sqlite3 used size: 0<0x1b>[0m\u240d\u240a\r\n[02:21:53:372] <0x1b>[0;31mE (23:21:53.341) SQLite3DataProvider: SQL ERROR LENGHT:32<0x1b>[0m\u240d\u240a\r\n\r\nwhen i limit block count to 50000, again i got this.\r\n\r\n> [02:33:37:466] <0x1b>[0;31mE (23:33:37.455) sdmmc_cmd: sdmmc_read_sectors_dma: sdmmc_send_cmd returned 0x107<0x1b>[0m\u240d\u240a\r\n[02:33:37:466] <0x1b>[0;31mE (23:33:37.455) LFS_FLASH: failed to read block 7986, size 512, err 263<0x1b>[0m\u240d\u240a\r\n[02:33:37:466] <0x1b>[0;32mI (23:33:37.461) TAG: sqlite3 used size: 0<0x1b>[0m\u240d\u240a\r\n[02:33:37:466] <0x1b>[0;31mE (23:33:37.464) SQLite3DataProvider: SQL ERROR LENGHT:32<0x1b>[0m\u240d\u240a\r\n[02:33:37:466] <0x1b>[0;31mE (23:33:37.470) SQLite3DataProvider: SQL error: database disk image is malformed<0x1b>[0m\u240d\u240a\r\n\r\nmy sd card information:\r\n\r\n> [02:36:49:165] SD MCC init ok\u240d\u240a\r\n[02:36:49:165] Name: SD\u240d\u240a\r\n[02:36:49:165] Type: SDHC\/SDXC\u240d\u240a\r\n[02:36:49:165] Speed: 40 MHz\u240d\u240a\r\n[02:36:49:165] Size: 14922MB\u240d\u240a\r\n\r\nstable card information\r\n\r\n> [03:03:31:799] Name: SD01G\u240d\u240a\r\n[03:03:31:799] Type: SDSC\u240d\u240a\r\n[03:03:31:799] Speed: 40 MHz\u240d\u240a\r\n[03:03:31:799] Size: 922MB\u240d\u240a\r\n\r\n@siara-cc ","issue_id":1660803919308,"origin_id":1174455755,"user_origin_id":9981092,"create_time":1656977081,"update_time":1656979453,"id":1666798160096,"updated_at":"2022-10-26T15:29:20.095000Z","created_at":"2022-10-26T15:29:20.095000Z"},{"_id":"635952503056137e265e2459","body":"https:\/\/github.com\/espressif\/esp-idf\/issues\/9289 @siara-cc @raldone01 i created an issue.","issue_id":1660803919308,"origin_id":1174481377,"user_origin_id":9981092,"create_time":1656982008,"update_time":1656982008,"id":1666798160098,"updated_at":"2022-10-26T15:29:20.098000Z","created_at":"2022-10-26T15:29:20.098000Z"},{"_id":"635952503056137e265e245a","body":"i resolved this issue by adding retry algorithm for sd mmc read timeouts\r\n\r\nthe problem was \r\n\r\n> [02:33:37:466] <0x1b>[0;31mE (23:33:37.455) sdmmc_cmd: sdmmc_read_sectors_dma: sdmmc_send_cmd returned 0x107<0x1b>[0m\u240d\u240a\r\n\r\nthanks to retrying algorithm the esp32 will retry the communication 5 times. this resolves the issue.\r\n\r\nSqlite3 now works with SDHC\/SDXC 16GB cards\r\n\r\n\r\n> static int littlefs_api_read(const struct lfs_config *c, lfs_block_t block,\r\n lfs_off_t off, void *buffer, lfs_size_t size) {\r\n struct esp_littlefs_sd_ctx *ctx = c->context;\r\n int retry_count = 0; int32_t start_time = 0;\r\n\tesp_err_t err = ESP_FAIL;\r\n\tfor(retry_count = 0; retry_count < 5; retry_count++){\r\n\t\tstart_time= esp_timer_get_time();\r\n\t\terr = sdmmc_read_sectors(&ctx->sd, ctx->non_dma_buf,\r\n\t\t\t\t\t\t\t\t\t\t block , 1);\r\n\t\tif (err != ESP_OK) {\r\n\t\t\tprintf(\"[READ]Failed at block %d, err_code, retry_count %d \\n\",\r\n\t\t \t\t\t\t\t\t\t\t\t\t\t\tblock,\tretry_count );\r\n\t\t\tcontinue;\r\n\t\t}\r\n\t\tmemcpy(buffer , ctx->non_dma_buf, 512);\r\n\t\tprintf(\"[READ]Read ok block %d, size %d\\n\", block, size);\r\n\t\tbreak;\r\n }\r\n\tif(retry_count == 4){\r\n\t\tprintf(\"[READ]Cannot read block %d\\n\", block);\r\n\t\treturn LFS_ERR_IO;\r\n\t}\r\n\tint32_t stop_time = esp_timer_get_time() - start_time;\r\n\tlfs_perf_counter.read_size += size;\r\n\tlfs_perf_counter.read_step++;\r\n\tlfs_perf_counter.read_time += stop_time;\r\n\tsdmmc_benchmark_printf();\r\n return 0;\r\n}\r\n\r\n> [22:22:10:125] <0x1b>[0;33mW (19:22:10.043) -: DB before open:909375 <0x1b>[0m\u240d\u240a\r\n[22:22:10:205] <0x1b>[0;31mE (19:22:10.150) sdmmc_cmd: sdmmc_read_sectors_dma: sdmmc_send_cmd returned 0x107<0x1b>[0m\u240d\u240a\r\n[22:22:10:244] **[READ]Failed at block 1, err_code, retry_count 0** \u240d\u240a\r\n[22:22:10:244] <0x1b>[0;33mW (19:22:10.164) -: DB after open:904843 <0x1b>[0m\u240d\u240a\r\n[22:22:10:244] <0x1b>[0;33mW (19:22:10.165) SQLite3DataProvider: **Database init successfull**<0x1b>[0m\u240d\u240a\r\n[22:22:10:675] <0x1b>[0;32mI (19:22:10.617) SQLite3DataProvider: **SQL success**<0x1b>[0m\u240d\u240a\r\n[22:22:10:719] <0x1b>[0;32mI (19:22:10.617) SQLite3DataProvider: Change count:1<0x1b>[0m\u240d\u240a\r\n[22:22:10:719] <0x1b>[0;33mW (19:22:10.618) -: DB before close:898447 <0x1b>[0m\u240d\u240a\r\n[22:22:10:719] <0x1b>[0;33mW (19:22:10.626) -: DB after close:909375 <0x1b>[0m\u240d\u240a","issue_id":1660803919308,"origin_id":1175404573,"user_origin_id":9981092,"create_time":1657048051,"update_time":1657049017,"id":1666798160101,"updated_at":"2022-10-26T15:29:20.100000Z","created_at":"2022-10-26T15:29:20.100000Z"},{"_id":"662d811c6c617b7b500cd29d","body":"> Hi guys, if you're still trying to figure out how to use the SQlite3 along with LITLLEFS I have found out the following: 1- Set the basePath of the LITTLEFS as suggested by @tobozo.\r\n> \r\n> > ```c\r\n> > LITTLEFS.begin( false, \"\/spiffs\" ); \/\/ default for second argument is \"\/littlefs\"\r\n> > ```\r\n> \r\n> 2 - The files are not automatically created when openning the database because the stat call in the function ESP32Open located at the source file esp32.cpp returns -2, instead of the expected -1. Don't know why, maybe a LITLLEFS peculiarity? So it is assumed that the file already exists and it is openned with the flag \"r+\" which does not create the file if not exists. This is a possible fix:\r\n> \r\n> ```c++\r\n> \/*\r\n> ** Open a file handle.\r\n> *\/\r\n> static int ESP32Open(\r\n> sqlite3_vfs *pVfs, \/* VFS *\/\r\n> const char *zName, \/* File to open, or 0 for a temp file *\/\r\n> sqlite3_file *pFile, \/* Pointer to ESP32File struct to populate *\/\r\n> int flags, \/* Input SQLITE_OPEN_XXX flags *\/\r\n> int *pOutFlags \/* Output SQLITE_OPEN_XXX flags (or NULL) *\/\r\n> ){\r\n> static const sqlite3_io_methods ESP32io = {\r\n> 1, \/* iVersion *\/\r\n> ESP32Close, \/* xClose *\/\r\n> ESP32Read, \/* xRead *\/\r\n> ESP32Write, \/* xWrite *\/\r\n> ESP32Truncate, \/* xTruncate *\/\r\n> ESP32Sync, \/* xSync *\/\r\n> ESP32FileSize, \/* xFileSize *\/\r\n> ESP32Lock, \/* xLock *\/\r\n> ESP32Unlock, \/* xUnlock *\/\r\n> ESP32CheckReservedLock, \/* xCheckReservedLock *\/\r\n> ESP32FileControl, \/* xFileControl *\/\r\n> ESP32SectorSize, \/* xSectorSize *\/\r\n> ESP32DeviceCharacteristics \/* xDeviceCharacteristics *\/\r\n> };\r\n> \r\n> ESP32File *p = (ESP32File*)pFile; \/* Populate this structure *\/\r\n> int oflags = 0; \/* flags to pass to open() call *\/\r\n> char *aBuf = 0;\r\n> char mode[5];\r\n> \/\/Serial.println(\"fn: Open\");\r\n> \r\n> strcpy(mode, \"r\");\r\n> if( zName==0 ){\r\n> return SQLITE_IOERR;\r\n> }\r\n> \r\n> if( flags&SQLITE_OPEN_MAIN_JOURNAL ){\r\n> aBuf = (char *)sqlite3_malloc(SQLITE_ESP32VFS_BUFFERSZ);\r\n> if( !aBuf ){\r\n> return SQLITE_NOMEM;\r\n> }\r\n> }\r\n> \r\n> if( flags&SQLITE_OPEN_CREATE || flags&SQLITE_OPEN_READWRITE \r\n> || flags&SQLITE_OPEN_MAIN_JOURNAL ) {\r\n> struct stat st;\r\n> memset(&st, 0, sizeof(struct stat));\r\n> int rc = stat( zName, &st );\r\n> \r\n> \/\/Serial.print(zName);\r\n> \/\/Serial.print(\":\");\r\n> \/\/Serial.println(rc);\r\n> \r\n> \/* FIX FOR THE ISSUE: Check if less than 0, if so file does not exist *\/\r\n> \tif (rc < 0) { \r\n> strcpy(mode, \"w+\");\r\n> \/\/int fd = open(zName, (O_CREAT | O_RDWR | O_EXCL), S_IRUSR | S_IWUSR);\r\n> \/\/close(fd);\r\n> \/\/oflags |= (O_CREAT | O_RDWR);\r\n> \/\/Serial.println(\"Create mode\");\r\n> } else\r\n> strcpy(mode, \"r+\");\r\n> }\r\n> \r\n> memset(p, 0, sizeof(ESP32File));\r\n> \/\/p->fd = open(zName, oflags, 0600);\r\n> \/\/p->fd = open(zName, oflags, S_IRUSR | S_IWUSR);\r\n> p->fp = fopen(zName, mode);\r\n> if( p->fp<=0){\r\n> if (aBuf)\r\n> sqlite3_free(aBuf);\r\n> \/\/Serial.println(\"Can't open\");\r\n> return SQLITE_CANTOPEN;\r\n> }\r\n> p->aBuffer = aBuf;\r\n> \r\n> if( pOutFlags ){\r\n> *pOutFlags = flags;\r\n> }\r\n> p->base.pMethods = &ESP32io;\r\n> \/\/Serial.println(\"fn:Open:Success\");\r\n> return SQLITE_OK;\r\n> }\r\n> ```\r\n> \r\n> I could create a pull request, but since it's a very minor issue, it should take no time for @siara-cc to fix it.\r\n\r\nSorry for which part of the code or library I had to change or overwrite, the modification you share, I have the same problem of \"unable to open database file\" when I try to use INSERT or CREATE TABLE, but not to obtain data with SELECT, thanks","issue_id":1660803919308,"origin_id":1673943813,"user_origin_id":53156050,"create_time":1691702906,"update_time":1691702906,"id":1714258204158,"updated_at":"2024-04-27T22:50:04.158000Z","created_at":"2024-04-27T22:50:04.158000Z"}]
hey Arun and thanks for your awesome library :+1: I'm not sure I'm doing things properly, but I'm experiencing problems creating db files when using LittleFS as a substitute to...
800 inserts/updates per second versus 22 inserts per second when adding "ON CONFLICT DO UPDATE"
[{"_id":"63594dd23056137e265e20ac","body":"In SQLite, tables are also indexes which have the primary key as ROWID, which is invisible. So if you are updating using mac_id upon conflict, it would have no choice other than searching the table sequentially. I would suggest using `without rowid` clause to force mac_id as primary key of the table instead of ROWID to improve speed. Example:\r\n\r\n```sql\r\ncreate table t1 (c1 primary key, c2, c3) without rowid;\r\ninsert into t1 values ('v1', 'v2', 'v3');\r\ninsert into t1 values ('v1', 'v2', 'v3') on conflict (c1) do update set c3= 'v4'\r\n```","issue_id":1660803919328,"origin_id":1007895401,"user_origin_id":12986780,"create_time":1641623094,"update_time":1641623094,"id":1666797010178,"updated_at":"2022-10-26T15:10:10.178000Z","created_at":"2022-10-26T15:10:10.178000Z"},{"_id":"63594dd23056137e265e20ad","body":"For using the FRAM you would have to change esp32.c to write to FRAM using API for whatever file system it supports. If it is raw data storage then you would have to implement your own way of storing and retrieving files.\r\n\r\nAlternatively you could also look at https:\/\/github.com\/siara-cc\/sqlite_micro_logger_arduino which has callback methods for IO which lets you store\/retrieve from whichever storage medium as you please. However there are a lot of limitations. SQL not supported. It was made mainly for storing sensor data.","issue_id":1660803919328,"origin_id":1007895775,"user_origin_id":12986780,"create_time":1641623319,"update_time":1641623319,"id":1666797010181,"updated_at":"2022-10-26T15:10:10.181000Z","created_at":"2022-10-26T15:10:10.181000Z"},{"_id":"63594dd23056137e265e20ae","body":"Hi Arun, your suggestions made it a tiny bit faster ;) However I don't think I can make it any quicker with a SPI SD configuration. **The real deal breaker seems to use a SD MMC configuration**. I wrote a little program that fetch information from the SQL database, stores them in variables, shows them on a serial Monitor. And afterwards manipulates the SQL data to change the output in the next loop run. I am using two SQL prepare statements (one for SELECT + JOIN) and one for (UPDATE to manipulate by random). With an SD SPI card and about 40 rows \/ 7 columns it took about 5 to 7 second. While the sqlite3_step(res) part runs quite fast (a few Milliseconds). Before and after that it took quite some time to process the statements (between 2 - 4 seconds). My goal is to use the SQL database for graphical information. Storing all graphical information in the database and to be able to manipulate it with a PC via SD Card. \r\n\r\nBUT ;-) If I use ESP32 with an SD MMC configuration the whole process takes 400 milliseconds without a \"Begin Transaction\" statement in the setup. Instead of 5 to 7 seconds! Using the \"Begin Transaction\" the total time needed shrinks to 30 milliseconds! That is great! \r\n ON CONFLICT (...) DO UPDATE statements are also much, much faster with an SD MMC configuration. In the case of the example in my first post I can \"upsert\" instead of 22 rows per seconds about 400 to 500 rows per second. \r\n \r\n Summary:\r\n SPI SD = 5000-7000 milisec.\r\n SDMMC without transaction: 400 milisec.\r\n SDMMC with transaction: 30 milisec! (22 for Select+Join and 8 for Update)\r\n\r\nThis brings me to another question. Where the hell do I get ESP32 boards with SDMMC configuration?! ;-). I have tried to find the board from your picture without success. I have also tried soooo many boards that on paper looked promising. I also tried the [Waveshare Micro SD Storage Board](https:\/\/www.waveshare.com\/w\/upload\/8\/83\/Micro-SD-Storage-Board-Schematic.pdf), that has all the right resistors in place. However I was not able to get it running. Only the AI-Thinker ESP32-CAM is working out of the box. Is it possible to change the pin mapping for SD MMC? I would like to try the layout from the ESP32 documentation: https:\/\/docs.espressif.com\/projects\/esp-idf\/en\/latest\/esp32\/api-reference\/peripherals\/sdmmc_host.html \r\n\r\n","issue_id":1660803919328,"origin_id":1008332973,"user_origin_id":96186879,"create_time":1641746443,"update_time":1641746443,"id":1666797010185,"updated_at":"2022-10-26T15:10:10.185000Z","created_at":"2022-10-26T15:10:10.185000Z"},{"_id":"63594dd23056137e265e20af","body":"Thanks for sharing your experience!! I purchased the esp32+sdmmc board from banggood.com but I am unable to find it now either.","issue_id":1660803919328,"origin_id":1008345394,"user_origin_id":12986780,"create_time":1641751046,"update_time":1641751046,"id":1666797010188,"updated_at":"2022-10-26T15:10:10.187000Z","created_at":"2022-10-26T15:10:10.187000Z"},{"_id":"63594dd23056137e265e20b0","body":"Should I close this issue? Or should I let it open? \r\nBy the way. I copied the database to spiff and tried the SPIFF and LITTLEFS library.\r\nWithout the \"Begin Transaction\" statement updates were quite slow (nearly a second for each row). With it I am getting the following results:\r\n\r\n*total time for all processes (described in the post above:Select+Join & Update)*\r\nspiff with SPIFF library; 350ms (very inconsistent between 170 to 500ms)\r\nspiff with LITTLEFS library: 140ms (very consistent between 135 to 145ms: about 6ms for Updating and 130 for select+join) \r\n\r\nTo get LITTLEFS running I had to add the solutions posted [here](https:\/\/github.com\/siara-cc\/esp32_arduino_sqlite3_lib\/issues\/44): `LittleFS.begin(false, \"\/spiffs\")` + modifying esp32.cpp","issue_id":1660803919328,"origin_id":1008471323,"user_origin_id":96186879,"create_time":1641777341,"update_time":1641804188,"id":1666797010191,"updated_at":"2022-10-26T15:10:10.190000Z","created_at":"2022-10-26T15:10:10.190000Z"},{"_id":"63594dd23056137e265e20b1","body":"I will fix it for LITTLEFS during weekend and close both. Thanks for the info!!","issue_id":1660803919328,"origin_id":1008766212,"user_origin_id":12986780,"create_time":1641812862,"update_time":1641812862,"id":1666797010193,"updated_at":"2022-10-26T15:10:10.193000Z","created_at":"2022-10-26T15:10:10.193000Z"},{"_id":"63594dd23056137e265e20b3","body":"Hi, any update on LittleFS fix?","issue_id":1660803919328,"origin_id":1121304589,"user_origin_id":43222981,"create_time":1652112955,"update_time":1652112955,"id":1666797010198,"updated_at":"2022-10-26T15:10:10.198000Z","created_at":"2022-10-26T15:10:10.198000Z"},{"_id":"63594dd23056137e265e20b4","body":"@pasleto I fixed the issue and also added an example for Little FS. Thanks @Fischkopppp !!","issue_id":1660803919328,"origin_id":1174070195,"user_origin_id":12986780,"create_time":1656960000,"update_time":1656960000,"id":1666797010204,"updated_at":"2022-10-26T15:10:10.204000Z","created_at":"2022-10-26T15:10:10.204000Z"}]
Hi Arun, first of all thank you for this great library! I absolutely love it. I have used your "bulk_data_insert" example, adding "BEGIN TRANSACTION" / "END TANSACTION" and I am...
SQLite 2.3.0 - DELETE FROM [tablename] with many records results in I/O Error
[{"_id":"635953538041c95dfb15eb03","body":"Did you manage to resolve this? I'm getting the same issue.\r\n\r\nMy HEAP and SPIFFS size are all fine, not sure if it's an ESP32 issue or a library level issue?","issue_id":1660803919335,"origin_id":940885133,"user_origin_id":4587382,"create_time":1634035039,"update_time":1634035085,"id":1666798419587,"updated_at":"2022-10-26T15:33:39.587000Z","created_at":"2022-10-26T15:33:39.587000Z"},{"_id":"635953538041c95dfb15eb04","body":"this is a SPIFFS error. Change your filesystem to LITTLEFS and you should be good to go.","issue_id":1660803919335,"origin_id":940887282,"user_origin_id":59926104,"create_time":1634035221,"update_time":1634035221,"id":1666798419590,"updated_at":"2022-10-26T15:33:39.589000Z","created_at":"2022-10-26T15:33:39.589000Z"},{"_id":"635953538041c95dfb15eb06","body":"Could you solve the problem? I am testing the query DELETE FROM idHist WHERE id > 0 AND IsReady = 0 ORDER BY id DESC LIMIT 10; but it does not recognize the ORDER. \r\nHow do I delete 10 rows?","issue_id":1660803919335,"origin_id":1130620664,"user_origin_id":72167339,"create_time":1652912380,"update_time":1652912380,"id":1666798419595,"updated_at":"2022-10-26T15:33:39.594000Z","created_at":"2022-10-26T15:33:39.594000Z"},{"_id":"635953538041c95dfb15eb07","body":"> Could you solve the problem? I am testing the query DELETE FROM idHist WHERE id > 0 AND IsReady = 0 ORDER BY id DESC LIMIT 10; but it does not recognize the ORDER. How do I delete 10 rows?\r\n\r\nDeleting 10 rows isn't done by using a limit this way so your syntax is incorrect.\r\nI'm not sure if ANSI SQL allows you to use LIMIT in a DELETE. \r\nAlso an ORDER in a DELETE statement is not possible.\r\nSQL allows you to use limit using SELECT FIRST or using LIMIT (depending on the SQL implementation).\r\n\r\nYou should be able to execute the SELECT limit, put the ID's in an array and walk through the array to delete the records or pass them as a static string in the statement so something like\r\n`DELETE FROM idHist WHERE id IN ( 1,2,3,4,5,6,7,8,9,10 ) AND IsReady = 0;`\r\n\r\nIt's best to check the SQL manual a bit and get a good understand of the statements.\r\n\r\nYou could also use [this](https:\/\/stackoverflow.com\/questions\/1824490\/how-do-you-enable-limit-for-delete-in-sqlite) method.","issue_id":1660803919335,"origin_id":1131262737,"user_origin_id":8679150,"create_time":1652941203,"update_time":1652941398,"id":1666798419598,"updated_at":"2022-10-26T15:33:39.597000Z","created_at":"2022-10-26T15:33:39.597000Z"}]
I have been attempting to TRUNCATE a table in SQLite on an ESP32-WROVER-B using the SPIFFS library. Since TRUNCATE doesn't exist (u The execution fails with an I/O error on...
Cannot create a new table in an existing database file on SD card
[{"_id":"6359592ccecf4e081a206f34","body":"Hi, can we try the same without these lines:\r\n```\r\n File myFile;\r\n myFile = SD.open(\"\/testdb.db\", FILE_WRITE);\r\n \/\/ close the file:\r\n myFile.close();\r\n```\r\nDoes the example work without changes?","issue_id":1660803919343,"origin_id":1120397975,"user_origin_id":12986780,"create_time":1652008194,"update_time":1652008194,"id":1666799916959,"updated_at":"2022-10-26T15:58:36.959000Z","created_at":"2022-10-26T15:58:36.959000Z"},{"_id":"6359592ccecf4e081a206f35","body":"I removed the code as you suggested, along with the file check, and added `while (!SD.begin()) delay(100);` to ensure that the SD was mounted in time, and I got the following: \r\n```\r\nOpened database successfully\r\ncreate table surnames(name TEXT)\r\nSQL error: unable to open database file\r\nTime taken:23407\r\nSelect * from surnames where name = 'MICHELLE'\r\nSQL error: no such table: surnames\r\nTime taken:3470\r\n```\r\n\r\nAfter reverting back to the original example and running that out of the box, i get `Can't open database: unable to open database file`\r\n\r\nif I wipe the `testdb.db` file from the sd card, and rerun my example, I get the same result as the first block (with nearly indistinguishable times)\r\n\r\nAfter that, upon checking the SD card, it seems that a file `TEST.DB` with no disk size has been made. Thoughts?","issue_id":1660803919343,"origin_id":1120464011,"user_origin_id":66704796,"create_time":1652033758,"update_time":1652033758,"id":1666799916963,"updated_at":"2022-10-26T15:58:36.962000Z","created_at":"2022-10-26T15:58:36.962000Z"},{"_id":"6359592ccecf4e081a206f36","body":"Maybe it will help, but here is the partitioning of the SD card in use: \r\n\r\n\r\nThe partition in use is a FAT file system, and I doubt the fact that it only is allotted 16\/32GB matters, since I have already run the demos provided by Arduino to verify that read and write both are working","issue_id":1660803919343,"origin_id":1120465086,"user_origin_id":66704796,"create_time":1652034177,"update_time":1652034177,"id":1666799916968,"updated_at":"2022-10-26T15:58:36.968000Z","created_at":"2022-10-26T15:58:36.968000Z"}]
I am following [the spi demo](https://github.com/siara-cc/esp32_arduino_sqlite3_lib/blob/master/examples/sqlite3_sdspi/sqlite3_sdspi.ino) and I have so far only modified the setup function ```c++ #include #include #include #include "SD.h" //many includes excluded for conciseness //provided functions excluded...
