SQLite Forum

Timeline
Login

50 events by user stephan occurring around 2021-10-26 19:41:30.

2021-11-30
02:59 Reply: In a trigger insert all columns of NEW into another table artifact: 804ce4dba6 user: stephan

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.

2021-11-29
14:46 Reply: Proposed new date/time function to return a unix timestamp artifact: ca60b9bb1b user: stephan

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

2021-11-26
14:13 Delete reply: Sqlite3 doesn't support row level locking? artifact: 49432ca87f user: stephan
Deleted
2021-11-25
14:19 Reply: Multiply table select with table identification artifact: 19334d06f5 user: stephan

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'
2021-11-24
11:09 Reply: sqlite3_bind_parameter_count question artifact: 1360a1c401 user: stephan

Is this intended behaviour?

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

2021-11-23
09:48 Reply: Sqlite3 doesn't support row level locking? artifact: 984e611e95 user: stephan

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.

2021-11-20
14:31 Reply: A flat-file interface for SQLite? artifact: d5e87cfb61 user: stephan

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.

2021-11-18
09:46 Reply: DESC keyword is ignored artifact: 1d22ecd759 user: stephan

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

2021-11-05
20:07 Reply: 'values' with no args : has to be syntax error? artifact: a04d5cff0f user: stephan

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.

2021-11-02
13:49 Reply: v3.36.0 fails to compile under Ubuntu artifact: 631b84e2a4 user: stephan

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.

2021-10-29
13:08 Reply: Insert operation leads to exception on Windows when installed using msi artifact: ef087693de user: stephan

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.

2021-10-26
19:41 Reply: Database on android SD CARD artifact: 65a6731d78 user: stephan

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

10:00 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: c7e03274d8 user: stephan

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.

2021-10-22
07:04 Reply: Inconsistency in BETWEEN Query artifact: 83f15a9a51 user: stephan

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
06:50 Reply: Inconsistency in BETWEEN Query artifact: 5530f0cb55 user: stephan

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

2021-10-20
02:00 Reply: How do i submit a bug report artifact: 09083e25e5 user: stephan

How do i submit a bug report

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

https://sqlite.org/support.html

2021-10-18
07:25 Reply: Column names and aliases in expressions artifact: 4f1f5ff3f9 user: stephan

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

02:07 Edit reply: Column names and aliases in expressions artifact: 39c8dd277f user: stephan

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.

2021-10-15
09:35 Edit reply: Temp database artifact: f0656854b2 user: stephan

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.

09:34 Reply: Temp database artifact: 007162c61d user: stephan

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 ...;
08:51 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price artifact: 1188776048 user: stephan
          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.

2021-10-14
15:06 Reply: sqlite .archive can't handle big files? artifact: e64e94a17a user: stephan

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

14:59 Reply: Pragma_Function_List() artifact: bba5790bc0 user: stephan

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

Variadic: any number of args.

2021-10-08
08:22 Reply: mmap and blobs artifact: 9fd55bf6e6 user: stephan

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.

2021-10-07
15:20 Reply: x86_64/amd64 sqlite3 binaries artifact: 554fd8a358 user: stephan

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

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

2021-10-05
22:20 Edit: a function description is missing a word artifact: 218c116fdf user: stephan

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.
2021-10-04
02:43 Reply: can i use this for commercial use? artifact: 89695777f7 user: stephan

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.

2021-09-30
14:47 Edit: MicrosoftODBC Driver Manager Data source name not found and no default driver specified error when use VBA connect Sqlite artifact: 34ebdaecba user: stephan

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.

04:18 Reply: A little help with temp tables? artifact: 767d002e9a user: stephan

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
2021-09-29
02:21 Edit reply: User-defined functions in the CLI? artifact: 207e7218f5 user: stephan

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.

2021-09-16
15:13 Reply: Javascript enforcement artifact: 3ff7c0ff2f user: stephan

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

2021-09-09
06:38 Reply: How to insert duplicate rows? artifact: e245ee0aaf user: stephan

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.

2021-08-27
03:31 Reply: Amalgamation file for specific extension artifact: ca01212759 user: stephan

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.

2021-08-23
21:50 Reply: Explanation about C code style artifact: ba114d586c user: stephan

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

2021-08-13
16:40 Reply: pragma integrity_check; Error: disk I/O error artifact: a6800d75ce user: stephan

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 ;).

2021-08-12
22:21 Reply: pragma integrity_check; Error: disk I/O error artifact: 03711ff569 user: stephan

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.

17:16 Reply: LEFT AND RIGHT OUTER JOINS problem artifact: 4e0bcf17bb user: stephan

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.

16:57 Reply: LEFT AND RIGHT OUTER JOINS problem artifact: 20f02095cf user: stephan

Can You help me with what I am doing wrong

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

15:03 Reply: pragma integrity_check; Error: disk I/O error artifact: f411dde9d6 user: stephan

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.

13:59 Reply: SQLite irrespective query artifact: d28ee10891 user: stephan

Sorry, You just gave me the answer.

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

13:37 Reply: SQLite irrespective query artifact: 17ba85c53d user: stephan

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

2021-08-10
04:18 Edit reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed artifact: bd8bfe928c user: stephan

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.

04:17 Reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed artifact: 8c9bdcbc11 user: stephan

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 would be the same size or larger than the original, the compressed results are discarded and the original data is used in its place.

2021-08-09
11:38 Reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed artifact: c303dcd528 user: stephan

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.

2021-08-04
18:13 Reply: C api sqlite3_db_readonly returns -1 artifact: d72290462d user: stephan

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

16:30 Reply: C api sqlite3_db_readonly returns -1 artifact: 7ec8091f32 user: stephan

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.

2021-08-02
02:27 Reply: Which version of SQLite supports VxWorks DKM mode ? artifact: 90d4346581 user: stephan

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.

2021-08-01
10:46 Reply: (Deleted) artifact: e257826a25 user: stephan

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

2021-07-23
21:29 Edit reply: sqlite3 LIKE clause with parameters for c api artifact: 9cc83fb7a8 user: stephan

"SELECT ... FROM ... WHERE col LIKE '%%'+?1+'%%';"

In SQL the string concatenation operator is ||, so:

SELECT ... FROM WHERE col LIKE '%' || ?1 || '%'

Whether or not the % signs need to be doubled depends on the exact API you're passing the string to, but sqlite3_prepare_v2() does not expand/process percent signs so do not double them.

20:59 Reply: sqlite3 LIKE clause with parameters for c api artifact: 9a7993e907 user: stephan

"SELECT ... FROM ... WHERE col LIKE '%%'+?1+'%%';"

In SQL the string concatenation operator is ||, so:

SELECT ... FROM WHERE col LIKE '%' || ?1 || '%'

Whether or not the % signs need to be doubled depends on the exact API you're passing the string to, but sqlite3_prepare_v2() does expand/process percent signs so do not double them.

More ↓