[sqlite] Creating recurrent appointments?

2008-09-01 Thread Gilles Ganault
Hello

I need to write an application to manage appointments. Most of them
are recurrent, with no end-date, while others are one-shots, and the
user must be able to schedule reccurent appointments but be able to
tweak some of them if need be.

To investigate whether I need to bother keeping two tables (one-shots,
recurrent, and merge both to display the actual appointment book), I'd
like to see how SQLite performs if I use the brute-force approach,
which is pre-creating appointments till eg. 2100. Not very smart, but
if it works well...

Do I need to loop through this, or is there a command that lets me use
a single INSERT using something like pseudo "START_DATE, END_DATE,
FREQUENCY_EVERY_MONDAY_AT_10AM", etc?

==
sqlite> create table agenda (id INTEGER PRIMARY KEY, date VARCHAR,
label VARCHAR
);
sqlite> insert into agenda (id,date,label) values (NULL,"20080902",
"Test");
sqlite> commit;
==

Thank you.

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


Re: [sqlite] Creating recurrent appointments?

2008-09-01 Thread Mihai Limbasan

Gilles Ganault wrote:

Hello

I need to write an application to manage appointments. Most of them
are recurrent, with no end-date, while others are one-shots, and the
user must be able to schedule reccurent appointments but be able to
tweak some of them if need be.

To investigate whether I need to bother keeping two tables (one-shots,
recurrent, and merge both to display the actual appointment book), I'd
like to see how SQLite performs if I use the brute-force approach,
which is pre-creating appointments till eg. 2100. Not very smart, but
if it works well...

Do I need to loop through this, or is there a command that lets me use
a single INSERT using something like pseudo "START_DATE, END_DATE,
FREQUENCY_EVERY_MONDAY_AT_10AM", etc?

==
sqlite> create table agenda (id INTEGER PRIMARY KEY, date VARCHAR,
label VARCHAR
);
sqlite> insert into agenda (id,date,label) values (NULL,"20080902",
"Test");
sqlite> commit;
==

Thank you.
  

You will need to loop through this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] statement is not executing

2008-09-01 Thread Greg Morphis
To further complicate things..  I removed all of the s
and the code works.. I readd them and I get the same

Error Executing Database Query.
statement is not executing

The error occurred in
C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
line 92

The Stack Trace looks like :

java.sql.SQLException: statement is not executing
at org.sqlite.Stmt.checkOpen(Stmt.java:41)
at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
at 
coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
at coldfusion.sql.Executive.getRowSet(Executive.java:513)
at coldfusion.sql.Executive.executeQuery(Executive.java:1205)
at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
at coldfusion.sql.Executive.executeQuery(Executive.java:939)
...




On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
> I think it has to do with the JDBC driver I'm using.. I updated to
> v053 of the sqlitejdbc driver and am still getting the error..
>
>
> On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>> I've got ColdFusion 8 connecting to my SQLite3 database..
>>
>> When I run an insert using CFCs I get the error :
>>  Error Executing Database Query.
>> statement is not executing
>>
>>
>> What's odd is that the row gets inserted into the table...
>> If I hit refresh in the browser I get the error that the Primary Key
>> must be unique..
>>
>> Anyone ever seen this?
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] statement is not executing

2008-09-01 Thread P Kishor
On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
> To further complicate things..  I removed all of the s
>  and the code works.. I readd them and I get the same
>
>
>  Error Executing Database Query.
>  statement is not executing
>
>
> The error occurred in
>  C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
>  line 92
>
>  The Stack Trace looks like :
>
>  java.sql.SQLException: statement is not executing
> at org.sqlite.Stmt.checkOpen(Stmt.java:41)
> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
> at 
> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
> at coldfusion.sql.Executive.getRowSet(Executive.java:513)
> at coldfusion.sql.Executive.executeQuery(Executive.java:1205)
> at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
> at coldfusion.sql.Executive.executeQuery(Executive.java:939)
>  ...
>
>
>
>
>
>  On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>  > I think it has to do with the JDBC driver I'm using.. I updated to
>  > v053 of the sqlitejdbc driver and am still getting the error..
>  >
>  >
>  > On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>  >> I've got ColdFusion 8 connecting to my SQLite3 database..
>  >>
>  >> When I run an insert using CFCs I get the error :
>  >>  Error Executing Database Query.
>  >> statement is not executing
>  >>
>  >>
>  >> What's odd is that the row gets inserted into the table...
>  >> If I hit refresh in the browser I get the error that the Primary Key
>  >> must be unique..
>  >>
>  >> Anyone ever seen this?
>  >>
>  >


Hi Greg,

(a long time ago I used to use CF circa version 2 and 3).

As you have yourself identified, the problem seems to be either with
your own queries, examples of which you have not provided in any of
your emails, or in the JDBC driver. Since neither CF nor the JDBC
driver are the focus of this list, you are unlikely to get much
response here... after all, this is a SQLite list, and SQLite seems to
be working fine.

Perhaps if you were to provide example of your code, the query you are
doing, perhaps someone might be able to point out possible
logic/syntax errors, but other than that, you will likely get more
mileage from either CF list or from the provider of the JDBC driver.

Fwiw, I have found that the JDBC driver does work fine, but most of my
queries have been rather simple via straight-ahead Java.



-- 
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] statement is not executing

2008-09-01 Thread Greg Morphis
I understand this is a sqlite list, I had hoped that I wasnt the only
one using it or trying to use it with ColdFusion..
I've also asked this on a CF list, just hoping somewhere out there is
the answer..

Anyways, as I just posted the problem seems to be around the
 wrote:
> On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>> To further complicate things..  I removed all of the s
>>  and the code works.. I readd them and I get the same
>>
>>
>>  Error Executing Database Query.
>>  statement is not executing
>>
>>
>> The error occurred in
>>  
>> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
>>  line 92
>>
>>  The Stack Trace looks like :
>>
>>  java.sql.SQLException: statement is not executing
>> at org.sqlite.Stmt.checkOpen(Stmt.java:41)
>> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
>> at 
>> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
>> at coldfusion.sql.Executive.getRowSet(Executive.java:513)
>> at coldfusion.sql.Executive.executeQuery(Executive.java:1205)
>> at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
>> at coldfusion.sql.Executive.executeQuery(Executive.java:939)
>>  ...
>>
>>
>>
>>
>>
>>  On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>>  > I think it has to do with the JDBC driver I'm using.. I updated to
>>  > v053 of the sqlitejdbc driver and am still getting the error..
>>  >
>>  >
>>  > On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>>  >> I've got ColdFusion 8 connecting to my SQLite3 database..
>>  >>
>>  >> When I run an insert using CFCs I get the error :
>>  >>  Error Executing Database Query.
>>  >> statement is not executing
>>  >>
>>  >>
>>  >> What's odd is that the row gets inserted into the table...
>>  >> If I hit refresh in the browser I get the error that the Primary Key
>>  >> must be unique..
>>  >>
>>  >> Anyone ever seen this?
>>  >>
>>  >
>
>
> Hi Greg,
>
> (a long time ago I used to use CF circa version 2 and 3).
>
> As you have yourself identified, the problem seems to be either with
> your own queries, examples of which you have not provided in any of
> your emails, or in the JDBC driver. Since neither CF nor the JDBC
> driver are the focus of this list, you are unlikely to get much
> response here... after all, this is a SQLite list, and SQLite seems to
> be working fine.
>
> Perhaps if you were to provide example of your code, the query you are
> doing, perhaps someone might be able to point out possible
> logic/syntax errors, but other than that, you will likely get more
> mileage from either CF list or from the provider of the JDBC driver.
>
> Fwiw, I have found that the JDBC driver does work fine, but most of my
> queries have been rather simple via straight-ahead Java.
>
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] statement is not executing

