SQLite Forum

(Refreshing and clarifying this bug report.)

Recently, I had reason to see what the "--changeset SOME_FILE" option to sqldiff actually does. [a] I ran the "stock" version, one I downloaded as "sqlite-tools-win32-x86-3340100.zip" [b], with this invocation: sqldiff --changeset db_1-2_changeset db1.sdb db2.sdb , where the two .sdb files are different, confirmed by sqldiff's output when the --changeset option is omitted. The result is that db_1-2_changeset is created and left as an empty file. This is not what the sqldiff doc indicates should happen. Hence, I believe this represents a sqldiff bug.

[a. I had been testing a local change to sqldiff.c and noticed that the subject invocation (with ... naming two, differing databases) produced an entirely empty "changeset" file. The change was to optionally specify some non-PK column(s) that will not participate in the value comparison. It did not touch the changeset_one_table() function. Of course, wondering how I broke functionality I tried to not touch, I ran the "stock" version to see if I had created a bug. ]

[b. I tested sqldiff.exe on an up-to-date Windows 10 machine. ]

Is this 0-length changeset file creation, regardless of database differences, a known non-feature of sqldiff? If so, its doc is wrong. Otherwise this lack of output must be a bug.

Hard as it may be, you probably should wait to decide upon work-arounds, alternative approaches, feature avoidance or feature reliance. Pre-release code is unstable, lacking the backwards compatibility guarantees that the SQLite project honor, (as do many other projects.) The pre-release drops are made for improving bug-catching prospects, exposing problems such as you report, and getting feedback on new or enhanced features.

Re:

DML statements cannot be used in subqueries. I suppose that should be mentioned in the documentation.

Perhaps it should be mentioned. The syntax railroad diagrams "mention" that fact by omission, but deducing that limitation requires a careful study.

If something looks like a duck and acts like a duck in some way(s) but not all, an explicit caution, "This is NOT a duck.", is a favor to readers.

On behavior rather than documentation:

My thinking, and reason for liking the RETURNING clause feature (before understanding that limitation), was that it would simplify pure SQL operations that would otherwise require repetition of SELECT queries or client code written to collect a SELECT result set then run the DML.

When using SQL, it is helpful for comprehension that a category of "table-valued expressions" exists. Table and view names, table-valued functions, and SELECT queries can all be used as a "row set" within containing SQL. I had thought that DML with a RETURNING clause would and should also function as a table-valued expression. However, that simple view ignores an important side effect.

Of course, DML with RETURNING ("DMLR" here) is not something that should be expected to have the same "value" when repeated within an expression. I suspect this is why PG makes a DMLR table-value only usable when it is part of a WITH query. The WITH syntax makes avoiding repetition convenient and expresses reuse.

Even if a WITH-DMLR expression can only be followed by a single DML operation, it is useful for simple things like adding to an audit log. Follow-on operations that require multiple DML statements can be effected by a "WITH DMLR store-into-temp-table; use temp-table for more DML" sequence.

If the DMLR "value" could be used after a WITH ... query, using a secret temp table in the implementation would normally be a reasonably performant approach. The alternative involves storage of the DMLR result set anyway, within client code or with the repeated query returning what is to be modified.

Your question presumes a fact not in evidence. You mention (vaguely) a view which, for some reason unknown to anybody but you and possibly DRH, provided some basis to believe that one particular view had a rowid. That observation, (whatever it was), does not mean that "views have rowid".

A view might or might not have a column which is named "rowid". If the view's underlying query, whose results the view contains, happens to name one of its columns "rowid", (explicitly or via default naming), then you may see that. You might further conclude, incorrectly, that the view has a "rowid" in the same sense that tables often have a rowid as discussed here and in SQLite documentation. What you would be overlooking by so concluding is that the "rowid" in a SQLite technical discussion is used as a key in a b-tree used for table row storage. A view involves no storage at all, hence it cannot have a rowid in that technical sense. That 5-letter word, by itself, does not mean that an integer b-tree key is involved.

(v.1 Brain fart fixed, s/columns "view"/columns "rowid"/ .)

Well, none of the above trials fared any better in PostgreSQL. (It moans, "ERROR: WITH clause containing a data-modifying statement must be at the top level".) However, this screen-scrape shows what does succeed: postgres=# CREATE TABLE t0( postgres(# a INTEGER PRIMARY KEY, postgres(# b DATE DEFAULT CURRENT_TIMESTAMP, postgres(# c INTEGER postgres(# ); CREATE TABLE postgres=# create table t0log (whenCreated DATE, what integer); CREATE TABLE postgres=# with stuffed as (INSERT INTO t0(a,b,c) VALUES(5,CURRENT_DATE,random()) returning b,c) insert into t0log(whenCreated,what) select b,c from stuffed; INSERT 0 1 postgres=# select * from t0log; whencreated | what -------------+------ 2021-02-03 | 1 (1 row) .

