Re: [sqlite] Accessing sqlite3 through javascript.

[P Kishor]

On Sat, May 16, 2009 at 6:19 AM, Igor Tandetnik  wrote:
> "Saurabh Pawar" 
> wrote in message news:240022.95142...@web95016.mail.in2.yahoo.com
>> So I would like to have some
>> information if available on how to connect to sqlite database through
>> javascript and access the same.
>
> http://gears.google.com/

gears allows creating and interacting with db on the client's machine,
just like HTML5 databases, but not with db on the server machine. One
would have to use some kind of server-side js and then interact with
that via the client-side js.


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



-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
Sent from Karlstad, Sweden
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accessing sqlite3 through javascript.

[Igor Tandetnik]

"Saurabh Pawar" 
wrote in message news:240022.95142...@web95016.mail.in2.yahoo.com
> So I would like to have some
> information if available on how to connect to sqlite database through
> javascript and access the same.

http://gears.google.com/

Igor Tandetnik 



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

Re: [sqlite] Accessing sqlite3 through javascript.

[P Kishor]

On Sat, May 16, 2009 at 5:45 AM, Saurabh Pawar  wrote:
>
> I have been using sqlite3 for my engine based programs and now would like to 
> use it for my web based applications which are completely based on html and 
> javascript. So I would like to have some information if available on how to 
> connect to sqlite database through javascript and access the same.Also would 
> like to know if there are any disadvantages in doing so over a normal MYSQL 
> database(which is done through PHP and which is what i want to avoid).
> Thank You.
>
>

Javascript runs in the client, and the db is sitting on the server, so
there is no obvious way to use js to extract data from sqlite.
However, HTML5 offers a way of working a db. See Apple's example of
using HTML5 and databases with Safari. It uses sqlite to create the db
on the client's computer. In other words, every user gets his/her own
db, not a server db.


-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
Sent from Karlstad, Sweden
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Accessing sqlite3 through javascript.

[Saurabh Pawar]

I have been using sqlite3 for my engine based programs and now would like to 
use it for my web based applications which are completely based on html and 
javascript. So I would like to have some information if available on how to 
connect to sqlite database through javascript and access the same.Also would 
like to know if there are any disadvantages in doing so over a normal MYSQL 
database(which is done through PHP and which is what i want to avoid).
Thank You.




  Explore and discover exciting holidays and getaways with Yahoo! India 
Travel http://in.travel.yahoo.com/

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

Re: [sqlite] Corrupt Database Problems

[John Machin]

On 16/05/2009 3:08 AM, Jim Wilcoxson wrote:
> I think you have answered your own question.  If you use
> synchronous=off, you are saying "I don't care much about this
> database."  When you "save" documents, you are merely putting them in
> a computer's cache (memory) and then confirming to the user that they
> are on the hard drive, when they aren't necessarily there.
> 
> So, user clicks Save, program says it saved it, user turns off
> computer, database is corrupt.  Don't know why this would happen all
> of a sudden, unless maybe they upgraded their OS and it has decided to
> cache volatile data longer to increase performance at the expense of
> data integrity.
> 
> I hope you're able to rescue your data.  Someone else mentioned on
> this list a while back that they could recover their data by doing
> retrieval based on rowid: do a select * where rowid=1, then 2, then 3,
> etc. until you get a failure.  Once you get a failure, the rest is
> lost.

Not necessarily. I have been looking at a corrupt cookies.sqlite 
database from a FireFox incident a few months ago.  It has similar 
output from pragma integrity_check to what Kevin posted, with an added 
bonus: one case where an overflow page chain points to a data page 
instead of an overflow page. Doing a less tedious equivalent of the 
above (follow the B-tree until the first appearance of corruption) 
yielded 74 rows. Note that stopping at the first select failure is 
equivalent to stopping at the first "error code 11" error message. Not 
much of the file has been traversed. Skipping bad rows and bad pages 
yielded 853 rows with 833 unique rowids -- I haven't yet checked whether 
same rowid means same contents always/sometimes/never.

There were 3 "never used" pages; should possibly be described as 
"orphan" rather than "never used", because one was all zeroes but the 
other 2 look like data pages -- not yet explored.

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

Re: [sqlite] Use the result of a query, as a variable name in another query ?

[Florian Nigsch]

Hey,

