[sqlite] UPDATE reports SQL logic error or missing database

[Simon Posnjak]

Hi all!

When I try to do UPDATE of a record i get: SQL logic error or missing
database (error code 1)? The database is not missing or corrupted
because I can UPDATE stuff in it with sqlite3 and I can INSERT into,
SELECT from it and also DELETE. My application uses two threads. The
first thread does INSERT,UPDATE,DELETE and SELECT in to the db and the
other thread only does SELECT. To speed up things I use precompiled
query's. They are created at application startup and they last
throughout  the whole  running time  of the application. I have read and
followed the instructions
(http://www.sqlite.org/cvstrac/wiki?p=MultiThreading) on how to build
multi threaded applications.

Regards Simon



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite vs MS Access

[Alex Roston]

I wrote a little server based on SQLite, and it works just fine. I 
handled the "multiple processes" issue by simply having the software 
make a list of requests, then handling those requests serially.  
Essentially there are two parts to the software. One listens for 
requests and lists them. It does nothing else, and it does the job with 
as little overhead as possible, including minimal overhead. I took the 
easy way out and the list is on a ramdisk, so I didn't have to deal with 
interprocess communications.


The second part of the software takes requests off the bottom of the 
list, (FIFO) does gobs of error checking, then processes the request. 
Unless you expect to have massive numbers of users bombarding the 
server, it should work fairly well.


Alex



Allan Miller wrote:


Hi Mark:

We briefly considered using JET (the Microsoft database engine for Access), but
went with SQLite in the end for a few reasons:

1. portability was important to us
2. open source with lots of applications (seems very well supported)
3. it seems to handle large databases very well

One of our deciding factors was that POPFile switched to SQLite and its author
was very pleased with it.

Also, if you ever have to look at the source code, it's a real pleasure to read,
compared to many open-source projects I've seen.

If you really need tight integration with Microsoft Office applications, you'll 
probably
be happier with JET, but otherwise, I think you'll find SQLite will exceed your 
expectations.

The only "weak point" of SQLite is its support for multiple processes accessing
the same database.  That leaves something to be desired, but to be fair, that's 
not
really what it was designed for.  JET isn't exactly stellar in that department 
either.

One thing to keep in mind is that if you write your own interface layer to the 
database
and make it fairly generic, and try to stay away from SQL extensions, you can 
minimize
the effort needed to switch to another database should that become necessary.

Also, we've never used it, but I know that there is an ODBC driver for SQLite, 
so
that may satisfy your needs for using the Access front-end, integrating with 
other
applications, etc.

Allan Miller


- Original Message -
From: "Allan, Mark" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, September 06, 2006 1:05 PM
Subject: [sqlite] SQLite vs MS Access


Hi,

After successfully using SQLite on an embedded device, we are now thinking of 
using SQLite in a PC application.

This would be used in place of an MS Access database on a local/network disk. I 
believe that SQLite should be quicker for both
transactions and queries than Access. The one draw back that comes to mind 
maybe portability (i.e. accessing data outside of the
application), although the data would be portable across machines (PC, Mac, 
Unix, etc) should we ever need it to be in the future.

Is there any webpage, or does anyone have any information comparing the 
benefits of the two. I can only find comparisons between
MySQL and PostgreSQL. This information would aid us greatly in deciding whether 
to use SQLite or stick with Access.

Any help/advice will be gratefully received.

Mark


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to
which it is addressed and may contain information that is privileged, 
confidential, and exempt from disclosure under applicable law.
If the reader of this message is not the intended recipient, or the employee or 
agent responsible for delivering the message to the
intended recipient, you are hereby notified that any dissemination, 
distribution, forwarding, or copying of this communication is
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and
delete the original message immediately.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-


 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite vs MS Access

[Anne Kirchhellen]

On Wed, 6 Sep 2006 18:05:36 +0100, you wrote:

Hello Mark

I think, either Access or ODBC is not a real good idea.
SQLite is a real good idea, if you need a small embedded database. 

So far as I know, its even not a good idea, to compare  MySQL and
Postgres to SQLite, because MySQL is a Client/Server-DB, SQLIte a
really actual Flatfile-Embedded-DB, primary running on your local
machine.

If you can use ADO instead of ODBC, its allways the better way.Try to 
find an ADO-Provider, so you can use SQLIte and Access (or MSSQL too)
with a similar Syntax.

Greetings from Germany
Anne

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite vs MS Access

[Robert Simpson]

> -Original Message-
> From: Allan, Mark [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 06, 2006 10:06 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQLite vs MS Access
> 
> Hi,
> 
> After successfully using SQLite on an embedded device, we are 
> now thinking of using SQLite in a PC application.
> 
> This would be used in place of an MS Access database on a 
> local/network disk. I believe that SQLite should be quicker 
> for both transactions and queries than Access. The one draw 
> back that comes to mind maybe portability (i.e. accessing 
> data outside of the application), although the data would be 
> portable across machines (PC, Mac, Unix, etc) should we ever 
> need it to be in the future.
> 
> Is there any webpage, or does anyone have any information 
> comparing the benefits of the two. I can only find 
> comparisons between MySQL and PostgreSQL. This information 
> would aid us greatly in deciding whether to use SQLite or 
> stick with Access.

If you want some performance comparisons, I have them at:
http://sqlite.phxsoftware.com/forums/thread/622.aspx

Off the top of my head, here's what's wrong with Access:
- It is not ACID compliant
- It uses non-standard SQL syntax
- Databases cannot be larger than 2gb
- It is dead technology, having reach end-of-life at MS
- Databases are massively bloated, further encroaching on the 2gb limit

SQLite smokes Access in every category (speed of
inserts/updates/selects/joins/etc) including db size.

Robert





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite vs MS Access

[John Stanton]

George Ionescu wrote:

Helo Allan,



This would be used in place of an MS Access database on a local/network


disk. I believe that SQLite should be quicker for both 


transactions and queries than Access. The one draw back that comes to mind


maybe portability (i.e. accessing data outside of the 


application), although the data would be portable across machines (PC,


Mac, Unix, etc) should we ever need it to be in the future.



Is there any webpage, or does anyone have any information comparing the


benefits of the two. I can only find comparisons between 


MySQL and PostgreSQL. This information would aid us greatly in deciding


whether to use SQLite or stick with Access.



Any help/advice will be gratefully received.



Trying to compare sqlite to MySQL or PostgreSQL is (a little) like comparing
apples and oranges: they have different targets. While sqlite was made for
embedding into an application, it is not a database server (like the other
two you mentioned). This doesn't necessarily mean it won't be faster in some
situations. It just means that it won't handle exceptionally well the
concurrent access to your database.

Putting the database on a network share and accessing it from there is
definitively something to be avoided. I've had two customers (for our
SQLiteDb product) which experienced 'database is locked' errors with only
two clients accessing a single db on a network. This happens because of the
faulty implementation of file locking on NFS. We do have a product (a
multithreaded TCP/IP server, which is currently in beta stage) for accessing
sqlite databases over TCP/IP. It won't boost performance to handle hundreds
of users but we have succesfully tested against 10 clients. And, if your
queries are written right, the server should be able to handle more
concurent users.

As far as performance, sqlite is definitively a winner here. If you're
interested in some benchmarks, check out
http://www.terrainformatica.com/sqlitedb/downloads.whtm. You don't have to
purchase SQLiteDb: just download the demo and you will find there a direct
comparison between JET (accessed via ADO) and SQLiteDb.

Accessing sqlite via ODBC is an option. However, the ADO dependecy you're
stuck with is something that should be avoided.

Regards,
George Ionescu

Adding to George's comments, we built a multi-threaded application 
server which embeds Sqlite and uses HTTP and that works much better in 
some cases than having a DBMS server like PostgreSQL connected to a 
regular WWW server.  The reason is that everything runs in one process 
and the threads are carefully synchronized.  It does not have IPC 
overheads and effectively hooks Sqlite to its clients using the 
efficient "sendfile/TransmitFile" interface, resulting in performance of 
 the highest level.


Be very wary if you want to use Sqlite as a shared file.  It is not a 
server so you have to take care of contentions in your application.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] [ANN] New DBManager Standard Edition 3.2.0

