Re: [sqlite] Boolean values in variable, tclsqlite3

On 19 Jul 2019, at 9:15pm, Gilles Pérez wrote: > Is it possible in Tcl to specify I want a boolean? You don't want a boolean. SQLite doesn't understand booleans. You want integers. Do this Constants: DB_FALSE = 0, DB_TRUE = 1 I would suggest you don't use names like SQLITE_FALSE

Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

On 18 Jul 2019, at 5:02pm, Alexandre Billon wrote: > INSERT INTO sales ("client", "salesman", "revenue") > VALUES ('C1', NULL, 10.0); The value NULL in SQLite means 'value unknown'. It is a special case. If you compare two NULLs SQLite will act as if those values are different.

Re: [sqlite] how to delete BLOB object from the data base

On 15 Jul 2019, at 8:38pm, Zhu, Liang [AUTOSOL/ASSY/US] wrote: > I am getting the SQLite_locked error on delete, and my data from the select > statement are junk. I think you are saying that you get the result SQLITE_LOCKED. To do this you must be A) Using one database connection for two

Re: [sqlite] Safe saving of in-memory db to disk file

On 14 Jul 2019, at 6:05pm, ardi wrote: > I have read the backup API page (https://www.sqlite.org/backup.html) > that shows how to read a sqlite db from disk to memory, and how to > save it back to disk, but it doesn't talk about the topic of > performing the save in a safe way. This is a

Re: [sqlite] Bug report: crash when close blob handle after close_v2 db

On 14 Jul 2019, at 11:18am, Chaoji Li wrote: > This problem is only present for 3.28+. A sample test case is attached. Thank you for identifying this behaviour. I'm sure the development team will reply to your post. Attachments are automatically ignored by the mailing list. You can include

Re: [sqlite] Grammar police

Folks, lets return to charter, please. DRH is writing the document. He gets to pick the language to be used. You want something else, write your own. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Quirks of SQLite. Was: Version 3.29.0

On 11 Jul 2019, at 3:21pm, Richard Hipp wrote: > EVERYONE: If you have personally experienced some unusual or > unexpected feature of SQLite that you think should be added to > "quirks.html", please follow-up to this thread, or send me private > email, so that I can consider adding it. A)

Re: [sqlite] How lock is implemented upon write?

Here's my guess. OP is trying to implement locking in Python. OP sees that SQLite does locking and wants to copy code. Obviously, that's beyond the range of this mailing list, but just to be helpful, here's some stackoverflow:

Re: [sqlite] How lock is implemented upon write?

You might want to take a look at the standard VFSen: At a low level, SQLite depends on the VFS for reliable locking. The main parts of SQLite call a routine supplied by the VFS. The VFS does the actual locking. If we didn't answer

Re: [sqlite] the sqlite3 documentation would be pretty good if itwasn't tragic...

On 26 Jun 2019, at 5:58pm, a...@zator.com wrote: > I understand and empathize absolutely with the O.P. and must add that in the > documentation of SQLite, I have always missed examples and comments that, for > example, can be found in the PHP doc. Likewise. The PHP system of documentation,

Re: [sqlite] misuse of aggregate function max()

On 21 Jun 2019, at 10:29am, ingo wrote: > Is this because max() is not deterministic, > or because current_timestamp is not, > or both? The max(). For the reason you identified: it would have to recalculate the index values for all rows in the table every time any of the rows changed. You

Re: [sqlite] SQLITE_LOCKED means "database table is locked"?

On 19 Jun 2019, at 3:39pm, Carsten Müncheberg wrote: > /* SQLITE_BUSY*/ "database is locked", > /* SQLITE_LOCKED */ "database table is locked", With two different connections, either by the same program or different programs/computers, you will see SQLITE_BUSY . SQLITE_BUSY is

Re: [sqlite] Please explain multi-access behaviour to me

On 18 Jun 2019, at 2:11pm, Thomas Kurz wrote: > So have I understood correctly, that in an application, this kind of > SQLITE_BUSY handling is sufficient: > > BEGIN > UPDATE #1 > SELECT #2 > UPDATE #3 > COMMIT <- check for busy here and retry only the commit on failure I believe that the

Re: [sqlite] Please explain multi-access behaviour to me

On 18 Jun 2019, at 4:19am, Keith Medcalf wrote: > See https://sqlite.org/lockingv3.html > for how transactions and locks work in journal_mode=delete|persist|truncate > (ie, not WAL). > There is a link on that page to how transactions work when WAL is in effect. > > Note that the default