(The 1 came in the what column comes from PostgreSQL's random() returning a float between 0 and near 1, which is rounded going into t0.c, and integer.)

I will suggest a few RETURNING doc changes as I study it further.

Using this table, CREATE TABLE t0( a INTEGER PRIMARY KEY, b DATE DEFAULT CURRENT_TIMESTAMP, c INTEGER ); , (lifted from RETURNING ), and having another table, create table t0log (whenCreated text, what integer); , I am trying to insert into t0log the results returned by this INSERT statement, INSERT INTO t0(c) VALUES(random()) RETURNING b,c; . The fact that DML with a RETURNING clause produces results, just as select statements do, leads me to believe that inserting them into t0log should be possible. This page on PostgreSQL SELECT substantiates my notion that DML with a RETURNING clause may be used as a subquery (in PostgreSQL at least. See the with_query use and expansion at PostgreSQL SELECT.)

I have tried mightily, with SQLite version 3.35.0 2021-02-03 13:52:17, to get the RETURNING feature to produce something usable as a subquery that can be used in further (or "outer") operations rather then just producing a result set.

For example, this fails: sqlite> with stuffer as (INSERT INTO t0(c) VALUES(random()) RETURNING b,c) select b,c from stuffer; Error: near "INSERT": syntax error

These are fine: insert into t0log (whenCreated,what) select b,c from t0; with stuffed as (select b,c from t0) select b,c from stuffed; , but this is not: sqlite> insert into t0log (whenCreated,what) with stuffer as (INSERT INTO t0(c) VALUES(random()) RETURNING b,c) select b,c; Error: near "INSERT": syntax error sqlite> .

At this point, I conclude that either: (1) I am doing this wrongly; (2) my notion is ill-founded that adding a RETURNING clause to some DML makes that DML into something with further use like a subquery or table-valued function; or (3) SQLite is not yet able to do more with RETURNING clause results than make them available to column value retrieval via the API.

Any thoughts as to which of those 3 cases applies is welcome. Especially welcome would be an incantation that makes DML with a RETURNING clause into something able to participate in containing DML statements. (I must admit that it was this latter utility that had me excited about the new RETURNING feature.)

(Edited to append:)

I should add that the draft RETURNING doc says, "The RETURNING clause is not a statement itself, but a clause that can optionally appear near the end of top-level DELETE, INSERT, and UPDATE statements." This might be interpreted as "The RETURNING clause does not convert the preceding DML into a subquery. It can only be used with a top-level DELETE, INSERT, or UPDATE statement." Hence, I am not claiming that SQLite v3.35.0 does not work as documented. I only claim that the feature might be made more useful. At the very least, it would be nice if there was a way to get RETURNING results into a temp table without writing library client code to do so.

Adding to Ryan's immediately clueful post: You really need to read, understand and heed the common wisdom on asking technical questions. A good starting place would be "How To Ask Questions The Smart Way" A web search on ' "how to ask" good "technical questions" ' will turn up some more. You will do yourself and those willing to help you a favor by putting some serious study and thought into your question posting.

Given the ubiquitous use of UTF-8, which cleverly avoids encodings having bytes equal to zero, relying on the NUL terminator would be simpler. As the docs say, If the fourth parameter to sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length of the string is the number of bytes up to the first zero terminator.. So, just write: return sqlite3_bind_text( statement, 1, buffer.baseAddress, Int32(-1), unsafeBitCast(-1, to: sqlite3_destructor_type.self) ) , and be done with it.

I made additional changes so that options such as --summarize do the sensible thing and various corner cases are handled correctly. (For example, specifying that all non-PK columns be ignored.) Generated inserts copy all columns from the base DB. Relevant files are:

1. Patches for tool/sqldiff.c

2. The sqldiff.c file from SQLite release 3.34.1 with above patches applied

3. An NMAKE makefile to build 64-bit sqldiff.exe

I do not grok swift or know what version of SQLite ships with iOS (14). However, I can assert that SQLite v3.33.0 handles your parameter substitution correctly and as you hoped. This fact is demonstrated by the following session screen-scrape: > sqlite3 SQLite version 3.33.0 2020-08-14 13:23:32 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .parameter set :myvar B sqlite> select 'A'||:myvar||'C'; ABC sqlite>

I also know that parameter substitution has worked correctly in most (if not all) SQLite library versions released over the last decade (if not much longer) and up to the v3.35.0 prerelease.

From that evidence, I conclude that something is going wrong with your call into sqlite3_bind_text().

Once your SQL is compiled and executing, there will be no "sqlite escape" action. The escaping is something SQL authors do and SQL lexers undo.

Yes, all you need (apparently, as your 3rd query success indicates) is for simple_query() to return a length-3 string, consisting of the character sequence: double-quote, single-quote, double-quote. (I write that out because, in this discussion, conventional quoting tends to confound the issue.)

That length-3 string value will not be subjected to any more SQL language spelling or representation transformations, such as "escaping" or its inverse. The value will be passed, without alteration, as an operand to whatever function you have caused to implement the "match" operator.

I realize you may have already understood all this. I expound on it because (in my opinion and experience) it is very important to distinguish between how things are represented and what they are at a deeper level. Such is the distinction between a string literal and a string value. One is a way of writing a string value so that a compiler (or lexer) may correctly interpret it; the other is a sequence of characters treated as an object in some hardware or a virtual machine.

What I gather from your post is that the middle query of the above 3 fails to yield the results you hope to see. You have presented a lot of code to read, which indicates that you have not narrowed this problem much, if at all.

One problem that does appear from a superficial study is this: Your function, simple_query(), when given the SQL literal '''' as its argument, appears to return either the SQL empty string literal token (two successive single-quotes), or that token bounded by double-quotes. [a] Neither one is likely to be a suitable right operand for the match operator.

[a. Showing the .mode meta-command output would show whether the double-quotes are being emitted by the sqlite3 shell or by your simple_query() function. ]

I think maybe your problem is that you expect that a pair of concatenated single-quotes, as a string value, should be interpreted as a length 1 string value containing a single-quote character. That is not so. The conversion of a SQL literal such as '''' to that length 1 string value happens when the SQL is parsed (or lexically analyzed, actually), and not as its parsed and compiled form is executed. Of course, the return from your custom function known as simple_query() is never passed through the SQL lexer or compiler; instead it is made, (by the compiler's output code), to participate in evaluation of the binary match expression with whatever string value it has, without any quoting, quote-escaping, or any such finagling.

