SQLite Forum

Timeline
Login

50 most recent forum posts by user anonymous

2021-09-27
07:49 Reply: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately (artifact: b0680bf448 user: anonymous)

Are you setting PRAGMA journal_mode=WAL; on the connection you're using to run your application-initiated checkpoints on? When I last tried a similar setup to yours, I had to set it explicitly again on the checkpoint connection for those calls to work, even though WAL mode is meant to be persistent.

05:10 Post: Test SQLITE_NOMEM result code on Linux (Memory : 8Gb) (artifact: 678ac0c4ec user: anonymous)

Is there any way to test SQLITE_NOMEM result code on the following interfaces : 1. sqlite_open(:memory:) 2. sqlite_prepare_v2

I am using it in a library and have to simulate for test purpose.

Also please provide any pointers on how to use sqlite_test_control for limiting memory and fault injections.

Sqlite Version : 3.22

2021-09-25
21:37 Reply: V3.36 (artifact: 9dcc644e3c user: anonymous)

In the CLI

,databases

or

PRAGMA database_list

tells me which databases are in scope in the current session (as Simon Slavin points out) ... but I see the alias as main and not the handle i.e. as returned by the out parameter of sqlite3_open(dbname, out handle).

Programmatically, I will need to use sqlite3_open--which creates handles and not aliases as the CLI--to open databases.

To paraphrase, my original question was simply to figure out whether I could get the list of databases I've opened together with their corresponding handles ... programmatically .

It looks like I have to manage this within my application. Correct?

20:08 Post: V3.36 (artifact: da4d820656 user: anonymous)

Can I get a dictionary (name = database name, value = handle) for all opened databases?

16:04 Post: sqlite3_busy_handler missing (artifact: ce406b9379 user: anonymous)

In the latest versions of SQLite.Interop.dll the api entry sqlite3_busy_handler is no longer being exported. Is it possible to have it back?

12:55 Post: question about sqlite3_close_v2 call! (artifact: c0306b4460 user: anonymous)

If i call sqlite3_close_v2 and it return OK then quit process, but there is some statements not finalized. In this case journal file and wal-shm file will be safely handled like I call sqlite3_close and return SQLITE_OK?

I open database in single process only.

11:46 Reply: Question about WAL Checkpoint process (artifact: 549763d556 user: anonymous)