Re: [sqlite] Please explain multi-access behaviour to me

I think I see my error. I thought that the lock was promoted from read to read/write when the INSERT command was processed. At this point, SQLite knows that it is going to need to write. Instead, although SQLite knows that it is going to have to write, it does not try to promote the lock

Re: [sqlite] Please explain multi-access behaviour to me

On 18 Jun 2019, at 1:09am, Igor Tandetnik wrote: > A connection doesn't need to check locks on every statement - only when it > tries to spill to disk, most commonly during commit. I think I understand what you wrote. So the bit of my program can think that its changes were written to the

[sqlite] Please explain multi-access behaviour to me

Can someone please explain this error message to me: Simple database, journal mode set to 'delete', accessed by two simultaneous sessions running the SQLite command-line shell, SQLite version 3.28.0 2019-04-15 14:49:49 Session A: PRAGMA journal_mode; <-- says 'delete' CREATE TABLE

Re: [sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

On 17 Jun 2019, at 9:14am, Dominique Devienne wrote: > SQLite4's LSM backend is now an extension in SQLite3 called LSM1 > https://www.sqlite.org/cgi/src/dir?ci=trunk=ext/lsm1=tree > > Which has been discussed in this list before. > Few people are using it it seems, given the low volume of

Re: [sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

On 16 Jun 2019, at 7:54pm, Jens Alfke wrote: > As far as I know, there is no benefit to storing each element of such a > vector as a separate column in SQLite. Instead, the entire vector should be > stored as a single blob — for example, as a concatenation of 3072 IEEE floats > in some fixed

Re: [sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

On 16 Jun 2019, at 7:35pm, Amirouche Boubekki wrote: > Isn't this a use-case of LSM extension? It would seem a very good thing to do using LSM, but I can find documentation for LSM only in SQLite4, not SQLite3. I did find this: " I've had the

Re: [sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

On 15 Jun 2019, at 2:42pm, Dan Kaminsky wrote: [about the 32676 hard limit on the number of columns in a table] > I spent quite a bit of time hacking large column support into a working > Python pipeline, and I'd prefer never to run that in production. > Converting this compile time variable

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

On 13 Jun 2019, at 11:08pm, Keith Medcalf wrote: > Note that even when the column is declared with no affinity that NaNs are > stored as null. Other than NaN, all other IEEE754 double values (including > subnormals and +/- Inf) seem to round-trip correctly through the bind_double > and

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

On 13 Jun 2019, at 10:01pm, Donald Shepherd wrote: > Given there's been numerous comments to the effect that SQLite now supports > -0.0 storing and retrieval other than printing, I'm curious which version > this was implemented in as I wouldn't mind removing my custom code when we > move to a

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On 12 Jun 2019, at 10:28pm, James K. Lowden wrote: > what kind of computation > would lead to a value in memory representing -0,0? Here's the classic answer. It's not very impressive or convincing because it just kicks the can further down the road. Suppose you have a system like IEEE754

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On 12 Jun 2019, at 9:23pm, Jean-Marie CUAZ wrote: > A possible solution could be a new function, > - something like abszero() or trimzero() - acting only on +/- 0.0 Another would be a PRAGMA which sets whether the two values are converted to text iodentically or differently.

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On 12 Jun 2019, at 4:35pm, R Smith wrote: > (maybe a sort of CAST target or collation rather than a column "Type") That's an interesting idea. With a REAL value you get the same things when you print -0.0 and +0.0. But if you CAST it to a new type of I754 then apply printf to that, you

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On 12 Jun 2019, at 2:35pm, Richard Hipp wrote: > IEEE754 floating point numbers have separate representations for +0.0 > and -0.0. Like other people I am concerned at confusion when various different parts of the engine try -0 == +0 or -0 < +0 . I suspect that however you implement it it

Re: [sqlite] How to insert the BLOB in database?

On 10 Jun 2019, at 4:08am, Mark Halegua wrote: > I'm using Python. What would the programming sequence be to display the > graphic in that language? It's not a SQlite question. SQLite doesn't understand graphics. You'd do it similar to how you'd display a graphic you'd read from a file.

Re: [sqlite] How to insert the BLOB in database?

On 10 Jun 2019, at 3:44am, Mark Halegua wrote: > I probably should figure this out, but in a GUI, how do I recover a graphic > from the database? Programming. SQLite can't do it since it doesn't even understand that that sequence of octets is a graphics. How you do it in programming depends

Re: [sqlite] How to insert the BLOB in database?

On 9 Jun 2019, at 2:24pm, Igor Korot wrote: >> INSERT INTO tab1(blob1) VALUES(readfile('some.png')); > > I presume the file extension can be anything? As you presume, the SQLite extension does not understand the contents of the file. It's treated just as a sequence of octets. So you can use

Re: [sqlite] Failing SQLite .import returns exit status 0

On 8 Jun 2019, at 4:15pm, Adrian Ho wrote: > Except...it's not bailing on the failed import. Does the SQLite shell > consider failed dot-commands as errors? That's not what I expected. One for the developers. ___ sqlite-users mailing list

Re: [sqlite] Failing SQLite .import returns exit status 0

Use .bail ON If the shell bails out because of an error, it should be setting the exit code to indicate an error. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] SQLITE_OPEN_WAL

On 6 Jun 2019, at 9:11am, Wout Mertens wrote: > I'd like to create a database in WAL mode if it doesn't exist Create it without the flag then execute PRAGMA journal_mode=WAL The PRAGMA is harmless if the database is already in WAL mode. If you want to get fussy about it execute

Re: [sqlite] CVE-2019-5018, Resolved Which Version?

On 5 Jun 2019, at 2:35am, Mike Nicolino wrote: > My theory is that it is resolved in the 3.28 SQLite release (rather than on > that date), but I'd like confirmation as the release notes for 3.27 and 3.28 > don't reference it. The only public comment about the fix seems to be here:

[sqlite] The silliest thing you can do in SQLite

!!Con 2019 Writing an Interpreter in SQL for Fun and No Profit! by Michael Malis I think this was done in PostgreSQL. It does use on some JSON calls which don't appear in SQL's JSON extension, but I think you could use features which do

Re: [sqlite] select * where abc like "xxx" works, ...where abc='xxx' fails

On 4 Jun 2019, at 12:46am, Doug wrote: > Why does the "=" query fail and the "like" query work? To help us investigate ... Which version of SQLite is this ? You can use SELECT sqlite_version(); to find out. What do you mean by 'work' and 'fail' ? Are you referring to an error code ?

Re: [sqlite] Best way to set default PRAGMA's in System.Data.SQLite

On 1 Jun 2019, at 9:54pm, Sam Carleton wrote: > What is the best way to ensure that these two features are set correctly? > Does the program simply need to make sure it issues the pragma after each > connection, assuming it is always new? The simplest way is what you say: execute those two

Re: [sqlite] Best way to set default PRAGMA's in System.Data.SQLite

On 1 Jun 2019, at 4:34pm, Sam Carleton wrote: > What I cannot find is what to set to enable foreign keys and disable > recursive triggers. Only flags which can be supplied in connection strings can be set in DefaultFlags_SQLiteConnection . And you cannot set those two settings in connection

Re: [sqlite] SQLite4 version

On 29 May 2019, at 2:12pm, Mike King wrote: > Would it make sense for DRH to rename V4 to something else? It might be helpful to put a note at the top of ___ sqlite-users mailing list

Re: [sqlite] SQLite4 version

On 29 May 2019, at 2:02pm, Warren Young wrote: > On May 29, 2019, at 6:16 AM, Simon Slavin wrote: > >> There may one day be a release of SQLite4 > > It doesn’t look like it: https://sqlite.org/src4/info/c0b7f14c0976ed5e Right. I was considering a 'new' SQLite4 with new

Re: [sqlite] SQLite4 version

On 29 May 2019, at 7:21am, Mittal, Pradeep wrote: > I am new to SQLite and looking for SQLite version which supports the SQLite4 > interfaces and implementation. There has never been a release version of SQLite4. SQLite4 is a testbed for new ideas and new features. Some features which

Re: [sqlite] Making blob as a sqlite database.

On 29 May 2019, at 4:22am, Mohd Radzi Ibrahim wrote: > But it seems that the blob in SQLite could not do that. You can read/write to a BLOB as if it's a file. You just can't address the BLOB as if it's a SQLite database. To do that you'd have to

Re: [sqlite] bug in sqlite3??

On 28 May 2019, at 6:49pm, Jens Alfke wrote: > Try opening a copy of the database with the `sqlite3` CLI tool and entering > “PRAGMA vacuum;”. Then exit and look at the file size. Alternatively use the sqlite3_analyze tool and read the "Pages on the freelist" figures.

Re: [sqlite] How to set access permissions to protect a database file?

On 27 May 2019, at 3:33am, Adrian Ho wrote: > The OP wants *all users* to be able to update (write) the DB via the Tcl > script reading_room.tcl, but *not* by (say) running the SQLite shell or > something else. In your setup, as long as a specific user has write > permissions, *every program*

Re: [sqlite] SEE extension questions

On 26 May 2019, at 2:27pm, Karl Sanders wrote: > I'd like to ask a few questions about the SEE extension. > > - Are tables (virtual and real) used by the various extensions (in > particular by the FTS5 and R*Tree modules) encrypted? The entire database file is encrypted, block by block,

Re: [sqlite] round function inconsistent

On 24 May 2019, at 1:30pm, Jose Isaias Cabrera wrote: > Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist? > Thanks. As Dr. Hipp wrote, round was giving the right answer. All you need to do is pass the number 3.255 as the parameter. If you're passing the wrong

Re: [sqlite] Have SQLite handle values of my own type

On 23 May 2019, at 12:02pm, Dominique Devienne wrote: > On Thu, May 23, 2019 at 12:37 PM Simon Slavin wrote: > >> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the >> four parts is always three digits long. > > Sure. But representi

Re: [sqlite] Have SQLite handle values of my own type

On 23 May 2019, at 3:55am, Keith Medcalf wrote: > Technically, COLLATE only works on TEXT. Most people declare their own types > as binary blobs and the programmer has to keep track of what is in there and > how to work with it. So it would seem that rather than define a function which

Re: [sqlite] SQL Features That SQLite Does Not Implement

On 23 May 2019, at 7:57am, Thomas Kurz wrote: > CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ... 'if' in SQL language is CASE. Near the beginning of your code, try to execute a function that contains stddev(). Make a note of whether it compiles without errors or

[sqlite] Have SQLite handle values of my own type

Since there are people posting who appear know about these things … Suppose I want SQlite to handle my own type. Or to do its best to simulate that. IP address, x/y location, something like that. What should I be doing ? Do I store BLOBs and define my own COLLATEs ? Or didn't I read

Re: [sqlite] SQL Features That SQLite Does Not Implement

On 22 May 2019, at 6:51pm, sky5w...@gmail.com wrote: > Please add a note to the omitted page that many basic math functions are NOT > supported. (sqrt,mod,power,stdev,etc.) Which ones should the documentation mention ? Can you find us a specification of SQL which includes that these functions

Re: [sqlite] CSV import does not handle fields with a comma surrounded by double

On 21 May 2019, at 2:38pm, Patrick Sherrill wrote: > I don’t know about ‘valid’ csv, it has been a moving target for decades. > White space as far as my recollection should not be considered in parsing a > csv. You can do it, but you have to be consistent. There is a problem with this

Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 1:33pm, Clemens Ladisch wrote: > This keyword behaves magically. Mmmm. In that case, to implement this properly you need to store a default-type flag alongside the default value. Proposed values might be 0) No default specified, so use … 1) Fixed default specified, so use

Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 12:06pm, J. King wrote: > Then there would be no differentiating "default CURRENT_TIMESTAMP" from > "default 'CURRENT_TIMESTAMP'". That interesting. If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate CURRENT_TIMESTAMP, find a string value like

Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 11:55am, J. King wrote: > I would expect it to print only the string delimiters. I might expect it to print only the thing inside the delimiters, i.e. nothing. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] LSM Extension Performance (from sqlite3 tree)

