SQLite Forum

Undefined symbols for architecture arm64:

Because you're trying to compile the amalgamation as a standalone executable, which it is not. It's intended to be built as an object file or a library. Though i don't know exactly what the conventions are on your OS, that generally looks something like:

clang -arch arm64 -arch x86_64 -c -o sqlite3.o sqlite3.c

The -c flag tells it to compile but not link. The -o flag tells it the output file name. You end up with an object file (the whole sqlite3 library) which you can then link together with your application's other object files.

If you're trying to build the shell, you'll have further steps to take, but someone familiar with the oddities of your platform will need to suggest what they are.

On my OS that looks something like:

[stephan@nuc:~/Downloads/sqlite-amalgamation-3370000]$ ls -la
total 9516
drwxrwxr-x 2 stephan stephan    4096 Nov 27 16:05 .
drwxr-xr-x 8 stephan stephan   12288 Dec  1 03:40 ..
-rw-rw-r-- 1 stephan stephan  704219 Nov 27 16:05 shell.c
-rw-rw-r-- 1 stephan stephan 8385624 Nov 27 16:05 sqlite3.c
-rw-rw-r-- 1 stephan stephan   35995 Nov 27 16:05 sqlite3ext.h
-rw-rw-r-- 1 stephan stephan  595850 Nov 27 16:05 sqlite3.h

[stephan@nuc:~/Downloads/sqlite-amalgamation-3370000]$ clang -c sqlite3.c
[stephan@nuc:~/Downloads/sqlite-amalgamation-3370000]$ clang -c shell.c
[stephan@nuc:~/Downloads/sqlite-amalgamation-3370000]$ clang -o sqlite3 sqlite3.o shell.o

/usr/bin/ld: sqlite3.o: in function `unixDlOpen':
sqlite3.c:(.text+0x662a): undefined reference to `dlopen'
/usr/bin/ld: sqlite3.o: in function `unixDlError':
sqlite3.c:(.text+0x6659): undefined reference to `dlerror'
/usr/bin/ld: sqlite3.o: in function `unixDlSym':
sqlite3.c:(.text+0x66aa): undefined reference to `dlsym'
/usr/bin/ld: sqlite3.o: in function `unixDlClose':
sqlite3.c:(.text+0x66f5): undefined reference to `dlclose'
/usr/bin/ld: sqlite3.o: in function `pthreadMutexAlloc':
sqlite3.c:(.text+0x1ba8d): undefined reference to `pthread_mutexattr_init'
/usr/bin/ld: sqlite3.c:(.text+0x1ba9e): undefined reference to `pthread_mutexattr_settype'
/usr/bin/ld: sqlite3.c:(.text+0x1baba): undefined reference to `pthread_mutexattr_destroy'
/usr/bin/ld: sqlite3.o: in function `pthreadMutexTry':
sqlite3.c:(.text+0x1bb81): undefined reference to `pthread_mutex_trylock'
/usr/bin/ld: sqlite3.o: in function `sqlite3ThreadJoin':
sqlite3.c:(.text+0x37a0c): undefined reference to `pthread_join'
/usr/bin/ld: sqlite3.o: in function `sqlite3ThreadCreate':
sqlite3.c:(.text+0x5859f): undefined reference to `pthread_create'
clang: error: linker command failed with exit code 1 (use -v to see invocation)

(Oooops - we need to link in some external dependencies)

[stephan@nuc:~/Downloads/sqlite-amalgamation-3370000]$ clang -o sqlite3 sqlite3.o shell.o -ldl -lpthread

[stephan@nuc:~/Downloads/sqlite-amalgamation-3370000]$ ./sqlite3 
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ^D

On your OS it will be something very close to that, if not identical.

clang++ -arch arm64 -arch x86_64 -o sqlite_build sqlite3.c

Use clang instead of clang++. sqlite3 is C and not all C constructs are legal in C++.

Related: all of the extern "C" blocks in sqlite3.c seem to be no-ops:

#if 0
extern "C" {
#endif

That's a strange (and seemingly useless?) way to do that. The more common convention is:

#ifdef __cplusplus
extern "C" {
#endif

I know I can list all the columns manually but then I will need to update the trigger every time I change scheme of the tables.

Don't forget that even if your desired syntax worked, you would still have to change your backup table even time the source table changes, otherwise the trigger would fail.

One way or the other, you have to account for those changes somewhere.

unixtime()

...

should the return value from "unixtime('2021-11-29 14:31:14.25')" be 1638196274 or 1638196274.25?

Just spitballing...

It "might be interesting" (or might not) to instead follow JavaScript's lead and always use ms precision, but to do so without a fractional part. In JS the above would be:

• 1638196274 ==> 1638196274000
• 1638196274.25 ==> 1638196274250

in which case maybe call it jstime() instead of unixtime(), or additionally offer unixtimems() (or unixtime('...', 1)) to specify that it should return ms precision in whatever format is decided upon (fractional or integer).

SELECT * FROM TABLE1, TABLE2; i'd get result ...

Those are not the results you'd get from that query. You'd get a number of results equal to the number of rows from table 1 multiplied by the number of rows from table 2:

sqlite> create temp table x(y);
sqlite> create temp table z(a);
sqlite> insert into x(y) values(1),(2),(3);
sqlite> insert into z(a) values(4),(5),(6);
sqlite> select * from x, z;
1,4
1,5
1,6
2,4
2,5
2,6
3,4
3,5
3,6

What you want is something like:

sqlite> select *, 'table x' from x union select *, 'table z' from z;
1,'table x'
2,'table x'
3,'table x'
4,'table z'
5,'table z'
6,'table z'

Is this intended behaviour?

Reread the docs for the prepare APIs. They only prepare single statements. Any statement after the first one is ignored.

but sqlite3 insert so slow because it is locking all over the table.

It locks the whole file, not individual tables:

https://www.sqlite.org/lockingv3.html

There aren't feature like row level locking like mysql InnoDB?

Nope. Note the "lite" part of the name.

Is this possible?

To add to what MBL said, see:

https://www.sqlite.org/vtab.html

in particular, there's a link at the bottom of section 1 to more virtual tables:

https://www.sqlite.org/vtablist.html

one of which may already do what you want or provide a good starting point.

SELECT * FROM Mesures WHERE NumLot IS 'lot1' ORDER BY 'Id' DESC LIMIT 10

Try changing 'Id' (a string literal) to Id or "Id" or [Id] (an identifier).

VALUES(); -- one row, zero columns

Those semantics are incompatible with how generic APIs which accept arbitrary SQL work: they prepare their SQL then ask if it has any result columns. If it does, it's a SELECT-ike statement (and therefore may generate output), otherwise it's not. Adding select-like statements which have no result columns would break all software which relies on that property.

Is there an issue tracker or is it enough to bring it up on the forum?

sqlite's ticket system is not open for anonymous submissions - the forum is the correct place to report problems.

The workaround for this issue, since verified by the OP, is given further up in this thread:

Try leaving out the -DSQLITE_OMIT_WAL.

The SQLite project does not publish any application which uses either ".msi" installation or exception-style error handling.

That particular API is maintained under this project's umbrella:

https://system.data.sqlite.org/

As you say, though, the OP's problem is certainly one of trying to write somewhere where a non-admin user is not permitted.

/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.

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.

For SQLite the integer can be up to 8 bytes and will casting as "INTEGER" solve the problem without overflow?

You can ask the foremost expert yourself: sqlite. If it behaves that way today you can be 99.99% certain it will behave that way in 10 years (the project places tremendous value on backwards compatibility).

sqlite> select cast(4011110000001 as integer);
4011110000001
sqlite> select cast(401111000000100 as integer);
401111000000100
sqlite> select cast(4011110000001000 as integer);
4011110000001000
sqlite> select cast(40111100000010000 as integer);
40111100000010000
sqlite> select cast(401111000000100000 as integer);
401111000000100000
sqlite> select cast(4011110000001000000 as integer);
4011110000001000000
sqlite> select cast(40111100000010000000 as integer);
9223372036854775807

I can't use "int" because of the length of the numbers. If I cast the query as "long", can you guarantee that all query ranges will run without errors?

sqlite does not distinguish between "long" and "int". See:

https://www.sqlite.org/datatype3.html

How do i submit a bug report

Such reports should be sent directly to the project lead, Richard Hipp:

https://sqlite.org/support.html

(/me begrudgingly dons his admin hat, at the risk of thereby becoming That Guy. TL;DR: jump to the last couple of paragraphs.)

It is still a "secure the building" problem.

That's a term i'm unfamiliar with, but according to this article about the "secure the build" problem:

Although this adage is, of course, a joke, it also serves as a cautionary tale about the importance of a strong and clear problem statement within successful acquisitions. To "secure the building" barely describes "what" is to be done and leaves out the other two critical elements, "why" and "how."

Applying "secure the building problem" to Scott's request, i can address...

1. "why": the operators of this forum wish for it to uphold a reputation of being professional, respectful, and "family-friendly" (when in doubt, following the US definition of the term, noting that it is more lax, sometimes considerably so, in many other regions).

2. "how": if the non-family-friendly language continues, your posts will be forced to go through moderation before approval, with instructions for the moderators to reject, rather than silently continue to edit, the forum-inappropriate language. (Noting that the forum does not provide an option for editing before approving, but that's a feature we can potentially address in Fossil.)

The article continues with:

A problem statement, as defined by Dr. Edward F. Crawley, Ford professor of engineering in the Massachusetts Institute of Technology's Engineering Systems Division, must include:

1. To… (enterprise or stakeholders' intent, or the "why" you are attacking the problem; what value are you trying to create?).

Addressed above.

2. By… (the "how," using solution-neutral verbs such as create, destroy, transport, transform, compare, etc… ).

Addressed above.

3. Using… (the "what," or statement of structure; this introduces cost).

Using the administrative-level tools of this forum and the authority granted to me to apply them in pursuit of point (1).

4. While… (detailing other important goals or constraints).

While respecting that you are an undeniably top-notch expert in this field with a stupendously tremendous[^1] amount of information to impart and are, for that reason, admitted some leeway in your "bedside manner" which would not be afforded to the proverbial mere mortals. That leeway, however, does not (or does not any longer) apply to non-family-friendly language.

FWIW, i think it's fair to say that everyone[^2] here recognizes the tremendous value of your continued participation and would like it to continue unfettered.

(/me removes his admin hat)

[^1] = "stupendously tremendous" is, in this case, an accurate characterization, not hyperbole.

[^2] = Okay, there's always someone in every group who's going to be contrary about any position ostensibly applying to "everyone," but we'll ignore them for this purpose.

If you reference a "name" in a context in which that use is ambiguous, you will get a message telling you that the "name" is ambiguous.

As an example, if I have I have a subquery that refers to a column that exists in several tables that are in the parent query including itself, which instance will be used?

None. The computer will inform you that the column reference is ambiguous, which is an error condition that will prevent the query from being able to be prepared/compiled.

How do I refer to the specific one I want?

You use a fully qualified name that is not ambiguous. A referent to a table column has the format [[<schema>.]<table>.]<colname> or [<table-alias>.]<colname>. You must specifically identify the column completely and unambiguously. Once you have done so, the query may be prepared/compiled without raising an ambiguity error.

Computers do not guess (unless they have been programmed to do so). SQLite3 is not an IBM PL/1 Level H compiler. It does not try to "guess what you meant", but will rather "do what you say". If you say something ambiguous then you will be informed of your error. If you say something which is syntactically valid (but semantically a bluster-duck) then you will get a bluster-ducking.

Mutatis mutandis for any other "name" which may be ambiguous when specified without sufficient qualifiers.

what is the string for opening/attaching a temporary database?

sqlite doesn't, to the best of my knowledge, have any APIs for managing temp databases. It has temp tables:

create temp table x ...;

Edit: that said, try "" instead of ":memory:". IIRC that works like memory but uses a temp database.

          WHEN OLD.rental_cost = NULL AND 
               NEW.date_back != NULL

NULL will never compare properly that way. Use IS NULL and IS NOT NULL to compare against NULL values.

Question: is the sqlite-archive not designated to work with bigger files?

My recollection is that the 2GB limit is currently baked into sqlite blobs. It's not an artificial limit of sqlar (fossil, based on sqlite, has the same limit).

I assume narg stands for the number of arguments; if so, how do I interpret -1?

Variadic: any number of args.

Am I right in concluding that directly mapping internal blobs to memory is not possible?

Blobs may span multiple db pages and thus not all bytes in them will necessarily reside in contiguous on-storage memory, making them incompatible with direct memory mapping.

Consider what would happen if they were mmapped and their records were deleted while you were using them (and then those db pages reused for other records).

That Way Lies Madness.

(And what should be done to make it possible?)

Donating such a machine to the project would be the first step.

Hey! I was reading SQLite's code just to get a feel for its B-tree implementation, when I noticed a weirdly worded piece of a comment. I presume it's simply missing a single word: "This can if" → "This can happen if". Here's the diff to fix it:

--- src/btree.c
+++ src/btree.c
@@ -8498,11 +8498,11 @@
 
 /*
 ** Return SQLITE_CORRUPT if any cursor other than pCur is currently valid
 ** on the same B-tree as pCur.
 **
-** This can if a database is corrupt with two or more SQL tables
+** This can happen if a database is corrupt with two or more SQL tables
 ** pointing to the same b-tree.  If an insert occurs on one SQL table
 ** and causes a BEFORE TRIGGER to do a secondary insert on the other SQL
 ** table linked to the same b-tree.  If the secondary insert causes a
 ** rebalance, that can change content out from under the cursor on the
 ** first SQL table, violating invariants on the first insert.

After I sell this app does the person who bought it have free access to redistribute /resell my code or do I have to place a public domain license on my app?

The license for your code is not affected by sqlite's license. You may license your code however you like. Lots and lots of proprietary commercial software includes its own copy of sqlite.

Hi guys, I am using VBA to connect to SQLite. I have installed ODBC driver 64 bits from http://www.ch-werner.de/sqliteodbc/ and checked that SQLite ODBC Driver exists in System DSN and User DSN in ODBC Data Source Admin app. Here is my VBA code:

Sub SQLiteADO()
    Dim con As ADODB.Connection, rs As ADODB.Recordset
    Dim s As String
    s = "Driver = {SQLite3 ODBC Driver}; Database = " & ThisWorkbook.Path & "\test.db;"
    Set con = New Connection
    con.Open s
    s = "SELECT * FROM Data"
    Set rs = New Recordset
    rs.Open s, con
    Range("A1").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    con.Close
    Set con = Nothing
End Sub

My problem is I cannot connect and receive error "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Please help me. Thank you.

CREATE TEMP TABLE hello ( SELECT ProductI...

sqlite> create temp table foo as select * from event limit 3;
sqlite> select * from foo;
'ci',2454303.0902662039734,1,NULL,NULL,NULL,NULL,'drh',NULL,'initial empty baseline',NULL,2454303.0902662039734
'ci',2454304.0000694449991,181,NULL,NULL,NULL,NULL,'drh',NULL,'Setup webpag updates.',NULL,2454304.0000694449991
'ci',2454303.3139583338051,182,NULL,NULL,NULL,NULL,'drh',NULL,'Improvements to web-based user management.',NULL,2454303.3139583338051

which is very much not possible currently

That is actually untrue. You can export symbols from an executable (such as the CLI). You do not actually need to name the "shared object" with a .dll or .so extension if you do not want to do so. You can call it "ThumpingGiraff.Eaters" and STILL have it be the CLI application and STILL export symbols for external use.

I suspect that what the OP is asking is whether the CLI has a "CREATE FUNCTION" command and a compiler. The answer is no. However, the CLI does "contain" sqlite3_create_function and calls it quite fine for defining functions such as date(), time(), datetime(), strftime(), sin(), cos(), etc, etc, as specified in the documentation.

Although you can type "sqlite3_create_function" at the CLI user interface, it will have no different effect than if you type that phrase into a word processor.

... gives a pretty bad smell on the site like doing malicious stuff or spying out the visitors...

It's an anti-bot measure attempting to stop bots from sucking up the site's bandwidth and CPU. (Nowadays most crawlers can run JS, so it's less effective than it used to be.)

Please see this document about the topic in sqlite's sister project, fossil:

https://fossil-scm.org/home/doc/trunk/www/javascript.md

Why all the double quotes? They seem to be in very strange places.

Formatting quirk. Tap the "source" link on the top post to see the original formatting.

I suspect the question you meant to ask is:

FWIW, i understood the OP as meaning: is there a set of -Dxxxx options they can provide which will filter the core sqlite lib out of the build, leaving only the desired extension(s) in the output object file.

That does sound like an interesting way to distribute extensions, with the caveat that those extensions would, de facto, be assumed to be compatible with the version of sqlite they're bundled with and may well not work with the hypothetical external sqlite copy the OP apparently already has.

Is the sqlite3_release_memory() function the public API interface?

If you can find documentation for it in the public interface then yes, else no.

What about sqlite3PcacheReleaseMemory()

The functions and data types with sqlite3CamelCaseNames are all internal, not for client-side use. They may be modified or removed by the sqlite3 developers at any time with no concerns for backwards compatibility (in strong contrast to the documented public APIs, which have strong backwards compatibility guarantees).

So whether the culprit was some system bug, AV bug or just memory crap piled up is not clear, but the AV interference sound a sensible option. Wonder if the lib/tool can be made more robust on that.

AV interference is not an uncommon problem on Windows. Libraries like sqlite have zero insight into what such a tool is doing and how it may be interfering. The "real" (if snarky) answer is to make the OS more robust so that it doesn't need AV running 24/7 ;).

Funny, quitted then reloaded the DB checked as per original post: last integrity_check got I/O error, now instead (started with -stats):

This sounds like a side effect of a virus scanner.

kod do diagramu

All questions about why a given query does or does not work "really should" include sample data. A diagram of the schema is not nearly as helpful as the data. Posting a question about a query without sample data makes it far more difficult for this group's volunteers to help you, which greatly reduces the chances of getting a useful answer.

Can You help me with what I am doing wrong

Without the data to accompany your query, all anyone can reasonably do is speculate.

that's on Windows 10 though [sqlite3 dll+tools SQLite 3.36.0 2021-06-18], whereas on (Debian, SQLite 3.16.2 2017-01-06) Linux the check yields just 'ok'.

Are you perchance accessing that db via a virtual filesystem mounted in the other OS? If so, i recommend eliminating that middleman and trying again. It seems highly unlikely that, on a local/native/non-virtual filesystem, you could get different results for bitwise-identical copies of a database file. It is never recommended to use sqlite db's across remote mounts, e.g. SMB or NFS, or virtualization layers which abstract a host OS's storage to a guest OS. That Way Lies Madness.

Sorry, You just gave me the answer.

Note that the formulations from David and Donald are simpler and should be preferred.

WHERE (gender = 'F') OR (gender = 'M' AND date_joined >= '2020-01-01')

Since the db schema allows for NULL genders, this slight tweak seems appropriate:

WHERE (gender = 'F') OR (gender IS NOT 'F' AND date_joined >= '2020-01-01')

(Noting that IS NOT is required instead of <>.)

I am not asking how the code works.

That's precisely what you asked for before: "plain language" explanation of the code.

I am asking why it makes sense when length(sqlar.blob)==sqlar.sz, there is no compression, when length(sqlar.blob)<sqlar.sz there is compression?

My previous response answers that. sqlar will never produce compressed data which is the same size or larger than the original. If compression (including the header) would be the same size or larger than the original, the compressed results are discarded and the original data is used in its place.

I don't follow the code. Could you explain it in plain language how it determines whether it is compressed or not in the case that I mentioned?

In the very last block it checks whether the compressed data is smaller than the original. If so, it keeps the compressed data, else the compression is discarded and the original is retained.

so if anyone can give me some clues about the causes of that returning value -1

Your array indexes for values[...] are wrong, so the final one is passing invalid memory to bind. Array index start at 0, not 1. Bind indexes, on the other hand, start at 1. Whether that is the problem, i don't know, but it's certainly a problem. (Apologies for the brevity, but am working from a tablet.)

I need to know which are the causes of the returnining value -1.

For anyone here to be able to speculate on that, you'll need to demonstrate the exact code which is failing for you, not a summary of it.

pls close this topic.

This software doesn't have a way of closing topics. The closest we can do is stop responding to it (starting right after this response), noting that someone else may come along in a year, find it relevant to them, and "reopen" it.

Wouldn't it be nice and simple to have a '.attach' feature in the CLI ?

We do, it's just missing the dot prefix:

sqlite> attach database 'foo.db' as 'blah';

More succinctly:

sqlite> attach 'foo.db' as 'blah';

That's only 1 letter longer than your proposal: it removes the "." and adds an "as".