Re: [sqlite] sqlite support for geometric column types?

[Alexey Pechnikov]

2011/11/26 Peter Aronson :
> Not directly.  But Alessandro Furieri has created a version of SQLite with
> OGC conforming geometry columns, called SpatiaLite.  It can be found here:
>
> http://www.gaia-gis.it/spatialite/
>
> It's based on an older version of SQLite (3.6.16, but a beta version
> supports 3.7.3), and it uses a more restrictive license (not hard, since any
> license is more restrictive than SQLite's non-license!)

It can be used as extensions set for any new SQLite 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

Re: [sqlite] Free c code for embedded sqlite3

[Matt Young]

Good idea, as soon as I figure out how it works!

On Wed, Nov 23, 2011 at 2:41 PM, Nico Williams wrote:

> Docs would help people understand what you're up to...
>
> Nico
> --
> ___
> 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] SQLITE_BUSY

[Simon Slavin]

On 26 Nov 2011, at 4:00pm, Jean-Christophe Deschamps wrote:

>> So I make up for it by using an 'exec' which executes only the first 
>> command, and by hashing the command so that tampering with it would make it 
>> fail the hash.
> 
> AFAIK sqlite3_exec will hapilly prepare and run more than one statements in a 
> row.  I use it all the time.

But several APIs offer ones which won't.  For instance

http://www.php.net/manual/en/sqlite3.query.php

will only execute one command.  So even if someone cracks my (fairly secure but 
not paranoid) system and does a 'little Bobby Tables' on me, the second command 
won't be executed.

>> I hadn't known [_exec] removal was even being considered.
> 
> Not by the dev team AFAIK but the idea has been expressed several times that 
> it was an obsolote, useless API we should get rid of.

Arghh !I use _prepare for apps which do a lot of processing, but quite 
a lot of my SQLite use is on computers I personally control and in situations 
where _exec is as safe as anything.

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

Re: [sqlite] SQLITE_BUSY

[Jean-Christophe Deschamps]

So I make up for it by using an 'exec' which executes only the first 
command, and by hashing the command so that tampering with it would 
make it fail the hash.


AFAIK sqlite3_exec will hapilly prepare and run more than one 
statements in a row.  I use it all the time.


But there's no reason you'd have to use exec just because you're 
passing a string.  You can still use _prepare, _step, _finalize if you 
find it convenient, you just use it on a string which has the 
completed command in.


Yes it was confusing on my part saying so without details.  In my 
wrapper I offer _SQLite_Exec wrapping sqlite3_exec for simple "dry" 
statements like setting pragmas and such.
For actual queries (i.e. returning results), I use internally _prepare, 
_step and optionally _finalize, all wrapped into higher-level 
interface.  For my advanced users these APIs are also available as 
separate functions (but not any _bind).  But the fact is that most 
users here have hard time getting how SQL works at all, so keeping it 
simple is good enough.
I even have wrappers like _SQLite_GetTable2D for returning a whole 
ready-to-use result array.



I hadn't known its removal was even being considered.


Not by the dev team AFAIK but the idea has been expressed several times 
that it was an obsolote, useless API we should get rid of.


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