On 16 May 2019, at 3:51pm, Amirouche Boubekki wrote: > I can create a small program that read the key/value pairs from a file that > you would be able to re-use in another program written in C for instance. Can you create a small program which shows the problem you are complaining about ?

Re: [sqlite] Affinity Performance - Non Issue?

On 15 May 2019, at 2:37am, D Burgess wrote: > I have a SQLite database that was ported from mysql. Most of the > column definitions remain unchanged. > There is lots of CHAR and VARCHAR definitions and contrary to Simon's > response they mean what they say. No problem with SELECTing an existing

Re: [sqlite] Affinity Performance - Non Issue?

On 14 May 2019, at 5:50pm, Stephen Chrzanowski wrote: > I've seen it grumbled about before about giving a field a property of CHAR, > and have seen the correction that it should be TEXT. > I understand that SQLite

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

On 12 May 2019, at 11:56pm, Manuel Rigger wrote: > Is this intended? Yes. Because the temp database disappears when you close your connection. So you would open a database and find that either a parent or a child table had disappeared. Which would mean the database was corrupt. Actually

Re: [sqlite] SQLite with single writer on Windows network share

On 12 May 2019, at 12:52am, Jose Isaias Cabrera wrote: > the other question is, that I think I only need the gcc@6 package to build > Bedrockdb. Does anybody know what is gcc@6? But I don't know where to find it for your platform.

