Re: [sqlite] sqlite3_last_insert_rowid fails on ARM microprocessor

[Wilfred]

Nothing wrong with SQLite.
The compiler is unable to convert a long long to a double.

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

Re: [sqlite] Meaning of the following code

[drh]

"Rich Rattanni" <[EMAIL PROTECTED]> wrote:
> All:
> I am able to consistently cause the following message during a integrity check
> 
> Page xxx is never used
> 
> This seems non-critical, since a vacuum clears this up.  If someone
> has the time could you explain the meaning (besides the obvious),
> causes, and dangers of receiving this message during a integrity
> check?
> 

It is not critical.

This can happen if another process appends something to the
end of your database.  It can also happen if the "ftruncate()"
does not work on your 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] SQLITE_CONSTRAINT error after sqlite3_step

[Dennis Cote]

Vincent Vega wrote:
> Thanks a lot . Your explantion does make sence.
> I'll remove the UNIQUE index and see if it helps.
> 

You probably shouldn't remove the index, just remove the unique keyword 
from the create index statement.

create index InventoryIndex on Inventory (Tag);

If you have an existing database you can drop the old index and create 
the new index without losing any data.

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

[sqlite] Meaning of the following code

[Rich Rattanni]

All:
I am able to consistently cause the following message during a integrity check

Page xxx is never used

This seems non-critical, since a vacuum clears this up.  If someone
has the time could you explain the meaning (besides the obvious),
causes, and dangers of receiving this message during a integrity
check?

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

Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

[Dennis Cote]

Bharath Booshan L wrote:
> 
> Table File:
> -
> FileID  INTEGER PRIMARY KEY Auto Incrment
> FullPathTEXT
> FileNameTEXT
> CreationDateREAL
> ModifiedDateREAL
> 
> 
> Table Segments:
> ---
> FileID  INTEGER REFERENCES File(FileID)
> SegmentID   TEXT PRIMARY KEY
> SegmentName TEXT
> Description TEXT
> 
> Table Metadata:
> --
> FileID  INTEGER REFERENCES File(FileID)
> NameTEXT
> MetaDataID  INTEGER PRIMARY KEY
> 
> Table SegmentMetadata
> ---
> SegmetnID   TEXT REFERENCES Segments(SegmentID)
> MetadaIDINTEGER REFERENCES Metada(MetaDataID)
> 
> 
> Process A: (accepts FilePath as input)
> --
> a) Check If File exists in table - "SELECT * from File WHERE FullPath =
>
> b) BEGIN IMMEDIATE TRANSACTION
> c) if exists: UPDATE table File  SET  CreationDate =,
> ModificationDate = 
> Info Values)

It seems strange to change the creation date for an update. I would 
think this should only happen when the record is created in step d.

What do you mean by the line that says "Info Values)"?

Where do the  and  come from?

> UPDATE table Segments SET  SegmentName = , Desription =
>  WHERE FileID = 
> 

Where do the  and  come from?

> d) If FilePath doesn't exists then create a new entry  File Table, Segments
> Segments table, Metadata table and in SegmentMetada, in order
> 

Where does all the data to create the new records come from? You say 
Process A only has a FilePath as input.

> E) COMMIT
> ---
> 
> Process B:
> -
> a) Query used to Read Sements in File, say File A:
> 

So, a File has one or more segments.

> "SELECT * FROM Segments,File WHERE File.FileID =  Segments.FileID AND
> File.FullPath = ;
> 

This will return the primary key for each segment associated with the 
file. With that you can get any other info about the segment that may be 
needed.

select SegmentID
from File join Segments using(FileID)
where FullPath = ;

> b)Invoke Process A for each set of files

Where is this "set of files" coming from? The query in step a returns 
results for a single file only. Process A takes a FilePath parameter, 
the same as used in the where clause in query above I would assume.

> 
> C) Repeat Step a)
> 

This is where you say you are having problems, correct? Are you saying 
that you are not getting the same segments for the file FileA after 
running process A with a parameter value of FileA?

This is unexpected since you say Process A only updates existing rows if 
the file FileA exists. It only adds new rows if FileA doesn't exist. But 
if FileA doesn't exist when you run Process A then there would have been 
nothing to return from the query in step a above, so any new rows would 
be expected. If the file exists, then you update some column values, but 
not the values that are used to select the results in the query in step 
a above, so the results should be the same.

Is there more stuff happening elsewhere that you haven't described? Are 
the file and/or segment rows ever deleted?

> 
> Is that BEGIN IMMEDIATE TRANSACTION causing problem? It says, it acquires
> RESERVED lock, but it allows other process to read.

I don't think that is your problem. The reserved lock simply lets other 
processes read until the holder is ready to start making changes. The 
reserved lock will change to an exclusive lock when process A starts its 
  first update command. It is intended to let a process read with the 
