Re: [sqlite] Windows user app to display input form + reporting?

[Stephen Chrzanowski]

SQLite Expert has a free version that can be used.  Certain UI features are
missing, but, it does allow for excel like table editing.  Its also MUCH
cheaper than anything Microsoft has put out, even with their "dealer
discounts" and "oem" sales with proper hardware purchases.


On Tue, Mar 11, 2014 at 7:31 PM, Gilles Ganault wrote:

> Hello
>
> A friend needs to move from Excel to a database. The school won't
> pay for the full version of MS Office that includes Access, so
> recommended that she use LibreOffice Base instead.
>
> I just checked it out, and it seems to only be a front-end to the
> HSQLDB database which is written in Java.
>
> Before I check it out, I'd like to make sure there's no SQLite-based
> lighter solution where...
> 1. I'll run the commands originally to build the DB + tables
> 2. The Windows application will either display forms for her to enter
> data, or provide an easy way to import data from Excel
> 3. It will then display data through some reporting tool.
>
> Is there a good SQLite-based alternative to LibreOffice Base?
>
> I found this:
> http://sqlitestudio.pl
> http://www.valentina-db.com
> http://www.navicat.com/products/navicat-for-sqlite
>
> Thank you.
>
> ___
> 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] Windows user app to display input form + reporting?

[Gilles Ganault]

Hello

A friend needs to move from Excel to a database. The school won't
pay for the full version of MS Office that includes Access, so
recommended that she use LibreOffice Base instead.

I just checked it out, and it seems to only be a front-end to the
HSQLDB database which is written in Java.

Before I check it out, I'd like to make sure there's no SQLite-based
lighter solution where...
1. I'll run the commands originally to build the DB + tables
2. The Windows application will either display forms for her to enter
data, or provide an easy way to import data from Excel
3. It will then display data through some reporting tool.

Is there a good SQLite-based alternative to LibreOffice Base?

I found this:
http://sqlitestudio.pl
http://www.valentina-db.com
http://www.navicat.com/products/navicat-for-sqlite

Thank you.

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

Re: [sqlite] Exception when querying a range index

[Simon Slavin]

On 11 Mar 2014, at 10:59pm, St. B.  wrote:

> So I updated to V1.0.91 of SDS, but now I get the following error at each
> query run against the database :
> SQLite error (1): no such table: sqlite_stat1
> 
> What should I do to get the table properly created?

Open the database and execute the SQL command ANALYZE, either inside your app 
or using the Shell tool.  This creates that table, which will then be saved 
inside the database file.

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

Re: [sqlite] Exception when querying a range index

[Richard Hipp]

On Tue, Mar 11, 2014 at 6:59 PM, St. B.  wrote:

> HI,
>
> So I updated to V1.0.91 of SDS, but now I get the following error at each
> query run against the database :
> SQLite error (1): no such table: sqlite_stat1
>

What is the text of the query you are running?


>
> What should I do to get the table properly created?
>
>
>
>
> On Tue, Mar 11, 2014 at 12:50 PM, Simon Slavin 
> wrote:
>
> >
> > On 11 Mar 2014, at 11:06am, St. B.  wrote:
> >
> > > There is one writing thread (every 5 minutes). The one writing thread
> > > writes in the same database, but it is on a different table.
> >
> > Have you set a timeout for all your database handles (or just the one
> > handle if they're all using the same handle):
> >
> > 
> >
> > Many problems disappear if you set a timeout of ten seconds.
> >
> > Simon.
> > ___
> > 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
>



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

[sqlite] Manual call to sqlite3_wal_checkpoint

[veeresh kumar]

I have turned off using PRAGMA wal_autocheckpoint = 0. There is a background 
thread which would call sqlite3_wal_checkpoint at some interval of time to 
ensue that WAL size does not grow big.

I dont see any error returned by API sqlite3_wal_checkpoint. Also I dont see 
WAL size being reduced after successful call to API. One thing i am sure is 
there is no write operations happening during this process.

Is there something I am missing? 


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

Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Andrew Arnott]

Hi Joe,

Thanks for the workaround. It works!

Is there a place where a bug should be filed to track this?

--
Andrew Arnott
"I [may] not agree with what you have to say, but I'll defend to the death
your right to say it." - S. G. Tallentyre


On Tue, Mar 11, 2014 at 3:30 PM, Joe Mistachkin wrote:

>
> Andrew Arnott wrote:
> >
> > I tried adding sqlite3_set_directory to the sqliteWP8.cpp file but it got
> > too intense for my limited C++ knowledge.
> >
>
> If the Windows Phone 8 wrapper for SQLite does not properly set the
> temporary
> directory, queries that requires a temporary file (e.g. VACUUM) may fail.
>
> Until the wrapper package is changed to do this, the workaround would be to
> open a database connection and then immediately execute the following
> query:
>
> "PRAGMA temp_store_directory = 'C:\some\temp\directory';"
>
> Using the ApplicationData.LocalFolder property for the temporary directory
> value should work fine.
>
> --
> Joe Mistachkin
>
> ___
> 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] very slow fdsync() calls

[Nico Williams]

On Mon, Mar 10, 2014 at 7:01 PM, Jono Poff  wrote:
> I'm using Solaris 10 with zfs and I may be able to disable write caching on
> the entire disk, but would prefer not to!  Any ideas appreciated.