Re: [sqlite] SQLite with single writer on Windows network share

On 11 May 2019, at 11:20pm, Warren Young wrote: > I doubt it’s any more difficult to port to Windows than dozens of other > similar packages like Apache and MySQL. Mutex/Locking. You're relying on a File System and/or a Network File System that does locking properly. I'm not saying that

Re: [sqlite] SQLite with single writer on Windows network share

On 8 May 2019, at 3:42pm, Andrew Moss wrote: > My question is, if we limit the application (through other means) to a > single writer, but allow multiple readers, does that remove the risk of > database corruption from multiple SQLite processes? > > Any notes from other users who had to do

Re: [sqlite] Where are the likes of sqlite3_db and sqlite3_stmt defined?

On 4 May 2019, at 11:16am, x wrote: > I can’t find any definition of them in the amalgamation code nor see any > #included files that are likely to contain them. They're not defined in the way you'd expect. The lines quoted in the documentation are intended to give you the structure

Re: [sqlite] Query planner: Covering index not chosen over primary key

On 3 May 2019, at 9:34pm, Jen Pollock wrote: > SELECT filename > FROM images >JOIN embedded_files ON images.file_id == embedded_files.id > WHERE type == 'png'; Try this: CREATE INDEX images (type, file_id); ANALYZE; ___ sqlite-users mailing