I don't know if this works in SQLite the same way, but in MySQL you  
can do it like this:

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html (Basically  
"prepare" an SQL statement from a string)

Best,

Flo

On 15 May 2009, at 16:30, Stef Mientki wrote:

> hello,
>
> to test complex queries, I want to use the command line utility ( or  
> an
> equivalent that remembers what I type).
>
> Now I've a table which contains the names of a set of other tables.
> In Python I can easily create the SQL string (containing the field  
> name)
> and commit it to the database.
>
> Is there a way realize this in the command line ?
> So I need to get the result of a query into a variable and then use  
> that
> variable in a new query.
>
> thanks,
> Stef Mientki
> ___
> 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] About Time Field

[John Stanton]

Sqlite does not have a TIME type..  It is interpreting your time as an 
integer or floating point number or maybe text, depending upon its format.

Hughman wrote:
> Hi,
>
> I create a table with a field of datatype Time, and when I insert a
> formatting string like 'HHMMSS' into it , the first number 0 always be
> trimed . I want to keep it, how should I do?
> For exampe, '081220' will be converted into '81220'.
>
>
> 
>Say goodbye to romance...
> ___
> 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] SQL logic error or missing database

[Igor Tandetnik]

Alex Ousherovitch
 wrote:
> Is it safe to use sqlite3_errmsg() when each thread is using its own
> connection handle

Yes. Each connection allocates its own memory for this string.

Igor Tandetnik 



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

Re: [sqlite] SQL logic error or missing database

[Alex Ousherovitch]

