SQLite Forum

Database anomaly

Database anomaly

(1) By anonymous on 2021-09-19 15:08:55 [source]

Using v3.36 on Windows, progrramatically, I open an existing database


Then write to a table within it and find


What is this latter database? Why is my change not in the database I opened?


(2) By Larry Brasfield (larrybr) on 2021-09-19 15:18:35 in reply to 1 [link] [source]

First, discover what the "current directory" is for the process that uses v3.36 progrramatically. Then look at ajay.db in that directory and observe your "lost" change.

Second, specify the absolute pathname (rather than a relative pathname) to the database your program should be accessing.

If the terms "absolute" and "relative" make no sense to you in this context, do a web search for "relative pathname" and study the difference.

Why is my change not in the database I opened?

Your question presupposes a fact not in evidence. What, exactly, is "the database [you] opened"?

(3) By anonymous on 2021-09-19 15:36:23 in reply to 2 [link] [source]

Thanks; I opened the initial database using an absolute path, namely,


There is neither an ajay.db in the process's (executable's) folder/location nor in the Start-In folder specified in the shortcut that starts the executable.

If it has any bearing, I am enabling load_extension to load fileio.dll an using the extension to write to the database in a transaction. When the transaction completes, I close the database sqlite3_close. No reported errors in my code.

My code runs successfully and the changes are in ajay.db��0 and not in ajay.db as I expected.

(4) By Larry Brasfield (larrybr) on 2021-09-19 15:46:31 in reply to 3 [link] [source]

Thanks; I opened the initial database using an absolute path, namely,

I suggest sidestepping a host of "what is the pathname seen by library?" issues by using this pathname to the sqlite3_open call: D:/sqlite32/db/ajay.db . (It has been ages since anything called "Windows" actually required backslash as the path-part separator.)

Your (and our) focus needs to be on what pathname is actually reaching the SQLite API. A debug print of it may be illuminating.

(5) By anonymous on 2021-09-19 16:04:30 in reply to 4 [link] [source]

I replaced backslash with forward slash i.e. changed d:\sqlite32\db\ajay.db to d:/sqlite32/db/ajay.db: that made no difference i.e. the code runs successfully and ajay.db��0 appears in the same location as ajay.db.

  • The CLI fails to open ajay.db��0. I renamed it to ajay2.db and the CLI can open ajay2.db.
  • With the CLI, I can see the changes in ajay2.db
  • However, ajay.db��0 aka ajay2.db appears to be a NEW database since the other content in ajay.db aren't in ajay.db��0 aka ajay2.db.

I am investigating further ...

(6) By Larry Brasfield (larrybr) on 2021-09-19 16:11:33 in reply to 5 [link] [source]

Are you passing a Unicode pathname to sqlite3_open()? Maybe you need to call sqlite3_open16(). Or, if you're calling sqlite3_open_v2(), a WideCharToMultiByte() conversion may be needed.

(7) By Warren Young (wyoung) on 2021-09-19 16:14:57 in reply to 1 [link] [source]

I’ve seen this when mixing UTF-8 and UTF-16 improperly. Cygwin binaries with cmd.exe or native binaries with MinTTY, etc.

(8) By anonymous on 2021-09-19 17:32:34 in reply to 7 [link] [source]

It might be related to Unicode. However, I am able to use the database using sqlite3_open and sqlite3_exec without issues when not loading an extension.

I stepped though my code and ajay.db��0 materialises as soon as I execute

select load_extension('fileio.dll')

using sqlite3_exec.

I tried to compile fileio.c (using CL) with no success. I acquired fileio.DLL from this link in this forum.

It is possible that fileio.dll from here has supplementary code that could account for what I am experiencing.

In fact, I am certain of it since I can run the SQLs in my code in the CLI (unlike SQLITE3.DLL, SQLITE3.EXE has ReadFile etc built in) without any anomalous behaviour.

(... Can I get hold of fileio.DLL from somewhere else?)

(9) By Larry Brasfield (larrybr) on 2021-09-19 18:07:24 in reply to 8 [link] [source]

I acquired fileio.DLL from ...

That was an act of faith.

Can I get hold of fileio.DLL from somewhere else?

I should hope so. See the fileio source and Compiling a Loadable Extension.

(10) By anonymous on 2021-09-19 18:14:22 in reply to 9 [link] [source]

That was an act of faith.

Indeed! I had a choice from an options list offering one choice only. I took it as trying out was not pernicious (no installation, no registry, environment variables changes etc.)

I'll re-try compiling the DLL from the source you've provided. Thanks.

(11) By Simon Slavin (slavin) on 2021-09-20 15:08:58 in reply to 1 [link] [source]

Which API are you using ? Are you addressing SQLite's C API directly, or using a SQLite library for your programming language ?

The string with path and filename that you're passing to the API. Is it ASCII, UTF-8, or 16-bit Unicode ?

My suspicion is that the API is expecting one, but you're using another.

(12) By Larry Brasfield (larrybr) on 2021-09-20 15:23:51 in reply to 11 [link] [source]

Simon, If you sift through the thread, and its follow-ons, you will see that the OP's troubles were caused by using a strange DLL of unknown provenance.

(13) By anonymous on 2021-09-20 17:37:38 in reply to 12 [link] [source]

The sequence is:

  1. (returnCode dbHandle)=sqlite3_open("ajay.db",out dbHandle)

  2. sqlite3_enable_load_extension(dbHandle,1)

  3. sqlite3_exec(dbHandle,"select load_extension('fileio.dll');",0,0,0)

After step 3, ajay.db��0 materializes (as a new DB i.e. does NOT have the tables in ajay.db) and all write activity end up in ajay.db��0.

Regardless of Unicode or rogue extension DLL, what this means is that dbHandle created in step 1 when ajay.db is opened, is inherited/hijacked by ajay.db��0 in step 3.

To me, that does not make any sense. Any insights?

(14) By Larry Brasfield (larrybr) on 2021-09-20 17:53:48 in reply to 13 [link] [source]

... what this means is that ...
To me, that does not make any sense. Any insights?

It is quite possible that the code whose execution precedes the misbehavior you observe induces the (ill)famed "undefined behavior" well known to mortal C/C++ programmers with much experience. Whether that inducement is in your code or in the image you loaded that came from a semi-mysterious source is not clear.

I have found that making sense of undefined behavior can be quite challenging at times, requiring careful use of a debugger and inordinate time expenditure. It is best to just avoid it, or find where it is induced and fix that.

It would take much more evidence to convince me that it is worthwhile to attempt to diagnose your ex-problem without source for the mystery DLL you found.

(15) By RandomCoder on 2021-09-20 17:57:29 in reply to 13 [link] [source]

No, it means unknown code doing unknown things produced unknown results.

It's hard to guess what's happening, but trying to reverse engineer the results is likely a waste of time. It could be memory corruption, it could be specifically doing that for its own odd reasons. It could be a stack corruption causing a call to some other function.

If you want to dive into it in a debugger, feel free, of course, but it's unlikely anyone here can gaze into a crystal ball and figure out what happened, we simply don't have enough information.