Re: [sqlite] What could happen if an app crashes while calling sqlite3_rekey_v2()?

On 3 May 2019, at 5:56pm, Jens Alfke wrote: > Thanks, Simon. So the rekey is expected to double the database file size? > That’s good to know. I don't know for sure. I believe that the rekeying is done block by block rather than for the entire database at one time. > After the call

Re: [sqlite] Please remove my email address from the list

On 3 May 2019, at 4:34pm, Jean Marcoux wrote: [nothing] Please click on the link at the bottom of every post, including this one, and remove yourself. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] What could happen if an app crashes while calling sqlite3_rekey_v2()?

On 3 May 2019, at 4:15pm, Pasin Suriyentrakorn wrote: > What could happen if an app crashes while calling sqlite3_rekey_v2()? Is > there best practice to safely call sqlite3_rekey_v2()? While rekey is working, the database temporarily contains both sets of some data. Automatic crash recovery

Re: [sqlite] [EXTERNAL] Re: seeking information on the throughput requirement using Sqlite

On 3 May 2019, at 4:26pm, Zhu, Liang [AUTOSOL/ASSY/US] wrote: > The planning transition rate is 1ms per 34-40Kb data, we only have one > connection trying to write to the database. If we have two connection, will > we running to database concurrency issue? If you have normal hardware,

Re: [sqlite] seeking information on the throughput requirement using Sqlite

On 3 May 2019, at 3:01pm, Zhu, Liang [AUTOSOL/ASSY/US] wrote: > In my current project, we have the need to inserting and deleting data to and > from the database between 1 and 10 ms for 24/7. I am seeking the > clarification on the following questions > > 1. what is the throughput

Re: [sqlite] PRAGMA case_sensitive_like conflicts with LIKE operator when creating an index

On 28 Apr 2019, at 7:58pm, Manuel Rigger wrote: > It seems that setting "PRAGMA case_sensitive_like" to either false (the > default behavior) or true results in no longer being able to use a LIKE or > GLOB clause when creating an index. Correct. Because you cannot depend that every

Re: [sqlite] Regarding sqlite3 reliability in using in service oriented architecture

On 28 Apr 2019, at 6:22pm, Lullaby Dayal wrote: > SQLite supports an unlimited number of simultaneous readers, but it will only > allow one writer at any instant in time. Please note that this is a statement about how one journal mode (WAL) works. Are you telling us that you're using that

Re: [sqlite] String interpreted as a column name when creating an index