The way ZFS works, if you don't have a ZIL (ZFS Intent Log) device
then all fdatasync() calls sync metadata as well.  It has to be thus
due to ZFS' copy-on-write filesystem nature.

Things you should do:

1) Make sure that the SQLite3 page size and ZFS recordsize in the
dataset holding the SQLite DB match.

2) Make that size something appropriate (SQLite3 does -or used to-
default to a page size of 512 bytes on Unix, which is way too small
nowadays).

3) Get an SSD with high write IOPs rates, and use it as a ZIL.  Or a
battery-backed RAM ZIL.

4) If you do get a ZIL, make sure you set the SQLite3 page size and
ZFS recordsize to match the device's natural block size (which will
typically be either 4KB or 8KB).

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

Re: [sqlite] Exception when querying a range index

[St. B.]

HI,

So I updated to V1.0.91 of SDS, but now I get the following error at each
query run against the database :
SQLite error (1): no such table: sqlite_stat1

What should I do to get the table properly created?




On Tue, Mar 11, 2014 at 12:50 PM, Simon Slavin  wrote:

>
> On 11 Mar 2014, at 11:06am, St. B.  wrote:
>
> > There is one writing thread (every 5 minutes). The one writing thread
> > writes in the same database, but it is on a different table.
>
> Have you set a timeout for all your database handles (or just the one
> handle if they're all using the same handle):
>
> 
>
> Many problems disappear if you set a timeout of ten seconds.
>
> Simon.
> ___
> 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 on Windows Phone 8 fails on "VACUUM" command

[Joe Mistachkin]

Andrew Arnott wrote:
> 
> I tried adding sqlite3_set_directory to the sqliteWP8.cpp file but it got
> too intense for my limited C++ knowledge.
> 

If the Windows Phone 8 wrapper for SQLite does not properly set the
temporary
directory, queries that requires a temporary file (e.g. VACUUM) may fail.

Until the wrapper package is changed to do this, the workaround would be to
open a database connection and then immediately execute the following query:

"PRAGMA temp_store_directory = 'C:\some\temp\directory';"

Using the ApplicationData.LocalFolder property for the temporary directory
value should work fine.

--
Joe Mistachkin

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello,

Thanks to Keith's help I succeeded to set up a comparison a "native" sqrt()
versus a "python" mysqrt() function .

The speed-up in a best case non-realistic scenario is only 40%.

create_function() looks very performant.

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

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Jan Nijtmans]

2014-03-11 17:45 GMT+01:00 Keith Christian :
> Will have to troubleshoot the details of the makefile changes between
> the autoconf version from 3.8.3 to 3.8.4, to see why on this Cygwin
> environment a "sqlite3.exe" wasn't created.  That will be awhile,
> pretty busy at $WORK (not a bad thing.)

Don't know, but in my Cygwin enviroment everything works fine. The
diff's in your first mail indicate the changes in the TEA makefiles,
which were adapted to the latest versions in this commit:
   
those are used to build the sqlite3 Tcl extension, apparently
not what you are after. It works fine for me.

Anyway, I just uploaded the SQLite 3.8.4.1 build for Cygwin, it
just depends on the mirrors how fast they pick it up.

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

Re: [sqlite] About Syntax Diagrams

[Petite Abeille]

On Mar 11, 2014, at 3:51 AM, yulea...@163.com wrote:

>  Now I have a non-technical issues. The syntax diagrams for SQLite on your 
> SQLite website is so beautiful, and i want to draw one for myself but I do 
> not know what software you use to draw it. Can you tell me? and, is it the 
> software automatically converted from EBNF?

For the record, those are called syntax diagrams (or railroad diagrams) :

http://en.wikipedia.org/wiki/Syntax_diagram

SQLite call them bubble diagram:

http://www.sqlite.org/docsrc/finfo?name=art/syntax/bubble-generator.tcl

Alternatively, ditaa [1] may be of interest:

http://ditaa.sourceforge.net

Here are some example generated with ditaa of the Oracle ‘alter user’ statement:

http://picpaste.com/pics/alter_user_shadow.1394569031.png
http://picpaste.com/pics/alter_user_noshadow.1394568104.png

And here is one for ‘query block’:

http://picpaste.com/pics/query_block.1394568877.png

For reference, here are the original Oracle diagrams:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/img/alter_user.gif
http://docs.oracle.com/cd/E11882_01/server.112/e26088/img/query_block.gif



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

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Keith Christian]

Hick, Tim,

Thanks, I thought it was something like that.  Couldn't remember the
exact gcc switches as to whether the debug code may have been included
or not in Richard's command line, which was my first thought.  But at
least with Richard's command line I have a working, up to date sqlite3
executable!

Will have to troubleshoot the details of the makefile changes between
the autoconf version from 3.8.3 to 3.8.4, to see why on this Cygwin
environment a "sqlite3.exe" wasn't created.  That will be awhile,
pretty busy at $WORK (not a bad thing.)


Keith

