Get SQLITE_READONLY when using UPDATE command
(1) By anonymous on 2021-11-02 21:19:56 [link]
Hi, I am using UWP C++/WinRT in VS 2019 along with winsqlite3.h, .lib, and .dll files provided by MS. The OS is Win10 and the SDK number is 10.0.19041.0 which is just a little behind the latest. The SQLite3 version is SQLITE_VERSION "3.29.0". From what I have read, it seems to be a permissions problem. I made sure my user has permissions on the database file, and all folders in the path to the file, but since it is in the Public Users folder, I have a problem with it being some folder permission. I could be wrong. I seem to have a vague memory of reading somewhere that WinRT needs a list of files that it can modify. The database file is in the same folder with all my source code. I may change that later, but for now it is fine where it is at, unless that violates some rule I am not aware of. I will go check if my memory was right after this post. Other than that, I have no other ideas to try. I welcome any other ideas or things to try. Thanks.
(2) By Keith Medcalf (kmedcalf) on 2021-11-02 22:17:42 in reply to 1 [link]
What is the extended error code?
(3) By anonymous on 2021-11-02 22:55:29 in reply to 2 [link]
result of the sqlite3_open() was 0. result of the sqlite3_exec() was 8. sqlite3() errorMsg = "attempt to write a readonly database" result of sqlite3_errcode() was 8. result of sqlite3_extended_errcode() was 8. Here is my query string: "update Users set Username='hidden', Password='hidden' where Id='1';" Not sure what the problem is. I can't seem to decipher the extended code. Thanks.
(4) By Larry Brasfield (larrybr) on 2021-11-02 23:13:41 in reply to 3
I think it's time to show your sqlite3_open() call. It would help rule out a whole category of problems if you were to temporarily substitute for your DB filespec a pathname which you are quite certain can be written by whatever user is getting your unexpected SQLITE_READONLY return. If that continues through the substitution, we can focus on your call. Otherwise, the investigation needs to examine file permissions.
(5) By Simon Slavin (slavin) on 2021-11-03 03:44:15 in reply to 1 [link]
Do you get the same problem if you do an INSERT or DELETE FROM ? Please be aware that if you try to make a change to a database, SQLite creates a new file in the same folder as the database. Which means in your case that you will need write permissions for the folder your source code is in. Supplying a read-only database inside your project is often done and works fine. Giving your application write permissions to itself represents a horrible security problem and tempts malware. The standard way to start with a SQLite database containing empty tables and indexes, or pre-prepared data, is to include that database file with the project and have your program duplicate it to a folder where it has read/write permissions.
(6) By anonymous on 2021-11-03 14:45:55 in reply to 4 [link]
Here is some code I have before I ever call sqlite3_open(). It is to show you the pieces I have or don't have. database.h file: extern sqlite3* ptrMain_Db; SQLITE_EXTERN char* sqlite3_temp_directory; const char* m_Main_Db_File ; database.cpp file:(in constructor) m_Main_Db_File = "file:\\Users\\David\\test.db"; // Assert the database versions. assert(sqlite3_libversion_number() == SQLITE_VERSION_NUMBER); assert(strncmp(sqlite3_sourceid(), SQLITE_SOURCE_ID, 80) == 0); assert(strcmp(sqlite3_libversion(), SQLITE_VERSION) == 0); // Make the sqlite3 temp directory. LPCWSTR zPath = ApplicationData::Current().TemporaryFolder().Path().data(); char zPathBuf[MAX_PATH + 1]; memset(zPathBuf, 0, sizeof(zPathBuf)); WideCharToMultiByte(CP_UTF8, 0, zPath, -1, zPathBuf, sizeof(zPathBuf), NULL, NULL); sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf); This is how I use sqlite3_open(). int result = sqlite3_open(m_Main_Db_File, &ptrMain_Db); I moved the database file to my user folder which I should have full permission rights to. Now I get a hex E from result. I tried all the possible ways for my file string. Without the file:, single back slashes which Windows did not like, and forward slashes. I got the same result, E. This is different from when the database was in my project folder. In VS I have installed SQLite/SQL Server Compact Toolbox. From this too I can always access and modify any SQLite database. This is how I have been making changes to the database so far. Is this tool causing my problems? Thanks.
(7) By Larry Brasfield (larrybr) on 2021-11-03 16:07:51 in reply to 6 [link]
(Quoting code fragments as context for response fragments:) > ``` database.cpp file:(in constructor) m_Main_Db_File = "file:\\Users\\David\\test.db"; ``` It appears that you intend to use a URI database specification. This one is in good order. I prefer (single) forward slashes, as Windows has accepted them since the 16-bit codebase became history. > ``` // Make the sqlite3 temp directory. LPCWSTR zPath = ApplicationData::Current().TemporaryFolder().Path().data(); char zPathBuf[MAX_PATH + 1]; memset(zPathBuf, 0, sizeof(zPathBuf)); WideCharToMultiByte(CP_UTF8, 0, zPath, -1, zPathBuf, sizeof(zPathBuf), NULL, NULL); sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf); ``` If your DB file will be in a writable directory, there is no need (evident here to me) for setting a different temp directory. It's a complication at best. > ``` int result = sqlite3_open(m_Main_Db_File, &ptrMain_Db); ``` You need to use [sqlite3_open_v2()](https://sqlite.org/c3ref/open.html) with a SQLITE_OPEN_URI flag. > I moved the database file to my user folder which I should have full permission rights to. Now I get a hex E from result. Getting a **different** result should be considered progress. That return is also known as [SQLITE_CANTOPEN](https://sqlite.org/rescode.html#cantopen). As the linked doc indicates, the open failure could be either the main DB file or one of the potential auxiliary files. Eliminating the sqlite3_temp_directory set would help distinguish cases here. > Without the file:, single back slashes which Windows did not like, and forward slashes. It is not Windows which disliked single backslashes. (It never saw them.) You should study what C/C++ string literal escaping conventions mean and do. A double-backslash becomes a single backslash in the compiled literal. > This is different from when the database was in my project folder. Yes, and with a little more information that will be a potent clue, I think. At this point, I suspect your problem is that Windows, when given file:\\Users\\David\\test.db as a pathname, refuses to create a file object under that name. Telling SQLite to treat that text as a URI will keep the "file:" part from being passed to the OS file open API. > In VS I have installed SQLite/SQL Server Compact Toolbox. From this too I can always access and modify any SQLite database. This is how I have been making changes to the database so far. Is this tool causing my problems? I was unaware that the tool could deal with SQLite databases. (I'm skeptical that it can.) But if you are not keeping a SQLite database open in that tool, I highly doubt it will lead to the problem(s) you see now.
(8) By anonymous on 2021-11-03 16:34:55 in reply to 7 [link]
I will try all the things you have suggested. I do wonder if my problem is mostly caused by not using sqlite3_open_v2(). I will try it, but may have questions if this solves the problem. As for the double \\. For strings that contain a single \, it must be escaped or when printed, a string like "i\t" would be printed as i followed by a tab. I don't think this is the problem. I do understand this, I just stated it poorly. But I did discover why when I moved the database to my user folder, it did not work. First, I did not have the file Included in the project, and second, it was not marked as Content for its properties in VS. Once I did both of those I did not get the E when I opened the database. I got a 0. So I will make the temp folder my project folder, and try using sqlite3_open_v2() and see where that gets me. Thanks. I will post my results, good or bad.
(9) By Keith Medcalf (kmedcalf) on 2021-11-03 17:53:41 in reply to 7 [link]
The "proper" URL would be "file://Users/David/test.db" which would be the file `\Users\David\test.db` on the `current drive` (ie, the current directory location when the program is loaded. The format specified is FUBAR. The filename you used, unless URI filenames are enabled, would name the "database" as the alternate data stream called `\Users\David\test.db` on the file called `file` in the current direcvtory. This is probablyu not what you want. You should specify the filename properly. Example: `C:\\Users\\David\\test.db` or `c:/users/david/test.db`. THis is standard DOS/Windows since about 1979.
(10.1) By Keith Medcalf (kmedcalf) on 2021-11-03 18:12:18 edited from 10.0 in reply to 6 [link]Deleted
(11) By Larry Brasfield (larrybr) on 2021-11-03 18:18:15 in reply to 10.0 [link]
> > Now I get a hex E from result. > You mean you get an error:<br> <br> (15) SQLITE_PROTOCOL That would be "hex F". > Try specifying a proper filename in proper filename format and see if that helps (ie, forget about using the URI format, use the CPM/DOS/Windows filename format standard that has existed unchanged since at least 1979 if not earlier. The OP's' filename will be fine when either stripped of its leading "file:" or when passed with the SQLITE_URI flag in sqlite3_open_v2(). The '\\'-as-path-separator convention was misbegotten from the beginning. It is no longer necessary to use it instead of '/' for Win32 APIs, and has not been since Win16 was obsoleted nearly 2 decades ago. (FWIW, the backslash convention was hotly contested with Microsoft, but imposed by IBM as MS-DOS was made ready for use in PC's, evolved from Tim Paterson's 86-DOS.)
(12) By Keith Medcalf (kmedcalf) on 2021-11-03 18:22:22 in reply to 11 [link]
Actually, `/` was accepted as a path separator since DOS 1.1
(13) By Larry Brasfield (larrybr) on 2021-11-03 18:22:22 in reply to 9 [link]
I pretty much agree, except for: > The filename you used, unless URI filenames are enabled, would name the "database" as the alternate data stream called ... I don't think that stream names (whether alternate or not) can contain path separators. I've not tested this, but it would be pernicious if permitted.
(14) By Larry Brasfield (larrybr) on 2021-11-03 18:23:46 in reply to 12 [link]
Interesting. I expect that was the Unix-experienced faction's work.
(15) By RandomCoder on 2021-11-03 18:35:02 in reply to 13 [link]
Correct, stream names have the same rules for allowed characters as filenames on NTFS. So, `<>:"/\|?*` are not allowed in either.
(16) By Keith Medcalf (kmedcalf) on 2021-11-03 18:38:37 in reply to 14 [link]
Actually that is incorrect. Directory organization (rather than simply files in the root) appeared in DOS 2.0, not DOS 1.1, so the variable path separator was available since DOS 2.1 or perhaps some patch version of DOS between 2.0 and 2.1.
(17) By anonymous on 2021-11-03 18:44:21 in reply to 9 [link]
I forgot to show you how I declared my database objects. sqlite3* ptrMain_Db = nullptr; char* sqlite3_temp_directory; Here are my changes. sqlite3_temp_directory = "C:\Users\David" Database file path = "file://Users/David/test.db" (m_Main_Db_File) open function is now. result = sqlite3_open_v2(m_Main_Db_File, &ptrMain_Db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL); and now I get a result of 1.
(18) By anonymous on 2021-11-03 18:49:19 in reply to 11 [link]
Just so you know, I was getting a hex E from the IDE when I hovered over the result of the function. The function returns an int which means the value was 14. It was just easier to type an E. The error was SQLITE_CANTOPEN.
(19) By Larry Brasfield (larrybr) on 2021-11-03 18:51:37 in reply to 9 [link]
> The "proper" URL would be "file://Users/David/test.db" which would be the file \\Users\\David\\test.db on the current drive (ie, the current directory location when the program is loaded. > The format specified is FUBAR. SQLite does not require the double-slash between the URI's scheme part and the remainder. However, per my reading of <u>[RFC 3986](https://xml2rfc.tools.ietf.org/public/rfc/html/rfc3986.html#components)</u>, it should.<sup><b>1</b></sup> So, in that sense, the OP's URL is improper and other adjectives may apply. I'm not sure how this deviation arose or should be handled now given the backwards compatibility issues. ---- 1. This deviation creates an ambiguity between absolute and relative pathnames. SQLite interprets a leading/only slash after the scheme as the beginning of an absolute path. To get a relative path, no leading slash should be used.
(20) By anonymous on 2021-11-03 20:05:57 in reply to 16 [link]
I think I have solved the problem. It is a WinRT problem with how SQLite writes a file. See this url - https://www.codeproject.com/Articles/879846/Using-SQLite-Databases-in-Any-Folder-in-WinRT This explains what the problem is and gives a solution to writing database files anywhere using VFS in SQLite. I am going to try putting my database in the app's local storage folder and see if it works. I bet it does. Thanks and I will post how this works out for me.
(21) By anonymous on 2021-11-03 20:57:49 in reply to 16 [link]
The problem is solved. I was able to create the database, read from it, and write back to it in the App Data local stare folder where MS recommends it be stored.
(22.1) By Scott Robison (casaderobison) on 2021-11-03 21:00:27 edited from 22.0 in reply to 12 [link]
Forward slash was supported by the DOS API starting with version 2 of DOS, as previous versions of DOS did not support hierarchical directories (as Keith noted in a subsequent post). IBMs desire to have forward slash serve as the command line parameter switch character starting with DOS 1.whatever meant that you could type things like: ``` A>DIR/W ``` And it would be recognized as a DIR command followed by no white space and command arguments. As the customer, IBM did not want DOS 2 to break that convention that many people had some to expect, and allowing forward slash as a path separator would add ambiguity to parsing of the command line: does `DIR/W` mean show a wide directory or does it mean run the W program in the subdirectory named DIR? So yes, the engineers at MS knew of the convention that forward slash was a path separator, but they were enhancing a system based on CP/M conventions and IBM imposed requirements. It's always been an issue from the command line, but never from the API from the time DOS began supporting hierarchical directories.
(23) By tom (younique) on 2022-06-06 17:02:38 in reply to 12 [link]
> Actually, / was accepted as a path separator since DOS 1.1 Where you got this idea from? You can do that if using 4DOS, but Microsoft's command.com doesn't accept this. Just tested on MS-DOS 6.2: `> cd /tools `Invalid switch - /tools On DOS, the slash has *always* been used to start a command parameter.
(24) By John Dennis (jdennis) on 2022-06-07 07:11:09 in reply to 23 [link]
Interesting and curious. The forward slash works fine in a Command Prompt window in Win10 for some folders... d:\>cd /temp d:\temp> but not all: d:\>cd /df The system cannot find the path specified. d:\>cd \df d:\df>
(25) By jchd (jchd18) on 2022-06-07 10:44:36 in reply to 24 [link]
That's because the CD command will accept option /D if provided. This is a bug/misfeature of command.exe in (at least) Win10. It's unable to distinguish /d* as a target folder and option /d. Powershell has no such problem with cd /df or /d.
(26) By John Dennis (jdennis) on 2022-06-08 03:13:21 in reply to 25 [link]
That makes sense. Thanks