SQLite Forum

Timeline
Login

50 most recent forum posts by user stephan

2022-01-27
15:57 Reply: Error in UPDATE FROM examples? "GROUP BY 2)"? (artifact: e5c8b2dabd user: stephan)

Or maybe it is some sort of syntax I don't understand?

It means to group by the 2nd column. That syntax also works for ORDER BY.

2022-01-25
14:13 Edit reply: Case when (artifact: 4d55778ec9 user: stephan)
Hey everyone,

First, I just looked at my last message and it looks like the SQL did not paste nicely, so I apologize for the confusion!!

Second, I think I got it to work! 

Here is my SQL (I double-checked it this time):

select CASE WHEN substr(`accounting period`, 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr(`accounting period`, 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr(`accounting period`, 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr(`accounting period`, 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END||'-'|| substr(`accounting period`,7,4)||'.csv' AS Filename from B


This gave me the following result (which is exactly what I was looking for):

Q3-2021.csv




Thank you all so much for your time and effort! Clearly I am still trying to learn SQL and all the nuances that come with it but this was extremely helpful for my work and beneficial for my growth! So thank you again!
11:47 Reply: Stale data returned on subsequent select query (artifact: 8f84f7b062 user: stephan)

I can reproduce this on other laptops.

But it cannot be reproduced in other programming environments, most notably the sqlite C library. The issue is in the technology stack you are using, somewhere between sqlite and your code. The authors of the JDBC driver are using are your best bet in terms of finding a solution.

10:19 Reply: Stale data returned on subsequent select query (artifact: 8085420f03 user: stephan)

Tried with setting Autocommit and with explicit coo=nnection.commit() Still takes 1 sec to reflect the changes.

That's a property/misfeature of your environment. This project is only responsible for the C-level sqlite3 library and its immediate partners (namely the sqlite3 CLI client and the dot-net sqlite3 wrapper), and the C library certainly does not exhibit the behavior you're describing. All Java wrappers for sqlite are maintained by third parties not represented in this forum. My recommendation is to get in touch with the Hikari folks.

2022-01-24
20:08 Reply: Case when (artifact: fe2220fb0b user: stephan)

month ac ac ac ac ac ac ac ac ac

The implication of that would seem to be that the column is not really called "accounting period" in the database. You have not yet shown a schema demonstrating that that's the name sqlite is seeing.

14:30 Edit reply: >= and <= (or between potentially) query (artifact: 891641e7ee user: stephan)
Still up the creek with this..

Data looks like this..
Prefix_Start Prefix_End  Country_Name
A2A 	     A2Z	Botswana (Republic of)
A3A	     A3Z	Tonga (Kingdom of)
A4A	     A4Z	Oman (Sultanate of)
A5A	     A5Z	Bhutan (Kingdom of)
A6A	     A6Z	United Arab Emirates
A7A	     A7Z	Qatar (State of)
A8A	     A8Z	Liberia (Republic of)
A9A	     A9Z	Bahrain (Kingdom of)

I have A4A as the input.... so I want to retrieve "Oman"..
2022-01-20
08:26 Reply: Developer (artifact: e396c8ab52 user: stephan)

See (no pun intended) this overview of the SEE license:

It's been brought to my attention that that page requires logging in, so here's the relevant snippet:

For an enterprise license, the source code can be used by anyone with an email address for the enterprise.

And the link to the price confirmation:

https://sqlite.org/purchase/see

04:52 Edit reply: Developer (artifact: a3ba498a5f user: stephan)

Please confirm SQLite Encryption Extension (SEE) costs US $2000.00 is enterprise license and product is used by multiple teams and transferable.

See (no pun intended) this overview of the SEE license:

https://www.sqlite.org/see/file?name=LICENSE.md&ci=tip

Point 2 on that page answers your 2nd question and a hyperlink in the top section links to a page which answers your first question.

04:52 Edit reply: Developer (artifact: 6951b01169 user: stephan)

Please confirm SQLite Encryption Extension (SEE) costs US $2000.00 is enterprise license and product is used by multiple teams and transferable.

See this overview of the SEE license:

https://www.sqlite.org/see/file?name=LICENSE.md&ci=tip

Point 2 on that page answers your 2nd question and a hyperlink in the top section links to a page which answers your first question.

04:52 Reply: Developer (artifact: aa9ebe0654 user: stephan)

Please confirm SQLite Encryption Extension (SEE) costs US $2000.00 is enterprise license and product is used by multiple teams and transferable.

This overview of the SEE license:

https://www.sqlite.org/see/file?name=LICENSE.md&ci=tip

Point 2 on that page answers your 2nd question and a hyperlink in the top section links to a page which answers your first question.

2022-01-18
14:29 Reply: WAL with Read Only Access (artifact: c40f929da7 user: stephan)

But in the Read Only mode we are completely sure that there is no following write transactions so why we need to create markers in WAL file?

Because a different client may open the db file in write mode. sqlite can't know that your connection will be the only one.

2022-01-16
13:25 Reply: Windows 3.37.3 sqlite3.exe .schema output includes creation of sqlite_ tables in error (presumably) (artifact: 699e1b8cfe user: stephan)

These prevent importing the .schema into a new DB.

There's a flag for that:

sqlite> .help .schema
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
   Options:
      --indent             Try to pretty-print the schema
      --nosys              Omit objects whose names start with "sqlite_"
2022-01-15
10:15 Edit reply: www3.sqlite.org out of sync (artifact: 1399271c6b user: stephan)

server did not reply

@Richard this is exactly what i was seeing when althttpSd is started from xinetd in TLS mode. IIRC you set up althttpd that way yesterday on www3, so that may be the culprit.

Edit: citation = https://sqlite.org/althttpd/forumpost/f58bb40d99ca62b2

10:13 Reply: www3.sqlite.org out of sync (artifact: 948ff6da1a user: stephan)

server did not reply

@Richard this is exactly what i was seeing when althttpSd is started from xinetd in TLS mode. IIRC you set up althttpd that way yesterday on www3, so that may be the culprit.

2022-01-12
06:52 Reply: Unicode Input/output in sqlite3.exe on Windows 10 (artifact: 9a66ff8f76 user: stephan)

Who knows how can I submit an issue to the official repo?

The preferred outlet for public problem reports is this forum. Arbitrary users are not permitted to open tickets in the main sqlite repo.

2022-01-11
00:18 Reply: RETURNING clause on prepared statement not behaving like un-prepared (artifact: 5c2d6bb402 user: stephan)

Using prepared statement BEGIN TRANSACTIONl INSERT INTO MyTable (my_pk,my_value) VALUES (?,?) ON CONFLICT(my_key) DO UPDATE SET my_value=? RETURNING my_pk; END TRANSACTION;

Just to be clear: you do understand that a prepared statement only executes the FIRST SQL statement in the code it is given, correct? That would explain why:

The return code (rc) of sqlite3_step() is "SQLITE_DONE" and Not "SQLITE_ROW".

The correct result of a "BEGIN" statement via sqlite3_step() is SQLITE_DONE.

2022-01-09
08:46 Reply: Proposed JSON enhancements. (artifact: 7652996583 user: stephan)

Additionally, I should think that JSON functions should not be included by default; they should be available as an option in the amalgamation and as a file that can be compiled separately if desired. (However, the -> and ->> syntax should be always included in the core SQLite, so that the extension will work.)

You've just brought up a good argument against the new syntax, perhaps inadvertently: since they new syntax can be overridden with user-defined, functions, users moving from one sqlite3 app to another might have misconceptions about what those operators actually do. Much has been said about compatibility with PG and MySQL, and yet allowing the new syntax to be bound to UDFs means that the new syntax isn't even necessarily compatible within two different sqlite3 connections.

2022-01-07
23:44 Reply: How do you determine if a row/column is holding a DATETIME value (artifact: e3e02df660 user: stephan)

If I have a column of type INTEGER or REAL, how do I determine if the value returned from a query represents a datetime (in time since epoch, julian value, etc.) that should to be converted to something that looks like a datetime with one of the built-in functions OR if it is just some random number that should be treated as an int or double?

Without knowing the human-applied intent of the schema, which your reply says isn't the case, you cannot possibly generically know whether any given number is intended to be a timestamp or not.

2022-01-05
17:29 Reply: config.h include is missing (artifact: 61e23ba463 user: stephan)

I used config.h in the same directory to configure sqlite because we use multiple build systems. Don't ask why. But it seams that the include is now out commented.

It sounds like you're looking for the new SQLITE_CUSTOM_INCLUDE: /forumpost/9f431ed4c1f6e82

2022-01-03
21:18 Reply: strings separated by ";" convert to columns (artifact: 1730430b3c user: stephan)

Could you tell me the correct extension?

The message you responded to when you wrote:

Thanks for the reply, but it gives the message that this function does not exist in sqlite

Has a link to the extension, its C file, and instructions for how to load it.

2022-01-01
10:39 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: 69ba806358 user: stephan)

For completeness's sake:

The plot thickens: doing the exact same operations via the exact same library via a script binding, instead of directly in C, works:

It turns out that my script-binding demo succeeds, despite me leaving off the CREATE flag, because the binding internally (for whatever reason) rewrites the open-flags to automatically include CREATE if the db name is ":memory:" or "". i have no idea why it does that but i wrote it, so it must have made sense at the time (6 or 8 years ago).

10:16 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: 76963f0026 user: stephan)

It appears that until something is done to a main DB to get it past "about to really exist" state, ...

Culprit found: it seems that the SQLITE_OPEN_CREATE bit is relevant. Here's an adaption of your example which works without the need for the initial CREATE TABLE:

#include <stdio.h>
#include "sqlite3.h"


int main( int na, char *av[] ){
  sqlite3 *db=0;
  char *zErr=0;
  char const * zDb = 0
      ? "/home/stephan/fossil/fossil.fsl"
      : ":memory:";
  int const oflags = SQLITE_OPEN_READWRITE
      | (0 ? 0 : SQLITE_OPEN_CREATE);
  int rc = sqlite3_open_v2(zDb, &db, oflags , NULL);
  if( SQLITE_OK==rc ){
    /* Next line merits SB's particular attention: */
#if 0
    rc = sqlite3_exec(db, "CREATE TABLE main.Junk(useless, stuff)",
                      NULL, NULL, &zErr);
    if( SQLITE_OK!=rc ) fprintf(stderr, "CREATE main. failed (%d)\n", rc);
#endif
    rc = sqlite3_exec(db, "ATTACH 'x.sdb' as x", NULL, NULL, &zErr);
    if( SQLITE_OK!=rc ) fprintf(stderr, "ATTACH failed (%d)\n", rc);
    rc = sqlite3_exec(db, "CREATE TABLE x.Junk(useless, stuff)",
                      NULL, NULL, &zErr);
    if( SQLITE_OK!=rc ) fprintf(stderr, "CREATE x. failed (%d)\n", rc);
    sqlite3_close(db);
}
[stephan@nuc:~/tmp]$ gcc -I~/fossil/sqlite3 -o lb lb.c ~/fossil/sqlite3/sqlite3.c -ldl -lpthread
[stephan@nuc:~/tmp]$ rm -f x.sdb; ./lb
# no output

Changing the oflags to:

  int const oflags = SQLITE_OPEN_READWRITE
      | (1 ? 0 : SQLITE_OPEN_CREATE);

leads to:

[stephan@nuc:~/tmp]$ gcc -I~/fossil/sqlite3 -o lb lb.c ~/fossil/sqlite3/sqlite3.c -ldl -lpthread
[stephan@nuc:~/tmp]$ rm -f x.sdb; ./lb
ATTACH failed (14)
CREATE x. failed (1)

i guess it makes sense that the CREATE flag would propagate to ATTACH, and that same change resolves my own test app's failure, but i really don't want to go opening all dbs in my lib with the CREATE flag.

Well, at least it's something i can work with/around (just requires manually checking for the file first in cases which should not be auto-CREATEd).

10:06 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: 4b9d95762e user: stephan)

Then I added the line marked for your attention. It appears that until something is done to a main DB to get it past "about to really exist" state...

That is interesting, but this failure also happens when using a real, live db with lots of contents instead of an empty :memory: db:

MARKER: f-apps/f-sanity.c:2446:test_db_attach_thingie2():	Opening db: /home/stephan/fossil/fossil.fsl
# ^^^^^ that's a clone of the main fossil repository
MARKER: f-apps/f-sanity.c:2432:sq3TraceV2():	trace: ATTACH '_thingie' as thingie;
MARKER: f-apps/f-sanity.c:2465:test_db_attach_thingie2():	db error: 14 unable to open database: _thingie

Indeed, with your example, it works if i do a create table before attach, but fails if the initial create table is elided. That feels like a bug, especially considering that it fails in the same way when using a populated db file instead of :memory::

  char const * zDb = 1
      ? "/home/stephan/fossil/fossil.fsl"
      : ":memory:";
  int rc = sqlite3_open_v2(zDb, &db,
                           SQLITE_OPEN_READWRITE/*|SQLITE_OPEN_CREATE*/, NULL);
...

Same failure.

3.35.5 and 3.36 behave the same.

My head-scratching continues.

09:00 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: dfefb74a29 user: stephan)

... approach to debugging would replace:

i actually already did that, shortly after the initial post. The buffer thing was a remnant of older test code. The current one looks like:

static void test_db_attach_thingie2(void){
  char const *zRepoDb = ":memory:";
  char const *zNewDb = "_thingie";
  sqlite3 * db = 0;
  int rc;
  char *zErr = 0;

  assert(zRepoDb);
  MARKER(("Opening db: %s\n", zRepoDb));
  fsl_file_unlink(zNewDb);
  rc = sqlite3_open_v2(zRepoDb, &db, SQLITE_OPEN_READWRITE, NULL);
#define RC if(rc) goto dberr
  RC;
  sqlite3_extended_result_codes(db, 1);
  sqlite3_trace_v2(db, SQLITE_TRACE_STMT, sq3TraceV2, NULL);
  rc = sqlite3_exec(db, "ATTACH '_thingie' as thingie;", NULL, NULL, &zErr);
  RC;
  assert(fsl_is_file(zNewDb));
  rc = sqlite3_exec(db, "DETACH thingie", NULL, NULL, &zErr);
  RC;

  assert(fsl_is_file(zNewDb));
  sqlite3_close(db);
  fsl_file_unlink(zNewDb);
  return;
  dberr:
  {
    MARKER(("db error: %d %s\n", rc, zErr));
    sqlite3_free(zErr);
    fsl_file_unlink(zNewDb);
    assert(!"failed");
  }
  return;
#undef RC
}

where, apart from the name-change and an added semicolon, the second thingie is single-quoted. Could that be an issue?

i've tried all variations: identifiers vs strings, with/without semicolons (shouldn't/doesn't make a different, but i'm at that point of confoundedness where anything which comes to mind seems worth trying).