Yet another option for dealing with your particular annoyance:

If sqldiff is invoked as usual, but with this additional invocation argument: --debug 2 , then it blats the differencing SQL instead of the differences. That can be collected (as edited_sqldiff.sql), edited then used thusly in the sqlite3 CLI shell: .open MY_BaseDB attach MY_NewerDB as aux .output migrate_BaseNewer.sql .read edited_sqldiff.sql .output stdout .quit . The edited_sqldiff.sql is almost what sqldiff --debug 2 emits, except that it will have some lines reading: SQL for TableWhatever: which must be removed before it deserves the .sql extension.

Once that CLI shell session is run, the migrate_BaseNewer.sql file will contain SQL which, if applied to MY_BaseDB, will alter it to be equivalent to MY_NewerDB . It would be relatively easy to edit edited_sqldiff.sql so that any columns you wish to be ignored are not mentioned in value comparisons. Specifically (and most easily), all expressions resembling "A.noisyCol IS NOT B.noisyCol" can just become "0" (sans quotes.)

This may look like a lot of fuss, but the collection of and edits upon edited_sqldiff.sql need be done only once with repeats for schema changes.

It needs a little more work to cause generated INSERT statements to copy all of the columns. As it is with that patch, the ignored columns need to have a default value because they do not appear in the generated INSERT statements.

I will follow up later in this thread with that revision.

For grins, I made sqldiff accept another option whose usage tip reads: --skipcolumn COL Ignore column COL differences

Here is an example of its action: C:Usersbrasf>sqlite3m -batch db1.sdb "select * from Pets" name species lastMeal Fido dog 17:54 Fluffy cat 17:49 Slither snake 15 days ago

C:Usersbrasf>sqldiff -skipcolumn lastMeal db1.sdb db2.sdb UPDATE Pets SET species='serpent' WHERE rowid=3;

Here are Diffs of Changes to tool/sqldiff.c.

The option is not paired with table selection. The to-be-ignored column is ignored in any table which has the named column. It is only ignored of not part of a primary key. To restrict the ignoring to a single table, the (preexisting) --table option must be used.

I imagine "the link" was in a particular package description. That's common, as it indicates where one might go to get more recent versions. But if you are serious about trying to get the package repository maintainers/updaters to advance their update of a particular package, you must discover who they are, and that will virtually never be the creators of the software in individual packages.

