[sqlite] Changing the actual data contained based on calculation...
Greetings and salutations! Ok, the subject is obscure, so let me explain... I am already doing this programmatically, but, but I would like to have SQLite do it instead. Let us imagine the following table: CREATE TABLE PMTime (id integer primary key, rec integer, date, secs integer); sqlite> select * from PMTime; 3545|7086|2008-12-18|1800 3546|7163|2008-12-18|1800 3547|7164|2008-12-18|1800 3548|7165|2008-12-18|1800 3549|7166|2008-12-18|1800 3550|7167|2008-12-18|3600 3554|3920|2008-12-18|1800 3555|3920|2008-12-19|28800 3556|5180|2008-12-22|1800 3559|8015|2008-12-22|1800 3560|8017|2008-12-22|1800 3561|4172|2008-12-22|23400 3562|7582|2008-12-23|5400 3563|7581|2008-12-23|5400 3564|6610|2008-12-23|3600 3565|7426|2008-12-23|7200 3570|8069|2008-12-23|7200 3580|4172|2008-12-24|28800 3581|1101|2008-12-25|28800 3582|1101|2008-12-26|28800 3597|7840|2008-12-29|7200 3600|8069|2008-12-29|7200 3602|4172|2008-12-29|14400 3603|3920|2008-12-30|14400 3604|8136|2008-12-30|10800 3605|1241|2008-12-30|3600 3606|1241|2008-12-31|14400 3607|2133|2008-12-31|14400 3608|1101|2009-01-01|28800 3610|1265|2009-01-02|28800 3611|8182|2009-01-05|14400 3612|8183|2009-01-05|14400 3613|8142|2009-01-06|1800 3614|8141|2009-01-06|1800 3642|8244|2009-01-06|1800 3643|8261|2009-01-06|14400 3644|6975|2009-01-06|9000 3645|8244|2009-01-07|3600 3657|7144|2009-01-07|1800 3658|7145|2009-01-07|1800 3659|7146|2009-01-07|1800 3660|7151|2009-01-07|1800 3661|7152|2009-01-07|3600 3662|7153|2009-01-07|1800 3663|8182|2009-01-07|5400 3664|7154|2009-01-07|1800 3665|7155|2009-01-07|1800 3666|7027|2009-01-07|1800 3667|7183|2009-01-07|1800 3668|7184|2009-01-08|1800 3669|7185|2009-01-08|1800 3670|7186|2009-01-08|1800 3671|8261|2009-01-08|14400 3672|7037|2009-01-08|1800 3673|7194|2009-01-08|1800 3674|7195|2009-01-08|1800 3675|7193|2009-01-08|3600 3676|7196|2009-01-09|1800 3682|8183|2009-01-09|12600 3685|7311|2009-01-09|1800 sqlite> What the program does is to keep track that the secs will not add to more than 28800 for any date. If it does, a new PMTime record is added with the rest of the secs that are over 28800 and assigned to the next date that is not Saturday or Sunday and to the same rec. I have been looking into triggers, but I can not even come up with anything close. Any help would be greatly appreciated. thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory SQLite
use ":memory:" as the path of sqlite file when invoke sqlite3_open. 2009/1/22 Ionut > Hi, > > I am trying to use SQLite in a new operating system. > Right now we don't support a large functionality > Is there a way to make SQLite to work entirely into memory? > (meaning that everything should be on main memory, from journals, > logs...) > > Regards, > Ionut > > ___ > 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 JDBC driver performance
are you running the pure version of the sqlite driver? On Tue, Jan 20, 2009 at 3:10 AM, Christopher Mason < christopher.ma...@proteomesoftware.com> wrote: > [Apologies for posting this here, but the sqlitejdbc list appears to be > defunct.] > > Hello. > > I'm noticing fairly severe performance difference between running a > complex query from the sqlite 3.6.7 command line and from within the > sqlite jdbc driver version 0.54 available at [1]. > > Sqlite command line: > real0m44.708s > user0m5.211s > sys 0m1.994s > > java: > 2.7min > > I've tried a number of things: > > * I've tried both sqlite 3.6.1 and 3.6.7 (I upgraded the jdbc driver > to .7); > * I've tried with and without the shared_cache option; this has no > effect for me. > * I've tried changing the optimization settings for sqlitejdbc to > match those of the command line tool I build and installed (-O2). > * Explicitly set the cache size of the > > > I've done some profiling [2] and the prevalence of > 'sqlite3_enable_shared_cache' in the java version is surprising. The > java version also uses significantly less CPU than the command line > version. > > I've copied and pasted the SQL out of the prepared statement, so I'm > confident that I'm running the same SQL. It's a single query, so I > doubt that JDBC/JNI overhead is to blame. > > Any help or ideas would be most appreciated! > > Thanks so much, > > -c > > > [1] http://zentus.com/sqlitejdbc/ > > > [2] > > Profiling with shark on Mac OS X 10.5.6: > > java: >self total > 0.0% 50.2% sqlite3_step > 5.5% 48.4%sqlite3_blob_open > 9.2% 40.0% sqlite3_randomness > 8.4% 25.1% sqlite3_enable_shared_cache > 2.1% 16.0% sqlite3_config > 5.4% 7.6% sqlite3_malloc > 1.1% 1.2% sqlite3_config > 0.0% 0.1% sqlite3_free > 0.1% 0.1%sqlite3_vfs_unregister > 0.8% 1.0% sqlite3_mutex_try > 0.1% 0.1% sqlite3_mutex_leave > 0.2% 3.3% sqlite3_value_type > 0.9% 2.7% sqlite3_os_end > 0.2% 0.2% sqlite3_mutex_try > 0.1% 0.1% sqlite3_snprintf > 0.3% 0.3%sqlite3_free > 0.2% 0.2%sqlite3_malloc > 0.1% 0.1%sqlite3_snprintf > > command line: > self total > 0.0% 64.1% sqlite3_exec > 0.0% 64.0% sqlite3_step > 8.6% 63.3% sqlite3Step > 0.3% 21.0%sqlite3VdbeCursorMoveto > 6.0% 20.6% sqlite3BtreeMovetoUnpacked > 0.3% 10.6% moveToChild > 1.0% 10.2% getAndInitPage > 0.5% 5.1% sqlite3PcacheFetch > 3.0% 4.5% pcache1Fetch > 0.6% 0.9%pthreadMutexEnter > 0.2% 0.2% dyld_stub_pthread_self > 0.1% 0.1% dyld_stub_pthread_mutex_lock > 0.2% 0.2%pcache1PinPage > 0.2% 0.2%sqlite3_mutex_leave > 0.1% 0.1%sqlite3_mutex_enter > 0.1% 0.1%pthreadMutexLeave > 0.1% 0.1%dyld_stub_pthread_mutex_unlock > 0.1% 0.1% pthreadMutexLeave > 0.0% 3.3% sqlite3BtreeGetPage > 0.3% 0.3% btreePageFromDbPage > 0.0% 0.2% pagerPagecount > 0.1% 0.1% sqlite3BtreeInitPage > 0.1% 0.1% sqlite3PagerGetData > 0.1% 0.1% sqlite3PagerGetExtra > 0.1% 0.1% btreePageFromDbPage > 0.1% 0.1% sqlite3PcacheFetch > 0.3% 2.0%moveToRoot > 1.9% 1.9%sqlite3GetVarint > 0.1% 0.1%sqlite3Get4byte > 0.1% 0.1%sqlite3PagerUnref > 0.1% 0.1% sqlite3GetVarint > 0.1% 0.1% sqlite3Get4byte > 1.5% 15.1%sqlite3BtreeMovetoUnpacked > > > > -- > Christopher Mason Proteome Software(503) 244-6027 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim Dodgen j...@dodgen.us ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 - operator
Thanks Dan, didn't realize that NOT completely replaced -, thought you could
use them side by side.
All OK now
Mike
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan
> Sent: 21 January 2009 17:14
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FTS3 - operator
>
>
> On Jan 21, 2009, at 11:43 PM, Mike Marshall wrote:
>
> > Hi all
> >
> >
> >
> > Quick question
> >
> >
> >
> > Should the - operator work in FTS in conjunction with the phrase
> > operator
> >
> >
> >
> > i.e. router -"ip address"
> > sqlite3_step returns SQLITE_ERROR which leads me to believe its
> > invalid but
> > the question is should it be invalid, it seems a perfectly valid use
> > of the
> > syntax to me.
>
> Hi,
>
> I don't think that has ever worked. The documentation has:
>
>A token that is not part of a quoted phrase may be preceded by a '-'
>character, which indicates that matching rows must not contain the
>specified term.
>
> See here for more:
>
>http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/
> README.syntax
>
> Also, new versions of Fts3 support two different syntaxes. The legacy
> syntax that is enabled by default and the enhanced syntax that is
> enabled by defining SQLITE_ENABLE_FTS3_PARENTHESIS at compile time.
>
> The enhanced syntax does not support the '-' operator at all. Instead,
> it uses the binary operator NOT. When using the enhanced syntax, you
> can do:
>
> MATCH 'router NOT "ip address"'
>
> and things will work as expected.
>
> Dan.
>
>
>
>
> >
> >
> >
> >
> > My code is
> >
> >
> >
> > sqlite3_stmt* pStatement;
> >
> > char* acSQLQuery = sqlite3_mprintf("SELECT * FROM data WHERE story
> > MATCH
> > '%q'",acQuery);
> >
> > nRet = sqlite3_prepare_v2(pDB,acSQLQuery,-1,&pStatement,0);
> >
> > nRet = sqlite3_step(pStatement);
> >
> >
> >
> > Thanks for any help or pointers, or even just letting me know it's a
> > bug so
> > I can raise a ticket.
> >
> >
> >
> > Mike
> >
> >
> >
> >
> >
> > ___
> > 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] SQLITEBUSYTIMEOUT
Some of your writes may be failing with SQLITE_BUSY or SQLITE_LOCKED if others take too long to complete. If this is happening, you should retry the write until it succeeds or increase your busy timeout (or both). Cheers, Dave. -Original Message- From: SATISH [mailto:g.satis...@gmail.com] Sent: 21 January 2009 01:38 To: 'General Discussion of SQLite Database' Subject: [sqlite] SQLITEBUSYTIMEOUT Hi! I am having 10 threads in my application by default my application uses UTF-16, where I use the 10 threads to open the same sqlite file and write in 10 different tables simultaneously.these all threads write around 7000 records in 10 different tables. While a thread opens the connection I will set the SQLite Busy timeout to 60 seconds. Each thread will sleep until their turn comes and writes into database.After Complete execution of 10 threads . I have found 12 records which were not written into sqlite file out of 7000 Records. Can U Please tell me Why this loss of Information is occurring. Regards G.Satish. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In memory SQLite
Hi, I am trying to use SQLite in a new operating system. Right now we don't support a large functionality Is there a way to make SQLite to work entirely into memory? (meaning that everything should be on main memory, from journals, logs...) Regards, Ionut ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 - operator
On Jan 21, 2009, at 11:43 PM, Mike Marshall wrote:
> Hi all
>
>
>
> Quick question
>
>
>
> Should the - operator work in FTS in conjunction with the phrase
> operator
>
>
>
> i.e. router -"ip address"
> sqlite3_step returns SQLITE_ERROR which leads me to believe its
> invalid but
> the question is should it be invalid, it seems a perfectly valid use
> of the
> syntax to me.
Hi,
I don't think that has ever worked. The documentation has:
A token that is not part of a quoted phrase may be preceded by a '-'
character, which indicates that matching rows must not contain the
specified term.
See here for more:
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/
README.syntax
Also, new versions of Fts3 support two different syntaxes. The legacy
syntax that is enabled by default and the enhanced syntax that is
enabled by defining SQLITE_ENABLE_FTS3_PARENTHESIS at compile time.
The enhanced syntax does not support the '-' operator at all. Instead,
it uses the binary operator NOT. When using the enhanced syntax, you
can do:
MATCH 'router NOT "ip address"'
and things will work as expected.
Dan.
>
>
>
>
> My code is
>
>
>
> sqlite3_stmt* pStatement;
>
> char* acSQLQuery = sqlite3_mprintf("SELECT * FROM data WHERE story
> MATCH
> '%q'",acQuery);
>
> nRet = sqlite3_prepare_v2(pDB,acSQLQuery,-1,&pStatement,0);
>
> nRet = sqlite3_step(pStatement);
>
>
>
> Thanks for any help or pointers, or even just letting me know it's a
> bug so
> I can raise a ticket.
>
>
>
> Mike
>
>
>
>
>
> ___
> 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] Date datatype
On Wed, Jan 21, 2009 at 11:30:58AM -0500, Igor Tandetnik wrote:
> One advantage of the string format is that it's visible to the "naked
> eye" - when working with the database using generic tools (e.g. for
> administration or troubleshooting). It's a pain to run ad-hoc queries
> when the database stores dates as, say, Julian days (which just look
> like huge numbers, all alike).
And using international date format also allows you to use
LIKE/GLOB/REGEXP to efficiently express BETWEEN:
SELECT ... FROM ... WHERE tstamp LIKE '2007-08-%' ...;
Expressing the same using seconds since the Unix epoch is more
complicated:
SELECT ... FROM ... WHERE tstamp BETWEEN
strftime('%s', '2007-08-01') AND
strftime('%s', '2007-08-31') ...;
OTOH, using seconds since the Unix epoch too makes some computations
simpler. You could always store timestamps both ways.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 - operator
Hi all
Quick question
Should the - operator work in FTS in conjunction with the phrase operator
i.e. router -"ip address"
sqlite3_step returns SQLITE_ERROR which leads me to believe its invalid but
the question is should it be invalid, it seems a perfectly valid use of the
syntax to me.
My code is
sqlite3_stmt* pStatement;
char* acSQLQuery = sqlite3_mprintf("SELECT * FROM data WHERE story MATCH
'%q'",acQuery);
nRet = sqlite3_prepare_v2(pDB,acSQLQuery,-1,&pStatement,0);
nRet = sqlite3_step(pStatement);
Thanks for any help or pointers, or even just letting me know it's a bug so
I can raise a ticket.
Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Paolo Pisati wrote: > Igor Tandetnik wrote: >> If you use -MM-DD format consistently, then simple string >> comparison just happens to give the same results as date comparison. >> > i'll take this route, but is it the best choice performance-wise? Two other good choices well supported by built-in functions are to store a Julian day as a floating point value, or a number of seconds since Unix epoch as integer. Comparison is probably slightly faster on these than with strings (but I suggest you test it to make sure, if you believe performance is critical). If you need to do a lot of date arithmetic like getting the next day, then numeric formats will probably also be faster and easier (you could simply add 1 or 86400 to existing value). But if you need calculations like "same date next month", then you would have to go through strftime anyway, which I suspect works about the same for all formats (but again - if it matters, test). One advantage of the string format is that it's visible to the "naked eye" - when working with the database using generic tools (e.g. for administration or troubleshooting). It's a pain to run ad-hoc queries when the database stores dates as, say, Julian days (which just look like huge numbers, all alike). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Igor Tandetnik wrote: > If you use -MM-DD format consistently, then simple string comparison > just happens to give the same results as date comparison. > i'll take this route, but is it the best choice performance-wise? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
It depends on the purpose for which you are using SQLite. It sure makes it ugly trying to convert an existing application TO SQLite. Oracle-like TO_DATE and TO_CHAR functions would be a big help. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, January 21, 2009 10:57 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date datatype Hoover, Jeffrey wrote: > You are comparing the values as strings. > > Instead, format your dates as -MM-DD and use the date function to > convert strings to dates for comparison: If you use -MM-DD format consistently, then simple string comparison just happens to give the same results as date comparison. 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
Re: [sqlite] Date datatype
Hoover, Jeffrey wrote: > You are comparing the values as strings. > > Instead, format your dates as -MM-DD and use the date function to > convert strings to dates for comparison: If you use -MM-DD format consistently, then simple string comparison just happens to give the same results as date comparison. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
that's what date masks are for.. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, January 21, 2009 10:56 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date datatype Hoover, Jeffrey wrote: > Seems the date function could use a lot of work. > > Be nice if it understood some other formats, too, such as 02-JAN-09 Is that January 2nd, 2009 or January 9th, 2002? > or 11/17/2004... If it were 11/12/2004 instead, would it be December 11th or November 12th? 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
Re: [sqlite] Date datatype
Hoover, Jeffrey writes: > ... > Be nice if it understood some other formats, too, such as 02-JAN-09 or > 11/17/2004... But both those two examples are potentially ambiguous !! MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Hoover, Jeffrey wrote: > Seems the date function could use a lot of work. > > Be nice if it understood some other formats, too, such as 02-JAN-09 Is that January 2nd, 2009 or January 9th, 2002? > or 11/17/2004... If it were 11/12/2004 instead, would it be December 11th or November 12th? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on in-memory database
If I put a memory size limit such as SQLITE_MEMORY_SIZE = 8388608 (8MB), is a database created with the :memory: argument created within this chunk of memory, or external? Is it possible to save and load the in-memory database to disk (or in my case, serial or NAND flash)? Thanks! - M Feller -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Sorry about my previous post. I was wrong. It appears that the DATE
function is exceptionally rigid.
You MUST use 2-digit months and 2-digits days, using a leading zero for
values < 10. Probably need a 4-digit year, too.
Seems the date function could use a lot of work.
Be nice if it understood some other formats, too, such as 02-JAN-09 or
11/17/2004...
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hoover, Jeffrey
Sent: Wednesday, January 21, 2009 10:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date datatype
You are comparing the values as strings.
Instead, format your dates as -MM-DD and use the date function to
convert strings to dates for comparison:
select date from envelope where date > date('2009-01-20') limit 3;
here are some examples:
sqlite> select date('2009-07-01') where
date('2009-07-01')>date('2009-06-01');
2009-07-01
sqlite> select date('2009-07-01') where
date('2009-07-01')mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati
Sent: Wednesday, January 21, 2009 6:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Date datatype
It seems i'm having an hard time with dates in sqlite:
sqlite> .schema
CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT
NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL,
`delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL,
`rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL,
`user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res`
int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL,
`vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL);
sqlite> select date from envelope where date > '2009/01/20' limit 3;
2009/1/7
2009/1/7
2009/1/7
why?
--
bye,
P.
___
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] Date datatype
You are comparing the values as strings.
Instead, format your dates as -MM-DD and use the date function to
convert strings to dates for comparison:
select date from envelope where date > date('2009-01-20') limit 3;
here are some examples:
sqlite> select date('2009-07-01') where
date('2009-07-01')>date('2009-06-01');
2009-07-01
sqlite> select date('2009-07-01') where
date('2009-07-01')mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati
Sent: Wednesday, January 21, 2009 6:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Date datatype
It seems i'm having an hard time with dates in sqlite:
sqlite> .schema
CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT
NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL,
`delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL,
`rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL,
`user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res`
int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL,
`vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL);
sqlite> select date from envelope where date > '2009/01/20' limit 3;
2009/1/7
2009/1/7
2009/1/7
why?
--
bye,
P.
___
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] problems with importing data from c
Thank You !!
i get it now!
robert
Igor Tandetnik wrote:
>
> "baxy77bax" wrote in
> message news:21581675.p...@talk.nabble.com
>> the problem is that i have this situation:
>>
>> /*global*/
>> /*for sqlite */
>> sqlite3 *db;
>> sqlite3_stmt* insert_stmt;
>>
>> /*finito sqlite*/
>>
>> // function :
>>
>> Pr (VoidPtr ptr){
>>
>> // 3 variables char a1, int a2, float a3->u,
>>
>> sqlite3_prepare(db, "insert into NRP(a1, a2, a3) values ("what do
>> i write here to get values from a1,a2,a3->u into my table-");",-1,
>> &insert_stmt, NULL);
>
> You write
>
> "insert into NRP(a1, a2, a3) values (?, ?, ?);"
>
> Each question mark is one parameter, you need one for each value being
> inserted. For more details, see http://sqlite.org/c3ref/bind_blob.html
>
>> sqlite3_bind_int(insert_stmt, 1, 42);
>
> Given your column types, you do
>
> sqlite3_bind_text(insert_stmt, 1, "some text", -1, SQLITE_TRANSIENT);
> sqlite3_bind_int(insert_stmt, 2, 42);
> sqlite3_bind_double(insert_stmt, 3, 42.0);
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
View this message in context:
http://www.nabble.com/problems-with-importing-data-from-c-tp21581675p21583834.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Sqlite has no date type. Use a floating point number and the Sqlite date functions. Add your own ones to get extra functionality. Paolo Pisati wrote: > It seems i'm having an hard time with dates in sqlite: > > sqlite> .schema > CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT > NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, > `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, > `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, > `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` > int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, > `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); > > sqlite> select date from envelope where date > '2009/01/20' limit 3; > 2009/1/7 > 2009/1/7 > 2009/1/7 > > why? > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ambiguous columns with natural join
On Wed, Jan 21, 2009 at 07:40:53AM -0500, Igor Tandetnik wrote: > > Can you just write "select a.c from a natural join b;" ? Of course, but the DB-application is already written and is used by other DBs also. The application design currently needs the column to be unique without the need to specify the table. According to all the descriptions and documentations I can access, the table-qualifier of a natural-join column is not needed and sometimes even considered a syntax error. What is the statement of the sqlite-developers about that topic? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ambiguous columns with natural join
"Emil Obermayr" wrote in message news:20090121123739.gc15...@nobswolf.info > I am not sure what the standard says, but I am used to single columns > after a natural join. So if table a and b are joined through column c > the following statement is valid: > > select c from a natural join b; > > But I get "SQL error: ambiguous column name: c" > > Can this behaviour be changed? Can you just write "select a.c from a natural join b;" ? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with importing data from c
"baxy77bax" wrote in
message news:21581675.p...@talk.nabble.com
> the problem is that i have this situation:
>
> /*global*/
> /*for sqlite */
> sqlite3 *db;
> sqlite3_stmt* insert_stmt;
>
> /*finito sqlite*/
>
> // function :
>
> Pr (VoidPtr ptr){
>
> // 3 variables char a1, int a2, float a3->u,
>
> sqlite3_prepare(db, "insert into NRP(a1, a2, a3) values ("what do
> i write here to get values from a1,a2,a3->u into my table-");",-1,
> &insert_stmt, NULL);
You write
"insert into NRP(a1, a2, a3) values (?, ?, ?);"
Each question mark is one parameter, you need one for each value being
inserted. For more details, see http://sqlite.org/c3ref/bind_blob.html
> sqlite3_bind_int(insert_stmt, 1, 42);
Given your column types, you do
sqlite3_bind_text(insert_stmt, 1, "some text", -1, SQLITE_TRANSIENT);
sqlite3_bind_int(insert_stmt, 2, 42);
sqlite3_bind_double(insert_stmt, 3, 42.0);
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ambiguous columns with natural join
I am not sure what the standard says, but I am used to single columns after a natural join. So if table a and b are joined through column c the following statement is valid: select c from a natural join b; But I get "SQL error: ambiguous column name: c" Can this behaviour be changed? Will it be changed in the future? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
On 21/01/2009 10:09 PM, Paolo Pisati wrote: > It seems i'm having an hard time with dates in sqlite: > > sqlite> .schema > CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT > NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, > `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, > `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, > `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` > int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, > `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); > > sqlite> select date from envelope where date > '2009/01/20' limit 3; > 2009/1/7 > 2009/1/7 > 2009/1/7 > > why? Because "1/7" > "01/20" ... think about it, "1" > "0" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
I had a tough time myself with dates. Hope the following helps. Dates are actually stored in SQLite in -MM-DD format, with leading 0 and they are stored as strings. What helped me is that I do the date comparison as is in the database - even though they are stored as strings the format is consistent, hence 2009-01-21. > 2009-01-20 If I need to display the date it is a simple matter to convert the string to MM/DD/. --Original Message-- From: Paolo Pisati Sender: sqlite-users-boun...@sqlite.org To: General Discussion of SQLite Database ReplyTo: General Discussion of SQLite Database Sent: Jan 21, 2009 06:50 Subject: Re: [sqlite] Date datatype MikeW wrote: > > It's a string comparison, '2009/' is identical in each case > but '1/7' > '01/20' since '1' > '0' > i suspected it, ok. > See also http://www.sqlite.org/lang_datefunc.html > that page shows how to convert date in different formats: does that mean that i have first to convert the date in a unix timestamp and then do a numercial comparison? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Timothy A. Sawyer, CISSP Managing Director MBD Solutions Phone: (603) 546-7132 Web: http://www.mybowlingdiary.com Email: tsaw...@mybowlingdiary.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problems with importing data from c
hi,
the problem is, i'm trying to modify some c script but i'm not originally c
programmer, so c is not my strong side .
the problem is that i have this situation:
/*global*/
/*for sqlite */
sqlite3 *db;
sqlite3_stmt* insert_stmt;
/*finito sqlite*/
// function :
Pr (VoidPtr ptr){
// 3 variables char a1, int a2, float a3->u,
sqlite3_prepare(db, "insert into NRP(a1, a2, a3) values ("what do i
write here to get values from a1,a2,a3->u into my table-");",-1,
&insert_stmt, NULL);
sqlite3_bind_int(insert_stmt, 1, 42);
sqlite3_step(insert_stmt);
sqlite3_finalize(insert_stmt);
}
so what i want to do is to insert these three values a1, a2, a3->u into my
columns a1,a2,a3
how do i do that
if there are any more information you need from me just ask
thank you
robert
--
View this message in context:
http://www.nabble.com/problems-with-importing-data-from-c-tp21581675p21581675.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
MikeW wrote: > > It's a string comparison, '2009/' is identical in each case > but '1/7' > '01/20' since '1' > '0' > i suspected it, ok. > See also http://www.sqlite.org/lang_datefunc.html > that page shows how to convert date in different formats: does that mean that i have first to convert the date in a unix timestamp and then do a numercial comparison? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to port sqlite to uc/os
YIN HUI-XIAN-BHMJ64 writes: > > Hi all: >I want to port sqlite to uc/os without FS supporting. >Is it extremely difficult? > Can someone do me a favor? > Tks. > ___ > sqlite-users mailing list > sqlite-us...@... > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Have a look here: http://www.sqlite.org/cvstrac/wiki?p=HowToCompile As long as you have the compilers etc. it should not be too hard. MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Paolo Pisati writes: > > It seems i'm having an hard time with dates in sqlite: > > sqlite> .schema > CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT > NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, > `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, > `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, > `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` > int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, > `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); > > sqlite> select date from envelope where date > '2009/01/20' limit 3; > 2009/1/7 > 2009/1/7 > 2009/1/7 > > why? > It's a string comparison, '2009/' is identical in each case but '1/7' > '01/20' since '1' > '0' See also http://www.sqlite.org/lang_datefunc.html MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date datatype
It seems i'm having an hard time with dates in sqlite: sqlite> .schema CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); sqlite> select date from envelope where date > '2009/01/20' limit 3; 2009/1/7 2009/1/7 2009/1/7 why? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITEBUSYTIMEOUT
Hi! I am having 10 threads in my application by default my application uses UTF-16, where I use the 10 threads to open the same sqlite file and write in 10 different tables simultaneously.these all threads write around 7000 records in 10 different tables. While a thread opens the connection I will set the SQLite Busy timeout to 60 seconds. Each thread will sleep until their turn comes and writes into database.After Complete execution of 10 threads . I have found 12 records which were not written into sqlite file out of 7000 Records. Can U Please tell me Why this loss of Information is occurring. Regards G.Satish. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
