Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation

[qinligeng]

I am sorry that I can not provide any sample records of the table because every 
time I acces the table sqlite will crash.

exceute sql: select * from carimages limit 0,1;
OK!
exceute sql: select * from carimages limit 1,1;
OK!
exceute sql: select * from carimages limit 2,1;
CRASH!!

Maybe the database has been damaged.

The shcema of table "CarImages" is :
CREATE TABLE CarImages(
 CarID char(32) NOT NULL,
 CarNumber char(20) NULL,
 OpTime datetime NOT NULL ,
 TSCode char(6) NOT NULL,
 LaneNum int NOT NULL,
 PicBigPlate Long null,
 PicSmallPlate Long null,
 PicBlackWhitePlate Long null,
 PicLane Long null,
 Constraint CarImages_Key Primary Key (CarID)
);


- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Wednesday, May 14, 2008 9:50 PM
Subject: Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation


> [EMAIL PROTECTED] wrote:
>> When I execute sql statement "delete from Carimages where OpTime 
>> <'2008-05-01 00:00:00'"  in my database, sqlite3 crashed.
>> The Exception Information reported by XP is:
>> Code: 0xc005   Flags:0x  
>> Record: 0x   Address: 0x00406652
>> 
>> The sqlite3.exe is downloaded from http://www.sqlite.org/sqlite-3_5_8.zip
>> The database file is to big ( about 600M, after compressed by WinRAR, the 
>> size is 18M), so I can't upload here.
> 
> Please don't hijack other threads. 
> http://en.wikipedia.org/wiki/Thread_hijacking
> 
> What is the schema of Carimages table? Can you provide a couple of 
> sample records instead of the entire database?
> 
> Dennis Cote
> ___
> 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 C API question

[Jay A. Kreibich]

On Wed, May 14, 2008 at 09:26:53PM -0700, Jon Dixon scratched on the wall:
> I am a Perl programmer, and I am trying to update the DBD::SQLite 
> package to use version 3 rather than version 2 of SQLite (somehow it 
> doesn't seem that anyone else has done this yet). 

  DBD::SQLite-1.14 (http://search.cpan.org/~msergeant/DBD-SQLite-1.14/)
  uses SQLite 3.4.2. 

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 C API question

[Jon Dixon]

I am a Perl programmer, and I am trying to update the DBD::SQLite package to 
use version 3 rather than version 2 of SQLite (somehow it doesn't seem that 
anyone else has done this yet). Unfortunately, I am really rusty on my C 
skills, which are needed to handle the glue between Perl and the SQLite 
routines. Using the API description with the old version of the routines, I 
believe I have most of them changed over, but the part that steps through and 
grabs a row at a time is a little beyond me. The old routine used the following 
command:
 imp_sth->retval =
sqlite_step(imp_sth->vm, 
 
&(imp_sth->ncols), (const char ***)&(imp_sth->results), (const
char ***)&(imp_sth->coldata));
 to load the proper values in the proper places. Unfortunately for the purposes 
of this exercise, the sqlite_step interface is now significantly different.


So can someone with more C background help me out with a snippet of code using 
the new API that will load imp_sth->ncols, imp_sth->results, and 
imp_sth->coldata in the same manner that the old API would have?


Thanks,


Jon


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

[sqlite] SQLite remote management tools?

[Richard Klein]

Are there any GUI-based tools that provide
management of a remote SQLite database?

I'm looking for a workstation-based tool
that will allow me to manage a database
on an embedded device.

Thanks in advance,
- Richard Klein
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confirming locking and transaction characteristics

[Igor Tandetnik]

David Barrett <[EMAIL PROTECTED]> wrote:
> Igor Tandetnik wrote:
>> SQLite detects deadlock situation. The call trying to execute the
>> update statement fails immediately with SQLITE_BUSY error. Neither
>> transaction releases its locks: to make progress, one of them has to
>> explicitly roll back.
>
> Aha, got it.  One more question: take the same scenario as before, but
> now have two distinct tables (barA and barB) inside the same database,

Makes no difference: SQLite locks at file level, not table level.

Igor Tandetnik 



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

Re: [sqlite] Confirming locking and transaction characteristics

[David Barrett]

Igor Tandetnik wrote:
> David Barrett <[EMAIL PROTECTED]> wrote:
>> There is a single table (bar) with a single column (foo) with a single
>> row containing the integer value "1".  Two processes (A and B) have
>> opened the database in READWRITE mode.  They both try to atomically
>> increment the value at the same time.  What happens?
>>
>> 1) [Process A] BEGIN TRANSACTION
>> 2) [Process B] BEGIN TRANSACTION
>> 3) [A] SELECT foo FROM bar
>> (A acquires a SHARED lock)
>> 4) [B] SELECT foo FROM bar
>> (B acquires a SHARED lock)
>> 5) [A] UPDATE bar SET foo=2
>> (A acquires the RESERVED lock)
>> 6) [B] UPDATE bar SET foo=2
>> (? query fails, B's transaction aborted, SHARED lock released?)
> 
> SQLite detects deadlock situation. The call trying to execute the update 
> statement fails immediately with SQLITE_BUSY error. Neither transaction 
> releases its locks: to make progress, one of them has to explicitly roll 
> back.

Aha, got it.  One more question: take the same scenario as before, but 
now have two distinct tables (barA and barB) inside the same database, 
and have each process only deal with its own table:

1) [Process A] BEGIN TRANSACTION
2) [Process B] BEGIN TRANSACTION
3) [A] SELECT foo FROM barA
(A acquires a SHARED lock)
4) [B] SELECT foo FROM barB
(B acquires a SHARED lock)
5) [A] UPDATE barA SET foo=2
(A acquires the RESERVED lock)
6) [B] UPDATE barB SET foo=2
(returns SQLITE_BUSY?)