BTW, regarding off-topic posting: You were just informed, not chastised. No doubt you (once) thought your quest might be resolved here. (I call it a "quest" because I doubt it will be resolved elsewhere either. Hence my suggested alternative.)

It would be fairly simple to run the output of sqldiff through a filter that sifted out lines which only updated a column of your choosing in tables of your choosing. This would be simpler than using your eyes and brain, and less annoying too.

With slightly more effort, sqldiff.c could be made to accept an option telling it to ignore a table,column combination. That would be a better ask than specializing the tool to "ignore all 'DEFAULT CURRENT_TIMESTAMP' columns".

I would be remiss to neglect mention of what could be done with the sqlite3 shell, attaching the databases of interest, and specifying joins on tables that might be similar but not identical. You could ignore any set of columns that way.

As Richard says, your request is off-topic here.

However, you do have an alternative which is well within the SQLite project's purview. You can get the SQLite sources, and build binaries from it for whatever "dev" purpose you have. See the SQLite Download Page and How To Compile SQLite. Once you do so, you may slap your forehead for spending to much time trying to get the OS package manager to "save" you the effort. And if it is not easy enough for you from perusing those docs, help can be readily found here.

That route is probably better from a software control perspective than relying on the vagary of dependence upon decisions of system software repository managers over which you have vanishing little control or influence. I dare say they have concerns and objectives very different from trying to cater to the desire of software developers to use the latest and greatest libraries, particularly when those users have such excellent alternatives. (See above.)

(Speaking for myself only:)

The cascading delete case you present is an outlier. In the usual case, cascaded deletions will happen in an arbitrary set of tables, which only by coincidence includes the origination of the cascade. I see no reason that it should be made easier to get the cascaded results only for that one somewhat special case when so many other cascaded results are unavailable that way. Most foreign keys reference other tables. (I suspect that is why they are named "FOREIGN".) Of course, this is just reiterating your point, "Shouldn't all tables be treated the same?" Yes, it is best that they are, to ease human conceptualization.

The consequence, "you might get something in between depending on the delete order and the timing", gives me a shuddering dread. SQL is best considered as a way of specifying set operations. That "delete order" and "timing" dependence is a monkey wrench thrown into that eminently useful conceptualization.

Hmmm. The information leakage exposure seems little different from what those schema-revealing pragma's can do when ensconced in views in the temp schema.

I do not deny the utility of the (un)trusted_schema scheme. I was thinking that the table-valued pragmas are innocuous and hence could be harmlessly attributed as such in the code which makes them visible as tables. IOW, no security risk is created beyond what the same views in temp entail (which is some schema leakage.)

Yes, I have trusted_schema left at its default. (off)

Is there any reason that the schema-revealing table-valued pragma functions should not be marked as INNOCUOUS? They certainly are not writable tables. Nor do they have side-effects. How dangerous can they be?

Thanks for that. After some early hiccups arising from pulling a slightly incoherent trunk tip file set, that built without a hitch and appears to work as well as your previous "Catalog" does (or better.)

I am tempted to create the views in an in-memory database known as 'information_schema', not because that is (or is becoming) conventional but because it segregates the view names from application table/view names. If there was a "DETACH IF ATTACHED Whatever_Schema" feature in SQLite, I would suggest that. It would be doable if there was a way to say "ATTACH ':memory:' as information_schema" only if SELECT COUNT(name) as ise FROM pragma_database_list WHERE name = 'information_schema' returns 0. Is there a clean way to do that?

(Appended by edit:)

If those views are not in the temp schema, but in information_schema, this happens: sqlite> select * from information_schema.SysTables; Error: unsafe use of virtual table "pragma_table_list" Rats.

Nice.

Have you signed the project's contributor's agreement? Those pragma enhancements would be a great optionally compiled feature.

Can you publish diffs somewhere?

I agree that this is very strange behavior, perhaps to be considered a bug.

With SQLite v3.33.0, I get a different error message from the same input: there is already an index named i1

IMHO this is definitely a bug.

Without getting into the whys and wherefores of your "bug" claim, do you have some complaint or improvement suggestion for how ALTER TABLE works after "PRAGMA legacy_alter_table = 1" is run or when the library is compiled with that as the default?

If your "bug" claim is a dispute with the legacy behavior, I would simply point out that there is an unknown number of library users who would be as dissatisfied as you, if not more so, were that behavior to conform to your wishes. There is an excellent chance that those users outnumber you by a lot.

I cannot tell what your fnrownum column is supposed to mean. I can guess that its meaning may be well met by counting rows in your table with some ordering criterion (that you have not mentioned.) I hesitate to suggest a real solution because the problem is too ill-defined. However, this would at least produce the result you say you want with the table content you have shown: UPDATE tnTEST set fnrownum = fnidx - 9;

