SQLite Forum

Timeline
Login

50 events by user larrybr occurring around 2021-10-22 20:07:18.

More ↑
2021-11-03
16:07 Reply: Get SQLITE_READONLY when using UPDATE command artifact: bf78d1f0cb user: larrybr

(Quoting code fragments as context for response fragments:)

database.cpp file:(in constructor)
m_Main_Db_File = "file:\\Users\\David\\test.db";

It appears that you intend to use a URI database specification. This one is in good order. I prefer (single) forward slashes, as Windows has accepted them since the 16-bit codebase became history.

// Make the sqlite3 temp directory.
LPCWSTR zPath = ApplicationData::Current().TemporaryFolder().Path().data();
char zPathBuf[MAX_PATH + 1];
memset(zPathBuf, 0, sizeof(zPathBuf));
WideCharToMultiByte(CP_UTF8, 0, zPath, -1, zPathBuf, sizeof(zPathBuf), NULL, NULL);
sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf);

If your DB file will be in a writable directory, there is no need (evident here to me) for setting a different temp directory. It's a complication at best.

int result = sqlite3_open(m_Main_Db_File, &ptrMain_Db);

You need to use sqlite3_open_v2() with a SQLITE_OPEN_URI flag.

I moved the database file to my user folder which I should have full permission rights to. Now I get a hex E from result.

Getting a different result should be considered progress. That return is also known as SQLITE_CANTOPEN. As the linked doc indicates, the open failure could be either the main DB file or one of the potential auxiliary files. Eliminating the sqlite3_temp_directory set would help distinguish cases here.

Without the file:, single back slashes which Windows did not like, and forward slashes.

It is not Windows which disliked single backslashes. (It never saw them.) You should study what C/C++ string literal escaping conventions mean and do. A double-backslash becomes a single backslash in the compiled literal.

This is different from when the database was in my project folder.

Yes, and with a little more information that will be a potent clue, I think.

At this point, I suspect your problem is that Windows, when given file:\Users\David\test.db as a pathname, refuses to create a file object under that name. Telling SQLite to treat that text as a URI will keep the "file:" part from being passed to the OS file open API.

In VS I have installed SQLite/SQL Server Compact Toolbox. From this too I can always access and modify any SQLite database. This is how I have been making changes to the database so far. Is this tool causing my problems?

