Re: [sqlite] is it reliable to directly read sqlite3_sequence?

[Stephan Beal]

On Mon, Apr 9, 2012 at 4:16 AM, Pavel Ivanov  wrote:

> If you read the link Simon gave you'll see that sqlite_sequence have
> records only for tables with AUTOINCREMENT ROWID. And that record
> doesn't necessarily contains value from some row that really existed
> at any point of time. In other words with this implementation your
> function may return incorrect value.


Thanks for that clarification. i incidentally removed it because it causes
a huge number of malloc()s in my test app.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it reliable to directly read sqlite3_sequence?

[Pavel Ivanov]

> i just (experimentally)
> implemented a lookup on sqlite_sequence if the hint is provided, else
> falling back to sqlite3_last_insert_id(). It "works for me", but if it's a
> potential portability problem then i'll remove it.

If you read the link Simon gave you'll see that sqlite_sequence have
records only for tables with AUTOINCREMENT ROWID. And that record
doesn't necessarily contains value from some row that really existed
at any point of time. In other words with this implementation your
function may return incorrect value.


Pavel


On Sun, Apr 8, 2012 at 9:44 PM, Stephan Beal  wrote:
> On Mon, Apr 9, 2012 at 3:25 AM, Simon Slavin  wrote:
>
>> The C function sqlite3_last_insert_id(), and the SQLite wrapper around it
>> last_insert_rowid() are as good as it gets.  You do have to be a little
>> careful if you have a TRIGGER on an INSERT which does another INSERT.  If
>> you use TRIGGERs I suggest you test out the behaviour for yourself to
>> figure out what it does.
>>
>
> Thanks for that, Simon. A bit of background might help understand why i
> ask: i have a db access abstraction API in C, and of course have an sqlite3
> driver/wrapper. The last-insert-id op in the API supports (but does not
> require) that a table/sequence name argument be passed to it (my research
> into several C db APIs showed that some drivers want/need this). Currently
> the sqlite3 wrapper ignores that argument. i just (experimentally)
> implemented a lookup on sqlite_sequence if the hint is provided, else
> falling back to sqlite3_last_insert_id(). It "works for me", but if it's a
> potential portability problem then i'll remove it.
>
> @Pavel: the id/trigger "problem" is not something i've seen personally - it
> was reported by someone on this list some time last year, and i've just
> always had it in the back of my mind (in the context of my db wrapper API).
> i appreciate the link to the docs - that certainly clarifies it for me (as
> a non-problem).
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it reliable to directly read sqlite3_sequence?

[Stephan Beal]

On Mon, Apr 9, 2012 at 3:25 AM, Simon Slavin  wrote:

> The C function sqlite3_last_insert_id(), and the SQLite wrapper around it
> last_insert_rowid() are as good as it gets.  You do have to be a little
> careful if you have a TRIGGER on an INSERT which does another INSERT.  If
> you use TRIGGERs I suggest you test out the behaviour for yourself to
> figure out what it does.
>

Thanks for that, Simon. A bit of background might help understand why i
ask: i have a db access abstraction API in C, and of course have an sqlite3
driver/wrapper. The last-insert-id op in the API supports (but does not
require) that a table/sequence name argument be passed to it (my research
into several C db APIs showed that some drivers want/need this). Currently
the sqlite3 wrapper ignores that argument. i just (experimentally)
implemented a lookup on sqlite_sequence if the hint is provided, else
falling back to sqlite3_last_insert_id(). It "works for me", but if it's a
potential portability problem then i'll remove it.

@Pavel: the id/trigger "problem" is not something i've seen personally - it
was reported by someone on this list some time last year, and i've just
always had it in the back of my mind (in the context of my db wrapper API).
i appreciate the link to the docs - that certainly clarifies it for me (as
a non-problem).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it reliable to directly read sqlite3_sequence?

[Pavel Ivanov]

On Sun, Apr 8, 2012 at 9:25 PM, Simon Slavin  wrote:
> On 9 Apr 2012, at 2:14am, Stephan Beal  wrote:
>
>> Is it reliable/portable/well-defined to use (read-only) the sqlite_sequence
>> table to find the last insertion ID for a given table? i know about
>> sqlite3_last_insert_id(), but i'm wondering if reading the sqlite_sequence
>> table directly might be safer vis-a-vis triggers which perform an insert
>> (and thereby cause sqlite3_last_insert_id() to possibly return an
>> unexpected value).
>
> The C function sqlite3_last_insert_id(), and the SQLite wrapper around it 
> last_insert_rowid() are as good as it gets.  You do have to be a little 
> careful if you have a TRIGGER on an INSERT which does another INSERT.  If you 
> use TRIGGERs I suggest you test out the behaviour for yourself to figure out 
> what it does.

