[sqlite] Storing RTF text in a field

[Gaurav Patole]

Hello,

   I have the following table

 

CREATE TABLE Tasks(taskID INTEGER PRIMARY KEY, parentTaskID INTEGER, projID
INTEGER, name varchar(25), assignee varchar(25), start_date timestamp,
end_date timestamp, completed_date timestamp, last_edited timestamp,
created_date timestamp, last_edited_by varchar(25), author varchar(25),
percent_complete INTEGER, priority INTEGER, status INTEGER, description
blob, last_modified timestamp);

 

With description field as BLOB.

 

I wish to store the description that may contain RTF text.

Example: 

 

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\froman\fprq2\fcharset
0 Times New Roman;}}

{\colortbl ;\red0\green0\blue255;}

\viewkind4\uc1\pard\fi-360\li900\tx900\f0\fs24 1.\tab Showing comment
notifications though all the notification options are unchecked when we copy
and paste the task having comments.

\par \pard\fi-360\li900 2.\tab Description of 15 to 20 lines is not
retaining when we reassigned the task.

\par 3.\tab Problem in copy and paste: Copy and pasting the task having
attachments, source file's attachments gets disappeared - \cf1\b
Completed\cf0\b0 .

\par 4.\tab Normal user is not able to mark his task as completed - \cf1\b
Completed\cf0\b0 .

\par 5.\tab Problem in copy paste: Not asking for changing resource when
resource is not available in the source project.

\par 6.\tab User level rights user s/d be able to move his task.

\par \pard 

\par \pard\li360 Date: 19 September' 05

\par Private Build 62

\par 

\par \pard\fi-360\li720\tx720 1.\tab User s/d attaches attachment while
assigning the new task.

\par \pard\fi-360\li720 2.\tab Problem with right click move to option: The
option is not working when user has not changed the resource name, means its
not working with default resource name.

\par 3.\tab Once click on no to all (if resource is not available) next time
unable to copy and paste the task from one project to another, also
simulating with moving tasks by drag and drop.

\par 4.\tab While right click move to don't allow the user to select
multiple resources initially rather than showing message later.

\par 5.\tab Making project status as on-hold or proposed, all its existing
tasks are not becoming on-hold.

\par 6.\tab Project manager s/d be able to view his completed and proposed
projects.

\par 7.\tab All the tasks of the completed projects s/d become completed.

\par 8.\tab Not changing assigned to field while moving the task from one
project to another project, by using right click move to option.

\par 9.\tab Assignee field s/d gets changed while reassigning the task from
right click - reassigned to option for example if amol is reassigning the
task to bhavna which is assigned to amol by sayali , in this case author s/d
be sayali and assignee s/d be amol. Presently assignee and author remains as
sayali.

\par \pard Reassigned to is not changing the assignee field, author and
assignee field remains same though task is reassigned by 

\par }

 

 

 

Now when I try to save it to database, I get an error: unrecognized token:
"\". What should I do?

 

Thanks,

Gaurav.

 

 

[sqlite] Re: query problem

[Alain Bertrand]

D. Richard Hipp a écrit :




I've changed my mind.  I think instead that there is a bug in
SQLite that caused LEFT JOINs to be computed incorrectly if one
of the terms in the ON clause restricts only the left table in
the join.  


Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725
contains
a fix for this problem for version 3.x.  The problem has existed in
SQLite
forever (because it originates from a conceptual misunderstanding by the
code author :-)) so version 2.8.16 is still broken.  Because the problem
is obscure, I am not inclined to fix it in the 2.8.x series...

Thanks for your answer. Do you have an approximativ dateframe to see 
this fix incorporated in a release ?



Alain

Re: [sqlite] trigger question

[Jim McNamara]

Hi-

i tried a different dll and sql tool.  the trigger
worked. 

i tried a trick and it seems to work.

what i did is put the trigger in under the new dll and
new sql tool and then copy the updated db (now with a
trigger) to my project directory that uses the java
wrapper/driver.  

it is enforcing the trigger now :-)

thanks,
jim

--- Jim McNamara <[EMAIL PROTECTED]> wrote:

> Hi-
> 
> This syntax isnt working.
> I cant figure out what i am doing wrong.
> I created a budget calculator with a sqlite java
> wrapper/driver.  I am trying to make it a little
> easier to use.
> 
> thanks for any tips!
> jim
> 
> 
> CREATE TRIGGER fki_statusDetail_statusMaster_id
> BEFORE INSERT ON Status_Detail
> FOR EACH ROW BEGIN 
>   SELECT CASE
>  WHEN ((SELECT ID FROM Status_Master WHERE ID =
> NEW.Detail_ID) IS NULL)
>  THEN RAISE(ABORT, 'insert on detail "table"
> violates foreign key ' )
>   END;
> END;
> 
> 
>   
> __ 
> Yahoo! Mail - PC Magazine Editors' Choice 2005 
> http://mail.yahoo.com
> 




__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

[sqlite] trigger question

[Jim McNamara]

Hi-

This syntax isnt working.
I cant figure out what i am doing wrong.
I created a budget calculator with a sqlite java
wrapper/driver.  I am trying to make it a little
easier to use.

thanks for any tips!
jim


CREATE TRIGGER fki_statusDetail_statusMaster_id
BEFORE INSERT ON Status_Detail
FOR EACH ROW BEGIN 
  SELECT CASE
 WHEN ((SELECT ID FROM Status_Master WHERE ID =
NEW.Detail_ID) IS NULL)
 THEN RAISE(ABORT, 'insert on detail "table"
violates foreign key ' )
  END;
END;



__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

RE: [sqlite] query problem

[Cariotoglou Mike]

You are correct. I just run a test on 3.2.6 release, and it does handle
joins incorrectly. I cant get the files from cvs, so I am not sure
whether the fix also
Handles the reverse situation:

Select *
>From T1 left join t2 on (t1.ref=t2.id) and (t2.kind=1)

Ie if there is a join term that restricts the RIGHT hand table only.

Just a thought. 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 12:07 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] query problem
> 
> I've changed my mind.  I think instead that there is a bug in 
> SQLite that caused LEFT JOINs to be computed incorrectly if 
> one of the terms in the ON clause restricts only the left 
> table in the join.  
> 
> Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725
> contains
> a fix for this problem for version 3.x.  The problem has 
> existed in SQLite forever (because it originates from a 
> conceptual misunderstanding by the code author :-)) so 
> version 2.8.16 is still broken.  Because the problem is 
> obscure, I am not inclined to fix it in the 2.8.x series...
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> 

[sqlite] Re: - Re: [sqlite] query problem

[rbundy]

No. COUNT(*) of an empty result returns 0. COUNT(*) should always return an
integer value, never NULL. It is a row-based, rather than a column-based,
aggregate function.

rayB



|-+>
| |   "D. Richard Hipp"|
| |   <[EMAIL PROTECTED]>  |
| ||
| |   20/09/2005 05:19 |
| |   Please respond to|
| |   sqlite-users |
| ||
|-+>
  
>--|
  | 
 |
  |   To:   sqlite-users@sqlite.org 
 |
  |   cc:   
 |
  |   Subject:  - Re: [sqlite] query problem
 |
  
>--|




Hence, the result set contains no rows.  A COUNT() of a empty result
set gives NULL.
--
D. Richard Hipp <[EMAIL PROTECTED]>








** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING *
*** Confidentiality and Privilege Notice ***

This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

Re: [sqlite] query problem

[D. Richard Hipp]

On Mon, 2005-09-19 at 15:19 -0400, D. Richard Hipp wrote:
> On Mon, 2005-09-19 at 19:36 +0200, Alain Bertrand wrote:
> > hi all,
> > 
> > I am porting a program from mysql to sqlite.
> > The following statement doesn't work correctly with sqlite though it does
> > with mysql.
> > SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON
> > ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots
> > ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId
> > 
> 
> I believe the query above should always return 0...

I've changed my mind.  I think instead that there is a bug in
SQLite that caused LEFT JOINs to be computed incorrectly if one
of the terms in the ON clause restricts only the left table in
the join.  

Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725
contains
a fix for this problem for version 3.x.  The problem has existed in
SQLite
forever (because it originates from a conceptual misunderstanding by the
code author :-)) so version 2.8.16 is still broken.  Because the problem
is obscure, I am not inclined to fix it in the 2.8.x series...

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

Re: [sqlite] query problem

[D. Richard Hipp]

On Mon, 2005-09-19 at 16:06 -0400, Kervin L. Pierre wrote:
> D. Richard Hipp wrote:
> > Hence, the result set contains no rows.  A COUNT() of a empty result
> > set gives NULL.
> 
> I thought per the last discussion on "Sum and NULL"
> that the count of an empty set would return zero.
> 

