SQLite Forum

Thanks for confirming.

On my SD card the permissions were RWXRWX--- but on my emulated storage its rw rw ---

That's a decent clue that Stephan's "noexec" tip is pertinent.

I selected the 20 FE phone specifically for the SD card so I could do this project without rooting my phone.

The "cannot root phone" constraint is not one likely to be catered to.

I'm on the verge of giving up on SQLite even though I really really want it to work!!

If wishes were fishes, ...

What makes you think this has anything to do with SQLite, other than that it is ultimately executed (like anything else running) and your environment is blocking execution?

You should go to an Android development forum to get advice on your problem. You have a platform issue rather than a SQLite issue.

Based on the phrasing, it seems what is wanted is a "wait forever" that semantically says what is being sought vs "wait for a really long but finite time". Something that in code would be immediately obvious as the difference between "wait until something is available" and "wait until something is available or the timer elapses".

To that end, defining a custom busy handler would be the way to do that as described.

It says generic hierarchical.

What if I format the SD card?

I tried navigating to

cd /tmp but I got invalid directory.

I have no idea of any other "nonFAT" places. How can I solve it?

---- update

I tried reformatting but android didn't offer me any alternative formats.

As an aside,

I moved my files to the storage/emulated/0/test directory and there was a difference.

On my SD card the permissions were RWXRWX--- but on my emulated storage its rw rw ---

I selected the 20 FE phone specifically for the SD card so I could do this project without rooting my phone.

I'm on the verge of giving up on SQLite even though I really really want it to work!!

Any last ideas?

Define your own busy handler which does whatever you want:

https://sqlite.org/c3ref/busy_handler.html

You can, of course, copy the default one and modify it for your own purposes.

However, along with Ivan, I suspect something weird is going on if a pause of 24 days is not enough for your purposes. It might be to your advantage to tell us what you're really trying to do.

/storage/.../sqlite3 can't execute: Permission denied

That SD card is very likely formatted with the FAT filesystem and linux distributions will mount such filesystems with the "noexec" option, making it impossible to run executables. Copy the sqlite3 binary to /tmp (or some other non-FAT place) and run it from there.

Curious, why do you need that? It is possible to get an example of an actual use-case?

thank you so much for this excellent reply!

Some specifics of my project: I'm not using andriod studio. I'm working with Macrodroid and shell command.

So, instead of the andriod binaries, I downloaded Precompiled Binaries for Linux and was able to move the sqlite file to my sdcard

I then called it in my shell like:

/storage/2EA5-2D26/Xenno/sqlite /storage/2EA5-2D26/Xenno/zztest.db

the result I got was:

/system/bin/sh: <stdin>[25] /storage/.../sqlite3 can't execute: Permission denied

I then used the LS command and the sqlite3 file is set to rwxrwx---

I tried using chmod o=rwx /storage/2EA5-2D26/Xenno/sqlite but I get no result

I've spent so many hours on this. Any help is much appreciated. If only I could make sqlqueries!? it'd be a whole other world!