i'm utterly stupefied and can only assume that there is a big fat piece of PEBKAC to be found in here somewhere.

08:50 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: d732d4fec0 user: stephan)

i've been banging my head for hours over this...

The plot thickens: doing the exact same operations via the exact same library via a script binding, instead of directly in C, works:

First, the C (shown in an earlier post):

[stephan@nuc:~/fossil/libfossil/f-apps]$ ./f-sanity -1
MARKER: f-apps/f-sanity.c:2443:test_db_attach_thingie2():	Opening db: :memory:
MARKER: f-apps/f-sanity.c:2432:sq3TraceV2():	trace: ATTACH '_thingie' as thingie;
MARKER: f-apps/f-sanity.c:2462:test_db_attach_thingie2():	db error: 14 unable to open database: _thingie
f-sanity: f-apps/f-sanity.c:2465: void test_db_attach_thingie2(void): Assertion `!"failed"' failed.
Aborted (core dumped)

# And then the script binding...

[stephan@nuc:~/fossil/libfossil/f-apps]$ ../bindings/s2/f-s2sh.bin -v
s2sh2> const d = new Fossil.Db(":memory:", "rwT")
result: Db@0x13d1c70[scope=#1 ref#=1] ==> Db@0x13d1c70
s2sh2> d.exec("attach '_thingie' as thingie")
SQL TRACE #1: attach '_thingie' as thingie
result: Db@0x13d1c70[scope=#1 ref#=1] ==> Db@0x13d1c70
s2sh2> ^D

[stephan@nuc:~/fossil/libfossil/f-apps]$ l _thingie 
-rw-r--r-- 1 stephan stephan 0 Jan  1 09:42 _thingie

Same OS-level user (and same real-world user!), same directory, same sqlite3.c and sqlite3.h linked in...

i'm utterly dumbfounded as to what's going on here. It's got to be something completely silly.

07:52 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: 9a4162c76d user: stephan)

It looks like you said you're checking it, but your code shows you checking the errors from sqlite_open_v2(), not the sqlite3_exec() on ATTACH.

Note the "RC" macro invoked after each exec(). That's doing the error check and doing a goto dberr if rc!=0.

What does sqlite3_extended_errcode() return when the ATTACH fails ?

My understanding is that that call should not be necessary because of:

sqlite3_extended_result_codes(db, 1);

done immediately after the sqlite3_open_v2() call result is confirmed.

In the larger project where this was initially encountered, i've tried both with and without that call, and sqlite3_extended_errcode() returns 14 (SQLITE_CANTOPEN) in both cases (which somewhat surprises me).

i failed to mention earlier: sqlite3 3.37.

02:58 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: daca01de81 user: stephan)

Does your program have permission to create new files in that folder ? Try making it create a new text file in the same folder.

All this is under my home dir, and the sqlite3 shell demo was done in the same dir as my test app. Permissions issues were the first thing i ruled out, including trying the same test in multiple dirs with multiple main dbs (temp, :memory:, and existing real db files). In all cases, an ATTACH from my code cannot create a new, empty file, whereas the sqlite3 shell, running the exact same code (copy/pasted from the sqlite3_trace() output) creates new/empty files.

00:53 Edit: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: 7e28b5b845 user: stephan)

i've been banging my head for hours over this... literally since last year ;)!

  • When running (ATTACH '_x' as x) from the sqlite3 shell, file _x will be created if it does not already exist.
  • Doing the same thing from my own C code invariably results in SQLITE_CANTOPEN (with no extended result code info) and i have no idea why.

First, the sqlite3 shell:

$ rm _x
$ echo "ATTACH '_x' as x" | sqlite3
$ ls -la _x
-rw-r--r-- 1 stephan stephan 0 Jan  1 01:40 _x

Exactly as expected. However, my own code behaves differently. i have finally narrowed it down to a mostly standalone reproducible case. It unfortunately requires more framework-level code than can be easily pasted in here, but this will give the gist of what's happening and "should" rule out any possibility that this is a PEBKAC error...

static int sq3TraceV2(unsigned t,void*c,void*p,void*x){
  if(t || c ||p){/*unused*/}
  MARKER(("trace: %s\n", (char const *)x));
  return 0;
}

static void test_db_attach_thingie2(void){
  char const *zRepoDb = ":memory:";
  char const *zNewDb = "_thingie";
  sqlite3 * db = 0;
  int rc;
  char *zErr = 0;
  fsl_buffer buf = fsl_buffer_empty;

  MARKER(("Opening db: %s\n", zRepoDb));
  fsl_file_unlink(zNewDb);
  rc = sqlite3_open_v2(zRepoDb, &db, SQLITE_OPEN_READWRITE, NULL);
#define RC if(rc) goto dberr
  RC;
  sqlite3_extended_result_codes(db, 1);
  sqlite3_trace_v2(db, SQLITE_TRACE_STMT, sq3TraceV2, NULL);
  fsl_buffer_appendf(&buf, "ATTACH %Q as 'thingie';", zNewDb);
  rc = sqlite3_exec(db, fsl_buffer_cstr(&buf), NULL, NULL, &zErr);
  RC;

  assert(fsl_is_file(zNewDb));
  rc = sqlite3_exec(db, "DETACH thingie", NULL, NULL, &zErr);
  RC;

  assert(fsl_is_file(zNewDb));
  sqlite3_close(db);
  fsl_file_unlink(zNewDb);
  fsl_buffer_clear(&buf);
  return;
  dberr:
  {
    MARKER(("db error: %d %s\n", rc, zErr));
    sqlite3_free(zErr);
    fsl_file_unlink(zNewDb);
    assert(!"failed");
  }
  return;
#undef RC
}

When run, it fails at the ATTACH:

$ ./f-sanity -1
MARKER: f-apps/f-sanity.c:2447:test_db_attach_thingie2():	Opening db: :memory:
MARKER: f-apps/f-sanity.c:2430:sq3TraceV2():	trace: ATTACH '_thingie' as 'thingie';
MARKER: f-apps/f-sanity.c:2472:test_db_attach_thingie2():	db error: 14 unable to open database: _thingie
f-sanity: f-apps/f-sanity.c:2475: void test_db_attach_thingie2(void): Assertion `!"failed"' failed.
Aborted (core dumped)