[COS]

DBTools Software is pleased to announce the new DBManager Standard Edition
version 3.2.0.
This application replaces the old DBManager Freeware Edition (3.1.1) and it
can be used for FREE
in the following conditions:

- Personal use only

You can register the Standard Edition at any time you want and if you choose
to, at a very small fee.
Registered users will get the following additional features:

- Personal Use License restriction removed. You can use DBManager Standard
for commercial purposes
- Can manage multiple servers and multiple database engines. MySQL,
PostgreSQL, SQLite, Interbase,
  Firebird and DBF Tables
- Can execute queries on DBF Tables. Requires DAO 3.51 or later (usually
installed with Windows/Office)
- Can execute multiple statements queries
- Query/Procedure/View Editors with Planner and Debugger capabilities
- Picture viewer allow you to view image content from binary fields (Query
Editor and Datasheet)
- Can connect to Workgroup Servers
- Can connect to MySQL using the MySQL Webservice. Very soon other engines
will also have this feature builtin

The application is now available in our Downloads Center
(http://www.dbtools.com.br/EN/downloads).

What is DBManager?

DBManager is an application for Database Management available only for
Windows environments. Supporting the most popular database engines: MySQL,
PostgreSQL, Interbase, Firebird, SQlite, Oracle, MSAccess, MSSQL Server,
Sybase, ODBC and DBF Tables. To learn more about DBManager check out
http://www.dbtools.com.br/EN/dbmanagerpro.

MONTHLY SPECIAL OFFER

For limited time you will be able to benefit from our sales promotion.
During the promotion period you can purchase
any of our products with a considerable discount. To see all the details
check http://www.dbtools.com.br/EN/prices.php.
This promotion is valid for the following products:

- DBManager Professional Enterprise Edition (Open Source Pack and Master
Pack)
- DBManager Standard Edition
- DBTools QueryIT

Best Regards,

DBTools Software
http://www.dbtools.com.br



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How to find errors ?

[Martin Alfredsson]

Sorry if this messages dont follow the thread, I only have digest
and I dont know how to attach msg to the correct thread.


I have just now put together wiki pages that describe these errors.
See

  http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
  http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence

Other readers of this mailing list are encouraged to review and
extend my remarks on those pages.
--
D. Richard Hipphwaci.com>



Just checked with my customer,
He gets 5 -Database is locked, that is SQLITE_BUSY every time
if he starts the app from another machine. This makes me think
that I have some sort of configuration error since every SQL
request on any table gives this error.

- Is there any way to ask the database if its locked (from the app that 
apparently locks it) ?


- I use these pragmas when I create the database, do they affect ?
 PRAGMA page_size=4096;
 PRAGMA auto_vacuum = 1;

- Do sqlite3_exec() need any release or any close (three last parameters 
are NULL).


- Will opening, reading from and closing another database affect ?

- Will nested selects from the same table affect ?

- Can the "library routine called out of sequence" be a consiquence of 
the above ?



Sorry for bugging this list with all these questions but I need to help 
my customer
and I need to know what I do wrong so that I can avoid trouble in the 
future.


John Martin Alfredsson
ma1999ATjmaDOTse

[sqlite] Re: SQLite vs MS Access

[George Ionescu]

Helo Allan,

> This would be used in place of an MS Access database on a local/network
disk. I believe that SQLite should be quicker for both 
> transactions and queries than Access. The one draw back that comes to mind
maybe portability (i.e. accessing data outside of the 
> application), although the data would be portable across machines (PC,
Mac, Unix, etc) should we ever need it to be in the future.

> Is there any webpage, or does anyone have any information comparing the
benefits of the two. I can only find comparisons between 
> MySQL and PostgreSQL. This information would aid us greatly in deciding
whether to use SQLite or stick with Access.

> Any help/advice will be gratefully received.

Trying to compare sqlite to MySQL or PostgreSQL is (a little) like comparing
apples and oranges: they have different targets. While sqlite was made for
embedding into an application, it is not a database server (like the other
two you mentioned). This doesn't necessarily mean it won't be faster in some
situations. It just means that it won't handle exceptionally well the
concurrent access to your database.

Putting the database on a network share and accessing it from there is
definitively something to be avoided. I've had two customers (for our
SQLiteDb product) which experienced 'database is locked' errors with only
two clients accessing a single db on a network. This happens because of the
faulty implementation of file locking on NFS. We do have a product (a
multithreaded TCP/IP server, which is currently in beta stage) for accessing
sqlite databases over TCP/IP. It won't boost performance to handle hundreds
of users but we have succesfully tested against 10 clients. And, if your
queries are written right, the server should be able to handle more
concurent users.

As far as performance, sqlite is definitively a winner here. If you're
interested in some benchmarks, check out
http://www.terrainformatica.com/sqlitedb/downloads.whtm. You don't have to
purchase SQLiteDb: just download the demo and you will find there a direct
comparison between JET (accessed via ADO) and SQLiteDb.

Accessing sqlite via ODBC is an option. However, the ADO dependecy you're
stuck with is something that should be avoided.

Regards,
George Ionescu


-
To unsubscribe, send email to [EMAIL PROTECTED]
-