[sqlite] An undefined function in a check constraint will cause selecting from sqlite_master to fail.

I've thought about this a bit more, and concluded this is probably a bug. If you define your own function, and reference it in a check constraint, and then try to select from sqlite_master when that function is not currently defined, the select fails with Error: malformed database schema.  I've

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Jay A. Kreibich wrote: > On Fri, Jan 04, 2013 at 10:55:43AM +0100, Krzysztof scratched on the wall: >> Hi, >> >> When I use INSERT OR IGNORE, if insertion fail (record exists), >> then sqlite3_last_insert_rowid does return nothing. Is exists similar >> solution which: >> 1. If insert success then

Re: [sqlite] Change in behavior between 1.0.79.0 and 1.0.83.0 inSystem.Data.SQLite

Michael Russell wrote: > > We were previously using 1.0.79.0, but in an attempt to move to VS2012 / > .NET 4.5 I started doing some testing with 1.0.83.0. I found that many of > our UnitTests now fail when they try to delete the temporary test database > file they created. This happens with

[sqlite] Change in behavior between 1.0.79.0 and 1.0.83.0 in System.Data.SQLite

Hello all, We were previously using 1.0.79.0, but in an attempt to move to VS2012 / .NET 4.5 I started doing some testing with 1.0.83.0. I found that many of our UnitTests now fail when they try to delete the temporary test database file they created. This happens with the SQLiteConnection()

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 1/4/2013 8:40 AM, Krzysztof wrote: BTW: About storing dictionary tables in memory. What is faster: - Load all "contries" table to memory and locate it locally (I'm using Free Pascal / Lazarus) - Create unique index on country name and select from countries table on each insert to customers

[sqlite] New INSTR function in 3.7.15.1 DLL/EXE

Discovered a small issue with new INSTR function in both 3.7.15.1 DLL and Command Line EXE: Select INSTR('test','blahblahtestblahblah'); returns 0 however Select INSTR(''blahblahtestblahblah','test'); returns the right answer 9 Either the documentation or function needs to be corrected as

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 4 Jan 2013, at 1:04pm, Igor Tandetnik wrote: > Have your program keep a list of already-inserted countries, complete with > their IDs, in memory. Look up against this in-memory structure. There are > only about 200 countries in the world. Or just premake the table with

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 4 Jan 2013, at 12:08pm, Krzysztof wrote: > I want split > repeated columns to dictionary tables like: > > table customers > - name > - surname > - id_country // FK to table countries > > I'm looking for one command which before inserting to "customers" will look > to table

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 1/4/2013 7:08 AM, Krzysztof wrote: CSV has one table for example: name | surname | country. I want split repeated columns to dictionary tables like: table customers - name - surname - id_country // FK to table countries I'm looking for one command which before inserting to "customers" will

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On Fri, Jan 04, 2013 at 10:55:43AM +0100, Krzysztof scratched on the wall: > Hi, > > When I use INSERT OR IGNORE, if insertion fail (record exists), > then sqlite3_last_insert_rowid does return nothing. Is exists similar > solution which: > 1. If insert success then return new rowid > 2. If

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Clemens Ladisch wrote: > Krzysztof wrote: >> When I use INSERT OR IGNORE, if insertion fail (record exists), >> then sqlite3_last_insert_rowid does return nothing. > > If your unique key is the rowid, then you already know the ID that > you tried to insert. > If your unique key is not the rowid,

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Ok I have done this with two commnads where first check if record exist. I tried do this with one command because I want to speed up importing from csv. CSV has one table for example: name | surname | country. I want split repeated columns to dictionary tables like: table customers - name -

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Maybe is faster to swap the commands: 1. Select. If found done. 2. Insert (not doing ignore to get an error if it fails) and then get the rowid from sqlite3_last_insert_rowid. Patrik On 01/04/2013 11:18 AM, Simon Slavin wrote: > > On 4 Jan 2013, at 9:55am, Krzysztof wrote: > >>

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Krzysztof wrote: > When I use INSERT OR IGNORE, if insertion fail (record exists), > then sqlite3_last_insert_rowid does return nothing. If your unique key is the rowid, then you already know the ID that you tried to insert. If your unique key is not the rowid, then why do you need the rowid?

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 4 Jan 2013, at 9:55am, Krzysztof wrote: > When I use INSERT OR IGNORE, if insertion fail (record exists), > then sqlite3_last_insert_rowid does return nothing. Is exists similar > solution which: > 1. If insert success then return new rowid > 2. If insert fail (record exists)

[sqlite] INSERT OR IGNORE - returning new or existing rowid

Hi, When I use INSERT OR IGNORE, if insertion fail (record exists), then sqlite3_last_insert_rowid does return nothing. Is exists similar solution which: 1. If insert success then return new rowid 2. If insert fail (record exists) then return rowid of existing record Can I get this information