Error code 14 is SQLITE_CANTOPEN.

If i first create an empty file named _thingie and remove the first unlink() call in the test then the test runs to completion:

$ ./f-sanity -1
MARKER: f-apps/f-sanity.c:2442:test_db_attach_thingie2():	Opening db: :memory:
MARKER: f-apps/f-sanity.c:2430:sq3TraceV2():	trace: ATTACH '_thingie' as 'thingie';
MARKER: f-apps/f-sanity.c:2430:sq3TraceV2():	trace: DETACH thingie

So my questions are:

  1. Why on Earth can the sqlite3 shell create new files with ATTACH but my own code cannot?

  2. What must be done for my code to be able to do that as well?

00:49 Post: My code's ATTACH will not create a new db but sqlite3's shell will? (artifact: d5ac4dc797 user: stephan)

i've been banging my head for hours over this... literally since last year ;)!

  • When running (ATTACH '_x' as x) from the sqlite3 shell, file _x will be created if it does not already exist.
  • Doing the same thing from my own C code invariably results in SQLITE_CANTOPEN (with no extended result code info) and i have no idea why.

First, the sqlite3 shell:

$ rm _x
$ echo "ATTACH '_x' as x" | sqlite3
$ ls -la _x
-rw-r--r-- 1 stephan stephan 0 Jan  1 01:40 _x