>> At the end of the loop, instead of having 4000 rows I have 3976 rows
>> (it's random, sometimes I have 3972 or 3974).
>> sqlite3_exec doesn't returns any error during the INSERT statement,
>> but I have some errors during the BEGIN IMMEDIATE, errors are all:
>> SQL logic error or missing database (printed with sqlite3_errmsg).

> Your use of sqlite3_errmsg is itself very likely a race. Between the 
> time you detect an error and the time you retrieve error message, the 
> other thread could have run some statements that modify the error 
> message. Moreover, between the time you call sqlite3_errmsg and the
time 
> you actually print the string pointed to by the char* pointer the 
> function returns, the string may be modified or even deallocated.

> Igor Tandetnik 

Is it safe to use sqlite3_errmsg() when each thread is using its own
connection handle to access the database and get the error messages for
that connection?


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

[sqlite] Use the result of a query, as a variable name in another query ?

[Stef Mientki]

hello,

to test complex queries, I want to use the command line utility ( or an 
equivalent that remembers what I type).

Now I've a table which contains the names of a set of other tables.
In Python I can easily create the SQL string (containing the field name) 
and commit it to the database.

Is there a way realize this in the command line ?
So I need to get the result of a query into a variable and then use that 
variable in a new query.

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

Re: [sqlite] SQLite spawns multiple processes?

[Logan.Ratner]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of D. Richard Hipp
> Sent: Friday, May 15, 2009 1:03 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite spawns multiple processes?
> 
> 
> On May 15, 2009, at 1:21 PM, 
>   > wrote:
> 
> > I don't know what causes it, but I see the exact same thing running
an
> > embedded 2.6.17 kernel on a PPC architecture.  And my application is
> > decidedly single-threaded. Oddly enough, the exact same application
> > running on multiple 2.6 kernels running on i86 machines does not
show
> > this behavior.
> >
> 
>  From a comment in the os_unix.c source file (which implements the
> SQLite backend for all unix-like systems):
> 
> ** Yet another problem:  LinuxThreads do not play well with posix
locks.
> **
> ** Many older versions of linux use the LinuxThreads library which is
> ** not posix compliant.  Under LinuxThreads, a lock created by thread
> ** A cannot be modified or overridden by a different thread B.
> ** Only thread A can modify the lock.  Locking behavior is correct
> ** if the appliation uses the newer Native Posix Thread Library (NPTL)
> ** on linux - with NPTL a lock created by thread A can override locks
> ** in thread B.  But there is no way to know at compile-time which
> ** threading library is being used.  So there is no way to know at
> ** compile-time whether or not thread A can override locks on thread
B.
> ** We have to do a run-time check to discover the behavior of the
> ** current process.
> 
> The run-time check mentioned in the last sentence involves creating a
> new thread and experimenting to see how it interacts with posix
> advisory locks.  I'm guessing that extra thread is what you are seeing
> as a second process.

That could be.  Is it expected that this 'extra' thread never exits?  On
my system once it appears it never goes away.  strace tells me that it's
not doing much (it calls poll() and getppid() about once every two
seconds), but it does hang around.

> One always runs into a bazillion subtle problems like this when you
> get into threads.  I keep trying to tell y'all that threads are evil.
> I wish you'd listen.

Holy wars aside, as I said, my application is single threaded.

> If you recompile SQLite with -DSQLITE_THREADSAFE=0 then all of the
> above code is omitted and you should not get any extra process table
> entries.

That would be terrific.  While it doesn't seem to break anything, I
would like to have the process resources back.  Thanks!

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

Logan Ratner | Software Engineer | Gas Chromatographs
Emerson Process Management | 5650 Brittmoore Rd | Houston | TX | 77041 |
USA
T +1 713 839 9656 | F +1 713 827 3807
logan.rat...@emerson.com

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

Re: [sqlite] SQLl question

[Evan Burkitt]

On 05/15/2009 03:41, Dennis Cote wrote:
>
> Your database would be simpler if you simply combined these two tables
> into a single table in the first place. It would eliminate the need to
> combine them for this type of query. The tables already have a type
> field to distinguish the email adresses from the phone numbers, so there
> is no need to put them in separate tables.
>
Thanks to Dennis, John and Patty for your answers. I had the feeling 
this was not difficult, but up to now I've managed to avoid learning 
about SQL unions.

This isn't my database, so I live with what's there. In fact, P and E 
aren't even tables. They're subqueries, and this solution is going to be 
part of a larger query that extracts data from one (complex and large) 
DB2 database and puts it into another, simpler one in SQLite. Both 
source and target are already specified and the program design is such 
that the adaptation must be done completely in the query.

Thanks again.

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

Re: [sqlite] SQLite spawns multiple processes?

[D. Richard Hipp]

On May 15, 2009, at 1:21 PM,  
 wrote:

> I don't know what causes it, but I see the exact same thing running an
> embedded 2.6.17 kernel on a PPC architecture.  And my application is
> decidedly single-threaded. Oddly enough, the exact same application
> running on multiple 2.6 kernels running on i86 machines does not show
> this behavior.
>

 From a comment in the os_unix.c source file (which implements the  
SQLite backend for all unix-like systems):

** Yet another problem:  LinuxThreads do not play well with posix locks.
**
** Many older versions of linux use the LinuxThreads library which is
** not posix compliant.  Under LinuxThreads, a lock created by thread
** A cannot be modified or overridden by a different thread B.
** Only thread A can modify the lock.  Locking behavior is correct
** if the appliation uses the newer Native Posix Thread Library (NPTL)
** on linux - with NPTL a lock created by thread A can override locks
** in thread B.  But there is no way to know at compile-time which
** threading library is being used.  So there is no way to know at
** compile-time whether or not thread A can override locks on thread B.
** We have to do a run-time check to discover the behavior of the
** current process.

The run-time check mentioned in the last sentence involves creating a  
new thread and experimenting to see how it interacts with posix  
advisory locks.  I'm guessing that extra thread is what you are seeing  
as a second process.

One always runs into a bazillion subtle problems like this when you  
get into threads.  I keep trying to tell y'all that threads are evil.   
I wish you'd listen.

If you recompile SQLite with -DSQLITE_THREADSAFE=0 then all of the  
above code is omitted and you should not get any extra process table  
entries.



D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLite spawns multiple processes?

[Logan.Ratner]

I don't know what causes it, but I see the exact same thing running an
embedded 2.6.17 kernel on a PPC architecture.  And my application is
decidedly single-threaded. Oddly enough, the exact same application
running on multiple 2.6 kernels running on i86 machines does not show
this behavior.

For what it's worth, the application runs fine for me, and we've beat up
on it a fair bit over the course of several months.  Still, the clone
process worries me a bit, and I'd love to be rid of it.

Logan Ratner | +1 713 839 9656

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of jkim...@one.net
Sent: Friday, May 15, 2009 10:23 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite spawns multiple processes?


I'm running a embedded SQL C application (SQLite v3.6.13) on an embedded
platform (Coldfire) running Linux 2.6.25 kernel. Something odd I've
noticed is that when my application reaches the point of calling dbopen
it
spawns a new process, with the same name as my application, so that the
process list always shows two of my application running.

Why is this? Is this normal behaviour?

My application talks (via a socket) to a "backend" process that is
multithreaded and also reads/writes to the database and each thread
creates two processes.

Am I doing something wrong here or is this just something SQLite does
normally? I'm having problems with my application that seem to jump
around
and are definately memory corruption related so I'm wondering if I'm
doing
something wrong with the DB and causing this.

Any help or suggestions would be much appreciated...

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

Re: [sqlite] SQLite spawns multiple processes?

[Ken]

not sure really... 

But threading in linux will show two processes when a thread is created.

Can you run strace and see if clone is called at thread creation?

Check your threading package and verify its internal operations. Newer versions 
do not show duplicate process. But older versions do.



--- On Fri, 5/15/09, jkim...@one.net  wrote:

> From: jkim...@one.net 
> Subject: [sqlite] SQLite spawns multiple processes?
> To: sqlite-users@sqlite.org
> Date: Friday, May 15, 2009, 10:23 AM
> 
> I'm running a embedded SQL C application (SQLite v3.6.13)
> on an embedded
> platform (Coldfire) running Linux 2.6.25 kernel. Something
> odd I've
> noticed is that when my application reaches the point of
> calling dbopen it
> spawns a new process, with the same name as my application,
> so that the
> process list always shows two of my application running.
> 
> Why is this? Is this normal behaviour?
> 
> My application talks (via a socket) to a "backend" process
> that is
> multithreaded and also reads/writes to the database and
> each thread
> creates two processes.
> 
> Am I doing something wrong here or is this just something
> SQLite does
> normally? I'm having problems with my application that seem
> to jump around
> and are definately memory corruption related so I'm
> wondering if I'm doing
> something wrong with the DB and causing this.
> 
> Any help or suggestions would be much appreciated...
> 
> ___
> 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] Corrupt Database Problems