I was unaware that the tool could deal with SQLite databases. (I'm skeptical that it can.) But if you are not keeping a SQLite database open in that tool, I highly doubt it will lead to the problem(s) you see now.

15:18 Reply: Is the order conserved in a table from a VALUES clause? artifact: 2c57e13d27 user: larrybr

I almost hate to spoil the fun here, but ...

Thanks for your input Larry - I totally get that relying on undocumented behaviour is not a good strategy!

The thrust of my argument now is that the current behaviour should be fixed and documented.

Yes, I understood that.

What is documented is that an ORDER clause enforces some kind of ordering. If feasible, it will be optimized away where the implementation naturally (in its current evolution) achieves the specified ordering. You can rely on that.

The extra guarantee you seek/suggest is highly unlikely to be made by the present SQLite project. In part this is because VDBE code generation is complex enough without imposing requirements which are contrary to long-standing SQL convention. And, I venture, further cause to decline your invitation is that it would weaken the message: If you want ordering, use ORDER BY. The docs have been quite clear that, absent an ORDER BY clause, no ordering is guaranteed. I do not foresee an ever-growing list of exceptions appearing with those warnings.

This stance on ordering is especially important for input to the windowing functions, where ordering (or misordering) is baked into the result rather than merely affecting result ordering.

2021-11-02
23:13 Reply: Get SQLITE_READONLY when using UPDATE command artifact: a691e55d81 user: larrybr

I think it's time to show your sqlite3_open() call.

It would help rule out a whole category of problems if you were to temporarily substitute for your DB filespec a pathname which you are quite certain can be written by whatever user is getting your unexpected SQLITE_READONLY return. If that continues through the substitution, we can focus on your call. Otherwise, the investigation needs to examine file permissions.

21:23 Reply: sqlite3_threadsafe() return value artifact: b1f79e2c43 user: larrybr

This should be clearer. I chose less verbiage than you or Keith suggest because the immediate context would render it surplusage. Please feel welcome to voice reasoned disagreement with this opinion.

20:12 Reply: sqlite3_threadsafe() return value artifact: 2cca976410 user: larrybr

I read the allegedly incorrect doc quote, saying "The sqlite3_threadsafe() interface predates the multi-thread mode and start-time and run-time mode selection and so is unable to distinguish between multi-thread and serialized mode ...", as entirely consistent with the fact that it returns a constant set at compile time. The actual running mode, as set at runtime via pragma and possibly limited by compile-time preprocessor variables, can vary for a given instance of the library code, whereas the return from sqlite3_threadsafe() cannot.

I am inclined to say there is no doc bug here. However, the evidence suggests a better wording might be be clearer to you and perhaps others. Would you care to suggest something specific?

17:54 Reply: Is the order conserved in a table from a VALUES clause? artifact: ed4938d840 user: larrybr

You are clear that this may change in the future? However, I can rely on this functionality for versions up till now?

You can rely on currently released versions to continue their present behavior. (There are no manipulations of history in the source archives.)

Anybody is welcome to rely on undocumented, implementation-defined behavior. They do so at their peril with respect to continuation of such behavior in future or other as-yet-untested releases.

17:28 Reply: v3.36.0 fails to compile under Ubuntu artifact: 7f36fcaa10 user: larrybr

Dan Kennedy has fixed this build problem with this checkin.

00:53 Reply: sqlar : how to remove files artifact: 68d015f77d user: larrybr

Perhaps you or others interested can try breaking the archive_remove branch tip. Its name should be archive_remove_and_glob now. The archive content is subject to glob matching for --remove, --list and --extract subcommands. Glob matching for incoming files (--insert and --update) may come later.

2021-11-01
23:25 Reply: Unable to open the connection artifact: e3192cf089 user: larrybr

I would like to keep the DB created outside the application context @ c: drive so that multiple process can access it .

I do not understand this "would like". To me, it is as if you said, "I would like the database to exist in a file so that more than one process can access it." To that, the answer is: "That's what happens when you name a file to the open API." This is so obvious that I think you must mean something else, but I cannot fathom what that is.

Able to create the connection and get connection object but unable to open connection.

It would help if the meaning of "create the connection", "get the connection object" and "open connection" did not have to be guessed. What APIs are you calling, in what order, and with what effect?

I tried read/write access to that drive.

What does "drive" access have to do with this? Can we restrict discussion to a specific file that you name as the database to an SQLite API that expects one?

18:59 Reply: sqlar : how to remove files artifact: 03ba9de2ef user: larrybr

I considered using glob(x,y), and may have it in before the merge to trunk. Because feature freeze is imminent, I elected to defer that nicety in favor of leveraging existing code to process a known file list, without changing it. I expect to get globbing in later. Whether I can get it working and properly tested before v3.37 feature freeze is uncertain, so I want to be sure to get the basic function in first.

17:53 Reply: sqlar : how to remove files artifact: 42ef352bd1 user: larrybr

Perhaps that feature should be backported.

The feature is going into the 3.37 release.

2021-10-29
14:02 Reply: Request: Allow VACUUM INTO with a new page size even in WAL mode artifact: 924c0a71e2 user: larrybr

Richard has implemented your suggested behavior, in a similar manner.

13:05 Reply: Insert operation leads to exception on Windows when installed using msi artifact: 9297a20a9f user: larrybr

Your application, (identity of which remains a mystery), is clearly doing something which requires permission(s) not given to some ordinary users.

The SQLite project does not publish any application which uses either ".msi" installation or exception-style error handling. Your issue is with the mystery application and should be taken up with its vendor, author or publisher. Or you need to install it differently. (I can only guess on this.)

12:57 Reply: Assistance with using SQLite in PAMGuard artifact: e49140200b user: larrybr

[lots of PAMGuard-specific moans] because database TowST could not be resolved. ...

Does this shed any light on what my issue could be?

It sheds the same light as your previous post which appeared to show that "the database" of which you spoke was not well defined. That is why I advised: you need to specify what, specifically, you wrote/did/clicked etc. to designate an actual database file for the SQLite library to open and operate upon.

A problem we often see in this forum is this: A user specifies a filename for the database file but does not know where in their filesystem(s) that file actually resides. Then they get confused because their program's effect(s) upon "the database1" cannot be observed when they use another tool to examine "the database2", which happens because database1 is a different byte sequence than database2.

Did you specify an absolute path as "the database"? If not, your problem is highly likely to be the same above-described one we see here often. Until I see an answer to this question, I will assume you are suffering that same problem. The solution is to specify an absolute path to PAMGuard for its database, then specify the same absolute path to whatever other tool you use to observe PAMGuard's output data.

2021-10-28
19:53 Reply: --skip option does not work correctly. artifact: 90f8d99465 user: larrybr

Thanks for reporting this. And thanks to Harald for making the problem clearer.

This is fixed here.

13:11 Reply: Database on android SD CARD artifact: e14107dabe user: larrybr

The reason for not wanting to root is that I wanted to develop an application / extension that others could easily use without rooting their phone. If it were just for me, fine, but if I want others to use it who may not be as enthusiastic about rooting, wouldn't it be great if it could work?

I do not question your motivation for wanting to avoid rooting a phone. I just don't think that's a constraint that is going to be flexible during troubleshooting or allow you to do the experiments that would allow you to figure out what the relevant factors are that lead to today's problem.

When you said its a clue that the comment noexec is relevant, How can you say that when the permission says RWXRWX--- Doesn't that say that the base user should be able to execute?

I saw the difference between "RWXRWX---" and "rw rw ---" (on your "emulated storage") as significant and related to the "noexec" mounting. It suggests that the 'x' bit is less significant (with respect to today's problem) than you seem to think.

The per-file execute permission is separate from the effect of the noexec mount option. I would not expect the per-file permission bits to reflect whether the block device was mounted with the noexec option. (They do not on Linux, and I'm too lazy to check this on other Unix-like systems.) They do not mean that that, once loaded, program image text segments from the file will be physically executable. IOW, the 'x' bits do not mean quite what you think they mean.

While you are likely right that my issue is with my environment and not with SQLite, my hope is that other SQLite enthusiasts may have bumped up against this issue and be able to help with how to solve it.

I do not hope to disabuse you of your hope(s). My recommendation of a different forum was intended to improve your odds of success and help keep this forum on-topic. Other Android developers are much more likely to have the pertinent knowledge, and on an Android forum they are less likely to regard the issue as off-topic which will also improve your odds of getting a useful answer.

2021-10-26
21:46 Reply: Database on android SD CARD artifact: 605fa14ffe user: larrybr

On my SD card the permissions were RWXRWX--- but on my emulated storage its rw rw ---

That's a decent clue that Stephan's "noexec" tip is pertinent.

I selected the 20 FE phone specifically for the SD card so I could do this project without rooting my phone.

The "cannot root phone" constraint is not one likely to be catered to.

I'm on the verge of giving up on SQLite even though I really really want it to work!!

If wishes were fishes, ...

What makes you think this has anything to do with SQLite, other than that it is ultimately executed (like anything else running) and your environment is blocking execution?

You should go to an Android development forum to get advice on your problem. You have a platform issue rather than a SQLite issue.

17:22 Reply: Get SQLITE_PROTOCOL error when opening database artifact: 0de0b94877 user: larrybr

It seems odd to me that you abandon code with simple, identified errors in favor of courting new ones. Leaving that aside ...

Also leaving aside the wisdom of throwing an exception for failure to .open() ...

What is the absolute path which your "db.sqlite3" filename resolves to? Or, what is the process current directory when the code you now have is run? Is the directory in which you try to create a database file one which you can modify without unusual permissions?

As part of diagnosing your trouble, I suggest setting m_szFile to name a file in a a directory you can create files in. It should contain the whole path, starting from a filesystem root. That way, the process current directory will not matter.

16:18 Edit reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: bf94df725a user: larrybr

Perhaps the CLI application can be modified to display the extended error code as well as the text. ...

This seems like a good idea to me, and fairly simple to implement.
(Edit resulting from discussion with dev team:)
Although it's a feature change, it is trivial enough to be included in v3.37 (unless my perception of triviality is way off.)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

(Correction via edit regarding silence upon stepping error:)

At present, any error returned from sqlite3_step() is treated as the end of stepping, then reported like others. Given the potential locking issues raised in this thread, indicating this also seems worthwhile.

15:30 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: 37f63aff57 user: larrybr

You do not need "file" or me to tell you that is not the beginning of a SQLite3 database file.

14:50 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: 05bded67c9 user: larrybr

In other words, it is of type "Unrecognized". The "file" utility has known of SQLite databases for awhile now. But you need not rely on it. The first 16 bytes of a SQLite3 database are as stated earlier in this thread. What does 'od -c your.db | head' say?

14:45 Edit reply: Get SQLITE_PROTOCOL error when opening database artifact: 629f9bdd62 user: larrybr

The object known as "m_dB" does not have a value which can sensibly be passed to sqlite3_open(). This is a fundamental problem, unrelated to Win10 permissions.

(Added via edit:) Also, your use of sqlite3_temp_directory represents a memory leak.

14:42 Reply: Get SQLITE_PROTOCOL error when opening database artifact: d3de6254b8 user: larrybr

The object known as "m_dB" does not have a value which can sensibly be passed to sqlite3_open(). This is a fundamental problem, unrelated to Win10 permissions.

13:47 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: 29f056d0c7 user: larrybr

Perhaps the CLI application can be modified to display the extended error code as well as the text. ...

This seems like a good idea to me, and fairly simple to implement. I think it's a v3.38 feature though because of the current state of v3.37 development.

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

At present, any error returned from sqlite3_step() is silently treated as the end of stepping. Given the potential locking issues raised in this thread, that seems like a behavior worth changing. That will necessitate some modest internal restructuring which definitely makes it a post-v3.37 task.

2021-10-22
20:07 Reply: Documentation bug regarding max SQL length artifact: 992d12f58e user: larrybr

As you say, it is just a doc issue. The discrepancy is now corrected. Thanks.

2021-10-21
14:43 Reply: Inconsistency in BETWEEN Query artifact: 9deb80dec8 user: larrybr

If you expect your text values to be compared as integers, you need to write something like CAST( my_text_resembling_a_number AS INT ) for each such value. The inequality operators (which set includes BETWEEN) will not do it for you.

00:17 Reply: How do i submit a bug report artifact: cc490e5902 user: larrybr

CSR:

The "lemon" program converts a structured description of a grammar which has actions associated with specified grammar constructs into a C function which is able to parse an instance of the grammar and perform the actions corresponding to various constructs in that instance.

The C function so generated from SQLite's SQL grammar is used in SQLite to process SQL passed into the prepare_statement() APIs.

Of course, the grammar description which Lemon is called upon to convert into a parser when SQLite is built does not contain anything like what you or other "Security Researchers" devise to expose so-called vulnerabilities in the lemon parser generator.

If you really want to learn about lemon, you can peruse The Lemon Parser Generator to your heart's content. This document can be found under "Lemon" in the website keyword index. I urge you to consult that index first when you have questions regarding SQLite. Much effort has gone into keeping the online docs current and accurate, so it should be your first source of answers. (And if something you cannot find there ought to be there, that is a fact worth bringing to the dev team's attention.)

2021-10-18
18:23 Reply: SQLite Page Cache Subsystem as Library artifact: dd608140b2 user: larrybr

Have you studied SQLite As An Application File Format?

You should be (or become) aware that SQLite's ACID attribute is not resting on the page cache system. Rather, it is the use of that system along with other means that enables such functionality.

2021-10-12
03:28 Reply: Compiling sqlite system on Ubuntu artifact: 6d057c6677 user: larrybr

sudo gcc shell.c sqlite3.c -lpthread -ldl -lm

There is no need to use sudo for this.

but all I get is the a.out file.

The command, mv a.out sqlite3 , will rename the executable you got to what you wanted (I suppose.) Alternatively, replace "gcc" with "gcc -o sqlite3" in that compilation to get what you seem to expect.

last thing I did was was run ./configure and make, ... but when I do 'which sqlite3', there is no output.

The current directory is typically not among the directories listed in $PATH. Hence, 'which' does not find named executable files that happen to be in the current directory unless the current directory is so listed. Once you get yourself a properly named 'sqlite3' executable, the final step will be to place it somewhere such that it will be found by the shell (using $PATH) when invoked simply as 'sqlite3'.

2021-10-11
04:30 Reply: Updates to 22.1. The --safe command-line option artifact: cbed526cae user: larrybr

Thanks for the spelling pickup. The docs will update soon to reflect a correction.

is .shell disabled in --safe?

Yes, as is .system and anything that allows output to be written or shells to be run.

2021-10-10
03:48 Reply: Understanding memory allocation behavior of SQLite artifact: 41db6d8ce5 user: larrybr

Without gainsaying anything earlier, I wonder about this:

Alternatively, loading the data in sorted order and then creating the additional index (after the data is loaded) also works and is the only alternative for a WITHOUT ROWID table to avoid B-Tree rebalance operations.

I would think that the insert order would need to be close enough to or match a breadth-first traversal of the ultimate (as) balanced (as it's going to get) B-tree. Otherwise, lots of rebalancing will be needed because, ultimately the ordering will match a depth-first traversal but insertion in that order would necessarily create imbalances. (I'll be happy to be educated on this if I've got it wrong.)

2021-10-07
02:36 Reply: Assistance with using SQLite in PAMGuard artifact: f55162a25c user: larrybr

Without being a PAMGuard user, I can still suggest some clarifications that will help others (who might be PAMGuard users) to sort out what is going wrong for you. My comments follow quoted fragments:

I have loaded an SQLite database module into PAMGuard,

Is an "SQLite database module" the same as an "SQLite database"? Or are you simply saying that you have enabled PAMGuard to access SQLite databases generally?

when the analysis is finished, opeing the database in SQLite Studio shows no data

Here, it will be crucial to detail what "the database" means. We can assume that you intend to refer to a database that you intended to access from within PAMGuard, but you need to specify what, specifically, you wrote/did/clicked etc. to designate an actual database file for the SQLite library to open and operate upon.

shows no data (all fields are empty)

Are the expected tables present? Do you mean that no rows were inserted when they should have been? Please use conventional terminology, where rows in a table correspond to tuples of the column values. (The term "field" often means a member of such a tuple. Is that your usage here?)

even when I can see the detector modules are making detections.

If you were to briefly explain the importance or consequence of this in terms of resulting modifications upon the actual database opened by PAMGuard, folks here who are not PAMGuard experts may be able to help.

I have also attempted opening the SQLite database in PAMGuard Viewer after PAMGuard has finished running, but all of these fields are also empty.

To me, (a PAMGuard non-user), this means one of two things: (1) the effects of your PAMGuard run on the database it actually opens are not what you are expecting; or (2) the database being modified by your PAMGuard run is not the same one you are peering into with other tools (including "PAMGuard Viewer".) I do not see enough information in your post to distinguish these cases.

suggestions as to why I am not able to view the output in my SQLite database?

(Not to be flippant here, just analytical:) Either "the output" is an empty set, or it is landing somewhere other than "[your] SQLite database". Have you run an experiment where you use PAMGuard the same way, except with a different DBMS where "the database" unambiguously refers to something you can view with other tools? Or put differently: Why do you believe "the output" is not the empty set?

2021-10-06
17:58 Reply: journal file created when journal_mode=OFF artifact: 914b9686f6 user: larrybr

I have a few suggestions:
1. Find a way to avoid using the count_changes pragma. It is deprecated.
2. Reduce your scenario to a set of meta-commands and SQL statements that the CLI shell can run.
3. Read the docs for the members of the step 2 set.
4. Post here the minimal repro set of step 2 and remaining questions.

Time ordering of operations will have an effect upon the outcome, and is not apparent in your post. So please consider my 4-step procedure to be a good way to clarify what you are doing, (if this proves necessary after doing step 3.)

16:08 Reply: doc nit for sign() artifact: 6514f13119 user: larrybr

Thanks for the nit report. This has been fixed (redundantly.)

2021-10-05
17:52 Reply: Double section number artifact: 9d923b5c5e user: larrybr

Thanks for the tip. Fixed on doc trunk.

17:38 Reply: Utility extensions (Windows-only) artifact: b6ee340328 user: larrybr

Thanks for sharing (almost.) It could well be useful.

The link has ...

I could find no link.

2021-10-03
02:18 Edit reply: Strange parsing problem in the shell artifact: 73876f06fa user: larrybr

... I don't buy the hypothesis that my problem arises from a failed pipeline.

The misbehavior was not caused solely by the broken pipe issue. It was due to a bug which was exposed by a broken pipe among other scenarios. That bug was fixed today, by this checkin.

Can you try it?

2021-10-02
22:25 Reply: Strange parsing problem in the shell artifact: 8ffb52506e user: larrybr

I think this poor behavior was fixed by this checkin today.

Can you try it?

15:44 Reply: bug? Unexpected CLI shell comment handling artifact: c2984b0e75 user: larrybr

Kees: Please disregard my earlier comments about a behavior-changing revision. The behavior to which you were accustomed and the intended current behavior were identical. (This can be seen in the code, with some study.) The issue is that I allowed a simple bug to creep in during a recent revision made to avoid an O(N^2) performance degradation with huge block comments.

The current trunk version should behave more satisfactorily with that script of yours which recently failed.

14:44 Edit reply: bug? Unexpected CLI shell comment handling artifact: 400c214193 user: larrybr

(Edited to describe a change under consideration which addresses this behavior.)

The CLI's line collection process applies just a few rules, which can be summarized as:

  1. If the line begins with '.' and is the first since the last SQL or meta-command was processed, then it is a single-line meta-command and processed as such.

  2. If the line ends with ';' followed by nothing but whitespacea and is not within an SQL construct within which that character is considered part of the construct rather than a statement terminator, then the line together with its predecessors (that were not meta-commands) is submitted for SQL execution. (prepare, step with output, finalize)

In your example, the "--" which begins the line, per all SQL standards, makes that and the remainder of the line, whatever it may be, a comment.

It would have to be a special cased handler that would treat ';' as a SQL terminator only if it appears at the end and outside of all lexically delimited constructsb except end-of-line comments.

I do not see how the present behavior is weird. Should an end-of-line semicolon within a block comment also terminate the SQL to avoid this weirdness?

(Added via edit:)
This additional summarized rule would cure your script problem and should be harmless in all circumstances I am imagining:

3. As non-meta-command lines are accumulated, at any time that they constitute a complete SQL construct which is nothing but whitespacea the accumulation is discarded, the primary prompt is issued (if in interactive mode), and line processing reverts to the state where either meta-commands or SQL (finally ending with ';') are accepted.

Comments on any difficulties arising from this approach are more than welcome.


a. In this context, "whitespace" includes the usual not-dark characters and SQL comments.

b. The "lexically delimited constructs" are quoted strings and identifiers, and end-of-line or block comments.

14:03 Reply: bug? Unexpected CLI shell comment handling artifact: 82b2656710 user: larrybr

The CLI's line collection process applies just a few rules, which can be summarized as:

  1. If the line begins with '.' and is the first since the last SQL or meta-command was processed, then it is a single-line meta-command and processed as such.

  2. If the line ends with ';' followed by nothing but whitespacea and is not within an SQL construct within which that character is considered part of the construct rather than a statement terminator, then the line together with its predecessors (that were not meta-commands) is submitted for SQL execution. (prepare, step with output, finalize)

In your example, the "--" which begins the line, per all SQL standards, makes that and the remainder of the line, whatever it may be, a comment.

It would have to be a special cased handler that would treat ';' as a SQL terminator only if it appears at the end and outside of all lexically delimited constructsb except end-of-line comments.

I do not see how the present behavior is weird. Should an end-of-line semicolon within a block comment also terminate the SQL to avoid this weirdness?


a. In this context, "whitespace" includes the usual not-dark characters and SQL comments.

b. The "lexically delimited constructs" are quoted strings and identifiers, and end-of-line or block comments.

02:47 Edit reply: Strange parsing problem in the shell artifact: 74b6cca12c user: larrybr

This is a bit odd: ...

Here is a screen-scrape, just now taken: larry@Bit-Booger:~/SQLiteDev/LibTrunk$ ./sqlite3 SQLite version 3.37.0 2021-10-01 22:48:52 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .import -csv "|iconv -f L1 < pets.csv | grep '^M' | cat -" incoming sqlite> .header on sqlite> select * from incoming; Moses|dog|12 Meowser|cat|17 sqlite>

I adjusted your inconv from encoding to reflect ones listed via -l, and replaced your "tail -r" with something that does not break the pipe (as happens with Ubuntu 20.04's tail which knows not the -r option.)

However, using an equivalent to your posted .import pipe extravaganza, I get: sqlite> .import -csv "|iconv -f l1 < pets.csv | grep '^\"20' | tail -r" incoming tail: invalid option -- 'r' Try 'tail --help' for more information. <pipe>: empty file sqlite> select * from sqlite_schema; ...> ; ...>

This appears to replicate your problem. There is a simple, near-term solution: Do not subject the CLI's piped-input-accepting commands to broken pipes. Meanwhile, I will investigate why such expectable input produces this strange result.

(A 2nd work-around appended via edit:)

It seems that output reaching a shared stderr stream may be involved in this problem: sqlite> .import "|cat /dev/null" junk <pipe>: empty file sqlite> select * from sqlite_schema; sqlite> .import -csv "|iconv -f L1 < pets.csv | grep '^M' | tail -r 2>/dev/null" incoming <pipe>: empty file sqlite> sqlite>

Note that an empty input pipe, by itself, does not trigger the misbehavior. It can be avoided by merely diverting the pipe-breaker's moaning away from the same stderr stream the CLI is using. So that would be another short-term work-around.

02:37 Reply: Subclassing & private data artifact: 5f60195332 user: larrybr

Actually, no. I'm wondering how do I write my code as to be forward compatible with future versions of Sqlite that may add new members to the structs without breaking my code written for the current versions.

If the SQLite library were to change the definition of the leading (or not yet "subclassed") portion of the structs it uses at the extension interfaces, it would break a great many extensions compiled against earlier versions of that publicly exposed data. This kind of backward incompatibility is carefully avoided by the project's management and the developers generally.

As I mentioned in my OP, C is not my forte and from what I saw so far, C has the convention of having a field in a struct to report the size of the structure or in Sqlite's case, the version supported.

Neither of those common practices is a C convention. You accurately perceive what the practice accomplishes. If the library designer(s) had elected to leave room for later, changed versions of those structs, a member named something like sizeof_public_portion might well have been used. However, along with such changed data structure would come changed requirements as to what is done with it or perhaps changed allowance as to what the extension might do with it. Those intriguing options for change are closed off by the API stability guarantees that are part of the library's interface.

How will I know that I won't be writing code that will break in a future version of Sqlite?

The same way that you know sqlite3_open() will do the same thing next decade as it does today.

01:27 Edit reply: Strange parsing problem in the shell artifact: 918f8cbb46 user: larrybr

This is a bit odd: ...

Here is a screen-scrape, just now taken: larry@Bit-Booger:~/SQLiteDev/LibTrunk$ ./sqlite3 SQLite version 3.37.0 2021-10-01 22:48:52 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .import -csv "|iconv -f L1 < pets.csv | grep '^M' | cat -" incoming sqlite> .header on sqlite> select * from incoming; Moses|dog|12 Meowser|cat|17 sqlite>

I adjusted your inconv from encoding to reflect ones listed via -l, and replaced your "tail -r" with something that does not break the pipe (as happens with Ubuntu 20.04's tail which knows not the -r option.)

However, using an equivalent to your posted .import pipe extravaganza, I get: sqlite> .import -csv "|iconv -f l1 < pets.csv | grep '^\"20' | tail -r" incoming tail: invalid option -- 'r' Try 'tail --help' for more information. <pipe>: empty file sqlite> select * from sqlite_schema; ...> ; ...>

This appears to replicate your problem. There is a simple, near-term solution: Do not subject the CLI's piped-input-accepting commands to broken pipes. Meanwhile, I will investigate why such expectable input produces this strange result.

(A 2nd work-around appended via edit:)

It seems that output reaching a shared stderr stream may be involved in this problem: sqlite> .import "|cat /dev/null" junk <pipe>: empty file sqlite> select * from sqlite_schema; sqlite> .import -csv "|iconv -f L1 < pets.csv | grep '^M' | tail -r 2>/dev/null" incoming <pipe>: empty file sqlite>

Note that an empty input pipe, by itself, does not trigger the misbehavior. It can be avoided by merely diverting the pipe-breaker's moaning away from the same stderr stream the CLI is using. So that would be another short-term work-around. sqlite>

01:07 Reply: Strange parsing problem in the shell artifact: 2bbac15098 user: larrybr

This is a bit odd: ...

Here is a screen-scrape, just now taken: larry@Bit-Booger:~/SQLiteDev/LibTrunk$ ./sqlite3 SQLite version 3.37.0 2021-10-01 22:48:52 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .import -csv "|iconv -f L1 < pets.csv | grep '^M' | cat -" incoming sqlite> .header on sqlite> select * from incoming; Moses|dog|12 Meowser|cat|17 sqlite>

I adjusted your inconv from encoding to reflect ones listed via -l, and replaced your "tail -r" with something that does not break the pipe (as happens with Ubuntu 20.04's tail which knows not the -r option.)

However, using an equivalent to your posted .import pipe extravaganza, I get: sqlite> .import -csv "|iconv -f l1 < pets.csv | grep '^\"20' | tail -r" incoming tail: invalid option -- 'r' Try 'tail --help' for more information. <pipe>: empty file sqlite> select * from sqlite_schema; ...> ; ...>

This appears to replicate your problem. There is a simple, near-term solution: Do not subject the CLI's piped-input-accepting commands to broken pipes. Meanwhile, I will investigate why such expectable input produces this strange result.

2021-10-01
23:43 Reply: MicrosoftODBC Driver Manager Data source name not found and no default driver specified error when use VBA connect Sqlite artifact: 62a0fff8a0 user: larrybr

I have used Werner's ODBC driver for SQLite many times, including in an application and as a plug-in with DBMS tools. It works well, and is a good citizen among ODBC drivers insofar as its management by the ODBC infrastructure works. So the locus your problem is within the set of things you are doing or should be doing.

Your problem is not a SQLite issue and it is off-topic here. Furthermore, you are much more likely to get a useful answer by consulting ODBC documentation and posing your problem somehwhere that ODBC issues are discussed and participants are familiar with ODBC infrastructure issues.

23:31 Reply: Is the lifetime of argv in sqlite3_module::xConnect greater than sqlite3_module::xFilter? artifact: 0aff2778dc user: larrybr

If I have a SQLITE virtual table, can I store a direct pointer value from argv (const char*) inside sqlite3_module::xConnect to use later in a call to sqlite3_module::xFilter? Specifically the address of the module name inside argv[0].

My study of the relevant docs shows them to be silent on the issue of argument lifetimes. So your question has several answers of varying utility.

I just need to know whether the lifetime of the module name is guaranteed for the life of the Virtual Table, or do I need to deep copy it.

I find no such guarantee, and I was looking for it carefully. I would think that means the usual guarantee applies: The passed argument values (whether indirect or not) can be relied upon during execution of the call. No longer lifetime is guaranteed.

(It seems to work).

Lots of things doomed to fail one day do not do so conveniently soon. That can be seen as bad luck or as a design failure.

I looked at the implementation calling into that xConnect method, and it looked like no needless copies were made. Whether that will change I dare not predict. So it appears that the same pointer you provided earlier is reaching your xConnect method. Is that so? And if so, I suggest, (plagiarizing): You've got to ask yourself one question: 'Do I feel lucky?'.

22:57 Reply: create taable with syntax error works ? artifact: 12735ae7ec user: larrybr

So you should use [STRICT] if it is too hard to remember to separate your column definitions as SQL requires.

This sort of left handed jab really isnt necessary

It was not meant as a jab. It can be hard sometimes to read code closely enough to realize what it means, even when one is fully familiar with the language being used. (I say this from experience.) Let's try that another way:

If you dislike having to keep in mind that SQLite will slurp up a long sequence of words as a typename, you will probably appreciate the STRICT feature.

I was just surprised that there wasnt any apparent error warning or any other indication that I had missed a single comma Every other DB ...

The fact that you would submit such a sequence to the SQLite compiler, and expect something different, shows that your expectations developed from work with other DBMSs need some adjustment, at least until you qualify table definitions with STRICT. That's the practical response rather than railing about SQLite behavior that is decades old and certain not to change. I am sympathetic about the feeling of having a well-entrenched expectation found wanting, but it's the price to be paid when one chooses to not become familiar with one's tools, hoping assumptions about them will be enough.

22:33 Reply: Subclassing & private data artifact: efb6b532ec user: larrybr

However, I wonder how versioning is supposed to work. Some implementation will put in a size parameter to size the structure as to infer the version of the struct. Without that information, a new version of struct could potentially read private data from the old version and therefore garbage. But I don't see such member in sqlite's structure. Does the member iVersion of the struct sqlite3_module handle this?

I'm reasonably sure that the iVersion member is intended to deal with attempts to use extensions written for SQLite major version 3 with some later version(s). In other words, it addresses possible changes in the extension host.

The problem you are wondering about is how to deal with changes in your own code. (I presume you are writing such, or contemplate doing so.) For that, you probably need a mechanism that is available even before attempting to "attach" an extension after it is loaded. And that mechanism will be one you devise or model after similar solutions. This is a non-trivial problem, especially with dynamically loaded code. And the solutions vary according to how much and what kind of change is anticipated. You should not expect a ready-made solution to that general problem in the SQLite library or project.

I say this to perhaps put an end to your wondering how that problem is already solved. It is not.

You may recognize, in the pre-defined structs for various extension types, the polymorphism that is available in C++ and other fully OO languages. Typically, that is utilized purely through variation in what the methods do, without relying on varying data being somehow dealt with at the interface. I suggest that this sort of discipline will simplify how you might insulate your extension design from implementation changes or behavior evolution at the interface.

18:23 Reply: Strange parsing problem in the shell artifact: aac444acc7 user: larrybr

Upon closer reading, I agree. BTW, I have exercised pipe-style meta-command input quite a bit and have not seen the sort of strangeness you reported. So I look forward to seeing what is special about your input.

More ↓