On Tue, Mar 11, 2014 at 10:23 AM, Tim Streater  wrote:
> On 11 Mar 2014 at 16:11, Keith Christian  wrote:
>
>> The resulting file is quite large, almost 14 times the size of the
>> sqlite3 version 3.8.3 packages with Cygwin:
>>
>> ls -l /usr/bin/sqlite3.exe
>> -rwxr-xr-x 1 kchris Domain Users 60957 Feb  4 04:45 /usr/bin/sqlite3.exe
>>
>> ls -l ./sqlite3.exe
>> -rwxr-xr-x 1 kchris Domain Users 845353 Mar 11 10:05 ./sqlite3.exe
>
> That's about what I get under OS X if I link to an amalgamation instead of 
> the OS-supplied .dylib. The advantage is that you know which library you are 
> using, unlike if you dynamically link to a library. The application uses just 
> as much memory once linked, either way. These days the extra disk space used 
> for the app is of no consequence (unless you have millions of apps, I 
> suppose).
>
> --
> Cheers  --  Tim
>
> ___
> 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] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Tim Streater]

On 11 Mar 2014 at 16:11, Keith Christian  wrote: 

> The resulting file is quite large, almost 14 times the size of the
> sqlite3 version 3.8.3 packages with Cygwin:
>
> ls -l /usr/bin/sqlite3.exe
> -rwxr-xr-x 1 kchris Domain Users 60957 Feb  4 04:45 /usr/bin/sqlite3.exe
>
> ls -l ./sqlite3.exe
> -rwxr-xr-x 1 kchris Domain Users 845353 Mar 11 10:05 ./sqlite3.exe

That's about what I get under OS X if I link to an amalgamation instead of the 
OS-supplied .dylib. The advantage is that you know which library you are using, 
unlike if you dynamically link to a library. The application uses just as much 
memory once linked, either way. These days the extra disk space used for the 
app is of no consequence (unless you have millions of apps, I suppose).

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

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Hick Gunter]

That is because you are statically linking the SQLite shell and the SQLite 
library into a single executable file instead of having the shell (sqlite3.exe) 
and the library (sqlite3.dll?) in separate files.

-Ursprüngliche Nachricht-
Von: Keith Christian [mailto:keith1christ...@gmail.com]
Gesendet: Dienstag, 11. März 2014 17:11
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] sqlite3.exe file not produced by 
sqlite-autoconf-3080400.tar.gz on Cygwin

Richard,

Thanks for the reply.  Your instructions produced a working sqlite3.exe in the 
Cygwin environment, using sqlite-amalgamation-3080401.zip.

The resulting file is quite large, almost 14 times the size of the
sqlite3 version 3.8.3 packages with Cygwin:


ls -l /usr/bin/sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 60957 Feb  4 04:45 /usr/bin/sqlite3.exe

ls -l ./sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 845353 Mar 11 10:05 ./sqlite3.exe


But it works!

./sqlite3.exe
SQLite version 3.8.4.1 2014-03-11 15:27:36 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit


Keith