[Jim Wilcoxson]

I think you have answered your own question.  If you use
synchronous=off, you are saying "I don't care much about this
database."  When you "save" documents, you are merely putting them in
a computer's cache (memory) and then confirming to the user that they
are on the hard drive, when they aren't necessarily there.

So, user clicks Save, program says it saved it, user turns off
computer, database is corrupt.  Don't know why this would happen all
of a sudden, unless maybe they upgraded their OS and it has decided to
cache volatile data longer to increase performance at the expense of
data integrity.

I hope you're able to rescue your data.  Someone else mentioned on
this list a while back that they could recover their data by doing
retrieval based on rowid: do a select * where rowid=1, then 2, then 3,
etc. until you get a failure.  Once you get a failure, the rest is
lost.

Good luck,
Jim

On 5/15/09, Kevin Gale  wrote:
...
> 4. synchronous is OFF (we have stopped setting this in the new build of our
> app).

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

Re: [sqlite] About Time Field

[cmartin]

On Fri, 15 May 2009, Hughman wrote:

>> There is no Time type in SQLite.
>
> Oops... I use Sqlite Administrator to create a table , and the datatypes are
> almost as many as MySQL , such as Date, Time, TimeStamp, varchar.
> Since sqlite only has 5 kinds of datatype, why doesn't it throw a error
> message when I create a table with a wrong datatype?

Igor previously indicated (above) that there is no time type in SQLIte. 
However, he also said:

> For more details, see http://sqlite.org/datatype3.html

Based on your question about errors not being thrown, you apparently did 
not read the above link for the details; if you had you would realize no 
error is thrown because no error has occurred. SQLite does not use static 
typing, like mySQL and most others, it uses manifest typing.

Read the link and all will be clear.

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

Re: [sqlite] About Time Field

[Igor Tandetnik]

Hughman  wrote:
>> There is no Time type in SQLite.
>
> Oops... I use Sqlite Administrator to create a table , and the
> datatypes are
> almost as many as MySQL , such as Date, Time, TimeStamp, varchar.
> Since sqlite only has 5 kinds of datatype, why doesn't it throw a
> error
> message when I create a table with a wrong datatype?

In SQLite, you can write

create table t(col LOREM IPSUM);

and it will happily accept LOREM IPSUM as the type name. The article I 
referred you to explains how column affinity is determined from type 
name. TIME is not in any way special.

