Re: [sqlite] Create table error on AIX -- Unable to open database "mytest": SQL logic error or missing database

[John Stanton]

I had a problem compiling Sqlite on ealrier versions of AIX.  It turned 
out to be a linker problem and compiling without the -g optimization 
solved the problem.

What compiler are you using?  Xlc or gcc?

Chris Pierce wrote:
> Hi,
> 
> I am having problems with SQLite v3.5.7 on AIX v5.2.
> 
> I downloaded the amalgamation tarball.  It looks like
> it builds fine, but I get an error when I try to
> create a table.
> 
> Here's what I'm doing/getting:
> 
> AIX$ ./sqlite3 test.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> create table mytest(first smallint);
> Unable to open database "mytest": SQL logic error or
> missing database
> AIX$
> 
> I've searched the list's archives and seen some
> similar problems, but I have been unable to fix the
> problem.
> 
> Looking through the config.log file I guess it's not
> building fine.  Here are a few things from the log
> that may or may not help in troubleshooting:
> 
> 1) configure:3025: cc -c -Os  conftest.c >&5
> "conftest.c", line 15.14: 1506-275 (S) Unexpected text
> me encountered.
> "conftest.c", line 15.8: 1506-045 (S) Undeclared
> identifier choke.
> configure:3031: $? = 1
> configure: failed program was:
> | /* confdefs.h.  */
> | #define PACKAGE_NAME "sqlite"
> | #define PACKAGE_TARNAME "sqlite"
> | #define PACKAGE_VERSION "3.5.7"
> | #define PACKAGE_STRING "sqlite 3.5.7"
> | #define PACKAGE_BUGREPORT "http://www.sqlite.org;
> | #define PACKAGE "sqlite"
> | #define VERSION "3.5.7"
> | /* end confdefs.h.  */
> |
> | int
> | main ()
> | {
> | #ifndef __GNUC__
> |choke me
> | #endif
> |
> |   ;
> |   return 0;
> | }
> 
> 2) configure:3279: cc  -c -Os  conftest.c >&5
> "conftest.c", line 45.39: 1506-195 (S) Integral
> constant expression with a value
>  greater than zero is required.
> configure:3285: $? = 1
> configure: failed program was:
> | /* confdefs.h.  */
> 
> 3) configure:3279: cc -qlanglvl=extc89 -c -Os 
> conftest.c >&5
> 1506-173 (W) Option langlvl=extc89 is not valid. 
> Enter xlc for list of valid op
> tions.
> "conftest.c", line 45.39: 1506-195 (S) Integral
> constant expression with a value
>  greater than zero is required.
> 
> 
> I'm not sure if any of this is helpful or not.  There
> are more errors like this.  I can send the whole
> config.log if needed.  Oh, the system has cc, but
> doesn't have gcc, FWIW.
> 
> BTW, I used the Windows version to create a small test
> database that works fine in Windows, but when I try to
> use it with the AIX build I still get the error:
> 
> AIX$ ./sqlite3 test.db
> Unable to open database "test.db": SQL logic error or
> missing database
> AIX$
> 
> Sorry for the long message, but if anyone could help I
> would greatly appreciate it.
> 
> I'm not much of a C programmer, I'm rusty on *nix, and
> I'm not an admin on this system.
> 
> Please let me know if there is other information I
> need to send that might help in fixing the problem (or
> if I'm just completely missing the obvious cause).
> 
> Thanks!
> 
> Chris
> [EMAIL PROTECTED]
> 
> 
> 
>   
> 
> You rock. That's why Blockbuster's offering you one month of Blockbuster 
> Total Access, No Cost.  
> http://tc.deals.yahoo.com/tc/blockbuster/text5.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

Re: [sqlite] What is the standard way to store dates and do operations with dates please?

[John Stanton]

Not a hack, but the traditional way to store dates and times.  Sqlite 
functions use a magic epoch which facilitates the presentation of the 
date and time in the form of the major calendars.

We do not use the Julian calendar these days.  It was supplanted by the 
Gregorian in 1582.  Julian refers to defining a date by offset from an 
epoch and is the preferred method of storing date and time.

sqlfan wrote:
> is this just your "hack" or the standard way to do this?  I don't need it to
> be floating point, since I'm not interested in "when" during the day.  and,
> to be clear, "julian" is the calendar we all use, right? it's completely 1:1
> with the ansi format 2008-04-05 that I mentioned, right?
> 
> Thank you.
> 
> 
> Dennis Cote-2 wrote:
> 
>>sqlfan wrote:
>>
>>>I'm very new to sqlite but I notice there is no way to mark a column as
>>>containing dates... What is the standard way to do operations with dates,
>>>please, and to store dates?  Should I try the format 20080405 and do my
>>>own
>>>calculations using my language's standard library?  (I'm using Python) or
>>>is
>>>there a better way to store dates?  Thank you for all your help.  I'm
>>>very
>>>new to all this.
>>>  
>>
>>See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions for info 
>>on date and time functions.
>>
>>I would suggest storing dates as floating point julian day numbers.
>>
>>HTH
>>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] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"

[John Stanton]

Zbigniew Baniewski wrote:
> I'm sorry to confirm the problem described at http://tinyurl.com/29wc8x
> 
> #v+
>   $ tclsh8.5 
>   % package require sqlite3 
>   couldn't load file "/usr/lib/sqlite3/libtclsqlite3.so.0": 
>   /usr/lib/sqlite3/libtclsqlite3.so.0: undefined symbol: sqlite3StrICmp
> #v-
> 
> Does there exist any cure?

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

Re: [sqlite] Clear screen in command line interface

[John Stanton]

Fred J. Stephens wrote:
> Would it be possible to incorporate a clear screen command, like "clear" 
> in BASH? This would be handy to keep the view in the command line 
> interface uncluttered.
> Maybe ".clear"?
You have the source and it uses curses so the addition should be fairly 
simple.

> ___
> 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] BCD representation of floats

[John Stanton]

We actually added this type of capability to Sqlite (actually fixed 
point display format numbers), but it may be unnecessary in your case. 
Instead of representing integers as BCD how about using the 64 bit 
Sqlite integers?  You may have sufficient precision.  The COBOL-style 
COMP3 integers are pretty much obsolete these days.

Liam Healy wrote:
> I am porting a numerical application from Oracle to SQLite.  For the
> most part, I have been successful, but there are slight disagreements
> in the floating point number results.  I have traced this back and
> found a problem.  According to
> http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle
> by default stores floats as binary-coded decimal (BCD), and not
> IEEE754 binary.  SQLite on the other hand does
> http://www.sqlite.org/datatype3.html: "REAL. The value is a floating
> point value, stored as an 8-byte IEEE floating point number."  For the
> results of the application, it makes no difference how the numbers are
> stored -- the differences in the 15th significant figure are
> irrelevant.  However, I would like to insure that there no
> disagreements in the way the two applications operate (other than the
> storage of floating point numbers), and for that I temporarily need
> exact agreement on input numbers.  I cannot change the Oracle
> application, so I'm wondering if there's a wrapper or something I can
> put around sqlite calls (or better, a mode that I can put sqlite in)
> that will reproduce exactly the BCD format of Oracle.
> 
> Thanks for any guidance.
> 
> Liam
> ___
> 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] paged query in sqlite

[John Stanton]

Do a SELECT, read 100 rows, then read another 100.  Your program 
controls it.

Shailesh Madhukar Birari wrote:
> Does Sqlite support Paged query? what I mean is on doing select* it should
> return me first, say 100, rows and then subsequent calls should return me
> successive rows.
> Is this supported in sqlite:? If yes, what are the interfaces?
> If not, is there an easy way to add this functionality using existing
> interfaces?
> 
> regards.
> Shailesh
> ___
> 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] performance under load

[John Stanton]

Sam Carleton wrote:
> On Mon, Mar 24, 2008 at 4:11 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
> 
>>  Try the math over:
>>
>>  24 hours * 60 minutes * 60 seconds = 86,400 seconds/day.
>>
>>  100,000 hits/day  /  86,400 seconds/day = 1.1574 hits/sec (on average).
>>
>>  Of course, this is on average.  A real website getting "100K hits per
>>  day" would generally expect 10x to 15x this rate during heavy times.
>>
>>  That's still not ~69 hits/sec, however.
> 
> 
> yep, I was off by a factor of 60.
> 
> 
>> > As I am developing the software, is there anything I need to keep in
>> > mind to help optimize the database usage to achieve the million hits a
>> > day the "Appropriate Uses for SQLite"
>>
>>  The big thing is that locks are exclusive across the whole database,
>>  so an application needs to get it, do what it needs, and get out.
>>  A clean database design and proper use of indexes on critical columns
>>  (for queries) as well as transactions (for updates) are most likely the
>>  first places to look.
> 
> 
> So can SQLite can keep up with the load of 40 to 50 viewing stations
> going at one time?  Or am I better off looking at other options?  If I
> should look at other options, what would you recommend?  The
> application is used at events, each event having it's own set of
> images and data.  What I really like about SQLite is that it is file
> based and I can keep the data for each event with the images for that
> event, allowing my customer an easy way to back things up at the end
> of the event.  I also like the cost, too;)
> 
> Sam
This is a bit like asking "how long is a piece of string".  If you are 
doing short transactions and have a well conceived database and 
efficient server side software (using things like PHP and CGI are 
detrimental) then it should work.  If you do a lot of inserts and 
updates or have SQL which results in row searches then it will not work.
> ___
> 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] performance under load

[John Stanton]

My performance measurements using a modest server and a more efficient 
www server than Apache gives an Sqlite capability of 30-50 hits per 
second using simple SQL selects.  Think of that as a reasonable upper limit.

Jay A. Kreibich wrote:
> On Mon, Mar 24, 2008 at 03:55:12PM -0400, Sam Carleton scratched on the wall:
> 
> 
>>>From the web site's "Appropriate Uses for SQLite" it says that "any
>>site that gets fewer than 100K hits/day should work fine with SQLite".
>> I did the math and that looks to be around 69 hits a second.
> 
> 
>   Try the math over:
> 
>   24 hours * 60 minutes * 60 seconds = 86,400 seconds/day.
> 
>   100,000 hits/day  /  86,400 seconds/day = 1.1574 hits/sec (on average).
> 
>   Of course, this is on average.  A real website getting "100K hits per
>   day" would generally expect 10x to 15x this rate during heavy times.
> 
>   That's still not ~69 hits/sec, however.
> 
> 
>>As I am developing the software, is there anything I need to keep in
>>mind to help optimize the database usage to achieve the million hits a
>>day the "Appropriate Uses for SQLite"
> 
> 
>   The big thing is that locks are exclusive across the whole database,
>   so an application needs to get it, do what it needs, and get out.
>   A clean database design and proper use of indexes on critical columns
>   (for queries) as well as transactions (for updates) are most likely the
>   first places to look.
> 
>-j
> 

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

Re: [sqlite] Command to create a database in sqlite

[John Stanton]

sqlite3_open.

Mozaharul Haque wrote:
> Hi,
> 
> A silly asking. I found the command to create a table but what about to
> create a database?
> 
> The table would be stored in the sqlite_master table.
> 
> And how do I refer (full path) to the database  using application like
> Basic4ppc 6.05.
> 
> 
> Please help.
> 
> 
> regards,
> 
> Mozaharul Haque
> 
> ___
> 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] Multiple Row Updates

[John Stanton]

Use this sequence -

   sqlite3_prepare_v2
 while not finished
   sqlite3_bind_xxx
   sqlite3_step
 until SQLITE_DONE
   sqlite3_reset
 repeat
   sqlite3_finalize

The sqlite3_reset readies the compiled statement for binding with 
another value.

Derek Developer wrote:
> Just wanted to check that there is no "reset" statement that I can use with 
> UPDATE's.
> 
> My understanding is that for multiple Row updates the complete sequence needs 
> to be executed for each row ie:
> 
> "UPDATE mytable SET  ' name=?, street=?, Phone=? WHERE ROWID=333"
> Prepare 
> Bind the data
> step
> finalize
> 
> then the next row must complete the same sequence ie:
> "UPDATE mytable SET  ' name=?, street=?, Phone=? WHERE ROWID=333"
>  Prepare 
>  Bind the data
>  step
>  finalize
> 
> Obviously I can do all these within a BEGIN/END TRANSACTION
> 
> but was just wondering if there is any way to use the reset statement and 
> update the ROWID?
> 
> This would save a lot of time for large numbers of rows.
> 
> Testing with Seinfeld data from Michael Owens excellent book:
> http://books.google.com/books?id=VsZ5bUh0XAkC=PA75=PA75=sqlite+seinfeld=web=u42Lep_3F7=A3whrQ0XJbW7DBQbPhyspKdHJuc=en
> Download:
> http://www.apress.com/book/downloadfile/2847
> 
> 
> (also would love to figure out how to respond to my own threads. I cannot 
> seem to find the link in the digests I am getting at my email account)
> 
> 
> 
>
> -
> Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it 
> now.
> ___
> 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] Tables dropped (mac)

[John Stanton]

What was your filename?

Janis Rough wrote:
> I created some tables at the sqlite prompt using the terminal window on a
> Mac.  I checked with .tables and .schema and they were created and I
> inserted data.  I closed the terminal window and went back later.  At the
> sqlite prompt there were no tables.  I tried .tables and .schema and
> nothing.
> So what happened to my tables?
> 
> Thanks,
> ___
> 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] Use of two columns for a key and query on first clm.

[John Stanton]

No, use ORDER BY

Neville Franks wrote:
> If I use two columns for a key (primary or separate index) and query
> just on the first column component will I always get back the first
> match in a set. For example.
> 
> -
> create table mytable ( clm1 text collate nocase, clm2 text collate nocase, 
> constraint mycs1 primary key( clm1, clm2 ) );
> 
> insert following:
> Clm1  Clm2
> abc   123
> abc   456
> abc   789
> def   123
> def   456
> def   789
> 
> select * from table where clm1='def';
> -
> 
> Will the returned row always be def - 123. ie. the first row for def?
> 
> I've looked at the query plan for this select and it does use the
> index if clm1 alone is in the query and it appears to match on the
> first row.
> 
> Also my tests indicate I do get back the first matching row. But I'd
> like confirmation if possible.
> 

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

Re: [sqlite] Preventing Master Journal Files

[John Stanton]

Looks like Sqlite is not the right storage tool for your application 
since you don't want transactional integrity.  How about something like 
Berkeley DB.

Ken wrote:
> Hello list,
> 
> How do I prevent the creation of a journal file and a master journal file? I 
> need to be able to do this dynamically not just a compile time flag.
> 
> The process does not need recovery as it is an all or none for some of the 
> DB's it creates. And is restarted from the beginning in the event of a crash.
> 
> Thanks,
> Ken
> 
> 
> ___
> 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] 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

Re: [sqlite] Value between changes

[John Stanton]

Store your timestamp in the Sqlite format as a floating point number and 
use the built-in date functions.

Bjørn T. Nøstdahl wrote:
>>Can you give an example of the output?  For example, do you want to
>>know the difference between A0001 and A0002 or between to records with
>>the same code?
> 
> 
> 20080314100030 A0001 (9min 30sec to next status change)
> 20080314101000 A0002 (7min to next status change)
> 20080314101700 A (3min to next status change)
> 20080314102000 A0002 (1min to next status change)
> 20080314102100 A
> 
> Expected output: (The total time within that status)
> A 300 (3min)
> A0001 930 (9min 30sec)
> A0002 800 (7min + 1min)
> 
> One problem is sadly that the date/time is stored as text, and this will 
> make the calculations even more difficult. 
> 
> ___
> 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] Rowid After Sorting

[John Stanton]

The rowid is the b-tree key for the row.  You cannot change it.

Mahalakshmi.m wrote:
> 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

Re: [sqlite] SQLite and (high) concurrency

[John Stanton]

It looks as if you are not syncing correctly.  Do you check for 
SQLITE_BUSY?  If you are using transactions are you certain that you COMMIT?

Are you using mutexes for synchronization or using the Sqlite BUSY checks?

Tore Austraatt wrote:
> Thanks, but I'm afraid this don't add up. 
> I have tested this in numerous examples. Concurrent INSERT's
> disappears into thin air, they leave no trace what so ever. 
> It seems very strange if some of you haven't experienced simular
> problems...?
> 
> John, PRAGMA sync = whatever does not remedy this either.
> Ken, locks are handled without any problem at all...
>  
> Tore.
> ___
> 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] Can't get concat operator to work

[John Stanton]

The problem appears to be in your function.  Post the code for it.

[EMAIL PROTECTED] wrote:
> 
>   I'm trying to get the concat operator to work with my user-defined
> function.  This works fine:
> 
>   SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM  Employees
> 
>   But this doesn't work:
> 
>   SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time)
> FROM  Sessions
> 
>   I get only the formatted date - missing the formatted time. 
> FORMAT_DATE is my own user-defined function that returns text data
> type.
> 
>   Can someone *please* check into this.  I must get this working.
> 
>   Thank you
> -brett
> 
>   
> 
> 
> This message was sent using IMP, the Internet Messaging Program.
> ___
> 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] Does an sqlite3_get_table() results set get updated.

[John Stanton]

