Re: [sqlite] sqlite-users Digest, Vol 130, Issue 12
>8. Re: sqlite-users Digest, Vol 130, Issue 11 (Shawn Wagner) > >On Thu, 11 Oct 2018 09:51:15 -0500, Balaji Ramanathan < > balaji.ramanat...@gmail.com> wrote: > >> > >>2. Re: SQLite mailing list > >> > > > > The 1990's called and they want their mailing lists back. So, let us > > switch to 21st century technology already. > > And yet you read the digest! A very good way to be overwhelmed, one of > the good things about mailing lists that you can see everything > separately and only read the ones you are interested in. And you want > sub-forums, so we'll all have to go through each sub-forum looking for > things of interest. > > > Well, if there is one thing that is more annoying than one email, it is multiple emails. If it weren't for the digest option, I probably wouldn't be subscribed to the sqlite mailing list at all. Emails are one of the worst productivity killers ever invented, which is why every company wants to move from emails, and will try practically anything else to try to kill the beast. Believe me, email is the bathwater that came with the internet baby. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Row value in predicate doesn't work
Following up, this also doesn't seem to be implemented: SELECT 1 WHERE (1, 1) IS NULL; I think this should be added for completeness' sake of the row value predicate functionality On Wed, Oct 10, 2018 at 3:42 PM Lukas Eder wrote: > According to my understanding of the SQL standard, the following query > should work, but doesn't on SQLite 3.25.2: > > SELECT 1 > WHERE (1, 1) IN ((1, 1), (1, 2)); > > The error I'm getting (via xerial) is: > SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (row value > misused) > > A workaround is this: > > SELECT 1 > WHERE (1, 1) IN (SELECT 1, 1 UNION ALL SELECT 1, 2); > > Thanks, > Lukas > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Row value in predicate doesn't work
According to my understanding of the SQL standard, the following query should work, but doesn't on SQLite 3.25.2: SELECT 1 WHERE (1, 1) IN ((1, 1), (1, 2)); The error I'm getting (via xerial) is: SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (row value misused) A workaround is this: SELECT 1 WHERE (1, 1) IN (SELECT 1, 1 UNION ALL SELECT 1, 2); Thanks, Lukas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
On 2018/10/12 8:19 PM, Lars Frederiksen wrote:
I type info into some labeledits and by pressing "Append to DB" button this
code is executed (fdqGoser2 is a FDQuery)
fdqGloser2.Open;
fdqGloser2.Append;
fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
fdqGloser2.FieldByName('Dansk').AsString:= ledDansk.Text;
fdqGloser2.FieldByName('Lektion').AsString:= ledLektion.Text;
fdqGloser2.FieldByName('Note').AsString:= ledNote.Text;
fdqGloser2.FieldByName('Kategori').AsString:= ledKategori.Text;
fdqGloser2.FieldByName('Langenscheidt').AsString:= ledLangenscheidt.Text;
fdqGloser2.FieldByName('Ordtype').AsString:= ledOrdtype.Text;
fdqGloser2.FieldByName('Boejning').AsString:= ledBøjning.Text;
fdqGloser2.FieldByName('Graesk_ren').AsString:=
Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
fdqGloser2.Post; [ here the error is triggered]
I wonder if some kind of property i fdqGloser2 is set wrong. I think the only
thing I have set i SQL ('SELECT * FROM Gloser2').
Any ideas?
This is a kind of novice way to do it, but should work fine (I'm not
judging, it's just that I'm not 100% sure if Append starts a transaction
or not, or if there is another command to follow Append after the field
values are set, like AddRecord or CloseRecord or some such, before Post
can be called - Prefer doing these lower down and have some control over
it, but happy to do a little trouble-shooting).
What is fdqGloser2? I mean what is its Delphi Type? (T..something... )
I'm guessing fdq is for Fire-DAC-Query, which makes it seem like this is
from some tutorial.
Actually, this question is veering further and further away from an
SQLite question - perhaps we should not spam everyone and move off-list
(along with whomever wish to join).
Send me off-list the entire Unit perhaps and the DB file (at least the
schema if the content is sensitive), then I can have a look at what goes
wrong.
If someone else is interested in contributing to this, send me a mail
too to include in the reply chain.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
I type info into some labeledits and by pressing "Append to DB" button this
code is executed (fdqGoser2 is a FDQuery)
fdqGloser2.Open;
fdqGloser2.Append;
fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
fdqGloser2.FieldByName('Dansk').AsString:= ledDansk.Text;
fdqGloser2.FieldByName('Lektion').AsString:= ledLektion.Text;
fdqGloser2.FieldByName('Note').AsString:= ledNote.Text;
fdqGloser2.FieldByName('Kategori').AsString:= ledKategori.Text;
fdqGloser2.FieldByName('Langenscheidt').AsString:= ledLangenscheidt.Text;
fdqGloser2.FieldByName('Ordtype').AsString:= ledOrdtype.Text;
fdqGloser2.FieldByName('Boejning').AsString:= ledBøjning.Text;
fdqGloser2.FieldByName('Graesk_ren').AsString:=
Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
fdqGloser2.Post; [ here the error is triggered]
I wonder if some kind of property i fdqGloser2 is set wrong. I think the only
thing I have set i SQL ('SELECT * FROM Gloser2').
Any ideas?
/Lars
-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På
vegne af Stephen Chrzanowski
Sendt: 12. oktober 2018 19:47
Til: General Discussion of SQLite Database
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
FSCK is File System ChecK, so the equivalent in Windows Scandisk.
Trace through the code to find out when exactly the SQLite is reporting the
lock. Is it at a query, on opening, is it multi-thread or
multi-application access? Is the query taking too long? If you're
debugging and everything is working through the transactions, I'd look at
how you're setting the timeout and maybe it isn't being passed through
correctly. As mentioned, depending on the size of the DB, your AV may be
holding on and doing a scan of the file that is much longer than the 5
seconds. Set breakpoints at every point where you do something with SQL,
then first step-over the break point to validate that specific function
isn't causing the lock, then resume until the next break point. When/if
you do hit a break point that causes a lock, restart the app, then
step-into the function and see where the lock is happening.
Transactions may also cause some grief, but I defer to those with more
experience on that. I know that there are blocks in play for writes, not
100% sure if you open a transaction if you'll get a locked message from
another call.
Another thing you could do is, again depending on the size of your database
and memory constraints, is "backup" the database to memory, if you're only
using a single-thread & single-application, this should be valid.
For a short bit, turn off all your AV software, or, get an AV free OS up
and running to test your application on. Disconnect your Ethernet if
you're truly paranoid about getting a rogue application hitting your
machine while you've got AV off. (Been there with Melissa Virus)
Another option is start up a new application and just have it do some data
massaging on a scrap database. SQLite Expert I think has a demo DB you can
play with, or, just start from scratch with a single file, single table
database. If your new application can do work without your AV having a
problem, then I'd be looking at what exactly your code is doing. I'm sure
there's a SQLite3 Northwind database here:
https://github.com/jpwhite3/northwind-SQLite3
I use the free version of Berlin and Tokyo, and I've never had a lock issue
that couldn't be explained by another process taking too long with the
data. I typically write single-threaded applications, but sometimes there
are multiple applications touching the database. (IE: SQLite Expert;
sqlite3.exe, my applications, etc). I've only ever run into database
locked issues is when my app is writing the data pretty quickly to the DB
and SQLite Expert runs a query. I'll get a timeout somewhere, but its
easily explained. Expanding the timeout on both applications usually fixes
the locked issue.
On Fri, Oct 12, 2018 at 1:20 PM Lars Frederiksen wrote:
> Thank you for your response. To answer your questions:
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
This would seem to imply that BitDefender is not the cause of your woes. Do you have write permission on the database file? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen >Sent: Friday, 12 October, 2018 06:26 >To: 'SQLite mailing list' >Subject: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database >is locked > >That is: I have disabled ALL protecting parameters in BitDefender to >find the culprit module - without luck. > >-Oprindelig meddelelse- >Fra: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] På vegne af Radovan Antloga >Sendt: 12. oktober 2018 10:10 >Til: sqlite-users@mailinglists.sqlite.org >Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is >locked > >Try to change database name (extension). > >For example: >mydb.sqlite >mydb.sdb >mydb.db3 > >I would use .sqlite extension and exclude that >files from antivirus check. > >Regards > >Lars Frederiksen je 12.10.2018 ob 9:11 napisal: >> Hello, >> >> I have made (in Delphi) a program that writes to a sqlite database. >This is >> a simple stand-alone solution. Now something is blocking for the >programs >> attempt to writing to the databse. There is a an error message: >> [FireDAC][Phys][SQLite] Database is locked. >> >> A friend of mine has analysed the scenario and tells me that >BitDefender is >> the Problem. In BitDefender "Application Access" I have given my >program >> permission to "Work". >> >> Any solution on how to "tell" BitDefender that it is OK to make >changes in >> my database? (I have tried BitDefender support - but no ticket >answer!) >> >> Maybe also somebody knows an antivirus/anti-malware solution which >are not >> so "hard" as BitDefender? (and with a userfriendly GUI). >> >> Thank you so much for any help. I am rather "desperate" for some >help! >> >> Best Regards >> >> Lars >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
FSCK is File System ChecK, so the equivalent in Windows Scandisk. Trace through the code to find out when exactly the SQLite is reporting the lock. Is it at a query, on opening, is it multi-thread or multi-application access? Is the query taking too long? If you're debugging and everything is working through the transactions, I'd look at how you're setting the timeout and maybe it isn't being passed through correctly. As mentioned, depending on the size of the DB, your AV may be holding on and doing a scan of the file that is much longer than the 5 seconds. Set breakpoints at every point where you do something with SQL, then first step-over the break point to validate that specific function isn't causing the lock, then resume until the next break point. When/if you do hit a break point that causes a lock, restart the app, then step-into the function and see where the lock is happening. Transactions may also cause some grief, but I defer to those with more experience on that. I know that there are blocks in play for writes, not 100% sure if you open a transaction if you'll get a locked message from another call. Another thing you could do is, again depending on the size of your database and memory constraints, is "backup" the database to memory, if you're only using a single-thread & single-application, this should be valid. For a short bit, turn off all your AV software, or, get an AV free OS up and running to test your application on. Disconnect your Ethernet if you're truly paranoid about getting a rogue application hitting your machine while you've got AV off. (Been there with Melissa Virus) Another option is start up a new application and just have it do some data massaging on a scrap database. SQLite Expert I think has a demo DB you can play with, or, just start from scratch with a single file, single table database. If your new application can do work without your AV having a problem, then I'd be looking at what exactly your code is doing. I'm sure there's a SQLite3 Northwind database here: https://github.com/jpwhite3/northwind-SQLite3 I use the free version of Berlin and Tokyo, and I've never had a lock issue that couldn't be explained by another process taking too long with the data. I typically write single-threaded applications, but sometimes there are multiple applications touching the database. (IE: SQLite Expert; sqlite3.exe, my applications, etc). I've only ever run into database locked issues is when my app is writing the data pretty quickly to the DB and SQLite Expert runs a query. I'll get a timeout somewhere, but its easily explained. Expanding the timeout on both applications usually fixes the locked issue. On Fri, Oct 12, 2018 at 1:20 PM Lars Frederiksen wrote: > Thank you for your response. To answer your questions: > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
>>> An SQL database is deemed "Relational" when it can communicate >>> mildly ... SQL stands for Structured Query Language. It has nothing whatsoever to do with the data store but rather is a specification of the Language used to retrieve/manipulate the datastore. This is the same as "C" or "FORTRAN" being language specifications for how a problem is stated to a computer and DOES NOT say anything nor impose any requirements on the underlying CPU architecture (RISC? CISC? DEC? IBM? Intel? etc) nor how the program is stored and run (RAM? Paper Tape? Core? Drum?). You can have "SQL" used to query a "relational database", query a hierarchical database, query a network or network-extended model database. You can use "navigational calls" to navigate a "relational database", "hierarchical database", or a "network or network-extended database". >> Relational Model (E.F.Codd ...) is a way of organising data (any >> data, despite many arguments to the contrary). Using >> "relational" as a sort of generic term, or as a metaphor, >> leads to confusion because it hides the fact that there is a >> formal system which is behind the creation of SQL databases, >> except that the various implementers got it wrong, including >> missing out some key points of the Model. Actually, this is incorrect. a "relational database" is one in which the "relations" are expressed by the storage of "duplicate data" in the various record structures, and that relations are based on the value of the data itself and nothing else. That is to say that a Bill-of-Material does not contain a "master record" with a "linked list" of "child records" pointing to their "owners" (as in a set), but rather that the "master record" is linked to its children by the duplication of data. Relational Algebra is the mathematic theory by which this hunk of duplicated data can be designed and manipulated in a cohesive manner without introducing "anomalies" into the processing, such consistency having previously been imposed directly by designing the database using hierarchical, network, or network-extended data models (which are more accurate portrayals (models) of the structure of the real world) ... however, the use of those methods had "issues" of their own that could be solved by using a "relational data duplication" model (in that the relationships are inherent in the data values themselves and not dependent on some artificial data structure which cannot be easily re-created if corrupted) and reflected the increase in storage and processing power that was necessary to maintain all that duplication. >Your basic thesis here revolves around "SQL engines do not follow the >letter and the law of the Relational model" - and the reply, same is >before, is "We know. So what?". >(I'm not disagreeing, I'm just not convinced of controversy) That is because SQL (Structured Query Language) is an imperative Query Language. It does not describe the underlying datastore, which may or may not be based on Relational Algebra, but is merely a Query Language -- a way to express what it is that one wants to be done. >I can't speak for everyone, but it is my sincere belief that every DB >engine, at the start, intended to be "The One" that was going to be >closest to the relational model and/or the SQL standard - right >before reality intervened. No, the Database Engine (as in the data storage layer) intends to provide a way of modelling the "real world". The relational model is simply a method by which mathematical concepts can be applied to prove correctness of the model. That is to say that a "relational database" means that the datastore is "relational" and can be manipulated in accordance with the postulates of relational algebra and is, under those postulates, correct and anomaly free. SQL is merely the manner in which one manipulates the datastore, it does not matter the actual format of storage of the datastore since, in the end, correctness of the data model can only be proved by mathematical means. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Find key, value duplicates but with differing values
On Thu, Oct 11, 2018 at 6:21 PM Hick Gunter wrote: > Two nested selects > The inner select groups by partId, name, value > The outer select groups by partId, name > Thank you who replied, Gunter, Ryan, Roman, David. This was simpler than I thought. I should have reflected a bit more myself :) --DD create view qc$xdata_duplicate_names as > > select xdata.partId, parts.title, xdata.name, > >count(*) "#duplicates", > >group_concat(xdata.value) "values", > >group_concat(xdata.idx) "indexes" > > from xdata > > join parts on parts.id = xdata.partId > > group by partId, name > > having "#duplicates" > 1 > > >> create view qc$xdata_dups_diff_value as >> > select xdata.partId, parts.title, xdata.name, > >count(*) "#duplicates", > >group_concat(xdata.value) "values", > >group_concat(xdata.idx) "indexes" > > from ( > > select * > >from xdata > > group by partId, name, value > >) xdata > > join parts on parts.id = xdata.partId > > group by xdata.partId, xdata.name > > having "#duplicates" > 1 > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
Thank you for your response. To answer your questions: Ryan: It's sad to see someone get dismayed with what is usually a flawless working system. It's not the database that's tricky, it's the reason the file is locked. This would be a problem with a string based file too. - I agree Ryan, but I get SO totally frustrated [and locked myself :-)] not being able to go on with my favorite hobby!!! Do you have it open in any DB manager or other CLI or such? - I have just tried SQLite Manager and in fact I am able to edit in the db through this program What's the actual path of the folder containing the file on your drive? - My database (called flashcard.db) is placed in a subfolder to %programdata% (c:\programdata\my name\flashcard_data This leaves other possible culprits as Windows Virtualization - Personally I do not know much about Windows Virtualization. I will try a search!! and possible other programs/processes on your machine locking the DB file. - I am totally empty about what kind of program that might be?? Thomas: Could the problem arise due to filesystem corruption? Have you tried an fsck? - no I have not tried an fsck [is this not a Linux prg that checks the files?] Chris: Close your application. Is there a xxx-journal file in the same directory as the database? (where xxx is the name of the database) Try deleting this file. - there is no such file in the database directory Best Regards Lars -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Chris Locke Sendt: 12. oktober 2018 18:22 Til: SQLite mailing list Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked > Database is locked Close your application. Is there a xxx-journal file in the same directory as the database? (where xxx is the name of the database) Try deleting this file. Thanks, Chris On Fri, Oct 12, 2018 at 4:54 PM Thomas Kurz wrote: > >> Could the problem arise due to filesystem corruption? Have you tried an >> fsck? >> >> >> - Original Message - >> From: R Smith >> To: sqlite-users@mailinglists.sqlite.org < >> sqlite-users@mailinglists.sqlite.org> >> Sent: Friday, October 12, 2018, 17:35:28 >> Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is >> locked >> >> On 2018/10/12 2:44 PM, Lars Frederiksen wrote: >> > I have tried that too! - I must admit that right now I am turning to a >> KISS solution: String-based database with functions and procedures in a >> class that handles the different jobs you do on a table. After all these >> old-fashioned DB's are not so picky :-) >> >> It's sad to see someone get dismayed with what is usually a flawless >> working system. >> It's not the database that's tricky, it's the reason the file is locked. >> This would be a problem with a string based file too. >> >> Reading the above, I'm no longer convinced it's your anti-virus' fault >> either. >> >> Using Delphi FireDAC quite a bit, I can promise you that its SQLite >> connectivity is working just fine, and while an Antivirus can lock a >> file for the time it takes to check it, unless your database is >> Gigabytes in size, that lock should be released within milliseconds, >> unless found to be unsafe - but then the log should speak of it (which I >> assume it doesn't). >> >> This leaves other possible culprits as Windows Virtualization and >> possible other programs/processes on your machine locking the DB file. >> Do you have it open in any DB manager or other CLI or such? >> What's the actual path of the folder containing the file on your drive? >> >> >> Cheers, >> Ryan >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
Clemens Ladisch wrote: > > John Found wrote: >> Also, it seems max(b = ?1) will do the trick as well as count(b = ?1) >> >> And here another question appears. What is more efficient? > > In SQLite, both are equally efficient. > > Use whatever makes the query easier to understand. Clemens, I just learned from you that count(b=?1) is not an option as it also counts zero or false. Thanks, E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
>> On Oct 9, 2018, at 7:19 PM, Warren Young wrote: >> >> The arguments about mailing lists vs forums have all been had. > It’s not clear why this entire conversation is presented in such stark terms: LIST > *OR* FORUM. Puzzling. > > If this is all driven by DRH edict that “all things shall run on fossil” , then you > know what to do: list2forum + forum2list > > There is no problem, if you don’t dogmatically insist on making it one. Thank you. So far I have seen mostly only one insisted advocate for a forum with no comment seems from the actually owner of this list. If the owner wishes to transition to only a forum then please just state it and get on with it; your idea seems better though. Maybe then this thread will die. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
> Database is locked Close your application. Is there a xxx-journal file in the same directory as the database? (where xxx is the name of the database) Try deleting this file. Thanks, Chris On Fri, Oct 12, 2018 at 4:54 PM Thomas Kurz wrote: > >> Could the problem arise due to filesystem corruption? Have you tried an >> fsck? >> >> >> - Original Message - >> From: R Smith >> To: sqlite-users@mailinglists.sqlite.org < >> sqlite-users@mailinglists.sqlite.org> >> Sent: Friday, October 12, 2018, 17:35:28 >> Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is >> locked >> >> On 2018/10/12 2:44 PM, Lars Frederiksen wrote: >> > I have tried that too! - I must admit that right now I am turning to a >> KISS solution: String-based database with functions and procedures in a >> class that handles the different jobs you do on a table. After all these >> old-fashioned DB's are not so picky :-) >> >> It's sad to see someone get dismayed with what is usually a flawless >> working system. >> It's not the database that's tricky, it's the reason the file is locked. >> This would be a problem with a string based file too. >> >> Reading the above, I'm no longer convinced it's your anti-virus' fault >> either. >> >> Using Delphi FireDAC quite a bit, I can promise you that its SQLite >> connectivity is working just fine, and while an Antivirus can lock a >> file for the time it takes to check it, unless your database is >> Gigabytes in size, that lock should be released within milliseconds, >> unless found to be unsafe - but then the log should speak of it (which I >> assume it doesn't). >> >> This leaves other possible culprits as Windows Virtualization and >> possible other programs/processes on your machine locking the DB file. >> Do you have it open in any DB manager or other CLI or such? >> What's the actual path of the folder containing the file on your drive? >> >> >> Cheers, >> Ryan >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
Could the problem arise due to filesystem corruption? Have you tried an fsck? - Original Message - From: R Smith To: sqlite-users@mailinglists.sqlite.org Sent: Friday, October 12, 2018, 17:35:28 Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked On 2018/10/12 2:44 PM, Lars Frederiksen wrote: > I have tried that too! - I must admit that right now I am turning to a KISS > solution: String-based database with functions and procedures in a class that > handles the different jobs you do on a table. After all these old-fashioned > DB's are not so picky :-) It's sad to see someone get dismayed with what is usually a flawless working system. It's not the database that's tricky, it's the reason the file is locked. This would be a problem with a string based file too. Reading the above, I'm no longer convinced it's your anti-virus' fault either. Using Delphi FireDAC quite a bit, I can promise you that its SQLite connectivity is working just fine, and while an Antivirus can lock a file for the time it takes to check it, unless your database is Gigabytes in size, that lock should be released within milliseconds, unless found to be unsafe - but then the log should speak of it (which I assume it doesn't). This leaves other possible culprits as Windows Virtualization and possible other programs/processes on your machine locking the DB file. Do you have it open in any DB manager or other CLI or such? What's the actual path of the folder containing the file on your drive? Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
On 2018/10/12 2:44 PM, Lars Frederiksen wrote: I have tried that too! - I must admit that right now I am turning to a KISS solution: String-based database with functions and procedures in a class that handles the different jobs you do on a table. After all these old-fashioned DB's are not so picky :-) It's sad to see someone get dismayed with what is usually a flawless working system. It's not the database that's tricky, it's the reason the file is locked. This would be a problem with a string based file too. Reading the above, I'm no longer convinced it's your anti-virus' fault either. Using Delphi FireDAC quite a bit, I can promise you that its SQLite connectivity is working just fine, and while an Antivirus can lock a file for the time it takes to check it, unless your database is Gigabytes in size, that lock should be released within milliseconds, unless found to be unsafe - but then the log should speak of it (which I assume it doesn't). This leaves other possible culprits as Windows Virtualization and possible other programs/processes on your machine locking the DB file. Do you have it open in any DB manager or other CLI or such? What's the actual path of the folder containing the file on your drive? Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
Relational databases, and the Relational Model, are not so called because their records stand in relation to other records. The Model, and the subsequent databases, are about relations, which are a long-standing and precisely defined mathematical concept. So, I'm afraid, you are actually wrong (in common with many others of course). I was defending my use of the the phrase "Relational data" (and even conceded it), not arguing about the meaning of Relational model - and we call Relational databases so because it does Relational things with data, things described in the Relational model... but not necessarily all the 12 things Edgar wanted us/them to conform to before allowing the term "Relational" - and that's ok... Surely. Every word I speak in this exchange seems to serve only as food to feed a next construed difference, so I'll concede everything and return to the original observation: Perhaps it's a cultural difference or linguistic weirdness, but in my tongue "controversial" means having real controversy, two sides to a coin, often hotly contested. i.e. because there are some people who believe the Earth to be flat, doesn't make the theory of Gravity a debated controversy. If we call anything that have a couple of naysayers a controversy, then everything is a controversy. Cheers, Ryan PS: You're right about one important thing, this thread must be boring everyone to bits, so I'll rest the case here. :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
On Fri, 12 Oct 2018 14:31:10 +0200, R Smith wrote: > > >> An SQL database is deemed "Relational" when it can communicate mildly > >> relational data using mildly relational (but mathematically sound) > >> methods. It doesn't need to be (nor claim to be) the Almighty keeper of > >> all relationality, nor even simply conform to various specific > >> interpretations of the word "Relation". >> There is no such thing as relational data, data is what it is and the 8>< > I will call it relational data when it is structured to contain records > that stand in relation to other records. Relational databases, and the Relational Model, are not so called because their records stand in relation to other records. The Model, and the subsequent databases, are about relations, which are a long-standing and precisely defined mathematical concept. So, I'm afraid, you are actually wrong (in common with many others of course). 8>< > its use/function/storage arrangement. Use and function can be put together, but not storage management with them. You can have any type of storage management you can think of as long as it allows the use and function and is efficient enough. This is one of the points of the Relational Model which almost all SQL-based databases ignore, they only have one or two storage arrangements, so "relational" products get blamed for bad performance when it is the design and implementation of the storage management that causes the problem, not the relational theory that is claimed to be behind the product. > Actually, never-mind, I'll concede the point. It's just data. *shrug* 8>< > Your basic thesis here revolves around "SQL engines do not follow the > letter and the law of the Relational model" - and the reply, same is > before, is "We know. So what?". > (I'm not disagreeing, I'm just not convinced of controversy) "So what" is that they and their users do not reap the benefits of the model. Also they may produce incorrect results and we can not prove that they don't. Any data retrieval that was not anticipated when the system was designed may perform very badly or even be impossible. > Let me qualify that: Sure you can kick a person who feels controversial > out from behind any Bush, but if such a controversionist had a real > point, then please ask them to provide a select query example which one > of the current engines cannot solve, but which an engine that followed > the REAL path would be able to solve. I'd venture that if you can find > such a real example with real-world application, then mainline DB > engines would quickly incorporate/adopt it. Controversy requires only that there are outspoken people on both sides of an argument, not that either side has any real points. You seem to have assumed that any controversionists (your invented word) were on my side, whereas I intended to discourage the more reasonable of those on the other side from wading in. > I can't speak for everyone, but it is my sincere belief that every DB > engine, at the start, intended to be "The One" Do you remember that there was once a short-lived product actually called "The Last One"? > that was going to be closest to the relational model and/or the SQL > standard Just "or", the standard itself contradicts the model. > - right before reality intervened. Reality can be cruel and illogical and take no regard for the best interest of anyone involved. At which point we should stop being quite so OT before someone stamps on us ;-) Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
Try this: 1. check file atribute if it is not read only maybe is not BitDefender 2. copy your database to some test database in same folder. Try to edit with simple editor and save that file to see if is possible. R.A. Lars Frederiksen je 12.10.2018 ob 14:23 napisal: I have tried this, unfortunately without any luck. I think the problem might be in the sqlite DB-system. I have disabled ALL protecting parameters to find the culprit module - without luck. My sqlite version is: SQLite version 3.19.3 2017-06-08 14:26:16 There might be a newer and better version without this error? /Lars -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Radovan Antloga Sendt: 12. oktober 2018 10:10 Til: sqlite-users@mailinglists.sqlite.org Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked Try to change database name (extension). For example: mydb.sqlite mydb.sdb mydb.db3 I would use .sqlite extension and exclude that files from antivirus check. Regards Lars Frederiksen je 12.10.2018 ob 9:11 napisal: Hello, I have made (in Delphi) a program that writes to a sqlite database. This is a simple stand-alone solution. Now something is blocking for the programs attempt to writing to the databse. There is a an error message: [FireDAC][Phys][SQLite] Database is locked. A friend of mine has analysed the scenario and tells me that BitDefender is the Problem. In BitDefender "Application Access" I have given my program permission to "Work". Any solution on how to "tell" BitDefender that it is OK to make changes in my database? (I have tried BitDefender support - but no ticket answer!) Maybe also somebody knows an antivirus/anti-malware solution which are not so "hard" as BitDefender? (and with a userfriendly GUI). Thank you so much for any help. I am rather "desperate" for some help! Best Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
I have tried that too! - I must admit that right now I am turning to a KISS solution: String-based database with functions and procedures in a class that handles the different jobs you do on a table. After all these old-fashioned DB's are not so picky :-) -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Stephen Chrzanowski Sendt: 12. oktober 2018 14:37 Til: General Discussion of SQLite Database Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked How about moving the databases elsewhere? Sometimes applications are picky when it comes where files are being written to (IE: Program Files). On Fri, Oct 12, 2018 at 8:30 AM Lars Frederiksen wrote: > Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the > problem - unfortunately! > > -Oprindelig meddelelse- > Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > På vegne af Stephen Chrzanowski > Sendt: 12. oktober 2018 14:25 > Til: General Discussion of SQLite Database > Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is > locked > > Change the timeout value on the connection to 5 seconds or so. The DB will > retry. The AV has its grip on it, doing a scan, and won't let anything > else touch it until its done. > > On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen > wrote: > > > Hello, > > > > I have made (in Delphi) a program that writes to a sqlite database. This > is > > a simple stand-alone solution. Now something is blocking for the programs > > attempt to writing to the databse. There is a an error message: > > [FireDAC][Phys][SQLite] Database is locked. > > > > A friend of mine has analysed the scenario and tells me that BitDefender > is > > the Problem. In BitDefender "Application Access" I have given my program > > permission to "Work". > > > > Any solution on how to "tell" BitDefender that it is OK to make changes > in > > my database? (I have tried BitDefender support - but no ticket answer!) > > > > Maybe also somebody knows an antivirus/anti-malware solution which are > not > > so "hard" as BitDefender? (and with a userfriendly GUI). > > > > Thank you so much for any help. I am rather "desperate" for some help! > > > > Best Regards > > > > Lars > > > > > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
How about moving the databases elsewhere? Sometimes applications are picky when it comes where files are being written to (IE: Program Files). On Fri, Oct 12, 2018 at 8:30 AM Lars Frederiksen wrote: > Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the > problem - unfortunately! > > -Oprindelig meddelelse- > Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > På vegne af Stephen Chrzanowski > Sendt: 12. oktober 2018 14:25 > Til: General Discussion of SQLite Database > Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is > locked > > Change the timeout value on the connection to 5 seconds or so. The DB will > retry. The AV has its grip on it, doing a scan, and won't let anything > else touch it until its done. > > On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen > wrote: > > > Hello, > > > > I have made (in Delphi) a program that writes to a sqlite database. This > is > > a simple stand-alone solution. Now something is blocking for the programs > > attempt to writing to the databse. There is a an error message: > > [FireDAC][Phys][SQLite] Database is locked. > > > > A friend of mine has analysed the scenario and tells me that BitDefender > is > > the Problem. In BitDefender "Application Access" I have given my program > > permission to "Work". > > > > Any solution on how to "tell" BitDefender that it is OK to make changes > in > > my database? (I have tried BitDefender support - but no ticket answer!) > > > > Maybe also somebody knows an antivirus/anti-malware solution which are > not > > so "hard" as BitDefender? (and with a userfriendly GUI). > > > > Thank you so much for any help. I am rather "desperate" for some help! > > > > Best Regards > > > > Lars > > > > > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
An SQL database is deemed "Relational" when it can communicate mildly relational data using mildly relational (but mathematically sound) methods. It doesn't need to be (nor claim to be) the Almighty keeper of all relationality, nor even simply conform to various specific interpretations of the word "Relation". There is no such thing as relational data, data is what it is and the You call it "pink sky-monkey data" then, I will call it relational data when it is structured to contain records that stand in relation to other records. My contention is not to the name of the data but to its use/function/storage arrangement. Actually, never-mind, I'll concede the point. It's just data. Relational Model (E.F.Codd ...) is a way of organising data (any data, despite many arguments to the contrary). Using "relational" as a sort of generic term, or as a metaphor, leads to confusion because it hides the fact that there is a formal system which is behind the creation of SQL databases, except that the various implementers got it wrong, including missing out some key points of the Model. Again... We know. *shrugs*. Your basic thesis here revolves around "SQL engines do not follow the letter and the law of the Relational model" - and the reply, same is before, is "We know. So what?". (I'm not disagreeing, I'm just not convinced of controversy) Let me qualify that: Sure you can kick a person who feels controversial out from behind any Bush, but if such a controversionist had a real point, then please ask them to provide a select query example which one of the current engines cannot solve, but which an engine that followed the REAL path would be able to solve. I'd venture that if you can find such a real example with real-world application, then mainline DB engines would quickly incorporate/adopt it. I can't speak for everyone, but it is my sincere belief that every DB engine, at the start, intended to be "The One" that was going to be closest to the relational model and/or the SQL standard - right before reality intervened. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the problem - unfortunately! -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Stephen Chrzanowski Sendt: 12. oktober 2018 14:25 Til: General Discussion of SQLite Database Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked Change the timeout value on the connection to 5 seconds or so. The DB will retry. The AV has its grip on it, doing a scan, and won't let anything else touch it until its done. On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen wrote: > Hello, > > I have made (in Delphi) a program that writes to a sqlite database. This is > a simple stand-alone solution. Now something is blocking for the programs > attempt to writing to the databse. There is a an error message: > [FireDAC][Phys][SQLite] Database is locked. > > A friend of mine has analysed the scenario and tells me that BitDefender is > the Problem. In BitDefender "Application Access" I have given my program > permission to "Work". > > Any solution on how to "tell" BitDefender that it is OK to make changes in > my database? (I have tried BitDefender support - but no ticket answer!) > > Maybe also somebody knows an antivirus/anti-malware solution which are not > so "hard" as BitDefender? (and with a userfriendly GUI). > > Thank you so much for any help. I am rather "desperate" for some help! > > Best Regards > > Lars > > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
That is: I have disabled ALL protecting parameters in BitDefender to find the culprit module - without luck. -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Radovan Antloga Sendt: 12. oktober 2018 10:10 Til: sqlite-users@mailinglists.sqlite.org Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked Try to change database name (extension). For example: mydb.sqlite mydb.sdb mydb.db3 I would use .sqlite extension and exclude that files from antivirus check. Regards Lars Frederiksen je 12.10.2018 ob 9:11 napisal: > Hello, > > I have made (in Delphi) a program that writes to a sqlite database. This is > a simple stand-alone solution. Now something is blocking for the programs > attempt to writing to the databse. There is a an error message: > [FireDAC][Phys][SQLite] Database is locked. > > A friend of mine has analysed the scenario and tells me that BitDefender is > the Problem. In BitDefender "Application Access" I have given my program > permission to "Work". > > Any solution on how to "tell" BitDefender that it is OK to make changes in > my database? (I have tried BitDefender support - but no ticket answer!) > > Maybe also somebody knows an antivirus/anti-malware solution which are not > so "hard" as BitDefender? (and with a userfriendly GUI). > > Thank you so much for any help. I am rather "desperate" for some help! > > Best Regards > > Lars > > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
Change the timeout value on the connection to 5 seconds or so. The DB will retry. The AV has its grip on it, doing a scan, and won't let anything else touch it until its done. On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen wrote: > Hello, > > I have made (in Delphi) a program that writes to a sqlite database. This is > a simple stand-alone solution. Now something is blocking for the programs > attempt to writing to the databse. There is a an error message: > [FireDAC][Phys][SQLite] Database is locked. > > A friend of mine has analysed the scenario and tells me that BitDefender is > the Problem. In BitDefender "Application Access" I have given my program > permission to "Work". > > Any solution on how to "tell" BitDefender that it is OK to make changes in > my database? (I have tried BitDefender support - but no ticket answer!) > > Maybe also somebody knows an antivirus/anti-malware solution which are not > so "hard" as BitDefender? (and with a userfriendly GUI). > > Thank you so much for any help. I am rather "desperate" for some help! > > Best Regards > > Lars > > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
I have tried this, unfortunately without any luck. I think the problem might be in the sqlite DB-system. I have disabled ALL protecting parameters to find the culprit module - without luck. My sqlite version is: SQLite version 3.19.3 2017-06-08 14:26:16 There might be a newer and better version without this error? /Lars -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Radovan Antloga Sendt: 12. oktober 2018 10:10 Til: sqlite-users@mailinglists.sqlite.org Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked Try to change database name (extension). For example: mydb.sqlite mydb.sdb mydb.db3 I would use .sqlite extension and exclude that files from antivirus check. Regards Lars Frederiksen je 12.10.2018 ob 9:11 napisal: > Hello, > > I have made (in Delphi) a program that writes to a sqlite database. This is > a simple stand-alone solution. Now something is blocking for the programs > attempt to writing to the databse. There is a an error message: > [FireDAC][Phys][SQLite] Database is locked. > > A friend of mine has analysed the scenario and tells me that BitDefender is > the Problem. In BitDefender "Application Access" I have given my program > permission to "Work". > > Any solution on how to "tell" BitDefender that it is OK to make changes in > my database? (I have tried BitDefender support - but no ticket answer!) > > Maybe also somebody knows an antivirus/anti-malware solution which are not > so "hard" as BitDefender? (and with a userfriendly GUI). > > Thank you so much for any help. I am rather "desperate" for some help! > > Best Regards > > Lars > > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
John Found wrote: > Also, it seems max(b = ?1) will do the trick as well as count(b = ?1) > > And here another question appears. What is more efficient? In SQLite, both are equally efficient. Use whatever makes the query easier to understand. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11
> On Oct 11, 2018, at 6:35 PM, Shawn Wagner wrote: > > I'm about ready to unsubscribe from this list and not come back until it > transitions to a forum (which is the interface I prefer anyways) because of > all this nattering clogging up my inbox the last few days. I thought I was > a grumpy old fart set in my ways, but I don't have anything on some of you. Thank you for the kind word of encouragement ☺ Have we meet before? https://www.youtube.com/watch?v=Uo0KjdDJr1c ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
> On Oct 9, 2018, at 7:19 PM, Warren Young wrote: > > The arguments about mailing lists vs forums have all been had. It’s not clear why this entire conversation is presented in such stark terms: LIST *OR* FORUM. Puzzling. If this is all driven by DRH edict that “all things shall run on fossil” , then you know what to do: list2forum + forum2list There is no problem, if you don’t dogmatically insist on making it one. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11
> On Oct 11, 2018, at 4:51 PM, Balaji Ramanathan > wrote: > > The 1990's called and they want their mailing lists back. It is/was there all the time. No one moved the cheese yet. (11) Every old idea will be proposed again with a different name and a different presentation, regardless of whether it works. RFC 1925, The Twelve Networking Truths https://tools.ietf.org/html/rfc1925 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
On 2018/10/12 7:38 AM, John Found wrote: The following code does not work, but gives an idea what I want to do: create table t (a, b); select group_concat(b) as list from t group by a having ?1 in (list); i.e. how to select only the groups that contain some value in the set of values in a column not specified in group by clause. My favourite choice is always to try and state the query as close to what you need it to do. In English: Give me all a's where there is a b value equal to ?1 among it's members , and then group those lines together (by a value) and concatenate the b values in the output. (I hope I understood) Said the same in SQL: SELECT a, group_concat(b) FROM t AS t1 WHERE EXISTS(SELECT 1 FROM t AS t2 WHERE t2.a=t1.a AND t2.b = ?1) GROUP BY a Now if you were trying to gain absolute fastest speed, the best would be to first see which a's has a b value of ?1, then simply join to that list. Two ways of doing that: Subquery: SELECT a, group_concat(b) FROM (SELECT DISTINCT a FROM t WHERE b=?1) AS X JOIN t ON t.a = X.a GROUP BY a CTE: WITH X(a) AS (SELECT DISTINCT a FROM t WHERE b=?1) SELECT a, group_concat(b) FROM X JOIN t ON t.a = X.a GROUP BY a -- Another option... -- SELECT a, group_concat(b) FROM t WHERE t.a IN (SELECT DISTINCT a FROM t WHERE b=?1) GROUP BY a ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
On Fri, 12 Oct 2018 11:12:17 +0200 Clemens Ladisch wrote: > I wrote: > > But you need to find some aggregate function that can do the filtering. > > HAVING SUM(b = ?1) > > (In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END) > > 0") Hey, this really looks great. Thanks. Also, it seems max(b = ?1) will do the trick as well as count(b = ?1) And here another question appears. What is more efficient? At first glance, max() looks better, because it does not need to scan all values from the group. But is it the case in SQLite implementation? > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- John Found ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
On Thu, 11 Oct 2018 15:44:23 -0600, Warren Young wrote: > On Oct 11, 2018, at 2:25 PM, Eric wrote: >> >> On Thu, 11 Oct 2018 10:20:08 -0600, Warren Young wrote: >>> On Oct 11, 2018, at 12:26 AM, Darren Duncan wrote: >> 8>< >> This makes me think that it would be useful, if it doesn't already, for Fossil to have something analogous to a database replication feature. >>> >>> That's pretty much what Fossil *is*: a replicated database. > [following piece of snip restored] >> Database replication that could be applied to any SQLite database might >> be a very useful thing, but the Fossil synchronization algorithm is not >> much help for that, and that sort of database replication is not a lot >> of use for Fossil. > [snip] > >> This is nothing like database replication as generally understood, which >> is commonly done by applying redo (write-ahead) logs from the other side, >> but it is exactly what Fossil needs. > > I agree that what Fossil does is not the same thing as general-purpose > relational database replication, but it doesn't need to be > general-purpose. Fossil's synchronization mechanism is custom-tailored > to its specific purpose. This is what I was actually saying. > If you were hoping to use Fossil as a general-purpose SQLite replication > system, then yeah, it's not going to work for you. No, I wasn't, but others were, e.g. thread "How does fossil synchronize databases?" in the comp.lang.tcl newsgroup. > You might want to look at Bedrock: > > http://bedrockdb.com/ I will. >> The interlocking of artifacts by cryptographic hashes does seem very much >> like the same idea as blockchain > > Relevant: https://fossil-scm.org/index.html/doc/trunk/www/blockchain.md > > I prefer the term Merkle tree, as it gets you away from all the hype > around cryptocurrencies, but drh prefers blockchain, so that's what > I use now when talking about Fossil. > > https://en.wikipedia.org/wiki/Merkle_tree I agree with the idea of not using the term blockchain, but one of the reasons for using it is that everyone has heard of it :-( . Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
> Op 12 okt. 2018, om 11:23 heeft Clemens Ladisch het > volgende geschreven: > > E.Pasma wrote: >> select group_concat(b) as list >> from t >> group by a >> having count(b=?1) >> ; > > In SQLite, a boolean expression returns 0 when false, and count(0) is 1. > You have to generate a NULL for failed matches, or use another function > like sum() or max() that can filter out zeros. > Ok, I withdraw my suggestion. Yours: select group_concat(b) as list from t where a in (select a from t where b = ?1) group by a; looks more meanibgful then. Also, with large amounts of data, and if column b is indexed, it likely becomes more efficient. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
E.Pasma wrote: > select group_concat(b) as list > from t > group by a > having count(b=?1) > ; In SQLite, a boolean expression returns 0 when false, and count(0) is 1. You have to generate a NULL for failed matches, or use another function like sum() or max() that can filter out zeros. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
> John Found : > > The following code does not work, but gives an idea what I want to do: > >create table t (a, b); > >select > group_concat(b) as list >from t >group by a >having ?1 in (list); > > Clemens Ladisch : > > select >group_concat(b) as list > from t > where a in (select a > from t > where b = ?1) > group by a; I think the original query is the best, here the having clause is be written as valid sql. select group_concat(b) as list from t group by a having count(b=?1) ; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
On Fri, 12 Oct 2018 00:06:38 +0200, R Smith wrote: > >> WARNING: the following sentence will be claimed to be controversial: >> >> No database based on SQL is truly relational. > > LOL - who would claim that to be controversial? > > It doesn't spur controversy... > > It's worthy of a shrug at best, perhaps a "So what?". You may say that, many others would disagree. 8>< > An SQL database is deemed "Relational" when it can communicate mildly > relational data using mildly relational (but mathematically sound) > methods. It doesn't need to be (nor claim to be) the Almighty keeper of > all relationality, nor even simply conform to various specific > interpretations of the word "Relation". There is no such thing as relational data, data is what it is and the Relational Model (E.F.Codd ...) is a way of organising data (any data, despite many arguments to the contrary). Using "relational" as a sort of generic term, or as a metaphor, leads to confusion because it hides the fact that there is a formal system which is behind the creation of SQL databases, except that the various implementers got it wrong, including missing out some key points of the Model. 8>< > PS: While I feel some ambivalence towards the subject, I was nodding in > agreement with most of your post, till that line appeared. :) Thankyou! > PPS: Apologies for inventing some words there I have no problem with that. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
I wrote: > But you need to find some aggregate function that can do the filtering. HAVING SUM(b = ?1) (In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END) > 0") Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
John Found wrote: > Clemens Ladisch wrote: >> select >> group_concat(b) as list >> from t >> where a in (select a >> from t >> where b = ?1) >> group by a; >> >> But you will not be able to avoid the subquery: the filter has to >> include all other rows of the group with matching b, and after the >> group_concat(), the result is no longer in a form useful for lookups. > > Hm, is sounds strange because when HAVING clause is processed, > the aggregate functions should not be processed yet (for a performance > reasons) i.e. the query still has access to all values from the field b > and theoretically should be able to search these values the same way > it searches them on executing min() or max() aggregate functions. Yes, the HAVING clause can run any aggregate function. But you need to find some aggregate function that can do the filtering. PostgreSQL would have "HAVING array_position(array_agg(b), $1)", but I have not been able to get SQLite's json_group_array() to work with this. And "',' || group_concat(b) || ',' LIKE '%,' || ?1 || ',%'" would be horrible. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
On Friday, 12 October, 2018 01:02, John Found wrote: >Hm, is sounds strange because when HAVING clause is processed, >the aggregate functions should not be processed yet (for a >performance reasons) i.e. the query still has access to all >values from the field >b >and theoretically should be able to search these values the same way >it searches them on executing min() or max() aggregate functions. The WHERE clause filters the table rows going into the GROUP-BY and the HAVING filters results coming out of the GROUP-BY processing, which is why the HAVING clause may only refer to attributes of the grouping ... which is then passed to the ORDER-BY sorter to determine the presentement order. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
On Thu, 11 Oct 2018 16:56:21 -0700, David Barrett wrote: > Incidentally, Bedrock is built on a blockchain as well -- though I agree > with the sentiment that blockchain isn't actually new at all, and not that > big of a deal. More information is here: > http://bedrockdb.com/blockchain.html Hope you enjoy it! Thankyou, I did, except for their use of the word "query" to mean inserts, updates, and deletes :-) Eric -- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
Try to change database name (extension). For example: mydb.sqlite mydb.sdb mydb.db3 I would use .sqlite extension and exclude that files from antivirus check. Regards Lars Frederiksen je 12.10.2018 ob 9:11 napisal: Hello, I have made (in Delphi) a program that writes to a sqlite database. This is a simple stand-alone solution. Now something is blocking for the programs attempt to writing to the databse. There is a an error message: [FireDAC][Phys][SQLite] Database is locked. A friend of mine has analysed the scenario and tells me that BitDefender is the Problem. In BitDefender "Application Access" I have given my program permission to "Work". Any solution on how to "tell" BitDefender that it is OK to make changes in my database? (I have tried BitDefender support - but no ticket answer!) Maybe also somebody knows an antivirus/anti-malware solution which are not so "hard" as BitDefender? (and with a userfriendly GUI). Thank you so much for any help. I am rather "desperate" for some help! Best Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error message: [FireDAC][Phys][SQLite] Database is locked
Hello, I have made (in Delphi) a program that writes to a sqlite database. This is a simple stand-alone solution. Now something is blocking for the programs attempt to writing to the databse. There is a an error message: [FireDAC][Phys][SQLite] Database is locked. A friend of mine has analysed the scenario and tells me that BitDefender is the Problem. In BitDefender "Application Access" I have given my program permission to "Work". Any solution on how to "tell" BitDefender that it is OK to make changes in my database? (I have tried BitDefender support - but no ticket answer!) Maybe also somebody knows an antivirus/anti-malware solution which are not so "hard" as BitDefender? (and with a userfriendly GUI). Thank you so much for any help. I am rather "desperate" for some help! Best Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
On Fri, 12 Oct 2018 08:27:10 +0200 Clemens Ladisch wrote: > John Found wrote: > > i.e. how to select only the groups that contain > > some value in the set of values in a column not > > specified in group by clause. > > > > select > > (select group_concat(b) from t t1 where t1.a = t2.a) as list > > from t t2 > > where b = ?1; > > Similarly: > > select > group_concat(b) as list > from t > where a in (select a > from t > where b = ?1) > group by a; > > But you will not be able to avoid the subquery: the filter has to > include all other rows of the group with matching b, and after the > group_concat(), the result is no longer in a form useful for lookups. > (And doing the filter after the grouping is probably not efficient.) Hm, is sounds strange because when HAVING clause is processed, the aggregate functions should not be processed yet (for a performance reasons) i.e. the query still has access to all values from the field b and theoretically should be able to search these values the same way it searches them on executing min() or max() aggregate functions. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- John Found ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering groups by non-grouped field.
John Found wrote: > i.e. how to select only the groups that contain > some value in the set of values in a column not > specified in group by clause. > > select > (select group_concat(b) from t t1 where t1.a = t2.a) as list > from t t2 > where b = ?1; Similarly: select group_concat(b) as list from t where a in (select a from t where b = ?1) group by a; But you will not be able to avoid the subquery: the filter has to include all other rows of the group with matching b, and after the group_concat(), the result is no longer in a form useful for lookups. (And doing the filter after the grouping is probably not efficient.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