assurance that no other process can write to the database.

It would typically be used to move your step a in process A to a point 
between step b and step c like this.

a) BEGIN IMMEDIATE TRANSACTION
b) Check If File exists in table - "SELECT * from File WHERE FullPath = 

c) if exists: UPDATE table File  SET  CreationDate =,

Process A knows it will write later, so the reserved lock at step a lets 
others continue to read until it gets to step c or step d. The advantage 
of this is that it prevents some other process form executing a delete 
between your steps a and c for example. If the file existed when your 
process A did its existence check as step a, but didn't exist when it 
got to step c, there would be problems (not the problems you are seeing 
though). It is probably a good idea to acquire the lock (i.e. start the 
immediate transaction) before you start reading the database though.

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

Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step

[Vincent Vega]

Dennis ,
Thanks a lot . Your explantion does make sence.
I'll remove the UNIQUE index and see if it helps.

Vincent.


- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Monday, March 17, 2008 4:27:11 PM
Subject: Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step

Vincent Vega wrote:
> Anyhow, as I said before the code works fine most of the times(I can
> insert 500 records to the table and read them correctly) but from time
> to time I get SQLITE_CONSTRAINT error code after the sqlite3_step.
> Any Idea why?
> 
> Here are the table defenitions :
> CREATE TABLE 'Inventory' (
> 'TagIndex' integer PRIMARY KEY,
> 'Tag' varchar(12) NOT NULL,
> ) ;
> 
> CREATE UNIQUE INDEX InventoryIndex ON Inventory (Tag);
> 

Vincent,

You are almost certainly inserting the same value a second time into the 
Tag column. You have added a unique index to this column which will 
raise a constraint error if you try to insert a row that has the same 
value in that column as some other row.

If you don't really require that the Tag column data be unique for each 
row, then change your index to o normal index without the unique 
constraint.

HTH
Dennis Cote


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


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

[drh]

MarcoN <[EMAIL PROTECTED]> wrote:
> Hello, everybody.
> 
> I have the following problem: I have an old project that uses a database
> created with an older SQLite library version.
> Now, since I updated SQLite to 3.5.5, I can't use the database anymore,
> because any query on the database tables returns:
> 
> SQLite error 11 - Malformed database schema - near ")": syntax error
> 
> The strange point is that I downloaded "SQLite Database Browser" from
> sourceforge, and I can actually open the database with this tool. In the
> help, it is saying that "SQLite Database Browser" is using version 3.3.5 of
> the database engine. With this tool, I can browse the data in any table, and
> a "pragma integrity_check" returns "ok".
> 
> Any idea / help on this would be greatly appreciated.

Please send the following output:

   SELECT sql FROM sqlite_master;

Please do so quickly.  We are scheduled to release 3.5.7 in about
30 minutes.  If this is a bug in 3.5.x, we'd like to identify it
before then.

--
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] Database Table corrupt in SQLite v 3.4.0

[Ken]

Do you test for SQLITE_BUSY, when you perform BEGIN IMMEDIATE ?   

Yes a process that is reading will continue to read. Once it completes if 
anothre process is waiting to write then the additional read locks will not be 
granted. This is to prevent writer starvation.

How do you know that the data for which you are querying is actually loaded? I 
don't think you can since you invoke a load process after the query.

HTH.

Bharath Booshan L <[EMAIL PROTECTED]> wrote: > You will have to provide more 
detail about what you are doing to change
> the database. Since you say process A is the only one that write to the
> database, you would probably be best to post the actual code, or at
> least the actual SQL statements, that you are using in that process

Ok. Here it is.


Table File:
-
FileID  INTEGER PRIMARY KEY Auto Incrment
FullPathTEXT
FileNameTEXT
CreationDateREAL
ModifiedDateREAL


Table Segments:
---
FileID  INTEGER REFERENCES File(FileID)
SegmentID   TEXT PRIMARY KEY
SegmentName TEXT
Description TEXT

Table Metadata:
--
FileID  INTEGER REFERENCES File(FileID)
NameTEXT
MetaDataID  INTEGER PRIMARY KEY

Table SegmentMetadata
---
SegmetnID   TEXT REFERENCES Segments(SegmentID)
MetadaIDINTEGER REFERENCES Metada(MetaDataID)


Process A: (accepts FilePath as input)
--
a) Check If File exists in table - "SELECT * from File WHERE FullPath =
   
b) BEGIN IMMEDIATE TRANSACTION
c) if exists: UPDATE table File  SET  CreationDate =,
ModificationDate = 
Info Values)
UPDATE table Segments SET  SegmentName = , Desription =
 WHERE FileID = 

d) If FilePath doesn't exists then create a new entry  File Table, Segments
Segments table, Metadata table and in SegmentMetada, in order

E) COMMIT
---