You're right.  I was thinking of sum.  The count should
be zero.

BTW, I'm beginning to doubt my previous analysis of the
problem and am thinking perhaps that SQLite does LEFT JOINs
wrong if on the ON constraints refers only to the left
table of the LEFT JOIN.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] query problem

[Kervin L. Pierre]

D. Richard Hipp wrote:

Hence, the result set contains no rows.  A COUNT() of a empty result
set gives NULL.


I thought per the last discussion on "Sum and NULL"
that the count of an empty set would return zero.

Regards,
Kervin

RE: [sqlite] Multiple Threads and Transactions

[Iulian Popescu]

Thank you very much for the answer and I apologize for my misconception. My
confusion came from the fact the database handle was used by two different
threads and one of them was executing operations inside a transaction.
Is that possible to obtain more than one handle to the same in memory
instance of the database?

Thank you,

Iulian.

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 19, 2005 1:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multiple Threads and Transactions

On Mon, 2005-09-19 at 13:27 -0400, Iulian Popescu wrote:
> Hello,
> 
>  
> 
> I'm kind of new to the SQLite. I'm running an instance of the database in
> memory. I'm trying to figure out if the transaction isolation is also
valid
> between threads. To give a concrete example, two threads as part of the
same
> process share the same database connection. Suppose a transaction is
started
> in one thread and inserts some rows in a table A. If the same thread does
a
> SELECT * FROM A it should see the inserted records if my understanding is
> correct. What will happen if another thread does a SELECT from the same
> table A before the first thread commits the transaction? Will it see the
> records inserted by the first thread? Based on the tests I have made it
> seems to - weather I expected not. Is that correct or am I doing something
> wrong?
> 
> I've noticed if I do the same thing with a database on disk and between
> processes, the second process won't see the changes until the first one
will
> commit the transaction.
> 

Isolation only occurs between separate database handles.  If
you do two statements against the same database handle, they
will not be isolated.  I do not understand why you would expect
that they would be.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] help with GROUP BY

[Jay Sprenkle]

> 
> 
> SELECT t.title, t.date, t.speed
> FROM table t JOIN (
> SELECT title, MAX(speed) maxsp
> FROM table
> GROUP BY title) t2 ON t.title = t2.title
> WHERE t.speed = t2.maxsp
> ORDER BY t.title
> 
> Couldn't have done without your suggestion. Here's a virtual 22 oz. of
> cold, local Wisconsin beer to you.
> 

This will return multiple rows when there are several with the same speed.

---
The Castles of Dereth Calendar: a tour of the art and architecture of 
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] Sqlite.dll killing Tcl?

[D. Richard Hipp]

On Mon, 2005-09-19 at 14:15 -0500, Dinsmore, Jeff wrote:
> I can't reproduce the failure, so it's difficult to fix the problem
> S, I'm asking the experts.
> 
> Have any of you seen anything like this?
> 

Nothing comes to mind.  But you are running a 2 year old version
of SQLite.  The first thing I would try is upgrading to version
2.8.16.  If it is possible for you to migrate your legacy database
files forward to the 3.0 format, then upgrade to version 3.2.6.

Failing that, you can scan the log of error reports going back
two years at http://www.sqlite.org/cvstrac/timeline and see if
you spot anything similar.

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

Re: [sqlite] DEFAULT PAGE SIZE option = malformed database schema

[Guillaume Fougnies]

Ok, i saw the Check-in #2724.

You should make in pager.h:

#if SQLITE_DEFAULT_PAGE_SIZE > SQLITE_MAX_PAGE_SIZE
#error "blah blah"
#endif

Mon, Sep 19, 2005 at 02:53:53PM -0400: D. Richard Hipp wrote:
> On Mon, 2005-09-19 at 13:42 -0400, D. Richard Hipp wrote:
> > On Mon, 2005-09-19 at 19:04 +0200, Guillaume Fougnies wrote:
> > > Hello,
> > > 
> > > During the upgrade from 3.2.5 to 3.2.6, i removed my
> > > compile time option SQLITE_DEFAULT_PAGE_SIZE.
> > > 
> > > 3.2.6 is not able to access anymore to databases !?!
> > > SQLite version 3.2.6
> > > Enter ".help" for instructions
> > > sqlite> .schema
> > > Error: malformed database schema
> > > 
> > > 
> 
> Recompile with -DSQLITE_MAX_PAGE_SIZE=32768 or ...=16384.
> Should work then.
--
Guillaume FOUGNIES Cell: +33 6 23869151
Eulerian Technologies  Fax : +33 1 43665802
52 bis rue ramponeau   ICQ :   19261222
75020 Paris - FRANCE   MSN : [EMAIL PROTECTED]