>> I bet you don't actually use quotes as you show above.
> In fact, I have used in the sql code.

Ah, right. The type name of TIME would result in NUMERIC column 
affinity. This means that a string that looks like a number is converted 
to and stored as a number.

Use TEXT, CHAR or VARCHAR for a type name to get TEXT affinity, or omit 
the type name entirely to get no affinity.

Igor Tandetnik 



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

Re: [sqlite] CURRENT_TIMESTAMP precision

[Eric Minbiole]

Please ignore my previous post.  Doug's suggestion is much better.

~Eric

Eric Minbiole wrote:
>> I would like CURRENT_TIMESTAMP to be more accurate than just one second, 
>> any suggestions on how I might do that once?  My solution is all a C/C++ 
>> interface, so all features are open to me.
> 
> One option would be to create and register a custom SQL function that 
> returned the current time, including fractional seconds.  If you format 
> your result as "-MM-DD HH:MM:SS.SSS", then all standard SQLite date 
> functions should work as-is (up to millisecond resolution).
> 
> Of course, how you actually get the time from the O/S will depend on 
> your particular O/S.  For windows, GetSystemTimeAsFileTime() may be of 
> use.  (Resolution around 1~16ms depending on O/S version.)  I'm sure 
> others can help with APIs for other Operating Systems.
> 
> Some links:
> 
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> http://www.sqlite.org/c3ref/create_function.html
> http://msdn.microsoft.com/en-us/library/ms724397(VS.85).aspx
> 
> ~Eric
> ___
> 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] CURRENT_TIMESTAMP precision

[Eric Minbiole]

> I would like CURRENT_TIMESTAMP to be more accurate than just one second, 
> any suggestions on how I might do that once?  My solution is all a C/C++ 
> interface, so all features are open to me.

One option would be to create and register a custom SQL function that 
returned the current time, including fractional seconds.  If you format 
your result as "-MM-DD HH:MM:SS.SSS", then all standard SQLite date 
functions should work as-is (up to millisecond resolution).

Of course, how you actually get the time from the O/S will depend on 
your particular O/S.  For windows, GetSystemTimeAsFileTime() may be of 
use.  (Resolution around 1~16ms depending on O/S version.)  I'm sure 
others can help with APIs for other Operating Systems.

Some links:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
http://www.sqlite.org/c3ref/create_function.html
http://msdn.microsoft.com/en-us/library/ms724397(VS.85).aspx

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

[sqlite] SQLite spawns multiple processes?

[jkimble]

I'm running a embedded SQL C application (SQLite v3.6.13) on an embedded
platform (Coldfire) running Linux 2.6.25 kernel. Something odd I've
noticed is that when my application reaches the point of calling dbopen it
spawns a new process, with the same name as my application, so that the
process list always shows two of my application running.

Why is this? Is this normal behaviour?

My application talks (via a socket) to a "backend" process that is
multithreaded and also reads/writes to the database and each thread
creates two processes.

Am I doing something wrong here or is this just something SQLite does
normally? I'm having problems with my application that seem to jump around
and are definately memory corruption related so I'm wondering if I'm doing
something wrong with the DB and causing this.

Any help or suggestions would be much appreciated...

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

Re: [sqlite] CURRENT_TIMESTAMP precision

[Doug Currie]

On May 15, 2009, at 9:07 AM, Sam Carleton wrote:

> I would like CURRENT_TIMESTAMP to be more accurate than just one  
> second, any suggestions on how I might do that once?  My solution is  
> all a C/C++ interface, so all features are open to me.

Option 1 - use: julianday('now') instead of CURRENT_TIMESTAMP

Option 2 - modify ctimestampFunc in date.c

Presently ctimestampFunc uses datetimeFunc; datetimeFunc doesn't  
return fractional seconds.

e


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

[sqlite] Corrupt Database Problems

[Kevin Gale]

Hi.

We are using SQLite 3.3.6 via Realbasic & the REALSQLDatabase plug-in. Over the 
past few weeks we have had calls from different customers stating that the 
documents that they have been saving from our application (a sqlite database) 
are now empty.

I have received the database from the customer and when I step through the 
statements I get the following RB error when I perform a select on one of the 
tables:
SQL logic error or missing database