2008-09-01 Thread P Kishor
On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
> I understand this is a sqlite list, I had hoped that I wasnt the only
>  one using it or trying to use it with ColdFusion..
>  I've also asked this on a CF list, just hoping somewhere out there is
>  the answer..

No, no... don't get me wrong. There is no problem with describing the
tools you are using (CF and JDBC driver, in this case). However, you
have given no other context... no db schema, no example code, the
query you are executing, etc. There is little here for anyone to try
and help, other than if there happens to be another CF/SQLite/JDBC
user.

Just offer more details, and you have a slightly better chance of
getting a meaningful reply.

>
>  Anyways, as I just posted the problem seems to be around the
>  
>  I suppose since this is a local app, I don't need the   tags, just looking for answers...
>
>  Thanks
>
>
>
>  On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote:
>  > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>  >> To further complicate things..  I removed all of the s
>  >>  and the code works.. I readd them and I get the same
>  >>
>  >>
>  >>  Error Executing Database Query.
>  >>  statement is not executing
>  >>
>  >>
>  >> The error occurred in
>  >>  
> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
>  >>  line 92
>  >>
>  >>  The Stack Trace looks like :
>  >>
>  >>  java.sql.SQLException: statement is not executing
>  >> at org.sqlite.Stmt.checkOpen(Stmt.java:41)
>  >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
>  >> at 
> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
>  >> at coldfusion.sql.Executive.getRowSet(Executive.java:513)
>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:1205)
>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:939)
>  >>  ...
>  >>
>  >>
>  >>
>  >>
>  >>
>  >>  On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>  >>  > I think it has to do with the JDBC driver I'm using.. I updated to
>  >>  > v053 of the sqlitejdbc driver and am still getting the error..
>  >>  >
>  >>  >
>  >>  > On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> 
> wrote:
>  >>  >> I've got ColdFusion 8 connecting to my SQLite3 database..
>  >>  >>
>  >>  >> When I run an insert using CFCs I get the error :
>  >>  >>  Error Executing Database Query.
>  >>  >> statement is not executing
>  >>  >>
>  >>  >>
>  >>  >> What's odd is that the row gets inserted into the table...
>  >>  >> If I hit refresh in the browser I get the error that the Primary Key
>  >>  >> must be unique..
>  >>  >>
>  >>  >> Anyone ever seen this?
>  >>  >>
>  >>  >
>  >
>  >
>  > Hi Greg,
>  >
>  > (a long time ago I used to use CF circa version 2 and 3).
>  >
>  > As you have yourself identified, the problem seems to be either with
>  > your own queries, examples of which you have not provided in any of
>  > your emails, or in the JDBC driver. Since neither CF nor the JDBC
>  > driver are the focus of this list, you are unlikely to get much
>  > response here... after all, this is a SQLite list, and SQLite seems to
>  > be working fine.
>  >
>  > Perhaps if you were to provide example of your code, the query you are
>  > doing, perhaps someone might be able to point out possible
>  > logic/syntax errors, but other than that, you will likely get more
>  > mileage from either CF list or from the provider of the JDBC driver.
>  >
>  > Fwiw, I have found that the JDBC driver does work fine, but most of my
>  > queries have been rather simple via straight-ahead Java.
>  >
>  >
>  >
>  > --
>  > 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
>  >
>


-- 
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] statement is not executing

2008-09-01 Thread Greg Morphis
It's just a simple insert into table (columns) values (values)



   

   

 
INSERT INTO
   BILLS
   (
  PAYEEID,
  PAYEE,
  MINIMUMDUE,
  DUEDAY,
  ISCC,
  ISACTIVE,
  AMOUNTOWED,
  APR
   )
VALUES
   (
  , 

  ,
  ,
  ,
  ,
  ,
  ,
  
   )
 

   



and the DB looks like..

CREATE TABLE bills
(
  payeeid integer primary key,
  payee TEXT,
  minimumdue numeric,
  apr numeric,
  amountowed real,
  iscc numeric DEFAULT 1,
  dueday TEXT,
  isactive numeric DEFAULT 1
)