Would the UPDATE in (6) still return SQLITE_BUSY, and would one of the 
processes still need to rollback for the other to continue?

I guess this is really a question of whether SQLite supports table 
locking or only database locking (and I'm guessing it's the latter, 
meaning the two disjoint tables should be put in different databases so 
they are independently locked).


> Why not simply execute "UPDATE bar SET foo=foo+1;"?

Heh, just looking for a simple example of a multi-query transaction.


Thanks for your help!

-david

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

Re: [sqlite] Confirming locking and transaction characteristics

[Igor Tandetnik]

David Barrett <[EMAIL PROTECTED]> wrote:
> There is a single table (bar) with a single column (foo) with a single
> row containing the integer value "1".  Two processes (A and B) have
> opened the database in READWRITE mode.  They both try to atomically
> increment the value at the same time.  What happens?
>
> 1) [Process A] BEGIN TRANSACTION
> 2) [Process B] BEGIN TRANSACTION
> 3) [A] SELECT foo FROM bar
> (A acquires a SHARED lock)
> 4) [B] SELECT foo FROM bar
> (B acquires a SHARED lock)
> 5) [A] UPDATE bar SET foo=2
> (A acquires the RESERVED lock)
> 6) [B] UPDATE bar SET foo=2
> (? query fails, B's transaction aborted, SHARED lock released?)

SQLite detects deadlock situation. The call trying to execute the update 
statement fails immediately with SQLITE_BUSY error. Neither transaction 
releases its locks: to make progress, one of them has to explicitly roll 
back.

> 7) [A] COMMIT
> (A gets the EXCLUSIVE lock, writes, clears the lock)

Unless B rolled back after getting an error on step 6, A will get the 
same error here.

Why not simply execute "UPDATE bar SET foo=foo+1;"?

Igor Tandetnik 



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

[sqlite] Confirming locking and transaction characteristics

[David Barrett]

Can you please double-check the following to make sure it's accurate?

I've read the "File Locking and Concurrency" page and I'm trying to 
verify how SQLite works in the following scenario:

There is a single table (bar) with a single column (foo) with a single 
row containing the integer value "1".  Two processes (A and B) have 
opened the database in READWRITE mode.  They both try to atomically 
increment the value at the same time.  What happens?

1) [Process A] BEGIN TRANSACTION
2) [Process B] BEGIN TRANSACTION
3) [A] SELECT foo FROM bar
(A acquires a SHARED lock)
4) [B] SELECT foo FROM bar
(B acquires a SHARED lock)
5) [A] UPDATE bar SET foo=2
(A acquires the RESERVED lock)
6) [B] UPDATE bar SET foo=2
(? query fails, B's transaction aborted, SHARED lock released?)
7) [A] COMMIT
(A gets the EXCLUSIVE lock, writes, clears the lock)

Is this right?  In particular, in (6) does B's query fail because there 
is already a RESERVED lock held by A?  Or is there some other system 
that notices B's conflicting transaction and aborts it?

Or do I completely misunderstand this, and do both transactions succeed 
(leaving the value as "2" rather than "3")?

Just trying to sort this all out in my head.  Thanks!

-david

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

Re: [sqlite] SQLite version 3.5.9

[Peter K. Stys]

On Wed, May 14, 2008 at 3:40 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:

>
>
>  WORKAROUNDS:
>
>  Set SQLites page size to be much larger (PRAGMA page_size).
>Makes SQLite deal with bigger chunks of data, reducing the overhead
>percentage.  You'll very likely need to turn this up pretty high
>to see significant changes.
>
>  Set SQLites page cache to be much larger (PRAGMA cache_size)
>Reduces the number of I/O operations.  Great for lookups and sorts.
>Not that useful for writes.  Depends a lot on how you use the DB.
>
>  Live dangerously and turn down/off disk syncing (PRAGMA synchronous).
>Reduces the delay for writes.  Dangerous.
>
>  Or, brute force: Copy the file locally, do your stuff, copy it back.
>


Thank you all for your suggestions and explanations.  I now understand
better the complexity underlying networked volumes.

I tried:

  PRAGMA page_size = SQLITE_MAX_PAGE_SIZE
  PRAGMA cache_size = 100
  PRAGMA synchronous = OFF