I am doing nothing special for the compilation. I just added the 3 files that were the wrapper for SQLite to my project as normal. Then I added winsqlite3.dll and .lib files to the project also. Everything compiles with no errors. I did not no about the MACRO, so I will check that out. While waiting for an answer I was messing around with the project and I did something (don't know what) but now the open function works. I can only guess that I fixed the project scope with the database file because that was the only thing I was changing. Strange!!! I am happy now.

The file is in my home folder for the project hence the lack of any path on the file. SQLite says the full path is not necessary so I did not use it. The reason I dumped the code that it was not working. I have used the wrapper previously in another project that was written in MFC and it worked flawlessly. I always try to use things that work, but in this case I was following the example on sqlite.org first, and it did not work. I moved on because this has been so frustratingly hard to get to work.

On WinRT, it's fairly unlikely that the processes current directory is writable. You'll need to explicitly specify one of the writable directories.

Also, how are you compiling the SQLite library?

I don't see any mention of setting the SQLITE_OS_WINRT macro that's necessary for SQLite to function on WinRT.

Is there any way to configure busy_timeout so that SQLite waits indefinitely?

I know I can set the timeout to INT32_MAX milliseconds, which is roughly equal to 24.5 days of waiting. But I'm wondering if there is a more semantically direct way of saying "wait forever".

It seems odd to me that you abandon code with simple, identified errors in favor of courting new ones. Leaving that aside ...

Also leaving aside the wisdom of throwing an exception for failure to .open() ...

What is the absolute path which your "db.sqlite3" filename resolves to? Or, what is the process current directory when the code you now have is run? Is the directory in which you try to create a database file one which you can modify without unusual permissions?

As part of diagnosing your trouble, I suggest setting m_szFile to name a file in a a directory you can create files in. It should contain the whole path, starting from a filesystem root. That way, the process current directory will not matter.

Update - I dumped all the code above and tried a different scheme. I used a wrapper for SQLite3 from https://github.com/neosmart/CppSQLite and used the same .lib and .dll files. Everything compiles and I get a different error, which is 14 or SQLITE_CANTOPEN. I suspected that was the problem. Why it was one off? Here is the code.

CppSQLite3DB m_dB; const char* m_szFile = "db.sqlite3"; const char* m_szFilesTable = "Files";

void UI_Database::InitializeDatabase(void) { bool fileTableExists = false; const char* ver; int verNumb = 0;

ver = CppSQLite3DB::SQLiteHeaderVersion();
ver = CppSQLite3DB::SQLiteLibraryVersion();
verNumb = CppSQLite3DB::SQLiteLibraryVersionNumber();

try
{
    // Open the database.
    m_dB.open(m_szFile);

    // Check if database contains the 'Files' table.
    fileTableExists = m_dB.tableExists(m_szFilesTable);

    if (fileTableExists == false)
    {
    }
}
catch (CppSQLite3Exception& e)
{
    // Show the exception.
    ver = e.errorMessage();
}

}

Everything works except for getting an exception when open on the database is called. It returns SQLITE_CANTOPEN. I think it is getting blocked from opening by WinRT, but I don't know how to give it access. I just want to confirm my suspicions. Thanks.

Hello!

Documentation for SQLITE_LOCKED says that it can happen either because of a conflict inside the current connection, or because of someone who uses the same shared cache. Similarly, documentation for transactions states (emphasis mine):

If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with SQLITE_BUSY.

However, I managed to run into a situation where an attempt to upgrade to a write transaction results in SQLITE_LOCKED rather than SQLITE_BUSY.

To reproduce this, we'll need two SQLite CLI tools running in the same directory (I changed their prompts to clarify which is which).

First, we initialize a database in WAL mode:

first> .open test.db
first> pragma journal_mode='wal';
wal
first> create table test (id integer primary key, note text);
first> insert into test(note) values ('hello'), ('world');

We open the same database in the second shell:

second> .open test.db

Back in the first shell, we start a transaction which obtains a read lock:

first> begin transaction;
first> select * from test;
1|hello
2|world

In the second shell, we modify the table:

second> insert into test(note) values ('foo');

Now in the first one, we try to modify the table as well, but get SQLITE_LOCKED:

first> update test set note = 'bar' where note = 'hello';
Error: database is locked

This was a bit surprising, and the docs didn't clarify the situation because they insist that SQLITE_LOCKED can only happen because of shared cache or something on the same connection ­— neither of which applies to the scenario described above.

SQLite's behaviour is correct though. SQLITE_BUSY would imply that I can fix the first shell by waiting a bit, but this clearly won't work; I have to restart the transaction to get an up-to-date view of the database, hence SQLITE_LOCKED.

It'd be nice if docs for SQLITE_LOCKED and transactions mentioned this possibility.

(As usual with FLOSS, I only get a chance to talk to developers when something isn't perfect, but: thank you very much for creating, maintaining, and sharing SQLite with the rest of the world!)

Perhaps the CLI application can be modified to display the extended error code as well as the text. ...

This seems like a good idea to me, and fairly simple to implement.
(Edit resulting from discussion with dev team:)
Although it's a feature change, it is trivial enough to be included in v3.37 (unless my perception of triviality is way off.)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

(Correction via edit regarding silence upon stepping error:)

At present, any error returned from sqlite3_step() is treated as the end of stepping, then reported like others. Given the potential locking issues raised in this thread, indicating this also seems worthwhile.

Thanks... I will copy the db, install several database engines and try to import the db into those engines to see which one recognizes it... thanks a lot for your time and patience :-)

You do not need "file" or me to tell you that is not the beginning of a SQLite3 database file.

0 0 0 0 0 0 0 0 M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R 0 357 R 0 0 0 0 0 0 0 0 0 0 0 0 0 0 357 R 0 0 0 0 0 ...

In other words, it is of type "Unrecognized". The "file" utility has known of SQLite databases for awhile now. But you need not rely on it. The first 16 bytes of a SQLite3 database are as stated earlier in this thread. What does 'od -c your.db | head' say?

The object known as "m_dB" does not have a value which can sensibly be passed to sqlite3_open(). This is a fundamental problem, unrelated to Win10 permissions.

(Added via edit:) Also, your use of sqlite3_temp_directory represents a memory leak.

Hi, thanks. It said it is of type "data"

You're totally right. My bad, I wasn't attentive enough; I just read "database is locked" and assumed it's SQLITE_LOCKED, even though my program reported the error code of 5 (which is SQLITE_BUSY).

So sorry to waste your time with this; you even took the pain of reproducing it in yet another way, with Python — thanks for that!

I did so and can read text/words does make sense, but there was no "SQLite format 3" text on the head...

Does this mean that 1) the db is an sqlite db (why could I not get any .table nor .schema? it says file is not a database of it is encrypted 2) the db is not an sqlite db but its encoding could be read with "strings myfile.db | head"?

