Re: [sqlite] defalut value of col

[Dan]

On Oct 23, 2008, at 4:51 AM, Dennis Cote wrote:

> Antoine Caron wrote:
>>
>> I was expecting -1 instead of NULL as default value, can anyone  
>> explain me
>> that ?
>>
>
> That looks like a bug in the table_info pragma.

I think so too. Fix is here:

   http://www.sqlite.org/cvstrac/chngview?cn=5839


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

Re: [sqlite] defalut value of col

[Dennis Cote]

Antoine Caron wrote:
> 
> I was expecting -1 instead of NULL as default value, can anyone explain me
> that ?
> 

That looks like a bug in the table_info pragma. You should probably 
report it.

The following test script shows that the correct default value is used 
even though the an incorrect null value is displayed by the pragma.

 sqlite> CREATE TABLE IF NOT EXISTS test_table
...>   (test_field INT NOT NULL DEFAULT -1, two text);
 sqlite> .mode column
 sqlite> .header on
 sqlite> PRAGMA table_info (test_table);
 cid nametypenotnull dflt_value  pk
 --  --  --  --  --  --
 0   test_field  INT 99  0
 1   two text0   0
 sqlite> insert into test_table(two) values('one');
 sqlite> select * from test_table;
 test_field  two
 --  --
 -1  one


You can report the bug at 
http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew

HTH
Dennis Cote

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

[sqlite] Default Row value

[Antoine Caron]

Hi guys,

 

   I'm not sure I've posted at all my previous msg so here it is again with
few more explanation

 

I'm creating a table using the following :

 

#

CREATE TABLE IF NOT EXISTS `test_table` (

  `test_field1` INTEGER DEFAULT -1,

  `test_field2` INTEGER DEFAULT NULL

);

#

 

Note the DEFAULT  -1

I then do in firefox sqlitemanager addon (any better tool around to check
inside the DB ?)

 

#

PRAGMA table_info (test_table)

#

 

I'm getting the following

 

>>"0","test_field1","INTEGER","0","NULL","0"

>>"1","test_field2","INTEGER","0","NULL","0"

 

I was expecting -1 instead of NULL as default value for the 1st column, can
anyone explain me that ? I've noticed the if I put (-1) instead of -1,
PRAGMA gives me the good default value. That make me wonder if -1 were
interpreted as TEXT and casted into INT? or is it NULL that's interpreted as
TEXT? any idea what happened?

 

Im trying to access this data from a C++ app. When I'm executing the "PRAGMA
table_info(test_table)" from the application and parsing the returned field
and casting field in String, test_field1 is '' and test_field2 is 'NULL',
which is completely different. Is it that in test_field2, the default value
is a TEXT  that couldn't cast into INT ?

 

Can anyone explain me the behavior around that ?

Thanks.

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

Re: [sqlite] Compiling with mingw32/msys on Windows

[John Belli]

I got the CVS version and it mostly works, but I'm hitting a bug in
mingw32 gcc now (which, after some investigation, may have been fixed
in a later version than the current release). At least I can take the
processed amalgamation and use it in a VS2k5 project.

Note: SQLITE_ENABLE_IOTRACE doesn't appear to work either, unless I
need to pass it to configure as well.

[EMAIL PROTECTED] ~/bld $ make OPTS="-DSQLITE_THREADSAFE=1
-DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_RTREE
-DSQLITE_DEFAULT_FILE_FORMAT=4 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT"
   ...