Process B:
-
a) Query used to Read Sements in File, say File A:

"SELECT * FROM Segments,File WHERE File.FileID =  Segments.FileID AND
File.FullPath = ;

b)Invoke Process A for each set of files

C) Repeat Step a)


Hope I am clear.

Is that BEGIN IMMEDIATE TRANSACTION causing problem? It says, it acquires
RESERVED lock, but it allows other process to read.

--
Bharath

On 3/17/08 8:34 PM, "Dennis Cote"  wrote:

> Bharath Booshan L wrote:
>> 
>> I will give overview of what is happening
>> 
>> App A - Writer process
>> ---
>> * Open SQLIte Connection
>> * BEGIN IMMEDIATE TRASACTION
>> * Insert/Update some 1000 rows in Table A,B,C
>> * COMMIT 
>> * Close SQLite connection
>> ---
>> 
>> 
>> App B - Reader process
>> 
>> A) Open SQLite Connection
>> 
>> B) Read rows fro Table A,B,C based on some constraint -( Returned Results
>> are
>>   as expected)
>> ...
>> 
>> C) Initiate App A to write some 10-20 times information (This step is
>> executed several times, however it is ensured that all these write
>> operations are serialized)
>> 
>> D) Read rows fro Table A,B,C based on some constraint -( Returned Results
>> are not as expected. )
>> 
>> E) Close SQLite connection
>> -
>> 
>> The actual problem I am facing is at the last step in App B, where I expect
>> that all the information updated/inserted to be returned from query, instead
>> some of the rows in table A,B,C are lost forever.
> 
> Based on what you have said here there should be no problems.
> 
> You will have to provide more detail about what you are doing to change
> the database. Since you say process A is the only one that write to the
> database, you would probably be best to post the actual code, or at
> least the actual SQL statements, that you are using in that process.
> 
>> (just to know what is left in database, I tried to open the database in some
>> document application, for example TextEdit, and the row information which
>> looked as erased are available, and in between all these the database size
>> has not changed )
>> 
> 
> That is a very unreliable way to look an SQLite database. SQLite doesn't
> necessarily remove data that has been deleted, it may remain in unused
> pages and/or records in the database file. SQLite doesn't change the
> size of the file when rows are deleted or updated, any pages that are no
> longer used are added to an internal free page list.
> 
> You should use the command line sqlite3 program to view the database file.
> 
>> Overall I guess I am using some wrong version which I need to upgrade to new
>> version, but have to re-consider if it requires major change.
> 
> There should be no need to use a different version.
> 
> 
> 

Re: [sqlite] Complex SELECT Syntax

[Kees Nuyt]

On Fri, 14 Mar 2008 20:10:36 -0700 (PDT), you wrote:

>In testing a my code I came across this example.
>Could someone help me understand what this syntax is doing please
>(from the Seinfeld demo database examples)

I assume this is syntax for the command line tool:

>..m col
>..h on
>..w 20 17 6 23 6
>..e on

I'm sure it is explained in a previous chapter, but
you can always type  .help  in the command line tool.
It recognizes commands as soon as it the first 1..N
characters are unique.

HTH
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

[John Stanton]

How are you synchronizing your updates and reads?  It looks like you may 
be missing a COMMIT.

Bharath Booshan L wrote:
> Ken,
>  
> Thanks for reply,
> 
> 
>>I would start with APP A to determine after processing that the data that you
>>asked to be loaded is actually loaded. If it does not, then look into app A.
>>
> 
> I think I need to add some more information here.
> 
> App B will be in running state, and whenever a write operation needs to be
> performed, it invokes App B.
> 
> 
> 
>>If app A data load succeeds (and is correct in that all data loaded).
>>Run app B. If this errors out you more than likely have a query related
>>problem.
> 
> 
> Query is absolutely fine. A simple select on the table after the database
> table corrupt does not show some rows, which is expected to be there.
> 
> I will post the simple schema and the actual SQL statements I am using so
> that might be helpful.
> --
> 
> Bharath
> 
> 
> On 3/17/08 8:16 PM, "Ken" <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> 
>>HTH,
>>Ken
> 
> 
> 
> 
> ---
> Robosoft Technologies - Come home to Technology
> 
> Disclaimer: This email may contain confidential material. If you were not an 
> intended recipient, please notify the sender and delete all copies. Emails to 
> and from our network may be logged and monitored. This email and its 
> attachments are scanned for virus by our scanners and are believed to be 
> safe. However, no warranty is given that this email is free of malicious 
> content or virus.
> 
> 
> ___
> 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] Malformed database schema with SQLite version > 3.5.x

[MarcoN]

Hello, everybody.

I have the following problem: I have an old project that uses a database
created with an older SQLite library version.
Now, since I updated SQLite to 3.5.5, I can't use the database anymore,
because any query on the database tables returns:

SQLite error 11 - Malformed database schema - near ")": syntax error

The strange point is that I downloaded "SQLite Database Browser" from
sourceforge, and I can actually open the database with this tool. In the
help, it is saying that "SQLite Database Browser" is using version 3.3.5 of
the database engine. With this tool, I can browse the data in any table, and
a "pragma integrity_check" returns "ok".

Any idea / help on this would be greatly appreciated.
Thanks and kind regards
Marco.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Complex SELECT Syntax

[Glenn]

Dennis Cote wrote:
> Derek Developer wrote:
>> Could someone help me understand what this syntax is doing please
>> (from the Seinfeld demo database examples)
>>
> 
> What Seinfeld demo database examples?
> 

The book "The Definitive Guide to SQLite" by Michael Owens (ISBN 
1-59059-673-0) examples use a database with Seinfeld episode data. 
Thats probably what he's referring to.

-- 
Glenn McAllister <[EMAIL PROTECTED]>  +1 416 348 1594
SOMA Networks, Inc.  http://www.somanetworks.com/  +1 416 977 1414

   Asking a writer what he thinks about criticism is like asking a
   lamppost what it feels about dogs.
 - John Osborne

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

Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

[Bharath Booshan L]

Ken,
 
Thanks for reply,

> I would start with APP A to determine after processing that the data that you
> asked to be loaded is actually loaded. If it does not, then look into app A.
> 
I think I need to add some more information here.

App B will be in running state, and whenever a write operation needs to be
performed, it invokes App B.


> If app A data load succeeds (and is correct in that all data loaded).
> Run app B. If this errors out you more than likely have a query related
> problem.

Query is absolutely fine. A simple select on the table after the database
table corrupt does not show some rows, which is expected to be there.

I will post the simple schema and the actual SQL statements I am using so
that might be helpful.
--

Bharath


On 3/17/08 8:16 PM, "Ken" <[EMAIL PROTECTED]> wrote:



> 
> HTH,
> Ken



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

Re: [sqlite] Efficiency Question - Value Or Liability forIndexingof This Table?

[Lee Crain]

That search is currently in process.

Lee

_

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jay Sprenkle
Sent: Friday, March 14, 2008 4:36 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Efficiency Question - Value Or Liability
forIndexingof This Table?


The intelligent choice would be to begin a search for an employer who
is more rational.
___
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance degradation after upgrade to 3.5.x on slow flash cards

[Dennis Cote]

Dima Dat'ko wrote:

> Sorry for the long introduction. Here is the question. Am I right
> there is no option defined to have the stmtjrnl file in memory or in
> some specified path other then the same folder as the db? It's
> critical to prevent the db corruption in all kinds of software and
> hardware fails. If I manage stmtjrnl file to be created in memory
> instead of the slow flash card and the file disappear after a power
> brake on the device will it result in unrecoverable corruption of the
> data in the db?
> 

You are correct. SQLite requires the journal file to exist in the same 
directory as the database file itself. In memory databases don't have 
journal files at all.

> Any other advice for my problem?
> 

You could make a customized version of SQLite that keeps the journal at 
some other location. It would need to check that other location on 
startup, so that it can restore the database file using the journal 
entries in case there is a hot journal file left after a crash.

Whatever process opens the database after a crash must have access to 
both the database file and the journal to do this restoration and avoid 
database corruption. This is why they are stored in the same directory 
by default. If there is a possibility of the user removing the flash 
card after a crash, and inserting it into the PC to open the database, 
then the journal must be on the flash card to do the rollback. If it 
must be on the flash card, it might as well be in the same directory.

If you don't store the journal on the falsh card, your custom SQLite 
would have to have a mechanism to open the database and thereby rollback 
any uncommitted changes to ensure that the database is valid. The users 
must do this before removing the flash card with the database from the 
device.

I would suggest using a modified SQLite only as a last resort.

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

Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

[Dennis Cote]

Bharath Booshan L wrote:
> 
> I will give overview of what is happening
> 
> App A - Writer process
> ---
> * Open SQLIte Connection
> * BEGIN IMMEDIATE TRASACTION
> * Insert/Update some 1000 rows in Table A,B,C
> * COMMIT 
> * Close SQLite connection
> ---
> 
> 
> App B - Reader process
> 
> A) Open SQLite Connection
> 
> B) Read rows fro Table A,B,C based on some constraint -( Returned Results
> are
>   as expected)
> ...
> 
> C) Initiate App A to write some 10-20 times information (This step is
> executed several times, however it is ensured that all these write
> operations are serialized)
> 
> D) Read rows fro Table A,B,C based on some constraint -( Returned Results
> are not as expected. )
> 
> E) Close SQLite connection
> -
> 
> The actual problem I am facing is at the last step in App B, where I expect
> that all the information updated/inserted to be returned from query, instead
> some of the rows in table A,B,C are lost forever.