(all executed before creating any tables)

There was no improvement in first read/write performance at all.

Looks like the brute force solution is the only answer here.

Cheers,
Peter.



-- 
-
Peter K. Stys, MD
Dept. of Clinical Neurosciences
Hotchkiss Brain Institute
University of Calgary
tel (403) 210-8646
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

[Samuel Neff]

On Tue, May 13, 2008 at 7:51 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> for version 3.6.0 we are considering a behavior change in which a call
> to sqlite3_close() will silently and automatically call
> sqlite3_finalize() on all outstanding prepared statements.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
We would be very much in favor of this change.  While calling
sqlite3_close() without first finalizing all statements is a programmer
error, if this occurs returning SQLITE_BUSY doesn't help much, it seems to
compound the problem.  We actually run into this problem a lot recently
after some new features have exponentially increased the concurrent access
in our application.  the sqlite3_close() error occurs for us as a secondary
error.  Usually we get db locked first, and then fail to finalize the
statement (our bad), but then it's compounded when sqlite3_close() fails.
Unfortunately due to how connection pooling works in SQLite.NET this
sqlite3_close() failure ends up happening on a garbage collector thread
which in MS's infinite wisdom leads to a full application crash (at least it
did initially--we've fixed that since this started).

Interestingly, we're getting these errors in SQLite.NET which Robert said
earlier in this thread already handles finalizing dangling statements.
We're not using the official SQLite.NET distribution, we have some small
modifications, but the code he mentioned is in place in the version we're
using and is untouched.

Thanks,

Sam

-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.5.9

[Jay A. Kreibich]

On Wed, May 14, 2008 at 02:40:28PM -0600, Peter K. Stys scratched on the wall:
> On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> > Performance on a remote volume is about 20x slower because the raw I/O
> > capability of a network disk drive is about about 1/20th of a local
> > disk drive.  There isn't anything much SQLite can do about that.

> I would disagree with this, unless I misunderstand.  File copies (from the
> Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50%
> of theoretical max on our Gbit LAN, whereas reading the records from the
> same file via SQLite is 20-25x slower (?2MB/sec at best, terrible
> performance).  So there is plenty of raw I/O bandwidth across the LAN and
> network drive, but for some reason SQLite access to its remote files is
> extremely slow (to be clear: these are single users accessing single files).

  Bandwidth isn't the issue... it is mostly latency.  Copy operations
  can buffer large amounts of data and spit it across in one big linear
  stream.  The file system driver can easily pipeline file requests,
  keeping the pipe good and full (or nearly so).

  SQLite, on the other hand, is going to pick and choose different
  blocks for reading and writing from all over the data file.  That's
  why you see the 40% drop (vs copies) even on local disks.  The killer
  is that the overhead for a file request is MUCH larger for network
  based systems since it requires a network round-trip and that's aways
  going to be a dozen milliseconds or so, no matter what the bandwidth of
  the network.  Copies can hide a lot of this because they're moving
  larger chunks of data, so the overhead percentage is reduced.  If you
  know what you need next (such as a copy), you can also stagger requests.
  
  SQLite (or any application that accesses a file in a non-linear way)
  can't do that.  SQLite tends to digest fairly small chunks of data
  (related to the page size), keeping the overhead high for network
  systems.  Additionally, SQLite is normally extremely paranoid about
  I/O operations and blocks fully on all file operations, including
  writes.  Operating systems also tend to be a lot more cautious about
  caching file pages in RAM from network volumes vs. local disks, which
  will further cut into your SQLite performance (but make little
  difference to a copy).

  I can go on and on, but it basically boils down to  A) Copies are
  about the worst comparison you can make because they're a near-ideal
  situation.   B) Network transaction overhead is significant and
  SQLite's I/O behavior tends to make the worst of that.


  This is pretty inherent in what SQLite does.  It isn't poor coding,
  it's just the way things work for any system that needs quasi-random
  access to a file in small chunks.

> So I don't understand why the huge performance hit (compared to other
> network file access like Finder copies, not comparing remote vs. local) when
> accessing remote SQLite files?  I could understand some slowdown because of
> extra overhead with network operations, but 20-fold?

  Yes.  Easily.

  I'm sure if you do a bit of testing you'll also find the bandwidth of the
  network doesn't matter much.  If you see 20-fold for GigE, I'd expect
  25-fold (or less) for 100Mb.  The issue is the huge number of
  round-trips, not the speed in which the smallish pages are passed
  back.



  WORKAROUNDS:

  Set SQLites page size to be much larger (PRAGMA page_size).
Makes SQLite deal with bigger chunks of data, reducing the overhead
percentage.  You'll very likely need to turn this up pretty high
to see significant changes.

  Set SQLites page cache to be much larger (PRAGMA cache_size)
Reduces the number of I/O operations.  Great for lookups and sorts.
Not that useful for writes.  Depends a lot on how you use the DB.

  Live dangerously and turn down/off disk syncing (PRAGMA synchronous).
