[sqlite] Regarding Memory Database

[shankar m]

Hi,

I am using SQLite in a embedded system which has the following databases
1. 64MB Flash stored Database for persistent storage
2. 2 MB In-Memory Database.

When the Memory Database is closed the 2MB should be returned to the
operating system. The 2 MB will be reallocated when the system wants to use
the mem db. SQLite currently does not support freeing of allocated memory
unless it is shutdown.


To implement the above requirement I am planning to do as follows

In SQLite Page cache module i.e pcahe1.c handles the datbase buffering of
both persistent storage and in-memory storage.
To identify whether the database is mem or file there is a flag bPurgeable
in struct PCache1.

The pages for cache are allocated in functions
1. pcache1AllocPage
2. pcache1FreePage

Add 2 new configuration variables
   1. SQLITE_CONFIG_MEMDB - Enables separate memory DB with the default
values
   2. SQLITE_CONFIG_MEMSIZE- Configures the mem db with user specified
memory block. Similar to the config variable
  SQLITE_CONFIG_PAGECACHE

Using bPurgeable in pcache1AllocPage identify the mem db, If memdb then use
the mem block specified by the user.
The algorithm for the page allocation will be same as the list
implementation present for the SQLITE_CONFIG_PAGECACHE

Please provide comments or suggestion.

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

Re: [sqlite] Forensics data control

[Simon Slavin]

On 26 Aug 2009, at 4:28am, Gene Allen wrote:

> Well, now we want to add some sophisticated forensics features.  
> Currently we
> have fancy filtering but no real OLAP sort of capability.
>
> Have anyone seen a anything like this that we might be able to  
> integrate
> into our product?  We're using C# on Windows.

Are you talking straight OLAP, or do you have other forensic needs  
which will require you to roll your own functions ?  The only mention  
of OLAP on

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

is SQLite Maestro, a big many-functioned package compatible with many  
SQL engines.  I have no experience of it.

Another solution is the FOSS package 'Cubulus':

http://sourceforge.net/projects/cubulus/

However, if you need to write your own forensic capabilities, there  
seems little point in buying in a pre-made package just for OLAP since  
you will pretty-much have to duplicate it yourself.  SQLite has enough  
ways of accessing table structure information for you to be able to  
roll your own OLAP functions without too much trouble.  Since you  
already have programmers good enough to write your own product, they  
should be able to whip up a simple OLAP system without too much  
problem.  On the other hand buying one in might be cheaper than paying  
for that programmer time.

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

Re: [sqlite] SQLite Tools

[Mohit Sindhwani]

BareFeet wrote:
> Hi Barton,
>
>   
>> I saw all the tools listed on the SQLite wiki.  I was hoping that I  
>> could solicit opinions on the various ones there to save me the time  
>> of trying all of them.
>> 
>
> I've tried several and posted a fairly detailed comparison at:
> http://www.tandb.com.au/sqlite/compare/?ml
>
>   
>>> is there a SQLite centric utility to do a wide range of admin tasks  
>>> and to elegantly document the data base structure?
>>>   
>
> What admin tasks exactly are you after?
also, what constitutes elegant documentation?

Cheers,
Mohit.
8/26/2009 | 12:27 PM.

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

[sqlite] Forensics data control

[Gene Allen]

Hello all, 

I would like to ask for some guidance. We sell a file auditing product and,
like you would think, the database can get large.  SQLite has been a perfect
engine for us since we only do inserts and searches.

Well, now we want to add some sophisticated forensics features. Currently we
have fancy filtering but no real OLAP sort of capability.

Have anyone seen a anything like this that we might be able to integrate
into our product?  We're using C# on Windows.

Anyone used DevExpress's OLAP control?  It looks pretty cool (if I could
figure out what I'm doing) but I'm not sure it works with a raw DataSet or
DataTable.

Thanks,

Gene

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

Re: [sqlite] sqlite-users Digest, Vol 20, Issue 65

[nick huang]

> 
> > I am currently involved in porting sqlite on mobile phone
> 
> As an aside, you do realize that most smartphone OSes already have
> SQLite available? WinCE, iPhone OS, Symbian, PalmOS, Pre, and Android
> either have SQLite libs built-in to the SDK, or have a version of SQLite
> that compiles without modification. If you're using one of those
> environments, there is no reason to re-port SQLite unless you need a
> cutting-edge feature that just appeared in a newer version.
> 

 

Actually we are planning to use "qualcomm" solution for CDMA which wraps sqlite 
in its sdk.


> > And that's why I am interested to see if the first awailable row
> > can return ASAP.
> 
> This gets back to the original presumption that if you want it
> sorted, you want it sorted, and all the wishing and hoping isn't
> going to change the fact that sorting is often more computationally
> complex than returning rows in random order. The database engine
> will do what it takes to return a sorted result. If that takes more
> time, the database engine doesn't care. The database engine will not
> return an incorrect unsorted result if you asked for a sorted result,
> regardless of the API.
> 
> If you have an extremely tight reset timer you have to deal with, you
> can just have the rows returned and sort them yourself. The overall
> time is not likely to be much faster, but if you don't do the sort in
> one pass, you can keep the phone from resetting.
> 

 

