Re: [sqlite] Problem with rename table

2016-09-15 Thread Zsbán Ambrus
On Tue, Sep 6, 2016 at 3:37 PM, Radovan Antloga wrote: > I can't find a solution how to fix my database after > I have renamed table DOKUMENTI to DOKUMENTI2. > Table DOKUMENTI had trigger dokumenti_trigger1 > and after renaming table I cant execute any sql. I forgot > to

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

2016-09-15 Thread Richard Hipp
On 9/15/16, Martin Raiber wrote: > > The program opens the database file with fd = > open("/path/to/database/file", ...) and then closes it with close(fd) > using the OS file api. The close() clears the posix file locks of the > process in the database file (that is all posix

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

2016-09-15 Thread Martin Raiber
On 16.09.2016 00:53 Simon Slavin wrote: > On 15 Sep 2016, at 11:38pm, Martin Raiber wrote: > >> There are two instances in the program where the sqlite database file is >> opened >> and closed outside of sqlite3 (to backup the database file and to sync >> it before >>

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

2016-09-15 Thread Simon Slavin
On 15 Sep 2016, at 11:38pm, Martin Raiber wrote: > There are two instances in the program where the sqlite database file is > opened > and closed outside of sqlite3 (to backup the database file and to sync > it before > checkpointing). This clears away the posix locks on

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

2016-09-15 Thread Martin Raiber
After getting some additional information, namely that the users are also using the sqlite3 command line tool to read data from the database, I think I found out how this issue occurs. There are two instances in the program where the sqlite database file is opened and closed outside of sqlite3

Re: [sqlite] Complicated join

2016-09-15 Thread Simon Slavin
On 15 Sep 2016, at 9:46pm, David Raymond wrote: > The idea is to find the closest date that matches that couldn't be matched to > another record. [snip] > Can this join be done in SQL? I wouldn't even try to do it in any SQL engine. It would be ridiculously

Re: [sqlite] Complicated join

2016-09-15 Thread David Raymond
Can it be done in SQL? Yes. In any sort of pretty or efficient manner? Ehhh, maybe? I came up with something that seems to work with the small sample cases that I came up with to try and cover your requirements there, but it's got a couple levels of CTE's with long "where not exists..."

Re: [sqlite] When is data committed on one connection seen on another?

2016-09-15 Thread R Smith
On 2016/09/14 8:29 PM, Alex Ward wrote: We currently have 500 tables and 1500 triggers in the schema. Perhaps that is why we didn't have much luck having one connection per thread or opening a connection per access. Perhaps our schema needs a rework, would one table with a million rows

[sqlite] Complicated join

2016-09-15 Thread David Bicking
I have two tables: CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, TransDate)); "CombinedKeyFields" is shorthand for a

Re: [sqlite] Ordering looks incorrect

2016-09-15 Thread David Crayford
On 14/09/2016 9:59 PM, R Smith wrote: I think this is answered, but since you are a bit new to SQLite, and to be somewhat more informant... SQLite is certainly a different experience to the enterprise class data bases that I'm used to but I like it. I'm still getting used to the duck

Re: [sqlite] When is data committed on one connection seen on another?

2016-09-15 Thread Hick Gunter
>> As already stated, and per my own experience, each thread should have it's >> own connection and do whatever it needs to do there, without interference >> from other threads. > >I appreciate this point. Early prototyping indicated that this might not be >possible for our system, which makes