Re: [sqlite] query problem

[D. Richard Hipp]

On Mon, 2005-09-19 at 19:36 +0200, Alain Bertrand wrote:
> hi all,
> 
> I am porting a program from mysql to sqlite.
> The following statement doesn't work correctly with sqlite though it does
> with mysql.
> SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON
> ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots
> ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId
> 

I believe the query above should always return NULL (or 0 prior
to check-in [2677]).  Here's why:

Joins group from left to right.  The first join to be evaluated
is:

photos LEFT JOIN trees ON photos.kind=1 AND photos.refid=trees.id

This join results (logically) in a table where every row has a
column named "photos.kind" with a value of 1.  This logical table
is then joined as follows:

LEFT JOIN pots ON photos.kind=2 AND photos.refid=pots.id

In this second join, the photos.kind=2 condition can never be met 
because every row in the result of the previous join has photos.kind==1.
Hence, the result set contains no rows.  A COUNT() of a empty result
set gives NULL.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Sqlite.dll killing Tcl?

[Dinsmore, Jeff]

I have a little wrapped Tcl server app that uses sqlite.dll via the Tcl
API.

Tcl version: 8.4.9
Sqlite version: 2.8.6 
Running on Windows 2003 Server.

Every once in a while, the server will die - and it looks like
sqlite.dll is doing the killing.

There are two different processes that occcasionally contend for the
same database. One is the server, the other is a cleanup agent. When the
cleanup agent is using the database and the server tries to access it,
VERY occasionally, the server app will die. As far as I can see, I'm
handling all error conditions properly and, for the most part, it's
working. But once a month or so, it goes kablooie. 

I can't reproduce the failure, so it's difficult to fix the problem
S, I'm asking the experts.

Have any of you seen anything like this?

Thanks in advance,

Jeff Dinsmore
MIS - Interfaces
Ridgeview Medical Center
[EMAIL PROTECTED]
952.442.2191 x6592



Ridgeview Medical Center Confidentiality Notice: This email message, including 
any attachments, is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message.

Re: [sqlite] Re: query problem

[Puneet Kishor]

Alain Bertrand wrote:

Puneet Kishor a écrit :


without seeing the data, a good guess would be, "Yes."


What do you mean ? That my query is syntaxically wrong ?


Your query is syntactically correct in that it returns a result without 
the SQL engine generating a syntax error... it just doesn't seem to be 
producing what you want, hence it is perhaps logically incorrect, non!.






LEFT JOIN selects all the rows with values from the left table and 
either matching values or NULL from the right table.


Yes, I know, not very well because I have only a basic knowledge of sql 
(this query has been written by a friend), but this is what I want.
This may look complicated for just counting the number of rows, but in 
fact this a kind of minimum example to reproduce the problem I have with 
a more complex query.



If you just had JOIN then you would get only those rows where both left 
and right sides match (the result set would have fewer records than...) 
if you have LEFT (or RIGHT) JOIN whereby you will get all the rows where 
the left side matches, and NULL for where right side doesn't match (the 
result set would have more records).



Try SQLzoo.net for very nice tutorials with basic to intermediate SQL.

Re: [sqlite] help with GROUP BY

[Puneet Kishor]

Kurt Welgehausen wrote:
I want the title, the MAX(speed) for each title group, and the date 
that occurred. In case, ...



I haven't tried this, but I think it's correct. Let me
know if it's not.

  select distinct table.title, table.date, t2.maxsp
  from table,
   (select title ttl, max(speed) maxsp from table group by ttl) t2
  where table.title = t2.ttl and table.speed = t2.maxsp



thanks Kurt... a slight tweak on your suggestion, and I get the desired 
results from the following


SELECT t.title, t.date, t.speed
FROM table t JOIN (
 SELECT title, MAX(speed) maxsp
 FROM table
 GROUP BY title) t2 ON t.title = t2.title
WHERE t.speed = t2.maxsp
ORDER BY t.title

Couldn't have done without your suggestion. Here's a virtual 22 oz. of 
cold, local Wisconsin beer to you.