Interference of sqlite3_last_insert_id() with triggers is well
documented and doesn't have any inconvenient caveats. See
http://www.sqlite.org/c3ref/last_insert_rowid.html:

"If an INSERT occurs within a trigger or within a virtual table
method, then this routine will return the rowid of the inserted row as
long as the trigger or virtual table method is running. But once the
trigger or virtual table method ends, the value returned by this
routine reverts to what it was before the trigger or virtual table
method began."

So I'm not sure what problems do you see with using
sqlite3_last_insert_id() and triggers even if they issue another bunch
of inserts.


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined symbol: sqlite3_stricmp

[Alexey Pechnikov]

Tcl-way is
package require sqlite3

The problem was fired in some old SQLite versions but was fixed before
previous release.

2012/4/8 Black, Michael (IS) 

> Don't you need to load the sqlite3 library first for tclsh?
>
>
>
> load ./libtclsqlite3.so Sqlite3
>
> Or something like that?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Alexey Pechnikov [pechni...@mobigroup.ru]
> Sent: Saturday, April 07, 2012 7:14 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] undefined symbol: sqlite3_stricmp
>
> Command-line shell works fine but tclsqlite package does not:
>
> $ tclsh8.5 test.tcl
> /usr/lib/libsqlitefts3.so: undefined symbol: sqlite3_stricmp
>while executing
> "db eval {SELECT load_extension('/usr/lib/libsqlitefts3.so')}"
>
>
> $ sqlite3
> SQLite version 3.7.12 2012-04-07 11:00:54
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT load_extension('/usr/lib/libsqlitefts3.so');
> load_extension('/usr/lib/libsqlitefts3.so')
> sqlite>
>
>
>
> 2012/4/7 Richard Hipp 
>
> > On Sat, Apr 7, 2012 at 7:20 AM, Alexey Pechnikov  > >wrote:
> >
> > > Compiled as extension FTS3 module produce error "undefined symbol:
> > > sqlite3_stricmp" by loading.
> > > The problem exists in last release and current trunk.
> > >
> >
> >
> > See http://www.sqlite.org/src/artifact/906731099c43?ln=225-231
> >
> > Probably you are trying to link against a pre-3.7.11 version of SQLite,
> > perhaps the one that comes with your OS, instead of the latest version.
> >
> >
> >
> > >
> > > --
> > > Best regards, Alexey Pechnikov.
> > > http://pechnikov.tel/
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update problem with Sqlite (one PC one single operation) Vb.net

[Larry Brasfield]

On April 8, Marco wrote:

OpenConnection(TMP_ConnectionString, OBJ_Connection)

sql= "UPDATE ANG_Operatori Set NomeOperatore = 'Amministratore',UserID =
'admin',ParolaChiave = 'prova',DataUltimoAccesso = '08/04/2012
15:04:00',Supervisore = 1,ID_Dentista = 1 where ID_ANG_Operatore = 1"


 Execute("BEGIN TRANSACTION")   '("BEGIN EXCLUSIVE
TRANSACTION")
 Execute(SQL)
 Execute("END TRANSACTION")

//


According to http://www.sqlite.org/lang_transaction.html , "An attempt 
to invoke the BEGIN command within a transaction will fail with an 
error, regardless of whether the transaction was started by SAVEPOINT or 
a prior BEGIN."


That alone would be enough to suggest the above code should be avoided. 
 The SQLite3 shell throws an error when given the second "BEGIN ..." 
command.



With this code the db don't save.


Are you checking for error returns?  (I would guess not.  In that case, 
your first fix should be to check every single call into SQLite that can 
return an error.)



If I add 'EXCLUSIVE' the program stop after 30 seconds and return "The
database file is locked"


Probably the first transaction is still in effect.


If I remove the transaction some result.


Show us the code that fails.  It's alright to cut out what is truly 
irrelevant, but please do not submit pseudocode.



With MS access this query work without need to insert transaction.


I see no queries here at all, so I cannot judge what "this query work" 
means.



How I can solve this problem ?


Too little information to guess.

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update problem with Sqlite (one PC one single operation) Vb.net

[marco1]

Hi Simon ,

Thank you for your fast reply:

SO: Windows 7.
VB.net 2010 

//

OpenConnection(TMP_ConnectionString, OBJ_Connection)

sql= "UPDATE ANG_Operatori Set NomeOperatore = 'Amministratore',UserID =
'admin',ParolaChiave = 'prova',DataUltimoAccesso = '08/04/2012
15:04:00',Supervisore = 1,ID_Dentista = 1 where ID_ANG_Operatore = 1"


 Execute("BEGIN TRANSACTION")   '("BEGIN EXCLUSIVE