On Tue, Mar 11, 2014 at 8:30 AM, Richard Hipp  wrote:
> On Tue, Mar 11, 2014 at 9:46 AM, Keith Christian
> wrote:
>
>> A few days ago, I successfully compiled the previous version of the
>> autoconf tar package (sqlite-autoconf-3080300.tar.gz) and a
>> sqlite3.exe file was produced on a Cygwin environment.
>>
>> This morning, I downloaded sqlite-autoconf-3080400.tar.gz, unpacked,
>> ran 'make clean' and 'make', but no sqlite3.exe file was produced.
>>
>
>
> I'm sorry you are having trouble.
>
> I don't know what is wrong because everything works fine when I try it.
>
> As an alternative, consider downloading
> sqlite-amalgamation-3080400.zip and then running:
>
>  gcc -o sqlite3.exe -I. sqlite3.c shell.c
>
>
>
>
>
>>
>> I believe the issue is in some changes in the Makefiles produced
>> between 3080300 and 3080400.
>>
>> Makefile.am and Makefile.in are identical between 3080300 and 3080400.
>>
>> The cygwin environment on the machine has not been updated since
>> 3080300 was compiled on 7 Mar.
>>
>> See the output of 'wdiff' below.  Recent versions of the VIM editor
>> have a nice syntax mode for wdiff, copy the output below into VIM and
>> then type "syn on" and "set filetype=wdiff" at the colon prompt.
>>
>>
>>
>> wdiff -s ../../sqlite-autoconf-3080300/tea/Makefile
>> ../../sqlite-autoconf-3080400/tea/Makefile|grep "\[-.*-\]"|sed 'G;G'
>>
>>
>> PKG_LIB_FILE= [-libsqlite3.8.3.dll-] {+libsqlite3.8.4.dll+}
>>
>>
>> PKG_STUB_LIB_FILE = [-libsqlitestub3.8.3.a-] {+libsqlitestub3.8.4.a+}
>>
>>
>> INSTALL = [-/usr/bin/install-] {+$(SHELL)
>> $(srcdir)/tclconfig/install-sh+} -c
>>
>>
>> PACKAGE_VERSION = [-3.8.3-] {+3.8.4+}
>>
>>
>> #DEFS   = $(TCL_DEFS) -DPACKAGE_NAME=\"sqlite\"
>> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
>> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\
>> [-3.8.3\"-] {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
>> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
>> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
>> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
>> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
>> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
>> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
>> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
>> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
>> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
>> $(PKG_CFLAGS)
>>
>>
>> DEFS= -DPACKAGE_NAME=\"sqlite\"
>> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
>> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\
>> [-3.8.3\"-] {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
>> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
>> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
>> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
>> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
>> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
>> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
>> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
>> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
>> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
>> $(PKG_CFLAGS)
>>
>>
>>
>> Keith
>> ___
>> 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
___
sqlit

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Keith Christian]

Richard,

Thanks for the reply.  Your instructions produced a working
sqlite3.exe in the Cygwin environment, using
sqlite-amalgamation-3080401.zip.

The resulting file is quite large, almost 14 times the size of the
sqlite3 version 3.8.3 packages with Cygwin:


ls -l /usr/bin/sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 60957 Feb  4 04:45 /usr/bin/sqlite3.exe

ls -l ./sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 845353 Mar 11 10:05 ./sqlite3.exe


But it works!

./sqlite3.exe
SQLite version 3.8.4.1 2014-03-11 15:27:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit


Keith



On Tue, Mar 11, 2014 at 8:30 AM, Richard Hipp  wrote:
> On Tue, Mar 11, 2014 at 9:46 AM, Keith Christian
> wrote:
>
>> A few days ago, I successfully compiled the previous version of the
>> autoconf tar package (sqlite-autoconf-3080300.tar.gz) and a
>> sqlite3.exe file was produced on a Cygwin environment.
>>
>> This morning, I downloaded sqlite-autoconf-3080400.tar.gz, unpacked,
>> ran 'make clean' and 'make', but no sqlite3.exe file was produced.
>>
>
>
> I'm sorry you are having trouble.
>
> I don't know what is wrong because everything works fine when I try it.
>
> As an alternative, consider downloading sqlite-amalgamation-3080400.zip and
> then running:
>
>  gcc -o sqlite3.exe -I. sqlite3.c shell.c
>
>
>
>
>
>>
>> I believe the issue is in some changes in the Makefiles produced
>> between 3080300 and 3080400.
>>
>> Makefile.am and Makefile.in are identical between 3080300 and 3080400.
>>
>> The cygwin environment on the machine has not been updated since
>> 3080300 was compiled on 7 Mar.
>>
>> See the output of 'wdiff' below.  Recent versions of the VIM editor
>> have a nice syntax mode for wdiff, copy the output below into VIM and
>> then type "syn on" and "set filetype=wdiff" at the colon prompt.
>>
>>
>>
>> wdiff -s ../../sqlite-autoconf-3080300/tea/Makefile
>> ../../sqlite-autoconf-3080400/tea/Makefile|grep "\[-.*-\]"|sed 'G;G'
>>
>>
>> PKG_LIB_FILE= [-libsqlite3.8.3.dll-] {+libsqlite3.8.4.dll+}
>>
>>
>> PKG_STUB_LIB_FILE = [-libsqlitestub3.8.3.a-] {+libsqlitestub3.8.4.a+}
>>
>>
>> INSTALL = [-/usr/bin/install-] {+$(SHELL)
>> $(srcdir)/tclconfig/install-sh+} -c
>>
>>
>> PACKAGE_VERSION = [-3.8.3-] {+3.8.4+}
>>
>>
>> #DEFS   = $(TCL_DEFS) -DPACKAGE_NAME=\"sqlite\"
>> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
>> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
>> {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
>> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
>> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
>> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
>> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
>> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
>> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
>> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
>> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
>> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
>> $(PKG_CFLAGS)
>>
>>
>> DEFS= -DPACKAGE_NAME=\"sqlite\"
>> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
>> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
>> {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
>> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
>> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
>> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
>> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
>> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
>> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
>> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
>> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
>> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
>> $(PKG_CFLAGS)
>>
>>
>>
>> Keith
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Richard Hipp]

On Tue, Mar 11, 2014 at 9:46 AM, Keith Christian
wrote:

> A few days ago, I successfully compiled the previous version of the
> autoconf tar package (sqlite-autoconf-3080300.tar.gz) and a
> sqlite3.exe file was produced on a Cygwin environment.
>
> This morning, I downloaded sqlite-autoconf-3080400.tar.gz, unpacked,
> ran 'make clean' and 'make', but no sqlite3.exe file was produced.
>


I'm sorry you are having trouble.

I don't know what is wrong because everything works fine when I try it.

As an alternative, consider downloading sqlite-amalgamation-3080400.zip and
then running:

 gcc -o sqlite3.exe -I. sqlite3.c shell.c





>
> I believe the issue is in some changes in the Makefiles produced
> between 3080300 and 3080400.
>
> Makefile.am and Makefile.in are identical between 3080300 and 3080400.
>
> The cygwin environment on the machine has not been updated since
> 3080300 was compiled on 7 Mar.
>
> See the output of 'wdiff' below.  Recent versions of the VIM editor
> have a nice syntax mode for wdiff, copy the output below into VIM and
> then type "syn on" and "set filetype=wdiff" at the colon prompt.
>
>
>
> wdiff -s ../../sqlite-autoconf-3080300/tea/Makefile
> ../../sqlite-autoconf-3080400/tea/Makefile|grep "\[-.*-\]"|sed 'G;G'
>
>
> PKG_LIB_FILE= [-libsqlite3.8.3.dll-] {+libsqlite3.8.4.dll+}
>
>
> PKG_STUB_LIB_FILE = [-libsqlitestub3.8.3.a-] {+libsqlitestub3.8.4.a+}
>
>
> INSTALL = [-/usr/bin/install-] {+$(SHELL)
> $(srcdir)/tclconfig/install-sh+} -c
>
>
> PACKAGE_VERSION = [-3.8.3-] {+3.8.4+}
>
>
> #DEFS   = $(TCL_DEFS) -DPACKAGE_NAME=\"sqlite\"
> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
> {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
> $(PKG_CFLAGS)
>
>
> DEFS= -DPACKAGE_NAME=\"sqlite\"
> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
> {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
> $(PKG_CFLAGS)
>
>
>
> Keith
> ___
> 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

Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Andrew Arnott]

Hi Joe,

Thanks for your reply. After making that change (and also adding the
SQLite3.SetDirectory method definition itself since that too was inside an
#if) the app crashes because WP8 doesn't support the TemporaryFolder
property.

'Windows.Storage.ApplicationData.Current.TemporaryFolder' threw an
exception of type 'System.NotImplementedException'

When I replaced the use of TemporaryFolder with just LocalFolder, the line
again threw an exception, but with one I couldn't figure out how to
workaround:

System.NotSupportedException occurred
  _HResult=-2146233067
  _message=DllImport cannot be used on user-defined methods.
  HResult=-2146233067
  Message=DllImport cannot be used on user-defined methods.
  Source=Dart.WinPhone8Lib
  StackTrace:
   at SQLite.SQLite3.SetDirectory(UInt32 directoryType, String
directoryPath)
   at SQLite.SQLiteConnection..ctor(String databasePath,
SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks)
  InnerException:

I tried adding sqlite3_set_directory to the sqliteWP8.cpp file but it got
too intense for my limited C++ knowledge.


--
Andrew Arnott
"I [may] not agree with what you have to say, but I'll defend to the death
your right to say it." - S. G. Tallentyre


On Mon, Mar 10, 2014 at 10:57 PM, Joe Mistachkin wrote:

>
> Andrew Arnott wrote:
> >
> > The exception raised when I send "VACUUM" as a sql statement is:
> > SQLite.SQLiteException occurred
> >   _HResult=-2146233088
> >   _message=SQL logic error or missing database
> >   HResult=-2146233088
> >   Message=SQL logic error or missing database
> >   Source=Dart.WinPhone8Lib
> >   StackTrace:
> >at SQLite.SQLiteCommand.ExecuteNonQuery()
> >   InnerException:
> >
> > Thanks for any help you can offer!
> >
>
> In the "SQLite.cs" file, there is the following:
>
> #if NETFX_CORE
> SQLite3.SetDirectory(/*temp directory type*/2,
> Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);
> #endif
>
> Can you please try removing the surrounding "#if" from this code and try it
> again?
>
> --
> Joe Mistachkin
>
> ___
> 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] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Keith Christian]

A few days ago, I successfully compiled the previous version of the
autoconf tar package (sqlite-autoconf-3080300.tar.gz) and a
sqlite3.exe file was produced on a Cygwin environment.

This morning, I downloaded sqlite-autoconf-3080400.tar.gz, unpacked,
ran 'make clean' and 'make', but no sqlite3.exe file was produced.

I believe the issue is in some changes in the Makefiles produced
between 3080300 and 3080400.

Makefile.am and Makefile.in are identical between 3080300 and 3080400.

The cygwin environment on the machine has not been updated since
3080300 was compiled on 7 Mar.

See the output of 'wdiff' below.  Recent versions of the VIM editor
have a nice syntax mode for wdiff, copy the output below into VIM and
then type "syn on" and "set filetype=wdiff" at the colon prompt.



wdiff -s ../../sqlite-autoconf-3080300/tea/Makefile
../../sqlite-autoconf-3080400/tea/Makefile|grep "\[-.*-\]"|sed 'G;G'


PKG_LIB_FILE= [-libsqlite3.8.3.dll-] {+libsqlite3.8.4.dll+}


PKG_STUB_LIB_FILE = [-libsqlitestub3.8.3.a-] {+libsqlitestub3.8.4.a+}


INSTALL = [-/usr/bin/install-] {+$(SHELL)
$(srcdir)/tclconfig/install-sh+} -c


PACKAGE_VERSION = [-3.8.3-] {+3.8.4+}


#DEFS   = $(TCL_DEFS) -DPACKAGE_NAME=\"sqlite\"
-DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
{+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
{+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
{+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
-DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
-DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
-DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
-D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
-DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
-DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
-DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
$(PKG_CFLAGS)


DEFS= -DPACKAGE_NAME=\"sqlite\"
-DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
{+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
{+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
{+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
-DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
-DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
-DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
-D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
-DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
-DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
-DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
$(PKG_CFLAGS)



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

Re: [sqlite] very slow fdsync() calls

[Simon Slavin]

On 11 Mar 2014, at 12:20pm, Dan Kennedy  wrote:

> Or "PRAGMA locking_mode=EXCLUSIVE; PRAGMA journal_mode=PERSIST;",
> if there will only ever be a single connection to the database.
> 
> Or you could build with SQLITE_DISABLE_DIRSYNC, which omits all
> syncs on directories. Of course, that will slightly increase the
> chances of a power failure causing database corruption.

'slightly' is more like 'greatly'.  At least, that's my understanding of the 
characteristics of the most common file systems these days.

Also watch for file systems which store the length of the file as a file 
attribute you can read without opening the file.  This means that every time 
the file changes length, you /do/ have to rewrite the metadata.  One of the 
nice things about some SQLite modes is that the number of pages changes rarely 
(pages are marked as unused rather than the file shortened).  This means that 
it's not necessary to change metadata very often.

The "PRAGMA journal_mode=PERSIST" thing is a great tip and should make a of 
difference for some operations.  I'll add to that my advice to leave 
auto_vacuum off.

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

Re: [sqlite] very slow fdsync() calls

[Dan Kennedy]

On 03/11/2014 07:01 AM, Jono Poff wrote:

Hi,

I have an application that uses sqlite3.

Investigating a problem with the app stalling occasionally I found 
that (every hour or two) an fdsync() system call from sqlite3_step() 
was taking over 3 seconds to return.


On closer investigation, the file descriptor in these calls point to 
the directory in which the database resides, rather than the database 
of journal file itself, and are opened with O_LARGEFILE flag and no 
O_RDWR and transfers zero bytes.


Presumably this is updating metadata, but I understood fdsync() 
doesn't touch metadata.


Can anybody suggest a way to prevent sqlite making these fdsync() 
calls?  The database updates aren't high volume (maybe 20/sec max) but 
the app needs to be responsive.  I traced mmap() calls in my process 
and found none relating  to the sqlite db.  I thought maybe I could 
force sqlite to not cache writes this way.  It seems that using pragma 
to disable caching  in sqlite simply exposes the database to 
corruption rather than forcing synchronous writing to disk.  Is this 
correct?


I'm using Solaris 10 with zfs and I may be able to disable write 
caching on the entire disk, but would prefer not to!  Any ideas 
appreciated.


It's probably syncing the directory to make sure that the entry
corresponding to a journal file has made it to disk before it
begins updating the database file. Otherwise, if a power failure
were to occur, following recovery SQLite might find that although
the contents of the journal file have been synced to disk, they
cannot be located as the directory entry has been lost. Leading
to corruption.

Using "PRAGMA journal_mode=WAL" might prevent SQLite from syncing
the directory as often.

Or "PRAGMA locking_mode=EXCLUSIVE; PRAGMA journal_mode=PERSIST;",
if there will only ever be a single connection to the database.

Or you could build with SQLITE_DISABLE_DIRSYNC, which omits all
syncs on directories. Of course, that will slightly increase the
chances of a power failure causing database corruption.

Dan.




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

Re: [sqlite] About Syntax Diagrams

[Simon Slavin]

On 11 Mar 2014, at 2:51am, yulea...@163.com wrote:

>  Hi, glad you had designed SQLite what a great open source software. 
> 
>  Now I have a non-technical issues. The syntax diagrams for SQLite on your 
> SQLite website is so beautiful, and i want to draw one for myself but I do 
> not know what software you use to draw it. Can you tell me? and, is it the 
> software automatically converted from EBNF?

Take a look at this and get back to us if you have any questions:



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

[sqlite] About Syntax Diagrams

[yulea...@163.com]

Dear SQLite Team:

  Hi, glad you had designed SQLite what a great open source software. 

  Now I have a non-technical issues. The syntax diagrams for SQLite on your 
SQLite website is so beautiful, and i want to draw one for myself but I do not 
know what software you use to draw it. Can you tell me? and, is it the software 
automatically converted from EBNF?

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

[sqlite] very slow fdsync() calls

[Jono Poff]

Hi,

I have an application that uses sqlite3.

Investigating a problem with the app stalling occasionally I found that 
(every hour or two) an fdsync() system call from sqlite3_step() was 
taking over 3 seconds to return.


On closer investigation, the file descriptor in these calls point to the 
directory in which the database resides, rather than the database of 
journal file itself, and are opened with O_LARGEFILE flag and no O_RDWR 
and transfers zero bytes.


Presumably this is updating metadata, but I understood fdsync() doesn't 
touch metadata.


Can anybody suggest a way to prevent sqlite making these fdsync() 
calls?  The database updates aren't high volume (maybe 20/sec max) but 
the app needs to be responsive.  I traced mmap() calls in my process and 
found none relating  to the sqlite db.  I thought maybe I could force 
sqlite to not cache writes this way.  It seems that using pragma to 
disable caching  in sqlite simply exposes the database to corruption 
rather than forcing synchronous writing to disk.  Is this correct?


I'm using Solaris 10 with zfs and I may be able to disable write caching 
on the entire disk, but would prefer not to!  Any ideas appreciated.


Cheers,
Jono P.



--

--
This email, including any attachments, is only for the intended recipient. 
It is subject to copyright, is confidential and may be the subject of legal 
or other privilege, none of which is waived or lost by reason of this 
transmission.
If you are not an intended recipient, you may not use, disseminate, 
distribute or reproduce such email, any attachments, or any part thereof. 
If you have received a message in error, please notify the sender 
immediately and erase all copies of the message and any attachments.
Unfortunately, we cannot warrant that the email has not been altered or 
corrupted during transmission nor can we guarantee that any email or any 
attachments are free from computer viruses or other conditions which may 
damage or interfere with recipient data, hardware or software. The 
recipient relies upon its own procedures and assumes all risk of use and of 
opening any attachments.

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

Re: [sqlite] Exception when querying a range index

[Simon Slavin]

On 11 Mar 2014, at 11:06am, St. B.  wrote:

> There is one writing thread (every 5 minutes). The one writing thread
> writes in the same database, but it is on a different table.

Have you set a timeout for all your database handles (or just the one handle if 
they're all using the same handle):



Many problems disappear if you set a timeout of ten seconds.

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

Re: [sqlite] Bug in sqlite? Can't read tables just after creating them

[Simon Slavin]

On 11 Mar 2014, at 11:31am, Fabrice Triboix  wrote:

> The problem is actually elsewhere. I changed the filesystem and it works 
> fine. 
> 
> So the problem is not with sqlite but with our special filesystem. 

Make sure your code tests the results returned by all API calls to see that 
they are returning SQLITE_OK where appropriate.  Many times, the call which you 
realise is returning the wrong thing is after the call which created the 
problem.

But I'm glad to see you have it working.

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

Re: [sqlite] Bug in sqlite? Can't read tables just after creating them

[Fabrice Triboix]

Hi Richard,

The problem is actually elsewhere. I changed the filesystem and it works fine. 

So the problem is not with sqlite but with our special filesystem. 

Sorry for having raised the alarm too quickly!

Best regards,

  Fabrice

-Original Message-
From: Richard Hipp 
Sender: sqlite-users-bounces@sqlite.orgDate: Tue, 11 Mar 2014 07:27:37 
To: General Discussion of SQLite Database
Reply-To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Bug in sqlite? Can't read tables just after creating
them

On Tue, Mar 11, 2014 at 6:35 AM,  wrote:

> Hi,
>
> I found a problem in sqlite.
>
> In essence, here is what my code does:
>  1 - It opens a database file
>  2 - If it doesn't find certain tables, it assumes this is a new one and
> creates the necessary tables and add a few entries in one of them (let's
> call it mytable)
>  3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;'
>
> When I run this code on Debian, I don't have any problem.
>
> When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such
> table: mytable'. I found out that closing and then re-opening the database
> file works. So I do steps 1 and 2, and close and re-open the database
> file, and now step 3 works fine!
>
> For the time being, I have this workaround of closing/re-opening the
> database file, but that's really a kludge.
>
> Any idea about from where this could come from?
>

No ideas. Please enable the error and warning log (
http://www.sqlite.org/errlog.html) and see if that provides any further
information.


>
> Many thanks for any ideas!
>
>   Fabrice
>
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite? Can't read tables just after creating them

[Richard Hipp]

On Tue, Mar 11, 2014 at 6:35 AM,  wrote:

> Hi,
>
> I found a problem in sqlite.
>
> In essence, here is what my code does:
>  1 - It opens a database file
>  2 - If it doesn't find certain tables, it assumes this is a new one and
> creates the necessary tables and add a few entries in one of them (let's
> call it mytable)
>  3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;'
>
> When I run this code on Debian, I don't have any problem.
>
> When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such
> table: mytable'. I found out that closing and then re-opening the database
> file works. So I do steps 1 and 2, and close and re-open the database
> file, and now step 3 works fine!
>
> For the time being, I have this workaround of closing/re-opening the
> database file, but that's really a kludge.
>
> Any idea about from where this could come from?
>

No ideas. Please enable the error and warning log (
http://www.sqlite.org/errlog.html) and see if that provides any further
information.


>
> Many thanks for any ideas!
>
>   Fabrice
>
>
> ___
> 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

Re: [sqlite] Fwd: Exception when querying a range index

[St. B.]

On Tue, Mar 11, 2014 at 11:13 AM, RSmith  wrote:

>
> On 2014/03/11 11:58, St. B. wrote:
>
>> I still have a question. Since I have many threads (between 100 and 200)
>> that do reading on the table that has the R Tree, and 1 thread that will
>> write to another table once every five minutes, is it normal that I get
>> database is locked error on a regular basis? I was under the impression
>> that read only is lock free in when doing multiple access, and that locks
>> should only occur during the writes.
>>
>
> So to be sure - You have no threads at all writing to this DB? Just 200
> that read often and 1 that reads every 5 minutes (and then writes to a
> completely different DB)?
>
There is one writing thread (every 5 minutes). The one writing thread
writes in the same database, but it is on a different table.

>
> You cannot get locked errors then. Unless maybe some of these reads happen
> inside a transaction that is exclusive. Or a pragma that locks or does some
> function that requires a lock is called.
>
>
>
>
> ___
> 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] Bug in sqlite? Can't read tables just after creating them

[ftriboix]

Hi,

I found a problem in sqlite.

In essence, here is what my code does:
 1 - It opens a database file
 2 - If it doesn't find certain tables, it assumes this is a new one and
creates the necessary tables and add a few entries in one of them (let's
call it mytable)
 3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;'

When I run this code on Debian, I don't have any problem.

When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such
table: mytable'. I found out that closing and then re-opening the database
file works. So I do steps 1 and 2, and close and re-open the database
file, and now step 3 works fine!

For the time being, I have this workaround of closing/re-opening the
database file, but that's really a kludge.

Any idea about from where this could come from?

Many thanks for any ideas!

  Fabrice


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

Re: [sqlite] Fwd: Exception when querying a range index

[RSmith]

On 2014/03/11 11:58, St. B. wrote:

I still have a question. Since I have many threads (between 100 and 200)
that do reading on the table that has the R Tree, and 1 thread that will
write to another table once every five minutes, is it normal that I get
database is locked error on a regular basis? I was under the impression
that read only is lock free in when doing multiple access, and that locks
should only occur during the writes.


So to be sure - You have no threads at all writing to this DB? Just 200 that read often and 1 that reads every 5 minutes (and then 
writes to a completely different DB)?


You cannot get locked errors then. Unless maybe some of these reads happen inside a transaction that is exclusive. Or a pragma that 
locks or does some function that requires a lock is called.




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

Re: [sqlite] Fwd: Exception when querying a range index

[St. B.]

Hi Dan,

Thanks for this answer, I will update the SDS to the latest version to see
how it goes, and if we get more explicit.

I still have a question. Since I have many threads (between 100 and 200)
that do reading on the table that has the R Tree, and 1 thread that will
write to another table once every five minutes, is it normal that I get
database is locked error on a regular basis? I was under the impression
that read only is lock free in when doing multiple access, and that locks
should only occur during the writes.

Regards,


On Tue, Mar 11, 2014 at 10:30 AM, Dan Kennedy  wrote:

> On 03/11/2014 04:17 AM, St. B. wrote:
>
>> Hi,
>>
>>
>> On Mon, Mar 10, 2014 at 6:37 PM, Dan Kennedy 
>> wrote:
>>
>>  On 03/10/2014 06:49 PM, St. B. wrote:
>>>
>>>  Hi,

 I posted this question last year, but did not get any feed back. Is this
 something that can be handled on the ML, or should I ask in another
 place?

 Regards,

 -- Forwarded message --
 From: St. B. 
 Date: Sun, Jan 6, 2013 at 11:45 PM
 Subject: Exception when querying a range index
 To: sqlite-users@sqlite.org


 Hi All,

 I have an application using sqlite to store coast lines (application
 runs
 on windows and uses the .Net version of sqlite v1.0.83). I use range
 indexes to look-up coast line within range of a given point.

 During run time I find that the following exception occurs.

 vtable constructor failed: MapLevel_Idx3

  How is virtual table MapLevel_Idx3 created? Is it an rtree table?
>>>
>>>  The virtual table  is created with the following sql statement:
>>
>> CREATE VIRTUAL TABLE MapLevel_Idx4 USING rtree(
>> id,  -- Integer primary key
>> minX, maxX,  -- Minimum and maximum X coordinate
>> minY, maxY   -- Minimum and maximum Y coordinate
>> );
>>
>> Yes it is an rtree table
>>
>>  SQLite version?
>>>
>>>  I am using  system.data.SQLite v 1.0.84.0. I guess it includes sqlites
>> in v
>> 3.7.6.3 (but am not sure at all)
>>
>
> If it's possible, you should upgrade to a newer version of SDS. 3.7.6.3
> is almost three years old now.
>
> The "vtable constructor failed" message tells us that an error occurred
> while trying to initialize the virtual table instance, but it doesn't
> tell us any more than that. However, the error messages returned by the
> rtree module have improved a lot since 3.7.6, so I fancy that if you
> upgrade the new version will spit out a more specific complaint.
>
> For example, I just tested the effects of encountering an SQLITE_BUSY
> while initializing the rtree instance. With 3.8.4 the error message is
> "database is locked". But with 3.7.6 all you get is "vtable constructor
> failed".
>
> There is a good chance that this is what is happening in your environment
> as well - the db is locked and the error message is deceptive. In which
> case you should just retry the operation or add a busy-handler to the
> database connection. But upgrading first is the safer option.
>
> Dan.
>
>
>
>
>
>
>
>
> ___
> 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] Fwd: Exception when querying a range index

[Dan Kennedy]

On 03/11/2014 04:17 AM, St. B. wrote:

Hi,


On Mon, Mar 10, 2014 at 6:37 PM, Dan Kennedy  wrote:


On 03/10/2014 06:49 PM, St. B. wrote:


Hi,

I posted this question last year, but did not get any feed back. Is this
something that can be handled on the ML, or should I ask in another place?

Regards,

-- Forwarded message --
From: St. B. 
Date: Sun, Jan 6, 2013 at 11:45 PM
Subject: Exception when querying a range index
To: sqlite-users@sqlite.org


Hi All,

I have an application using sqlite to store coast lines (application runs
on windows and uses the .Net version of sqlite v1.0.83). I use range
indexes to look-up coast line within range of a given point.

During run time I find that the following exception occurs.

vtable constructor failed: MapLevel_Idx3


How is virtual table MapLevel_Idx3 created? Is it an rtree table?


The virtual table  is created with the following sql statement:

CREATE VIRTUAL TABLE MapLevel_Idx4 USING rtree(
id,  -- Integer primary key
minX, maxX,  -- Minimum and maximum X coordinate
minY, maxY   -- Minimum and maximum Y coordinate
);

Yes it is an rtree table


SQLite version?


I am using  system.data.SQLite v 1.0.84.0. I guess it includes sqlites in v
3.7.6.3 (but am not sure at all)


If it's possible, you should upgrade to a newer version of SDS. 3.7.6.3
is almost three years old now.

The "vtable constructor failed" message tells us that an error occurred
while trying to initialize the virtual table instance, but it doesn't
tell us any more than that. However, the error messages returned by the
rtree module have improved a lot since 3.7.6, so I fancy that if you
upgrade the new version will spit out a more specific complaint.

For example, I just tested the effects of encountering an SQLITE_BUSY
while initializing the rtree instance. With 3.8.4 the error message is
"database is locked". But with 3.7.6 all you get is "vtable constructor
failed".

There is a good chance that this is what is happening in your environment
as well - the db is locked and the error message is deceptive. In which
case you should just retry the operation or add a busy-handler to the
database connection. But upgrading first is the safer option.

Dan.







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