This answer is for anybody having difficulty with the (thread) subject.

When Visual Studio is installed, there are many feature options. At an early stage of the installation, these options can be selected via a dialog box. The same dialog is presented when an existing installation is modified (by launching the "Visual Studio Installer", a shortcut placed in the "Visual Studio 20##" folder in the Start Menu.) The option, "Desktop development with C++" must be checked. There are suboptions, most of which are not strictly needed to compile SQLite. The suboptions tagged "MSVC v### ..." and "Windows ## SDK ..." should be checked. Then the installation (or its modification) can be told to proceed to completion.

If the installation for it has been done, the Start Menu will have a folder tagged "Visual Studio 20##" which contains one or more shortcuts tagged "... Tools Command Prompt" where the elided part indicates various combinations of host and target platform (x86 or x64) and "Native" or "Cross" compilation.

Launching one of those "... Tools ..." shortcuts will start a command-line shell, cmd.exe, known as "the command prompt" or sometimes (incorrectly) "DOS prompt". That shell will have its INCLUDE, LIB, LIBPATH and PATH environment variables set or modified such that useful command-line tools such as CL.exe, LINK.exe, LIB.exe, RC.exe, DUMPBIN.exe and NMAKE.exe can be found among the directories named in %PATH% and those tools can find "#include <whatever.h>" header files and commonly used system link libraries, among others.

To build the SQLite library or its shell, sqlite3.exe, How To Compile SQLite should be studied and heeded. There, the use of an NMAKE script, "Makefile.msc", distributed with the "sqlite-autoconf-3######.tar.gz" forms of the amalgamation archive is described. Building with MSVC without the benefit of that makefile is a complex subject, best left to experts or those willing to learn NMAKE and study that makefile.

The makefile implements many build options and targets. By default, it builds the sqlite3.exe CLI shell with the SQLite library statically linked. It can be told to build the library as a DLL. Debug and normal builds can be specified. To see all the options, learn rudimentary "make" syntax and study the makefile.

Please examine the following code, then say whether you want to continue asserting that there is something amiss worth anybody else's investigation. You may also want to read about the UnmanagedFunctionPointerAttribute.