Reduces the delay for writes.  Dangerous.

  Or, brute force: Copy the file locally, do your stuff, copy it back.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.5.9

[Brad House]

> You may see some performance increase by setting pragma page_size to a 
> larger value so that SQLite transfers fewer, but larger, blocks across 
> the network. I would try benchmark tests with page sizes of 8K and 32K 
> to see if there is a substantial difference.

Good point Dennis, though you should probably mention that he would
need to be using at least 3.5.8 and Vacuum after setting the page size
pragma for it to actually take effect on an existing database.

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

Re: [sqlite] SQLite version 3.5.9

[Brad House]

> I would disagree with this, unless I misunderstand.  File copies (from the
> Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50%
> of theoretical max on our Gbit LAN, whereas reading the records from the
> same file via SQLite is 20-25x slower (—2MB/sec at best, terrible
> performance).  So there is plenty of raw I/O bandwidth across the LAN and
> network drive, but for some reason SQLite access to its remote files is
> extremely slow (to be clear: these are single users accessing single files).

Peter, there is a lot more latency over a network than just hitting
a local disk as well, so you've got potentially hundreds of requests
from disk to perform a single select on the database (traversing
the Btree, etc).  Your OS may perform some read-aheads and caching
which would reduce the latency to nearly nothing for the disk access
(on a local machine), but you're having to deal with network latency
and protocol overhead on _each_ of those hundreds of requests
when you're working over a network. Raw sequential throughput you
mentioned really has no relevance here at all.

Like Richard said, use the right tool for the job.  You need a
database that resides on the server and communicates using its
own network protocol.  If you'd like to continue using SQLite
you might check out some of the server/client wrappers out there:
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

You've got to realize that no other (non-server based) database would
be able to perform better in this situation.

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

Re: [sqlite] SQLite version 3.5.9

[Dennis Cote]

Peter K. Stys wrote:
> 
> I would disagree with this, unless I misunderstand.  File copies (from the
> Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50%
> of theoretical max on our Gbit LAN, whereas reading the records from the
> same file via SQLite is 20-25x slower (≈2MB/sec at best, terrible
> performance).  So there is plenty of raw I/O bandwidth across the LAN and
> network drive, but for some reason SQLite access to its remote files is
> extremely slow (to be clear: these are single users accessing single files).
> 
> In contrast SQLite R/W to local files runs at about 60% of raw binary file
> access on a local volume, very acceptable (≈35MB/s vs. 60-70MB/sec).
> 
> So I don't understand why the huge performance hit (compared to other
> network file access like Finder copies, not comparing remote vs. local) when
> accessing remote SQLite files?  I could understand some slowdown because of
> extra overhead with network operations, but 20-fold?
> 

Random access I/O to a file using 1K pages is very different than 
sequential reading or writing used to do a file copy. There is *much* 
more overhead involved. What SQLite is doing is more like copying a 
directory with thousands of small files, rather than copying a single 
large file.

You may see some performance increase by setting pragma page_size to a 
larger value so that SQLite transfers fewer, but larger, blocks across 
the network. I would try benchmark tests with page sizes of 8K and 32K 
to see if there is a substantial difference.

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

Re: [sqlite] SQLite version 3.5.9

[Peter K. Stys]

On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On May 14, 2008, at 3:58 PM, Peter K. Stys wrote:
>
> > I'm curious, when you say performance enhancements, does that include
> > improved R/W performance to/from remote volumes on OS X, which
> > presently
> > suffer serious (20-fold) speed issues.  I had a recent post on this.
> > If not, will 3.6.0 address this?
> >
>
> Performance on a remote volume is about 20x slower because the raw I/O
> capability of a network disk drive is about about 1/20th of a local
> disk drive.  There isn't anything much SQLite can do about that.
>

Dear Richard,

I would disagree with this, unless I misunderstand.  File copies (from the
Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50%
of theoretical max on our Gbit LAN, whereas reading the records from the
same file via SQLite is 20-25x slower (≈2MB/sec at best, terrible
performance).  So there is plenty of raw I/O bandwidth across the LAN and
network drive, but for some reason SQLite access to its remote files is
extremely slow (to be clear: these are single users accessing single files).

In contrast SQLite R/W to local files runs at about 60% of raw binary file
access on a local volume, very acceptable (≈35MB/s vs. 60-70MB/sec).

So I don't understand why the huge performance hit (compared to other
network file access like Finder copies, not comparing remote vs. local) when
accessing remote SQLite files?  I could understand some slowdown because of
extra overhead with network operations, but 20-fold?

Peter.


-- 
-
Peter K. Stys, MD
Dept. of Clinical Neurosciences
Hotchkiss Brain Institute
University of Calgary
tel (403) 210-8646
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.5.9

[D. Richard Hipp]

On May 14, 2008, at 3:58 PM, Peter K. Stys wrote:

> I'm curious, when you say performance enhancements, does that include
> improved R/W performance to/from remote volumes on OS X, which  
> presently
> suffer serious (20-fold) speed issues.  I had a recent post on this.
> If not, will 3.6.0 address this?
>

Performance on a remote volume is about 20x slower because the raw I/O  
capability of a network disk drive is about about 1/20th of a local  
disk drive.  There isn't anything much SQLite can do about that.

If you need to access a database that physically resides on a remote  
machine, you should probably use a client/server database engine with  
the server located on the same machine where the data lives.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLite version 3.5.9

[D. Richard Hipp]

On May 14, 2008, at 2:14 PM, Petite Abeille wrote:

>
> On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote:
>
>> Works for me.  Did you compile the shell yourself or use the prebuilt
>> binary?
>
> I did compile it myself. Any additional configuration(s) one should
> take care of to enable this pragma?
>

No.  It should just work.  As I said, I can't get it to fail.

What is the result of the pragma statement - what does it return.  It  
should return the new journal mode:

 $ sqlite3 test.db
 sqlite3> PRAGMA journal_mode=OFF;
off

If you did not see the "off" return, then perhaps you mistyped the  
pragma name.  Unrecognized pragmas are silently ignored (a feature,  
not a bug).


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLite version 3.5.9

[Petite Abeille]

On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote:

>
> Works for me.  Did you compile the shell yourself or use the prebuilt
> binary?

Ooops... never mind... the shell works fine... I was using  
sqlite3_prepare and my application was linked against a different  
version of the lib...

Everything works as advertise :)