On 27 Apr 2019, at 8:46pm, Manuel Rigger wrote: > INSERT INTO test(c0, c1) VALUES ("c1", 0); Technically, SQLite should return an error for that, since you supplied an entity name "c1" where it wanted an expression. For historical compatibility reasons, SQLite will accept the "c1" and

Re: [sqlite] sqlite3 java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state

On 27 Apr 2019, at 10:47am, Frank Kemmer wrote: > I can connect to the database, create a table, select from the table, insert > into the table, but the first time I try to read after an insert I get the > following exception: Are you checking the result codes from all these operations ? If

Re: [sqlite] Regarding sqlite3 reliability in using in service oriented architecture

On 26 Apr 2019, at 3:48pm, James K. Lowden wrote: > Am I the only one who reads a sentence like that and thinks, "I don't want to > drive that car"? Databases are used in black boxes (which every car has these days) and in infotainment and SatNav systems. It might not be involved in a

Re: [sqlite] .expert output help

On 26 Apr 2019, at 3:30pm, Jose Isaias Cabrera wrote: > -- On line 5 of the resulted output , > > SCAN TABLE Project_List USING COVERING INDEX Project_Name > > Why is it scanning the table using that INDEX if there is no "Project_Name" > referenced in the query? That is one of the fields of

Re: [sqlite] Regarding sqlite3 reliability in using in service oriented architecture

On 26 Apr 2019, at 2:09am, Lullaby Dayal wrote: > We use sqlite3 in an embedded automotive system based on QNX hypervisor > running multiple virtual machines. Software is architectured in a service > oriented way. We are using C programming language. > > Multiple services (involving multiple

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 4:14pm, Charles Leifer wrote: > SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1)); > > Perhaps the row-value doc could clarify the behavior of IN with row values? This > ((2019, 1, 1), (2019, 2, 1)) is not a list of scalers, it's a list of lists of

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 3:52pm, Charles Leifer wrote: > My question, though, is why is the VALUES bit needed for the WHERE clause in > this case? " If the right operand of an IN or NOT IN operator is a list of values, each of those values must be scalars

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 3:42pm, Charles Leifer wrote: > Thanks Simon -- this is just a minimal example. The WHERE clause is needed > because, unless I would provide a CASE WHEN for every single (key, value), > then the UPDATE would set the "extra" value of any unmatched key, value to > NULL. You

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 3:11pm, Charles Leifer wrote: > UPDATE "bu" SET "extra" = CASE ("key", "value") > WHEN ('k1', 1) THEN 100 > WHEN ('k2', 2) THEN -200 > WHEN ('k3', 3) THEN 30 > END > WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3)); Your WHERE clause it not needed, and SQLite is

Re: [sqlite] Multiple Independent Database Instances

On 23 Apr 2019, at 12:32am, Lee, Jason wrote: > The current code is effectively just an sqlite3_open_v2 followed by an > sqlite3_close Then either your code is faulty, and doesn't actually do this, or your problem has nothing to do with SQLite. SQLite doesn't open a database file when you

Re: [sqlite] Multiple Independent Database Instances

On 22 Apr 2019, at 10:25pm, Lee, Jason wrote: > I have a set of several million database files sitting on my filesystem. Each > thread will open a previously unprocessed database file, do some queries, > close the database, and move on to the next unprocessed database file. If this process is

Re: [sqlite] Multiple Independent Database Instances

On 22 Apr 2019, at 9:08pm, Lee, Jason wrote: > the cumulative time it takes for all of the threads to just open all > (millions) of the databases goes from 1200 seconds to 2200 seconds to 3300 > seconds. I'm guessing that it's the number of file handles which increases. Most OSes maintain a

Re: [sqlite] Multiple Independent Database Instances

On 22 Apr 2019, at 7:39pm, Lee, Jason wrote: > Hi. Are there any gotchas when opening multiple independent databases from > within one process using the C API? I am opening one database per thread in > my code, and noticed that sqlite3_open_v2 and sqlite3_close slow down as the > number of

Re: [sqlite] "Table Not Found" when multiple reader processes written in C++ accessing the same DB file in Ubuntu

On 22 Apr 2019, at 3:19pm, Polly Tang wrote: > I have an urgent issue with multiple reader processes in C++ accessing the > same DB file in Ubuntu and all reader experience "Table Not Found". /All/ say "Table Not Found" ? Including if you open just one reader process ? Are you sure your