TRANSACTION")
 Execute(SQL)
 Execute("END TRANSACTION")

//

With this code the db don't save.

If I add 'EXCLUSIVE' the program stop after 30 seconds and return "The
database file is locked"

If I remove the transaction some result.

With MS access this query work without need to insert transaction.

How I can solve this problem ?

thank you !

Marco


Simon Slavin-3 wrote:
> 
> 
> On 8 Apr 2012, at 2:32pm, marco1  wrote:
> 
>> Hi , I don't know how is possible save record with Sqlite. 
>> 
>> I was try i different way:
>> 
>> 1) With vb.net updatecommand (The database file is locked)
>> 2) With "Insert Into"  (The database file is locked)
>> 3) With transaction Begin . Commit (The database file is locked)
> 
> Which operating system are you running ?
> 
> What programming language are you using ?
> 
> What is the development environment you're using ?
> 
> Please show us the command you're using to specify which file on disk you
> are opening.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
-- 
View this message in context: 
http://old.nabble.com/Update-problem-with-Sqlite-%28one-PC-one-single-operation%29-Vb.net-tp33651706p33651759.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update problem with Sqlite (one PC one single operation) Vb.net

[Simon Slavin]

On 8 Apr 2012, at 2:32pm, marco1  wrote:

> Hi , I don't know how is possible save record with Sqlite. 
> 
> I was try i different way:
> 
> 1) With vb.net updatecommand (The database file is locked)
> 2) With "Insert Into"  (The database file is locked)
> 3) With transaction Begin . Commit (The database file is locked)

Which operating system are you running ?

What programming language are you using ?

What is the development environment you're using ?

Please show us the command you're using to specify which file on disk you are 
opening.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update problem with Sqlite (one PC one single operation) Vb.net

[marco1]

Hi , I don't know how is possible save record with Sqlite. 

I was try i different way:

1) With vb.net updatecommand (The database file is locked)
2) With "Insert Into"  (The database file is locked)
3) With transaction Begin . Commit (The database file is locked)

This message is false because there is not another parallel process.

When I need to read data from DB it work fine.

When I need to Update record don't work.

with MS access never seen similar problems.

How can I do ???
-- 
View this message in context: 
http://old.nabble.com/Update-problem-with-Sqlite-%28one-PC-one-single-operation%29-Vb.net-tp33651706p33651706.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined symbol: sqlite3_stricmp

[Black, Michael (IS)]

Don't you need to load the sqlite3 library first for tclsh?



load ./libtclsqlite3.so Sqlite3

Or something like that?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Alexey Pechnikov [pechni...@mobigroup.ru]
Sent: Saturday, April 07, 2012 7:14 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] undefined symbol: sqlite3_stricmp

Command-line shell works fine but tclsqlite package does not:

$ tclsh8.5 test.tcl
/usr/lib/libsqlitefts3.so: undefined symbol: sqlite3_stricmp
while executing
"db eval {SELECT load_extension('/usr/lib/libsqlitefts3.so')}"


$ sqlite3
SQLite version 3.7.12 2012-04-07 11:00:54
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT load_extension('/usr/lib/libsqlitefts3.so');
load_extension('/usr/lib/libsqlitefts3.so')
sqlite>



2012/4/7 Richard Hipp 

> On Sat, Apr 7, 2012 at 7:20 AM, Alexey Pechnikov  >wrote:
>
> > Compiled as extension FTS3 module produce error "undefined symbol:
> > sqlite3_stricmp" by loading.
> > The problem exists in last release and current trunk.
> >
>
>
> See http://www.sqlite.org/src/artifact/906731099c43?ln=225-231
>
> Probably you are trying to link against a pre-3.7.11 version of SQLite,
> perhaps the one that comes with your OS, instead of the latest version.
>
>
>
> >
> > --
> > Best regards, Alexey Pechnikov.
> > http://pechnikov.tel/
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE vs INSERT WHERE NOT EXISTS

[Petite Abeille]

On Apr 8, 2012, at 12:25 AM, Josh Gibbs wrote:

> SQLite: INSERT OR IGNORE INTO test_table VALUES('prop','val');
> Postgres: INSERT INTO test_table SELECT 'prop','val' WHERE NOT EXISTS (SELECT 
> 1 FROM test_table WHERE property='prop');

My 2¢…

The first variant, insert or ignore, is arguably more idiomatic to SQLite, and 
theoretically requires less work.

As the DML will have to check the primary key constraint either ways, best to 
let SQLite do it only once. Instead of twice, as in your second variant: once 
for exists, and once for insert. 

Tangentially related, some RDBMS provide support for DML error logging, which 
comes handy at time:

http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users