Re: [sqlite] DEFAULT PAGE SIZE option = malformed database schema

[D. Richard Hipp]

On Mon, 2005-09-19 at 13:42 -0400, D. Richard Hipp wrote:
> On Mon, 2005-09-19 at 19:04 +0200, Guillaume Fougnies wrote:
> > Hello,
> > 
> > During the upgrade from 3.2.5 to 3.2.6, i removed my
> > compile time option SQLITE_DEFAULT_PAGE_SIZE.
> > 
> > 3.2.6 is not able to access anymore to databases !?!
> > SQLite version 3.2.6
> > Enter ".help" for instructions
> > sqlite> .schema
> > Error: malformed database schema
> > 
> > 

Recompile with -DSQLITE_MAX_PAGE_SIZE=32768 or ...=16384.
Should work then.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Re: query problem

[Alain Bertrand]

Puneet Kishor a écrit :


without seeing the data, a good guess would be, "Yes."

What do you mean ? That my query is syntaxically wrong ?


LEFT JOIN selects all the rows with values from the left table and 
either matching values or NULL from the right table.
Yes, I know, not very well because I have only a basic knowledge of sql 
(this query has been written by a friend), but this is what I want.
This may look complicated for just counting the number of rows, but in 
fact this a kind of minimum example to reproduce the problem I have with 
a more complex query.


Regards,
Alain

Re: [sqlite] help with GROUP BY

[Kurt Welgehausen]

> I want the title, the MAX(speed) for each title group, and the date 
> that occurred. In case, ...

I haven't tried this, but I think it's correct. Let me
know if it's not.

  select distinct table.title, table.date, t2.maxsp
  from table,
   (select title ttl, max(speed) maxsp from table group by ttl) t2
  where table.title = t2.ttl and table.speed = t2.maxsp

Regards

Re: [sqlite] Multiple Threads and Transactions

[D. Richard Hipp]

On Mon, 2005-09-19 at 13:27 -0400, Iulian Popescu wrote:
> Hello,
> 
>  
> 
> I'm kind of new to the SQLite. I'm running an instance of the database in
> memory. I'm trying to figure out if the transaction isolation is also valid
> between threads. To give a concrete example, two threads as part of the same
> process share the same database connection. Suppose a transaction is started
> in one thread and inserts some rows in a table A. If the same thread does a
> SELECT * FROM A it should see the inserted records if my understanding is
> correct. What will happen if another thread does a SELECT from the same
> table A before the first thread commits the transaction? Will it see the
> records inserted by the first thread? Based on the tests I have made it
> seems to - weather I expected not. Is that correct or am I doing something
> wrong?
> 
> I've noticed if I do the same thing with a database on disk and between
> processes, the second process won't see the changes until the first one will
> commit the transaction.
> 

Isolation only occurs between separate database handles.  If
you do two statements against the same database handle, they
will not be isolated.  I do not understand why you would expect
that they would be.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] help with GROUP BY

[Jay Sprenkle]

You need a unique field in your table to do this right. I used title here.
This is the idea:

SELECT a.title, b.date, a.mx 
FROM
( SELECT title, date, MAX(speed) AS mx FROM tbl GROUP BY title, date ) AS a
INNER JOIN ( SELECT title, date, MAX(speed) AS mx FROM tbl GROUP BY title, 
date ORDER BY date DESC LIMIT 1 ) AS b ON b.title = a.title and
b.mx=
a.mx 
ORDER BY a.title, b.date

Table A gets the title and max speed. Table B gets a single latest date 
associated with the maximum speed Ideally you'd join B using an ID field so 
you could get
the date associated with a specific record.