On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>> I understand this is a sqlite list, I had hoped that I wasnt the only
>>  one using it or trying to use it with ColdFusion..
>>  I've also asked this on a CF list, just hoping somewhere out there is
>>  the answer..
>
> No, no... don't get me wrong. There is no problem with describing the
> tools you are using (CF and JDBC driver, in this case). However, you
> have given no other context... no db schema, no example code, the
> query you are executing, etc. There is little here for anyone to try
> and help, other than if there happens to be another CF/SQLite/JDBC
> user.
>
> Just offer more details, and you have a slightly better chance of
> getting a meaningful reply.
>
>>
>>  Anyways, as I just posted the problem seems to be around the
>>  >
>>  I suppose since this is a local app, I don't need the >  tags, just looking for answers...
>>
>>  Thanks
>>
>>
>>
>>  On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote:
>>  > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>>  >> To further complicate things..  I removed all of the s
>>  >>  and the code works.. I readd them and I get the same
>>  >>
>>  >>
>>  >>  Error Executing Database Query.
>>  >>  statement is not executing
>>  >>
>>  >>
>>  >> The error occurred in
>>  >>  
>> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
>>  >>  line 92
>>  >>
>>  >>  The Stack Trace looks like :
>>  >>
>>  >>  java.sql.SQLException: statement is not executing
>>  >> at org.sqlite.Stmt.checkOpen(Stmt.java:41)
>>  >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
>>  >> at 
>> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
>>  >> at coldfusion.sql.Executive.getRowSet(Executive.java:513)
>>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:1205)
>>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
>>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:939)
>>  >>  ...
>>  >>
>>  >>
>>  >>
>>  >>
>>  >>
>>  >>  On Sun, Aug 31, 2008 at 6:03 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>>  >>  > I think it has to do with the JDBC driver I'm using.. I updated to
>>  >>  > v053 of the sqlitejdbc driver and am still getting the error..
>>  >>  >
>>  >>  >
>>  >>  > On Sun, Aug 31, 2008 at 5:27 PM, Greg Morphis <[EMAIL PROTECTED]> 
>> wrote:
>>  >>  >> I've got ColdFusion 8 connecting to my SQLite3 database..
>>  >>  >>
>>  >>  >> When I run an insert using CFCs I get the error :
>>  >>  >>  Error Executing Database Query.
>>  >>  >> statement is not executing
>>  >>  >>
>>  >>  >>
>>  >>  >> What's odd is that the row gets inserted into the table...
>>  >>  >> If I hit refresh in the browser I get the error that the Primary Key
>>  >>  >> must be unique..
>>  >>  >>
>>  >>  >> Anyone ever seen this?
>>  >>  >>
>>  >>  >
>>  >
>>  >
>>  > Hi Greg,
>>  >
>>  > (a long time ago I used to use CF circa version 2 and 3).
>>  >
>>  > As you have yourself identified, the problem seems to be either with
>>  > your own queries, examples of which you have not provided in any of
>>  > your emails, or in the JDBC driver. Since neither CF nor the JDBC
>>  > driver are the focus of this list, you are unlikely to get much
>>  > response here... after all, this is a SQLite list, and SQLite seems to
>>  > be working fine.
>>  >
>>  > Perhaps if you were to provide example of your code, the query you are
>>  > doing, perhaps someone might be able to point out possible
>>  > logic/syntax errors, but other than that, you will likely get more
>>  > mileage from either CF list or from the provider of 

Re: [sqlite] statement is not executing

2008-09-01 Thread Javier Julio
Greg,

I'm sorry to hear about the troubles you are running into. I've been using
SQLite since I've been doing AIR development although my real bread and
butter is CF development. I've never seen anyone connect CF8 and SQLite and
am wondering why you have chosen that? The reason I ask is CF8 comes with
Derby embedded and I would think that would be the best answer for you.
Sorry I can't help any further.

Ciao!
Javi

On Mon, Sep 1, 2008 at 11:46 AM, Greg Morphis <[EMAIL PROTECTED]> wrote:

> It's just a simple insert into table (columns) values (values)
>
>  returntype="boolean">
>
>   
>
>   
>
> 
>INSERT INTO
>   BILLS
>   (
>  PAYEEID,
>  PAYEE,
>  MINIMUMDUE,
>  DUEDAY,
>  ISCC,
>  ISACTIVE,
>  AMOUNTOWED,
>  APR
>   )
>VALUES
>   (
>  
> value="#arguments.bill.getPayeeID()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getPayee()#"
> cfsqltype="cf_sql_longvarchar" />,
>  
> value="#arguments.bill.getMinimumDue()#"
> cfsqltype="cf_sql_numeric" />,
>  
> value="#arguments.bill.getDueDay()#"
> cfsqltype="cf_sql_longvarchar" />,
>   value="#arguments.bill.getIsCC()#"
> cfsqltype="cf_sql_numeric" />,
>  
> value="#arguments.bill.getIsActive()#"
> cfsqltype="cf_sql_numeric" />,
>  
> value="#arguments.bill.getAmountOwed()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getAPR()#"
> cfsqltype="cf_sql_numeric" />
>   )
> 
>
>   
>
> 
>
> and the DB looks like..
>
> CREATE TABLE bills
> (
>  payeeid integer primary key,
>  payee TEXT,
>  minimumdue numeric,
>  apr numeric,
>  amountowed real,
>  iscc numeric DEFAULT 1,
>  dueday TEXT,
>  isactive numeric DEFAULT 1
> )
>
>
>
>
>
>
>
> On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
> >> I understand this is a sqlite list, I had hoped that I wasnt the only
> >>  one using it or trying to use it with ColdFusion..
> >>  I've also asked this on a CF list, just hoping somewhere out there is
> >>  the answer..
> >
> > No, no... don't get me wrong. There is no problem with describing the
> > tools you are using (CF and JDBC driver, in this case). However, you
> > have given no other context... no db schema, no example code, the
> > query you are executing, etc. There is little here for anyone to try
> > and help, other than if there happens to be another CF/SQLite/JDBC
> > user.
> >
> > Just offer more details, and you have a slightly better chance of
> > getting a meaningful reply.
> >
> >>
> >>  Anyways, as I just posted the problem seems to be around the
> >>   >>
> >>  I suppose since this is a local app, I don't need the  >>  tags, just looking for answers...
> >>
> >>  Thanks
> >>
> >>
> >>
> >>  On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> >>  > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
> >>  >> To further complicate things..  I removed all of the s
> >>  >>  and the code works.. I readd them and I get the same
> >>  >>
> >>  >>
> >>  >>  Error Executing Database Query.
> >>  >>  statement is not executing
> >>  >>
> >>  >>
> >>  >> The error occurred in
> >>  >>
>  C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
> >>  >>  line 92
> >>  >>
> >>  >>  The Stack Trace looks like :
> >>  >>
> >>  >>  java.sql.SQLException: statement is not executing
> >>  >> at org.sqlite.Stmt.checkOpen(Stmt.java:41)
> >>  >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
> >>  >> at
> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
> >>  >> at coldfusion.sql.Executive.getRowSet(Executive.java:513)
> >>  >> at
> coldfusion.sql.Executive.executeQuery(Executive.java:1205)
> >>  >> at
> coldfusion.sql.Executive.executeQuery(Executive.java:1008)
> >>  >> at 

Re: [sqlite] statement is not executing

2008-09-01 Thread Greg Morphis
I rebuilt the table as

CREATE TABLE bills
(
  payeeid integer primary key,
  payee TEXT,
  minimumdue float,
  apr float,
  amountowed float,
  iscc integer DEFAULT 1,
  dueday TEXT,
  isactive integer DEFAULT 1
)