1. What would you recommend me to get the schema of this db and manage to read it?
2. Can I dump somehow these strings to a kind of csv textfile?

Thanks, I did so and then typed ".tables" and nothing appeared... does this mean, does the db is not an sqlite db? (because I now for certain, that the db is not empty)

noting that 3.7 is now more than 8 years old and will be missing all sorts of more current features.

Yeah. A simpler solution would be to download and use the latest 3.36.0 CLI.

No ".open" option in version 3.7.17 2013-05-20

...

How can I open then the db?

You pass the db name to sqlite3:

$ sqlite3 /path/to/the/db.file

noting that 3.7 is now more than 8 years old and will be missing all sorts of more current features.

Hi, I am trying to open a db in my server directory but the command ".open" is not recognized. When I type ".help", the ".open" is not listed... How can I open then the db? Thanks!

Perhaps the CLI application can be modified to display the extended error code as well as the text. It is displayed in some places, but not in others.

Instead of printing the error message as:

Error: <message text>

could it perhaps be displayed as:

Error: <message text> (<extended error code>)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

That is instead of the CLI printing the message Error: database is locked in the above circumstance, perhaps the CLI could print the message Error during Step: database is locked (517).

This would make the CLI more useful as a debugging tool.

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be upgraded -- which case applies is up to the programmer to determine).

** NB: I have MonkeyPatched APSW to have added methods such as .begin, .execute, and .commit to the apsw Connection class **

Are you sure that the error code is not SQLITE_BUSY_SNAPSHOT, just being simplificated for display to the user by the application?

I have a query that uses WHERE id IN (1,2,3,...) where the list (1,2,3,...) is dynamically generated from an array of integers (not using parameters). Now I have a particular query that takes roughly 500ms with 26623 ids but 50s (100x slower) with 26624 ids. In a different scenario the jump in query time happens between 3071 and 3072 IDs.