Re: [sqlite] SQLITE_BUSY

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/11/11 02:07, joel.bertr...@external.gdfsuez.com wrote:
> If sqlite3_step() returns BUSY_TIMEOUT, I try to restart statement :
> 
> While((result = sqlite3_step(stmt)) == SQLITE_BUSY) { Usleep(_random
> [0s, 1s[_); }

Is there any particular reason you do this?  Just set a busy timeout and
SQLite has an internal function that does a series of usleeps so you won't
get busy errors in the first place until the overall timeout elapses.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk7RBf4ACgkQmOOfHg372QSc3gCePh1Ur8x1r6+8k2GKfQFTnZsD
bBgAoNdkXWhqwx9rvk767oB/p0WqtaZJ
=MF6R
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

[Jean-Christophe Deschamps]

And it's convenient to be able to do the prepare during startup.  I've 
just never had to worry about this situation.  It means you have to do 
special handling for the first _step, but it's a reasonably compact 
way of handling the potential problem.


I can't use prepared statements in practice with the (interpreted) 
scripting language I use most (AutoIt) due to its DLL invokation being 
so slow: having to first prepare then perform multiple _Bind calls and 
having to sort out variable type for each _Bind proves way slower than 
an all-in-one "dumb" _Exec.  That's possibly true for more scripting 
languages.


That's why _Exec should definitely be kept part of the SQLite API.

Anyway, I expect things in this language could change in some future 
and prepared statements become affordable in this context. 


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

Re: [sqlite] Cross-platform Intel <-> ARM sqlite file issue...

[Kees Nuyt]

On Fri, 25 Nov 2011 12:30:10 +0100, Laurent PERROTON
 wrote:

> I am having a cross-platform Intel/ARM sqlite file issue.
>
> "...the file format is cross-platform. A database that is created on one
> machine can be copied and used on a different machine with a different
> architecture."
>
> So, how come I get this (sqlite 3.5.9 and ext3 on both IntelHost and ARMHost):

[snipped sample]

> Did I miss something ?

Perhaps you missed a compiler option :

http://www.sqlite.org/changes.html#version_3_4_0

* Added the SQLITE_MIXED_ENDIAN_64BIT_FLOAT 
  compile-time option to support ARM7 processors
  with goofy endianness.

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] SQLITE_BUSY

[Simon Slavin]

On 26 Nov 2011, at 2:29pm, Jean-Christophe Deschamps wrote:

> Hi Simon,
> 
>> Ah, so it's the first _step which does the locking, rather than the _prepare.
> 
> That makes sense to me.  If I had a serious application using, say, a hundred 
> of different prepared queries, I'd rather batch-prepare them all up front 
> then use them when needed without asking question.  Compared to having to 
> check before every use whether to prepare or not right in the core of the app 
> code.  If prepare were to lock thing up, it would defeat such use. 

Sure.  And it's convenient to be able to do the prepare during startup.  I've 
just never had to worry about this situation.  It means you have to do special 
handling for the first _step, but it's a reasonably compact way of handling the 
potential problem.

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

Re: [sqlite] SQLITE_BUSY

[Jean-Christophe Deschamps]

Hi Simon,

Ah, so it's the first _step which does the locking, rather than the 
_prepare.


That makes sense to me.  If I had a serious application using, say, a 
hundred of different prepared queries, I'd rather batch-prepare them 
all up front then use them when needed without asking 
question.  Compared to having to check before every use whether to 
prepare or not right in the core of the app code.  If prepare were to 
lock thing up, it would defeat such use. 


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

Re: [sqlite] SQLITE_BUSY

[Simon Slavin]

On 26 Nov 2011, at 2:15pm, Pavel Ivanov wrote:

> On Sat, Nov 26, 2011 at 9:03 AM, Simon Slavin  wrote:
>> On 26 Nov 2011, at 2:00pm, Pavel Ivanov wrote:
>> 
>>> When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
>>> before calling sqlite3_step again.
>> 
>> If your statement is a SELECT returning 50 rows, and you've already read 20 
>> of them by the time you get the _BUSY, do you get the first 20 again after 
>> doing the _reset ?
> 
> This is impossible. On any SELECT statement you either get SQLITE_BUSY
> before the first row or you don't get it at all.

Ah, so it's the first _step which does the locking, rather than the _prepare.  
Okay, that makes the programming easier than if you might have to reset at any 
_step.  Thanks.  Also to J-C.

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

Re: [sqlite] SQLITE_BUSY

[Pavel Ivanov]

On Sat, Nov 26, 2011 at 9:03 AM, Simon Slavin  wrote:
> On 26 Nov 2011, at 2:00pm, Pavel Ivanov wrote:
>
>> When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
>> before calling sqlite3_step again.
>
> If your statement is a SELECT returning 50 rows, and you've already read 20 
> of them by the time you get the _BUSY, do you get the first 20 again after 
> doing the _reset ?

This is impossible. On any SELECT statement you either get SQLITE_BUSY
before the first row or you don't get it at all.


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

Re: [sqlite] SQLITE_BUSY

[Jean-Christophe Deschamps]

If your statement is a SELECT returning 50 rows, and you've already 
read 20 of them by the time you get the _BUSY, do you get the first 20 
again after doing the _reset ?


I don't believe we can get _BUSY in the middle of a SELECT.  Am I wrong? 


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

Re: [sqlite] SQLITE_BUSY

[Simon Slavin]

On 26 Nov 2011, at 2:00pm, Pavel Ivanov wrote:

> When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
> before calling sqlite3_step again.

If your statement is a SELECT returning 50 rows, and you've already read 20 of 
them by the time you get the _BUSY, do you get the first 20 again after doing 
the _reset ?

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

Re: [sqlite] SQLITE_BUSY

[Pavel Ivanov]

> While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
> {
>                Usleep(_random [0s, 1s[_);
> }

When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
before calling sqlite3_step again. So your loop should look like this:


While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
{
               sqlite3_reset(stmt);
               Usleep(_random [0s, 1s[_);
}


Pavel


On Fri, Nov 25, 2011 at 5:07 AM,   wrote:
>                Hello,
>
>                I'm writing a multithreaded application (sqlite 3.7.9 on linux 
> i386).
> First thread exports some parts of a database, deletes exported records and 
> starts a vacuum. Second thread tries to write in database.
> When my application tries to write data when the other thread is running 
> vacuum, sqlite3_step() returns SQL_BUSY. I have set busy_timeout to 1000.
>
>                If sqlite3_step() returns BUSY_TIMEOUT, I try to restart 
> statement :
>
> While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
> {
>                Usleep(_random [0s, 1s[_);
> }
>
> But this loop always enters in a deadlock : if sqlite3_step() returns 
> SQLITE_BUSY, and if I try to restart the same statement, it always will 
> returns SQLITE_BUSY even if there is no other concurrent transaction. If I 
> write :
>
> Int I = 0;
> While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
> {
>                Usleep(_random [0s, 1s[_);
>                If (++I > 10) break;
> }
>
> I have forgotten current statement (and data) but following SQL query returns 
> SQLITE_OK. Why does sqlite3_step() always returns SQLITE_BUSY on a statement 
> when it has returned SQLITE_BUSY the first time ?
>
> Regards,
>
> JB
>
>
> GDF SUEZ Mail Disclaimer: http://www.gdfsuez.com/disclaimer/disclaimer-fr.html
>
> ___
> 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

[sqlite] Cross-platform Intel <-> ARM sqlite file issue...

[Laurent PERROTON]

Hello Everybody,

I am having a cross-platform Intel/ARM sqlite file issue.

"...the file format is cross-platform. A database that is created on one
machine can be copied and used on a different machine with a different
architecture."

So, how come I get this (sqlite 3.5.9 and ext3 on both IntelHost and ARMHost):

IntelHost% sqlite3 test.sqlite 'create table tab (one real, two real);\
insert into tab values (1.2, 1.3);'
IntelHost% sqlite3 test.sqlite 'select * from tab;'
1.2|1.3
IntelHost% scp test.sqlite ARMHost:

ARMHost% sqlite3 test.sqlite 'select * from tab;'
4.66726164313171e-62|-9.25596534088728e+61
ARMHost% sqlite3 test.sqlite 'insert into tab values (2.1, 2.2);'
ARMHost% sqlite3 test.sqlite 'select * from tab;'
4.66726164313171e-62|-9.25596534088728e+61
2.1|2.2

IntelHost% scp ARMHost:test.sqlite .
IntelHost% sqlite3 test.sqlite 'select * from tab;'
1.2|1.3
-9.25596534178523e+61|-2.35343828009147e-185

Did I miss something ?

Thank you very much,

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