Based on what you have said here there should be no problems.

You will have to provide more detail about what you are doing to change 
the database. Since you say process A is the only one that write to the 
database, you would probably be best to post the actual code, or at 
least the actual SQL statements, that you are using in that process.

> (just to know what is left in database, I tried to open the database in some
> document application, for example TextEdit, and the row information which
> looked as erased are available, and in between all these the database size
> has not changed )
> 

That is a very unreliable way to look an SQLite database. SQLite doesn't 
necessarily remove data that has been deleted, it may remain in unused 
pages and/or records in the database file. SQLite doesn't change the 
size of the file when rows are deleted or updated, any pages that are no 
longer used are added to an internal free page list.

You should use the command line sqlite3 program to view the database file.

> Overall I guess I am using some wrong version which I need to upgrade to new
> version, but have to re-consider if it requires major change.

There should be no need to use a different version.


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

[sqlite] sqlite3_last_insert_rowid fails on ARM microprocessor

[Wilfred]

Function sqlite3_last_insert_rowid always returns 1.

I have compiled SQLite for ARM big endian mode using the gcc ARM compiler.

I have also compiled SQLite for the Axis ETRAX RISC processor.

The two platforms we are using are the Linksys NSLU2 (the unslung 
firmware) and Acme systems FOX Board:
http://www.nslu2-linux.org/wiki/Unslung/HomePage
http://www.acmesystems.it/?id=4

Could this be an endianess and/or bitmasks problem?

I used the following flags when compiling for NSLU2:
-DNDEBUG -DSQLITE_ALLOW_XTHREAD_CONNECT=1  -DSQLITE_THREADSAFE=1 
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -O3 
-fno-common -DPIC

The above compiler flags was generated on a host computer (my mac) by 
running:
configure --disable-tcl --enable-static  --enable-threadsafe 
--enable-cross-thread-connections

Compiling the same code on a standard Linux X86 and Mac PPC using the 
same flags work without any problems.

Regards,
Wilfred

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

Re: [sqlite] Database Table corrupt in SQLite v 3.4.0

[Ken]

I would start with APP A to determine after processing that the data that you 
asked to be loaded is actually loaded. If it does not, then look into app A.

If app A data load succeeds (and is correct in that all data loaded).
Run app B. If this errors out you more than likely have a query related problem.

HTH,
Ken


Bharath Booshan L <[EMAIL PROTECTED]> wrote: 

Hello List,

 Here I am stuck with some Database Table corrupt problem and I would
request the people on this list to help me out as I am unable to figure out
the cause for this problem.

Previously I was using SQLite 3.1.3 and now since our application should
support Mac OS Leapord, it uses SQLite 3.4.0 which is available in the OS by
default.

I will give overview of what is happening

App A - Writer process
---
* Open SQLIte Connection
* BEGIN IMMEDIATE TRASACTION
* Insert/Update some 1000 rows in Table A,B,C
* COMMIT 
* Close SQLite connection
---


App B - Reader process

A) Open SQLite Connection

B) Read rows fro Table A,B,C based on some constraint -( Returned Results
are
  as expected)
...

C) Initiate App A to write some 10-20 times information (This step is
executed several times, however it is ensured that all these write
operations are serialized)

D) Read rows fro Table A,B,C based on some constraint -( Returned Results
are not as expected. )

E) Close SQLite connection
-

The actual problem I am facing is at the step D, in App B, where I expect
that all the information updated/inserted to be returned from query, instead
some of the rows in table A,B,C are lost forever.

(just to know what is left in database, I tried to open the database in some
document application, for example TextEdit, and the row information which
looked as erased are available, and in between all these the database size
has not changed )



I have checked these following links and one thing I required to change is
"BEGIN IMMEDIATE TRANSACTION" to "BEGIN EXCLUSIVE TRANSACTION", but did not
see any impact from this.

http://www.sqlite.org/releaselog/3_4_0.html
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Overall I guess I am using some wrong version which I need to upgrade to new
version, but have to re-consider if it requires major change.

I am bit unclear in my explanation I suppose, please let me know I could
provide some more information.


Any inputs will be very helpful,


Thanks & Regards,

Bharath

PS: The information written into the database is just a text information,
precisely, it stores File attribute and some metadata.



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


___
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] Complex SELECT Syntax

[Dennis Cote]

Derek Developer wrote:
> Could someone help me understand what this syntax is doing please
> (from the Seinfeld demo database examples)
> 

What Seinfeld demo database examples?

> ..m col
> ..h on
> ..w 20 17 6 23 6
> ..e on
> 
> Is this some form of typecasting?

There isn't enough information here to make any sense of what you have 
written, or what you are asking.

> This is how they are used
> 

The items above don't appear anywhere in the SQL you posted.