libtool: compile:  gcc -DSQLITE_OS_WIN=1 -I. -I../sqlite/src
-D_HAVE_SQLITE_CONFIG_H -DNDEBUG -IC:/Tcl/include
-DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_THREADSAFE=1
-DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_RTREE
-DSQLITE_DEFAULT_FILE_FORMAT=4 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT
-DUSE_TCL_STUBS=1 -c ../sqlite/src/tclsqlite.c  -DDLL_EXPORT -DPIC -o
.libs/tclsqlite.o
../sqlite/src/tclsqlite.c: In function `Sqlite3_Init':
../sqlite/src/tclsqlite.c:2524: internal compiler error: in
rest_of_handle_final, at toplev.c:2067
Please submit a full bug report,
with preprocessed source if appropriate.
See  for instructions.
make: *** [tclsqlite.lo] Error 1

JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

Re: [sqlite] Compiling with mingw32/msys on Windows

[John Belli]

On Wed, 22 Oct 2008 14:34:04 -0400, "Shane Harrelson"
<[EMAIL PROTECTED]> wrote:

>I was able to duplicate the issue, and I've updated the configure
>scripts to correct the problem.

OK, great. Is the tarball updated, or do I need to get it from CVS?

>If you are generating your makefile using configure, you can pass it
>options like this:
>
>   configure CFLAGS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1
>
>This will generate a makefile which passes the
>SQLITE_ENABLE_UPDATE_DELETE_LIMIT option to the build components.
>If you already have a makefile generated by configure, you should be
>able to add custom compile options like this:
>
>   make OPTS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1
>
>or for multiple options:
>
>   make "OPTS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1 -DSQLITE_OMIT_TRIGGER=1"

I thought you used OPTS with configure, too? At least, I did use it,
as shown upthread.

Thanks for looking into this.


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

[sqlite] INSERT OR REPLACE

[Peter Jacobi]

Dear All,

According to the documentation, and as far as I can see, also in
reality, INSERT OR REPLACE
will remove existing columns which conflict regarding a unique key.

Quite contrary to my initial foolish belief, it doesn't work at all
like a hypothetical
INSERT OR UPDATE, which in case of multiple conflicting rows would have to fail
anyway and in case of a single conflicting row would update *that*
*row* -- i.e. leaving
the rowid and all columns not specified in the statement intact.

What would be efficient and correct SQL to get the INSERT OR UPDATE
behaviour? Am I right
in assuming, that it will always suffer a performance penalty, either
in the INSERT or in the
UPDATE case, depending on the SQL used?

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

[sqlite] defalut value of col

[Antoine Caron]

Hi guys,

 

  Here's a quick one

 

I'm creating a table using the following :

 

#

CREATE TABLE IF NOT EXISTS `test_table` (

  `test_field` INT NOT NULL DEFAULT -1 

);

#

 

Note the default -1

I then do

 

#

PRAGMA table_info (test_table)

#

 

i'm getting the following

 

>>  "0","test_field","INT","99","NULL","0"

 

I was expecting -1 instead of NULL as default value, can anyone explain me
that ?

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

Re: [sqlite] Compiling with mingw32/msys on Windows

[Shane Harrelson]

I was able to duplicate the issue, and I've updated the configure
scripts to correct the problem.
If you are generating your makefile using configure, you can pass it
options like this:

   configure CFLAGS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1

This will generate a makefile which passes the
SQLITE_ENABLE_UPDATE_DELETE_LIMIT option to the build components.
If you already have a makefile generated by configure, you should be
able to add custom compile options like this:

   make OPTS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1

or for multiple options:

   make "OPTS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1 -DSQLITE_OMIT_TRIGGER=1"

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

Re: [sqlite] Update question

[Igor Tandetnik]

Jon Dixon <[EMAIL PROTECTED]> wrote:
> I am updating how I handle dates/durations in a database of mine and
> I would like to come up with an SQL command to make the change for
> me.
>
> The old format had a field Departure (-MM-DD) and a field Nights
> (an integer), where the new format
> still uses Departure but adds Return (-MM-DD) and the duration is
> calculated from the difference between Departure and Return.
>
> My stab at an SQL statement to pull this change off is as follows:
> UPDATE
> Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT
> pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND
> Nights != '')
>
> (trying to get the field Return to be "Nights"
> days after Departure, so long as Return wasn't set already and Nights
> is set, in case my aim is not clear from the attempted SQL)

Perhaps something like this:

update Events set Return = date(Departure, Nights || ' days')
where Return is null and Nights is not null;

Igor Tandetnik 



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

Re: [sqlite] Shared cache clarification.

[Hynes, Tom]

Excellent, that's what I was hoping for.  Thanks, Dan!

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dan
Sent: Wednesday, October 22, 2008 5:54 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Shared cache clarification.


On Oct 22, 2008, at 11:32 PM, Hynes, Tom wrote:

> Hello,
>
>
>
> I noticed that the documentation for PRAGMA read_uncommitted in
> http://www.sqlite.org/pragma.html says:
>
>
>
> "...Cache sharing is enabled using the sqlite3_enable_shared_cache()
>   API and is  
> only
> available between connections running the same thread. ..."
>
>
>
> But, my impression from reading the sqlite3_enable_shared_cache()
>    
> documentation is
> that this is no longer true:
>
> "... Cache sharing is enabled and disabled for an entire process. This
> is a change as of SQLite version 3.5.0. In prior versions of SQLite,
> sharing was enabled or disabled for each thread separately. ..."
>
> If I am understanding this correctly, it seems that with 3.5.0 and
> beyond connections do *not* have to live in the same thread to share a
> cache.   So, is the PRAGMA documentation just out of date, or am I
> misunderstanding this?

The pragma documentation is out of date. Pager caches may now be shared
between connections opened and used by different threads.

Dan.

___
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] Update question

[Brad Stiles]

> My stab at an SQL statement to pull this change off is as follows:
> UPDATE
> Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT
> pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights 
> != '')

Why do you think you need the sub-select?  Assuming that they syntax
for the date function is correct, why would the below not work?

UPDATE Events
SET Return = date(departure, nights + ' days')
WHERE Return = '' AND Nights != ''
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CURRENT_DATE Behavior

[Petite Abeille]

On Oct 21, 2008, at 10:44 PM, jonwood wrote:

> Given some of the comments here, one might wonder how those poor MS  
> SQL Server
> folks are able to get anything working at all. ;-)

Good point. Usually, "they" don't have anything working at all :))

Cheers,

PA.

--
http://alt.textdrive.com/nanoki/

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

[sqlite] Update question

[Jon Dixon]

Hello all,

I am updating how I handle dates/durations in a database of mine and I would 
like to come up with an SQL command to make the change for me.

The old format had a field Departure (-MM-DD) and a field Nights (an 
integer), where the new format
still uses Departure but adds Return (-MM-DD) and the duration is
calculated from the difference between Departure and Return.

My stab at an SQL statement to pull this change off is as follows:
UPDATE
Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT
pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND
Nights != '')

(trying to get the field Return to be "Nights"
days after Departure, so long as Return wasn't set already and Nights
is set, in case my aim is not clear from the attempted SQL)

I get an error from SQLite: "no such column: n"
which I'm guessing comes from trying to concatenate n and ' days' in
the date function. I'm also not sure whether I'm using the IN clause
correctly (will this also match when pkIndex is equal to "Nights"?).

So I was hoping someone on the list could give me a little steer in how to 
update the SQL to get it to do as I like.

Thanks,

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

Re: [sqlite] Shared cache clarification.

[Dan]

On Oct 22, 2008, at 11:32 PM, Hynes, Tom wrote:

> Hello,
>
>
>
> I noticed that the documentation for PRAGMA read_uncommitted in
> http://www.sqlite.org/pragma.html says:
>
>
>
> "...Cache sharing is enabled using the sqlite3_enable_shared_cache()
>   API and is  
> only
> available between connections running the same thread. ..."
>
>
>
> But, my impression from reading the sqlite3_enable_shared_cache()
>    
> documentation is
> that this is no longer true:
>
> "... Cache sharing is enabled and disabled for an entire process. This
> is a change as of SQLite version 3.5.0. In prior versions of SQLite,
> sharing was enabled or disabled for each thread separately. ..."
>
> If I am understanding this correctly, it seems that with 3.5.0 and
> beyond connections do *not* have to live in the same thread to share a
> cache.   So, is the PRAGMA documentation just out of date, or am I
> misunderstanding this?

The pragma documentation is out of date. Pager caches may now be shared
between connections opened and used by different threads.

Dan.

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

Re: [sqlite] FTS3 snippet generation speed

[P Kishor]

On 10/22/08, Tim Mohler <[EMAIL PROTECTED]> wrote:
> Hi all,
>
>  I'm happily using snippet generation in FTS3, but I notice that it has a
>  fairly large performance impact (5 seconds or more) on queries that return
>  lots of data (tens of thousands of rows) In my application, I never display
>  more than the first 20 rows returned by the MATCH operator, and I so I never
>  need to do more than 20 snippets at a time.
>
>  Based on the performance behavior my guess is that FTS3 does snippets for
>  each matching row, as opposed to finding the rows to return and then doing
>  snippets on that subset. Does anyone know if that is true?

I am not sure what you are asking here... as you yourself state above,
the snippet is applied to the matched rows not the rows you want to
display. If the rows you want to display are fewer than what you
match, reduce their number first. You could do like so --

SELECT Snippet(fts_table, '', '', '') AS foo FROM (SELECT fts_table
FROM fts_table WHERE column MATCH 'search term' LIMIT 
>  Thanks,
>  Tim
>  ___
>  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] FTS3 snippet generation speed

[Tim Mohler]

Hi all,

I'm happily using snippet generation in FTS3, but I notice that it has a
fairly large performance impact (5 seconds or more) on queries that return
lots of data (tens of thousands of rows) In my application, I never display
more than the first 20 rows returned by the MATCH operator, and I so I never
need to do more than 20 snippets at a time.

Based on the performance behavior my guess is that FTS3 does snippets for
each matching row, as opposed to finding the rows to return and then doing
snippets on that subset. Does anyone know if that is true?

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

[sqlite] Tip: Perfromance over large datasets

[Da Martian]

Hi

I suspect most of you know this, but since it helped me I decided to post
it. I have around 5 million records in a sqlite file of about 400MB. Running
joins over this was taking hours! And the disk went mental. Tweaking the
following two pragmas solved the problem though. Hours to seconds!

PRAGMA temp_store = 2;
PRAGMA cache_size = 10;

If this wasnt already so fast I would have just loaded the whole thing into
a :memory: db.

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

Re: [sqlite] CURRENT_DATE Behavior

[jonwood]

P Kishor-3 wrote:
> 
> 1. I don't fully understand how to convert UTC to local time?
> 2. on a Website
> 

For reasons I don't understand, you split one question in half and called it
two questions.


P Kishor-3 wrote:
> 
> b. On the other hand, if you want to display on the web page, time
> local to the viewer viewing the web page, but for a time stored in
> your web server for some row that you inserted or modified, then you
> use the , send it to the web
> page, and convert it there into user's local time using JavaScript.
> 

Yes, this is the converting UTC time to local time on a Website that I was
discussing. Although I've been programming for well over 20 years, I've only
been developing Websites over more recent years. So I'd probably search the
Web for an example of javascript for doing this. But it's not something I'm
doing at the moment.

At any rate, I get the basic idea.

-- 
View this message in context: 
http://www.nabble.com/CURRENT_DATE-Behavior-tp20075044p20113260.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] CURRENT_DATE Behavior

[Scott Baker]

On 10/21/2008 05:23 AM, Doug wrote:
> In an effort to share some hard-won insight:
>
> I used to store local times in the database, but it's a bad idea and I got
> bit.  Think about what gets stored: A number of seconds/nanoseconds/whatever
> from some time in the past (Jan 1, 1970 for example).  When daylight savings
> arrives, all of your stored values are now off by an hour (and if they're
> around midnight, they're on the wrong date too).  And times you save now
> will similarly be off when you leave day light savings.  It's much better to
> store UTC and then convert to local time as needed.
>
> I (and some customers) went through a lot of pain as I switched everything
> to UTC -- life has been good since then.

I complete agree with Doug here said. In my years of programming it's 
ALWAYS a good idea to *store* dates in UTC, and convert them to your 
timezone when you *display* them. Otherwise you'll always have issues of 
"this date was when DST was in effect" and this date was "when DST didn't 
count" so you're always trying to compensate.

DON'T DO THAT! Let SQLite do that work for you by storing UTC and doing the 
convert in your select statement. It will be 100x more accurate.

Depending on what you're doing with the dates, I almost always store dates 
in Unixtime, as they're much easier to work with than a string date value. 
SQLite works flawlessly with unixtime values also.

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CURRENT_DATE Behavior

[John Stanton]

jonwood wrote:
> 
> cmartin-2 wrote:
> 
>>I suspect it is quite common, e.g., web apps built on SQLServer backends 
>>are quite likely to use UTC. At any rate, it is definitely a design 
>>decision, if one expects that local times will always work in all 
>>scenarios, but all means use local times. If design criteria change 
>>unexpectedly in the future to the point where local times become 
>>problematic, for reasons already mentioned, it will need a redesign. By 
>>using UTC from the outset, it will be robust to all possible 
>>datetime-related needs in the future.
>>
> 
> 
> Yeah, I appreciate the discussion of UTC. Although I don't fully understand
> how to convert UTC to local time on a Website, I do some Web development and
> so I can imagine scenarios where they may be appropriate.

Use the timezone environment variable or similar.  You always stay out 
of trouble and fit in with best practice if you use UTC.  If you use 
local time you also need to store the TZ.  See ISO8601 for the format 
(e.g. 2008-10-22 14:39Z where Zulu time is UTC).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CURRENT_DATE Behavior

[P Kishor]

On 10/22/08, jonwood <[EMAIL PROTECTED]> wrote:
>
>
>  P Kishor-3 wrote:
>  >
>  > I thought it was very clearly indicated in one of the answers in this
>  > thread on how to do that. Here it is again...
>  >
>  > datetime('now', 'localtime')
>  >
>
>
> Yes, that was the answer. And it was implemented in my application within
>  moments of being posted. The discussion has since moved on to different
>  things.
>


Well, I have been reading the thread as well, and I am quite aware of
where the discussion has moved to.

You asked

> Yeah, I appreciate the discussion of UTC. Although I don't fully understand
>  how to convert UTC to local time on a Website, I do some Web development and
>  so I can imagine scenarios where they may be appropriate.
>

in which I see two questions (I quote below but have numbered the questions) --

1. I don't fully understand how to convert UTC to local time?
2. on a Website

The answer to #1 is datetime(, 'localtime') which you say
you have already implemented, so I am assuming you do understand how
to convert UTC to local time. Hence, #1 above is a non-question.

The answer to #2 is what I tried to allude in my discussion... "on a
Website" is not a clear articulation of the problem. Here are three
scenarios --

a. If you simply want to display on a web page, time local to the
viewer viewing the web page, you use JavaScript to show the user's
computer's time.

b. On the other hand, if you want to display on the web page, time
local to the viewer viewing the web page, but for a time stored in
your web server for some row that you inserted or modified, then you
use the , send it to the web
page, and convert it there into user's local time using JavaScript.

c. If you want to display  but
as time local to your webserver, you use datetime(, 'localtime') and send it to your web page as is.

d. If you want to display  but
as time local to you, well... I leave that and other combinations for
you to solve.

In all of these, storing time at UTC allows you to manipulate time for
display *at the time of extracting it from the db* without any
ambiguities about what time is being displayed.

Hope this helps.

-- 
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

Re: [sqlite] CURRENT_DATE Behavior

[jonwood]

P Kishor-3 wrote:
> 
> I thought it was very clearly indicated in one of the answers in this
> thread on how to do that. Here it is again...
> 
> datetime('now', 'localtime')
> 

Yes, that was the answer. And it was implemented in my application within
moments of being posted. The discussion has since moved on to different
things.
-- 
View this message in context: 
http://www.nabble.com/CURRENT_DATE-Behavior-tp20075044p20112704.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] CURRENT_DATE Behavior

[P Kishor]

On 10/22/08, jonwood <[EMAIL PROTECTED]> wrote:
>
>
>  cmartin-2 wrote:
>  >
>  > I suspect it is quite common, e.g., web apps built on SQLServer backends
>  > are quite likely to use UTC. At any rate, it is definitely a design
>  > decision, if one expects that local times will always work in all
>  > scenarios, but all means use local times. If design criteria change
>  > unexpectedly in the future to the point where local times become
>  > problematic, for reasons already mentioned, it will need a redesign. By
>  > using UTC from the outset, it will be robust to all possible
>  > datetime-related needs in the future.
>  >
>
>
> Yeah, I appreciate the discussion of UTC. Although I don't fully understand
>  how to convert UTC to local time on a Website, I do some Web development and
>  so I can imagine scenarios where they may be appropriate.
>

I thought it was very clearly indicated in one of the answers in this
thread on how to do that. Here it is again...

datetime('now', 'localtime')

The above function will take the current time in UTC and convert it to
localtime in the computer where it is run.

Your statement, "on a Website" is not clear. Do you want to convert
UTC to the local time of the user viewing your website or your local
time, or the local time of the server that is serving the website?

I am in USA Central, the company that is hosting my sites is in
California (I think) and their servers could be anywhere in the world
where they found cheap location for their data center. The user
looking at my website at any given site could be in... who knows
where.



-- 
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

Re: [sqlite] want that particular time and date when a row is inserted in a table

[P Kishor]

On 10/22/08, Hari <[EMAIL PROTECTED]> wrote:
> Hi All,
>  i want to know the particular that time and date when a row is inserted
>  in a table.
>  as i am using sqlite3_exec() function for sql query.
>  my sql statements are like:
>  sqlite3_exec(--, "create table my_table (key INTEGER,tag TEXT, timeEnter
>  DATE)", , , );
>  sqlite3_exec(--, "insert into my_table (key,TEXT) values
>  (1,'new'), , , ) ;
>  sqlite3_exec(--, "select datetime(timeEnter,'localtime') from my_table
>  where key=1", , , );
>  the third one is displaying null.
>  sqlite3_exec(--, "select * from my_table where key=1", , , )
>  it is displaying key and tag values but not giving time so how can i get
>  the time of
>  specifice row when it was inserted in table.
>  as if i use
> select datetime ('now', 'localtime') from my_tableor
> select strftime('%s','now','localtime') from my_table
>  it is giving current time. that i don't want. but i want the specific
>  row time when it was inserted.
>  am i missing something? as i want time at the time of insert, so how i
>  can get the that specific row time and date when this row was inserted.
>  suggest me any ideas..
>

Your table is

create table my_table (
  key INTEGER,
  tag TEXT,
  timeEnter DATE
)

Your insert statements are

insert into my_table (key,TEXT) values (1,'new')

Could you please tell looking from the above what will be inserted?

What you are missing is basic understanding of SQL, I mean, really
basic understanding of SQL. Any of the thousands of SQL tutorials on
the web will give you a good start.

After that, you will also be well advised to read the SQLite documents
on sqlite.org. That you are expecting the "specific row time when it
was inserted" from a column that has been declared as "timeEnter DATE"
implies that you haven't read any of the docs.


-- 
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

Re: [sqlite] [sqlite3] Macports Build Failed

[Brent Austin]

When it comes to the nuts and bolts of building this stuff I understand the 
basic stuff. I'll look into what you've suggested but I can't guarantee that 
it'll help me ;)
-Brent




From: Shane Harrelson <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, October 22, 2008 7:47:54 AM
Subject: Re: [sqlite] [sqlite3] Macports Build Failed

Taking a closer look at your output, it looks like you're using version 3.6.2.

And if you were building from the amalgamation downloads the TK_*
opcodes would have not been undefined (they are in sqlite3.c), which
means you are building from the individual sources.

The TK_* opcodes will be in parse.h, a file generated from the lemon
parser step of the build. You should check that the lemon parser
was correctly built, and that it ran successfully on the parse.y file
as part of the build.

The next part of the build combines all the sources, including the
generated parse.h file (and parse.c, etc.), into a single source file,
sqlite3.c.

You should be able to check your generate sqlite3.c file for a comment
of the form "Begin file parse.h".

HTH.
-Shane


On Wed, Oct 22, 2008 at 8:41 AM, Shane Harrelson <[EMAIL PROTECTED]> wrote:
> What version are you trying to build?  3.6.4?
> Where did you get your sources?  The SQLite website?
> Are you building from the amalgamation?  or the individual sources?
>
> -Shane
>
> On Wed, Oct 22, 2008 at 8:07 AM, Brent Austin <[EMAIL PROTECTED]> wrote:
>> I've been trying to get sqlite3 to build on my Mac under macports for ages. 
>> Any help would be appreciated--
>>
>> client-6x-1xx-17-x14:~ brent1a$ sudo port install sqlite3
>> --->  Building sqlite3 with target all
>> Error: Target org.macports.build returned: shell command " cd 
>> "/opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_databases_sqlite3/work/sqlite-3.6.2"
>>  && gnumake all " returned error 2
>> Command output: sqlite3.c:80990: error: 'TK_LSHIFT' undeclared (first use in 
>> this function)
>> sqlite3.c:80993: error: 'TK_LT' undeclared (first use in this function)
>> sqlite3.c:80999: error: 'TK_GE' undeclared (first use in this function)
>> sqlite3.c:81002: error: 'TK_RSHIFT' undeclared (first use in this function)
>> sqlite3.c:81005: error: 'TK_GT' undeclared (first use in this function)
>> sqlite3.c:81011: error: 'TK_ILLEGAL' undeclared (first use in this function)
>> sqlite3.c:81020: error: 'TK_BITOR' undeclared (first use in this function)
>> sqlite3.c:81023: error: 'TK_CONCAT' undeclared (first use in this function)
>> sqlite3.c:81028: error: 'TK_COMMA' undeclared (first use in this function)
>> sqlite3.c:81032: error: 'TK_BITAND' undeclared (first use in this function)
>> sqlite3.c:81036: error: 'TK_BITNOT' undeclared (first use in this function)
>> sqlite3.c:81053: error: 'TK_STRING' undeclared (first use in this function)
>> sqlite3.c:81056: error: 'TK_ID' undeclared (first use in this function)
>> sqlite3.c:81068: error: 'TK_DOT' undeclared (first use in this function)
>> sqlite3.c:81076: error: 'TK_INTEGER' undeclared (first use in this function)
>> sqlite3.c:81082: error: 'TK_FLOAT' undeclared (first use in this function)
>> sqlite3.c:81102: warning: assignment makes integer from pointer without a 
>> cast
>> sqlite3.c:81106: error: 'TK_VARIABLE' undeclared (first use in this function)
>> sqlite3.c:81115: error: 'TK_REGISTER' undeclared (first use in this function)
>> sqlite3.c:81155: error: 'TK_BLOB' undeclared (first use in this function)
>> sqlite3.c: In function 'sqlite3RunParser':
>> sqlite3.c:81227: error: 'TK_SPACE' undeclared (first use in this function)
>> sqlite3.c:81227: warning: case label value exceeds maximum value for type
>> sqlite3.c:81235: error: 'TK_ILLEGAL' undeclared (first use in this function)
>> sqlite3.c:81235: warning: case label value exceeds maximum value for type
>> sqlite3.c:81242: error: 'TK_SEMI' undeclared (first use in this function)
>> sqlite3.c:81242: warning: case label value exceeds maximum value for type
>> sqlite3.c:81258: warning: comparison between pointer and integer
>> sqlite3.c:81259: warning: passing argument 2 of 'sqlite3Parser' makes 
>> integer from pointer without a cast
>> gnumake: *** [sqlite3.lo] Error 1
>>
>> Error: Status 1 encountered during processing.
>>
>>
>>
>>
>> ___
>> 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] [sqlite3] Macports Build Failed

[Brent Austin]

I believe it's 3.6.2 as provided by the MacPorts system (used to be DarwinPorts 
similar to FINK).

I've successfully installed 3.6.4 from source outside of the MacPorts system 
but it won't register when I'm building other sources or programs.




From: Shane Harrelson <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, October 22, 2008 7:41:31 AM
Subject: Re: [sqlite] [sqlite3] Macports Build Failed

What version are you trying to build?  3.6.4?
Where did you get your sources?  The SQLite website?
Are you building from the amalgamation?  or the individual sources?

-Shane

On Wed, Oct 22, 2008 at 8:07 AM, Brent Austin <[EMAIL PROTECTED]> wrote:
> I've been trying to get sqlite3 to build on my Mac under macports for ages. 
> Any help would be appreciated--
>
> client-6x-1xx-17-x14:~ brent1a$ sudo port install sqlite3
> --->  Building sqlite3 with target all
> Error: Target org.macports.build returned: shell command " cd 
> "/opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_databases_sqlite3/work/sqlite-3.6.2"
>  && gnumake all " returned error 2
> Command output: sqlite3.c:80990: error: 'TK_LSHIFT' undeclared (first use in 
> this function)
> sqlite3.c:80993: error: 'TK_LT' undeclared (first use in this function)
> sqlite3.c:80999: error: 'TK_GE' undeclared (first use in this function)
> sqlite3.c:81002: error: 'TK_RSHIFT' undeclared (first use in this function)
> sqlite3.c:81005: error: 'TK_GT' undeclared (first use in this function)
> sqlite3.c:81011: error: 'TK_ILLEGAL' undeclared (first use in this function)
> sqlite3.c:81020: error: 'TK_BITOR' undeclared (first use in this function)
> sqlite3.c:81023: error: 'TK_CONCAT' undeclared (first use in this function)
> sqlite3.c:81028: error: 'TK_COMMA' undeclared (first use in this function)
> sqlite3.c:81032: error: 'TK_BITAND' undeclared (first use in this function)
> sqlite3.c:81036: error: 'TK_BITNOT' undeclared (first use in this function)
> sqlite3.c:81053: error: 'TK_STRING' undeclared (first use in this function)
> sqlite3.c:81056: error: 'TK_ID' undeclared (first use in this function)
> sqlite3.c:81068: error: 'TK_DOT' undeclared (first use in this function)
> sqlite3.c:81076: error: 'TK_INTEGER' undeclared (first use in this function)
> sqlite3.c:81082: error: 'TK_FLOAT' undeclared (first use in this function)
> sqlite3.c:81102: warning: assignment makes integer from pointer without a cast
> sqlite3.c:81106: error: 'TK_VARIABLE' undeclared (first use in this function)
> sqlite3.c:81115: error: 'TK_REGISTER' undeclared (first use in this function)
> sqlite3.c:81155: error: 'TK_BLOB' undeclared (first use in this function)
> sqlite3.c: In function 'sqlite3RunParser':
> sqlite3.c:81227: error: 'TK_SPACE' undeclared (first use in this function)
> sqlite3.c:81227: warning: case label value exceeds maximum value for type
> sqlite3.c:81235: error: 'TK_ILLEGAL' undeclared (first use in this function)
> sqlite3.c:81235: warning: case label value exceeds maximum value for type
> sqlite3.c:81242: error: 'TK_SEMI' undeclared (first use in this function)
> sqlite3.c:81242: warning: case label value exceeds maximum value for type
> sqlite3.c:81258: warning: comparison between pointer and integer
> sqlite3.c:81259: warning: passing argument 2 of 'sqlite3Parser' makes integer 
> from pointer without a cast
> gnumake: *** [sqlite3.lo] Error 1
>
> Error: Status 1 encountered during processing.
>
>
>
>
> ___
> 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] [sqlite3] Macports Build Failed

[Shane Harrelson]

Taking a closer look at your output, it looks like you're using version 3.6.2.

And if you were building from the amalgamation downloads the TK_*
opcodes would have not been undefined (they are in sqlite3.c), which
means you are building from the individual sources.

The TK_* opcodes will be in parse.h, a file generated from the lemon
parser step of the build. You should check that the lemon parser
was correctly built, and that it ran successfully on the parse.y file
as part of the build.

The next part of the build combines all the sources, including the
generated parse.h file (and parse.c, etc.), into a single source file,
sqlite3.c.

You should be able to check your generate sqlite3.c file for a comment
of the form "Begin file parse.h".

HTH.
-Shane


On Wed, Oct 22, 2008 at 8:41 AM, Shane Harrelson <[EMAIL PROTECTED]> wrote:
> What version are you trying to build?  3.6.4?
> Where did you get your sources?  The SQLite website?
> Are you building from the amalgamation?  or the individual sources?
>
> -Shane
>
> On Wed, Oct 22, 2008 at 8:07 AM, Brent Austin <[EMAIL PROTECTED]> wrote:
>> I've been trying to get sqlite3 to build on my Mac under macports for ages. 
>> Any help would be appreciated--
>>
>> client-6x-1xx-17-x14:~ brent1a$ sudo port install sqlite3
>> --->  Building sqlite3 with target all
>> Error: Target org.macports.build returned: shell command " cd 
>> "/opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_databases_sqlite3/work/sqlite-3.6.2"
>>  && gnumake all " returned error 2
>> Command output: sqlite3.c:80990: error: 'TK_LSHIFT' undeclared (first use in 
>> this function)
>> sqlite3.c:80993: error: 'TK_LT' undeclared (first use in this function)
>> sqlite3.c:80999: error: 'TK_GE' undeclared (first use in this function)
>> sqlite3.c:81002: error: 'TK_RSHIFT' undeclared (first use in this function)
>> sqlite3.c:81005: error: 'TK_GT' undeclared (first use in this function)
>> sqlite3.c:81011: error: 'TK_ILLEGAL' undeclared (first use in this function)
>> sqlite3.c:81020: error: 'TK_BITOR' undeclared (first use in this function)
>> sqlite3.c:81023: error: 'TK_CONCAT' undeclared (first use in this function)
>> sqlite3.c:81028: error: 'TK_COMMA' undeclared (first use in this function)
>> sqlite3.c:81032: error: 'TK_BITAND' undeclared (first use in this function)
>> sqlite3.c:81036: error: 'TK_BITNOT' undeclared (first use in this function)
>> sqlite3.c:81053: error: 'TK_STRING' undeclared (first use in this function)
>> sqlite3.c:81056: error: 'TK_ID' undeclared (first use in this function)
>> sqlite3.c:81068: error: 'TK_DOT' undeclared (first use in this function)
>> sqlite3.c:81076: error: 'TK_INTEGER' undeclared (first use in this function)
>> sqlite3.c:81082: error: 'TK_FLOAT' undeclared (first use in this function)
>> sqlite3.c:81102: warning: assignment makes integer from pointer without a 
>> cast
>> sqlite3.c:81106: error: 'TK_VARIABLE' undeclared (first use in this function)
>> sqlite3.c:81115: error: 'TK_REGISTER' undeclared (first use in this function)
>> sqlite3.c:81155: error: 'TK_BLOB' undeclared (first use in this function)
>> sqlite3.c: In function 'sqlite3RunParser':
>> sqlite3.c:81227: error: 'TK_SPACE' undeclared (first use in this function)
>> sqlite3.c:81227: warning: case label value exceeds maximum value for type
>> sqlite3.c:81235: error: 'TK_ILLEGAL' undeclared (first use in this function)
>> sqlite3.c:81235: warning: case label value exceeds maximum value for type
>> sqlite3.c:81242: error: 'TK_SEMI' undeclared (first use in this function)
>> sqlite3.c:81242: warning: case label value exceeds maximum value for type
>> sqlite3.c:81258: warning: comparison between pointer and integer
>> sqlite3.c:81259: warning: passing argument 2 of 'sqlite3Parser' makes 
>> integer from pointer without a cast
>> gnumake: *** [sqlite3.lo] Error 1
>>
>> Error: Status 1 encountered during processing.
>>
>>
>>
>>
>> ___
>> 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] [sqlite3] Macports Build Failed

[Shane Harrelson]

What version are you trying to build?  3.6.4?
Where did you get your sources?  The SQLite website?
Are you building from the amalgamation?  or the individual sources?

-Shane

On Wed, Oct 22, 2008 at 8:07 AM, Brent Austin <[EMAIL PROTECTED]> wrote:
> I've been trying to get sqlite3 to build on my Mac under macports for ages. 
> Any help would be appreciated--
>
> client-6x-1xx-17-x14:~ brent1a$ sudo port install sqlite3
> --->  Building sqlite3 with target all
> Error: Target org.macports.build returned: shell command " cd 
> "/opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_databases_sqlite3/work/sqlite-3.6.2"
>  && gnumake all " returned error 2
> Command output: sqlite3.c:80990: error: 'TK_LSHIFT' undeclared (first use in 
> this function)
> sqlite3.c:80993: error: 'TK_LT' undeclared (first use in this function)
> sqlite3.c:80999: error: 'TK_GE' undeclared (first use in this function)
> sqlite3.c:81002: error: 'TK_RSHIFT' undeclared (first use in this function)
> sqlite3.c:81005: error: 'TK_GT' undeclared (first use in this function)
> sqlite3.c:81011: error: 'TK_ILLEGAL' undeclared (first use in this function)
> sqlite3.c:81020: error: 'TK_BITOR' undeclared (first use in this function)
> sqlite3.c:81023: error: 'TK_CONCAT' undeclared (first use in this function)
> sqlite3.c:81028: error: 'TK_COMMA' undeclared (first use in this function)
> sqlite3.c:81032: error: 'TK_BITAND' undeclared (first use in this function)
> sqlite3.c:81036: error: 'TK_BITNOT' undeclared (first use in this function)
> sqlite3.c:81053: error: 'TK_STRING' undeclared (first use in this function)
> sqlite3.c:81056: error: 'TK_ID' undeclared (first use in this function)
> sqlite3.c:81068: error: 'TK_DOT' undeclared (first use in this function)
> sqlite3.c:81076: error: 'TK_INTEGER' undeclared (first use in this function)
> sqlite3.c:81082: error: 'TK_FLOAT' undeclared (first use in this function)
> sqlite3.c:81102: warning: assignment makes integer from pointer without a cast
> sqlite3.c:81106: error: 'TK_VARIABLE' undeclared (first use in this function)
> sqlite3.c:81115: error: 'TK_REGISTER' undeclared (first use in this function)
> sqlite3.c:81155: error: 'TK_BLOB' undeclared (first use in this function)
> sqlite3.c: In function 'sqlite3RunParser':
> sqlite3.c:81227: error: 'TK_SPACE' undeclared (first use in this function)
> sqlite3.c:81227: warning: case label value exceeds maximum value for type
> sqlite3.c:81235: error: 'TK_ILLEGAL' undeclared (first use in this function)
> sqlite3.c:81235: warning: case label value exceeds maximum value for type
> sqlite3.c:81242: error: 'TK_SEMI' undeclared (first use in this function)
> sqlite3.c:81242: warning: case label value exceeds maximum value for type
> sqlite3.c:81258: warning: comparison between pointer and integer
> sqlite3.c:81259: warning: passing argument 2 of 'sqlite3Parser' makes integer 
> from pointer without a cast
> gnumake: *** [sqlite3.lo] Error 1
>
> Error: Status 1 encountered during processing.
>
>
>
>
> ___
> 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] [sqlite3] Macports Build Failed

[Brent Austin]

I've been trying to get sqlite3 to build on my Mac under macports for ages. Any 
help would be appreciated--

client-6x-1xx-17-x14:~ brent1a$ sudo port install sqlite3
--->  Building sqlite3 with target all
Error: Target org.macports.build returned: shell command " cd 
"/opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_databases_sqlite3/work/sqlite-3.6.2"
 && gnumake all " returned error 2
Command output: sqlite3.c:80990: error: 'TK_LSHIFT' undeclared (first use in 
this function)
sqlite3.c:80993: error: 'TK_LT' undeclared (first use in this function)
sqlite3.c:80999: error: 'TK_GE' undeclared (first use in this function)
sqlite3.c:81002: error: 'TK_RSHIFT' undeclared (first use in this function)
sqlite3.c:81005: error: 'TK_GT' undeclared (first use in this function)
sqlite3.c:81011: error: 'TK_ILLEGAL' undeclared (first use in this function)
sqlite3.c:81020: error: 'TK_BITOR' undeclared (first use in this function)
sqlite3.c:81023: error: 'TK_CONCAT' undeclared (first use in this function)
sqlite3.c:81028: error: 'TK_COMMA' undeclared (first use in this function)
sqlite3.c:81032: error: 'TK_BITAND' undeclared (first use in this function)
sqlite3.c:81036: error: 'TK_BITNOT' undeclared (first use in this function)
sqlite3.c:81053: error: 'TK_STRING' undeclared (first use in this function)
sqlite3.c:81056: error: 'TK_ID' undeclared (first use in this function)
sqlite3.c:81068: error: 'TK_DOT' undeclared (first use in this function)
sqlite3.c:81076: error: 'TK_INTEGER' undeclared (first use in this function)
sqlite3.c:81082: error: 'TK_FLOAT' undeclared (first use in this function)
sqlite3.c:81102: warning: assignment makes integer from pointer without a cast
sqlite3.c:81106: error: 'TK_VARIABLE' undeclared (first use in this function)
sqlite3.c:81115: error: 'TK_REGISTER' undeclared (first use in this function)
sqlite3.c:81155: error: 'TK_BLOB' undeclared (first use in this function)
sqlite3.c: In function 'sqlite3RunParser':
sqlite3.c:81227: error: 'TK_SPACE' undeclared (first use in this function)
sqlite3.c:81227: warning: case label value exceeds maximum value for type
sqlite3.c:81235: error: 'TK_ILLEGAL' undeclared (first use in this function)
sqlite3.c:81235: warning: case label value exceeds maximum value for type
sqlite3.c:81242: error: 'TK_SEMI' undeclared (first use in this function)
sqlite3.c:81242: warning: case label value exceeds maximum value for type
sqlite3.c:81258: warning: comparison between pointer and integer
sqlite3.c:81259: warning: passing argument 2 of 'sqlite3Parser' makes integer 
from pointer without a cast
gnumake: *** [sqlite3.lo] Error 1

Error: Status 1 encountered during processing.



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

Re: [sqlite] want that particular time and date when a row is insertedin a table

[Igor Tandetnik]

"Hari" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> i want to know the particular that time and date when a row is
> inserted in a table.
> as i am using sqlite3_exec() function for sql query.
> my sql statements are like:
> sqlite3_exec(--, "create table my_table (key INTEGER,tag TEXT,
> timeEnter DATE)", , , );

create table my_table (key INTEGER,tag TEXT, timeEnter DATE DEFAULT 
CURRENT_TIMESTAMP);

Igor Tandetnik



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

Re: [sqlite] Foreign Key Constraints

[Igor Tandetnik]

"Daniel Zingaro"
<[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> I'm wondering if anyone can help me understand the discrepancy here.
> Are recursive triggers required to implement some FK constraints?

I can't think of any reason why, except perhaps if you have foreign keys 
referring to the same table, forming a tree-like structure. As in

create table tree(nodeid integer primary key, nodeinfo text, 
parentid integer);

where parentid is a foreign key into tree(nodeid) (in other words, 
parent of a node is itself a node). With such a table, it is reasonable 
to expect that deleting a node deletes the whole subtree under it - but 
that's impossible to achieve with triggers in SQLite.

Igor Tandetnik



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

Re: [sqlite] Why is subselect much faster than join?

[MikeW]

cody <[EMAIL PROTECTED]> writes:

> 
> Why is this (1,44 seconds)
> 
> select * from movies m
> where m.movieid in (select mp.movieid from people p, moviepeople mp 
> where p.fullname="Spencer, Bud" and mp.personid=p.personid)
> 
> much faster than the following (Runtime several minutes!):
> 
> select * from movies m
> join moviepeople mp on mp.movieid=m.movieid
> join people p on p.personid=mp.personid
> where p.fullname="Spencer, Bud"
> 
> Shouldn't it perform equally?
> ...
> The database was currently vacuumed and analyzed.
> 

Have you tried asking it to EXPLAIN ?

It may be that the JOIN does the join on the whole tables first,
then applying the WHERE, whereas the subselect is working on a much
reduced subset of data.

Google for "subselect faster than join" - some interesting comments !
(e.g. suggesting that EXISTS might be faster again than IN)

Regards,
MikeW



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

[sqlite] Why is subselect much faster than join?

[cody]

Why is this (1,44 seconds)

select * from movies m
where m.movieid in (select mp.movieid from people p, moviepeople mp 
where p.fullname="Spencer, Bud" and mp.personid=p.personid)

much faster than the following (Runtime several minutes!):

select * from movies m
join moviepeople mp on mp.movieid=m.movieid
join people p on p.personid=mp.personid
where p.fullname="Spencer, Bud"

Shouldn't it perform equally?

This is my table structure:

CREATE TABLE moviepeople (
movieid integer NOT NULL,
personid integer NOT NULL,
);

CREATE TABLE people (
personid integer primary key NOT NULL,
fullname character varying(200) NOT NULL,
);

CREATE TABLE movies (
movieid integer primary key NOT NULL,
moviename character varying(300) NOT NULL,
);

create unique index if not exists movies_moviename_key on movies(moviename);
create unique index if not exists people_fullname_key on people(fullname);
create unique index if not exists people_personid_key on 
people(personid); // is this duplicate because this is already primary key?
create unique index if not exists movies_movieid_key on movies(movieid);

create index if not exists moviepeople_movieid_idx on moviepeople (movieid);
create index if not exists moviepeople_personid_idx on moviepeople 
(personid);

The database was currently vacuumed and analyzed.

-- 
Fun stuff, Java and C# programming, freeware games, applications and tools

www.deutronium.de.vu | www.deutronium.de.ki

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

Re: [sqlite] SqLite Report Generator For Windows

[Rajesh Nair]

Thanks a lot.
Actually I don't want to use ODBC. If I use ODBC then I will be able to use
Crystall Report. I don't want ODBC in my project, don't even want to create
it using code itself. That's why I am using SQLITE. I don't have enough time
to develop a reporting control of my own ( Only to fullfill my needs ).
That's why I am searching the net. I will test those you suggested. I thing
the FastReport may be good one. Let me test it. Once angain THANKS for your
support.

Rajesh Nair





On Tue, Oct 21, 2008 at 12:39 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote:

> On Mon, 20 Oct 2008 12:26:33 +0530, "Rajesh Nair"
> <[EMAIL PROTECTED]> wrote in General Discussion of SQLite
> Database :
>
> >Is there any free SqLite3 *Report Generator For MS-Windows.* I searched
> the
> >web with Google but no result. All are commercial and/or with .NET, I want
> >it use with VC++ 6.0. Even a COM object can solve my problem. Have any one
> >developed such an utility that can be shared with all of us. Please reply.
> >With Report Generator I mean *a report template designer, and a report
> >viewer.* A report template designer will be enough for me to cope with my
> >current problem. Any one please reply. I have also read a mail long before
> >in sqlite group regarding this. There is one I found in CodeProject.COM
> and
> >the link is given below.
> >www.codeproject.com/KB/printing/ReportGenerator.aspx
> >It is good but not the one what I needed. Also a C# project is also
> provided
> >in CodeProject that uses the above project but I cannot use it since it is
> >C#. I want it in VC++. If some one have such a thing please share it.
>
> SQLiteExplorer (by Mike Cariotoglou) has an optional report
> designer/generator.
>
> See: http://www.singular.gr/sqlite/ (close to the bottom of the page)
>
> It should be possible to use any report generator that can connect to
> ODBC (even MS Access) together with one of the SQLite ODBC drivers.
>
> See also (you probably already found):
> http://www.sqlite.org/cvstrac/wiki?p=SqliteReporting
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
>
> Good luck.
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] CURRENT_DATE Behavior

[MikeW]

MikeW <[EMAIL PROTECTED]> writes:
>
> jonwood <[EMAIL PROTECTED]> writes:
>
>> Greetings,
>> ...
>> Is there ANY way to override this behaviour? Or must I simply initialize all
>> such fields explicitly if I would like to set it to the current date in my
>> particular time zone?
>> 
>> Thanks.
>
...
>
> Using UTC in the DB stops you going mad when something happens
> on the DST changeover (localtime hours vanish, or happen twice),
> or you have systems running in or across different countries.
>
> It also means you can subtract two times and always get the right answer
> for elapsed time !
>
> Regards,
> MikeW


That said, MySQL does provide both CURRENT_DATE/TIME and UTC_DATE/TIME ...
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

I believe in reading around !

MikeW


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

[sqlite] want that particular time and date when a row is inserted in a table

[Hari]

Hi All,
i want to know the particular that time and date when a row is inserted
in a table.
as i am using sqlite3_exec() function for sql query.
my sql statements are like:
sqlite3_exec(--, "create table my_table (key INTEGER,tag TEXT, timeEnter
DATE)", , , );
sqlite3_exec(--, "insert into my_table (key,TEXT) values
(1,'new'), , , ) ;
sqlite3_exec(--, "select datetime(timeEnter,'localtime') from my_table
where key=1", , , );
the third one is displaying null.
sqlite3_exec(--, "select * from my_table where key=1", , , )
it is displaying key and tag values but not giving time so how can i get
the time of  
specifice row when it was inserted in table. 
as if i use 
select datetime ('now', 'localtime') from my_tableor
select strftime('%s','now','localtime') from my_table
it is giving current time. that i don't want. but i want the specific
row time when it was inserted.
am i missing something? as i want time at the time of insert, so how i
can get the that specific row time and date when this row was inserted.
suggest me any ideas..

thanks

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