Thanks for the release!

Cheers,

PA.

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

Re: [sqlite] SQLite version 3.5.9

[Petite Abeille]

On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote:

> Works for me.  Did you compile the shell yourself or use the prebuilt
> binary?

I did compile it myself. Any additional configuration(s) one should  
take care of to enable this pragma?

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

Re: [sqlite] SQLite version 3.5.9

[D. Richard Hipp]

On May 14, 2008, at 1:59 PM, Petite Abeille wrote:
> Hello,
>
> On May 14, 2008, at 7:17 PM, D. Richard Hipp wrote:
>
>> There is also a new *experimental* PRAGMA called  "journal_mode"
>> which can provide performance improvements under some  circumstances.
>
> I'm trying the new journal_mode pragma:
>
> % uname -v
> Darwin Kernel Version 9.2.2; root:xnu-1228.4.31~1/RELEASE_I386
>
> % sqlite3 -version
> 3.5.9
>
> pragma journal_mode = off
>
> But this doesn't seem to have the expected effect as a '-journal' file
> is still created.
>

Works for me.  Did you compile the shell yourself or use the prebuilt  
binary?


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLite version 3.5.9

[Petite Abeille]

Hello,

On May 14, 2008, at 7:17 PM, D. Richard Hipp wrote:

> There is also a new *experimental* PRAGMA called  "journal_mode"  
> which can provide performance improvements under some  circumstances.

I'm trying the new journal_mode pragma:

% uname -v
Darwin Kernel Version 9.2.2; root:xnu-1228.4.31~1/RELEASE_I386

% sqlite3 -version
3.5.9

pragma journal_mode = off

But this doesn't seem to have the expected effect as a '-journal' file  
is still created.

Did I misunderstood what 'journal_mode = off' is meant to do, e.g. not  
creating a journal file in the first place?

Thanks in advance.

Kind regards,

--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite version 3.5.9

[D. Richard Hipp]

SQLite version 3.5.9 is now available on the SQLite website

 http://www.sqlite.org/
 http://www.sqlite.org/download.html

This release features some minor bug fixes and performance  
enhancements.  There is also a new *experimental* PRAGMA called  
"journal_mode" which can provide performance improvements under some  
circumstances.  Additional information about these and other changes  
is available on the website.

We anticipate that version 3.5.9 will be the last version in the 3.5  
series.  Our plan is for the next release to be version 3.6.0 which  
incorporates changes in the VFS layer used to tie SQLite into the  
underlying operating system.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Is this Sorting order right?

[Igor Tandetnik]

Mahalakshmi.m
<[EMAIL PROTECTED]>
wrote:
>  unsigned short ArtistName;
> sqlite3_bind_text16(insert,1,ArtistName,-1,SQLITE_STATIC);

Doesn't compile. The third parameter of sqlite3_bind_text16 is a void*, 
and you are passing unsigned short there. You might have meant 
, except that the length is wrong then: you do not have a 
NUL-terminated buffer.

Also, I don't see you actually initialize ArtistName anywhere. Did you 
actually mean to put some random garbage value into the database?

Also, you are only inserting a single row. How do you plan to 
demonstrate incorrect sorting order with just one row?

> unsigned char * Name = sqlite3_column_text16(select, 0 );

sqlite3_column_text16 gives you a pointer to a Unicode (wide) string, 
not a char* (narrow) string.

> For this I got wrong output..

Not at all surprising.
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925



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

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

[Robert Simpson]

Posted too soon ... change sqlite3_closeAndFreeMutex() to just
sqlite3_close();