BTW, I just happened to read documents about "progress-callback" in sqlite 
website(http://www.sqlite.org/c3ref/progress_handler.html) and it seems this is 
the best solution 

for embedded system which gives chances to kick the watch dog periodically 
without reset the system.

Any comment on this callback?


> > And if "prepare-step" can not do better than "execute" or "GetTable",
> 
> As I said before, prepare/step is the only way to get data out of the
> database. sqlite3_exec() and sqlite3_get_table() call prepare and step
> internally. exec and get_table are just wrapper functions. There is
> nothing special about them. You could write identical functions yourself
> with the public prepare/step API.
> 

 

agree.


> > then what is meaning to use "prepare-step"? 
> 
> That's a long discussion. 
> 
> I'd suggest you start here: http://sqlite.org/cintro.html
> 
> A quick summary:
> 
> 1) The "prepare" process is fairly expensive. Using bound variables,
> you can prepare a statement once and use it over and over without
> having to re-prepare it.
> 

understand.


> 2) Prepare/step is required to use bound variables. Bound variables
> prevent SQL injection attacks and a number of other issues. In
> general, you should be using bound variables and not doing string
> manipulations on SQL statements. If it was up to me, functions
> like sqlite3_exec, sqlite3_get_table, and sqlite3_vmprintf
> wouldn't be included in the API unless you compiled SQLite with
> SQLITE_BIG_SECURITY_HOLE or some similar flag.
> 

Interesting.


> 3) Prepare/step is generally faster and uses less memory. All the
> other APIs return results as strings, which requires further
> processing and memory management.
> 

good.

 


> 4) The step API allows you to break out of queries mid-way through
> and allows better processing and flow-control.
> 


> I'd also point out that the prepare/step paradigm is not unique to
> SQLite. Most database APIs have a similar construction, even if they
> use different terms.
> 

Yes, is there similar ones in MySql? not very sure. 


> > if I create a view with clause of "order by" to ask sqlite to sort
> > on that index. Can I get sorted result by querying the view with
> > prepare-step?
> 
> Sure, but it is going to take the same amount of processing as just
> issuing whatever the underlying query is. Views don't pre-sort or
> cache results, they're more like named sub-SELECTs.
> 

 

This is very helpful.

 


> > > sqlite3_step() returns rows as they become available. You're reading
> > > too deeply into "as they become available", however. The database
> > > engine is still required to return the correct result set. If the
> > > query needs to be sorted, it needs to be sorted. That may or may not
> > > require computing the full result set before returning the first row.
> > > It depends on the query. But either way, the database will do the
> > > right thing and return the correct results.
> 
> > According to your explanation, it seems the sorting prevents 
> > "prepare-step" from returning faster than "execute".
> 
> If you're talking about "time to first row returned", then yes.

 

good.


> 
> The time it takes for the first row to be available via step when using
> prepare/step and the time it takes for your first callback using exec
> is going to be the same. 
> 

> exec is actually a pretty thin wrapper. All it does is call prepare
> on the SQL you've provided, then calls step. Each time it calls step,
> it extracts the results and calls your callback. T

Re: [sqlite] SQLite Tools

[BareFeet]

Hi Barton,

> I saw all the tools listed on the SQLite wiki.  I was hoping that I  
> could solicit opinions on the various ones there to save me the time  
> of trying all of them.

I've tried several and posted a fairly detailed comparison at:
http://www.tandb.com.au/sqlite/compare/?ml

>> is there a SQLite centric utility to do a wide range of admin tasks  
>> and to elegantly document the data base structure?

What admin tasks exactly are you after?

Tom
BareFeet

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

Re: [sqlite] loadable extension doesn't load

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

sub sk79 wrote:
> I followed the exact instructions (
> http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions) on SQLite wiki for
> loadable extensions and still have run into this issue.
> Can someone help me here?

The instructions are how to make a loadable extension in C, but you are
using C++.

> /home/sk/utils/sqlite/sqlite-3.6.17/half.so: undefined symbol:
> sqlite3_extension_init

You need to declare sqlite3_extension_init as extern "C".  If you do not
then the name gets mangled according to normal C++ symbol mangling rules.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqUfT4ACgkQmOOfHg372QS/xQCfVwBrbNLGlX6/cliQiLDY2yrJ
YvoAoMb7d+pA4KJauvBREa8CWfSkkSwB
=tyLu
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] port sqlite to VxWorks

[ZhiHua Huang]

Hi,

use these definitions.

-DOS_VXWORKS_660=660\
-DOS_VXWORKS_670=670\
-DOS_VXWORKS=OS_VXWORKS_670\
-DSQLITE_HOMEGROWN_RECURSIVE_MUTEX\
-DSQLITE_ENABLE_LOCKING_STYLE=1\
-DSQLITE_OMIT_LOAD_EXTENSION

and below is my patch with sqlite 3.6.17.

*** sqlite3.c.origTue Aug 11 03:54:50 2009
--- sqlite3.cWed Aug 12 13:43:51 2009
***
*** 19,24 
--- 19,28 
  **
  ** This amalgamation was generated on 2009-08-10 19:53:32 UTC.
  */
+ #if defined(OS_VXWORKS)
+ #include 
+ #endif /* OS_VXWORKS */
+
  #define SQLITE_CORE 1
  #define SQLITE_AMALGAMATION 1
  #ifndef SQLITE_PRIVATE
***
*** 20983,20989 
--- 20987,20999 
  #include 
  #include 
  #include 
+
+ #if defined(OS_VXWORKS) && defined(_WRS_KERNEL)
+ #include 
+ #else
  #include 
+ #endif /* OS_VXWORKS */
+
  #include 

  #if SQLITE_ENABLE_LOCKING_STYLE