If I open the database using SQLiteManager (an OS X GUI application) I get 
several errors (including the following) when I analyse the database:
*** in database main ***
Page 5517: initPage() returns error code 11
Page 5518: initPage() returns error code 11
Page 5491: initPage() returns error code 11
On tree page 22 cell 58: Child page depth differs
Page 5508: initPage() returns error code 11
Page 5510: initPage() returns error code 11
Page 5520: initPage() returns error code 11
Page 5584: initPage() returns error code 11
Page 5758: initPage() returns error code 11
On tree page 17 cell 15: Child page depth differs
Page 5817: initPage() returns error code 11
Page 5993: initPage() returns error code 11
Page 6172: initPage() returns error code 11
Page 6362: initPage() returns error code 11
Page 6582: initPage() returns error code 11
Page 515 is never used
Page 516 is never used
Page 517 is never used
Page 518 is never used
Page 519 is never used
Page 520 is never used
Page 521 is never used
Page 522 is never used
Page 523 is never used


When we received the first report of the problem we passed it off as a problem 
with the user's computer. However, we have now had at least 5 of the reports so 
we now need to find the cause of the problem.
Here is more info on what we are doing:
1. The database is always ran single user.

2. A new database file is always created on the local hard disk every time the 
user saves.

3. AutoCommit is disabled.

4. synchronous is OFF (we have stopped setting this in the new build of our 
app).

5. The databases are encrypted.


We are now in a position where we are going to have to tell the customer that 
they are going to have to start again. This is going to be very upsetting as 
they will have spent a long time creating this document (my guess is that they 
won't have a backup).

What we are looking for is help in trying to understand why the problem is 
occurring and what we can do to prevent it. If anyone could help trying to 
restore the database file that would also help us.


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

Re: [sqlite] About Time Field

[Hughman]

Thanks.

> There is no Time type in SQLite.

Oops... I use Sqlite Administrator to create a table , and the datatypes are
almost as many as MySQL , such as Date, Time, TimeStamp, varchar.
Since sqlite only has 5 kinds of datatype, why doesn't it throw a error
message when I create a table with a wrong datatype?

> I bet you don't actually use quotes as you show above.
In fact, I have used in the sql code.

   Say goodbye to romance...


On Fri, May 15, 2009 at 8:37 PM, Igor Tandetnik  wrote:

> "Hughman"  wrote in
> message
> news:f1a32add0905150528r3bc74b2epd7ab93539ac68...@mail.gmail.com
> > I create a table with a field of datatype Time
>
> There is no Time type in SQLite. For more details, see
> http://sqlite.org/datatype3.html
>
> > and when I insert a
> > formatting string like 'HHMMSS' into it , the first number 0 always be
> > trimed .
>
> I bet you don't actually use quotes as you show above. In which case,
> what you store is an integer. Naturally, 012345 = 12345.
>
> Consider storing a string in HH:MM:SS format instead. That would allow
> you to use built-in date/time functions if you ever need to perform time
> arithmetic on this field: http://sqlite.org/lang_datefunc.html
>
> Igor Tandetnik
>
>
>
> ___
> 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] CURRENT_TIMESTAMP precision

[Sam Carleton]

I would like CURRENT_TIMESTAMP to be more accurate than just one second, 
any suggestions on how I might do that once?  My solution is all a C/C++ 
interface, so all features are open to me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About Time Field

[Igor Tandetnik]

"Hughman"  wrote in
message
news:f1a32add0905150528r3bc74b2epd7ab93539ac68...@mail.gmail.com
> I create a table with a field of datatype Time

There is no Time type in SQLite. For more details, see 
http://sqlite.org/datatype3.html

> and when I insert a
> formatting string like 'HHMMSS' into it , the first number 0 always be
> trimed .

I bet you don't actually use quotes as you show above. In which case, 
what you store is an integer. Naturally, 012345 = 12345.

Consider storing a string in HH:MM:SS format instead. That would allow 
you to use built-in date/time functions if you ever need to perform time 
arithmetic on this field: http://sqlite.org/lang_datefunc.html

Igor Tandetnik



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

[sqlite] About Time Field

[Hughman]

Hi,

I create a table with a field of datatype Time, and when I insert a
formatting string like 'HHMMSS' into it , the first number 0 always be
trimed . I want to keep it, how should I do?
For exampe, '081220' will be converted into '81220'.



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

Re: [sqlite] SQLl question