The checkpoint operation does not create a backup of the database, the pages being copied into the main file will not be marked as checkpointed in the WAL until after the copy is successful (here's your atomic commit). If the copy process breaks for any then the pages written are ignored (as they still show up as uncheckpointed in the WAL) and can be recovered from the WAL later

11:45 Reply: Question about WAL Checkpoint process (artifact: 0a60a64425 user: anonymous)

The WAL file is the backup. When a page is successfully checkpointed the database is updated to reflect this. If page 1000 was not written successfully, clients continue to read it from the WAL file as if the checkpoint of that page never even started.

07:43 Post: Question about WAL Checkpoint process (artifact: cd4b5a8d92 user: anonymous)

When SQLite start Checkpoint,is there a backup file create to avoid crash in between multi write into into main file?

for example if there need copy 1000 page into main file, what happy if app crashed at write 999 page?

If they are create backup file and rename it late for atomic, this will cause a lot IO for a huge database(for example 1TB database). Is this the case for SQLite?

I know there is batch-atomic-write to solve this problem, but that only work for F2FS.

01:42 Post: Multi-Process Access (artifact: d45d0814e5 user: anonymous)

Hi I know sqlite can support multi-threads. In my case, there are different processes that need to access the same db to read and write data on linux platform.

Can sqlite handle this scenario?

2021-09-24
17:32 Post: how can i simulate the failure of sqlite3_open(":memory:", &sqlite_db)? (artifact: 9154553cda user: anonymous)

Is there a way to simulate the failure of

sqlite3_open(":memory:", &sqlite_db)
i.e return not equal to
SQLITE_OK
?

I need the failure for testing purposes.

15:09 Reply: sqlite3_commit_hook bugs? (artifact: 475bdf57b5 user: anonymous)

Sorry, this is my mistake, I use it wrong.

14:01 Post: sqlite3_commit_hook bugs? (artifact: 01cab723b4 user: anonymous)

I try use sqlite3_commit_hook to log query, get this issue:

every auto commit transaction made the callback execute once except your next call is BEGIN TRANSACTION.

For example:

INSERT INTO t(v) VALUES(1);
INSERT INTO t(v) VALUES(2);
BEGIN TRANSACTION;
INSERT INTO t(v) VALUES(3);
INSERT INTO t(v) VALUES(4);
COMMIT;

The callback should fired 3 times, but it only fire 2 times. the second query is skiped.

Test on master branch.

05:29 Reply: SQLite query logs implementation (artifact: 1748beeb45 user: anonymous)

Maybe with sqlite3_commit_hook/sqlite3_rollback_hook log I can skip log all sqlite3_stmt_readonly(stmt) SQL ?

I am not sure this can handle sqlite embed transaction.

I need a hook to know a transaction is started so I know the position of embed transaction, and only remove the query logs for the last transaction with rollback hook. (I can do it from sqlite3Step, is there a more easy way?)

05:14 Post: SQLite query logs implementation (artifact: f7a0863792 user: anonymous)

I try implement a query log by hook sqlite3Step, is this the only function that I need to take care to log all database changes?

I will try disable date/random/PRAGMA function in database.

I will use sqlite3_expanded_sql to get the query SQL, skip SQLITE_SCHEMA result.

I will skip sqlite3_stmt_readonly(stmt) step, except BEGIN/COMMIT/Rollback/SAVEPOINT/RELEASE query. Is there any other readonly query I should not skip?

I will use sqlite3_commit_hook/sqlite3_rollback_hook to save the query logs or drop the query logs.

I want to make sure to execute them one by one will results in same database. (I will make sure execute them in same SQLite version)

With this logs I can reduplicate it to raft group, or master/slave node.

2021-09-23
18:29 Post: Changing the password or decompressing when using ZIPVFS (artifact: 7c558e0613 user: anonymous)

The public documentation of ZIPVFS does not really address these, but is it possible to

  1. Change the password to a database file using ZIPVFS

  2. Decompress the database file using ZIPVFS

without writing a new separate file, but replacing the current one on-the-fly?

16:37 Post: Cannot select a value that matches another column name (artifact: 45c2ea042b user: anonymous)

Is this a bug or is there a work around: https://stackoverflow.com/questions/69303775/sqlite3-how-to-select-the-value-of-a-column-that-matches-another-column-name

15:26 Post: sqlite3_exec (artifact: 511f037a67 user: anonymous)

If I have 2 or more SQL statements which I execute with sqlite3_exec, it will execute the first statement.

  • what is the simplest way to move to the second and subsequent statements
  • is that done in the callback function?
15:05 Post: sqlite not working on alpine arm32 image (artifact: ccd10b5844 user: anonymous)
Hi,

I am  not able to get SQLite running on an alpine arm32.

Any ideas?
10:55 Post: sqlite3_sql return value (artifact: 5d1b47c0e8 user: anonymous)

I am on the wal2 branch.

prepare with this sql:

" REPLACE INTO tab1 VALUES(abs(random() % 100)); REPLACE INTO tab1 VALUES(abs(random() % 100)); REPLACE INTO tab1 VALUES(abs(random() % 100)); REPLACE INTO tab1 VALUES(abs(random() % 100)); REPLACE INTO tab1 VALUES(abs(random() % 100)); "

sqlite3_sql only return this:

" REPLACE INTO tab1 VALUES(abs(random() % 100)); "

Is this correct ? how I can get the full SQL related with the stmt ?

2021-09-22
14:25 Post: sqlite3_exec: 5th parameter (artifact: 41b425a7bb user: anonymous)

Is the 5th a verbose description of the return code of sqlite3_exec?

Should I check the 5th parameter (which is a C# out parameter) all the time or only when the return code is not SQLITE_OK i.e. 0?

12:48 Post: vfs write amount size without WAL (artifact: 30378acf36 user: anonymous)

I try create a vfs, on my test the iAmt from write method always equal page size.

I want to confirm this allway be true if I stay on memory journal mode(or OFF mode)?

09:41 Reply: Windows thinks 64bit SQLite3.dll is 32 bit (artifact: 6e6368b248 user: anonymous)

You should not install your aplication files in system directories.

07:03 Reply: Windows thinks 64bit SQLite3.dll is 32 bit (artifact: 52b721942d user: anonymous)

Maybe that your setup program is 32-bit, and has %WINDIR%\System32 redirected to %WINDIR%\SysWOW64? Then tell the setup program to copy the file to %WINDIR%\Sysnative.

06:25 Reply: Queries across databases (artifact: fa6b412833 user: anonymous)

How are you "using SQLite3.DLL with C#"?

We've been here! I am loading SQLite3.DLL dynamically in C#. Reasons:

  • Available wrappers and drivers are statically linked to one or another historical version of SQLite3.
  • I want to be able to choose the version of SQLite3 that I use; I can keep SQLite3.EXE and SQLite3.DLL in sync.
  • There are (were) several versions of SQLite3 on my computer even before I started.

When I started (v 3.34 I think), I could

.read ' "D:/SQLite32/SCRIPTS/Techniques/N Random Records.BAT" tblSalesRevenue 10'

N Random Records.BAT has:

echo SELECT * FROM [%1] ORDER BY RANDOM() LIMIT %2;

I wanted to be able to use all the math functions without loading extensions.

The SQLite3.DLL APIs aren't easy to start with; however, with perseverance, the insights gleaned from this forum and other online resources, it all falls into place quite readily.

ATTACH DATABASE 'c:/sqlite/db/contacts.db' AS contacts;

I am certain that I tried this before posting. (!! did I have SELECT as a prefix !!) Thanks for the help ...

sqlite3_exec(pdb, "ATTACH DATABASE 'c:/sqlite/db/contacts.db' AS contacts;" ... );

.. is what I need (works).

there are limits to how many DBs can be attached,

I understand that the default number is 10 but that it would be 125: correct?

00:51 Reply: Question about UPDATE with LIMIT (artifact: 40d247920b user: anonymous)

thanks a lot! nyl

00:17 Post: Question about UPDATE with LIMIT (artifact: 87db052711 user: anonymous)

we have the following sqlite command that works:

UPDATE feeder SET quantity = quantity + 1 WHERE quantity < 200 AND available = 1 RETURNING id, food_weigth, quantity

but we would like to add a limit 1 since we don't want to update all the feeders

something like (it doesn't work):

UPDATE feeder SET quantity = quantity + 1 WHERE quantity < 200 AND available = 1 LIMIT 1 RETURNING id, food_weigth, quantity

nyl

2021-09-21
23:05 Post: Queries across databases (artifact: 85dd9c3e16 user: anonymous)

I am using SQLite3.DLL with C# (Windows).

How do I achieve

sqlite> attach database 'c:/sqlite/db/contacts.db' as contacts;

using SQL?

In other words, what should be the second parameter of sqlite3_exec to attach a database?

21:11 Reply: Compiling FILEIO.C (artifact: 206c1050cf user: anonymous)

There are 3 NEW blocks of code in the new version, namely, lines [75] - [79], [232] - [247], [1002] - [1030] ... made the difference between compiling success and failure.

Thanks for sorting it out.

I'm curious: why aren't compiled extensions not included in the downloads? Especially FILEIO.DLL as it is built-in SQLite3.EXE but omitted in SQLite3.DLL.

11:36 Reply: get only first record found (artifact: a68e603b7b user: anonymous)

thanks a lot! nyl

06:40 Reply: Compiling FILEIO.C (artifact: 8ffd88d49f user: anonymous)

Repeated the compilation with the new FILEIO.C. Passed. DLL is the same size as with the previous method of compilation.

For clarity, I used the CL syntax given i.e.

CL -Os -DFILEIO_WIN32_DLL fileio.c -link -dll -out:fileio.dll

05:55 Reply: Compiling FILEIO.C (artifact: 26b96453d1 user: anonymous)
  • Repeated the compilation with the new FILEIO.C. Passed. DLL is the same size as with the previous method of compilation.
  • Repeated my tests with the new FILEIO.DLL. Passed.

I noticed that the new FILEIO.C has the same date, namely, 2014-06-13. Should this change?

03:14 Post: get only first record found (artifact: 935e10bd78 user: anonymous)

I am using SELECT to search a value

SELECT * FROM test WHERE available = '1' LIMIT 1

LIMIT seems to still do the work and then discard the extra results but I only need the first record and want to stop SQLITE after that to save resources

00:27 Reply: Suggestion to support gzipped text in vsv extension (artifact: 2f30c54d38 user: anonymous)

Thanks for bringing this topic up. I have also tested it statically linked and everything works as expected.

2021-09-20
17:37 Reply: Database anomaly (artifact: a92c44ec41 user: anonymous)

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?

15:26 Reply: Compiling FILEIO.C (artifact: d164d2a76f user: anonymous)

It is odd to delete fileio.c and no other sources.

I have kept ALL the source files ... which are in a location (E:\SQLite_The Hospital\FILEIO\) other than where fileio.dll is created (D:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\VC\Tools\MSVC\14.29.30133\bin\Hostx86\x86)

14:28 Reply: Compiling FILEIO.C (artifact: 23b67c3846 user: anonymous)

I kept the faith knowing that the detailed instructions would have been tested successfully and persevered. Several hours later I have created FILEIO.DLL

20/09/2021 14:43 148,992 fileio.dll

Larry, I'd be interested to know how the size compares with the size of your FILEIO.DLL.

I have tested this extension and no longer get the aberration I reported The steps I followed are:

Step 1 Start Command Prompt as Administrator

Step 2 Find the location of CL.EXE; the location should have the dependencies mspd*.DLL. In my case, this location is: cd "D:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\VC\Tools\MSVC14.29.30133\bin\Hostx86\x86" That is, I have to be in this directory.

Step 3 Find the batch file that creates the environment wherein CL.EXE can find the *.h dependencies. In my case this batch file is: "D:Program Files (x86)Microsoft Visual Studio2019EnterpriseVCAuxiliaryBuildvcvars32.bat" I ran this batch file which provided this response:

[vcvarsall.bat] Environment initialized for: 'x86'

Step 4

cl -Os "E:SQLite_The HospitalFILEIOutf8_to_wide.c" -link -dll -out:fileio.dll

This provided this feedback:

Microsoft (R) C/C++ Optimizing Compiler Version 19.29.30133 for x86 Copyright (C) Microsoft Corporation. All rights reserved.

utf8_to_wide.c Microsoft (R) Incremental Linker Version 14.29.30133.0 Copyright (C) Microsoft Corporation. All rights reserved.

/out:utf8_to_wide.exe -dll -out:fileio.dll utf8_to_wide.obj Creating library fileio.lib and object fileio.exp

Step 5 Since "D:\Program Files (x86)\Microsoft Visual Studio\2019Enterprise\VC\Tools\MSVC\14.29.30133\bin\Hostx86\x86" is a system location (the reason for running as Administrator) , I need to tidy up.

  • Deleted utf8_to_wide.obj
  • Copied FILEIO.DLL to another (preferred) location
  • Deleted FILEIO.*

Step 6 Tested FILEIO.DLL from my preferred location with success.

Hopefully, this experience will save me (& others) time when compiling other extensions.

Larry, thank you very much..

12:47 Reply: adding record if it doesn't already exist (artifact: f7681f4b84 user: anonymous)

There may be an operational need for such information.

If someone asked you, during an audit, "when was this record added?", "I don't know" may not be an acceptable answer.

07:04 Reply: Compiling FILEIO.C (artifact: 6604272b7d user: anonymous)

Thank you for re-visiting this; sharing your insight is much appreciated.

Attempt 1:

CL -Os utf8_to_wide.c -link -dll -out:fileio.dll

And that takes me straight to the

fatal error C1034: ??.h: no include path set

Finding each *.h file and copying to the location where I have FILEIO.C & other files will take a long time.

Attempt 2:

I created a DLL project in VS2019. I feel that I am nearer to compiling FILEIO.C successfully but not quite there yet. Here's the output:


Rebuild started...
------ Rebuild All started: Project: FILEIO, Configuration: Release x64 ------
FILEIO.C
test_windirent.c
utf8_to_wide.c
Generating Code...
utf8_to_wide.obj : error LNK2005: closedir already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: opendir already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: readdir already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: readdir_r already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: sqlite3_fileio_init already defined in FILEIO.obj
utf8_to_wide.obj : error LNK2005: windirent_getenv already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: sqlite3_api already defined in FILEIO.obj
   Creating library E:\SQLite_The Hospital\FILEIO\x64\Release\FILEIO.lib and object E:\SQLite_The Hospital\FILEIO\x64\Release\FILEIO.exp
LINK : fatal error LNK1561: entry point must be defined
Done building project "FILEIO.vcxproj" -- FAILED.
------ Rebuild All started: Project: FILEIO, Configuration: Debug x64 ------
FILEIO.C
test_windirent.c
utf8_to_wide.c
Generating Code...
utf8_to_wide.obj : error LNK2005: closedir already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: opendir already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: readdir already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: readdir_r already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: sqlite3_fileio_init already defined in FILEIO.obj
utf8_to_wide.obj : error LNK2005: windirent_getenv already defined in test_windirent.obj
utf8_to_wide.obj : error LNK2005: sqlite3_api already defined in FILEIO.obj
   Creating library E:\SQLite_The Hospital\FILEIO\x64\Debug\FILEIO.lib and object E:\SQLite_The Hospital\FILEIO\x64\Debug\FILEIO.exp
FILEIO.obj : error LNK2019: unresolved external symbol sqlite3_win32_utf8_to_unicode referenced in function statTimesToUtc
test_windirent.obj : error LNK2019: unresolved external symbol sqlite3_malloc referenced in function opendir
test_windirent.obj : error LNK2019: unresolved external symbol sqlite3_free referenced in function closedir
test_windirent.obj : error LNK2019: unresolved external symbol sqlite3_stricmp referenced in function opendir
E:\SQLite_The Hospital\FILEIO\x64\Debug\FILEIO.dll : fatal error LNK1120: 4 unresolved externals
Done building project "FILEIO.vcxproj" -- FAILED.
========== Rebuild All: 0 succeeded, 2 failed, 0 skipped ==========

05:35 Reply: adding record if it doesn't already exist (artifact: dd1f0ca1ec user: anonymous)

what about if I want to insert more fields in addition to the email - like a password ?

05:09 Reply: adding record if it doesn't already exist (artifact: 272b73aeb6 user: anonymous)

how do I know if I inserted the record or ignored it ?

05:08 Reply: adding record if it doesn't already exist (artifact: 9f60854f88 user: anonymous)

thank you nyl

02:49 Post: adding record if it doesn't already exist (artifact: 703f3867a1 user: anonymous)

right now we are first searching for email like this:

SELECT id FROM table1 WHERE email = 'test@domain.com'

if this doesn't return an ID then:

INSERT INTO table1 (email) VALUES ('test@domain.com')


instead of doing two DB calls, how do we add the email using EXISTS ?

2021-09-19
21:17 Reply: Compiling FILEIO.C (artifact: 2902c6498d user: anonymous)

I copied sqlite3ext.h & sqlite3.h to the location where fileio.c is.

Now, I'm getting this error:

fatal error C1083: Cannot open include file: 'test_windirent.h': No such file or directory

Compiling is not as straight forward as made out here.

20:30 Post: Compiling FILEIO.C (artifact: f20c913919 user: anonymous)

Following the hints provided here:

  • I obtained the source
  • Trying To compile on Windows using MSVC, a command similar to the following will usually work:

cl YourCode.c -link -dll -out:YourCode.dll

I am running this from the command prompt:

"D:\Program Files (x86)\Microsoft Visual Studio\2019\EnterpriseSDK\ScopeCppSDK\vc15\VC\bin\cl.exe" FILEIO.c -link -dll -out:FILEIO.dll

My Command prompt is at the directory where FILEIO.C is saved; the location contains just the file FILEIO.C

I am getting this error:

fatal error C1034: ?.h: no include path set

Where ? = one file name or another.

I have made NO changes to FILEIO.C. t seems to fulfil the requirements 4. Programming Loadable Extensions.

I'd appreciate some help in overcoming fatal error C1034; thank you.

18:14 Reply: Database anomaly (artifact: a75a3aad27 user: anonymous)

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.

17:32 Reply: Database anomaly (artifact: 4a0f0a06a9 user: anonymous)

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

16:04 Reply: Database anomaly (artifact: 610115e7f5 user: anonymous)

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

15:36 Reply: Database anomaly (artifact: c1987866be user: anonymous)

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

d:\sqlite32\db\ajay.db

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.

15:08 Post: Database anomaly (artifact: 8314c494ec user: anonymous)

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

ajay.db

Then write to a table within it and find

ajay.db��0

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

Thanks.

More ↓