***
*** 23171,23177 
--- 23181,23189 
/* Otherwise see if some other process holds it. */
if( !reserved ){
  sem_t *pSem = pFile->pOpen->pSem;
+ #if !defined(OS_VXWORKS)
  struct stat statBuf;
+ #endif

  if( sem_trywait(pSem)==-1 ){
int tErrno = errno;
***
*** 23224,23230 
--- 23236,23244 
  */
  static int semLock(sqlite3_file *id, int locktype) {
unixFile *pFile = (unixFile*)id;
+ #if !defined(OS_VXWORKS)
int fd;
+ #endif
sem_t *pSem = pFile->pOpen->pSem;
int rc = SQLITE_OK;

***
*** 23293,23299 
--- 23307,23317 
  /*
   ** Close a file.
   */
+ #if (OS_VXWORKS < 600)
  static int semClose(sqlite3_file *id) {
+ #else
+ static int semClose_native(sqlite3_file *id) {
+ #endif
if( id ){
  unixFile *pFile = (unixFile*)id;
  semUnlock(id, NO_LOCK);
***
*** 23831,23837 
--- 23849,23861 
  }
  return -1;
}
+
+ #if defined(OS_VXWORKS) && defined(_WRS_KERNEL)
+   got = write(id->h, (char *)pBuf, cnt);
+ #else
got = write(id->h, pBuf, cnt);
+ #endif /* OS_VXWORKS */
+
  #endif
TIMER_END;
if( got<0 ){
***
*** 24310,24316 
--- 24334,24344 
  IOMETHODS(
semIoFinder,  /* Finder function name */
semIoMethods, /* sqlite3_io_methods object name */
+ #if (OS_VXWORKS < 600)
semClose, /* xClose method */
+ #else
+   semClose_native,  /* xClose method */
+ #endif
semLock,  /* xLock method */
semUnlock,/* xUnlock method */
semCheckReservedLock  /* xCheckReservedLock method */
***
*** 25087,25093 
** tests repeatable.
*/
memset(zBuf, 0, nBuf);
! #if !defined(SQLITE_TEST)
{
  int pid, fd;
  fd = open("/dev/urandom", O_RDONLY);
--- 25115,25121 
** tests repeatable.
*/
memset(zBuf, 0, nBuf);
! #if !defined(SQLITE_TEST) && !defined(OS_VXWORKS)
{
  int pid, fd;
  fd = open("/dev/urandom", O_RDONLY);

I'v tested it on VxWorks 6.6/6.7 with both DKM and RTP mode.

2009/8/24 mly_hlmgood 

> Hi,
>  I am puzzled by porting sqlite to VxWorks. can you help me.
> ___
> 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] loadable extension doesn't load

[sub sk79]

Hi,

I followed the exact instructions (
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions) on SQLite wiki for
loadable extensions and still have run into this issue.
Can someone help me here?


@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ gcc -I`pwd` -fPIC -shared
./loadableext.cpp -o half.so
@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ ls hal*
half.so
@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ ./sqlite3
SQLite version 3.6.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load half.so
/home/sk/utils/sqlite/sqlite-3.6.17/half.so: undefined symbol:
__gxx_personality_v0


I googled  *__gxx_personality_v0* and one solution was to link with
libstdc++ ,   so I tried that but it still won't work though it failed with
a different error this time. here is the log for that:


@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ gcc -I`pwd` -fPIC -shared
-lstdc++ ./loadableext.cpp -o half.so
@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ ./sqlite3
SQLite version 3.6.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load half.so
/home/sk/utils/sqlite/sqlite-3.6.17/half.so: undefined symbol:
sqlite3_extension_init



GCC Version is: gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4)

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

Re: [sqlite] Binding parameters to prepared statements

[John Elrick]

>> Although quotes can be used, they are not needed when the table and 
>> field names contain all valid characters.   I simply saw no value in 
>> adding them, and they did seem to unnecessarily complicate the text.
>>   
>> 
> It also says on http://www.sqlite.org/lang_keywords.html that in
> case you use a name that one day becomes a keyword you should always use
> quotes.

I appreciate that perspective, however, neither 'keys', 'IVAndKey', nor 
'ItemID' are SQL keywords.  Hence my confusion.



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

Re: [sqlite] Find the letters that can follow the current text

[Pavel Ivanov]

> Just a note:
> select distinct substr(col, length(?1), 1)
> should be
> select distinct substr(col, length(?1) + 1, 1)

Oh, yes, sure. I always mix 0-based and 1-based indexing of symbols in
different languages. :)

Pavel

On Tue, Aug 25, 2009 at 11:50 AM, Mohit Sindhwani wrote:
> Pavel Ivanov wrote:
>> Maybe you want something like this:
>>
>> select distinct substr(col, length(?1), 1)
>> from table
>> where col like ?1||'%'
>>
>>
> Hi Pavel, thanks!  It works..!
> Just a note:
> select distinct substr(col, length(?1), 1)
> should be
> select distinct substr(col, length(?1) + 1, 1)
>
> So, for the sample data earlier, it should be:
> select distinct substr(str, 3, 1) from items where str like "IT%";
>  and
> select distinct substr(str, 4, 1) from items where str like "ITS%";
>
> Thanks,
> Mohit.
>
> ___
> 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] Find the letters that can follow the current text

[Mohit Sindhwani]

Pavel Ivanov wrote:
> Maybe you want something like this:
>
> select distinct substr(col, length(?1), 1)
> from table
> where col like ?1||'%'
>
>   
Hi Pavel, thanks!  It works..!
Just a note:
select distinct substr(col, length(?1), 1)
should be
select distinct substr(col, length(?1) + 1, 1)

So, for the sample data earlier, it should be:
select distinct substr(str, 3, 1) from items where str like "IT%";
 and
select distinct substr(str, 4, 1) from items where str like "ITS%";

Thanks,
Mohit.

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

Re: [sqlite] SQLite Tools

[Kit]

2009/8/25 Barton Torbert :
> I am looking for one of two things.  The first thing would be a tool to  
> easily synchronize data and structure between SQLite and Access.   I want to 
> be able to do something in one system and have it exported to the other.
> Bart

http://www.sqlite.org/cvstrac/wiki?p=ConverterTools
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Binding parameters to prepared statements

[Jay A. Kreibich]

On Tue, Aug 25, 2009 at 11:19:17AM -0400, Angus March scratched on the wall:
 
> It also says on http://www.sqlite.org/lang_keywords.html that in
> case you use a name that one day becomes a keyword you should always use
> quotes.

  It is also quite specific about which quotes to use: single-quotes
  for string literals, double quotes for identifiers.  An identifier
  is a table/view, index, database, or column name.

  In your original statement you mix them at will, using both single
  and double for identifiers:

 UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN

  All of these are identifiers, and all of them should use double quotes:

 UPDATE "KEYS" SET "IVAndKey"=? WHERE "ItemID"=?

  As the page you reference explains, the use of single quotes works
  for identifiers, in some cases, but can actually make the situation
  worse and "Programmers are cautioned not to use the two exceptions"
  There have been serious discussions in the recent past about making
  SQLite's quote usage more strict.  Quoting is a good idea, but
  incorrect quoting is worse than no quoting at all.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CURRENT_TIMESTAMP with fractional seconds?

[Wilson, Ronald]

> Put parentheses around the strftime() function call:
> 
> CREATE TABLE info(..., stamp DEFAULT (strftime('%f','now')))
> 
> The extra parentheses are needed to avoid a parsing ambiguity in the
> SQL language.
> 
> D. Richard Hipp
> d...@hwaci.com

Perfect!

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division
assuredcommunications(tm)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Binding parameters to prepared statements

[Angus March]

John Elrick wrote:
> Angus March wrote:
>   
>> John Elrick wrote:
>>   
>> 
>>> Angus March wrote:
>>>   
>>> 
>>>   
 I'm trying to make a prepared statement and bind parameters to it, but
 the documentation is very confusing. This is the statement I'm trying to
 prepare:
 UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
 Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
 for the table. I'm told that there is a syntax error near "NNN".
   
 
   
 
>>> The page (http://sqlite.org/c3ref/bind_blob.html) reads:
>>>
>>> "In the SQL strings input to sqlite3_prepare_v2() and its variants, 
>>> literals may be replaced by a parameter in one of these forms:
>>>
>>> * ?
>>> * ?NNN
>>> * :VVV
>>> * @VVV
>>> * $VVV "
>>>
>>> The documentation for NNN is three sentences below this and states that 
>>> the NNN refers to a number such as:
>>>
>>> ?1 ?2
>>>   
>>> 
>>>   
>> I don't see that anywhere on the page. An example might have made a
>> difference.
>>   
>> 
>
> "The second argument is the index of the SQL parameter to be set. The 
> leftmost SQL parameter has an index of 1. When the same named SQL 
> parameter is used more than once, second and subsequent occurrences have 
> the same index as the first occurrence. The index for named parameters 
> can be looked up using the sqlite3_bind_parameter_index() API if 
> desired. The index for "?NNN" parameters is the value of NNN. The NNN 
> value must be between 1 and the sqlite3_limit() parameter 
> SQLITE_LIMIT_VARIABLE_NUMBER (default value: 999)."
>   
I read that, and it didn't make any sense to me. At that point, I
knew I'd be turning to the mailing-list sooner or later.
>
> Although quotes can be used, they are not needed when the table and 
> field names contain all valid characters.   I simply saw no value in 
> adding them, and they did seem to unnecessarily complicate the text.
>   
It also says on http://www.sqlite.org/lang_keywords.html that in
case you use a name that one day becomes a keyword you should always use
quotes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CURRENT_TIMESTAMP with fractional seconds?

[D. Richard Hipp]

On Aug 25, 2009, at 11:04 AM, Wilson, Ronald wrote:

> Is there a way to set a default timestamp with fractional seconds?
> This gives a syntax error (obviously) but expresses my intent:
>
> CREATE TABLE info(k, v, stamp DATETIME default strftime('%Y-%m-%d
> %H:%M:%f', 'now'));
>


Put parentheses around the strftime() function call:

CREATE TABLE info(..., stamp DEFAULT (strftime('%f','now')))

The extra parentheses are needed to avoid a parsing ambiguity in the  
SQL language.

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



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

Re: [sqlite] Find the letters that can follow the current text

[Pavel Ivanov]

Maybe you want something like this:

select distinct substr(col, length(?1), 1)
from table
where col like ?1||'%'


Pavel

On Tue, Aug 25, 2009 at 10:57 AM, Mohit Sindhwani wrote:
> OK, I know the subject is difficult to understand but I want to use a
> sqlite table for auto-completion.  So, suppose I have records that are like:
>
> ITEM0
> ITS THERE
> ITS HERE
> ITIS NOW
> ITSY BITSY
> ITZ COOL
>
> And if the user has typed in "IT", I'd like to prompt him saying that
> the only letters that can follow this are: "E,S,I,Z"
> If he then types in "S", I'd like to update the result and show that the
> next letters that can follow are: "(space), Y"
>
> Any ideas on how I could do this efficiently?
>
> Thanks,
> Mohit.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CURRENT_TIMESTAMP with fractional seconds?

[Wilson, Ronald]

Is there a way to set a default timestamp with fractional seconds?

 

This works but doesn't give fractional seconds:

CREATE TABLE info(k, v, stamp DATETIME default CURRENT_TIMESTAMP);

 

This gives a syntax error (obviously) but expresses my intent:

CREATE TABLE info(k, v, stamp DATETIME default strftime('%Y-%m-%d
%H:%M:%f', 'now'));

 

RW

 

Ron Wilson, Engineering Project Lead

(o) 434.455.6453, (m) 434.851.1612, www.harris.com

 

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

[sqlite] Find the letters that can follow the current text

[Mohit Sindhwani]

OK, I know the subject is difficult to understand but I want to use a 
sqlite table for auto-completion.  So, suppose I have records that are like:

ITEM0
ITS THERE
ITS HERE
ITIS NOW
ITSY BITSY
ITZ COOL

And if the user has typed in "IT", I'd like to prompt him saying that 
the only letters that can follow this are: "E,S,I,Z"
If he then types in "S", I'd like to update the result and show that the 
next letters that can follow are: "(space), Y"

Any ideas on how I could do this efficiently?

Thanks,
Mohit.

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

Re: [sqlite] SQLite Tools recommended: SQLite Manager

[Barton Torbert]

I use this tool now for basic data management.  But I am looking for a tool 
that will help with documentation.
 
Bart
 



From: sqlite-users-boun...@sqlite.org on behalf of J Glassy
Sent: Tue 8/25/2009 8:43 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Tools recommended: SQLite Manager



Hello Barton,
 Here is one suggestion to try: SQL Manager.
 I use SQLite Manager (by Mrinal Kant
(http://sqlite-manager.googlecode.com  ) 
and find it extremely useful.
It is developed in Xulrunner, Gecko, thus runs only under
Mozilla/Firefox. That said, it runs essentially as a stand alone
application, and at v.0.5.1 is fairly robust and stable at this point.
There are only a few common features that SQLite Manager omits or
doesn't do well, but it is very dynamically managed and new features
do get implemented from time to time. Give it a try if you use
Firefox.

cheers,
Joe Glassy

On Tue, Aug 25, 2009 at 8:37 AM, Barton Torbert wrote:
> I saw all the tools listed on the SQLite wiki.  I was hoping that I could 
> solicit opinions on the various ones there to save me the time of trying all 
> of them.
>
> Bart
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Kees Nuyt
> Sent: Tue 8/25/2009 8:28 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite Tools
>
>
>
> On Tue, 25 Aug 2009 09:00:46 -0500, "Barton Torbert"
>  wrote:
>
>
>> Hello,
>>
>> I have just started to use SQLite and am looking for
>> tools to manage my databases with.
>>
>> Right now my development group is manually importing
>> our SQLite databases into Access so to use the latter
>> to document table relationships, create ERDs, etc.
>> This is the painful way to do this, so I am looking for
>> tools to make life easier.
>>
>> I am looking for one of two things.  The first thing
>> would be a tool to  easily synchronize data and
>> structure between SQLite and Access.   I want to be
>> able to do something in one system and have it exported
>> to the other.
>
> Perhaps an ODBC driver would help.
> http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc
> http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
>
>> Otherwise, is there a SQLite centric utility to do a
>> wide range of admin tasks and to elegantly document the
>> data base structure?
>
> If there is, it should be listed here:
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
>
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> 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
>



--
---
Joseph Glassy
Lead Software Engineer (contractor)
NASA Measures (Freeze/Thaw),Rm CFC 424
College of Forestry and Conservation
Univ. Montana, Missoula, MT 59812
Tel: 406-243-6318 Cellular: 406-544-3315
and:
Research Analyst/Programmer
University of Montana NSF EPSCoR Program
Davidson Honors College Room 013
Missoula, MT 59812
um.gla...@gmail.com
Campus phone 243-6337   Cell(406) 544-3315
___
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] Binding parameters to prepared statements

[John Elrick]

Angus March wrote:
> John Elrick wrote:
>   
>> Angus March wrote:
>>   
>> 
>>> I'm trying to make a prepared statement and bind parameters to it, but
>>> the documentation is very confusing. This is the statement I'm trying to
>>> prepare:
>>> UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
>>> Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
>>> for the table. I'm told that there is a syntax error near "NNN".
>>>   
>>> 
>>>   
>> The page (http://sqlite.org/c3ref/bind_blob.html) reads:
>>
>> "In the SQL strings input to sqlite3_prepare_v2() and its variants, 
>> literals may be replaced by a parameter in one of these forms:
>>
>> * ?
>> * ?NNN
>> * :VVV
>> * @VVV
>> * $VVV "
>>
>> The documentation for NNN is three sentences below this and states that 
>> the NNN refers to a number such as:
>>
>> ?1 ?2
>>   
>> 
>
> I don't see that anywhere on the page. An example might have made a
> difference.
>   

"The second argument is the index of the SQL parameter to be set. The 
leftmost SQL parameter has an index of 1. When the same named SQL 
parameter is used more than once, second and subsequent occurrences have 
the same index as the first occurrence. The index for named parameters 
can be looked up using the sqlite3_bind_parameter_index() API if 
desired. The index for "?NNN" parameters is the value of NNN. The NNN 
value must be between 1 and the sqlite3_limit() parameter 
SQLITE_LIMIT_VARIABLE_NUMBER (default value: 999)."

>   
>> I'm also unsure why you've added single and double quotes.  Maybe:
>>
>> UPDATE KEYS SET IVAndKey=? WHERE ItemID = ?
>>   
>> 
>
> Well, I was using single quotes before, and it didn't like them in
> things like the WHERE clause and SELECT clause, so I changed those ones
> to double quotes.

Although quotes can be used, they are not needed when the table and 
field names contain all valid characters.   I simply saw no value in 
adding them, and they did seem to unnecessarily complicate the text.

HTH

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

Re: [sqlite] SQLite Tools recommended: SQLite Manager

[J Glassy]

Hello Barton,
 Here is one suggestion to try: SQL Manager.
 I use SQLite Manager (by Mrinal Kant
(http://sqlite-manager.googlecode.com) and find it extremely useful.
It is developed in Xulrunner, Gecko, thus runs only under
Mozilla/Firefox. That said, it runs essentially as a stand alone
application, and at v.0.5.1 is fairly robust and stable at this point.
There are only a few common features that SQLite Manager omits or
doesn't do well, but it is very dynamically managed and new features
do get implemented from time to time. Give it a try if you use
Firefox.

cheers,
Joe Glassy

On Tue, Aug 25, 2009 at 8:37 AM, Barton Torbert wrote:
> I saw all the tools listed on the SQLite wiki.  I was hoping that I could 
> solicit opinions on the various ones there to save me the time of trying all 
> of them.
>
> Bart
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Kees Nuyt
> Sent: Tue 8/25/2009 8:28 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite Tools
>
>
>
> On Tue, 25 Aug 2009 09:00:46 -0500, "Barton Torbert"
>  wrote:
>
>
>> Hello,
>>
>> I have just started to use SQLite and am looking for
>> tools to manage my databases with.
>>
>> Right now my development group is manually importing
>> our SQLite databases into Access so to use the latter
>> to document table relationships, create ERDs, etc.
>> This is the painful way to do this, so I am looking for
>> tools to make life easier.
>>
>> I am looking for one of two things.  The first thing
>> would be a tool to  easily synchronize data and
>> structure between SQLite and Access.   I want to be
>> able to do something in one system and have it exported
>> to the other.
>
> Perhaps an ODBC driver would help.
> http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc
> http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
>
>> Otherwise, is there a SQLite centric utility to do a
>> wide range of admin tasks and to elegantly document the
>> data base structure?
>
> If there is, it should be listed here:
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
>
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> 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
>



-- 
---
Joseph Glassy
Lead Software Engineer (contractor)
NASA Measures (Freeze/Thaw),Rm CFC 424
College of Forestry and Conservation
Univ. Montana, Missoula, MT 59812
Tel: 406-243-6318 Cellular: 406-544-3315
and:
Research Analyst/Programmer
University of Montana NSF EPSCoR Program
Davidson Honors College Room 013
Missoula, MT 59812
um.gla...@gmail.com
Campus phone 243-6337   Cell(406) 544-3315
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Tools

[Barton Torbert]

I saw all the tools listed on the SQLite wiki.  I was hoping that I could 
solicit opinions on the various ones there to save me the time of trying all of 
them.
 
Bart
 



From: sqlite-users-boun...@sqlite.org on behalf of Kees Nuyt
Sent: Tue 8/25/2009 8:28 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite Tools



On Tue, 25 Aug 2009 09:00:46 -0500, "Barton Torbert"
 wrote:


> Hello,
>
> I have just started to use SQLite and am looking for
> tools to manage my databases with.
>
> Right now my development group is manually importing
> our SQLite databases into Access so to use the latter
> to document table relationships, create ERDs, etc.
> This is the painful way to do this, so I am looking for
> tools to make life easier.
>
> I am looking for one of two things.  The first thing
> would be a tool to  easily synchronize data and
> structure between SQLite and Access.   I want to be
> able to do something in one system and have it exported
> to the other.

Perhaps an ODBC driver would help.
http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

> Otherwise, is there a SQLite centric utility to do a
> wide range of admin tasks and to elegantly document the
> data base structure?

If there is, it should be listed here:
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

--
  (  Kees Nuyt
  )
c[_]
___
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] Binding parameters to prepared statements

[Angus March]

John Elrick wrote:
> Angus March wrote:
>   
>> I'm trying to make a prepared statement and bind parameters to it, but
>> the documentation is very confusing. This is the statement I'm trying to
>> prepare:
>> UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
>> Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
>> for the table. I'm told that there is a syntax error near "NNN".
>>   
>> 
>
> The page (http://sqlite.org/c3ref/bind_blob.html) reads:
>
> "In the SQL strings input to sqlite3_prepare_v2() and its variants, 
> literals may be replaced by a parameter in one of these forms:
>
> * ?
> * ?NNN
> * :VVV
> * @VVV
> * $VVV "
>
> The documentation for NNN is three sentences below this and states that 
> the NNN refers to a number such as:
>
> ?1 ?2
>   

I don't see that anywhere on the page. An example might have made a
difference.

> I'm also unsure why you've added single and double quotes.  Maybe:
>
> UPDATE KEYS SET IVAndKey=? WHERE ItemID = ?
>   

Well, I was using single quotes before, and it didn't like them in
things like the WHERE clause and SELECT clause, so I changed those ones
to double quotes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Tools

[Kees Nuyt]

On Tue, 25 Aug 2009 09:00:46 -0500, "Barton Torbert"
 wrote:


> Hello,
> 
> I have just started to use SQLite and am looking for
> tools to manage my databases with.
> 
> Right now my development group is manually importing
> our SQLite databases into Access so to use the latter
> to document table relationships, create ERDs, etc.
> This is the painful way to do this, so I am looking for
> tools to make life easier.
> 
> I am looking for one of two things.  The first thing
> would be a tool to  easily synchronize data and
> structure between SQLite and Access.   I want to be
> able to do something in one system and have it exported
> to the other.

Perhaps an ODBC driver would help.
http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
 
> Otherwise, is there a SQLite centric utility to do a
> wide range of admin tasks and to elegantly document the
> data base structure?

If there is, it should be listed here:
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
 
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Binding parameters to prepared statements

[Angus March]

Pavel Ivanov wrote:
>> UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
>> Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
>> for the table. I'm told that there is a syntax error near "NNN".
>> 
>
> You have actually used some number instead NNN and wrote it here just
> for abstract example, haven't you? If not then complain about wrong
> syntax is because of this.
> I'd suggest you to not mix in one query parameters preceded by '?' and
> by ':'. So if you rewrite your query like this:
> UPDATE 'KEYS' SET 'IVAndKey'=?1 WHERE "ItemID"=?2
> or like this
> UPDATE 'KEYS' SET 'IVAndKey'=:FirstParam WHERE "ItemID"=:SecondParam
> then it will not cause syntax error and your bindings will be correct.
>   

Oh, I get it! No, those weren't abstract examples, that's what I
literally had there. I thought you used the literal ?NNN for INTEGER
afinity, and :VVV for strings. Like I said, the documentation is very
confusing. Even if it had italicized the VVV and the NNN I might have
twigged.
Thanks.

>
> Pavel
>
> On Tue, Aug 25, 2009 at 9:31 AM, Angus March wrote:
>   
>> I'm trying to make a prepared statement and bind parameters to it, but
>> the documentation is very confusing. This is the statement I'm trying to
>> prepare:
>> UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
>> Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
>> for the table. I'm told that there is a syntax error near "NNN".
>>
>> Also, are the correct binding calls:
>> sqlite3_bind_int(m_insert, 2, idItem);
>> sqlite3_bind_blob(m_insert, 1, p, DATA_KEY_RECORD_LENGTH, FreeBlob);
>> ?
>>
>> ___
>> 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] Binding parameters to prepared statements

[John Elrick]

Angus March wrote:
> I'm trying to make a prepared statement and bind parameters to it, but
> the documentation is very confusing. This is the statement I'm trying to
> prepare:
> UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
> Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
> for the table. I'm told that there is a syntax error near "NNN".
>   

The page (http://sqlite.org/c3ref/bind_blob.html) reads:

"In the SQL strings input to sqlite3_prepare_v2() and its variants, 
literals may be replaced by a parameter in one of these forms:

* ?
* ?NNN
* :VVV
* @VVV
* $VVV "

The documentation for NNN is three sentences below this and states that 
the NNN refers to a number such as:

?1 ?2

I'm also unsure why you've added single and double quotes.  Maybe:

UPDATE KEYS SET IVAndKey=? WHERE ItemID = ?

and then use the positional bindings

sqlite3_bind_int(m_insert, 2, idItem);
sqlite3_bind_blob(m_insert, 1, p, DATA_KEY_RECORD_LENGTH, FreeBlob);

Assuming m_insert is your statement.

HTH

John Elrick
Fenestra Technologies

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

[sqlite] SQLite Tools

[Barton Torbert]

Hello,
 
I have just started to use SQLite and am looking for tools to manage my 
databases with.
 
Right now my development group is manually importing our SQLite databases into 
Access so to use the latter to document table relationships, create ERDs, etc.  
This is the painful way to do this, so I am looking for tools to make life 
easier.
 
I am looking for one of two things.  The first thing would be a tool to  easily 
synchronize data and structure between SQLite and Access.   I want to be able 
to do something in one system and have it exported to the other.
 
Otherwise, is there a SQLite centric utility to do a wide range of admin tasks 
and to elegantly document the data base structure?
 
All suggestions are welcome.
 
Bart
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Binding parameters to prepared statements

[Pavel Ivanov]

> UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
> Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
> for the table. I'm told that there is a syntax error near "NNN".

You have actually used some number instead NNN and wrote it here just
for abstract example, haven't you? If not then complain about wrong
syntax is because of this.
I'd suggest you to not mix in one query parameters preceded by '?' and
by ':'. So if you rewrite your query like this:
UPDATE 'KEYS' SET 'IVAndKey'=?1 WHERE "ItemID"=?2
or like this
UPDATE 'KEYS' SET 'IVAndKey'=:FirstParam WHERE "ItemID"=:SecondParam
then it will not cause syntax error and your bindings will be correct.


Pavel

On Tue, Aug 25, 2009 at 9:31 AM, Angus March wrote:
> I'm trying to make a prepared statement and bind parameters to it, but
> the documentation is very confusing. This is the statement I'm trying to
> prepare:
> UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
> Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
> for the table. I'm told that there is a syntax error near "NNN".
>
> Also, are the correct binding calls:
> sqlite3_bind_int(m_insert, 2, idItem);
> sqlite3_bind_blob(m_insert, 1, p, DATA_KEY_RECORD_LENGTH, FreeBlob);
> ?
>
> ___
> 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] Binding parameters to prepared statements

[Angus March]

I'm trying to make a prepared statement and bind parameters to it, but
the documentation is very confusing. This is the statement I'm trying to
prepare:
UPDATE 'KEYS' SET 'IVAndKey'=:VVV WHERE "ItemID"=?NNN
Where IVAndKey is a BLOB and ItemID is an INTEGER and the primary key
for the table. I'm told that there is a syntax error near "NNN".

Also, are the correct binding calls:
sqlite3_bind_int(m_insert, 2, idItem);
sqlite3_bind_blob(m_insert, 1, p, DATA_KEY_RECORD_LENGTH, FreeBlob);
?

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

Re: [sqlite] datetime('now', 'utc') vs. CURRENT_TIMESTAMP

[Jay A. Kreibich]

On Mon, Aug 24, 2009 at 06:21:49PM -0400, Wilson, Ronald scratched on the wall:
> According to the documentation for CURRENT_TIMESTAMP, it should insert
> the current UTC date/time:  http://www.sqlite.org/lang_createtable.html.

> sqlite> select datetime('now', 'utc');
> 
> 2009-08-25 02:20:10

> Do I misunderstand something fundamental?


  Yes.  'now' is already in UTC, so there is no need to convert it.
  The syntax datetime('now', 'utc') is double-converting the time.
  
  The documentation for the 'utc' modifier is quite clear:

  "'utc' assumes that the string to its left is in the local timezone
  and adjusts that string to be in UTC. If the prior string is not in
  localtime, then the result of 'utc' is undefined."

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


-j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime('now', 'utc') vs. CURRENT_TIMESTAMP

[Pavel Ivanov]

> Do I misunderstand something fundamental?

According to http://www.sqlite.org/lang_datefunc.html datetime('now')
returns date and time already as UTC. If you add 'utc' modifier then
it makes datetime() think that it's your local time and convert it to
'utc' thus adding 4 hours (apparently you're in GMT -4 timezone).

Pavel

On Mon, Aug 24, 2009 at 6:21 PM, Wilson, Ronald wrote:
> According to the documentation for CURRENT_TIMESTAMP, it should insert
> the current UTC date/time:  http://www.sqlite.org/lang_createtable.html.
>
>
>
> However, there appears to be a mismatch with datetime('now', 'utc'):
>
>
>
> SQLite version 3.6.10
>
> Enter ".help" for instructions
>
> Enter SQL statements terminated with a ";"
>
> sqlite> create table test(stamp DATETIME default CURRENT_TIMESTAMP);
>
> sqlite> insert into test (rowid) values(1);
>
> sqlite> insert into test values (CURRENT_TIMESTAMP);
>
> sqlite> select * from test;
>
> 2009-08-24 22:19:43
>
> 2009-08-24 22:19:55
>
> sqlite> select datetime('now', 'utc');
>
> 2009-08-25 02:20:10
>
> sqlite>
>
>
>
> If you ignore the minor time diff from my slow typing, there is a clear
> 4 hour difference between CURRENT_TIMESTAMP and datetime('now', 'utc').
> Do I misunderstand something fundamental?
>
>
>
> RW
>
>
>
> Ron Wilson, Engineering Project Lead
>
> (o) 434.455.6453, (m) 434.851.1612, www.harris.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] thank

[prayudinata]

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

Re: [sqlite] port sqlite to VxWorks

[Kees Nuyt]

On Mon, 24 Aug 2009 14:46:00 +0800 (CST), 
mly_hlmgood  wrote:

> Hi,
> I am puzzled by porting sqlite to VxWorks. can you help me.  

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

[sqlite] datetime('now', 'utc') vs. CURRENT_TIMESTAMP

[Wilson, Ronald]

According to the documentation for CURRENT_TIMESTAMP, it should insert
the current UTC date/time:  http://www.sqlite.org/lang_createtable.html.

 

However, there appears to be a mismatch with datetime('now', 'utc'):

 

SQLite version 3.6.10

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> create table test(stamp DATETIME default CURRENT_TIMESTAMP);

sqlite> insert into test (rowid) values(1);

sqlite> insert into test values (CURRENT_TIMESTAMP);

sqlite> select * from test;

2009-08-24 22:19:43

2009-08-24 22:19:55

sqlite> select datetime('now', 'utc');

2009-08-25 02:20:10

sqlite>

 

If you ignore the minor time diff from my slow typing, there is a clear
4 hour difference between CURRENT_TIMESTAMP and datetime('now', 'utc').
Do I misunderstand something fundamental?

 

RW

 

Ron Wilson, Engineering Project Lead

(o) 434.455.6453, (m) 434.851.1612, www.harris.com

 

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

[sqlite] port sqlite to VxWorks

[mly_hlmgood]

Hi,
  I am puzzled by porting sqlite to VxWorks. can you help me.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite client and server with new RPC system

[andrew babanin]

Hi, all.

I'm working on new RPC (remote procedure call) system integrated into C
language,
so I desided to write a client and server side for Sqlite with this system.

System is called CRPC, integrated in C with special modificators, has own
network protocol and marshaling method.
Also SSL support is tested.

Client and server are very compact and can be downloaded here
https://sourceforge.net/projects/crpc/files/crpc/crpc-sqlite-1.tar.bz2/download

To compile client and server CRPC should be first built, to download use
this
https://sourceforge.net/projects/crpc/files/crpc/crpc-0.7.6-090825/crpc-0.7.6-090825.tar.bz2/download

CRPC Web-site is crpc.sourceforge.net.

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

[sqlite] Software operation and ETL tools

[Rstat]

Hi all, 

We are in the process of getting an ETL program. We need it to perform some
basic extract, transform and load jobs. 
But we want to get an open source tool with good training. 
Our team is mainly business oriented, with some computer knowledge. We would
like to have someone come to our company and explain to all the team how to
operate the tool. 

I can operate the tool on my own, but I am not experienced enough to show
other people how to operate the software. 
So would someone know which open source companies offer ETL tools with good
training and how much time would it take? 

Thanks.
-- 
View this message in context: 
http://www.nabble.com/Software-operation-and-ETL-tools-tp25131607p25131607.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