On 9/19/05, Puneet Kishor <[EMAIL PROTECTED]> wrote:
> 
> I have a table like so...
> 
> SELECT title, date, speed
> FROM table
> ORDER BY title
> 
> title date speed
>  -- -
> 2484312030 2005-04-01  *
> 2484312030 2005-04-02  *
> 2484312030 2005-04-03  *
> 2484312030 2005-04-04  *
> 2484312030 2005-04-14  *
> 2484312030 2005-05-19 10
> 2484312030 2005-05-20 9
> 2484312030 2005-05-23 49
> 2484312030 2005-05-24 52
> 2484312030 2005-05-25 41
> 2484312030 2005-05-26 70
> 2484312030 2005-07-14 40
> 2484312030 2005-07-15 47
> Angela 2005-07-27 52
> Angela 2005-07-28 58 *
> Angela 2005-07-29 57
> Angela 2005-08-04 57
> Ann 2005-08-01 48
> Ann 2005-08-08 94 *
> Ann 2005-08-09 65
> Ann 2005-08-10 80
> Ann 2005-08-15 66
> Ann 2005-08-16 94 *
> Ann 2005-08-16 94
> Ann 2005-08-16 94
> 
> I want the title, the MAX(speed) for each title group, and the date
> that occurred. In case, the MAX(speed) for a given title occurs more
> than once on a given date, I want only one instance of it... in other
> words, given the above table, I want...
> 
> title date speed
>  -- -
> 2484312030 2005-04-01 
> 2484312030 2005-04-02 
> 2484312030 2005-04-03 
> 2484312030 2005-04-04 
> 2484312030 2005-04-14 
> Angela 2005-07-28 58
> Ann 2005-08-08 94
> Ann 2005-08-16 94
> 
> The above are only the ones I have marked with a * in the first table.
> So, I started with
> 
> SELECT title, date, MAX(speed) AS mx
> FROM table
> GROUP BY title, date, speed
> HAVING speed = MAX(speed)
> ORDER BY title
> 
> But that didn't get me anywhere. Any guidance will be much
> appreciated...
> 
> --
> Puneet Kishor
> 
> 


-- 
---
The Castles of Dereth Calendar: a tour of the art and architecture of 
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] DEFAULT PAGE SIZE option = malformed database schema

[D. Richard Hipp]

On Mon, 2005-09-19 at 19:04 +0200, Guillaume Fougnies wrote:
> Hello,
> 
> During the upgrade from 3.2.5 to 3.2.6, i removed my
> compile time option SQLITE_DEFAULT_PAGE_SIZE.
> 
> 3.2.6 is not able to access anymore to databases !?!
> SQLite version 3.2.6
> Enter ".help" for instructions
> sqlite> .schema
> Error: malformed database schema
> 
> 
> SQLite version 3.2.6
> Enter ".help" for instructions
> sqlite> pragma SQLITE_DEFAULT_PAGE_SIZE=16384;
> sqlite> .schema
> Error: malformed database schema
> 

I cannot debug this problem unless you provide more
information.  What were you setting DEFAULT_PAGE_SIZE to,
for example.  Are you sure the original database is
readable by a stock copy of 3.2.5?  Can you provide
a copy of the database that does not work?
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] query problem

[Puneet Kishor]

On Sep 19, 2005, at 12:36 PM, Alain Bertrand wrote:


hi all,

I am porting a program from mysql to sqlite.
The following statement doesn't work correctly with sqlite though it 
does

with mysql.
SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON
ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN 
ttd_pots

ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId

In my test case, it reports 0 where it should report 2. This is the 
LEFT

JOIN part which has a problem ;)



without seeing the data, a good guess would be, "Yes."

LEFT JOIN selects all the rows with values from the left table and 
either matching values or NULL from the right table.



--
Puneet Kishor

[sqlite] query problem

[Alain Bertrand]

hi all,

I am porting a program from mysql to sqlite.
The following statement doesn't work correctly with sqlite though it does
with mysql.
SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON
ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots
ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId

In my test case, it reports 0 where it should report 2. This is the LEFT
JOIN part which has a problem ;)

Any idea ?

Thanks,

Alain

[sqlite] Multiple Threads and Transactions

[Iulian Popescu]

Hello,

 

I'm kind of new to the SQLite. I'm running an instance of the database in
memory. I'm trying to figure out if the transaction isolation is also valid
between threads. To give a concrete example, two threads as part of the same
process share the same database connection. Suppose a transaction is started
in one thread and inserts some rows in a table A. If the same thread does a
SELECT * FROM A it should see the inserted records if my understanding is
correct. What will happen if another thread does a SELECT from the same
table A before the first thread commits the transaction? Will it see the
records inserted by the first thread? Based on the tests I have made it
seems to - weather I expected not. Is that correct or am I doing something
wrong?

I've noticed if I do the same thing with a database on disk and between
processes, the second process won't see the changes until the first one will
commit the transaction.

 

Thank you,

 

Iulian.

[sqlite] DEFAULT PAGE SIZE option = malformed database schema

[Guillaume Fougnies]

Hello,

During the upgrade from 3.2.5 to 3.2.6, i removed my
compile time option SQLITE_DEFAULT_PAGE_SIZE.