Neville Franks wrote:
> Hi John,
> 
> Tuesday, March 11, 2008, 10:18:30 AM, you wrote:
> 
> JS> Neville Franks wrote:
> 
>>>Tuesday, March 11, 2008, 8:48:05 AM, you wrote:
>>>
>>>JAK> On Tue, Mar 11, 2008 at 08:37:27AM +1100, Neville Franks scratched on 
>>>the wall:
>>>
>>>
>If I use sqlite3_get_table() and update/insert/delete one of the tables
>in the original query, does the results set get updated. ie. If I
>iterate through the original results from sqlite3_get_table() will I
>see the changes, or do I need to run the sqlite3_get_table() query
>anew?
>
>If I do see the changes does this also apply to an sqlite3_get_table()
>query that was on a VIEW?
>>>
>>>
>>>JAK>   From :
>>>
>>>JAK> The sqlite3_get_table() interface is implemented as a wrapper around
>>>JAK> sqlite3_exec(). The sqlite3_get_table() routine does not have access
>>>JAK> to any internal data structures of SQLite. It uses only the public
>>>JAK> interface defined here. As a consequence, errors that occur in the
>>>JAK> wrapper layer outside of the internal sqlite3_exec() call are not
>>>JAK> reflected in subsequent calls to sqlite3_errcode() or
>>>JAK> sqlite3_errmsg().
>>>
>>>JAK>   In other words, "No."  You need to run it again.
>>>
>>>JAK>-j
>>>
>>>Jay,
>>>Thanks, I assumed that would be the case. Dynamically updating GUI's
>>>on SQL DB updates appears to be challenging.
>>>
> 
> JS> Are you using Windows and the WIN API?  If so you can just set up 
> JS> callbacks to do it.
> 
> I am using C++ and Windows. Do you mean use "triggers"?
> 
No.  If you have the data in some form of windows control like a 
listview when you get a notify messge telling you it is changed you can 
fire a callback to perform an Sqlite update.  It is a bit tedious to 
program.  It is quite a while since I wrote such a program so I can only 
give you a big picture.

By making the edit phase a transaction you can give the user the option 
of commiting changes or rolling back.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does an sqlite3_get_table() results set get updated.

[John Stanton]

Neville Franks wrote:
> Tuesday, March 11, 2008, 8:48:05 AM, you wrote:
> 
> JAK> On Tue, Mar 11, 2008 at 08:37:27AM +1100, Neville Franks scratched on 
> the wall:
> 
>>>If I use sqlite3_get_table() and update/insert/delete one of the tables
>>>in the original query, does the results set get updated. ie. If I
>>>iterate through the original results from sqlite3_get_table() will I
>>>see the changes, or do I need to run the sqlite3_get_table() query
>>>anew?
>>>
>>>If I do see the changes does this also apply to an sqlite3_get_table()
>>>query that was on a VIEW?
> 
> 
> JAK>   From :
> 
> JAK> The sqlite3_get_table() interface is implemented as a wrapper around
> JAK> sqlite3_exec(). The sqlite3_get_table() routine does not have access
> JAK> to any internal data structures of SQLite. It uses only the public
> JAK> interface defined here. As a consequence, errors that occur in the
> JAK> wrapper layer outside of the internal sqlite3_exec() call are not
> JAK> reflected in subsequent calls to sqlite3_errcode() or
> JAK> sqlite3_errmsg().
> 
> JAK>   In other words, "No."  You need to run it again.
> 
> JAK>-j
> 
> Jay,
> Thanks, I assumed that would be the case. Dynamically updating GUI's
> on SQL DB updates appears to be challenging.
> 
Are you using Windows and the WIN API?  If so you can just set up 
callbacks to do it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and (high) concurrency

[John Stanton]

If your synchronization logic is sound Sqlite will not ignore inserts.

Tore Austraatt wrote:
> I find sqlite3 as the perfect choice for our embedded application - if it 
> hasn't been for the
> issues concering concurrency.
> My questions are; 
>   1.  is the "default behaviour" from sqlite3 to simply ignore inserts if
> the concurrency gets to high? 
> 2. is there anything (C API) I can do to at least know that it does not do 
> an actual insert?
> (tried calling sqlite3_changes() but even that remains silent about 
> its results)
> 
> Thanks !
> Tore
> ___
> 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] group_concat for binary?

[John Stanton]

Why do you think a BLOB stores text more efficiently?  Do you compress it?

He Shiming wrote:
> Hi,
> 
> I would like to know if there is some workaround to use group_concat on 
> binary columns.
> 
> For performance consideration, I need to query two tables and accomplish 
> data retrieval in a single SELECT. There is this BLOB column, originally 
> designed as BLOB rather than text so that the storage is efficient. So can I 
> use group_concat to join binary columns? I don't know if the implementation 
> actually treats the output column as a string, or it will determine the 
> actual length, in which case, it might be possible.
> 
> What choices do I have? Is it a good idea to add another column that 
> contains the encoded text representation of the BLOB?
> 
> Thanks in advance,
> He Shiming 
> 
> ___
> 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] Beginners bad luck

[John Stanton]

Looks like you need to recompile PHP with the option.

Aharon (Rony) Shapira wrote:
> I tried using PDO as follows :
> // create a SQLite3 database file with PDO and return a database handle
> 
> try{
> 
> $dbHandle = new 
> PDO('sqlite:'.$_SERVER['DOCUMENT_ROOT'].'/../pdoTutorial.sqlite3');
> 
> }catch( PDOException $exception ){
> 
> die($exception->getMessage());
> 
> }
> 
> 
>  but got the following error message:
> 
> Fatal error: Class 'PDO' not found in /home/zbfckla/public_html/Ocx.php on 
> line 14
> 
> 
> 
> - Original Message - 
> From: "John Stanton" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database" 
> <sqlite-users@sqlite.org>
> Sent: Sunday, March 09, 2008 6:48 PM
> Subject: Re: [sqlite] Beginners bad luck
> 
> 
> 
>>PHP has Sqlite V2 embedded.  Use PDO to get Sqlite3.
>>
>>P Kishor wrote:
>>
>>>On 3/9/08, Aharon (Rony) Shapira <[EMAIL PROTECTED]> wrote:
>>>
>>>
>>>>Sorry. When I set $db and use $db the message I get is:
>>>>
>>>>Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is 
>>>>not a
>>>>database in /home/zbfckla/public_html/Ocx.php on line 49
>>>>Could not open database
>>>>
>>>
>>>
>>>again, no idea about PHP, but check once again if you can open the db
>>>ok from the command line. Also, confirm that the db is not on a
>>>network drive.
>>>
>>>If the db opens ok from the command line, it is some kind of PHP
>>>problem. Ask for help on the PHP forum unless a PHP user on this list
>>>can help. If the db does not open ok from the command line, the db is
>>>really fubar because of "unknown" reasons. Recreate the db and try
>>>again.
>>>
>>>Good luck.
>>>
>>>
>>>
>>>
>>>>
>>>>- Original Message -
>>>>From: "P Kishor" <[EMAIL PROTECTED]>
>>>>To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>>>>Sent: Sunday, March 09, 2008 5:02 PM
>>>>Subject: Re: [sqlite] Beginners bad luck
>>>>
>>>>
>>>>
>>>>>I don't know how PHP's db connector works but...
>>>>>
>>>>>On 3/9/08, Aharon (Rony) Shapira <[EMAIL PROTECTED]> wrote:
>>>>>
>>>>>> I created a simple table named "books" in "library.db".
>>>>>> After entering "select * from books" I saw the data that I put into 
>>>>>>it.
>>>>>>
>>>>>> When trying to access it through php:
>>>>>> $file = "library.db";
>>>>>> // open database file
>>>>>>
>>>>>> $handle = sqlite_open($db) or die("Could not open database");
>>>>>
>>>>>Is that a typo? You are setting $file, but trying to open $db.
>>>>>
>>>>>
>>>>>
>>>>>> I got the following:
>>>>>>
>>>>>> "Could not open database"
>>>>>>
>>>>>>
>>>>>>
>>>>>> When I tried the following:
>>>>>>
>>>>>> $file = "library.db";
>>>>>>
>>>>>> // create database object
>>>>>>
>>>>>> $db = new SQLiteDatabase($file) or die("Could not open database");
>>>>>>
>>>>>> I got the following:
>>>>>>
>>>>>> Fatal error: Uncaught exception 'SQLiteException' with message
>>>>>>'SQLiteDatabase::__construct() [>>>>>href='function.SQLiteDatabase---construct'>function.SQLiteDatabase---construct]:
>>>>>>file is encrypted or is not a database' in
>>>>>>/home/zbfckla/public_html/Ocx.php:17 Stack trace: #0
>>>>>>/home/zbfckla/public_html/Ocx.php(17):
>>>>>>SQLiteDatabase->__construct('library.db') #1 {main} thrown in
>>>>>>/home/zbfckla/public_html/Ocx.php on line 17
>>>>>>
>>>>>>
>>>>>>
>>>>>> Once again I apologies for these silly questions.
>>>>>>
>>>>>> Aharon
>>>>>> ___
>>>>>> sqlite-users mailing list
>>>>>> sqlite-users@sqlite.org
>>>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>>>
>>>>>
>>>>>
>>>>>--
>>>>>Puneet Kishor http://punkish.eidesis.org/
>>>>>Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>>>>>Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>>>>>___
>>>>>sqlite-users mailing list
>>>>>sqlite-users@sqlite.org
>>>>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>>
>>>
>>>
>>___
>>sqlite-users mailing list
>>sqlite-users@sqlite.org
>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 
> 
> ___
> 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] Beginners bad luck

[John Stanton]

I haven't tried to get it but I understand the Sqlite.org CVS server has 
it archived.  You should also be able to create the DB from PHP.  Sqlite 
open creates a DB if the file does not exist.

Aharon (Rony) Shapira wrote:
> Great. PHP has sqlite2 as you said. I could use it to create a db and add a 
> table. But when downloaded from the server to my pc couldn't get hold of it.
> I have a command-line program for accessing and modifying SQLite3 databases 
> which seems not to recognise it. How do I get one for sqlite2?
> Thanks a lot
> 
> - Original Message - 
> From: "John Stanton" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database" 
> <sqlite-users@sqlite.org>
> Sent: Sunday, March 09, 2008 6:48 PM
> Subject: Re: [sqlite] Beginners bad luck
> 
> 
> 
>>PHP has Sqlite V2 embedded.  Use PDO to get Sqlite3.
>>
>>P Kishor wrote:
>>
>>>On 3/9/08, Aharon (Rony) Shapira <[EMAIL PROTECTED]> wrote:
>>>
>>>
>>>>Sorry. When I set $db and use $db the message I get is:
>>>>
>>>>Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is 
>>>>not a
>>>>database in /home/zbfckla/public_html/Ocx.php on line 49
>>>>Could not open database
>>>>
>>>
>>>
>>>again, no idea about PHP, but check once again if you can open the db
>>>ok from the command line. Also, confirm that the db is not on a
>>>network drive.
>>>
>>>If the db opens ok from the command line, it is some kind of PHP
>>>problem. Ask for help on the PHP forum unless a PHP user on this list
>>>can help. If the db does not open ok from the command line, the db is
>>>really fubar because of "unknown" reasons. Recreate the db and try
>>>again.
>>>
>>>Good luck.
>>>
>>>
>>>
>>>
>>>>
>>>>- Original Message -
>>>>From: "P Kishor" <[EMAIL PROTECTED]>
>>>>To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>>>>Sent: Sunday, March 09, 2008 5:02 PM
>>>>Subject: Re: [sqlite] Beginners bad luck
>>>>
>>>>
>>>>
>>>>>I don't know how PHP's db connector works but...
>>>>>
>>>>>On 3/9/08, Aharon (Rony) Shapira <[EMAIL PROTECTED]> wrote:
>>>>>
>>>>>> I created a simple table named "books" in "library.db".
>>>>>> After entering "select * from books" I saw the data that I put into 
>>>>>>it.
>>>>>>
>>>>>> When trying to access it through php:
>>>>>> $file = "library.db";
>>>>>> // open database file
>>>>>>
>>>>>> $handle = sqlite_open($db) or die("Could not open database");
>>>>>
>>>>>Is that a typo? You are setting $file, but trying to open $db.
>>>>>
>>>>>
>>>>>
>>>>>> I got the following:
>>>>>>
>>>>>> "Could not open database"
>>>>>>
>>>>>>
>>>>>>
>>>>>> When I tried the following:
>>>>>>
>>>>>> $file = "library.db";
>>>>>>
>>>>>> // create database object
>>>>>>
>>>>>> $db = new SQLiteDatabase($file) or die("Could not open database");
>>>>>>
>>>>>> I got the following:
>>>>>>
>>>>>> Fatal error: Uncaught exception 'SQLiteException' with message
>>>>>>'SQLiteDatabase::__construct() [>>>>>href='function.SQLiteDatabase---construct'>function.SQLiteDatabase---construct]:
>>>>>>file is encrypted or is not a database' in
>>>>>>/home/zbfckla/public_html/Ocx.php:17 Stack trace: #0
>>>>>>/home/zbfckla/public_html/Ocx.php(17):
>>>>>>SQLiteDatabase->__construct('library.db') #1 {main} thrown in
>>>>>>/home/zbfckla/public_html/Ocx.php on line 17
>>>>>>
>>>>>>
>>>>>>
>>>>>> Once again I apologies for these silly questions.
>>>>>>
>>>>>> Aharon
>>>>>> ___
>>>>>> sqlite-users mailing list
>>>>>> sqlite-users@sqlite.org
>>>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>>>
>>>>>
>>>>>
>>>>>--
>>>>>Puneet Kishor http://punkish.eidesis.org/
>>>>>Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>>>>>Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>>>>>___
>>>>>sqlite-users mailing list
>>>>>sqlite-users@sqlite.org
>>>>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>>
>>>
>>>
>>___
>>sqlite-users mailing list
>>sqlite-users@sqlite.org
>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 
> 
> ___
> 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] Beginners bad luck

[John Stanton]

PHP has Sqlite V2 embedded.  Use PDO to get Sqlite3.

P Kishor wrote:
> On 3/9/08, Aharon (Rony) Shapira <[EMAIL PROTECTED]> wrote:
> 
>>Sorry. When I set $db and use $db the message I get is:
>>
>> Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a
>> database in /home/zbfckla/public_html/Ocx.php on line 49
>> Could not open database
>>
> 
> 
> again, no idea about PHP, but check once again if you can open the db
> ok from the command line. Also, confirm that the db is not on a
> network drive.
> 
> If the db opens ok from the command line, it is some kind of PHP
> problem. Ask for help on the PHP forum unless a PHP user on this list
> can help. If the db does not open ok from the command line, the db is
> really fubar because of "unknown" reasons. Recreate the db and try
> again.
> 
> Good luck.
> 
> 
> 
>>
>>
>> - Original Message -
>> From: "P Kishor" <[EMAIL PROTECTED]>
>> To: "General Discussion of SQLite Database" 
>> Sent: Sunday, March 09, 2008 5:02 PM
>> Subject: Re: [sqlite] Beginners bad luck
>>
>>
>> >I don't know how PHP's db connector works but...
>> >
>> > On 3/9/08, Aharon (Rony) Shapira <[EMAIL PROTECTED]> wrote:
>> >>
>> >>  I created a simple table named "books" in "library.db".
>> >>  After entering "select * from books" I saw the data that I put into it.
>> >>
>> >>  When trying to access it through php:
>> >>  $file = "library.db";
>> >>  // open database file
>> >>
>> >>  $handle = sqlite_open($db) or die("Could not open database");
>> >
>> > Is that a typo? You are setting $file, but trying to open $db.
>> >
>> >
>> >>
>> >>  I got the following:
>> >>
>> >>  "Could not open database"
>> >>
>> >>
>> >>
>> >>  When I tried the following:
>> >>
>> >>  $file = "library.db";
>> >>
>> >>  // create database object
>> >>
>> >>  $db = new SQLiteDatabase($file) or die("Could not open database");
>> >>
>> >>  I got the following:
>> >>
>> >>  Fatal error: Uncaught exception 'SQLiteException' with message
>> >> 'SQLiteDatabase::__construct() [> >> href='function.SQLiteDatabase---construct'>function.SQLiteDatabase---construct]:
>> >> file is encrypted or is not a database' in
>> >> /home/zbfckla/public_html/Ocx.php:17 Stack trace: #0
>> >> /home/zbfckla/public_html/Ocx.php(17):
>> >> SQLiteDatabase->__construct('library.db') #1 {main} thrown in
>> >> /home/zbfckla/public_html/Ocx.php on line 17
>> >>
>> >>
>> >>
>> >>  Once again I apologies for these silly questions.
>> >>
>> >>  Aharon
>> >>  ___
>> >>  sqlite-users mailing list
>> >>  sqlite-users@sqlite.org
>> >>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> > --
>> > Puneet Kishor http://punkish.eidesis.org/
>> > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>> > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> 
> 
> 

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

Re: [sqlite] [C] Linker Error

[John Stanton]

Looks like you need to include the shared library archive into your LIB 
list.

Severin Müller wrote:
> Hi folks
> 
> I tried to include sqlite3 in my current C Project. 
> 
> I downloaded the precompiled library sqlite-3.5.6.so and put it in my project.
> Then, i downloaded the sqlite source and added sqlite3.h to my project. 
> 
> Now, when i try to compiler, i get the following error message:
> 
> /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to 
> `dlsym'
> /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to 
> `dlerror'
> /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to 
> `dlopen'
> /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to 
> `dlclose'
> 
> I'm using the Ecplipse IDE with GCC on Linux. 
> 
> Does anyone know what i'm doing wrong? I noticed, that there is no *.a file 
> coming along with the precompiled library, Intentionally?
> 
> Many thanks in advance for your quick help!
> 
> Kind regards
> 
> Severin

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

Re: [sqlite] Odd problem with select on large table

[John Stanton]

For SQL it needs to be 'key5000' not "key3000".