On Mon, Sep 1, 2008 at 10:46 AM, Greg Morphis <[EMAIL PROTECTED]> wrote:
> It's just a simple insert into table (columns) values (values)
>
> 
>
>   
>
>   
>
> 
>INSERT INTO
>   BILLS
>   (
>  PAYEEID,
>  PAYEE,
>  MINIMUMDUE,
>  DUEDAY,
>  ISCC,
>  ISACTIVE,
>  AMOUNTOWED,
>  APR
>   )
>VALUES
>   (
>   value="#arguments.bill.getPayeeID()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getPayee()#"
> cfsqltype="cf_sql_longvarchar" />,
>   
> value="#arguments.bill.getMinimumDue()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getDueDay()#"
> cfsqltype="cf_sql_longvarchar" />,
>   value="#arguments.bill.getIsCC()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getIsActive()#"
> cfsqltype="cf_sql_numeric" />,
>   
> value="#arguments.bill.getAmountOwed()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getAPR()#"
> cfsqltype="cf_sql_numeric" />
>   )
> 
>
>   
>
> 
>
> and the DB looks like..
>
> CREATE TABLE bills
> (
>  payeeid integer primary key,
>  payee TEXT,
>  minimumdue numeric,
>  apr numeric,
>  amountowed real,
>  iscc numeric DEFAULT 1,
>  dueday TEXT,
>  isactive numeric DEFAULT 1
> )
>
>
>
>
>
>
>
> On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote:
>> On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>>> I understand this is a sqlite list, I had hoped that I wasnt the only
>>>  one using it or trying to use it with ColdFusion..
>>>  I've also asked this on a CF list, just hoping somewhere out there is
>>>  the answer..
>>
>> No, no... don't get me wrong. There is no problem with describing the
>> tools you are using (CF and JDBC driver, in this case). However, you
>> have given no other context... no db schema, no example code, the
>> query you are executing, etc. There is little here for anyone to try
>> and help, other than if there happens to be another CF/SQLite/JDBC
>> user.
>>
>> Just offer more details, and you have a slightly better chance of
>> getting a meaningful reply.
>>
>>>
>>>  Anyways, as I just posted the problem seems to be around the
>>>  >>
>>>  I suppose since this is a local app, I don't need the >>  tags, just looking for answers...
>>>
>>>  Thanks
>>>
>>>
>>>
>>>  On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote:
>>>  > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>>>  >> To further complicate things..  I removed all of the s
>>>  >>  and the code works.. I readd them and I get the same
>>>  >>
>>>  >>
>>>  >>  Error Executing Database Query.
>>>  >>  statement is not executing
>>>  >>
>>>  >>
>>>  >> The error occurred in
>>>  >>  
>>> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
>>>  >>  line 92
>>>  >>
>>>  >>  The Stack Trace looks like :
>>>  >>
>>>  >>  java.sql.SQLException: statement is not executing
>>>  >> at org.sqlite.Stmt.checkOpen(Stmt.java:41)
>>>  >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
>>>  >> at 
>>> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
>>>  >> at coldfusion.sql.Executive.getRowSet(Executive.java:513)
>>>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:1205)
>>>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
>>>  >> at coldfusion.sql.Executive.executeQuery(Executive.java:939)
>>>  >>  ...
>>>  >>
>>>  >>
>>>  >>
>>>  >>
>>>  >>

Re: [sqlite] statement is not executing

2008-09-01 Thread Greg Morphis
Honestly, for S mainly.. I have been using Postgres and I don't need
that kind of power or resources tied up for a simple app.
I suppose I could use MySQL but it's a simple DB.. I like using SQLite
and would love to get this to work..

I'll look into Apache Derby.. I know nothing of it however

On Mon, Sep 1, 2008 at 10:52 AM, Javier Julio <[EMAIL PROTECTED]> wrote:
> Greg,
>
> I'm sorry to hear about the troubles you are running into. I've been using
> SQLite since I've been doing AIR development although my real bread and
> butter is CF development. I've never seen anyone connect CF8 and SQLite and
> am wondering why you have chosen that? The reason I ask is CF8 comes with
> Derby embedded and I would think that would be the best answer for you.
> Sorry I can't help any further.
>
> Ciao!
> Javi
>
> On Mon, Sep 1, 2008 at 11:46 AM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>
>> It's just a simple insert into table (columns) values (values)
>>
>> > returntype="boolean">
>>
>>   
>>
>>   
>>
>> 
>>INSERT INTO
>>   BILLS
>>   (
>>  PAYEEID,
>>  PAYEE,
>>  MINIMUMDUE,
>>  DUEDAY,
>>  ISCC,
>>  ISACTIVE,
>>  AMOUNTOWED,
>>  APR
>>   )
>>VALUES
>>   (
>>  >
>> value="#arguments.bill.getPayeeID()#"
>> cfsqltype="cf_sql_numeric" />,
>>  > value="#arguments.bill.getPayee()#"
>> cfsqltype="cf_sql_longvarchar" />,
>>  >
>> value="#arguments.bill.getMinimumDue()#"
>> cfsqltype="cf_sql_numeric" />,
>>  >
>> value="#arguments.bill.getDueDay()#"
>> cfsqltype="cf_sql_longvarchar" />,
>>  > value="#arguments.bill.getIsCC()#"
>> cfsqltype="cf_sql_numeric" />,
>>  >
>> value="#arguments.bill.getIsActive()#"
>> cfsqltype="cf_sql_numeric" />,
>>  >
>> value="#arguments.bill.getAmountOwed()#"
>> cfsqltype="cf_sql_numeric" />,
>>  > value="#arguments.bill.getAPR()#"
>> cfsqltype="cf_sql_numeric" />
>>   )
>> 
>>
>>   
>>
>> 
>>
>> and the DB looks like..
>>
>> CREATE TABLE bills
>> (
>>  payeeid integer primary key,
>>  payee TEXT,
>>  minimumdue numeric,
>>  apr numeric,
>>  amountowed real,
>>  iscc numeric DEFAULT 1,
>>  dueday TEXT,
>>  isactive numeric DEFAULT 1
>> )
>>
>>
>>
>>
>>
>>
>>
>> On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote:
>> > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>> >> I understand this is a sqlite list, I had hoped that I wasnt the only
>> >>  one using it or trying to use it with ColdFusion..
>> >>  I've also asked this on a CF list, just hoping somewhere out there is
>> >>  the answer..
>> >
>> > No, no... don't get me wrong. There is no problem with describing the
>> > tools you are using (CF and JDBC driver, in this case). However, you
>> > have given no other context... no db schema, no example code, the
>> > query you are executing, etc. There is little here for anyone to try
>> > and help, other than if there happens to be another CF/SQLite/JDBC
>> > user.
>> >
>> > Just offer more details, and you have a slightly better chance of
>> > getting a meaningful reply.
>> >
>> >>
>> >>  Anyways, as I just posted the problem seems to be around the
>> >>  > >>
>> >>  I suppose since this is a local app, I don't need the > >>  tags, just looking for answers...
>> >>
>> >>  Thanks
>> >>
>> >>
>> >>
>> >>  On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote:
>> >>  > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
>> >>  >> To further complicate things..  I removed all of the s
>> >>  >>  and the code works.. I readd them and I get the same
>> >>  >>
>> >>  >>
>> >>  >>  Error Executing Database Query.
>> >>  >>  statement is not executing
>> >>  >>
>> >>  >>
>> >>  >> The error occurred in
>> >>  >>
>>  
>> C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
>> >>  >>  line 92
>> >>  >>
>> >>  >>  The Stack Trace looks like :
>> >>  >>
>> >>  >>  java.sql.SQLException: statement is not executing