Re: [sqlite] Is Cursor a 1 time only object?

On 20 Apr 2019, at 1:04am, Andy Hegedus wrote: > So is the cursor a 1 use only object? Your question is actually about python, rather than SQLite. You used this construction: > for row in cursor: That means python should go through all the rows that cursor has and do something with each

Re: [sqlite] Use cases for sqlite3_value_frombind()?

On 17 Apr 2019, at 7:37pm, Keith Medcalf wrote: > There are fields (columns) in your invoices table named 1.23 and 7524? Why > did you do this (or did you just use the wrong quotes around text strings?) I used the wrong quotes. Sorry, I've been doing things in other languages recently.

Re: [sqlite] Use cases for sqlite3_value_frombind()?

On 17 Apr 2019, at 6:37pm, Stephen Chrzanowski wrote: > What measures the trustworthiness? At what point would the running > application be notified that the statement was bound or injection avenue? You can include parameters as text in your SQL command: UPDATE invoices SET

Re: [sqlite] Intersect and Minus

On 15 Apr 2019, at 8:31pm, Mohit Mathur wrote: > Please let me know why it is throwing error. What error ? Do you have an error number or text ? If you perform the same SELECT in the SQLite command line tool do you get the same error ? ___

Re: [sqlite] Help with loading .DAT files

On 15 Apr 2019, at 4:48pm, Pablo Boswell (US - ASR) wrote: > I cannot get the following commands > to load anything reasonable (the engine always decides to load the data as > a single TEXT column with a column name of "sqlite3 data"): Please copy-and-paste the first line, and another line

Re: [sqlite] Help with sqlite3_value_text

I don't know about any of this, but it seems that someone needs to write a 'Unicode' (or 'Multibyte charaacters') page for the SQLite documentation. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Database corruption check.

On 15 Apr 2019, at 10:36am, Lullaby Dayal wrote: > Thank you very much for your response. The link seems to be helpful. But I > fail to run the .selftest command from my sqlite3 prompt. I got the error: > unknown command or invalid arguments error. This command was added to the command-line

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 6:32pm, Jim Dossey wrote: > It's just when I use 2 different connections that the second connection does > not see the rowid that was just added. Okay, I see you're using WAL mode, and two connections. Have you set up Shared-Cache Mode or anything else that might 'merge'

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 6:23pm, Jim Dossey wrote: > I did use sqlite3_last_insert_rowid() to get the last rowid. But I used > sqlite3_expanded_sql to get the actual SQL statement that was processed to > find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I > was looking for.

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

On 12 Apr 2019, at 5:00pm, Tony Papadimitriou wrote: > update t set s = replace(s, 'USA', '___'), > s = replace(s,'US','USA'), > s = replace(s,'___','USA'); To add to the answers other people gave, there's no set order for SQL to process these changes. The SQL

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 4:40pm, Jim Dossey wrote: > Which is obtained by calling sqlite3_expanded_sql(). Was that a copy-paste error or do you actually want ___ sqlite-users mailing list

Re: [sqlite] compressed sqlite3 database file?

On 12 Apr 2019, at 1:45am, Warren Young wrote: > ZFS I've used ZFS. ZFS is a great file system for some purposes. Fully-fledged databases isn't one of them. Someone already mentioned the problem of a transaction-based DBMS running on a transaction-based filesystem. I can add the problems

Re: [sqlite] compressed sqlite3 database file?

On 10 Apr 2019, at 8:51pm, Peng Yu wrote: > What do you recommend for Mac? Thanks. I do not have a good answer ready for you. These days storage is so cheap that buying an external 2TB drive is cheaper than spending a lot of time doing clever programming. But if you want to investigate this

Re: [sqlite] compressed sqlite3 database file?

On 10 Apr 2019, at 7:08pm, Peng Yu wrote: > https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos > > I work on Mac. Would this be worthwhile to try? I do not recommend it. Two reasons: A) If you copy the file you get an uncompressed result. This

Re: [sqlite] SQLite version 3.28.0 beta 1

Rather than sqlite3_stmt_isexplain(S) would it be possible to define a sqlite3_stmt_type(S) function instead ? This would return integers equivalent to SQLITE_STMT_ERROR = -1 SQLITE_STMT_OTHER = 0 SQLITE_STMT_EXPLAIN = 1 and in future other values as they become useful ? You could add

<    1   2   3   4   5   6   7   8   9   10   >