James Kimble wrote:
> On the command line:
> 
> / > sqlite3 test.db `select name from PerfTest1 where name = "key5000"'
> 
> does work. I know this because if I query for "key500" I get back that 
> row.  It's not blanks either because if I
> do:
> 
>  where name link "key1%"
> 
> I only get rows prior to "key199". Very weird. I did try the other 
> alternatives (single quotes and piping into
> sqlite3 test.db) but got the same result.
> 
> I'll try changing the varchar to TEXT. See if that makes a difference
> ___
> 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] Odd problem with select on large table

[John Stanton]

An SQL literal uses single quotes

[EMAIL PROTECTED] wrote:
> I created a 40 column table with 10,000 rows as a test database
> for a reader and a writer process to bang on (performance proof).
> 
> The table is as so:
> 
> sqlite3 test.db 'create table PerfTest1 (name varchar(20),  value1 int,
> value2 int, value3 int, value4 int, value5 int, value6 int, value7 int,
> value8 int, value9 int, value10 int, value11 int, value12 int, value13
> int,
> value14 int, value15 int, value16 int, value17 int, value18 int, value19
> int, value20 int, value21 int, value22 int, value23 int, value24 int,
> value25 int,
> value26 int, value27 int, value28 int, value29 int, value30 int, value31
> int,
> value32 int, value33 int, value34 int, value35 int, value36 int, value37
> int,
> value38 int, value39 int)'
> 
> 
> The data is repetitive junk. Just: "key1", 1, 2, ,3 .  "key2", 1, 2,
> 3
> 
> What's driving me mad is that when I do a select from the command line
> like so:
> 
> sqlite3 test.db `select name from PerfTest1 where name = "key5000"'
> 
> 0 rows are returned. However if I do a simple:
> 
> sqlite3 test.db 'select name from PerfTest1'
> 
> and just let it go it prints all 1 rows!! Is this due to the type of
> query prepartion done from the command line interface? Maybe limits the
> size of something? That doesn't make a lot of sense either though
> because if I query the specific row I want it returns nothing.
> 
> 
> ___
> 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] Newbie question re using SQLite in basic client/serversituation

[John Stanton]

Sqlite is an embedded database, not a server.  If you want to use it for 
multiple clinets you really need to add a server which embeds Sqlite. 
When you do that it works well and will handle many remote clients.

If you have multiple users on one machine you can use the inbuilt Sqlite 
synchronization.

[EMAIL PROTECTED] wrote:
> Not sure on the locking issue, but some of it might have to do with
> whether the database file is being provided via a NFS or a Windows
> Network Filesystem. Sometimes file-level locking is not real robust on
> networked file systems. If you are using some kind of ODBC/ADO provider
> then the details for that would be what counts.
> 
> Not sure what cocoa is, but it sounds fairly modern so you are probably
> able to go through an ODBC connection or provider of some sort, in which
> case youwould just configure the SQLITE connectivity layer you are
> using. As far as SQLITE itself goes, there is no database server to 'log
> into', all you are doing is providing a file name ( the file for which
> has to be visible to the client program ) to the connect function. If
> you want a more usual client/server situation, youd have to use ODBC or
> ADO, else you'd have to write it yourself.
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Brown
> Sent: Friday, March 07, 2008 4:06 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Newbie question re using SQLite in basic
> client/serversituation
> 
> Hi all
> 
> I'm looking at switching the database that I use for my XCode/Cocoa
> application from MySQL to SQLite. (I'm getting tired of all the
> incompatibilities with new versions of MySQL). 
> 
> But I need to be able to use it as a server with at most 10 clients that
> occasionally use the system. I noticed in the documentation that it said
> the following "so, the file locking logic of many network filesystems
> implementation contains bugs (on both Unix and windows). If file locking
> does not work like it should, it might be possible for two or more
> client programs to modify the same part of the same database at the same
> time, resulting in database corruption."
> 
>>From users' experience, is SQLite likely to be safe with so few clients
> on a local network. 
> 
> Also how do I login to the SQLite database from a client app?
> 
> Cheers
> Jeff
> 
> 
> 
> 
>   Get the name you always wanted with the new y7mail email address.
> www.yahoo7.com.au/y7mail
> 
> 
> ___
> 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] How To concatenate two fields in one

[John Stanton]

Use the standard SQL || operator for concatenate.  The + is not SQL and 
specific to SQl Server.

Alessio Forconi wrote:
> Hello everyone,
> 
> This is my first message.
> 
> I have a table called Students:
> 
> IDStudent char(10) PRIMARY KEY, NOT NULL
> Name char (30) NOT NULL
> Surname char(30) NOT NULL
> 
> 
> What I would like to achieve is the same as writing in SQL Server
> 
> SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students
> 
> 
> In SQL Server  the results is:
> IDStudentNominative
> MICA-00Michele - Calzolani
> 
> Unfortunately in SQLite I get this:
> DStudentNominative
> MICA-00   0.0
> 
> I do not understand where the error is, someone can help me?
> 
> I apologize for my bad English
> 
> 
> 
> ___
> 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] DATETIME data type

[John Stanton]

Try using the Sqlite date functions.

Yong Zhao wrote:
> It seems that sqlite3 does not support DATETIME data type.
> 
> If I have the following data in table t1, how do I select people who is
> older than certain date?
> 
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
> 
> select * from t1 where dob < '3/24/1950';
> 
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Prepare Statement

[John Stanton]

You misunderstand binding.  You use it like this -

   sql - "SELECT name FROM customers WHERE cust_id = ?";
   this_cust_id - "CUST1275";

   sqlite3_prepare_v2(...);

   sqlite3)bind_text(..., 1, this_cust_id, ...);

You bind a value to the data represented by the ?.  Then you reuse the 
compiled SQL by successively binding data values to it.

If you are not re-using the compiled SQL you do not use bind.

Mahalakshmi.m wrote:
>  
> 
> Hi,
> 
>  
> 
> My table looks like:
> 
>  
> 
> IdName
> 
> 1 1aaa
> 
> 2 01345
> 
> 3 1asdf
> 
>  
> 
>  
> 
> I want the statement to be like:
> 
> "SELECT id, Name FROM MUSIC WHERE Name >= '1a'  LIMIT 1;"
> 
> But using prepare I could not able to get the desired statements.
> 
> I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
> I want to bind it as 'a'.
> 
>  
> 
> My Prepare statement is as follows:
> 
>  
> 
> Unsigned char u8_ClassificationCode=1;
> 
> Unsigned short u16_Input=0x0061;
> 
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
> Name >= '%d%c'  LIMIT 1;",-1,_SearchPrepareStmt,0)!= SQLITE_OK) 
> 
> {
> 
> return SQLITE_DB_ERROR;
> 
> }
> 
> else
> 
> {
> 
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
> 
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
> *)u16_Input,-1,SQLITE_STATIC);
> 
> }
> 
>  
> 
> For the above the return status of sqlite3_prepare is success  but not
> properly binded.
> 
>  
> 
> Please help me 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

Re: [sqlite] Question on Blobs

[John Stanton]

Just get a pointer to the data in the file and the number of bytes and 
use the sqlite API call to transfer it into the DB.  You can get the 
pointer by either reading the file into local memory or by mmap'ing it. 
  Also look at the API calls which let you process a blob in chunks.

A BLOB is called a Binary Large OBject because it accepts anything.

Fred J. Stephens wrote:
> Mike McGonagle wrote:
>> Hello all,
>> I was hoping that someone might share some tips on working with Blobs? 
> I am also curious about this.
> For instance, how can I store a file in a table?
> Not read the file and store the text, but the binary file itself?
> Thanks.
> ___
> 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] Question on Blobs

[John Stanton]

Rather than doing malloc you can mmap the file and then copy it into the 
blob.

Peter A. Friend wrote:
> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
> 
>> Hello all,
>> I was hoping that someone might share some tips on working with  
>> Blobs? I
>> would like to be able to store some images and sound files in a  
>> database,
>> but never having dealt with them, I am kind of at a loss for some  
>> examples.
>> I have looked on the web, and there are few examples that were of use.
> 
> Well, I wrote a quick and dirty program for stuffing image files into  
> a database. You just provide a directory and it stats() each file,  
> allocates enough space for the image data, then loads it from disk.  
> Sql statement is something like:
> 
> char* sql = "insert into i (name, data) values (?, ?);";
> 
> Of course if your images are huge this method coud be problematic. I  
> believe SQLite supports an incremental way to do this but I haven't  
> looked at those calls yet.
> 
> while ( (dentry = readdir(dir)) != '\0') {
>if (dentry->d_name[0] == '.')
>   continue;
> 
>if (fd != -1) {
>   close(fd);
>   fd = -1;
>}
> 
>if (data != '\0') {
>   free(data);
>   data = '\0';
>}
> 
>snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
>stat(fname, );
> 
>if ( (data = malloc(sb.st_size)) == '\0') {
>   fprintf(stderr, "malloc() failed\n");
>   sqlite3_finalize(stmt);
>   sqlite3_close(db);
>   exit(1);
>}
> 
>if ( (fd = open(fname, O_RDONLY, )) == -1) {
>   fprintf(stderr, "open() failed\n");
>   sqlite3_finalize(stmt);
>   sqlite3_close(db);
>   exit(1);
>}
> 
>if ( (retval = read(fd, data, sb.st_size)) == -1) {
>   fprintf(stderr, "read() failed\n");
>   sqlite3_finalize(stmt);
>   sqlite3_close(db);
>   exit(1);
>}
> 
>if (retval != sb.st_size) {
>   fprintf(stderr, "read() failed\n");
>   sqlite3_finalize(stmt);
>   sqlite3_close(db);
>   exit(1);
>}
> 
>rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
>   SQLITE_STATIC);
> 
>if (rc != SQLITE_OK) {
>   fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg 
> (db));
>   sqlite3_finalize(stmt);
>   sqlite3_close(db);
>   exit(1);
>}
> 
>rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
> 
>if (rc != SQLITE_OK) {
>   fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg 
> (db));
>   sqlite3_finalize(stmt);
>   sqlite3_close(db);
>   exit(1);
>}
> 
>rc = sqlite3_step(stmt);
> 
>if (rc != SQLITE_DONE) {
>   fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
>   sqlite3_finalize(stmt);
>   sqlite3_close(db);
>   exit(1);
>}
> 
>sqlite3_reset(stmt);
> }
> 
> ___
> 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] IF...THEN constructs

[John Stanton]

Sqlite does not have a built in procedural language like PL/SQL.  For 
certain applications we just added Javascript to cover that requirement. 
  It was straightforward using Spidermonkey and had the advantage of 
being the same language used by the AJAX applications backended by 
Sqlite so applications programmers had one less skill to master.