Re: [sqlite] statement is not executing

2008-09-01 Thread Javier Julio
I don't want to upset anyone here either since SQLite is a great database
but since you work with CF8 and so do I, I think its safe to say SQLite is
not the answer for you here. Derby offers a lot but why I suggested it is
because it comes out of the box with CF8. You will have more pain setting up
SQLite connectivity then with Derby. Plus you get to use all those
cfqueryparams!

Again don't want to upset anyone here but since I work a lot with CF I think
this is the best call for you. Heck if you paid for a CF8 license you might
as well use everything it has out of the box! I'm thinking it will do you
much better since if it has CF support you won't have to worry about the
drivers. Hope this helps out.

Ciao!
Javi

On Mon, Sep 1, 2008 at 12:06 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:

> Honestly, for S mainly.. I have been using Postgres and I don't need
> that kind of power or resources tied up for a simple app.
> I suppose I could use MySQL but it's a simple DB.. I like using SQLite
> and would love to get this to work..
>
> I'll look into Apache Derby.. I know nothing of it however
>
> On Mon, Sep 1, 2008 at 10:52 AM, Javier Julio <[EMAIL PROTECTED]> wrote:
> > Greg,
> >
> > I'm sorry to hear about the troubles you are running into. I've been
> using
> > SQLite since I've been doing AIR development although my real bread and
> > butter is CF development. I've never seen anyone connect CF8 and SQLite
> and
> > am wondering why you have chosen that? The reason I ask is CF8 comes with
> > Derby embedded and I would think that would be the best answer for you.
> > Sorry I can't help any further.
> >
> > Ciao!
> > Javi
> >
> > On Mon, Sep 1, 2008 at 11:46 AM, Greg Morphis <[EMAIL PROTECTED]>
> wrote:
> >
> >> It's just a simple insert into table (columns) values (values)
> >>
> >>  >> returntype="boolean">
> >>
> >>/>
> >>
> >>   
> >>
> >>  datasource="#variables.DSN#">
> >>INSERT INTO
> >>   BILLS
> >>   (
> >>  PAYEEID,
> >>  PAYEE,
> >>  MINIMUMDUE,
> >>  DUEDAY,
> >>  ISCC,
> >>  ISACTIVE,
> >>  AMOUNTOWED,
> >>  APR
> >>   )
> >>VALUES
> >>   (
> >>   >>
> >> value="#arguments.bill.getPayeeID()#"
> >> cfsqltype="cf_sql_numeric" />,
> >>   >>
> value="#arguments.bill.getPayee()#"
> >> cfsqltype="cf_sql_longvarchar"
> />,
> >>   >>
> >> value="#arguments.bill.getMinimumDue()#"
> >> cfsqltype="cf_sql_numeric" />,
> >>   >>
> >> value="#arguments.bill.getDueDay()#"
> >> cfsqltype="cf_sql_longvarchar"
> />,
> >>   >>
> value="#arguments.bill.getIsCC()#"
> >> cfsqltype="cf_sql_numeric" />,
> >>   >>
> >> value="#arguments.bill.getIsActive()#"
> >> cfsqltype="cf_sql_numeric" />,
> >>   >>
> >> value="#arguments.bill.getAmountOwed()#"
> >> cfsqltype="cf_sql_numeric" />,
> >>   >>
> value="#arguments.bill.getAPR()#"
> >> cfsqltype="cf_sql_numeric" />
> >>   )
> >> 
> >>
> >>   
> >>
> >> 
> >>
> >> and the DB looks like..
> >>
> >> CREATE TABLE bills
> >> (
> >>  payeeid integer primary key,
> >>  payee TEXT,
> >>  minimumdue numeric,
> >>  apr numeric,
> >>  amountowed real,
> >>  iscc numeric DEFAULT 1,
> >>  dueday TEXT,
> >>  isactive numeric DEFAULT 1
> >> )
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> >> > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
> >> >> I understand this is a sqlite list, I had hoped that I wasnt the only
> >> >>  one using it or trying to use it with ColdFusion..
> >> >>  I've also asked this on a CF list, just hoping somewhere out there
> is
> >> >>  the answer..
> >> >
> >> > No, no... don't get me wrong. There is no problem with describing the
> >> > tools you are using (CF and JDBC driver, in this case). However, you
> >> > have given no other context... no db schema, no example code, the
> >> > query you are executing, etc. There is little here for anyone to try
> >> > and help, other than if there happens to be another CF/SQLite/JDBC
> >> > user.
> >> >
> >> > Just offer more 

[sqlite] Difference in performance between 32 and 64 bit versions of SQLite?

2008-09-01 Thread python
Does anyone have any benchmarks to share that compare common SQLite
operations running under a 32 and 64 bit versions of SQLite? This
question is OS neutral so please feel to share your experience with 32
and 64 bit versions of Windows or Linux.

Background: Will 64 bit versions of SQLite buy us any significant
difference in performance? I may have a chance to get our department 64
bit AMD workstations with 8G, but I need to justify the extra cost
against a reasonable guess at what the performance improvement may be
against an equivalent 32 bit AMD 4G workstation. These workstations will
be processing very large text based log files (> 16G each). We will be
using Python 2.52 as our SQLite scripting tool.

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


Re: [sqlite] Difference in performance between 32 and 64 bit versions of SQLite?

2008-09-01 Thread Sherief N. Farouk
I can't speak for SQLite, but off of the top of my head, a 64-bit platform
will give you enough virtual address space to memory map the files, which
usually proves to be a win. I'd say go with the 64-bit systems and as much
ram as you can afford.

- Sherief

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:sqlite-users-
> [EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
> Sent: Monday, September 01, 2008 3:10 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Difference in performance between 32 and 64 bit
> versions of SQLite?
> 
> Does anyone have any benchmarks to share that compare common SQLite
> operations running under a 32 and 64 bit versions of SQLite? This
> question is OS neutral so please feel to share your experience with 32
> and 64 bit versions of Windows or Linux.
> 
> Background: Will 64 bit versions of SQLite buy us any significant
> difference in performance? I may have a chance to get our department 64
> bit AMD workstations with 8G, but I need to justify the extra cost
> against a reasonable guess at what the performance improvement may be
> against an equivalent 32 bit AMD 4G workstation. These workstations
> will
> be processing very large text based log files (> 16G each). We will be
> using Python 2.52 as our SQLite scripting tool.
> 
> Thank you,
> Malcolm
> ___
> 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] Difference in performance between 32 and 64 bit versions of SQLite?