Exactly as expected. However, my own code behaves differently. i have finally narrowed it down to a mostly standalone reproducible case. It unfortunately requires more framework-level code than can be easily pasted in here, but this will give the gist of what's happening and "should" rule out any possibility that this is a PEBKAC error...

static int sq3TraceV2(unsigned t,void*c,void*p,void*x){
  if(t || c ||p){/*unused*/}
  MARKER(("trace: %s\n", (char const *)x));
  return 0;
}

static void test_db_attach_thingie2(void){
  char const *zRepoDb = ":memory:";
  char const *zNewDb = "_thingie";
  sqlite3 * db = 0;
  int rc;
  char *zErr = 0;
  fsl_buffer buf = fsl_buffer_empty;

  MARKER(("Opening db: %s\n", zRepoDb));
  fsl_file_unlink(zNewDb);
  rc = sqlite3_open_v2(zRepoDb, &db, SQLITE_OPEN_READWRITE, NULL);
#define RC if(rc) goto dberr
  RC;
  sqlite3_extended_result_codes(db, 1);
  sqlite3_trace_v2(db, SQLITE_TRACE_STMT, sq3TraceV2, NULL);
  fsl_buffer_appendf(&buf, "ATTACH %Q as 'thingie';", zNewDb);
  rc = sqlite3_exec(db, fsl_buffer_cstr(&buf), NULL, NULL, &zErr);
  RC;

  assert(fsl_is_file(zNewDb));
  rc = sqlite3_exec(db, "DETACH thingie", NULL, NULL, &zErr);
  RC;

  assert(fsl_is_file(zNewDb));
  sqlite3_close(db);
  fsl_file_unlink(zNewDb);
  fsl_buffer_clear(&buf);
  return;
  dberr:
  {
    MARKER(("db error: %d %s\n", rc, zErr));
    sqlite3_free(zErr);
    fsl_file_unlink(zNewDb);
    assert(!"failed");
  }
  return;
#undef RC
}