You will have to be more specific in your question if you want to get a 
more useful answer.

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

Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step

[Dennis Cote]

Vincent Vega wrote:
> Anyhow, as I said before the code works fine most of the times(I can
> insert 500 records to the table and read them correctly) but from time
> to time I get SQLITE_CONSTRAINT error code after the sqlite3_step.
> Any Idea why?
> 
> Here are the table defenitions :
> CREATE TABLE 'Inventory' (
> 'TagIndex' integer PRIMARY KEY,
> 'Tag' varchar(12) NOT NULL,
> ) ;
> 
> CREATE UNIQUE INDEX InventoryIndex ON Inventory (Tag);
> 

Vincent,

You are almost certainly inserting the same value a second time into the 
Tag column. You have added a unique index to this column which will 
raise a constraint error if you try to insert a row that has the same 
value in that column as some other row.

If you don't really require that the Tag column data be unique for each 
row, then change your index to o normal index without the unique 
constraint.

HTH
Dennis Cote


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

Re: [sqlite] mail forwarding loop?

[Bharath Booshan L]

Same here

On 3/14/08 7:17 PM, "P Kishor" <[EMAIL PROTECTED]> wrote:

> I have now twice gotten the following message. What gives?
> 
> 
> This is the mail system at host sqlite.org.
> 
> I'm sorry to have to inform you that your message could not
> be delivered to one or more recipients. It's attached below.
> 
> For further assistance, please send mail to postmaster.
> 
> If you do so, please include this problem report. You can
> delete your own text from the attached returned message.
> 
>   The mail system
> 
> : mail forwarding loop for sqlite-users@sqlite.org
> 
> Final-Recipient: rfc822; sqlite-users@sqlite.org
> Original-Recipient: rfc822;sqlite-users@sqlite.org
> Action: failed
> Status: 5.4.6
> Diagnostic-Code: X-Postfix; mail forwarding loop for sqlite-users@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

[sqlite] Database Table corrupt in SQLite v 3.4.0

[Bharath Booshan L]

Hello List,

 Here I am stuck with some Database Table corrupt problem and I would
request the people on this list to help me out as I am unable to figure out
the cause for this problem.

Previously I was using SQLite 3.1.3 and now since our application should
support Mac OS Leapord, it uses SQLite 3.4.0 which is available in the OS by
default.

I will give overview of what is happening

App A - Writer process
---
* Open SQLIte Connection
* BEGIN IMMEDIATE TRASACTION
* Insert/Update some 1000 rows in Table A,B,C
* COMMIT 
* Close SQLite connection
---


App B - Reader process

A) Open SQLite Connection

B) Read rows fro Table A,B,C based on some constraint -( Returned Results
are
  as expected)
...

C) Initiate App A to write some 10-20 times information (This step is
executed several times, however it is ensured that all these write
operations are serialized)

D) Read rows fro Table A,B,C based on some constraint -( Returned Results
are not as expected. )

E) Close SQLite connection
-

The actual problem I am facing is at the step D, in App B, where I expect
that all the information updated/inserted to be returned from query, instead
some of the rows in table A,B,C are lost forever.

(just to know what is left in database, I tried to open the database in some
document application, for example TextEdit, and the row information which
looked as erased are available, and in between all these the database size
has not changed )



I have checked these following links and one thing I required to change is
"BEGIN IMMEDIATE TRANSACTION" to "BEGIN EXCLUSIVE TRANSACTION", but did not
see any impact from this.

http://www.sqlite.org/releaselog/3_4_0.html
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Overall I guess I am using some wrong version which I need to upgrade to new
version, but have to re-consider if it requires major change.

I am bit unclear in my explanation I suppose, please let me know I could
provide some more information.


Any inputs will be very helpful,


Thanks & Regards,

Bharath

PS: The information written into the database is just a text information,
precisely, it stores File attribute and some metadata.



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

[sqlite] (no subject)

[dongsheng zhang]

Hello, sqlite-users 

i want to be the user member of sqlite.please let me in ,3x so mush!

dongsheng zhang, [EMAIL PROTECTED]
2008-03-17 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rowid After Sorting

[Paul Smith]

>
>But I need my rowid to be chaged as follows.
>
>Rowid   Id  Name
>1 4  aaa
>2 3  bbb
>3 2  xxx
>4   1  zzz

You can't.

Rowid isn't an index of where the row appeared in the results, it's a 
'hidden' field in each row in the table. It just 'happens' that it's 
sequential by the order that rows were written to the table.

If you think of it as just being like any other field in the data, 
then it'll all make sense.

I suspect you're trying to use it for something it's not suitable 
for. The only thing you should really use it for (IMHO) is as a 
unique row identifier (hence the name). Some databases use a row 
'GUID' or 'OID' instead, but they're essentially the same.

Also, note that if you deleted the 'bbb' row from the table (for 
example), the results would come back as