2008-09-01 Thread Jay A. Kreibich
On Mon, Sep 01, 2008 at 03:10:05PM -0400, [EMAIL PROTECTED] scratched on the 
wall:
> Does anyone have any benchmarks to share that compare common SQLite
> operations running under a 32 and 64 bit versions of SQLite? This
> question is OS neutral so please feel to share your experience with 32
> and 64 bit versions of Windows or Linux.
> 
> Background: Will 64 bit versions of SQLite buy us any significant
> difference in performance? I may have a chance to get our department 64
> bit AMD workstations with 8G, but I need to justify the extra cost
> against a reasonable guess at what the performance improvement may be
> against an equivalent 32 bit AMD 4G workstation. These workstations will
> be processing very large text based log files (> 16G each). We will be
> using Python 2.52 as our SQLite scripting tool.


  If money is limited, the first thing I'd be looking at is I/O
  systems and fast storage.  It doesn't matter how fast your CPU and
  application are if they're sitting around waiting for the drives to
  process data.  I'd be looking at the fastest drives you can find or,
  if funds are sufficient, a RAID systems with lots of drives (and lots
  of heads) to get as much parallel I/O as you can.  This is a case
  when eight smaller drives is better than four larger ones, assuming
  all else is the same.



  If you're processing text based log files, I assume you're just
  scanning those top to bottom.  As a result, cache size is fairly 
  unimportant, as you're going to read everything once and then dump it.
  Caches systems only provide an advantage if you come back to the data
  later.  As the file size grows, that all comes down to raw-I/O speeds.
  The memory situation is less demanding as memory-mapped I/O,
  double-/triple-buffered asyc I/O calls, or one of the other mass-I/O
  techniques is very likely going to serve quite well with only 256MB or
  less, even for the faster RAID systems.

  Where memory and cache size does become very important is the
  database size.  If you process a 20GB log file into a 1GB database,
  the extra address space afforded by the 64 bit system isn't going to
  buy you anything, since you can comfortably cache the whole database
  within the 32 bit address space.  On the other hand, if you're talking
  about databases in the 3 to 6GB range and, to a lesser degree,
  beyond, the 64 bit system (and the extra RAM) may serve you quite well.

  BUT... (and there is always a "but") don't forget that writing the
  database doesn't get a massive boost from caching anyways, unless
  you play things fast and loose and turn off most of SQLite's safety
  nets.  Assuming you're running in the normal mode where disk writes
  are sync'ed as much as possible, I/O speeds still play a very strong
  part of your total processing time any time INSERTs or UPDATEs are
  involved.

  If the processing just INSERTs records to the database, you get back
  into a situation much like reading the log files... pages tend to go
  one way (out to disk) and caching is less important.  As long as you
  have enough cache to hold your indexes, you're fairly good to go.

  On the other hand, if you do stuff like record event counts,
  incrementing fields with each log file record that is processed, the
  total database cache size becomes a lot more important.  That gets
  back to the question of database size, and if you really need the 64
  bit system or not, and how much it really matters once you get much
  beyond ~6GB.

  There is also the question of what you do with these database files.
  If you do a lot of processing once the database files are formed,
  then total memory becomes important, as we can assume you're pulling
  a lot of pages in and out and doing all kinds of interesting things.
  On the other hand, if all you do is scan the data and generate a
  report or two and that's it, you're back to the issue of limited
  cache use and I/O becoming king.



  The "best" decision really depends on what you're trying to do with
  all this data.  The two important points I'd make, however, are that
  cache doesn't matter unless you re-use the data, and that disk I/O
  tends to be thousands (if not millions) of times slower than CPU
  actions-- and that SQLite is extremely disk I/O intensive.

   -j

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Creating recurrent appointments?

2008-09-01 Thread Gilles Ganault
On Mon, 01 Sep 2008 15:44:05 +0300, Mihai Limbasan
<[EMAIL PROTECTED]> wrote:
>You will need to loop through this.

Thanks.

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


Re: [sqlite] Difference in performance between 32 and 64 bit versionsof SQLite?

2008-09-01 Thread John Elrick
[EMAIL PROTECTED] wrote:
> Does anyone have any benchmarks to share that compare common SQLite
> operations running under a 32 and 64 bit versions of SQLite? This
> question is OS neutral so please feel to share your experience with 32
> and 64 bit versions of Windows or Linux.
>
> Background: Will 64 bit versions of SQLite buy us any significant
> difference in performance? I may have a chance to get our department 64
> bit AMD workstations with 8G, but I need to justify the extra cost
> against a reasonable guess at what the performance improvement may be
> against an equivalent 32 bit AMD 4G workstation. These workstations will
> be processing very large text based log files (> 16G each). We will be
> using Python 2.52 as our SQLite scripting tool.

Given the potential expense, wouldn't it be justifiable to invest in a 
single test machine to benchmark the operations?


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


[sqlite] Bug in conversion from unixepoch

2008-09-01 Thread Brandon Fosdick
Using sqlite 3.4.0 (on both OS X 10.5.4 and iPhone 2.0.2) I'm seeing a 
problem converting unix timestamps from today and yesterday.

In the sqlite console I get...

sqlite> select date('1220302462','unixepoch');
2008-09-01
sqlite> select date('1220249914','unixepoch');
2008-09-01

...however, the equivalent in MySQL produces...

mysql> select from_unixtime(1220302462);
+---+
| from_unixtime(1220302462) |
+---+
| 2008-09-01 13:54:22   |
+---+
1 row in set (0.00 sec)

mysql> select from_unixtime(1220249914);
+---+
| from_unixtime(1220249914) |
+---+
| 2008-08-31 23:18:34   |
+---+
1 row in set (0.00 sec)


The MySQL version is correct in the sense that it returns dates/times 
that correspond to what my clock said when I created the records that 
contain the timestamps in question. This was all done in the Pacific 
timezone and the records were created on my iPhone.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Bug in conversion from unixepoch

2008-09-01 Thread Myk Melez
Brandon Fosdick wrote:
> sqlite>  select date('1220302462','unixepoch');
> 2008-09-01
> sqlite>  select date('1220249914','unixepoch');
> 2008-09-01
>
Unix times contain no time zone information (they are relative to an 
epoch in the UTC time zone), and SQLite's date/time functions return 
values in the UTC time zone by default:

sqlite> select datetime('1220302462','unixepoch');
2008-09-01 20:54:22
sqlite> select datetime('1220249914','unixepoch');
2008-09-01 06:18:34

But you can make them return values in the local time zone with the 
|localtime| modifier:

sqlite> select datetime('1220302462','unixepoch', 'localtime');
2008-09-01 13:54:22
sqlite> select datetime('1220249914','unixepoch', 'localtime');
2008-08-31 23:18:34

> The MySQL version is correct in the sense that it returns dates/times
> that correspond to what my clock said when I created the records that
> contain the timestamps in question.
That's just a coincidence. MySQL's from_unixtime 

 
returns values in the "current time zone," so if you'd changed zones 
since inserting the records, it would show you different values.

-myk

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


Re: [sqlite] Bug in conversion from unixepoch

2008-09-01 Thread Brandon Fosdick
Myk Melez wrote:
> Unix times contain no time zone information (they are relative to an 
> epoch in the UTC time zone), and SQLite's date/time functions return 
> values in the UTC time zone by default:

argh, I should have thought of that. Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite version 3.6.2

2008-09-01 Thread Russell Leighton

Are there expected performance differences (better or worse) as a 
result of the code factoring?

On Aug 30, 2008, at 8:01 PM, D. Richard Hipp wrote:

> SQLite version 3.6.2 is now available on the SQLite website:  
> http://www.sqlite.org/download.html
>
> SQLite version 3.6.2 contains rewrites of the page-cache subsystem and
> the procedures for matching identifiers to table columns in SQL
> statements. These changes are designed to better modularize the code
> and make it more maintainable and reliable moving forward. Nearly 5000
> non-comment lines of core code (about 11.3%) have changed from the
> previous release. Nevertheless, there should be no application-visible
> changes, other than bug fixes.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Default Column Value to Local Time

2008-09-01 Thread jonwood

Okay, I give up. I've been searching for a while now. I've found a number of
discussions about how CURRENT_DATE returns the date in UTC, and that it can
be converted to local time. But I've yet to find one article that clearly
states how one might do this.

I would love to have a table column default to the current date, but I want
it to be the date where the computer is located, rather than some far off
place.

Can anyone tell me if this is possible?

Thanks.
-- 
View this message in context: 
http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263208.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] Default Column Value to Local Time

2008-09-01 Thread Derrell Lipman
On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:

>
> Okay, I give up. I've been searching for a while now. I've found a number
> of
> discussions about how CURRENT_DATE returns the date in UTC, and that it can
> be converted to local time. But I've yet to find one article that clearly
> states how one might do this.
>
> I would love to have a table column default to the current date, but I want
> it to be the date where the computer is located, rather than some far off
> place.
>
> Can anyone tell me if this is possible?


I think this is the page you're looking for:
  http://www.sqlite.org/lang_datefunc.html

In particular, a query that returns the current time in the local (to
sqlite) time zone is:
   SELECT datetime('now', 'localtime');
or for just the date:
   SELECT date('now', 'localtime');

Note, however, that if sqlite is being accessed by your web server,
'localtime' is in reference to the web server's time zone, not the time zone
of the web browser which could be anyplace in the world.  If your
application is running all locally, the above should work.

Cheers,

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


Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread P Kishor
On 9/1/08, jonwood <[EMAIL PROTECTED]> wrote:
>
>  Okay, I give up. I've been searching for a while now. I've found a number of
>  discussions about how CURRENT_DATE returns the date in UTC, and that it can
>  be converted to local time. But I've yet to find one article that clearly
>  states how one might do this.
>
>  I would love to have a table column default to the current date, but I want
>  it to be the date where the computer is located, rather than some far off
>  place.
>
>  Can anyone tell me if this is possible?

Don't think it is possible. As you noted, the docs say very clearly
(http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT
constraint specifies a default value to use when doing an INSERT. ..
default value may also be one of the special case-independant keywords
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC
date and/or time is inserted into the columns. "

So, you can store only UTC date/time, but you can always convert on
retrieving the values by using datetime('now', 'localtime')

One thing to note -- the UTC date/time doesn't seem to account for
daylight savings adjustment. For example, I am right now in Washington
DC where it is 10:30 PM. It is 3:30 AM in the UK right now, but per
SQLite (the UTC bit), it is 2:30 AM. I guess I have to do all this
adjustment in my queries and application.


>
>  Thanks.
>
> --
>  View this message in context: 
> http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263208.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
>


-- 
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] Default Column Value to Local Time

2008-09-01 Thread jonwood


Derrell Lipman wrote:
> 
> I think this is the page you're looking for:
>   http://www.sqlite.org/lang_datefunc.html
> 
> In particular, a query that returns the current time in the local (to
> sqlite) time zone is:
>SELECT datetime('now', 'localtime');
> or for just the date:
>SELECT date('now', 'localtime');
> 

Well, okay. That's one of the articles I did see. Right now, I'm having two
issues:

1. I'm not sure how to use those functions. You've gave me some examples,
which I appreciate. But your examples don't work with columns. Perhaps the
first argument needs to be my column name?

2. I'm currently using a default value of CURRENT_DATE in several tables. At
this stage, it would be a lot less work if I could continue to not explictly
specify the initial value for these columns. Can anyone confirm that this is
simply not possible to do this with the local time? (I want the value
actually stored in the database to be local time, and not to simply convert
the UTC value every time it is used.)

Thanks!

-- 
View this message in context: 
http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263339.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] Default Column Value to Local Time

2008-09-01 Thread jonwood


P Kishor-3 wrote:
> 
> Don't think it is possible. As you noted, the docs say very clearly
> (http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT
> constraint specifies a default value to use when doing an INSERT. ..
> default value may also be one of the special case-independant keywords
> CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is
> CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC
> date and/or time is inserted into the columns. "
> 

Thanks for your input. This is really a shame, and quite surprising, that it
works this way with no means to modify the behavior. There are all sorts of
flags to modify how the source code works, but I could find nothing related
to this. Based on my previous searches, I am not the only one who thinks it
should work this way. It should at least be an option.

Thanks.
-- 
View this message in context: 
http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263371.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] Difference in performance between 32 and 64 bit versions of SQLite?

2008-09-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:
> Background: Will 64 bit versions of SQLite buy us any significant
> difference in performance?

You need to have some benchmark code first, ideally in the form of a
representative script to feed to the sqlite shell.  That way we can run
the code for you and give feedback.

In the specific case of SQLite the biggest determinant of performance
are the hard drives and the speed they spin at.  For transactions, data
has to be on the oxide and confirmed as such by the drive.  For 7200rpm
drives, each spot is under the heads 120 times per second.  Each
transaction requires two syncs so the maximum possible transaction rate
is 60 per second.  This will apply even if the data is spread across
raid stripes since every drive in the array still has to confirm the
data is committed. For random access the latency is also affected by the
drive speed, limiting you to a maximum of 120 random accesses per
second.  Sequential access will obviously be significantly faster but
again the maximum rate will be a factor of packing density and rpm.  You
can increase the amount of data that can be sent to/read from the drives
by using a RAID array, or by using drives that support NCQ.  For example
my bog standard Seagate 750GB drive will accept 100MB/s for writes and
does 70MB/s for mostly sequential reads.  A two drive array would
roughly double that.