3.2.6 is not able to access anymore to databases !?!
SQLite version 3.2.6
Enter ".help" for instructions
sqlite> .schema
Error: malformed database schema


SQLite version 3.2.6
Enter ".help" for instructions
sqlite> pragma SQLITE_DEFAULT_PAGE_SIZE=16384;
sqlite> .schema
Error: malformed database schema

Best regards,
--
Guillaume FOUGNIES
Eulerian Technologies
'I can't believe my eyes I must be dreaming, Wake up Jack, this
isn't fair!' ~ Jack

RE: [sqlite] problems compiling 3.2.6

[D. Richard Hipp]

On Mon, 2005-09-19 at 17:25 +0100, Drew, Stephen wrote:
> As you say, I think the first is just that Visual Studio 6 has a missing
> definition in winbase.h. I agree with you that it is in the
> documentation (perhaps a check could be performed and it defined if it
> doesn't exist...)
> 
> The second seems to be a limitation of VS6 - it certainly isn't a
> problem in VS7. Can't really think of any neat solution around it
> either...
> 

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

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

[sqlite] help with GROUP BY

[Puneet Kishor]

I have a table like so...

SELECT title, date, speed
FROM table
ORDER BY title

 titledatespeed
  --  -
 2484312030   2005-04-01  *
 2484312030   2005-04-02  *
 2484312030   2005-04-03  *
 2484312030   2005-04-04  *
 2484312030   2005-04-14  *
 2484312030   2005-05-19  10
 2484312030   2005-05-20  9
 2484312030   2005-05-23  49
 2484312030   2005-05-24  52
 2484312030   2005-05-25  41
 2484312030   2005-05-26  70
 2484312030   2005-07-14  40
 2484312030   2005-07-15  47
 Angela   2005-07-27  52
 Angela   2005-07-28  58  *
 Angela   2005-07-29  57
 Angela   2005-08-04  57
 Ann  2005-08-01  48
 Ann  2005-08-08  94  *
 Ann  2005-08-09  65
 Ann  2005-08-10  80
 Ann  2005-08-15  66
 Ann  2005-08-16  94  *
 Ann  2005-08-16  94
 Ann  2005-08-16  94

I want the title, the MAX(speed) for each title group, and the date 
that occurred. In case, the MAX(speed) for a given title occurs more 
than once on a given date, I want only one instance of it... in other 
words, given the above table, I want...


 titledatespeed
  --  -
 2484312030   2005-04-01  
 2484312030   2005-04-02  
 2484312030   2005-04-03  
 2484312030   2005-04-04  
 2484312030   2005-04-14  
 Angela   2005-07-28  58
 Ann  2005-08-08  94
 Ann  2005-08-16  94

The above are only the ones I have marked with a * in the first table. 
So, I started with


SELECT title, date, MAX(speed) AS mx
FROM table
GROUP BY title, date, speed
HAVING speed = MAX(speed)
ORDER BY title

But that didn't get me anywhere. Any guidance will be much 
appreciated...


--
Puneet Kishor

RE: [sqlite] problems compiling 3.2.6

[Drew, Stephen]

Further update:

I get both these errors in Visual Studio 6.

As you say, I think the first is just that Visual Studio 6 has a missing
definition in winbase.h. I agree with you that it is in the
documentation (perhaps a check could be performed and it defined if it
doesn't exist...)

The second seems to be a limitation of VS6 - it certainly isn't a
problem in VS7. Can't really think of any neat solution around it
either...

This doesn't concern me, as - as I mention - I use VS7...

Steve

-Original Message-
From: Drew, Stephen 
Sent: 19 September 2005 17:13
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] problems compiling 3.2.6

Mike,

3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can give it a go
in my copy of Visual Studio 6 if you like...

Steve 

-Original Message-
From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
Sent: 19 September 2005 10:14
To: sqlite-users@sqlite.org
Subject: [sqlite] problems compiling 3.2.6

I tried to compile 3.2.6 locally, using visual c 6, as I do with all
sqlite releases. this version introduces a couple of changes that do not
compile:

os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
identifier
vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
not implemented, use signed __int64

the first error has to do with an old version of winbase.h, which for
some reason omits the definition of INVALID_SET_FILE_POINTER (although
the documentation mentions it). this is probably a local problem, and I
will try to fix locally (although I would like to hear from other people
about it. I am compiling WITHOUT mfc).

the second I have no idea, as I don't know C.

anybody help ?

RE: [sqlite] problems compiling 3.2.6

[Drew, Stephen]

Mike,

3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can give it a go
in my copy of Visual Studio 6 if you like...

Steve 

-Original Message-
From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] 
Sent: 19 September 2005 10:14
To: sqlite-users@sqlite.org
Subject: [sqlite] problems compiling 3.2.6