[Patty Lindsay]

Try

select n.name, p.type, p.addr
from p
join name on p.id = n.id
union
select n.name, e.type, e.addr
from e
join name on e.id = n.id

Patty

On Fri, May 15, 2009 at 12:14 AM, Evan Burkitt  wrote:
> This isn't a Sqlite question per se, but I know there are some SQL gurus
> here who might have some insight into this problem. I apologize for
> being off-topic; I can be shameless when I need help. :)>
>
> I have three tables, N, P and E. N contains the fields id and name. The
> other two each contain the fields id, type and addr. P holds phone
> numbers, E email addresses. In P, the type field is always 'phone'; in
> the P it is always 'email'. They are all related on id.
>
> I want to build a single query that will return a result set consisting
> of N.name, P/E.type and P/E.addr. That is, it contains the like-named
> fields of both P and E. For example:
>
> -name -type--- -addr-
> "John Smith", "phone", "123-555-1212"
> "John Smith", "email", "john.sm...@domain.com"
> "Bill Jones", "phone", "123-555-1213"
> "Jane Johnson", "email", "j...@anotherdomain.com"
>
> and so forth. The order of the names and types is not important.
>
> Is this possible?
>
> -evan
> ___
> 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] How sqlite will store the data?

[John Machin]

On 15/05/2009 8:41 PM, Pramoda M. A wrote:
> But how to get the rows in the same order of insertion?
> Say, I will insert 2 3 4 and it will store in 2 4 3. But I need in the order 
> 2 3 4. Is it possible?

Possibility (1): Unless you use INTEGER PRIMARY KEY and supply your own 
values for the key column, the ROWID pseudocolumn will be automatically 
given values in ascending sequence. Then you can do queries like:

SELECT * FROM your_table ORDER BY ROWID;

Possibility (2): Have a column named e.g. when_created and populate it 
with CURRENT_TIMESTAMP either implicitly using a DEFAULT column 
constraint in CREATE TABLE, or explicitly when you INSERT. Note: the 
precision of the clock (1 second) may not be enough; you may wish to use 
  ROWID as a tie-breaker in your ORDER BY clause.

... *but* why do you want to recover stuff in insertion order?

HTH,

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

Re: [sqlite] How sqlite will store the data?

[Igor Tandetnik]

"Pramoda M. A" 
wrote in message
news:f7846b8f3c78c049b6a1dff861f6c16f03e94...@kcinblrexb01.kpit.com
> But how to get the rows in the same order of insertion?
> Say, I will insert 2 3 4 and it will store in 2 4 3. But I need in
> the order 2 3 4. Is it possible?

You would need a column to store some kind of ordinal or timestamp, set 
to increasing values when inserting, so you can order on it when 
selecting. See http://sqlite.org/autoinc.html - you can get SQLite to 
maintain just such a column for you automatically.

Igor Tandetnik 



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

Re: [sqlite] How sqlite will store the data?

[Pramoda M. A]

But how to get the rows in the same order of insertion?
Say, I will insert 2 3 4 and it will store in 2 4 3. But I need in the order 2 
3 4. Is it possible?


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Michael Schlenker
Sent: Friday, May 15, 2009 3:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How sqlite will store the data?

Pramoda M. A schrieb:
> In which order, SQLite will store the data? 
In any order it likes, it might use the insertion order if thats convenient.
> 
> I am using SQLite 3.6.5 and which is storing data in some other manner
> than I am inserting? 
> 
> How to make it to store in the same way as insertion?
> 
You usually don't. Simply use ORDER BY when retrieving your data, it was
made for that.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
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] How sqlite will store the data?

[Michael Schlenker]

Pramoda M. A schrieb:
> In which order, SQLite will store the data? 
In any order it likes, it might use the insertion order if thats convenient.
> 
> I am using SQLite 3.6.5 and which is storing data in some other manner
> than I am inserting? 
> 
> How to make it to store in the same way as insertion?
> 
You usually don't. Simply use ORDER BY when retrieving your data, it was
made for that.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How sqlite will store the data?

[Pramoda M. A]

Hi All,

 

In which order, SQLite will store the data? 

I am using SQLite 3.6.5 and which is storing data in some other manner
than I am inserting? 

How to make it to store in the same way as insertion?

Please anybody help me.

 

With Regards

Pramoda.M.A

 

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