14  aaa
32  xxx
4   1  zzz

So, rowid '2' would be missing.


PaulVPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED]  http://www.pscs.co.uk/


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

Re: [sqlite] Rowid After Sorting

[dan.winslow]

Why do you need your rowid to be changed?  

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mahalakshmi.m
Sent: Friday, March 14, 2008 1:03 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Rowid After Sorting

Hi,

I am working in 3.3.6 and my table looks like.

 

Id  -  Integer Primary Key

Name-   Text

 

Id Name

1 zzz

2 xxx

3 bbb

4 aaa

 

SELECT rowid,Id,Name FROM MyTable ORDER BY Name;

 

Rowid   Id  Name

 

4 4  aaa

3 3  bbb

2 2  xxx

1   1  zzz

 

But I need my rowid to be chaged as follows.

 

Rowid   Id  Name

 

1 4  aaa

2 3  bbb

3 2  xxx

4   1  zzz

 

I tried with Views but its rowid is not changed.

 

But by creating one new table like

 "create table Temp as select Name from Mytable order by Name;" 

gives the desired result as above.

 

Its taking more time for this.

So I there any other way I can do the same without creating table
because in My table I am having many

 fields and each time I will create and drop the table for each fields.

 

Can anyone please help to solve this.

 

Thanks & Regards,

Mahalakshmi.M

 

 

___
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] Performance degradation after upgrade to 3.5.x on slow flash cards

[Dima Dat'ko]

Dear List,

I faced with critical performance problem on a WinCE device when db
resides on it's slow built-in flash card (Windows CE .Net Version
4.20, processor Intel PXA255, some Toshiba obsolete compact flash
card, C++ application using SQLITE API directly).

Our application uses SQLite for collecting numerous measurements data
in field and for exchange the data between the field controller and
office postprocessing software. The db schema contains a number of
tables and triggers enforcing reference integrity in the db, as usual
like

CREATE TRIGGER on_update_fk_tblStations_fkeySoPoint
BEFORE UPDATE OF [fkeySoPoint] ON [tblStations]
  FOR EACH ROW BEGIN
SELECT RAISE(ABORT,'update violates foreign key constraint for
[tblStations].[fkeySoPoint]')
  WHERE NEW.fkeySoPoint IS NOT NULL
 AND (SELECT keySoPoint FROM tblSoPoints WHERE keySoPoint =
NEW.fkeySoPoint) IS NULL;
END;

All series of inserts and updates are done inside BEGIN and END transaction.

Having the triggers is essential for our application though it looks
like it's also essential to the problem we faced with after upgrade to
SQLITE 3.5.x. Though the problem proved to exist only for db on slow
flash cards there is no way to upgrade the devices and we have to
continue their support for a while. The problem did not exist in
earlier SQLITE versions - the performance of version 3.4.3 was quite
acceptable. Unfortunately we had to look for upgrade to more recent
version after we discovered the 3.4.3 had problems with removing
temporary files (http://www.sqlite.org/cvstrac/tktview?tn=2950 or
http://www.sqlite.org/cvstrac/tktview?tn=2350,33 for example) which
were critical for the divice. After we switched to version 3.5.3 the
speed of insert and update dropped by 10+ times. For 3.5.6 it's better
but still about 1.5-2 times slower then for 3.4.3 (again, the media
speed makes huge difference here: on very fast hard drives or flash
cards 3.5.6 works even better then 3.4.3!)

After some investigation I suspect it's the frequent access to the
temporary dbname-stmtjrnl file that slows down everything. The file
resides on the same folder as the db that's on that slow flash card.
I've tried to play with setting path to temporary files to the WinCE
standard place (\Temp which is in a RAM emulated file system and
read/write access is very fast for files there) but with no success. I
found that in pager.c (yes we can not use amalgamized version as the
VC++ debugger is getting crazy on line numbers grater then 65K) in
sqlite3PagerOpen() the field pPager->zStmtJrnl is hard coded to be set
to the same path as pPager->zJournal which will always set to the same
path as the db.

Sorry for the long introduction. Here is the question. Am I right
there is no option defined to have the stmtjrnl file in memory or in
some specified path other then the same folder as the db? It's
critical to prevent the db corruption in all kinds of software and
hardware fails. If I manage stmtjrnl file to be created in memory
instead of the slow flash card and the file disappear after a power
brake on the device will it result in unrecoverable corruption of the
data in the db?

Any other advice for my problem?

Thank you all in advance.

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

Re: [sqlite] Is there direct (non SQL) table access?

[dan.winslow]

Well, I'm not sure what you are trying to do that you can't do with SQL.
I'll assume that you really want to iterate over all rows of ( a single
table? ) and conditionally update a field? If so, wouldn't a
"update tablename set [Classification]='Retired' where [Age]>65" do what
you need?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of danjenkins
Sent: Sunday, March 16, 2008 12:19 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is there direct (non SQL) table access?


Is it possible to access a table without a SQL query?  I've been
searching and haven't found a way.
I'd like to do something like this:

sqlite3 *pDB = NULL; // database pointer
FILE* fp = fopen("people.db","a+");
sqlite3_open("people.db",);

// Pseudo code that I need help with follows...
while(!pDB->Eof){
if(pDB->FieldValues["Age"] > 65;
pDB->Edit();
pDB->FieldValues["Classification"] = "Retired";
pDB->Post();
}
pDB->Next();
}

fclose(fp);
sqlite3_close(pDB);

I'd really appreciate a couple of tips so I can get back on track with
my project.

Thanks a million.
Dan
--
View this message in context:
http://www.nabble.com/Is-there-direct-%28non-SQL%29-table-access--tp1607
5825p16075825.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

[sqlite] Database Table corrupt in SQLite v 3.4.0

[Bharath Booshan L]

Hello List,

 Here I am stuck with some Database Table corrupt problem and I would
request the people on this list to help me out as I am unable to figure out
the cause for this problem.

Previously I was using SQLite 3.1.3 and now since our application should
support Mac OS Leapord, it uses SQLite 3.4.0 which is available in the OS by
default.

I will give overview of what is happening

App A - Writer process
---
* Open SQLIte Connection
* BEGIN IMMEDIATE TRASACTION
* Insert/Update some 1000 rows in Table A,B,C
* COMMIT 
* Close SQLite connection
---


App B - Reader process

A) Open SQLite Connection