using System; using System.Runtime.InteropServices; namespace ConsoleApp2 { class Program { const string dbLib = "sqlite3.dll";

 [DllImport(dbLib, EntryPoint = "sqlite3_exec", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
 static extern int sqlite3_exec(IntPtr dbHandle, [In][MarshalAs(UnmanagedType.LPStr)] string sql,
                                Callback callback, IntPtr arbArg, ref IntPtr errmsg);

 [DllImport(dbLib, EntryPoint = "sqlite3_open", CharSet = CharSet.Ansi, CallingConvention = CallingConvention.Cdecl)]
 static extern int sqlite3_open([In][MarshalAs(UnmanagedType.LPStr)] string filename, out IntPtr dbPtr);

 [DllImport(dbLib, EntryPoint = "sqlite3_close_v2", CallingConvention = CallingConvention.Cdecl)]
 static extern int sqlite3_close_v2(IntPtr dbHandle);

 [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
 internal delegate int Callback(IntPtr p, int n,
            [In][MarshalAs(UnmanagedType.LPArray, ArraySubType = UnmanagedType.LPStr, SizeParamIndex = 1)] string[] names,
            [In][MarshalAs(UnmanagedType.LPArray, ArraySubType = UnmanagedType.LPStr, SizeParamIndex = 1)] string[] values);

 static int callCount = 0;

 static void Main(string[] args)
 {
    Callback IR = new Callback(IterateResults);
    IntPtr dbHandle;
    IntPtr errMsg = IntPtr.Zero;

    if (args.Length < 2)
    {
        Console.WriteLine("Provide DB filename and a table name as arguments.");
    }
    else if (0 == sqlite3_open(args[0], out dbHandle))
    {
        int rc = sqlite3_exec(dbHandle, @"select * from " + args[1], IR, dbHandle, ref errMsg);

        Console.WriteLine($"exec return: {rc}");
        sqlite3_close_v2(dbHandle);
    }
    else
    {
        Console.WriteLine("failed to open database");
    }
 }
 public static int IterateResults(IntPtr unused, int n, string[] values, string[] names)
 {
    ++callCount;
    for (int i = 0; i < n; i++)
    {
        Console.WriteLine($"{names[i]}[{callCount}]={values[i]}");
    }
    return 0;
 }

} }

You should see this post before putting much more time into postulating where the bug is or who should fix it.

[interop library developers] would be the appropriate recipients.

To my mind, that does not make sense.

1. Any such problems is likely to be picked up by the infinitely more interop library users than sqlite users.
2. If the interop library has a flaw, it would manifest itself with the first callback and not the 4th. The error arises during the 4th callback consistently irrespective of the number of columns i.e. the size of data in the sqlite3 pointer.

Disregarding the hyperbolic "infinitely more", what makes you think my hypothetical interop bug is subject to more exposure instances than your hypothetical SQLite bug involving use of a C callback? I cannot see a reason to believe such, knowing that SQLite's use, and use of its sqlite3_exec() function, are ubiquitous. I see less evidence that callbacks into managed code from unmanaged code are used much, particularly from 32-bit DLLs.

Regarding failure on "the 4th callback": All that this demonstrates is that a side-effect is at work rather than simple wrong computation. Side-effects, especially when they involve memory corruption, have a wild variety (or even a near-infinite variety) of manifestations. Knowing this, I see no reason to deduce anything from this delayed failure other than that it is a side-effect. (That is why I suggested heap checking awhile ago.) Furthermore, any reasoning as to what the delayed failure implies will apply equally to any subset of the subject code, what you wrote, the interop layer, or the SQLite library.

... NOT in any way pointing fingers

It is too early to do that. But deciding where to invest effort hunting for this bug is not premature. Looking within the SQLite library is counter-indicated.

The problem exists? Where does that leave me?

It leaves you with one fewer option than you had once imagined for solving your problem. CLR/C interop has been operable for many years now. You may need to use P/Invoke differently if you insist on using the 32-bit sqlite3.dll, as distributed. It would not be difficult to write some C# code not relying on a callback as sqlite3_exec() does for query results. That is merely a convenience function; it can be replaced with sqlite3_prepare() and sqlite3_step().

SQLite should at least investigate.

On what basis? Do you insist that there is a significant likelihood that such investigation will lead the SQLite dev team investigator(s) to making some improvement in the library? That is highly doubtful given that sqlite3_exec() is a legacy function, with an interface frozen for a few more decades. Do you realize that most of the team members primarily use Unixen machines?

Regardless of the moral or self-interest imperative you perceive on behalf of the SQLite dev team, I can predict that they are not going to go figure out why .Net interop is failing for you, and they certainly are not going to fix it. So you are on your own unless you can prevail upon the InteropServices library developer(s) to investigate what goes wrong with your simple sample code.

Are you using sqlite3_exec() with a callback to managed code? If so, would you be willing to show the declarations, with attributes, that you use for sqlite3_exec() and your callback?

If you are not using such a callback, your experience is a testament to the usual, one-way interop being a heavily trodden set of code paths.

I suggest you look into a design pattern known as "Model-View-Controller", or "MVC". I dare say that if you were using it, your problem first posed in this thread would not have arisen.

There is another reason to defer resting suspicion on the SQLite library in this case.

Hence my request for 'investigating' what appears to be an 'anomaly' as opposed to requesting a 'fix' for a 'bug'.

Regardless of what the disappointing behavior is called, it is not in any way a result of any defect in the SQLite library code. The sqlite3_exec() API is a very simple convenience function which has existed in essentially the same form for years. Its use of the callback function pointer is unchanged for 16+ years. This API is used in many places within sqlite3.c to help implement other functionality that is also well tested and in common use. To me, it is barely conceivable that that it would be incorrect under such circumstances. To even suspect a defect there, strong and conclusive evidence would be needed. All that your case shows is that something is going wrong somewhere in a collection of code that includes what you wrote, what the .Net CLR and a .Net library do to effect CLR/C interop, and the SQLite library. Given the testing to which sqlite3_exec() is subjected and the widespread success of its use in many applications, your suggestion that it might have a defect, exposed by your code, is very weak and ambiguous evidence. It would be a waste of the SQLite developers' time to debug this problem as part of their work on the SQLite library. (Somebody might do it to see yet another pitfall awaiting those who traipse in the interop realm.)

Your expectation that your code should work across multiple platforms is not unreasonable where it is platform-agnostic. However, the transition between the managed environment of .Net CLR execution and the unmanaged execution environments running on the AMD/Intel x64 and Intel/AMD i386+ Windows platforms cannot be achieved by "the same code". An adapter layer is being inserted to do that, and that layer must differ in some way for those unmanaged environments (even if only in code paths taken.) As far as the SQLite dev team should be concerned, that adapter layer is just as much "not SQLite" as the code you wrote. It is not their problem.

If your interop scenario had worked, I believe that would have been more correct for the .Net System.Runtime.InteropServices library. What you are seeing may well be due to a bug in that library. I say this because I do not see anything in the documentation for the DllImport functionality to suggest that it should be used differently between the cases where 64-bit versus 32-bit DLLs are called into. You have a fairly simple test case, using a well-proven DLL, that could be of real interest and value to the InteropServices library developer(s). So if you are going to plea for an investigation and eventual fix, they would be the appropriate recipients.

Your dummy, no-real-changes editing makes me want to just block you. ...

... I'm used to editing posts if I think it's necessary.

I refer to a succession of 3 post versions which differ in no way other than timestamp. I have to wonder how you can believe such "edits" to be necessary.

Assume the app has 2 tabs and therefore two connections in the pool. In first tab user writes attach database a.sqlite or pragma synchronous = 0. He will expect that these changes will be applyed to both tabs. But there is no mechanizm to sync them. And that is why I can't use "by using common code to get connections to the configuration you want".

It is not hard to imagine whatever code makes the connection changes doing them to the whole set of connections that are to act the same.

keeping them in a pool

I don't want to get a real copy. I need the same runtime configuration. I can achieve it by next steps:

...

But it is a lot of work for me and sqlite. I thought there is a simple way. It does not exist. ОК.

There are several simpler ways. Use the same connection instead of many doing the same thing. Apply changes in tandem, perhaps iterating over the pool.

I would not worry much about changes instigated via UI being too taxing for SQLite, at least not with any hardware from the last several decades.

Will you please stop "editing" your post(s) just to change the timestamp? It is annoying and time-wasting.

If you have something substantive to add, add it as another post. And if you believe, after a reasonable period of time has passed, that the thread deserves more attention that it is getting, maybe then add something calculated to improve prospects for an answer.

Your dummy, no-real-changes editing makes me want to just block you. It is like a child crowding between a group of other children and an attention-giving adult.

BTW, there is no API for cloning a connection. You need to simply use the multi-threading capability (such as it is) that SQLite already has. Even if you could get a cloned connection, it would perform no better in multi-thread scenarios. Critical database access must be serialized either way, either by the set of connection holders or the library itself. (See Using SQLite In Multi-Threaded Applications.) You should study Write-Ahead Logging to see what can be done to assuage your performance concerns. Use of multiple connections to the same database is among the scenarios addressed there.

Because there are allocated resources associated with a connection, it is better to simply make additional connections to the same database if there will be different prepared statements in process. Those clones (if they existed), would have to be deep copies, little different from seemingly redundant connections unless the copies were made different by usage.

Your objective of getting "similar" connection "configuration" could be achieved by using common code to get connections to the configuration you want, and keeping them in a pool instead of tossing them as threads are done or die.

I would also suggest that you first find out how well use of simply redundant connections solves the problem that motivates your "clone" inquiry before getting to attached to that approach or disappointed that it is unsupported. The real problem is not management of data held by some sqlite3 struct(s); it is management of the resources referenced by the struct(s). Replication of the struct data is a side problem, more of a distraction than the real issue.

Please investigate <Attempted to read or write protected memory.>

Something is amiss with 32-bit SQLite3.DLL

I have seen no reason whatsover to believe that the address fault is caused by anything in the sqlite3.dll conveniently built and published at sqlite.org . The fact that some client code, which includes an interface between its own .Net CLR code and that DLL, appears to lead to an address fault only shows that there is something wrong with the whole collection. When, as in this case, string data is being passed between different execution realms, there are well known opportunities for memory management bugs to arise. Interfaces between managed execution environments and native code are notoriously difficult to get right.

What is amiss?

Identical callback code fails with 32-bit SQLite3.DLL but works with 64-bit SQLite3.DLL.

There is little reason to conclude that the callback code seen/run by the sqlite3_exec() function is identical between those cases. Indeed, it should not be identical because calling conventions differ between 64-bit and 32-bit code. The issue is: Does the actual callback code (including parameter marshaling) differ as it should and only as it should between the case where a 32-bit sqlite3.dll is used and where a 64-bit sqlite3.dll is used? That is not yet known, and until it is known there is no reason to be suspecting that there is a bug in need of investigation by the SQLite dev team.

There is another reason to defer resting suspicion on the SQLite library in this case. The problem is occurring during a callback run by sqlite3_exec(), an API that is much used in the SQLite CLI shell and is undoubtedly tested extensively.

so it would be educational for you to say why [asking for code "raises doubts"].

I thought that anyone willing or capable of troubleshooting code will be able to create the code without any problems. On the other hand, it would make sense to use the same code as myself; therefore, the request for code is valid.

Someone capable of seeing what is wrong with some code would likely create code that did not fail, unless they intended to create a specific bug. However, creating an address fault can be done in so many different ways that it would be pure chance if such creation happened to match how some unseen code did so. This is why "show the code" is so much preferred over summary descriptions.

I can build the app and run it without any address faults

Thank you ... I am using Console App(.Net Framework) and 32-bit SQLite3.DLL

This might be an parameter marshaling problem, or it could be simply that you have not yet told the auto-magic marshaling builder enough that it knows what to expect. [a] A diligent perusal of the Native Code interfacing docs is indicated. I doubt that your problem is a simple bug in the .Net marshaling code or C# compiler. Debugging at the assembler level would likely be revealing as to what is going wrong, but not how to fix it. Debugging at that level for the working and failing versions, with cross-comparison, would be more interesting.

[a. I was surprised at how convenient marshaling setup has become since I last had to do it. Maybe it is not quite as easy as it looks in that post 15 code except when certain defaults are correct. ]

Switching to .Net Core is a sound idea but I need to be working with 32-bit SQLite3 my reasons will be clearer when I provide feedback ... more soon.

I am not advocating .Net Core or use of a 64-bit DLL, at least not here. I was trying to get defaults more likely to be favorable because, while I did not see anything wrong popping out of the Delegate declaration, I was less sure as to what might be missing. To me, it seemed too easy.

That you ask for the actual code raises doubts in my mind;

I cannot imagine why, so it would be educational for you to say why.

You might be interested to know that when I substitute your code into a demo .Net Core console application targeting 'Any CPU', change the DB and table name literals to match some databases I have laying around, and put a 64-bit sqlite3.dll next to the .exe, then I can build the app and run it without any address faults, including blatting out a table with 22588 rows.

Is that code in your post 15 what is actually producing address faults for you?

Your very long response does nothing absolutely nothing to help me search for a resolution.

I can see that. The "search" you intend must exclude using heap checking tools or changing your code to see whether use of simpler data types correlates with your problem. Good luck with that "search"; you will need it.

You did not answer Keith's question, "Are you attempting to 'hang onto a pointer' after your callback returns?"

I suggest you re-read the opening paragraph in my response.

I did read it and found a simple "No." followed by an elaboration of facts unrelated to the question. It was just as if you answered a different question.

When I call sqlite3_exec from my code, on which line in sqlite3.c does it land?

Your response does not help.

The line number might be 116761 if your SQLite version is 3.25. Does that help? Or do you expect some shoemaker's elf to provide a table with the line number you demand for every version of SQLite you might be using?

I provided guidance for finding that line in whatever version of sqlite3.c you happen to have. To say that does not help shows that your notion of what will be useful in your quest is extremely limited -- so limited that I doubt you will find it here or at stackoverflow.

On balance, since I am getting 3 records back with what I have suggests that the problem is more subtle

More subtle than what? More subtle than the effects of heap corruption?

To any experienced programmer, the fact that you see 3 (or 6 or 2) "successful" callback executions followed by an address fault when sqlite3_exec() is called suggests that the SQLite library code has been asked to use a corrupted heap, and that corruption has led to an address fault when the heap manager attempts to use the corrupted heap data structure. As many thousands of test cases show and many thousands of the library's users know, the library is pretty good about not corrupting the heap. Hence, it is reasonable to suspect that your code is corrupting the heap. Yet you are immune to doing the simple work needed to ascertain whether or not that is happening. Too much work, or beyond your ken, I suppose. Better to see if somebody else has a solution.

And I am not even thinking that it is anything to do with SQLite yet; that is why I've tried with several versions thereof.

Strange. Several versions of your failing code would be a better experiment. For example, if your callback, (which you have not revealed, even at the stackoverflow site where your plea for help was also made), does nothing with the data passed to it (by indirect reference), does the address fault still occur? Does it still occur if the callback makes only deep copies of the data? Is the heap intact across your sqlite3_exec() calls? Is it intact across execution of your callback? Inserting some diagnostics would be much more fruitful than trying different versions of sqlite3.c and hoping that matters.

Ryan has given you good advice for helping others to help you. And as Kees noted (and I confirm), you tried but failed to do that. Is this because you do not know what code is actually running when your callback is called? Or are you simply hopeful that with a few seconds more work posting that link that somebody is going to spot your bug?

Given what I see of your programming skill outside of the nice, managed execution environment that C# provides, I think you would be way ahead to use System.Data.SQLite and its SQLiteDataReader class to pull data from your SQLite database. Clearly, you are not yet knowledgeable enough about using C to be using the Native Code interfacing capability. (If you were, you would not be asking others to find a function definition for you in sqlite3.c .)