> I may have a chance to get our department 64 bit AMD workstations

It is a very good idea to get 64 bit machines and all current
workstation processors are 64 bit anyway.  64 bit Windows and Linux will
also run and compile 32 bit binaries so you can test compatibility.

> with 8G, 

It is pointless having more than about 3G with 32 bit operating systems
as they need the last 1G of physical address space for peripherals,
video memory etc.

> These workstations will
> be processing very large text based log files (> 16G each).

Generally using 64 bit operating systems makes life considerably easier
for developers.  As an example you can just memory map the files and
then treat them as a char*.  You can assemble multiple files in memory,
do sorting etc without having to worry about hitting limits.

Note that SQLite itself has limits which are 32 bit based even in a 64
bit process.  For example the largest a single record could be is 2GB
(signed 32 bit maxint).

In terms of general 32 bit vs 64 bit performance, the AMD64 instruction
set doubles the number of registers from 8 to 16.  But pointers take up
twice the space (8 rather than 4 bytes).  So basically the code can have
more balls in the air, but requires more memory accessed to operate.  If
you have access to 64 bit Linux you can compile 64 and 32 bit versions
of the SQLite shell for easy comparison.

  $ ./configure --disable-shared CC="gcc -m32" && make && mv sqlite3 \
   sqlite3-32

Repeat replacing 32 with 64.  The resulting binary is 371kb (32 bit) and
428kb (64 bit).

> We will be
> using Python 2.52 as our SQLite scripting tool.

Good tool choice.  That is far more productive for developers than C
development :-)  You may also want to try the APSW bindings as well as
pysqlite.  APSW is reported to be faster by others doing benchmarking.
(Disclaimer: I am the author of APSW).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIvK5pmOOfHg372QQRAs0zAJ9IIxOEFE+p9eLXNlyPOOoQFUu5pwCgoJiJ
bgnWRsZ58n3bGkgeGFwuHS8=
=QEH7
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread P Kishor
On 9/1/08, jonwood <[EMAIL PROTECTED]> wrote:
>
>
>  P Kishor-3 wrote:
>  >
>  > Don't think it is possible. As you noted, the docs say very clearly
>  > (http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT
>  > constraint specifies a default value to use when doing an INSERT. ..
>  > default value may also be one of the special case-independant keywords
>  > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is
>  > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC
>  > date and/or time is inserted into the columns. "
>  >
>
>
> Thanks for your input. This is really a shame, and quite surprising, that it
>  works this way with no means to modify the behavior. There are all sorts of
>  flags to modify how the source code works, but I could find nothing related
>  to this. Based on my previous searches, I am not the only one who thinks it
>  should work this way. It should at least be an option.

well, maybe. How data are stored is/should_be irrelevant to the end
user. In fact, the best way to store dates might well be just numbers
(search the archives for discussions on how to best store
dates/times). The key is in retrieving and displaying (or doing
whatever one wants to do with them) the data.

You have all the options in the world to use the datetime functions
for that, or, even more so in your application. All you have to do is
to remember how your data were inserted in the first place.


>
>  Thanks.
>
> --
>  View this message in context: 
> http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263371.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread Derrell Lipman
On Mon, Sep 1, 2008 at 10:29 PM, Derrell Lipman <
[EMAIL PROTECTED]> wrote:

>
>
> On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:
>
>>
>> Okay, I give up. I've been searching for a while now. I've found a number
>> of
>> discussions about how CURRENT_DATE returns the date in UTC, and that it
>> can
>> be converted to local time. But I've yet to find one article that clearly
>> states how one might do this.
>>
>> I would love to have a table column default to the current date, but I
>> want
>> it to be the date where the computer is located, rather than some far off
>> place.
>>
>> Can anyone tell me if this is possible?
>
>
> I think this is the page you're looking for:
>   http://www.sqlite.org/lang_datefunc.html
>
> In particular, a query that returns the current time in the local (to
> sqlite) time zone is:
>SELECT datetime('now', 'localtime');
> or for just the date:
>SELECT date('now', 'localtime');
>
> Note, however, that if sqlite is being accessed by your web server,
> 'localtime' is in reference to the web server's time zone, not the time zone
> of the web browser which could be anyplace in the world.  If your
> application is running all locally, the above should work.
>

I guess I didn't quite complete the picture here.  I don't think you can use
functions as default values, but you can easily use triggers to accomplish
the same thing, like this:

sqlite> CREATE TABLE x (i INTEGER PRIMARY KEY, t TIMESTAMP);
sqlite> CREATE TRIGGER x_insert_tr
   ...>   AFTER INSERT ON X
   ...>   FOR EACH ROW
   ...>   BEGIN
   ...> UPDATE x
   ...>   SET t  = datetime('now', 'localtime')
   ...>WHERE i = new.i;
   ...>   END;
sqlite> INSERT INTO x (i) VALUES (1);
sqlite> INSERT INTO x (i) VALUES (2);
sqlite> INSERT INTO x (i) VALUES (3);
sqlite> SELECT * FROM x;
1|2008-09-01 23:32:49
2|2008-09-01 23:32:55
3|2008-09-01 23:33:01
sqlite>

Those times shown above are when I ran the INSERT queries, in my local time
zone.

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


Re: [sqlite] Creating recurrent appointments?

2008-09-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gilles Ganault wrote:
>   I need to write an application to manage appointments. Most of them
> are recurrent, with no end-date, while others are one-shots, and the
> user must be able to schedule reccurent appointments but be able to
> tweak some of them if need be.

BTW the way most apps, phones and even standards manage this is by
storing a list of events each of which has start and end dates for
repeating, and a list of exceptions.

A one shot event would have no start or end date.  An event that repeats
forever would have a start date but no end date.

The exceptions override existing repeating events.  For example say you
have an event that repeats every Tuesday at 3pm.  If I want it to be 4pm
for next Tuesday then I'd create an exception event pointing at the
original but with a different time.  You also need exception events that
can cancel an existing one.

In your user interface or equivalent you have to establish when a user
is editing a repeating event whether they are changing just that one
instance or the series.  The former edits/creates exceptions and the
later edits the original.  Most applications just flat out ask you when
editting which one it is.

You should also look into vCalendar/iCalendar standards since that will
make import/export easier and make your life easier if you structure
your data similar to what they require.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIvMA1mOOfHg372QQRAmY4AJ9JC2Muslq/ollOwCBuVK2nBRTJvgCfYKBM
LnYj+ZCEXTIDK0Dzios7yKI=
=5wtg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users