I tried to compile 3.2.6 locally, using visual c 6, as I do with all
sqlite releases. this version introduces a couple of changes that do not
compile:

os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
identifier
vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
not implemented, use signed __int64

the first error has to do with an old version of winbase.h, which for
some reason omits the definition of INVALID_SET_FILE_POINTER (although
the documentation mentions it). this is probably a local problem, and I
will try to fix locally (although I would like to hear from other people
about it. I am compiling WITHOUT mfc).

the second I have no idea, as I don't know C.

anybody help ?

Re: [sqlite] binding ORDER BY

[D. Richard Hipp]

On Mon, 2005-09-19 at 09:42 -0500, [EMAIL PROTECTED] wrote:
> I am trying to 'ORDER BY ?' in my query because I would like to bind the
> order by criteria later in my code.  This always fails on sqlite3_prepare()
> with an error, "ORDER BY terms must be non-integer constants".  Do you have
> any suggestions on how I can use a variable in my ORDER BY clause?
> Thanks,
> Nicole Hinderman
> 
> 

Changes to the ORDER BY modify the choice of algorithms used
to process the query.  This requires that you rerun 
sqlite3_prepare() in order to generate new code for the
virtual machine.

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

[sqlite] binding ORDER BY

[njhinder]

I am trying to 'ORDER BY ?' in my query because I would like to bind the
order by criteria later in my code.  This always fails on sqlite3_prepare()
with an error, "ORDER BY terms must be non-integer constants".  Do you have
any suggestions on how I can use a variable in my ORDER BY clause?
Thanks,
Nicole Hinderman

RE: [sqlite] Using date fields in SQLiteExplorer

[Cariotoglou Mike]

it is definitely possible. 
how have you defined the data type in the CREATE statement ?
how did the data was inserted in the table initially ?

have you enabled the "use datatypes" option ?

dump the contents of the table with the "datatypes" disabled, so you see
raw data. are they really floating point values ?

remember, you can use the "show only" check box, to see the SQL that the
resolver will produce, without actually running it.
 
if you cannot figure it out, send me the db.

> -Original Message-
> From: Zibetti Paolo [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 19, 2005 1:07 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Using date fields in SQLiteExplorer
> 
> Here is another question about dates and SQLiteExplorer (v 1.7).
> I'm storing dates in the native Delphi format, i.e. as 
> floating point numbers ("dates as text" = FALSE).
> Whenever I try to update a record that contains such a date, 
> SQLiteExplorer displays the message "unexpected count of 
> affected records:0" and modifications are not written to the database.
> Is it possible that SQLiteExplorer is trying to update the 
> record on the database using the date fields in a "where" 
> clause but fails because of rounding errors in the floating 
> point rapresentation of dates ?
> How can I work around this problem ?
> 
> Thank you
> Bye
> 
> 
> 
> 

[sqlite] Using date fields in SQLiteExplorer

[Zibetti Paolo]

Here is another question about dates and SQLiteExplorer (v 1.7).
I'm storing dates in the native Delphi format, i.e. as floating point
numbers ("dates as text" = FALSE).
Whenever I try to update a record that contains such a date, SQLiteExplorer
displays the message "unexpected count of affected records:0" and
modifications are not written to the database.
Is it possible that SQLiteExplorer is trying to update the record on the
database using the date fields in a "where" clause but fails because of
rounding errors in the floating point rapresentation of dates ?
How can I work around this problem ?

Thank you
Bye

[sqlite] problems compiling 3.2.6

[Cariotoglou Mike]

I tried to compile 3.2.6 locally, using visual c 6, as I do with all
sqlite releases. this version introduces a couple of changes that do not
compile:

os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
identifier
vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
not implemented, use signed __int64

the first error has to do with an old version of winbase.h, which for
some reason omits the definition of INVALID_SET_FILE_POINTER (although
the documentation mentions it). this is probably a local problem,
and I will try to fix locally (although I would like to hear from other
people about it. I am compiling WITHOUT mfc).

the second I have no idea, as I don't know C.

anybody help ?