When run, it fails at the ATTACH:

$ ./f-sanity -1
MARKER: f-apps/f-sanity.c:2447:test_db_attach_thingie2():	Opening db: /home/stephan/fossil/libfossil.fossil
MARKER: f-apps/f-sanity.c:2430:sq3TraceV2():	trace: ATTACH '_thingie' as 'thingie';
MARKER: f-apps/f-sanity.c:2472:test_db_attach_thingie2():	db error: 14 unable to open database: _thingie
f-sanity: f-apps/f-sanity.c:2475: void test_db_attach_thingie2(void): Assertion `!"failed"' failed.
Aborted (core dumped)

Error code 14 is SQLITE_CANTOPEN.

If i first create an empty file named _thingie and remove the first unlink() call in the test then the test runs to completion:

$ ./f-sanity -1
MARKER: f-apps/f-sanity.c:2442:test_db_attach_thingie2():	Opening db: :memory:
MARKER: f-apps/f-sanity.c:2430:sq3TraceV2():	trace: ATTACH '_thingie' as 'thingie';
MARKER: f-apps/f-sanity.c:2430:sq3TraceV2():	trace: DETACH thingie

So my questions are:

  1. Why on Earth can the sqlite3 shell create new files with ATTACH but my own code cannot?

  2. What must be done for my code to be able to do that as well?

2021-12-28
14:33 Reply: DATE type (artifact: 8894083a8b user: stephan)

What am I missing?

That sqlite has no DATE type:

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

The conventional way to store dates as strings in a sensibly sortable manner is to store them in ISO-8601 format.

13:09 Reply: Serious error emptied my complete database! (artifact: dc7175d71b user: stephan)

I don't know where you are reading "my attitude",

That's a misunderstanding. He was proactively warning you that his response might be construed as having "attitude," in order to avoid that...

you're responses make me feel as being attacked

2021-12-10
07:55 Reply: STRICT is keyword? (artifact: 325c38f99c user: stephan)

create table REAL (x); works as expected.

Larry meant "real" keywords in the sense of "genuine" keywords, as opposed semi-/pseudo-keywords. He was not referring to the "real" numeric type.

2021-12-07
09:19 Reply: How to skip intermediate data of DB in file? (artifact: a4263b2660 user: stephan)

Is any possibility to stop the stream of SQL-operations, to write accumulated data of DB into output file, clear memory and then to continue main stream of SQL-write commands?

That's entirely up to your application which imports the SQL data. Import it in blocks of 10k rows or 10M rows or whatever you need.

If you're using the sqlite3 shell application (as opposed to the library) to do the import, break your input files into chunks of a size appropriate for your systems. On Unix-like systems, the split CLI command can be used to split files into X number of bytes or lines. On non-Unix-like systems (i.e. Windows), you'll have to provide your own solution.

2021-12-04
00:15 Reply: How to use SQLITE_CUSTOM_INCLUDE in configure (artifact: a47c8e9445 user: stephan)

How do you tell configure to add this to the generated Makefile?

The custom include is not intended to be added to the sqlite-generated makefile. It's intended to be used to customize a local build of the sqlite3.c amalgamation, like so:

$ echo '#warning "foo"' > foo.h
$ gcc -c sqlite3.c -DSQLITE_CUSTOM_INCLUDE=foo.h
In file included from sqlite3.c:298:
foo.h:1:2: warning: #warning "foo" [-Wcpp]
    1 | #warning "foo"
      |  ^~~~~~~
2021-12-03
03:38 Post: Journal mode for "" (temp) db cannot be set to WAL? (artifact: 2e7f015000 user: stephan)
$ sqlite3
SQLite version 3.35.3 2021-03-20 01:00:26
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach "" as foo;
sqlite> pragma foo.journal_mode;
delete
sqlite> pragma foo.journal_mode=WAL;
delete

Question 1: is it not possible to change such a db to WAL mode? Obviously, the WAL journal would be lost when the db is closed, because it's a temp file, but my goal here is not cross-session durability but gaining only the locking benefits of WAL.

Question 2: assuming the answer is "no", is there another way to get similar locking benefits?

Background:

My library has to open several databases and the order they're opened is not set in stone - it depends very much on how the library is used. To avoid certain vagaries of connection-juggling, it initializes itself with a temp db (opened with the name "") to act as its MAIN db and then ATTACHes the others on demand.

It was recently discovered that that leads to certain locking problems, however. e.g. trying to do a DROP TABLE X, where X is a TEMP table, fails in certain cases because cached read-only statements are opened on that connection. The hypothesis is that if the main DB's journal mode can be changed to WAL, that problem will disappear. However, it seems impossible to change the journal mode to WAL.

The docs recommend against changing it to MEMORY or OFF because of the potential for corruption, but my suspicion is that for a temp db like this one, OFF would be completely fine, so long as the journal mode for the attached DBs is WAL/DELETE. The potential lack of atomicity across the ATTACHed DBs in the case of a crash is not critical in this case, as only one operation updates more than one db at a time and that update is easily recovered from if it gets out of sync.

In this library the main DB is used solely as an ATTACH point to manage 1 to 3 other DBs, and does not actually store any tables, so corruption of the main db is a moot point.

Question 3: given the above, is it "safe" to set the journal mode to OFF for the main DB, where "safe" means something along the lines of "will not, by itself, lead to corruption of the ATTACHed DBs in case of a crash"?

2021-12-02
02:39 Edit reply: Suggestions for learning SQL(ite) (artifact: 5830975c59 user: stephan)

you might try SQLite Tutorial

[[Edited by Stephan to remove uncalled-for snideness.]]

2021-12-01
02:43 Reply: Compile for Apple Silicon (artifact: 96a6ffcdac user: stephan)

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.

2021-11-30
17:48 Reply: Compile for Apple Silicon (artifact: 106f5fb37b user: stephan)

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

More ↓