B) Read rows fro Table A,B,C based on some constraint -( Returned Results
are
  as expected)
...

C) Initiate App A to write some 10-20 times information (This step is
executed several times, however it is ensured that all these write
operations are serialized)

D) Read rows fro Table A,B,C based on some constraint -( Returned Results
are not as expected. )

E) Close SQLite connection
-

The actual problem I am facing is at the step D, in App B, where I expect
that all the information updated/inserted to be returned from query, instead
some of the rows in table A,B,C are lost forever.

(just to know what is left in database, I tried to open the database in some
document application, for example TextEdit, and the row information which
looked as erased are available, and in between all these the database size
has not changed )



I have checked these following links and one thing I required to change is
"BEGIN IMMEDIATE TRANSACTION" to "BEGIN EXCLUSIVE TRANSACTION", but did not
see any impact from this.

http://www.sqlite.org/releaselog/3_4_0.html
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Overall I guess I am using some wrong version which I need to upgrade to new
version, but have to re-consider if it requires major change.

I am bit unclear in my explanation I suppose, please let me know I could
provide some more information.


Any inputs will be very helpful,


Thanks & Regards,

Bharath

PS: The information written into the database is just a text information,
precisely, it stores File attribute and some metadata.



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

[sqlite] Database Table corrupt in SQLite v 3.4.0

[Bharath Booshan L]

Hello List,

 Here I am stuck with some Database Table corrupt problem and I would
request the people on this list to help me out as I am unable to figure out
the cause for this problem.

Previously I was using SQLite 3.1.3 and now since our application should
support Mac OS Leapord, it uses SQLite 3.4.0 which is available in the OS by
default.

I will give overview of what is happening

App A - Writer process
---
* Open SQLIte Connection
* BEGIN IMMEDIATE TRASACTION
* Insert/Update some 1000 rows in Table A,B,C
* COMMIT 
* Close SQLite connection
---


App B - Reader process

A) Open SQLite Connection

B) Read rows fro Table A,B,C based on some constraint -( Returned Results
are
  as expected)
...

C) Initiate App A to write some 10-20 times information (This step is
executed several times, however it is ensured that all these write
operations are serialized)

D) Read rows fro Table A,B,C based on some constraint -( Returned Results
are not as expected. )

E) Close SQLite connection
-

The actual problem I am facing is at the last step in App B, where I expect
that all the information updated/inserted to be returned from query, instead
some of the rows in table A,B,C are lost forever.

(just to know what is left in database, I tried to open the database in some
document application, for example TextEdit, and the row information which
looked as erased are available, and in between all these the database size
has not changed )



I have checked these following links and one thing I required to change is
"BEGIN IMMEDIATE TRANSACTION" to "BEGIN EXCLUSIVE TRANSACTION", but did not
see any impact from this.

http://www.sqlite.org/releaselog/3_4_0.html
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Overall I guess I am using some wrong version which I need to upgrade to new
version, but have to re-consider if it requires major change.

I am bit unclear in my explanation I suppose, please let me know I could
provide some more information.


Any inputs will be very helpful,


Thanks & Regards,

Bharath

PS: The information written into the database is just a text information,
precisely, it stores File attribute and some metadata.





---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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