My version of sqlite3_finalize() contains this small addition:
if (p->magic == VDBE_MAGIC_DEAD && p->db == NULL)
  {
sqlite3_free(p);
return SQLITE_OK;
  }

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson
Sent: Wednesday, May 14, 2008 7:58 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

I wrote this behavior into the SQLite.NET provider as well.  However, I make
a temp copy of the open statements in the db->Vdbe and finalize the copies
so that subsequent calls to sqlite3_finalize() on the original pointer will
not fail, but be a no op.

  int ret = sqlite3_close (db);

  if (ret == SQLITE_BUSY && db->pVdbe)
  {
while (db->pVdbe)
{
  // Make a copy of the first prepared statement
  Vdbe *p = (Vdbe *)sqlite3_malloc(sizeof(Vdbe));
  Vdbe *po = db->pVdbe;

  if (!p) return SQLITE_NOMEM;

  CopyMemory(p, po, sizeof(Vdbe));

  // Put it on the chain so we can free it
  db->pVdbe = p;
  ret = sqlite3_finalize((sqlite3_stmt *)p); // This will also free the
copy's memory
  if (ret)
  {
// finalize failed -- so we must put back anything we munged
CopyMemory(po, p, sizeof(Vdbe));
db->pVdbe = po;
break;
  }
  else
  {
ZeroMemory(po, sizeof(Vdbe));
po->magic = VDBE_MAGIC_DEAD;
  }
}
ret = sqlite3_closeAndFreeMutex(db);
  }

  return ret;


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Tuesday, May 13, 2008 7:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Virgilio Alexandre Fornazin wrote:
> A good and new safe could be a sqlite3_close_v2() call prototyped like
> 
> int sqlite3_close_v2(sqlite3 * db, int closePendingStatements);

Funnily enough that is exactly what I provide in my Python wrapper for
SQLite!  Reference counting ensures that the sqlite3_db can't be freed
before all the statements are closed but the above method is a quick way
for the developer to proactively close everything.

Having a close_v2 is most likely the best solution since there won't be
semantics changes over what people have already developed.  It can also
ensure the right thing happens when threads are being abused.

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

iD8DBQFIKlSymOOfHg372QQRAiEbAJ9mbFpensXkTXmJtI90vPfTqMNpswCgmh2/
2HwZvkW8FdDUzWId2mtE5fs=
=oawA
-END PGP SIGNATURE-
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

[Robert Simpson]

I wrote this behavior into the SQLite.NET provider as well.  However, I make
a temp copy of the open statements in the db->Vdbe and finalize the copies
so that subsequent calls to sqlite3_finalize() on the original pointer will
not fail, but be a no op.

  int ret = sqlite3_close (db);

  if (ret == SQLITE_BUSY && db->pVdbe)
  {
while (db->pVdbe)
{
  // Make a copy of the first prepared statement
  Vdbe *p = (Vdbe *)sqlite3_malloc(sizeof(Vdbe));
  Vdbe *po = db->pVdbe;

  if (!p) return SQLITE_NOMEM;

  CopyMemory(p, po, sizeof(Vdbe));

  // Put it on the chain so we can free it
  db->pVdbe = p;
  ret = sqlite3_finalize((sqlite3_stmt *)p); // This will also free the
copy's memory
  if (ret)
  {
// finalize failed -- so we must put back anything we munged
CopyMemory(po, p, sizeof(Vdbe));
db->pVdbe = po;
break;
  }
  else
  {
ZeroMemory(po, sizeof(Vdbe));
po->magic = VDBE_MAGIC_DEAD;
  }
}
ret = sqlite3_closeAndFreeMutex(db);
  }

  return ret;


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Tuesday, May 13, 2008 7:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Virgilio Alexandre Fornazin wrote:
> A good and new safe could be a sqlite3_close_v2() call prototyped like
> 
> int sqlite3_close_v2(sqlite3 * db, int closePendingStatements);

Funnily enough that is exactly what I provide in my Python wrapper for
SQLite!  Reference counting ensures that the sqlite3_db can't be freed
before all the statements are closed but the above method is a quick way
for the developer to proactively close everything.

Having a close_v2 is most likely the best solution since there won't be
semantics changes over what people have already developed.  It can also
ensure the right thing happens when threads are being abused.

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

iD8DBQFIKlSymOOfHg372QQRAiEbAJ9mbFpensXkTXmJtI90vPfTqMNpswCgmh2/
2HwZvkW8FdDUzWId2mtE5fs=
=oawA
-END PGP SIGNATURE-
___
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] Is this Sorting order right?

[Mahalakshmi.m]

Igor Tandetnik wrote:

>I'm not sure I understand. What exactly are you doing differently in these
>two cases? Can you quote the exact code that fails, in full?

#include 
#include 