Jason Salas wrote:
> Hi Igor,
> 
> Thanks for the insight.  I'm used to doing stored procedures for web 
> apps, which conditionally execute statements based on state and/or the 
> presence of variables.  Consider this construct, which I built recently 
> to populate a table with URL for a web spider bot I built:
> 
> CREATE PROCEDURE AddLinkInfo
> (
> @ProviderName VARCHAR(200),
> @LinkPath VARCHAR(200),
> @LinkText VARCHAR(200)
> )
> AS
> DECLARE @ProviderIDINT
> 
> -- only store a link if it isn't already listed in the database
> IF NOT EXISTS(SELECT LinkPath FROM SpiderBot WHERE LinkPath = @LinkPath)
> BEGIN
> -- is this a known provider?  if not, add it into the DB and 
> then assign it's new ID
> IF EXISTS(SELECT ContentProviderID FROM 
> SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
> BEGIN
> SET @ProviderID= (SELECT ContentProviderID FROM 
> SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)   
> END
> ELSE
> BEGIN
> INSERT INTO SpiderBot_ContentProviders VALUES 
> (@ProviderName)
> SET @ProviderID = @@IDENTITY
> END
>
>   -- do the main content insertion
> INSERT INTO SpiderBot (ContentProviderID,LinkPath,LinkText) 
> VALUES (@ProviderID,@LinkPath,@LinkText)   
> END
> GO
> 
> How would I got about re-writing something like this in SQLite?  Thanks 
> again for your help.
> 
> 
> Igor Tandetnik wrote:
>> "Jason Salas" <[EMAIL PROTECTED]> wrote in message
>> news:[EMAIL PROTECTED]
>>   
>>> I'm used to doing lengthy T-SQL programming in SQL Server, so this is
>>> kinda new to me.  How does one replicate doing IF...THEN conditional
>>> blocks in SQLite 3?
>>> 
>> One typically doesn't. Instead, one implements complex logic in one's 
>> application that hosts SQLite.
>>
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem to compile 3.5.6.

[John Stanton]

Looks like you have compiled Sqlite to use readline but don't have the 
link library.

David Michal wrote:
> Hello,
> 
> I have problem to compile it on Slackware 11 x86_64 
> 
>  
> 
> gcc -g -O2 -I. -I../sqlite-3.5.6/src -DNDEBUG -DSQLITE_THREADSAFE=1
> -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1
> -DHAVE_READLINE=1 -I/usr/include/readline -o .libs/sqlite3
> ../sqlite-3.5.6/src/shell.c  ./.libs/libsqlite3.so -lpthread
> 
> /tmp/cc9O4O7b.o: In function `process_input':
> 
> ../sqlite-3.5.6/src/shell.c:288: undefined reference to `readline'
> 
> ../sqlite-3.5.6/src/shell.c:290: undefined reference to `add_history'
> 
> /tmp/cc9O4O7b.o: In function `main':
> 
> ../sqlite-3.5.6/src/shell.c:2067: undefined reference to `read_history'
> 
> ../sqlite-3.5.6/src/shell.c:2071: undefined reference to
> `stifle_history'
> 
> ../sqlite-3.5.6/src/shell.c:2072: undefined reference to `write_history'
> 
> collect2: ld returned 1 exit status
> 
> make: *** [sqlite3] Error 1
> 
>  
> 
> any idea of what I am missing on my system?
> 
>  
> 
> Thanks,
> 
> David
> 
>  
> 
> ___
> 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 long time to index this table

[John Stanton]

I think that you have hit a situation where the sort and index build 
algorithm in Sqlite is a problem and gets limited by disk head seeking.
Others know more about this situation but I don't think there is much 
you can do to improve the speed.

Lars Aronsson wrote:
> I have a database table with 68 million rows and 4 columns, of 
> which 3 are integers and one is a short text string.  I'm 
> now trying to create an index on one of the integer columns, that 
> I forgot to create before I populated the table.  But it takes for 
> ever and doesn't seem to stop.
> 
> I estimate that each row takes 20 bytes, so the 68 million rows 
> should be a little over a gigabyte.  Together with some other 
> data, the database file is 3.1 gigabytes.  You can download a 
> compressed version (638 MB using bzip2) from
> 
> http://mirabell.runeberg.lysator.liu.se/sv-counts-20080219.db.bz2
> 
> The schema (767 bytes) is available at
> 
> http://mirabell.runeberg.lysator.liu.se/sv-counts-20080219.schema
> 
> The operation I'm trying is
> 
>   create index counts_2 on counts(name);
> 
> I'm using SQLite 3.4.2, which is the standard package in Ubuntu 
> Linux 7.10.  I'm running this on an otherwise idle Intel Core 2 
> Duo CPU with 4 GB of RAM.  The operation takes several hours and 
> still running.  Performance graphs indicate lots of iowait, with 
> many thousand blocks being written to disk each second.  But very 
> little CPU power is being used.  Is that reasonable?
> 
> Do I need to use PRAGMA cache_size?
> 
> Is there a good way to find out what sqlite3 is doing?  I know the 
> "explain" command lists the byte code, but can it predict how long 
> an operation will take?
> 
> 

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

Re: [sqlite] Reducing size of timestamps

[John Stanton]

Use the Sqlite method, a floating point number.  Inbuilt functions 
support that technique.

Rich Rattanni wrote:
> All:
> 
> I was wondering if there was any way to reduce the 'cost' of storing a
> timestamp on entries in a SQLite database.  I performed a hexdump of
> the file and it showed me the timestamp is stored as a 19-byte ASCII
> string.  One quick thing I thought of was to store the unix timestamp
> in each field, then when I wanted an actual date use
> datetime(mytimeField,'unixepoch','localtime') to convert it back.
> This would save me 9 bytes per record, but I would (greedily) like to
> save more... any thoughts?
> 
> --
> TIA,
> Richard Rattanni
> ___
> 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] Optimization Question - multithread prepare ?

[John Stanton]

Mark Gilbert wrote:
>>  > - We don't currently prepare SQL statements in advance, would this
>>>  technique benefit from somehow preparing statements *before* that
>>>  thread gets the lock on the database ?  Can we have multiple threads
>>>  using the SAME database connection preparing SQL Queries at the same
>>>  time (so long as only one is RUNNING the query at once ?).
>> With the latest sqlite, I believe you can even have multiple threads 
>> access the same database connection. In this case, access will still 
>> be serialized.
>> However, you can also have each thread create it's own database 
>> connection, then access the database concurrently (well, with 
>> limitations - there can always only be one writer thread). In this
>> case you can actually execute several statements in parallel on
>> different threads (i.e. different database connections).
> 
> In fact much of our access is read-only, so being able to work 
> multiple concurrent connections sounds perfect.
> 
> Could you confirm that we can open and execute additional read only 
> connections to the database whilst we already have a Transaction open 
> for our write thread ?
> 
> - We BEGIN the transaction on the write thread, then leave that in 
> place and only COMIT the transactions once per  minute to coincide 
> with our flush.Can we open and close extra read connections to 
> the database on other threads whilst the write transaction is still 
> active (but idle) ?
> 
> Thanks for your insight
> 
> Cheers
> 
> Mark
>
First a performance suggestion.  You may be able to partition your data 
and users into multiple databases.  That would add concurrency.

Sqlite transactions have an intermediate locking mode which maintains 
read access right up to the point of the journal being committed.  That 
can enhance your concurrency.

> ___
> 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] which is faster, PHP or SQLite?

[John Stanton]

Sqlite uses cacheing.  I would suggest not storing large amounts of data 
in PHP arrays.  It is buffer shadowing.  Ideally with Sqlite you would 
use a cursor (the sqlite3_step logic) and pick up rows as you need them 
from the Sqlite cache.

Digging a string of holes and filling them in is a tedious way to move a 
hole in the ground

Zbigniew Baniewski wrote:
> On Mon, Feb 18, 2008 at 08:33:49AM -0800, Scott Baker wrote:
> 
>> The less database hits you have to do, the faster your code will be. 
>> Getting all the data into a PHP data structure should be the way to go.
> 
> But, if one really is "loading all the data into memory at once" (just
> "SELECT * FROM xyz") - where are, actually, any benefits from using SQL
> database engine?
> 
> Using plain file you can have about the same:  open/read_all/close... done.

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

Re: [sqlite] locking problem

[John Stanton]

Do you have posix-locks enabled with your glusterfs?  If you have use 
regular Sqlite locking logic and hope that the locks work properly.

Lanyi Zoltan wrote:
> Hello!
> 
> I have shared disc system with glusterfs.
> When i read or write on this disc system then i get error: database is locked
> But i not get error if i'd like read or write another program. (php write to 
> text file, etc.)
> 
> I have sqlite3.
> 
> Somebody help me?
> 
> Thanks,
> Zoltan
> 
> 
> A szexjátékoktól a routergyorsításig  a legolvasottabb tartalmak!
> http://ad.adverticum.net/b/cl,1,6022,247493,293836/click.prm
> 
>  
> 
> ___
> 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] Updatable views

[John Stanton]

[EMAIL PROTECTED] wrote:
> John Stanton <[EMAIL PROTECTED]> wrote:
>> That ia a nice idea.  To have a pragma which specied the dialect.  There 
>> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
>>   It would give tighter control over hard to track annoying minor syntax 
>> errors.
>>
> 
> And, it would multiple exponentially the number of test cases
> we have to write and maintain in order to adequately test the
> parser ;-)
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
A practical limitation.  Perhaps a simpler to administer approach would 
be an auxiliary program like lint to decouple the maintenance of the 
dialects from the ongoing enhancement of Sqlite proper.

> ___
> 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] Updatable views

[John Stanton]

That ia a nice idea.  To have a pragma which specied the dialect.  There 
could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
  It would give tighter control over hard to track annoying minor syntax 
errors.

Dennis Cote wrote:
> BareFeet wrote:
>> No, I have no MS legacy or habits. I use the  
>> square brackets for identifiers because I find that using double  
>> quotes doesn't catch errors. If I say select "column name that does  
>> not exist" I get a string back. But if I use square brackets SQLite  
>> gives me an error that the column doesn't exist, which is far more  
>> useful, especially when embedded in some function.
>>
> 
> Tom,
> 
> Yes I'm aware of that issue. It's an unfortunate side effect of SQLite's 
> mySQL compatibility extension that supports double quotes for string 
> literals. It's sad that because of this extension, you need to use the 
> MS compatible extension for identifiers so that you get useful error 
> messages.
> 
> It would be nice if SQLite added a pragma that could be set to disable 
> the non-standard extensions. It would have to default off for backwards 
> compatibility of course. This would allow users to use only the SQL 
> standard quoting rules, and still get proper error messages if they make 
> a typographical error.
> 
> 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] INSERTing records in two tables?

[John Stanton]

last_row_id is not a function.  It is a predefined value.

Gilles wrote:
> On Sat, 9 Feb 2008 08:41:56 -0500, "Igor Tandetnik"
> <[EMAIL PROTECTED]> wrote:
>> INSERT INTO phones (tel,id_customers) VALUES ('1234567',last_row_id());
> 
> Thanks, but I'm getting an error (FWIW, I'm using 3.5.4):
> 
> 
> sqlite> BEGIN;INSERT INTO customers (id,name) VALUES (NULL,'John
> Doe');INSERT INTO phones (tel,id_customers) VALUES
> ('1234567',last_row_id());COMMIT;
> SQL error: no such function: last_row_id
> 
> 
> Besides, I need to get the last ROW_ID of table "customers", not the
> one from table "phones".
> 
> ___
> 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 to select Strict Affinity or No Affinity modes?Attention: DRH

[John Stanton]

I found that it was a fairly simple change to Sqlite to stop it changing 
formats and causing grief.  Out of the box it merges nicely with 
scripting environments like Javascript, Python and TCL but can be a pain 
in other places.  Fortunately the changes needed where format changes 
are detrimental are tiny.

Fowler, Jeff wrote:
> I agree. After many years with SQL Server and Oracle (but new to
> SQLite), the concept of storing different datatypes within the same
> field is something I've had difficulty grasping. I'm not saying it's a
> bad thing, but from a business perspective I can't think of a situation
> where we would not want strict affinity. So if it becomes an option
> we'll use it throughout our application.
> 
> - Jeff
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff
> Sent: Friday, February 08, 2008 11:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to select Strict Affinity or No Affinity
> modes?Attention: DRH
> 
> I would like to have strict affinity mode too.  In our schemas we use
> check constraints to enforce strict affinity.  Unless you're working in
> a dynamic typed environment, I can't imagine why you would want to have
> inconsistent data within a single database field.  Also for consistency
> with (every?) other database engine out there, a strict affinity mode
> would be good.
> Strict affinity will also benefit all wrapper writers who write wrappers
> following a framework that assumes strict field typing (which I think is
> pretty much all of them since all other db's have strongly typed
> fields).
> 
> Thanks,
> 
> Sam
> 
> 
> On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote:
> 
>> I second the strict affinity mode as an optional feature, for the same
> 
>> reasons as Lee.
>>
>>A while back I ran into a problem while using the bit and feature 
>> of sqlite and got unexpected results because sqlite changed the type 
>> from a 64bit integer into a real. (I think)... In this case it would 
>> have been simpler to debug, if there had been a type conversion
> warning or a failure.
>> Regards,
>> Ken
>>
>>
> ___
> 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] Version 3.2.2

[John Stanton]

Literals in SQL use single quotes.

Walt wrote:
> I have a table 'listings' with fields 'listnum', 'price' and 'buyer' etc.
> 
> executing the following sql
> 
> UPDATE listings SET buyer = "Price" WHERE listnum = 12345
> 
> results in the 'buyer' field being set to the contents of the 'price' field 
> instead of setting the field 'buyer' to "Price".
> 
> Need HELP
> 
> Walt Mc Whirter
> ___
> 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] Mailing List Changes

[John Stanton]

[EMAIL PROTECTED] wrote:
> Doug Currie <[EMAIL PROTECTED]> wrote:
>>> Please set the list so default reply is to the list.
>> http://www.unicom.com/pw/reply-to-harmful.html
>>
> 
> One finds various screeds such as the one Doug references
> above.  And on the configuration screen for GNU mailman,
> it "strongly recommends" that replys be to the author and
> not to the list.
> 
> And yet nearly everyone I know loaths that behavior.  The
> overwhelming majority of users prefer mailing list replies
> to go back to the mailing list *only*.
> 
> I think we have things configured now so that replies go
> back to the list instead of the to original author.  There
> are likely other settings that will need to be adjusted as
> we move forward.  Please let me know if you see anything
> unusual.
> 
> In a semi-related rant: Setting up a new mailing list is
> *way* harder than it needs to be.  Way, Way harder.  In order
> to go from ezmlm to GNU mailman, we had to prototype the
> setup on a separate machine, then spend a day debugging
> the setup after transferring it to the production machine.
> A day.  For a mailing list.  And this is with mailing list
> software that is suppose to be *easy* to configure.  I
> tremble to thing what the difficult-to-configure software
> must be like.
> 
> To tie this back to the original question, when people who
> write mail handling and mailing list software get their
> programs to the point where I can set up a new mail system
> and a new mailing list manager on a system in 15 minutes or
> less with reasonable assurance that I have not opened major
> security holes in the system, then, perhaps, I will be in a
> better mood to listen to their polemics on Reply-To field 
> munging.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
Some very apt comments.  Having been similarly irritated by the mindless 
complexity of setting up mailman to manage a simple list I empathize. 
The ease and simplicity of installing Sqlite is in stark comparison and 
no accident but the reward of a lot of discipline in paring away 
complexity, filtering out ornamentation and responding thoughtfully to 
user's experiences and suggestions.
  JS
There are no victims, only volunteers.
> 
> ___
> 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] Cache for SQLite

[John Stanton]

Alexander Batyrshin wrote:

 Hello John,
Right now i am using apache + fcgid (fast-cgi).
I will try to keep database handler open. But i need to implement it,
because i am using now more than 200 databases.

That means keeping open 200 connections if you want to maximize 
performance by exploiting cacheing.



On Jan 24, 2008 9:38 PM, John Stanton <[EMAIL PROTECTED]> wrote:

Using Apache is the problem.  The connections are not persistent so
caching is destroyed.  It sounds like you are using CGI, and that makes
it more so.  Somevariant like fastcgi (?) might give you what you look for.


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




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

Re: [sqlite] Cache for SQLite

[John Stanton]

Using Apache is the problem.  The connections are not persistent so 
caching is destroyed.  It sounds like you are using CGI, and that makes 
it more so.  Somevariant like fastcgi (?) might give you what you look for.


Clark Christensen wrote:

I don't think you're going to get the kind of caching you want using Perl and a 
web server (Apache, right?).  There's just no persistence across processes, no 
shared memory, no database connections.

Now, Apache's mod_perl and some associated modules could get you all that and 
more.  For me, anyway, it requires a big adjustment in the way you build your 
apps if you want to take advantage of the shared $dbh, shared variables, and 
caching.  For me, the investment isn't quite worth the benefit.

 -Clark

- Original Message 
From: Alexander Batyrshin <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, January 24, 2008 7:19:47 AM
Subject: Re: [sqlite] Cache for SQLite


On 
Jan 
24, 
2008 
4:03 
PM, 
Doug 
<[EMAIL PROTECTED]> 
wrote:
I 
don't 
know 
of 
a 
daemon, 
but 
based 
on 
someone 
else's 
post 
where 
they
described 
keeping 
a 
pool 
of 
sqlite3* 
handles 
to 
the 
database, 
and 
always
reusing 
the 
most 
recently 
used 
handle 
first 
(so 
that 
the 
SQLite 
page 
cache
is 
most 
likely 
still 
valid) 
I 
saw 
a 
very 
big 
jump 
in 
performance.


Perhaps 
that 
would 
help 
in 
your 
case 
too?


Sounds 
interesting, 
maybe 
it 
help 
me 
a 
little.
I 
am 
using 
Perl 
DBD::SQLite, 
so 
i 
need 
some 
investigation 
how 
this 
library 
work.






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

Re: [sqlite] Query problem

[John Stanton]

[EMAIL PROTECTED] wrote:

[EMAIL PROTECTED] wrote:

This appears to be slightly different than normal *nix globbing since
SQLite uses '^' rather than '!' for the set inversion (if my reading of
the source is correct).

GLOB is suppose to exactly mimic Unix, except that SQLite does not
break pattern matching at / boundaries the way the shell does.
So if the previous statement is true, it is a bug.



Experiments using bash indicate that either ^ or ! is accepted
as the negation of a character set.  Hence,

ls -d [^tu]*
ls -d [!tu]*

both return the same thing - a list of all files and directories
in the current directory whose names do not begin with "t" or "u".

SQLite only supports ^, not !.  I wonder if this is something I
should change?  It would not be much trouble to get GLOB to support
both, must like the globber in bash.

Anybody have an old Bourne shell around?  An authentic C-shell?
What do they do?

--
D. Richard Hipp <[EMAIL PROTECTED]>

Both Korn and Bourne shells behave differently for the examples but in 
the same way.

   ls -d [^tu]* behaves like your example
   ls -d [!tu]* lists all directories.

C Shell does not recognize the 2nd example.  First one behaves as above.



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




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

Re: [sqlite] Program is crahed on sqlite3_bind_int

[John Stanton]

Did you change something on the machine.  Is it Windows or Linux?

Joanne Pham wrote:

Thanks!! So how to fix this problem..
-JP



- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 3:11:52 PM
Subject: Re: [sqlite] Program is crahed on sqlite3_bind_int

It is accessing a mutex.  Looks like you have something wrong with a 
shared library or DLL.


Joanne Pham wrote:

Hi All,
The exactly error message below:
sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897./src/vdbeapi.c: No such file or directory.
   in ./src/vdbeapi.c
Current language:  auto; currently c
Thanks,
JP


- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 2:33:37 PM
Subject: [sqlite] Program is crahed on sqlite3_bind_int

Hi All,
I am using sqlite 3.5.2 and Suddenly my program is crashed on sqlite3_bind_int command 
The error message from the crash is :


sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897./src/vdbeapi.c: No such file or directory.
   in ./src/vdbeapi.c

I had no clue what is going on and Don't know how to fix this problem.
Do you know what is problem with sqlite3_bind_int? It used to work last week 
and suddenly it didn't work any more.
Your help is greatly appreciated
JP


 

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



 

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.   http://tools.search.yahoo.com/newsearch/category.php?category=shopping



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


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping



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

Re: [sqlite] How to create a database in run-time

[John Stanton]

Luis Esteban Fajardo Bravo wrote:
Hi! we've using SQLite on our software since about a month ago, and 
still i have some questions about how does sqlite works at all.


I have the following sourcecode to check if a database is already 
created, if yes then just open, if not, open it and create the 
appropiate schema for the database:


if ( sqlite3_open_v2(path,,SQLITE_OPEN_READWRITE,NULL) != 
SQLITE_OK) {


   // Create the database

   sqlite3_close(historydb);

   if (sqlite3_open(path,) == SQLITE_OK) {

   // Now we create the default tables

   strcpy(query,"create table parameters (param_id integer 
primary key, param_name text(100));create table data_values (timestamp 
integer(4), value real, param_id integer(2));");


   rc = sqlite3_exec(historydb,query,process_query,NULL,);

   if (rc == SQLITE_OK) {

   strcpy(query,"pragma default_cache_size=40;");

   rc=sqlite3_exec(historydb,query,process_query,NULL,);

   }

   if (rc != SQLITE_OK) {

   // An error ocurred during database creation

  printf("SQL Error: ",errmsg);

   sqlite3_free(errmsg);

   return 0;

   }

   } else {

   // Unable to create database

  return 0;

   }

   }


This works fine under linux, and in windows if i run it onto gdb, but 
not if i call my object code from the command prompt, my question is if 
there's a better solution for check if the database is already created, 
something like a "describe" SQL Command (in oracle) that help to know if 
the schema is already there on the database?


Thank you!

You can use an "access" call to see if the file exists.  If you open it 
to test for existence you can do a "magic" test and look for the first 
few bytes being "SQLite" to authenticate it as an Sqlite DB.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Program is crahed on sqlite3_bind_int

[John Stanton]

It is accessing a mutex.  Looks like you have something wrong with a 
shared library or DLL.


Joanne Pham wrote:

Hi All,
The exactly error message below:
sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897 ./src/vdbeapi.c: No such file or directory.
in ./src/vdbeapi.c
Current language:  auto; currently c
Thanks,
JP


- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 2:33:37 PM
Subject: [sqlite] Program is crahed on sqlite3_bind_int

Hi All,
I am using sqlite 3.5.2 and Suddenly my program is crashed on sqlite3_bind_int command 
The error message from the crash is :


sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897./src/vdbeapi.c: No such file or directory.
in ./src/vdbeapi.c

I had no clue what is going on and Don't know how to fix this problem.
Do you know what is problem with sqlite3_bind_int? It used to work last week 
and suddenly it didn't work any more.
Your help is greatly appreciated
JP


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping



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

Re: [sqlite] SQLite character comparisons

[John Stanton]

I would suggest that Sqlite is correct having regard to its universal 
variable length feature and single TEXT type.  Compatibility would be 
achieved by not storing redundant trailing spaces if a string field is 
required.  More efficient storage would be a bonus.


Fowler, Jeff wrote:

Hello All,
 
Not trying to be antagonistic, but I'm curious to know how many of you agree with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. Correct me if I'm wrong Darren, but you feel this is a bad decision, and in fact SQLite's implementation of character comparison (respecting trailing spaces) is superior to ANSI's specs. Keep in mind this is not some obscure issue that can be subject to different interpretations by different vendors; it's very clearly stated: "The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them."
 
Does anyone know of another RDBMS (ANSI or no) that respects trailing spaces when comparing character data? We have both Oracle 10g and SQL Server 2005 in house and they both work "correctly" according to the specification. Has anyone tried it with DB2 or Informix? What about PostGres and MySQL? Although I haven't asked him, I'm guessing Zbigniew's suggestion a while back for an auto-trim feature stemmed from this issue. Other than saving space, would there be a need to trim data if WHERE, HAVING clauses and joins followed the spec? Also, other than performance (which seems to be the primary concern), would anyone would be negatively impacted if the current behavior were changed?
 
Our app creates SQLite tables dynamically based on the output from user-defined queries that run against data warehouses (of practically any "flavor") we have no control over, and we insert the results into SQLite. Sure - we can handle this situation by writing more code looking for spaces everywhere they might occur. But to me (and maybe only to me?), it makes sense for SQLite -- where reasonably possible -- to attempt to follow clear ANSI guidelines, allowing developers to override it only in cases where this adherence produces undesirable results. I can't see where this is undesirable from an applications standpoint, although I can see where performance may be a concern.
 
Regards,
 
Jeff Fowler
 



From: Darren Duncan [mailto:[EMAIL PROTECTED]
Sent: Fri 1/18/2008 4:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite character comparisons



At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote:

"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data, run the exact same query, yet
get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
found this issue because we DID get a different answer.


And every SQL implementation already has many differences from every
other one, even with fundamentals, making them all generally
incompatible and proprietary, and this example is right in line with
the kinds of differences they have.  Other implementations have
distinct var-char and fixed-char types, while SQLite only has the
former.

Part of the problem here is that the ANSI standard is itself flawed.

(Maybe this particular case of trailing spaces is an exception, but
many parts of ANSI SQL, AFAIK, are more focused on what the syntax of
SQL is, and they say that the semantics, even of some fundamental
operations, is left up to the implementation to decide for itself.
So what good does it do you if SQL of the same syntax will compile on
different DBMSs if it behaves differently in each one?  And the
standard considers this valid.)

How many other programming langauges besides ANSI SQL treat trailing
spaces as insignificant.


Regarding whether by extension it should be impossible to create strings
with trailing spaces; I side with the SQLite developers who say it isn't
the engine's job to trim blanks in data. Most other engines I've used do
not trim spaces either, even if the field is a varchar.


And rightly so, you should not trim spaces, because spaces are significant.

Herein lies a wider part of the problem.  The ANSI SQL is
inconsistent in how it treats trailing spaces in strings.  On one
hand it wants to preserve them, but on the other hand it wants to
ignore them in its most fundamental operation other than preserving.

(With my "it should not be possible" sentence, I was not saying that
spaces should not be trimmed in the fictional scenario where a
character string does by definition not contain trailing spaces, but
that code specifying them should produce an error rather than
succeed.  The matter is analagous to what would happen if you write
code that tries to treat the character string literal 'foo' 

Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

[John Stanton]

SELECT is read only.  Think of an Sqlite lock as a lock on the journal 
file.  The critical action of an Sqlite insert or update is to transfer 
the contents of the journal file to disk and verify that the write to 
disk has completed (the ACID feature).  Apart from that it is read only.


Somewhere I have code for an efficient read lock for Windows.  A write 
lock is a critical section or a mutex  I can seek it out if it would 
help you.


If you have a general purpose RPC server you probably need to parse the 
SQL to a certain degree to establish read or write ststua or have the 
RPC announce whether is it is a read or write


Skilfully implemented your Sqlite-based RPC server will have the 
capabilities of a regular RDBMS server but with the advantage that it 
can be seeded with other functions.  For example we embed Sqlite inside 
an RPC server which sits inside an HTTP web server which also embeds an 
SSL capability and a web page generator also embedding Sqlite.  Sqlite 
is the silver bullet which removes the need to have inefficient IPCs and 
CGI-like process creation and destruction.  AJAX in one process is 
realized, thanks to Sqlite.  Sendfile/TransmitFile can be used to full 
advantage.


In such a system careful partitioning into separate databases minimizes 
contentions.


[EMAIL PROTECTED] wrote:

I'd like to use reader-writer lock in the client code, but at this point
I'm not sure if I can determine at which point SQLite is not writing.

I mean, INSERT / UPDATE are most likely need a writer lock, but I
don't know if SELECT is guaranteed to be read-only in its internal
operation within SQLite when I set SQLITE_THREADSAFE=0.

Implementing an efficient RW lock on Windows XP is another challenge
anyway.

-- sword

On Sat, 19 Jan 2008 22:56:43 +0100
Jens Miltner <[EMAIL PROTECTED]> wrote:


Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]:


OK I figured out SQLITE_THREADSAFE=0 for the second question...
And it seems the answer for the first question is yes, but if you know
a simpler way please share it with us, thanks!
You could use a read-write mutex to serialize access to your database  
connection. That way you can have multiple readers, but modifying the  
database becomes an exclusive operation. This matches the sqlite  
requirements.
Alternatively, you can just retry your write queries if you get  
SQLITE_BUSY errors...




-- sword

On Sat, 19 Jan 2008 09:57:10 +0900
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:


Hello all,

I've read http://www.sqlite.org/lockingv3.html but am still not  
sure about

multithread and locking in 3.5.4.

I have a multithread application that has a single connection to a  
single
SQLite3 database. Since it's multithreaded, SQL statements are  
thrown to

a single SQLite3 object concurrently. I'm using
http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip
on VC8 + WindowsXP.

Prior to this version (I was using SQLite2) I'd serialized all  
these database access
using critical sections and didn't care about SQLITE_BUSY or  
SQLITE_LOCKED
since they never happen. It was very simple as I didn't need to  
implement access

retry for a busy case.

However, I learned that SQLite 3.5 does mutexing by default. So I  
removed

all synchronization stuff in my SQLite access code, and now it seems
it's not working as I intended. Unfortunately I can't reproduce it  
in my

development environment and I've not yet implemented logging to see
if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering
sqlite3_mutex_enter multiple times in the debugger though, so it's  
thread-safe

at least.

My question is,

1. Do I still have to synchronize all SQLite access in my client  
code not to

encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?)

2. If so, how can I turn off all these mutexes (critical sections)  
in SQLite 3.5.4?
They are needless if I serialize all SQLite access in the client  
code.


Regards,

-- sword



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



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



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




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




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

Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

[John Stanton]

Using read and write locks around your statements gives you protection 
and lets you compile without thread safe so that Sqlite does not use 
internal mutexes as much for synchronization saving you considerable 
overhead as well as avoiding the logic necessary to handle BUSYs from 
Sqlite and skipping any polling or busy waits.


Pthreads provides all the capabilities in the API.  Windows needs a 
little work to implement read locks.


Jens Miltner wrote:


Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]:


OK I figured out SQLITE_THREADSAFE=0 for the second question...
And it seems the answer for the first question is yes, but if you know
a simpler way please share it with us, thanks!


You could use a read-write mutex to serialize access to your database 
connection. That way you can have multiple readers, but modifying the 
database becomes an exclusive operation. This matches the sqlite 
requirements.
Alternatively, you can just retry your write queries if you get 
SQLITE_BUSY errors...





-- sword

On Sat, 19 Jan 2008 09:57:10 +0900
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:


Hello all,

I've read http://www.sqlite.org/lockingv3.html but am still not sure 
about

multithread and locking in 3.5.4.

I have a multithread application that has a single connection to a 
single

SQLite3 database. Since it's multithreaded, SQL statements are thrown to
a single SQLite3 object concurrently. I'm using
http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip
on VC8 + WindowsXP.

Prior to this version (I was using SQLite2) I'd serialized all these 
database access
using critical sections and didn't care about SQLITE_BUSY or 
SQLITE_LOCKED
since they never happen. It was very simple as I didn't need to 
implement access

retry for a busy case.

However, I learned that SQLite 3.5 does mutexing by default. So I 
removed

all synchronization stuff in my SQLite access code, and now it seems
it's not working as I intended. Unfortunately I can't reproduce it in my
development environment and I've not yet implemented logging to see
if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering
sqlite3_mutex_enter multiple times in the debugger though, so it's 
thread-safe

at least.

My question is,

1. Do I still have to synchronize all SQLite access in my client code 
not to

encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?)

2. If so, how can I turn off all these mutexes (critical sections) in 
SQLite 3.5.4?

They are needless if I serialize all SQLite access in the client code.

Regards,

-- sword



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] sqlite3 performace

[John Stanton]

John Stanton wrote:

Philip Nick wrote:
I tracked down the problem. I was linking an old version of sqlite3 
(3.0.8).
That was the old version we used. I apparently doesn't cache between 
calls.


My code now performs IO very similar to the CLI. Each subsequent query 
once

the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work 
sends

reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the 
connection

into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.

opening a file is very slow. You need to move it out of your loop.


I have noticed my program basically reads the whole database every time

the

query is run. The IO Read bytes increases by 900k for every query we

run. We

also chew a good chunch of the cpu  I have tried keeping the database
connection open and open/closing for every query. But there was no

change.


if I launch the CLI and run the query it runs instantly and monitoring

the

IO read bytes is see only ~20 bytes of read to execute the query, when

my
code is using over 900k for every call. I have been looking into the 
CLI

source to see what is done differently, but was hoping someone on here

might

have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't
optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite<http://www.reddawn.net/%7Ejsprenkl/Sqlite> 



Cthulhu Bucks!
http://www.cthulhubucks.com


- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 







We have an RPC server.  When it gets a request it looks at a pool of 
threads and selects the most recently opened on the basis that it is 
still in cache.  If the pool is empty it launches a thread and opens an 
Sqlite connection to it.  Each thread has an Sqlite context and an open 
Sqlite connection.


Dormant threads wait on an event and are signalled to start execution.

The performance is quite good with RPC's executing in about 40mS as 
recorded by the browser.  There is no churning from opening and closing 
connections and creatiing and destroying threads.


On shutdown the Sqlite connections are closed and the threads destroyed.

A note to add to this.  This is for pre 3.5.4. Sqlite which does not 
share connections.  It can be improved by using the latest sqlite and 
sharing a connection and cache.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] sqlite3 performace

[John Stanton]

Philip Nick wrote:

I tracked down the problem. I was linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.

My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work sends
reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the connection
into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.

opening a file is very slow. You need to move it out of your loop.


I have noticed my program basically reads the whole database every time

the

query is run. The IO Read bytes increases by 900k for every query we

run. We

also chew a good chunch of the cpu  I have tried keeping the database
connection open and open/closing for every query. But there was no

change.


if I launch the CLI and run the query it runs instantly and monitoring

the

IO read bytes is see only ~20 bytes of read to execute the query, when

my

code is using over 900k for every call. I have been looking into the CLI
source to see what is done differently, but was hoping someone on here

might

have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't
optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





We have an RPC server.  When it gets a request it looks at a pool of 
threads and selects the most recently opened on the basis that it is 
still in cache.  If the pool is empty it launches a thread and opens an 
Sqlite connection to it.  Each thread has an Sqlite context and an open 
Sqlite connection.


Dormant threads wait on an event and are signalled to start execution.

The performance is quite good with RPC's executing in about 40mS as 
recorded by the browser.  There is no churning from opening and closing 
connections and creatiing and destroying threads.


On shutdown the Sqlite connections are closed and the threads destroyed.

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

Re: [sqlite] Can't understand out of memory error

[John Stanton]

Nemanja Čorlija wrote:

On Jan 17, 2008 12:00 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Nemanja Čorlija wrote:

My problem is that I am running out of memory when trying to load
1.6GB sqlite3 db from disk to :memmory: db on a computer with 4GB of
RAM.

Your RAM isn't the problem.  You are running out of address space.  For
a 32 bit process, the address space available is 4GB.  The operating
system typically reserves 1 or 2GB of that for its own uses (eg Linux
typically reserves 1GB, Windows reserves 2GB, I don't know what OSX does).

That means that all the shared libraries, your stack, each stack's
thread if the process is multithreaded, memory mapped files as well as
dynamically allocated memory all have to fit within that remaining
space.  In your case you ran out :-)

Here is an example of how to look at what is in a process' address space:

http://blog.mozilla.com/dolske/2007/10/16/os-x-and-virtual-bloat/



Thanks for clearing that up. That blog post was really useful in
helping me understand this issue better.


If you must take this :memory: approach then you will have to switch to
using a 64 bit process which will also require a 64 bit kernel and
processor.  (Note that the amount of physical RAM only affects performance).

However wanting the entire database in memory implies you are having
performance issues.  What were those?



I have a db with 27M rows and then I have 5 other dbs with exact same
schema that together have 100+M rows and for each of those 100M rows I
need to check if its primary key column has a match in 27M db and if
it does update that row to set a flag. There's of course a lot of ways
to go about this but I just assumed that it would be fastest if I can
perform all those lookups against memory db and already wrote a python
script to do all the work and then hit this problem. I first thought
that it is a python/APSW issue but then I got the exact same error
with sqlite3 command line program.
I'll try to take advantage of locality of reference on smaller
presorted sets of data and see if that can get the job done quick
enough.

Thanks.

You are unlikely to find a memory DB running faster than a disk based 
one (because of caching).




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

Re: [sqlite] sqlite3 performace

[John Stanton]

Areyou doing an sqlite3_finalize and checking to see that you actually 
close the DB?


Philip Nick wrote:

Thanks for replying,

I have tried moving the Open/Close outside the mutex no change.

As for using our own mutex, we started with early versions of sqlite and had
to come up with our own solution. I was planning on looking into using the
built in mutex's, but first I need to solve the performance issues.

The only difference I can see between our code and the CLI is it uses
sqlite3_exec and a call back, while we do

sqlite3_prepare
Then we call sqlite3_column_count.
We loop through 0-num_col and call:
   sqlite3_column_name
   sqlite3_column_decltype

Then we do  while(sqlite3_step() == SQLITE_ROW)


From my understanding the sqlite3_exec() is doing the same thing and sending

the info too the callback.

Any ideas?

Thanks

On Jan 17, 2008 5:09 PM, <[EMAIL PROTECTED]> wrote:


"Philip Nick" <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.
I use a Mutex to ensure only one process/thread can access the database

at

one time.

The current flow of events:
Get Mutex
Open Database connection
Run Query
Close Database connection
Release Mutex


SQLite does the mutexing automatically (assuming you are using
version 3.5.0 or later).
--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-








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

Re: [sqlite] How do I get back command history in v3.5.4?

[John Stanton]

You might be having a library location problem between /usr/local/lib 
and where Ubuntu puts libraries, usr/lib.


Fred J. Stephens wrote:

On 1/16/08, John Stanton <[EMAIL PROTECTED]> wrote:

Do you have readline installed on your Linux machine?  If not download
the package, configure and install.  Then compile Sqlite with readline.

John,
Yes I do have readline installed, and as I said, SQLite worked fine
with the version from the Ubuntu package repositories (3.4.2). I would
just use that one, but I am concerned about the possible data
corruption bug fixed in 3.5.4.
Thanks for the reply. Any other suggestions?

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




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

Re: [sqlite] How do I get back command history in v3.5.4?

[John Stanton]

Do you have readline installed on your Linux machine?  If not download 
the package, configure and install.  Then compile Sqlite with readline.


Fred J. Stephens wrote:

Just replaced SQLite v 3.42 on Kubuntu 7.10 with the new v3.45 binary
downloaded from sqlite.org. it works fine, but now I don't have the
(essential, to me) command history. I have searched the forums and
read that it requires readline. I do have /lib/libreadline.so.5.
I tried compiling from source but get errors, 137 if I remember
correctly. Tried editing the make file to enable readline and putting
in the path to the readline library, but I still get errors compiling.
Isn't readline support supposed to be automatically detected on Linux
by configure?

If I can't get this fixed, how likely is the possible  data corruption
problem that was corrected by 3.5.4?  Will I have to wait for the new
version to hit the Ubuntu repositories? Maybe I am spoiled by 3-4
years on Gentoo , in having the latest versions available, but if data
corruption is an issue I would like to use the new version.

Another curious thing is that the original binary for sqlite3 is only
about 32k, while this new one is 300k! The file command shows that
they are both dynamically linked and stripped, so I don't understand
the huge difference in size.

Any information appreciated

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




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

Re: [sqlite] Next Version of SQLite

[John Stanton]

[EMAIL PROTECTED] wrote:

Joe Wilson <[EMAIL PROTECTED]> wrote:

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
Sorry for the confusion. 

No problem.

For what it's worth, I am also curious as to the final form of the 
VM opcode transformation. The number of opcodes generated by the various
SQL statements seems to be roughly the same as the old scheme. At this 
point without sub-expresssion elimination are you seeing any speed 
improvement?




I have not even looked at performance yet.  I'm assuming that
performance will drop during the conversion process and that we
will have to fight to get it back up to previous levels after
the conversion is complete.

But consider would can be done with a register machine that
would couldn't do with the old stack machine.  In a statement
like this:

SELECT * FROM a NATURAL JOIN b;

Suppose tables a and b have column c in common and unique
columns a1, a2, a3 and b1, b2, b3.  With the stack machine, 
the algorithm is roughly this:


foreach each entry in a:
  foreach entry in b with b.c==a.c:
push a.c
push a.a1
push a.a2
push a.a3
push b.b1
push b.b2
push b.b3
return one row of result
  endforeach
endforeach

For each result row, all columns had to be pushed onto the
stack.  Then the OP_Callback opcode would fire, causing
sqlite3_step() to return SQLITE_ROW.  The result columns would
then be available to sqlite3_column_xxx() routines which read
those results off of the stack.  When sqlite3_step() is called
again, all result columns are popped from the stack and 
execution continues with the first operation after the 
OP_Callback.


In the register VM, result columns are stored in a consecutive
sequence of registers.  It is no longer necessary to pop the
stack of prior results at the start of each sqlite3_step().
So the code can look more like this:

foreach entry in a:
  r1 = a.c
  r2 = a.a1
  r3 = a.a2
  r4 = a.a3
  foreach entry in b where c=r1:
r5 = b.b1
r6 = b.b2
r7 = b.b3
return one row of result
  endforeach
endforeach

When result are stored in registers, the computation of the 
first four columns of the result set can be factored out of 
the inner loop.  If there are 10 matching rows in b for every

row in a, this might result in a significant performance boost.

Do not look for this improvement right away, though.  The
first order of business is to get the VM converted over into
a register machine.  Only after that is successfully accomplished
will we look into implementing optimizations such as the above.

--
D. Richard Hipp <[EMAIL PROTECTED]>

This architecture replacement is a very significant step for Sqlite from 
my perspective.  It indicates a transition from the simple, more easily 
implemented technology into a more refined one providing the basis for 
adding more sophistication in optimization and a generally more 
efficient core.


Such ongoing evolution guarantees that Sqlite will not ossify and become
irrelevant.  Congratulation Dr Hipp and team for not leaving well alone.



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




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

Re: [sqlite] Using sqlite3 from multiple threads

[John Stanton]

I find it much simpler to put a mutex around the accesses (or make them 
a critical section).  That serializes the access and avoids busy waits, 
retries etc.  It will prevent a certain amount of read concurrency. but 
that may be insignificant.


If you use pthreads and have plenty of reads for each write you might 
use pthread read/write locks instead of plain mutexes.  You can 
synthesize read/write locks from windows locking calls if you are patient.


Andreas Volz wrote:

Am Wed, 9 Jan 2008 10:20:31 -0800 (PST) schrieb Ken:


Definately use 3.5.4.

Not sure how to determine at compile time if the threadsafe part is
enabled. You can always compile yourself to guarantee its set, thats
what I do.

sqlite will lock the database file for you automatically. Your
threads do not need to implement locking. But they do need to handle
in some fashion. SQLITE_BUSY and/or SQLITE_LOCKED error codes. 


You should also look at "Begin", "Begin Exlusive" and "Begin
Immediate" sql commands.


Ok, I found the docs. But I exec only single SQL queries at the moment.
So I think I don't need that.


As a user you don't get to pick the lock state, sqlite does that
automatically for you. You must handle the return codes suche as
SQLITE_BUSY, SQLITE_LOCKED etc.


Now I implemented a multi-threading example that reads and writes in a
database file. Currently I check return values and if in BUSY or LOCKED
state I wait some time and try it again until I get the lock.

It works, but currently I have around 20 BUSY operations for 5
successful write operations in 5 threads parallel threads.

Is it really the way of choose to do it this way? Isn't there a way
without active waiting? I would prefer exec() blocking until I get a
write lock to not have to loop with a sleep until it works.

regards
Andreas

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




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

Re: [sqlite] sqlite3_open16 used in Qt4

[John Stanton]

Sqlite will create a database if it does not find one.  You really need 
to add a test for the existence of the file, such as an "access".


Petr Vanek wrote:

hello sqlite gang,

I'd like to consult here some possible bug in Qt4 SQL library. I'm fixing
one bug in my Qt4 based application and I see there is propably buggy
QSqlDatabase::open() method in their Sqlite driver. They are using this
routine to open a database:

...
if (sqlite3_open16(db.constData(), >access) == SQLITE_OK) {
sqlite3_busy_timeout(d->access, qGetSqliteTimeout(conOpts));
...
return true;
} else {
...
return false;
}

And when I look into sqlite code itself the sqlite3_open16 *always* returns
SQLITE_OK (return sqlite3ApiExit(0, rc); to be exact).

Where is the problem? When I use (in Qt4) e.g.:
db.open("this_is_not_a_sqlite_db.tgz")
I get "true" in the meaning "your file is opened successfully" and I have to
perform an additional check for example by a dummy select statement to be
sure it is a db.

I'd like to know if there is bad usage of sqlite function in Qt4 to report a
bug.

all the best
Petr Vanek



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




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

Re: [sqlite] "always-trim" - feature suggestion

[John Stanton]

Some of the correspondents seem to have lost track of the meaning of 
"Open Source".  They have the source code and can build their own 
version which trims, slashes or even burns.  They certainly do not have 
to inflict their particular needs or preferences on the whole user 
community.


I am sure that a prime reason for the success of Sqlite is that it's 
creator maintains discipline and saves it from becoming a bloated mess.


Rob Sciuk wrote:

On Wed, 9 Jan 2008, Zbigniew Baniewski wrote:


On Wed, Jan 09, 2008 at 12:51:16PM +, [EMAIL PROTECTED] wrote:


Why not have a possibility to make it default
behaviour of the SQL-engine itself, just by
using one "pragma"?

1. It'll make my code shorter.


But it makes the SQLite core code larger.  Why should the the
SQLite core be enlarged for the convenience of a single user.


It's rather hard to say, if really just "single". SQLite, as I 
understood,

has many users; just three of them were "against", until this time.

Who knows, which of the existing features are used by how many users?
Yesterday one of them wrote, that (if I properly understood) he 
appreciates

nothing more, than "job of storage".


2. It'll make my life easier. ;)


But it makes my life harder. [..]


Probably a bit - but it was your own choice of such way of life, 
anyway. ;)


You know, I believe that an "embedded" SQL has a philosophy which is 
inherently minimalist.  Your request specifically goes against the 
philosophy of what SQLite was designed to be. DRH is working hard to 
protect an ideal which has appealed to millions, and continues to do so, 
and adding bloat will not contribute to its future success.


There is nothing to top you from *FORKING* the SQLite project into a 
kitchen sink version of MySQL, PostGresql, Firebird or any of a number 
of existing full featured databases, and then *YOU* can be the one to 
determine what features belong in *YOUR* database managment system, and 
take on the work of implementing every idea which comes along ...


Perhaps SQL_Obese?


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] For Better Performance

[John Stanton]

Your disk is slow (large latency).  Speeding that up to 7,500 or 15,000 
RPM will help.


Sreedhar.a wrote:

Hi,
 
I am using sqlite for meta data storage of audio files.

I am storing the sqlite database in hard disk.
The sector size of FAT file system is 512 bytes.
Hard disk rpm is 4200
Page size = 1K
cache size = 2k
The processor speed is 600 Mhz.
 
I am using joins method in sqlite.The records are the meta data information

of the audio files.
How i can improve my search and insertion speeds?
 
any change in the above parameters will help?
 
Thanks and Regards,

A.Sreedhar.
 
 




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

Re: [sqlite] Trying to use SQLite3 with PHP5....

[John Stanton]

I take your point about TCL and endorse it.  Please excuse my irritation 
at finding that the makefile needed a little tweaking to install an 
upgrade.  TCL is elegant, not wretched, but tclsh appearing in a generic 
makefile was a minor nuisance for a person who gets to be more of a 
curmudgeon each year.  :-)


[EMAIL PROTECTED] wrote:

John Stanton <[EMAIL PROTECTED]> wrote:
You need to make a small change in the make file to get rid of the 
wretched tclsh and link sqlite3 from the library.  Just use an earlier 
makefile as a template.




The problem with the TCL dependency was reported by
tickets #2845 and #2846 and has been fixed in check-in [4636].

John, I will thank you to not refer to tclsh as "wretched".  The
Tool Control Language (TCL) is one of the most elegant and
power programming languages ever devised.  TCL is not part
of the Algol family of languages (it is more closely related
to Lisp) which makes it difficult to grok for people who have 
only been exposed to Algol-like langauges.  But this does not
detract from the extreme elegance of the language.  


Let me state unambiguiously that SQLite would not be possible
were it not for TCL.  70% of SQLite (all of the test scripts,
which are arguable the most important part of the code base)
is written in TCL.

--
D. Richard Hipp <[EMAIL PROTECTED]>


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




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

Re: [sqlite] Trying to use SQLite3 with PHP5....

[John Stanton]

You need to make a small change in the make file to get rid of the 
wretched tclsh and link sqlite3 from the library.  Just use an earlier 
makefile as a template.


Philip Butler wrote:

Hi all,

I am revisiting this topic again - I have tried a LONG time ago and gave 
up


I am wanting to use SQLite3 with PHP (5.2.5)..  I have 2 issues...

1)
I can't seem to compile sqlite3 on a Linux system.  It wants to use TCL 
which I don't have installed.  I have used:


configure --disable-tcl

and it still wants some tcl utility to (from what I think) build the 
sqlite3.c file.  I have searched the email archives and have seen others 
with the same problem - but haven't found an answer that works for me.  
I have punted trying to compile the source and have installed the 
precompiled sqlite3 files - sqlite-3.5.4.so, sqlite3-3.5.4.bin, 
sqlite3_analyzer-3.5.4.bin.





2)
Now comes the fun part - I can't figure out how to get sqlite3 to play 
right with PHP.  From what I can tell, PDO is needed to do this - but 
from the docs, it seems like I need to be able to compile sqlite3 (i.e. 
issue #1 above).  So it seems that I am stuck.  Does anyone have any 
notes they can share on getting the latest sqlite to work with the 
latest PHP ??  Ideally, the PHP team would incorporate sqlite3 into the 
PHP sources (like they do with sqlite2), but this hasn't been done.


I have been down this path before (a year or two ago) - posted to 
forums/maillists on both SQLite and PHP.  The PHP people say it's a 
sqlite issue and the sqlite people said it was a PHP issue.  Therefore, 
I have been using Postgresql for the past few years.  I don't have a 
problem with Postgres - but want to try something "lean and mean".



Thanks,

Phil Butler




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Date Problems

[John Stanton]

Look at the Sqlite date functions.  File date.c describes them.

You may find that a custom function gives you exactly what you want.

[EMAIL PROTECTED] wrote:

Hi Kees

Many thanks for your quick reply, but it doesn't give me the date i
want.

2006/03/31 - 1 month should be 2006/02/28

whereas
select date('2006-03-31', 'start of month','-1 month') obviously gives
me 2006/02/01



I need to be able to subtract 1 month from not just a single expression,
but from a 100s of dates that are stored in a table.


Richard - I also just spotted your email. Thanks, I agree, it is fuzzy.
However :

2006/03/31 minus 1 month : I could accept 28 Feb or 1 Mar as a
reasonable answer and I can make that point to my users.

03 March is not reasonable, I can't think of any logic that would give
me that answer and I'm not able to make any case.


Thanks
Craig
 


-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 03, 2008 9:25 AM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date Problems


Hi Craig,

On Thu, 3 Jan 2008 08:49:42 +0900, <[EMAIL PROTECTED]> wrote:


Hi

Can somebody give any explain to this please.

sqlite> select date("2006-03-31");
2006-03-31
--> correct

sqlite> select date("2006-03-31", "-1 month");
2006-03-03
--> not correct


Can anyone confirm? Any suggestions / workarounds greatfully received!


Confirmed.

Better:
select date('2006-03-31', 'start of month','-1 month');


Many thanks


HTH


Craig

--
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Visit our website at http://www.ubs.com

This message contains confidential information and is intended only 
for the individual named.  If you are not the named addressee you 
should not disseminate, distribute or copy this e-mail.  Please 
notify the sender immediately by e-mail if you have received this 
e-mail by mistake and delete this e-mail from your system.


E-mails are not encrypted and cannot be guaranteed to be secure or 
error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses.  The sender 
therefore does not accept liability for any errors or omissions in the 
contents of this message which arise as a result of e-mail transmission.  
If verification is required please request a hard-copy version.  This 
message is provided for informational purposes and should not be 
construed as a solicitation or offer to buy or sell any securities 
or related financial instruments.



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




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

Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?

[John Stanton]

An integer is always 64 bits.

Jerry Krinock wrote:
I need to read an sqlite database generated by others.  So I wrote an 
outer loop which steps through the rows of a table using sqlite3_step, 
and an inner loop which steps through the columns.  The inner loop finds 
the type using sqlite3_column_type(), then 'switches' to get the value 
using the appropriate sqlite3_column_X() function.


It works fine if, when encountering an SQLITE_INTEGER type, I use 
sqlite_column_int64() to get the data.


I don't know whether or not I'm just "lucky" that the application which 
wrote the database uses 64 bit for all of its integers?  If so, what if 
someone throws a 32-bit integer at me someday?  How can I tell whether 
integer data objects in a table are 32 or 64 bit?  The column 
specifications I get from pragma_table_info() are likewise 
uninformative, saying simply type=INTEGER.


Thanks again,

Jerry Krinock


// Method Implementation (Objective-C for Mac OS X)

- (NSArray*)dicsOfRowsInTable:(NSString*)table {
// Will return nil if fails, empty array if no rows
void* db = [self db] ;
//char* errMsg = NULL ;
int result ;

NSString* statement = [[NSString alloc] initWithFormat:@"SELECT * 
FROM '%@'", table] ;


// Compile the statement into a virtual machine
sqlite3_stmt* preparedStatement ;
result = sqlite3_prepare(db, [statement UTF8String], -1, 
, NULL) ;

[statement release] ;

NSArray* output = nil ;
if (result != SQLITE_OK) {
[self showError:"prepare" from:11 code:result] ;
}
else {
NSMutableArray* rowDics = [[NSMutableArray alloc] init] ;
NSArray* keys = [self keysInTable:table] ;
int nColumns = [keys count] ;
while (result = sqlite3_step(preparedStatement) == SQLITE_ROW) {
NSMutableDictionary* rowDic = [[NSMutableDictionary alloc] 
init] ;


int iColumn  ;
for (iColumn= 0; iColumn 0) {
pFirstByte = 
sqlite3_column_blob(preparedStatement, iColumn) ;
object = [[NSData alloc] 
initWithBytes:pFirstByte length:nBytes] ;

}
break ;
case SQLITE_INTEGER:
intValue = 
sqlite3_column_int64(preparedStatement, iColumn) ;

object = [NSNumber numberWithLongLong:intValue] ;
break ;
case SQLITE_TEXT:
// "Strings returned by sqlite3_column_text() 
and sqlite3_column_text16(),
// even zero-length strings, are always zero 
terminated."

// So, we ignore the length and just convert it
utf8String = 
sqlite3_column_text(preparedStatement, iColumn) ;
object = [NSString 
stringWithUTF8String:(char*)utf8String] ;

break ;
case SQLITE_FLOAT:
doubleValue = 
sqlite3_column_double(preparedStatement, iColumn) ;

object = [NSNumber numberWithDouble:doubleValue] ;
break ;
case SQLITE_NULL:
default:
// Just leave object nil, will replace with 
[NSNull null] soon.

;
}

if (object == nil) {
object = [NSNull null] ;
}

[rowDic setObject:object forKey:[keys 
objectAtIndex:iColumn]] ;

}

NSDictionary* rowDicCopy = [rowDic copy] ;
[rowDics addObject:rowDicCopy] ;
[rowDicCopy release] ;
}

output = [rowDics copy] ;
[rowDics release] ;
}

// Finalize the statement (this releases resources allocated by 
sqlite3_prepare()

Re: [sqlite] Commit fails due to "database is locked" in active transaction

[John Stanton]

In a threaded environment the simple and effective solution is to 
synchronize your transactions with a mutex.  You lose a little possible 
concurrency but if you do not need it you simplify the logic no end and 
have a more robust application.


Using pthreads you can improve a little by using read and write locks, a 
sophistication on a simple mutex.


Richard Klein wrote:

Requiring the second transaction to complete first is expected in
terms of SQLIte's concurrency system.


So in terms of using SQLite, I need to close the entire transaction 
and restart it when I get a "database locked" return code in a writer 
thread? It's not enough to just retry the commit in a little while?


You don't need to close the connection, but you do need to ROLLBACK
the transaction, unless you have some sort of a priori knowledge that
the second transaction will not try to write to the database.  In such
a case, the second transaction will not try to acquire the RESERVED lock
already held by the first transaction, and so the second transaction
will eventually run to completion.  In such a scenario, the first
transaction can sit in a busy wait loop (sleep for a bit, then retry
the COMMIT) until the COMMIT succeeds.

However, if the second transaction will (or might) try to write to the
database, you must ROLLBACK the first transaction, sleep for a bit, and
restart the first transaction.

- Richard Klein





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



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

Re: [sqlite] Date Problems

[John Stanton]

Rich Shepard wrote:

On Thu, 3 Jan 2008, [EMAIL PROTECTED] wrote:

The bottom line is that "one month ago" or "one month from now" is a 
fuzzy

concept since months vary in length.


  This is a very interesting thread since so many business applications are
highly dependent on dates, yet most SQL implementations have comparatively
minimal support. It would be great if SQL itself defined standards and
provided guidelines for complete implementations.

Rich


Do you have ideas?  What functions do you envisage?

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

Re: [sqlite] Fastest way to check if new row or update existing one?

[John Stanton]

Did you try using INSERT OR REPLACE?

Hugo Ferreira wrote:
> Hmmm... Would it be possible to make a trigger on a table such that if any
> update fails, it does an insert?
> If so, then one would only need to issue updates.
> 
> On Dec 26, 2007 11:35 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> 
>> On Wed, 26 Dec 2007 10:17:43 +0200, Ion Silvestru
>> <[EMAIL PROTECTED]> wrote:
>>
> QUESTION: is there a better way to make this important decision? using
> Sqlite
 INSERT OR REPLACE may work for you.
>>> There is a problem with "INSERT OR REPLACE" in that "REPLACE" is not
>>> truly , but is  (existing row is deleted and
>>> new row is added), or I am wrong?
>> It is a full replacement of the row, just as the word REPLACE
>> (Take the place or moveinto the position of) suggests. Perhaps
>> you are confused with UPDATE.
>> REPLACE has been introduced to increase compatibility with other
>> database engines, they all follow the same strategy.
>>
>>> If I am correct, then a question to developers of SQLite:
>>>
>>> Is it difficult to change the behaviour of the "REPLACE" part of "INSERT
>> OR REPLACE"
>>> to be the correct behaviour, row content is updated, and not deleted then
>> inserted?
>>
>> What would be the difference?
>> I guess your new row doesn't provide some of the column values
>> of the existing row, and you want to keep some of those. In that
>> case, SELECT / UPDATE is the only option.
>>
>> To change the behaviour of REPLACE into selective updating of
>> columns SQLite would have to know which columns it would have to
>> update and which not. It simply can't.
>>
>> A nice solution is found in
>> Date: Tue, 24 Apr 2007 14:36:48 -0400
>> Subject: [sqlite] Re: INSERT OR REPLACE without new rowid ,
>> Message-ID: <[EMAIL PROTECTED]> :
>>
>> IT> You can do
>> IT>
>> IT> UPDATE ... WHERE keyfield='xxx';
>> IT>
>> IT> then use sqlite3_changes to see whether any update
>> IT> has in fact taken place, and run INSERT if not.
>> IT>
>> IT> Igor Tandetnik
>>
>>> Thanks in advance and happy hollidays!
>> HTH
>> --
>>  (  Kees Nuyt
>>  )
>> c[_]
>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
>> -
>>
>>
> 
> 


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

Re: [sqlite] disable transaction support

[John Stanton]

The fastest performance you will get is with synchronous off.  That will 
relax the ACID requirement on the COMMIT but it can be unsafe if you get 
a crash during the commit.


Mohd Radzi Ibrahim wrote:

Hi Rasanth,

I'm not an expert in SQLite. Not sure what kind of performance gain you 
want to achieve. Perhaps you could try "pragma synchronous=off". Or 
in-memory database...


I guess the journal is there to provide ACID db characteristic.

For me even with that out-of-the-box, the insert performance is already 
faster than MS SQL Server 2005; (I've been working with more than 1 
million rows tables).


best regards,
Radzi.

On 26-Dec-2007, at 2:38 PM, Rasanth Akali Kandoth wrote:


Hi Radzi,
i do it with BEGIN and COMMIT. it is that, even in this case for 
transaction
support sqlite has to write into the journal files as well. i want to 
avoid

this too.

Thanks,
Rasanth

On Dec 26, 2007 11:34 AM, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:


On the contrary, sqlite work much-much faster when insert/update is
done within BEGIN and COMMIT;

regards,
Radzi.
On 26-Dec-2007, at 12:14 PM, Rasanth Akali Kandoth wrote:


Hi All,
I have an application which inserts large number of rows into a
table, where
transaction support is not necessary. For performance reason, i need
to
disable the transaction support in sqlite version 3.3.17 .   How can
i do
it?  any help is highly appreciated.

--
Thanks,
Rasanth




- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 







-



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Fetch all result set to memory

[John Stanton]

Whichever way you do it Sqlite fetches the rows one by one.

Ofir Neuman wrote:

Hi,

I'm using SQLite in a multithread application and I would like to fetch every 
select statement into the memory.

I can't use sqlite3_get_table since it doesn't support Unicode as far as I know.

Is there a better way to fetch all the result to memory other than fetching it 
one by one and build a custom matrix?

Thanks.



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

Re: [sqlite] SQLite for NAS storage - DVR like application

[John Stanton]

Trilok Soni wrote:

Hi John,

On Dec 18, 2007 11:53 PM, John Stanton <[EMAIL PROTECTED]> wrote:

Sqlite depends upon POSIX file locks.  It is no better or no worse than
the POSIX lock implementation on your platform.


Thanx for the reply. My platform is based on Linux running on ARM9, with
having 128MB of the flash on it. The captured video from analog/IP camera
will be stored on the NAS storage device connected to it through network, which
gets mounted to the filesystem using NFS. So, the database file containing these
videos meta information will be stored on NAS device, not on the flash
partition,
as flash is used to keep programs binaries and the size constraint.


We use Sqlite in a multi-user environment without dependency oin the
POSIX locks by embedding it in a server using HTTP when it is on a
remote machine.  We get the small footprint and simoplicity of Sqlite
and get no multi-user glitches regardless of platform.


I am not able to understand, what you mean here by "embedding sqlite in a
server using HTTP when it is on a remote machine". In our project scenario,
we do have remote interface of this platform using embedded webserver running
on the device (e.g. boa/lighthttpd/webapp) and we want to show various
information
about these videos on the webpage by searching this file database
residing on the NAS.

Only confusion for selecting SQLite came here is, because we are not storing
this database on the local flash on the device, but on the NFS mounted
NAS device,
which gets accessed/configured through web-interface provided by the
device through
some embedded webserver as explained in the earlier paragraph. And
SQLite FAQs I have
pointed mentions that there could be problem in accessing the database
over the NFS,
this is where I got confused and unable to decide to go for SQLite or not.

Your storage issue is not deciding on Sqlite, it is deciding on NAS or a 
DB server.


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

Re: [sqlite] sample code in C++ to get the data from sqlite database loop thru the record set

[John Stanton]

Here is a code fragment in C.  You add the C++.

Joanne Pham wrote:

Hi All,
If you have the sample code in C++ to run the command "Select column1, column2 from 
tablea" from C++ code and able to loop thru the record set to get each rows please 
share with me.
Thanks in Advance,
Joanne


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping


/*Code fragment to compile an SQL statement and read the result set.
this does not have code to handle an altered schema.*/

  int  col_cnt; 
  sqlite3  *db;  
  int  rc;
  cosnt char   *sqlst_end;
  sqlite3_stmt *sqlst;
  
  /*Compile the SQL.*/
  rc = sqlite3_prepare_v2(db, sqstmnt, -1, , _end);
  if (rc != SQLITE_OK) {
wrapup("9: SQL compile error");
return(TRUE);
  }  /*if*/

  /*Execute the SQL.  It could be busy.  Count busy polls to break 
  a deadlock.  When busy reset the current step, pause and relaunch it.*/

  /*Count columns in statement and access declared types.*/
  col_cnt = sqlite3_column_count(sqst);

  finished = FALSE;
  while (!finished) {
rc = sqlite3_step(sqst);
switch (rc) {
  case SQLITE_DONE: /*Execution finished.*/
finished = TRUE;
sqlite3_reset(sqst);  /*Resets the compiled statement for re-use.*/

/*Execute logic for end of data set.*/
/*!!!*/
break;
  case SQLITE_ROW:  /*We have a row.*/
if (rowcount == 0) {
  /*Execute code for start of data set*/
  /**/
}

/*Scan all the columns.  Return value in "strg"*/
for (a = 0; a < col_cnt; a++) {
  /*Get actual type.*/
  switch (sqlite3_column_type(sqst, a)) {
case SQLITE_INTEGER:
  result = sqlite3_column_int(sqst, a);
  sprintf(strg, "%d", result);
  break;
case SQLITE_FLOAT:
  {
/*Float to string.*/
double  dub;
dub = sqlite3_column_double(sqst, a);
sprintf(strg, "%f", dub);
  }
  break;
case SQLITE_TEXT:
  vp = sqlite3_column_text(sqst, a);
  p = strg;
  while (*vp > 0) *p++ = *vp++;
  break;
case SQLITE_BLOB:
  /*Write to a file, dynamic memory ...*/
  break;
case SQLITE_NULL:
  strg[0] = 0;
  break;
  }  /*switch*/
}/*for*/

rowcount++;
break;
  case SQLITE_BUSY:  /*DB is busy, try again*/
int errc = sqlite3_reset(sqst);
if (busy_count++ == MAX_BUSY_COUNT) {
  sqlite3_finalize(sqst);
  wrapup("9: Database Deadlocked");
  break;
}  /*if*/

/*To make the polling less intensive give up the time slice.*/
#if IS_WIN32
sleep(0);  /*Win32 has a different yield call.*/
#else
yield();   /*Relinquish time slice to limit overhead of polling.*/
#endif
break;
  default:/*A nasty error.*/
sqlite3_finalize(sqst);
wrapup("9: Fatal SQL EXEC error");
break;
}  /*switch*/
  }/*while*/

  /*This will clear the compiled statement so that the DB can be closed.*/
  sqlite3_finalize(sqst);

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

Re: [sqlite] sqlite in a daemon

[John Stanton]

We do it.  It works well.  We use a thread for each connection and HTTP 
protocol.  The DB connection stays open.  It performs an XMLHttpRequest 
transaction in 20-40mS, depending on the SQL complexity.


Jean-Christophe Roux wrote:

Hello,

I am considering writing a daemon that would keep the sqlite connection opened forever 
(at least in the "daemon" sense of forever). Is it a good idea? Any experience 
you could share about that kind of design?
Thanks




  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping



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

Re: [sqlite] Re: :Using sqlite3_progress_handler for GUI application

[John Stanton]

Zbigniew Baniewski wrote:

On Tue, Dec 18, 2007 at 02:10:02PM -0600, John Stanton wrote:

It won't be ignored.  If you have an index ordered in the sequence 
specified by the ORDER BY Sqlite will use that order instead of having 
to assemble the result set and sort it.


To say it precisely: although it won't be ignored - but (in the case
mentioned above) it won't be used anyway, and won't spoil the speed gain,
given by index, right?

I'm asking, because tries with new database I prefer to make without
indexing, and add indexes later. So I was wondering, should I then check the
code again, to remove some "ORDER BY"-s, or can I skip it.
Leave your order by as it is.  To rely on a physical ordering from the 
DB is to set up a future problem.


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

Re: [sqlite] Re: :Using sqlite3_progress_handler for GUI application

[John Stanton]

Zbigniew Baniewski wrote:

One more thing: suppose, I created an index for the column "name". Should I
then take care to avoid ending the clauses with "ORDER BY name" - or just
it doesn't matter, and it'll be automatically ignored?
It won't be ignored.  If you have an index ordered in the sequence 
specified by the ORDER BY Sqlite will use that order instead of having 
to assemble the result set and sort it.


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

Re: [sqlite]:Using sqlite3_progress_handler for GUI application

[John Stanton]

If you store the last key and have an index on that key you will get 
each 100 at the same speed by getting the first 100 greater than your 
highest last key.


Sreedhar.a wrote:
 


Hi,

 Say,I am having 4 entries in the table ALBUM.

"CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT
NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));"

Now I want to list the first 100 Album from ALBUM table.The result has to be
in sorting order.So my querry is like.

"SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;"

So for the next time I will change the offset to 200 then 300 ...
But the search Speed is fast initially and slows down later since it has
4 records.Since I want to display the results in the GUI Is there any
other way to can increase my speed.
I came across sqlite3_progress_handler() .Will this be better for my case.If
so kindly suggest where I can read to know more about this.

if ( sqlite3_prepare(pst_SqliteCallback->db"SELECT AlbumId ,Album FROM ALBUM
ORDER BY Album;",-1,_SearchPrepareStmt,0)!= SQLITE_OK) {
return SQLITE_EXEC_ERROR;
}
u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt);
sqlite3_progress_handler(db,26,xCallback,pArg);>Is this the right place.
u32_NumCols = sqlite3_column_count(pst_SearchPrepareStmt);

ps8_ColumnName = sqliteMalloc(2*u32_NumCols*sizeof(const char *) + 1);

while( u32_ReturnStatus == SQLITE_ROW )
{
for(u32_Count = 0; u32_Count < u32_NumCols; u32_Count++) {
  ps8_ColumnName[u32_Count] = sqlite3_column_name(pst_SearchPrepareStmt,
u32_Count);
  u8_SearchResult=sqlite3_column_text(pst_SearchPrepareStmt, u32_Count); }
u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt);
}
u32_ReturnStatus = sqlite3_finalize(pst_SearchPrepareStmt);

Kindly help to solve this.

Regards,
Sreedhar

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 04, 2007 9:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite:Deletion in Joins method

Sreedhar.a wrote:
CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); 
CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist 
Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text);


CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER 
CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE 
CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES
ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT 
fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE );
 
I created a trigger as follows .


Here I am checking after deleting that record in Music Table I will 
check wheather that Artist_id is present now in that table MUSIC i.e, 
(SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = 
OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in 
the

ARTIST table.

But this is not happening with the below trigger.
Do I need to add more constraints in the below trigger.
Please help to solve this.

"CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW
BEGIN
SELECT CASE
WHEN 
	(SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id) 
IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id'

END;
END;"


  

You need to use a conditional delete in your trigger. You can't do that
using a select with a case statement. You could try something like this
(untested):

CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW
BEGIN
  delete from Artist 
where ArtistId = old.ArtistId

and not exist (select id from music where ArtistId = old.ArtistId);
  delete from Album
where AlbumId = old.AlbumId
and not exist (select id from music where AlbumId = old.AlbumId);
  delete from BGM
where BgmId = old.BgmId
and not exist (select id from music where BgmId = old.BgmId); END;"

A couple of other things to note:

You should probably change your table definitions to key the phrase "INTEGER
PRIMARY KEY" together so that sqlite can use its btree key optimization.
Instead of this:

CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text);

use this:

CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text);

Also, if you want to speed up the searches in the deletes trigger above, at
the expense of slowing down all the insert and delete operations into the
tables, you could add indexes on the individual Id columns in the music
table.

create index MusicArtistId on Muisc(ArtistId); create index MusicAlbumId on
Muisc(AlbumId); create index MusicBgmId on Muisc(BgmId);

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-








-
To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] SQLite for NAS storage - DVR like application

[John Stanton]

Sqlite depends upon POSIX file locks.  It is no better or no worse than 
the POSIX lock implementation on your platform.


We use Sqlite in a multi-user environment without dependency oin the 
POSIX locks by embedding it in a server using HTTP when it is on a 
remote machine.  We get the small footprint and simoplicity of Sqlite 
and get no multi-user glitches regardless of platform.


Trilok Soni wrote:

Hi,

I am evaluating SQLite for the design of the surveillance camera/DVR
product based on Linux, which stores its captured
analog/IP camera streams to the NAS storage device(s). To facilitate
faster search/scanning of those media files
containing many days of videos spread over multiple files, we plan to
keep the metadata of those videos
in some file format/database stored on NAS itself, so that
search/analysis mechanism just go through this
database to locate exact file with some search criteria like
time/data/camera/alarms/events etc.

While reading the SQLite documentation I came to following FAQ, where
it lists that SQLite as application file format,
may not scale well to the NFS/Neworked attached drives due to the
locking problems.

http://www.sqlite.org/faq.html
Refer 5th question.
(5) Can multiple applications or multiple instances of the same
application access a single database file at the same time?

Is there any other way to work around this problem and use SQLite as
application file format in the above scenario. See that Linux
will run on the ARM9 having video processing done on specialized DSP.




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

Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

[John Stanton]

Joanne, there are no bad questions, only bad answers.

Use sqlite3_bind_double and your timestamp will be of type double (a 64 
bit FP number),


Joanne Pham wrote:

Hi John,
Thanks for the detail info.
I am still very new to SQLite3 and sorry for the question.
My application is used C++ to insert/select the data from this table. So if I 
defined it as
  create table mytable (
  
 createDate REAL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (remoteWXId)
);
 Then I can use sqlite3_bind_real to bind the column but what is the datatype 
that I should use in C++ code.Do you have any example code that work for C++ in 
this case.
Sorry for the question.
Thanks,
JP


- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 12:59:00 PM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.


Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
6. current_time
returns current time as HH:MM:SS
7. current_date
returns current date as -MM-DD
8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:

Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.

Thanks,
Joanne




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
   1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


   2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
   then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs


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


 

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.   http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 




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


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



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

Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

[John Stanton]

Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.


Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
   2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
   3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
   4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
   5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
   6. current_time
returns current time as HH:MM:SS
   7. current_date
returns current date as -MM-DD
   8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:

Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.

Thanks,
Joanne




- Original Message ----
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
   1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


   2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
   then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs





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


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 




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

Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

[John Stanton]

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs






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

Re: [sqlite] Re: Improving performance of SQLite. Anyone heard of DeviceSQL?

[John Stanton]

A good salesman taught me - "When you have made the sale, stop selling". 
 If you have identified "The Fox" (the hidden actual decision maker) 
and made your case with an erudite technical presentation all the rest 
is just noise, and can even be detrimental.  After all 'decent 
marketing" is measured by a simple objective test - did it sell?


Even if you sell a dud and manage to avoid breach of contract litigation 
you cannot avoid the bad publicity and rumors of "that dog don't hunt".


A. Pagaltzis wrote:

* John Stanton <[EMAIL PROTECTED]> [2007-12-15 22:55]:

Which is the better model?


False dilemma. Where there is a budget, there is no reason you
can’t have both a good product and at least decent marketing.

Even when the product isn’t good, it’s unlikely to be so useless
as to violate the terms of contract. Oracle seems to survive just
fine, say.

For the executive summary on the matter, read this short essay:

“Enterprise software” is a social, not technical, phenomenon
http://lists.canonical.org/pipermail/kragen-tol/2005-April/000772.html

Regards,



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

Re: [sqlite] SQLite and Columnar Databases

[John Stanton]

[EMAIL PROTECTED] wrote:

Joe Wilson <[EMAIL PROTECTED]> wrote:

--- [EMAIL PROTECTED] wrote:

Joe Wilson <[EMAIL PROTECTED]> wrote:
The reason why I asked is that I haven't had much luck with sqlite3 
performance for databases larger than the size of RAM on my machine

regardless of PRAGMA settings.

This is probably do to the cache locality problem.  We know how
to fix this, Joe.  Would you like to have a go at it?
I think setting aside contiguous pages in the file for exclusive use 
by each btree would help improve locality of reference on disk.


For example, let A, B and C represent in-use pages of 3 btrees and 
a, b and c represent free pages corresponding to the same btrees:


 AAaAAaAaaaBBbbBBBbbbCCcCCC

Is this what you had in mind in your post?



No.

The problem is when inserting into large database that is
indexed, the values being indexed are randomly distributed.
So with each insert, SQLite has to seek to a new random
place in the file to insert the new index entry there.
It does not matter that pages of the index are not in
consecutive order.  What matters is that each insertion
is into a different place and that the places are randomly
distributed over a large file - larger than will fit in cache.
In this situation, each new entry probably needs to go on 
a page that is not in cache, and hence a real disk

seek and read is required (as opposed to simply reading
the value out of cache) when inserting each new entry.  Disk
seeks and reads are much, much slower than disk cache hits,
which really slows down insertion performance.

If you do many inserts such that the indexed values are
in sorted order, then most inserts will go on the same page
as the previous.  The previous page is already in cache
so it doesn't need to be read from disk.  It has also
already been journaled, so no excess writing is required.
Disk I/O is greatly reduced. Things go much, much faster.
The problem is that you really have the luxury of being
able to insert entries in sorted order.  And if you are
indexing multiple columns, it is impossible to sort
the entries on both columns at once.

The usual way to work around this problem is to only do random
inserts into indices which are small enough to fit into
your disk cache.  Suppose you start inserting into index A.
Once A gets too big to fit entirely in cache, stop inserting
into it and start inserting into B.  Once B gets to be the
cache size, merge A and B together into a new index C.
The merge operation requires reading A and B straight through
from beginning to end once.  This is a lot of disk I/O but
it is still much faster than jumping around within the file
reading bits here and there.  After creating C, reset A and
B back to being empty (since all records have been transfered
into C).  Start inserting into A again until it fills up.
Then fill up B again.  Merge A and B into D, then merge C and D
into E.  Reset A, B, C, and D.  Keep doing this, merging
smaller indices into larger indices, until you insert all
the records you need to insert.  Then make a single pass
through all of the smaller indices and merge them all
together into a single big index Z.  Z becomes the new
index used for search operations on the database.

The algorithm above should probably go into SQLite 
to construct an index as part of the CREATE INDEX

statement.  When populating a database will a large
amount of data, first put all of the data into an
unindexed table.  This will be very fast because each
new entry goes at the end of the table (and also at the
end of the file.)  After all data is in place, issue
the CREATE INDEX statements to index any fields you
think need indexing.  The CREATE INDEX statement has
to populate the index.  The current algorithm is to
scan through the original table and create and insert
index entries one by one as they are encountered.  I am
proposing that you substitute the algorithm outlined in
the previous paragraph in place of the current algorithm.

--
D. Richard Hipp <[EMAIL PROTECTED]>

I experimented with that problem of building B-Tree indices on tables 
and discovered that to first build the table then build a list of keys 
into a file, sort the file using an iterative combination of quicksorts 
and merges then build the index in an optimal manner since the space 
needed for non-leaf nodes can be calculated and reserved, produced good 
performance on large tables even in a restricted memory environment.


Using this approach the data table and temporary files are only ever 
accessed sequentially and the "locality of reference" situation 
sidestepped.  If there are multiple disk drives available the 
intermediate files can be on another drive to further limit head movement.


Ensuring that interior nodes were contiguous seemed to be a winning 
strategy.  Only filling nodes to say 80% also delivered an index which 
would accept quite a lot of inserts before fragmenting with node splits 
and implementing node merging if possible instead of a 

Re: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

[John Stanton]

This also is an anecdote from some time back.  As we were signing a 
fairly significant software contract with a large organization their 
manager told us "You guys know nothing about marketing.  Your 
presentation was unprofessional, no glossy brochures, no audio visuals 
and we would not have bought except that you were the only ones who 
convinced us you could do the job".  We just smiled and watched the ink 
dry while we pondered "where did we go right?".


The simple truth is that if you hype a product and sell it into an area 
where it is inadequate your triumph is short lived and the scorn and 
litigation enduring.  On the other hand if you deliver a solution which 
works as well, or preferably better, than proposed you have generated 
raving fans who will buy again and endorse your product to all and 
sundry.  Which is the better model?


Fred Williams wrote:

This discussion reminds me of another long, long ago in a galaxy far,
far away. (When I worked on "Mainframes" with 32 K or less "core"
memory.)

Discussing the then lopsided world with my non-IBM salesman, in a local
watering hole, after a particularly trying day of dealing with
"management."  The topic was the state of the computer industry at that
time. (And yet today.)

I was complaining of managements' complete lack of ability to see the
superior to IBM technology, (IMHO) and cost effectiveness we had
installed.  That is when I learned of the non bits and bytes "real
world."  My late salesman friend said,  "Fred, don't you understand that
the computer industry is a Marketing industry based on technology, and
not a technology industry?"

Thirty years later nothing could be truer.  No matter how much things
change, they still stay the same...

Fred

Running Windoze  on a "PC".
I know, I know it should be Linux on a Mac.  But I live in the "real
world" today.
I rest my case.


-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 15, 2007 7:04 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard of
DeviceSQL?



On Dec 14, 2007, at 9:24 PM, Lynn Fredricks wrote:


That's true. A lot of those kinds of sales presentations

are correctly

targeted at decision makers that make financial decisions. I don't
consider
it a bad thing - it's really a necessity to be competitive.


My intent is  to provide complete detailed technical information
about SQLite, including its limitations and faults, and honest
comparisons and even recommendations of other products
(including, but not limited to DeviceSQL).  My intent is to avoid
sophistry, misrepresentation, exaggeration,  and hype.
This intent is sometimes imperfectly executed, but it is my goal.

If that means that SQLite is uncompetitive, then so be it.


D. Richard Hipp
[EMAIL PROTECTED]




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




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




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

Re: [sqlite] DeviceSQL

[John Stanton]

Samuel R. Neff wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 14, 2007 3:55 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL

... is it not 
directly comparable to DeviceSQL unless the external compiler handles 
not only SQL but also PL/SQL.  The addition of the command language 
allows for creating a library of data manipulation functions rather than 
just embedded SQL.  ...




John,

I'm not sure I see added value in incorporating procedural SQL in an
embedded database like SQLite or DeviceSQL.  Isn't the easily extensible
mechanism that SQLite has for creating custom functions in the host language
(C, C#, ActionScript, whatever) a fully valid replacement for whatever
procedure language could be provided through VDBE?  Also doesn't the fact
that SQLite (and any embedded db) runs in-process negate the importance of
SQL procedural code?  In my experience, stored procedures are usually used
to offload more work to the DB server and thus perform DB intensive code
closer to where the DB data is, which is not necessary when the DB is
already in-process?  


After moving from MSSQL to SQLite for our application, which previously used
stored procedures, we've never missed the functionality of TSQL and found
creating UDFs in C# for SQLite much easier and more powerful that using TSQL
UDFs, especially 'cause they run in-process and thus have full access to the
host application's objects and code.

Sam

The difference is if you are pursuing the DeviceSQL approach and 
compiling a library of data manipulation functions.  The partitioning is 
cleaner if procedural logic can be included in those functions.


In our Sqlite applications we have implemented Javascript as a DB 
procedural language by adding an Sqlite class.  You can store it in the 
DB and execute it.  You can also run Javascript scripts which manipulate 
an Sqlite DB.  When using Sqlite we see the value of a DB procedural 
language in the dynamic storage of rules rather than having rules 
chiselled in stone in the application code.  Multiple rules in 
Javascript can be integrated and compiled into one executable procedure, 
giving great flexibility in building systems in the "expert" style.


We chose Javascript for practical reasons.  It is universally used in 
WWW pages so the developers do not have any new language to learn.


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 



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




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

Re: [sqlite] create table with datatype = DATE - STORE as DATETIME or INTEGER.

[John Stanton]

If you make your date and time columns numeric (floating point) you use 
8 bytes to store a date and time.  You then use the embedded Sqlite date 
and time functions to present date and time as your application chooses.


I believe that if you declare the column as DATE Sqlite will give it an 
underlying type of numeric.  Look at Sqlite's "manifest typing" to see 
how it ignores the SQL data types and stores information in the way it 
chooses as most appropriate.


Look at date.c to get an idea of the inbuilt Sqlite date and time support.

Joanne Pham wrote:

Hi All,
Should I create the column in DATETIME or the INTEGER to store the time.
DATETIME has the value of GMT time. So I store this value as INTEGER then I 
need to convert datetime format but it will be use less space if I use the 
INTEGER. Please give me an advice.
Thanks
Joanne



- Original Message ----
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 3:05:52 PM
Subject: Re: [sqlite] create table with datatype = DATE

The type DATE is a declared type, not an actual type and has no effect u 
nless your code specifically picks it out as a declared type.


To do what you want use a trigger on insert and update the date field 
with datetime('now');


Joanne Pham wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



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


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 




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

Re: [sqlite] DeviceSQL

[John Stanton]

We did that with our products which used byte code.  Byte code compiled 
on earlier versions would run on later ones, but new code with the extra 
opcodes would not run on old interpreters.  It protected customers who 
had lost their source code or were afraid to recompile after an upgrade 
because of version control blunders.


Noah Hart wrote:

Why can't new OP codes be simply added to the end, so that the
serializations will always work?

Noah 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 14, 2007 8:20 AM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL
Importance: High

Case in point:  A new opcode has been added for the pending 3.5.4
release.  And this caused many of the other opcodes to be
automatically renumbered.  A serialization created for 3.5.3
has little hope of running on 3.5.4.

--
D. Richard Hipp <[EMAIL PROTECTED]>




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.





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




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

Re: [sqlite] DeviceSQL

[John Stanton]

Dennis, your application may not be a microwave oven but it does appear 
to be "deeply embedded" as opposed to a general purpose DB server which 
handles generic queries.


I was aware of the runtime only version of Sqlite, but is it not 
directly comparable to DeviceSQL unless the external compiler handles 
not only SQL but also PL/SQL.  The addition of the command language 
allows for creating a library of data manipulation functions rather than 
just embedded SQL.  My point was that the DeviceSQL approach could be 
used with Sqlite to produce a smaller footprint embedded product and 
that such a product has the potential of being less memory hungry than 
DeviceSQL simply by virtue of the higher information density of the VDBE 
target code compared to native machine instructions.


Dennis Cote wrote:

John Stanton wrote:


DeviceSQL is not suitable for general purpose SQL processing, unlike 
Sqlite, and should only be compared as an alternative in deeply 
embedded applications so the only useful comparison is one which looks 
like a cell phone, microwave oven or a TV set top box.




The application doesn't necessarily have to look like a microwave. :-)

It could be any application that uses a fixed set of predetermined SQL 
statements to perform its operations. My primary application does 
exactly that using SQLite with all statements prepared and cached as 
needed. It runs on a standard PC under Windows. We have no need for 
executing arbitrary, runtime generated, or user supplied SQL as many 
other applications do. Every SQL statement that can be executed is known 
before the application starts.


I can imagine that a version of Sqlite which does not include its SQL 
compiler and which uses precompiled VDBE code would provide similar 
functionality to DeviceSQL, particularly if the Sqlite compiler were 
extended to generate VDBE from PL/SQl.  I can imagine that the higher 
information density of the VDBE code could deliver the advantage =of a 
smaller memory footprint.


I thought that this already existed as a commercial product that Richard 
offered, but I can't find any reference to it on the paid support page 
at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was 
an offline compiler that ran on a PC and generated VDBE code to execute 
SQL statements, along with source code for a runtime execution engine 
that would execute those pre-compiled statements. The execution engine 
could be built for any target, and was much smaller than SQLite because 
it eliminated the parser and code generator functionality. Does this 
exist, or was I just imagining it?


Dennis Cote

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] DeviceSQL

[John Stanton]

Testing only finds bugs, it does not guarantee accuracy.  Careful design 
however can establish accuracy, and to verify that methodology requires 
examination of the source code.


James Steward wrote:

steveweick wrote:

Do you need to read the code to verify reliability as your next few
sentences seems to imply? For that to be true, the reader would have 
to be

able to spot bugs through inspection.  While that is certainly one way to
spot bugs, I seriously doubt that any shop would rely on code inspection,
when millions of dollars of potential recall costs are on the line.
  
I think many would agree that code inspections do find (serious) bugs, 
that may not show up from testing.  I'm sure your company conducts code 
inspection meetings as a part of all code development.  We (the company 
I work for) certainly do.  I know I've seen change logs that read 
something like "Fixed possible buffer overflow in foo..." for open 
source projects here and there as well.



In fact the SQLite marketing does not rely on code inspection as its
argument for why the code is reliable. Check it out.   
That would be bad if they did, I agree.  But all the testing in the 
world won't uncover all the bugs either, in a complex piece of code.  
See http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf .
"The Ptolemy II system itself began to be widely used, and every use of 
the system exercised this
code. No problems were observed until the code deadlocked on April 26, 
2004, four years later."  And that was after code inspections, 
regression tests and belt and braces programming techniques!

All of that said, I do admire the elegance of the SQLite code.  It makes
entertaining reading.  Unfortunately elegance does not translate into
performance or reliability.
  
Not necessarily, but it often does, and can make for better 
maintainability too.  I've not trawled through to SQLite code myself, so 
couldn't comment.  But it does have quite a few big name users, and an 
active and helpful user forum, which gives me good vibes at least.


Cheerio,
James.

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] DeviceSQL

[John Stanton]

I unfortunately missed the Encirq webinar thanks to a project commitment 
but have taken the time to download the Encirq demo and try to make good 
the loss.  It has some user examples in source code which give an idea 
of how it functions, but the information on the product is sparse so it 
was not possible to get an idea of the mechanics of indices, paging etc.


What I saw was a well conceived product to build embedded software.  It 
seems to be a compiler which transforms Encirq's version of PL/SQL into 
C statements which are then compiled into a library of data manipulation 
functions for use in the application.  The demo uses gcc.  Encirq has a 
means of including "storage modules" to handle different forms of 
persistent storage.  DeviceSQL appears to handle transactions and 
rollbacks.  There is no information I could find about ACID functionality.


I shall prepare some benchmarks against Sqlite once I figure out a 
suitable method.  Since DeviceSQl has no SQL compiler the Sqlite will 
need to have prepared statements and binding to provide an apples to 
apples comparison.  The Encirq introductory application example is by 
necessity trivial and small and not suited to a benchmark.


DeviceSQL is not suitable for general purpose SQL processing, unlike 
Sqlite, and should only be compared as an alternative in deeply embedded 
applications so the only useful comparison is one which looks like a 
cell phone, microwave oven or a TV set top box.


I can imagine that a version of Sqlite which does not include its SQL 
compiler and which uses precompiled VDBE code would provide similar 
functionality to DeviceSQL, particularly if the Sqlite compiler were 
extended to generate VDBE from PL/SQl.  I can imagine that the higher 
information density of the VDBE code could deliver the advantage =of a 
smaller memory footprint.


For Steve Weick - I note your very strong resume and would imagine that 
your comprehensive experience would lead you to introduce a less 
secretive policy as to revealing the capabilities of your product.


steveweick wrote:

Good  idea... I'll pass it along to the right folks. Meanwhile, if anyone has
further questions or comments, please feel free to write me here (if they
think the group would be interested) or at [EMAIL PROTECTED]

Steve

I would like to recommend that Encriq create a forum or mailing list of 
their own for those who are interesting in learning more.  For me, what 
might be an interesting product is quickly being overshadowed by this 
thread.






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

Re: [sqlite] create table with datatype = DATE

[John Stanton]

The type DATE is a declared type, not an actual type and has no effect u 
nless your code specifically picks it out as a declared type.


To do what you want use a trigger on insert and update the date field 
with datetime('now');


Joanne Pham wrote:

Hi All,
I create the table as :
create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



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

Re: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

[John Stanton]

[EMAIL PROTECTED] wrote:

Ion Silvestru <[EMAIL PROTECTED]> wrote:

SW: Richard,  We have written to you directly before to ask you to stop the
FUD and incorrect statements, and you have chosen to continue. I suggest you
not waste everyone's time by circulating deliberately misleading
information.

I think you are very aggressive and I think you must apologise to, not
only Richard, but to us (just see previous messages about DeviceSQL,
full of suppositions).



Thanks for posting, Ion.  I too found Steve's remarks to be
rather insolent.  But I was just going to let it go.  Seeing
your response was an encouragement to me since it shows me
that I am not the only one who feels that way.  Thanks!

Unfortunately, Steve Weick might not see your comment
since he appears to have unsubscribed from the mailing list
immediately after sending his inflammatory missive.


These were no "FUD and incorrect statements", nor "misleading
information", these were only suppositions, and this is because it's
hard to find real technical information or specifications on DeviceSQL, only
marketing information. Maybe DeviceSQL is a good product, but absence
of real info and abundance of marketing make us think and suppose
various things (just see previous messages).

All of us are waiting for what Richard stated:
"If you view their web presentation and/or try out Encirq's
products, I would be very interested to hear your impressions.
Even better would be if you could blog about it."

Even better if all of us can have access to this web presentation, to
find out maybe more technical info about DeviceSQL.

Any way, thank you.



--
D. Richard Hipp <[EMAIL PROTECTED]>


Methinks he doth protest too much.

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

Re: [sqlite] Occasional Database is Locked Error

[John Stanton]

Your application needs to handle the synchronization logic since there 
is no DB server to do it for you.  When you get a busy check you can 
pause for a short time and relaunch the query.


Mark Riehl wrote:

I'm running SQLite 3.4.1 under Linux.  I have a C++ application that
inserts records into the database.

During testing, I lauch the C++ app and I start sqlite3 from a console
and open the database that is being written to by the C++ application.
 I perform selects using sqlite3 (no inserts) to look at the collected
data and occasionally get "Error: database is locked".  Sometimes get
the error a few times in a row, other times, it doesn't happen at all.

I'm not inserting a lot of records - worst case, there are ~25 records
per second.

Am I just getting lucky and issuing selects at the same time I'm
writing from the other process?  I'm planning on using a web-based app
to periodically poll the database (to provide summary information)
while the C++ app performs the inserts.  What is the best way to
reduce/minimize the lock issue?

Thanks for the help,
Mark

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




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

Re: [sqlite] DeviceSQL

[John Stanton]

[EMAIL PROTECTED] wrote:

John Stanton <[EMAIL PROTECTED]> wrote:
I received an email promoting a DeviceSQL web presentation.  It 
specifically targets Sqlite and promises 5X performance.




If you view their web presentation and/or try out Encirq's
products, I would be very interested to hear your impressions.
Even better would be if you could blog about it.

Encirq has for years been running Google Adsense ads claiming
to be 20x faster than SQLite.  (Dunno why they have now reduced
that claim to 5x faster.)  But I have never yet seen an
independent confirmation of this.  Nor even have I been able
to find anybody who is actually using DeviceSQL in a product.
Web searches turn up nothing but marketing literature coming
directly or indirectly from Encirq.  Some independent analysis
(regardless of whether it is favorable or unfavorable to SQLite)
would be appreciated.

My understanding of DeviceSQL is:

   *  It is NOT transactional.  There is no such thing as ROLLBACK.
   *  If you lose power during a write, your database is toast.
   *  If your database schema changes, you have to recompile
  your application.
   *  The database file format changes depending on the schema.
   *  DeviceSQL is not a general-purpose database engine.  You
  compile SQL statements into C code on a development
  workstation, then compile the C code for your embedded
  device.

I can imagine circumstances where the DeviceSQL approach,
while much less flexible and forgiving than SQLite, might
be a better way to go, depending on what you are trying to
do.  But I have not gotten good vibes from Encirq as a 
company.  And I have no idea how reliable the DeviceSQL 
product is.  I would really appreciate your thoughts on 
that subject.


--
D. Richard Hipp <[EMAIL PROTECTED]>

Your earlier description of DeviceSQL intrigued me.  In general claims 
of "20x" or even "5x" imply either serious deficiencies in the compared 
product or a generous dose of snake oil in the challenger.  Since we 
know the Sqlite code and use it without encountering serious 
deficiencies, I smell snake, but shall look at the presentation and ask 
some questions if possible and report back.


Like you, I have been unable to uncover any concrete documentation or 
reviews of this product.


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

[sqlite] DeviceSQL

[John Stanton]

I received an email promoting a DeviceSQL web presentation.  It 
specifically targets Sqlite and promises 5X performance.


For those interested -

DeviceSQL vs. SQLite: Which Gets You the Most Efficient Embedded Database?

DATE: Thursday, December 13th, 2007
TIME:  Noon PST, 3:00 PM EST
DURATION: 50 minutes + Q & A

Register:  
http://seminar2.techonline.com/registration/distrib.cgi?s=1191=1700

Who Should Attend: Software Engineers, Software Architects, Software 
Engineering Managers

Webinar Overview

Reliably processing, searching and managing growing amounts of data is driving 
many embedded developers to use third-party data management software such as 
DeviceSQL or SQLite. However, a critical issue for these technologies is their 
efficiency in terms of performance and memory usage, especially when they’re 
being used to replace hand-coded databases in resource-constrained systems.

While SQLite is widely-known, many users are frustrated by not being able to 
meet stringent performance and/or memory size goals with SQLite, particularly 
in applications with a sub-2 GHz CPU. In this webinar you’ll learn how 
DeviceSQL, a next-generation technology for managing data, provides 5x the 
performance of SQLite while yielding a smaller memory footprint. Also, a 
DeviceSQL user, TV Guide, will share how they were able to meet their 
aggressive design goals for managing electronic program guide (EPG) data. This 
seminar is for engineers who are currently using SQLite or anyone considering 
an embedded database technology.


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