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?

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

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.

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.

What does sqlite3_extended_errcode() return when the ATTACH fails ? Is it SQLITE_OK ?

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.

Yeah, you're right. My post was a mess and I didn't understand your C code properly. I'm sorry about that.

I'm hoping that Ryan's has put his finger on the problem.

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.

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

My "this bit can't possibly be going wrong but at this point I'll try anything" approach to debugging would replace:

  rc = sqlite3_exec(db, fsl_buffer_cstr(&buf), NULL, NULL, &zErr);

with:

  rc = sqlite3_exec(db, "ATTACH '_thingie' as thingie", NULL, NULL, &zErr);

to eliminate anything "weird" in the "fsl" stuff.

But in doing so, I also noticed that with the CLI you're using:

ATTACH '_x' as x

but in your code you're using:

ATTACH '_thingie' as 'thingie';

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

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

Stephan,

Looking at your code, I was as mystified as you. So I decided to try it myself (and remained mystified for a bit.) 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, ATTACHed DB's are in some similar, "not yet quite existent" state. I'm inclined to ask whether this should be considered a bug.

Here is my code which works, (but still mystifies me:)

/* Build thusly: gcc -I./SQLite/LibTrunk -o dba dba.c ./SQLite/LibTrunk/sqlite3.c
 */
#include <stdio.h>
#include "sqlite3.h"


int main( int na, char *av[] ){
  sqlite3 *db=0;
  char *zErr=0;
  int rc = sqlite3_open_v2(":memory:", &db,
                           SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
  if( SQLITE_OK==rc ){
    /* Next line merits SB's particular attention: */
    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);
    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);
  }
}

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.

You should include both the flags: SQLITE_OPEN_READWRITE and SQLITE_OPEN_CREATE.

Apologies, that was my knee-jerk quick reply having only half-read the question and skimming the code. I mean, it might work, but I am not sure the open mode of the main DB transfers to any ATTACHed DB - you'll need to test that.

Even if that works, it's only half a solution because you might want that for attaches, but not for the main DB.

I'm sure Larry's observation might solve it for you, but I'm a bit surprised as I did not expect that to work. I'm still thinking there's probably reason enough to make it so you can specify whether you want an ATTACH DB to create the DB or fail if it doesn't exist. I much prefer it to fail (in line with my misguided belief until now).

Ok, after some testing, I am relaxing as the ATTACH in my code absolutely always fail if the given DB does not exist, even attaching to a long-existing large DB.

I know this does not solve your problem, but at least it makes me sleep easy again. :)

Going to have to +1 Larry's suggestion that the behaviour seen might be a bug. If that is intended, I am not finding any documentation or literature anywhere in support.