This happens in Node.js via better-sqlite3 (with its default compile options https://github.com/JoshuaWise/better-sqlite3/blob/23c56aa77e44689e56c56370345e3c3cc7895128/docs/compilation.md#bundled-configuration) with SQLite 3.36.0.

This query uses a custom virtual table and I'm not sure how to reproduce it without it and if it is part of the issue. This means I can't easily provide steps to reproduce it with vanilla SQLite. But I will provide as much information as I can.

The database contains normalized HTTP traffic. What the query does is get all unique URL parameters with a list of unique values. The search_params virtual table parses a query string and yields all name/value pairs.

This query takes 500ms:

SELECT params.name AS name, json_group_array(DISTINCT params.value) AS "values"
FROM view_requests AS req, search_params(search) AS params
JOIN flows ON flows.request_id = req.id
WHERE flows.id IN (1,2,3,...,26623)
GROUP BY params.name
ORDER BY json_array_length("values") DESC, params.name ASC

If I change the range of IDs up to 26624 it takes 50s. This on its own is already interesting. I must be running into some limitation and maybe it starts paging or something?

Here's what I also found, which is even more interesting. If I make search_params(search) constant, the jump happens from 3071 to 3072 (500x from 10ms to 5s).

SELECT params.name AS name, json_group_array(DISTINCT params.value) AS "values"
FROM view_requests AS req, search_params('?foo=bar') AS params
JOIN flows ON flows.request_id = req.id
WHERE flows.id IN (1,2,3,...,3071)
GROUP BY params.name
ORDER BY json_array_length("values") DESC, params.name ASC

Without the IN condition (running the query on all 31k flows) it takes roughly as long as with 26623 flows.

Now my workaround is to use json_each like this:

SELECT params.name AS name, json_group_array(DISTINCT params.value) AS "values"
FROM view_requests AS req, search_params(search) AS params
JOIN flows ON flows.request_id = req.id
WHERE flows.id IN (SELECT value FROM json_each('[1,2,3,...,26623]'))
GROUP BY params.name
ORDER BY json_array_length("values") DESC, params.name ASC

Which is just as fast but seems to scale effortlessly (and as a bonus can make use of parameters for any number of IDs).

EXPLAIN returns 80k and 9k rows respectively (that sounds like a lot). With the json_each implementation it's like 70 (not k, just 70). Maybe I'm running into problems with the generic virtual table implementation of xBestIndex or xFilter (https://github.com/JoshuaWise/better-sqlite3/blob/master/src/util/custom-table.lzz)? But why would that have any influence on how flows.id is queried?

You can download the EXPLAIN results here:

• https://drive.protonmail.com/urls/MP56BT56F0#S0VWMC1hD29I
• https://drive.protonmail.com/urls/QHZV7KWHV0#Yf0DvjP97B2A
• https://drive.protonmail.com/urls/8VZEQ3JXFR#5P6EEle67dCF
• https://drive.protonmail.com/urls/5B3D7M3WS8#5xib2GAKU03f

There's no problem with storing a SQLite database on an SD card. You need all privs for card access, including the privs needed to create new files, and to change file attributes.

You don't really 'install' SQLite. SQLite is an API, a programmer's library, a set of functions your program can call. You build it into your program. If you don't want to call Android's own library

https://developer.android.com/training/data-storage/sqlite

then download the amalgamation source code

https://sqlite.org/download.html

add it to your project, and call that. You can find precompiled binaries for Android on the same download page.

Hi I hit so Many brick walls trying to make my own database on android without using android studio.

I just want to make calls to the db using shell on my SD card.

Can I install sqlite on my SD card and put the db in the same directory and have it run there?

Please help. Burned up so many hours trying to get this working

As you say, it is just a doc issue. The discrepancy is now corrected. Thanks.

Hi, We recently saw SQLite error code regarding queries being too large. On checking the error code page, the docs mention limit as 1,000,000 (https://www.sqlite.org/rescode.html#toobig)

However the actual limit is 1,000,000,000 as documented here: https://www.sqlite.org/limits.html#max_sql_length, and in the code

https://sqlite.org/src/file?name=src/sqliteLimit.h&ci=trunk

/*
** The maximum length of a single SQL statement in bytes.
**
** It used to be the case that setting this value to zero would
** turn the limit off.  That is no longer true.  It is not possible
** to turn this limit off.
*/
#ifndef SQLITE_MAX_SQL_LENGTH
# define SQLITE_MAX_SQL_LENGTH 1000000000
#endif

Wanted to confirm that this indeed is just a documentation issue, and I'm not misreading the default value.

Regards, Kanishka