int main() 
{
  sqlite3* db;
  sqlite3_stmt* insert; 
 unsigned short ArtistName;
  sqlite3_open(" mysqlite.db ", );

  sqlite3_exec(db, "pragma encoding = UTF16", 0, 0, 0);
  sqlite3_exec(db, "create table ARTIST(id  integer primary key not null ,
ArtistName  test not null collate nocase )", 0, 0, 0);
 
 sqlite3_prepare(db,"INSERT INTO ARTIST (ArtistName)
VALUES(?);",-1,,0);
 sqlite3_bind_text16(insert,1,ArtistName,-1,SQLITE_STATIC);
 sqlite3_step(insert);
 sqlite3_finalize(insert);

  sqlite3_stmt* select;
  sqlite3_prepare(db, " select  ArtistName  from ARTIST  order by
ArtistName  ", -1, , 0);
  while (sqlite3_step(select) == SQLITE_ROW) 
 {
    unsigned char *  Name = sqlite3_column_text16(select,  0 );
    printf("text= %s  \n", Name );
  }
  sqlite3_finalize(select);
  sqlite3_close(db);
  return 0;
}

For this I got wrong output..

Thanks & Regards,
Mahalakshmi


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

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

[Ken]

I concur with bob's idea to get a list of open statements. But also like the 
idea the sqlite3_close_v2 interface that give programmers the option.

Ken


Bob Ebert <[EMAIL PROTECTED]> wrote: It seems like a programmer error if a 
statement remains unfinalized when
the DB is closed.  It would be great if something about the API would
help programmers detect and fix errors like this.  Having sqlite3_close
return an error helped a little, because it made you aware there was a
problem, but it didn't help much in finding the problem.

I suspect that if you make sqlite3_close automatically finalize all
statements it'll cause more problems for programmers that might still be
hanging on to those unfinalized statements.

Could you do something in between, where sqlite3_close does close the
file and puts all the statements in some kind of "error" state, but
which doesn't free all the memory for the unfinalized statements?  Then
you could safely and reliably report MISUSE later if the statement was
accessed.

It would also be a nice touch if sqlite3_close could report on the
unfinalized statements, even just getting the SQL text used to create
the statement would be a huge help in tracking down these leaks.  Or add
an additional API to report on all unfinalized statements that could be
called when this sort of error occurs?

  --Bob

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Tuesday, May 13, 2008 5:22 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.


On May 13, 2008, at 8:02 PM, Scott Hess wrote:

> On Tue, May 13, 2008 at 4:51 PM, D. Richard Hipp 
> wrote:
>> The currently documented behavior of sqlite3_close() is that when it 
>> called on a database connection that has unfinalized prepared 
>> statements is to return SQLITE_BUSY and fail to close the connection.
>> The rational is that we did not want a call to sqlite3_close() to 
>> destroy sqlite3_stmt* pointers out from under other subsystems. But 
>> for version 3.6.0 we are considering a behavior change in which a 
>> call to sqlite3_close() will silently and automatically call
>> sqlite3_finalize() on all outstanding prepared statements.
>>
>> This is, technically, an incompatible change and we strive to avoid 
>> incompatible changes. But we think it unlikely that this change will 
>> cause any problems, and in fact we suspect it will likely fix more 
>> bugs than it will induce. But before we move forward, it seems good 
>> to submit the idea to the community of SQLite users and programmers.
>>
>> Does anybody have any thoughts on this proposed behavior changes for 
>> the sqlite3_close() interface?
>
> What will happen if you call things like this:
>
> db = sqlite3_open(...);
> sqlite3_prepare_v2(db, zSql, -1, , NULL); // ...
> sqlite3_close(db);
> sqlite3_finalize(stmt);
>
> ??? If sqlite3_finalize() will succeed, then this seems pretty 
> reasonable to me. If sqlite3_finalize() will fail with a segfault or 
> somesuch, that may be dangerous.

The current behavior in the above is that sqlite3_close() will return
SQLITE_BUSY and then the application will leak memory and file
descriptors because the database connection is never closed.

The proposed new behavior is that the stmt object would be destroyed
(and its memory passed to free()) by the sqlite3_close() call.  We could
arrange for sqlite3_finalize() to attempt to return SQLITE_MISUSE, but
if memory were reused in between the close and the finalize, there is a
small but non-zero chance that the misuse could go undetected and
segfault could result.  Or if this were on android and memory used by
the stmt object gets unmapped, you might segfault with high probability.

>
> My rational is that if someone is building a system which is 
> abstracting SQLite, then it may be that the database handles and the 
> statement handles will not have clear clean-up ordering (for instance 
> in a garbage- collected system). One _could_ go ahead and create such 
> an ordering by using refcounting or whatever is appropriate in your 
> system, but it would also seem reasonable for SQLite to manage this.
>
> This may also come up with multi-threaded systems, where it could be 
> challenging to convince another thread to finalize statements before 
> calling close.
>
> I don't suggest that you should be able to do anything interesting 
> with the orphaned statement handle at this point, other than 
> finalizing it. I suppose it would be nice if all statement-handle 
> functions would start throwing SQLITE_MISUSE or other appropriate 
> error code.
>

Perhaps a better approach would be to modify sqlite3_close() to return
SQLITE_MISUSE if called with unfinalized prepared statements and also
fail and assert() in that case.  That way, applications would crash
during development so that the bugs would be found more quickly, but
after delivery (when assert() is usually turned off) would 

Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation

[Dennis Cote]

[EMAIL PROTECTED] wrote:
> When I execute sql statement "delete from Carimages where OpTime <'2008-05-01 
> 00:00:00'"  in my database, sqlite3 crashed.
> The Exception Information reported by XP is:
> Code: 0xc005   Flags:0x  
> Record: 0x   Address: 0x00406652
> 
> The sqlite3.exe is downloaded from http://www.sqlite.org/sqlite-3_5_8.zip
> The database file is to big ( about 600M, after compressed by WinRAR, the 
> size is 18M), so I can't upload here.

Please don't hijack other threads. 
http://en.wikipedia.org/wiki/Thread_hijacking

What is the schema of Carimages table? Can you provide a couple of 
sample records instead of the entire database?

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

Re: [sqlite] nVARCHAR as unique index

[Igor Tandetnik]

"Farzana" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> The query we used is "SELECT * FROM Brand ORDER BY BrandDescription".
> The Brand table contains more than 30,000 records. The execution time
> is reasonable when we execute in the system.But it is slower when we
> execute the same in the device.

And do you have reasons to expect the query to run faster on the device? 
Does the device have a faster CPU, or faster I/O, than your development 
system? Because otherwise I don't quite see why you would be surprised 
by this outcome.

Double-check that you have actually created the index in the copy of the 
database that's sitting on the device.

Igor Tandetnik 



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

Re: [sqlite] nVARCHAR as unique index

[Farzana]

The query we used is "SELECT * FROM Brand ORDER BY BrandDescription". The
Brand table contains more than 30,000 records. The execution time is
reasonable when we execute in the system.But it is slower when we execute
the same in the device.

Regards,
Farzana.



Igor Tandetnik wrote:
> 
> "Farzana" <[EMAIL PROTECTED]>
> wrote in message news:[EMAIL PROTECTED]
>> We have already created such index but we have to use ORDER BY in the
>> query explicitly to sort the data by BrandDescription.
> 
> Of course. Why do you believe this is a problem? How else do you expect 
> SQLite to know you want results sorted by this column? SQLite will see 
> there's a suitable index, and will use it to speed up ORDER BY clause.
> 
> If the query is still slower than you hoped, post the statement here, 
> and also post the output of this statement:
> 
> explain query plan select ;
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/nVARCHAR-as-unique-index-tp17206197p17229808.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] nVARCHAR as unique index

[Filip Navara]

CREATE INDEX BrandKey2 ON Brand(BrandDescription);

On Wed, May 14, 2008 at 7:32 AM, Farzana <[EMAIL PROTECTED]> wrote:
>
>  Thanks for your reply Igor.
>
>  When we checked with the provided query we found that the duplicate values
>  are present in the BrandDescription. We are working with the device where
>  the memory is limited. We are suppose to sort the data by
>  BrandDescription.When we tried with "ORDER BY BrandDescription" in the query
>  to be executed, its taking more time than we expected.
>
>  Is there any work around to sort the data by BrandDescription physically?
>  Thanks in advance.
>
>  Regards,
>  Farzana.
>
>
>
>
>  Igor Tandetnik wrote:
>  >
>  > "Farzana" <[EMAIL PROTECTED]>
>  > wrote in message news:[EMAIL PROTECTED]
>  >> We are using SQLite and we have the table structure as CREATE TABLE
>  >> Brand(PcNo numeric(4) Not Null,SubPcNo numeric(4) Not Null,BrandNo
>  >> numeric(9) Not Null,BrandDescription nVARCHAR(254)Not Null,ST
>  >> numeric(1),TS numeric(14)) where the index is CREATE UNIQUE index
>  >> BrandKey1 on Brand(PcNo,SubPcNo,BrandNo). When we use this table in
>  >> the application we are suppose to sort the datas by
>  >> BrandDescription.So we tried to create a unique index as CREATE
>  >> UNIQUE INDEX BrandKey2 on Brand(BrandDescription).But we couldn't
>  >> able to create the unique index.
>  >
>  > Define "couldn't able". What exactly seems to be the problem? Did you
>  > get an error message? What did the message say?
>  >
>  > Is it possible that the values in BrandDescription column are not in
>  > fact unique? If they are not, then naturally you would get an error
>  > trying to create a unique index. Try this statement:
>  >
>  > select * from Brand
>  > group by BrandDescription
>  > having count(*) > 1;
>  >
>  > If this returns any rows, you have duplicates.
>  >
>  > Igor Tandetnik
>  >
>  >
>  >
>  > ___
>  > sqlite-users mailing list
>  > sqlite-users@sqlite.org
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>  >
>
>  --
>  View this message in context: 
> http://www.nabble.com/nVARCHAR-as-unique-index-tp17206197p17223665.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users