Re: [sqlite] Slim down join results (all fields returned)

[John Machin]

On 18/04/2009 2:33 PM, flakpit wrote:
> If anyone else has a moment and a concrete example instead of sending me to
> tutorials, feel free to step in here.

If you would take a moment to read the fraction of a screen of a 
tutorial that I pointed you at (and which contains a concrete example), 
instead of repeating what you have already explained you want and is 
screamingly obvious anyway, you might actually *learn* something, and be 
able to find answers for yourself quickly.

> SELECT * FROM pubs; I want ALL columns from the 'pubs'
> table
> 
> WHERE pub_title LIKE '%salem%'  ; Where the title sounds like 'salem'

*sounds* like? The LIKE operator does simple pattern matching ... LIKE 
'%salem%' will match a string containing 'salem' (or 'Salem' or 'SaLem' 
or ...) anywhere, e.g. 'History of Salem, Mass.' or 'Jerusalem'. If you 
want any sort of phonetic matching, you'd have to write a user-defined 
function and use the MATCH operator instead of LIKE.

> The query below retrieves the correct note and publisher for each retrieved
> record in 'pubs' that sounds like 'salem' in the 'pub_title' but also every
> other column in 'notes' and 'publishers' which I don't want.

> I just want the matching notes.note_note for each pubs record and not
> notes.id or notes.idx etc.
> I just want the matching publishers.publisher_name for each pubs record and
> not publishers.id or publishers.idx etc.

> SELECT * FROM pubs

What do you want from the pubs table? All columns
What do you want from the publishers table? The publisher_name column
What do you want from the notes table? the note_note column [are you 
sure? It was note_notes in your previous message]

So just tell it what you want:

SELECT pubs.*, publishers.publisher_name, notes.note_note FROM ...

> INNER JOIN notes
> ON pubs.note_id=notes.note_id
> INNER JOIN publishers
> ON pubs.publisher_id=publishers.publisher_id
> WHERE pub_title LIKE '%salem%'


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

Re: [sqlite] Slim down join results (all fields returned)

[P Kishor]

On Fri, Apr 17, 2009 at 11:33 PM, flakpit  wrote:
>
> If anyone else has a moment and a concrete example instead of sending me to
> tutorials, feel free to step in here.
>
> SELECT * FROM pubs                    ; I want ALL columns from the 'pubs'
> table
>
> WHERE pub_title LIKE '%salem%'  ; Where the title sounds like 'salem'
>
> In the pubs table, there is an ID field that points to the note in the
> 'notes' table. I want to retrieve that note for the currently retrieved
> record from the 'pubs' table and no other column in the 'notes table'
>
> In the pubs table, there is an ID field that points to the publisher in the
> 'publisher' table. I want to retrieve that publisher for the currently
> retrieved record from the 'publishers' table and no other column in the
> 'publishers' table
>
> The query below retrieves the correct note and publisher for each retrieved
> record in 'pubs' that sounds like 'salem' in the 'pub_title' but also every
> other column in 'notes' and 'publishers' which I don't want.
>
> I just want the matching notes.note_note for each pubs record and not
> notes.id or notes.idx etc.
> I just want the matching publishers.publisher_name for each pubs record and
> not publishers.id or publishers.idx etc.
>
> SELECT * FROM pubs
> INNER JOIN notes
> ON pubs.note_id=notes.note_id
> INNER JOIN publishers
> ON pubs.publisher_id=publishers.publisher_id
> WHERE pub_title LIKE '%salem%'
>

SELECT pubs.*,
  notes.note,
  publishers.publisher
FROM pubs JOIN notes ON pubs.note_id = notes.note_id
  JOIN publishers ON pubs.publisher_id = publishers.publisher_id
WHERE pubs.pub_title LIKE '%salem%'


> I believe I explained myself well enough this time.

You did. You should still do some SQL tutorials.

In the meantime, I hope the above helps.


> --
> View this message in context: 
> http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23109313.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://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[flakpit]

If anyone else has a moment and a concrete example instead of sending me to
tutorials, feel free to step in here.

SELECT * FROM pubs; I want ALL columns from the 'pubs'
table

WHERE pub_title LIKE '%salem%'  ; Where the title sounds like 'salem'

In the pubs table, there is an ID field that points to the note in the
'notes' table. I want to retrieve that note for the currently retrieved
record from the 'pubs' table and no other column in the 'notes table'

In the pubs table, there is an ID field that points to the publisher in the
'publisher' table. I want to retrieve that publisher for the currently
retrieved record from the 'publishers' table and no other column in the
'publishers' table

The query below retrieves the correct note and publisher for each retrieved
record in 'pubs' that sounds like 'salem' in the 'pub_title' but also every
other column in 'notes' and 'publishers' which I don't want.

I just want the matching notes.note_note for each pubs record and not
notes.id or notes.idx etc.
I just want the matching publishers.publisher_name for each pubs record and
not publishers.id or publishers.idx etc.

SELECT * FROM pubs
INNER JOIN notes
ON pubs.note_id=notes.note_id
INNER JOIN publishers
ON pubs.publisher_id=publishers.publisher_id
WHERE pub_title LIKE '%salem%'

I believe I explained myself well enough this time.
-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23109313.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] Multi column ORDER BY across table peformance problem....

[Harold Wood]

just curious; have you tried doing the select in a subquery, and then the order 
by in the outer query?

 
woody
--- On Fri, 4/17/09, sorka  wrote:

From: sorka 
Subject: [sqlite] Multi column ORDER BY across table peformance problem
To: sqlite-users@sqlite.org
Date: Friday, April 17, 2009, 11:12 PM

This should be simple but apparently it isn't.

I have two tables:
"CREATE TABLE showing ( "
"showingIdINTEGER PRIMARY KEY, "
"stationId  INTEGER, "
"startTime  INTEGER, "
") "
CREATE INDEX showing_startTime on showing(startTime);


AND
"CREATE TABLE channel ( "
"  channelIdINTEGER PRIMARY KEY, "
"  ChannelNumber   INTEGER, "
"  stationIdINTEGER, "
"  ) "
CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
CREATE INDEX channel_stationId on channel(stationId);

When I do this select:
SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
123923 AND showing.stationId = channel.stationId ORDER BY
showing.startTime LIMIT 8;

I get back the correct 8 results in about 3 milliseconds. 

If I throw a secondary order term in there say ChannelNumber:

SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
123923 AND showing.stationId = channel.stationId ORDER BY
showing.startTime, channel.ChannelMajorNumber LIMIT 8;

It now takes over 120 seconds!!!

I've tried various multi-column indices including one on channel(stationId,
ChannelNumber).

No difference.

As far as I can tell, when ordering on columns that cross tables, sqlite
will bring in all the records that match the equality or inequality search
term on the first column before it does a secondary sort.

I have over 100,000 records in the showing table and about 100 records in
the channel table.

Sqlite should be smart enough to do the secondary sort on ChannelNumber as
soon as it sees that the records coming back have a later startTime than the
previous one. i.e. Say the first 5 records have the same startTime and
different channel numbers. Then the next 5 records have a later start time
than the first. Sqlite should be smart enough to see this as the results
come back and do a secondary sort on ChannelNumber on the first 5 results
and then rinse and repeat.

What appears to be happening is that even though startTime is indexed,
sqlite is bringing all 100K records into memory sorted by time before it
starts to sort by channel number. 

Is there a way to get sqlite to do the right thing? If there was only a way
to have a multi-column index that included columns from different tables. Oh
wait, there is, it's called an intermediate table. However the cost of
doing
this is pretty high for reasons I can't go into here. 

Any ideas? Maybe I'm just doing something wrong and this should be simple.

Thanks.



-- 
View this message in context:
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23109024.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

[sqlite] Multi column ORDER BY across table peformance problem....

[sorka]

This should be simple but apparently it isn't.

I have two tables:
"CREATE TABLE showing ( "
"showingIdINTEGER PRIMARY KEY, "
"stationId  INTEGER, "
"startTime  INTEGER, "
") "
CREATE INDEX showing_startTime on showing(startTime);


AND
"CREATE TABLE channel ( "
"  channelIdINTEGER PRIMARY KEY, "
"  ChannelNumber   INTEGER, "
"  stationIdINTEGER, "
"  ) "
CREATE INDEX channel_ChannelNumber on channel(ChannelNumber);
CREATE INDEX channel_stationId on channel(stationId);

When I do this select:
SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
123923 AND showing.stationId = channel.stationId ORDER BY
showing.startTime LIMIT 8;

I get back the correct 8 results in about 3 milliseconds. 

If I throw a secondary order term in there say ChannelNumber:

SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >=
123923 AND showing.stationId = channel.stationId ORDER BY
showing.startTime, channel.ChannelMajorNumber LIMIT 8;

It now takes over 120 seconds!!!

I've tried various multi-column indices including one on channel(stationId,
ChannelNumber).

No difference.

As far as I can tell, when ordering on columns that cross tables, sqlite
will bring in all the records that match the equality or inequality search
term on the first column before it does a secondary sort.

I have over 100,000 records in the showing table and about 100 records in
the channel table.

Sqlite should be smart enough to do the secondary sort on ChannelNumber as
soon as it sees that the records coming back have a later startTime than the
previous one. i.e. Say the first 5 records have the same startTime and
different channel numbers. Then the next 5 records have a later start time
than the first. Sqlite should be smart enough to see this as the results
come back and do a secondary sort on ChannelNumber on the first 5 results
and then rinse and repeat.

What appears to be happening is that even though startTime is indexed,
sqlite is bringing all 100K records into memory sorted by time before it
starts to sort by channel number. 

Is there a way to get sqlite to do the right thing? If there was only a way
to have a multi-column index that included columns from different tables. Oh
wait, there is, it's called an intermediate table. However the cost of doing
this is pretty high for reasons I can't go into here. 

Any ideas? Maybe I'm just doing something wrong and this should be simple.

Thanks.



-- 
View this message in context: 
http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23109024.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] Slim down join results (all fields returned)

[John Machin]

On 18/04/2009 8:24 AM, flakpit wrote:
>> SELECT pubs.* from pubs, notes, publishers WHERE ...
> 
> Thanks, but it didn't work the way I expected it to:)
> 
> Now I have my query laid out  a lot better and can 'slim' it down from here
> 
> SELECT * FROM pubs
> INNER JOIN notes
> ON pubs.note_id=notes.note_id  ; notes.

I presume the "; notes." on the end of the above line is not meant to be 
there.

> INNER JOIN publishers
> ON pubs.publisher_id=publishers.publisher_id
> WHERE pub_title LIKE '%salem%'
> 
> How do I return only the matching notes.note_notes field in that join and no
> the rest of the fields in notes?
> 
> How do I return only the matching publishers.publisher_name field in that
> join and not the rest of the fields in publishers?
> 
> Probably still explaining myself very badly here. Oh well.

Either that or you need to work through an elementary SQL tutorial ... 
e.g. see http://www.w3schools.com/Sql/sql_join_inner.asp

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

Re: [sqlite] Slim down join results (all fields returned)

[flakpit]

>SELECT pubs.* from pubs, notes, publishers WHERE ...

Thanks, but it didn't work the way I expected it to:)

Now I have my query laid out  a lot better and can 'slim' it down from here

SELECT * FROM pubs
INNER JOIN notes
ON pubs.note_id=notes.note_id  ; notes.
INNER JOIN publishers
ON pubs.publisher_id=publishers.publisher_id
WHERE pub_title LIKE '%salem%'

How do I return only the matching notes.note_notes field in that join and no
the rest of the fields in notes?

How do I return only the matching publishers.publisher_name field in that
join and not the rest of the fields in publishers?

Probably still explaining myself very badly here. Oh well.
-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23107065.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] combine records with matching values.

[Igor Tandetnik]

"David Bicking"  wrote
in message news:80463.54975...@web31802.mail.mud.yahoo.com
> I am trying to figure out an sql query or queries that will do the
> following:
>
> CREATE TABLE (Key_Fields, Start, End, Value_Fields,
>  Primary Key(Key_Fields, Start, End));
>
> For any pair of records where the Key_fields are the same, and the
> Start of the one is the same as the end of the other, then if the
> value_fields are the same, then remove the two and replace with one
> entry with the non-common start and end values.

I don't see a way around using a temporary table. Something like this 
perhaps:

create temp table toUpdate as
select t1.rowid id, t2.End newEnd from myTable t1 join myTable t2 on (
t1.Key_Fields = t2.Key_Fields and t1.Value_Fields = t2.Value_Fields 
and
t1.End = t2.Start);

delete from myTable where exists (
select 1 from myTable t2 where
myTable.Key_Fields = t2.Key_Fields and myTable.Value_Fields = 
t2.Value_Fields and
myTable.Start = t2.End);

update myTable set End = (select newEnd from toUpdate u where u.id = 
myTable.rowId)
where rowid in (select id from toUpdate);

drop table toUpdate;


Igor Tandetnik 



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

Re: [sqlite] Error : sqlite3.h:1722: parse error before string constant

[manohar s]

Hi,
 Renaming the parameters did the trick. however I searched in the app for
such declarations, I could not find any, But there are many third party
libraries are included before that. One of them might have caused the
problem. Thanks for the quick replies.

Manohar

On Sat, Apr 18, 2009 at 12:31 AM, D. Richard Hipp  wrote:

>
> On Apr 17, 2009, at 2:53 PM, manohar s wrote:
>
> > Hi
> >
> > I am getting this error:
> >
> > sqlite3.h:1757: parse error before string constant
> >
> > Whenever I am including the static sqlite library in my c++ app.
> > This is
> > happening with recent release 3.6.13. Earlier I never experienced any
> > problem. In that line in the header file,  This function is present:
> >
> > void sqlite3_randomness(int N, void *P);
> >
> > Strangely I am able to compile successfully if I comment out this
> > line!
> >
> > 1) Am I doing anything wrong here?
> > 2) Is it safe to comment this out?
> >
>
> No, it is not safe to comment this line out. You need to fix the
> problem, not just ameliorate the symptoms.
>
> Perhaps you have a line such as one of these:
>
>  #define P "something"
>  #define N "something"
>
> Somewhere in one of your header files prior to the point where you
> #include "sqlite3.h"?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
hope is the only thing which keeps us all happy
http://sqlyog.wordpress.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange SAVEPOINT behaviour with locking_mode=exclusive

[Ralf Junker]

I experience some strange behaviour with SAVEPOINT in combination with 
locking_mode=exclusive. Below is a script which I assembled from savepoint.text 
13.1 to 13.4. Those tests run OK in normal locking mode, but fail in exclusive 
locking more.

To reproduce, run the script below on a NEW and EMPTY database. The result 
difference is marked as follows:

  a  b
  1  2 
  3  4 
  5  6 
  7  8 
  9  10 
  11 12 
  13 14 <- missing rows if run in exclusive mode 
  15 16 <- on a new and empty database



PRAGMA locking_mode=exclusive;

DROP TABLE IF EXISTS t1;

BEGIN;
  CREATE TABLE t1(a PRIMARY KEY, b);
  INSERT INTO t1 VALUES(1, 2);
COMMIT;

PRAGMA journal_mode = off;

BEGIN;
  INSERT INTO t1 VALUES(3, 4);
  INSERT INTO t1 SELECT a+4,b+4  FROM t1;
COMMIT;

BEGIN;
  INSERT INTO t1 VALUES(9, 10);
  SAVEPOINT s1;
INSERT INTO t1 VALUES(11, 12);
COMMIT;

BEGIN;
  INSERT INTO t1 VALUES(13, 14);
  SAVEPOINT s1;
INSERT INTO t1 VALUES(15, 16);
ROLLBACK TO s1;
ROLLBACK;

SELECT * FROM t1;



I wonder if this behaviour is intended of if there is an error in the library?

Ralf

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

[sqlite] combine records with matching values.

[David Bicking]

I am trying to figure out an sql query or queries that will do the following:

CREATE TABLE (Key_Fields, Start, End, Value_Fields,
  Primary Key(Key_Fields, Start, End));

For any pair of records where the Key_fields are the same, and the Start of the 
one is the same as the end of the other, then if the value_fields are the same, 
then remove the two and replace with one entry with the non-common start and 
end values.

So:
A | 01 | 02 | 100
A | 02 | 00 | 100
B | 01 | 02 | 100
B | 02 | 00 | 200
C | 02 | 00 | 100

would produce in the end
A | 01 | 00 | 100   < combined record
B | 01 | 02 | 100   
B | 02 | 00 | 200
C | 02 | 00 | 100

To make things more complicated, the key_fields are actually a combination of 8 
fields, and there are 5 value_fields.

To make things easier, perhaps, there is only one value in the start or end 
that can be combined, in this example, that would be '02'. The table is likely 
to have around 30,000 records.

The best solution I have come up with is to combine, or not, the records in a 
temporary table, delete from the first table, then return the records from the 
temp table, but that seems wasteful.

Am I missing an obvious way to do what I want?

Thanks,
David

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

Re: [sqlite] Error : sqlite3.h:1722: parse error before string constant

[D. Richard Hipp]

On Apr 17, 2009, at 2:53 PM, manohar s wrote:

> Hi
>
> I am getting this error:
>
> sqlite3.h:1757: parse error before string constant
>
> Whenever I am including the static sqlite library in my c++ app.  
> This is
> happening with recent release 3.6.13. Earlier I never experienced any
> problem. In that line in the header file,  This function is present:
>
> void sqlite3_randomness(int N, void *P);
>
> Strangely I am able to compile successfully if I comment out this  
> line!
>
> 1) Am I doing anything wrong here?
> 2) Is it safe to comment this out?
>

No, it is not safe to comment this line out. You need to fix the  
problem, not just ameliorate the symptoms.

Perhaps you have a line such as one of these:

  #define P "something"
  #define N "something"

Somewhere in one of your header files prior to the point where you  
#include "sqlite3.h"?

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



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

[sqlite] Error : sqlite3.h:1722: parse error before string constant

[manohar s]

Hi

I am getting this error:

sqlite3.h:1757: parse error before string constant

Whenever I am including the static sqlite library in my c++ app. This is
happening with recent release 3.6.13. Earlier I never experienced any
problem. In that line in the header file,  This function is present:

void sqlite3_randomness(int N, void *P);

Strangely I am able to compile successfully if I comment out this line!

1) Am I doing anything wrong here?
2) Is it safe to comment this out?

Regards,
Manohar

-- 
hope is the only thing which keeps us all happy
http://sqlyog.wordpress.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory usage

[Marco Bambini]

Hello guys,

I need your help in order to solve a very annoying issue with sqlite  
3.6.11.

I have two opened db inside my application and when I insert 180,000  
rows inside a transaction I can see heap memory usage that exceeds  
100MB (data is written twice so I have 2 transactions inside two  
different db each one that write 180,000 rows).

I tried to use the sqlite3_soft_heap_limit without much luck(with  
SQLITE_ENABLE_MEMORY_MANAGEMENT defined) ... please note that  
transactions are started with BEGIN EXCLUSIVE.

I think that the new sqlite versions simply try to cache as much data  
as possible (memory usage was much lower with version 3.2.1 for  
example) ... so, how can keep memory usage low or to a limit similar  
to the old versions?

Thanks a lot.
-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





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

Re: [sqlite] sqlite database to xml converter??

[Wilson, Ron P]

Cruel.  Just cruel.

RW

Ron Wilson, Engineering Project Lead, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Vinnie
Sent: Friday, April 17, 2009 8:49 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite database to xml converter??


> From: candd 
> 
> Dear All!
> 
> I am new user of sqlite3
> I want to have a sample C source for a program that convert
> an sqlite 
> data base file to xml file.
> could you help me please!

Hi and welcome to the group. Your problem is very easy to solve, just rename 
your database file to have the extension ".xml" and you should be good to go.

___
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] converting sqlite db into xml

[Vinnie]

> From: candd 
> I want to have a sample C source for a program that convert
> an sqlite 
> data base file to xml file.
> could you help me please!

Thats a pretty broad and open ended question. What exactly are you trying to 
accomplish here? Do you want to produce enough XML data so that you can 
re-constitute the database at a later date?

Or did you want something like mapping the table schemas into XML templates, 
and then producing document instances that represent existing rows using that 
schema?

Or something else entirely?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can the page size cause bugs? (was Re: problems with shared cache?)

[Damien Elmes]

Just a follow-up in case anyone else runs into this.

The issue was not related to the page size, though different page
sizes could exacerbate the problem.

The problem turned out to be accidentally issuing a select in the
sqlite progress handler. My progress handler was repainting the GUI,
and occasionally a once-a-minute timer was firing in that repaint
call, which selected from the database.

It would be nice if sqlite noticed a query was being run in the
progress handler and pointed out the operator error.

Cheers,

Damien

On Fri, Apr 3, 2009 at 8:21 AM, Damien Elmes  wrote:
> Turning off the shared cache seems to have solved the problem for most
> users, but one win32 user continues to report problems. I noticed that
> in 3.6.12 the default page size is automatically calculated on
> Windows. In my application I explicitly set the page size to 4096. Can
> having a page size that doesn't match the disk geometry cause
> problems? That is the only reason I can think of that the user
> continues to report problems that aren't reproducible with the same DB
> file here. To recap, the user gets errors like:
>
> sqlalchemy.exc.IntegrityError: (IntegrityError) PRIMARY KEY must be
> unique u'update cards set priority = ? where id = ?' [[1,
> -9223199285979494924L], [1, -9221822696858457935L], [1,
> -9220362552298800344L], [1, -9218865005459903182L], [1,
> -9218053570259598995L], [1, -9217626953400592469L], [1,
> -9217257525142991358L], [2, -9217039826750418600L], [1,
> -9217011234538438799L], [1, -9216054651420921523L], [1,
> -9215471921529813571L], [3, -9215405945578177558L], [1,  list>
>
> But if the user saves the deck and sends it to me, all the ids are unique.
>
> Cheers,
>
> Damien
>
> On Wed, Mar 25, 2009 at 9:38 AM, Damien Elmes  wrote:
>> I can define the primary key column as not null if you think that will
>> help, but dumping the table reveals the ids are being assigned
>> sequential integers.
>>
>> On Tue, Mar 24, 2009 at 11:34 PM, Jim Wilcoxson  wrote:
>>> Not sure if it will make a difference, but in your trigger stuff you
>>> explicitly coded null for the primary key value.  Have you tried
>>> changing that so that you don't specify the primary key field at all?
>>> I can't remember from the previous post, but I think it was (or should
>>> be) set up as autoincrement.
>>>
>>> I think SQLite allows using multiple nulls for the primary key, but
>>> according to their docs, it is non-standard and it says something
>>> about "this may change in the future".  Maybe you are getting caught
>>> in the middle of a change that is going to occur across multiple
>>> revisions of SQLite.
>>>
>>> Jim
>>>
>>>
>>> On 3/24/09, Damien Elmes  wrote:
 Sorry, my application's files are called decks, and I unwittingly used
 the wrong terminology.

 Any ideas about the problem?

 On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald
  wrote:
>
>> However, when I ask the user to send me their deck, I find that:
>>
>> sqlite> pragma integrity_check;
>> integrity_check
>> ---
>> ok
>> sqlite> select id, count(id) from cards group by id having
>> count(id)
>>> 1;
>> sqlite>
>>
>> Any ideas?
>
> Obviously, that user is not playing with a full deck.   ;-)
>
>
> ___
> 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

>>>
>>>
>>> --
>>> Software first.  Software lasts!
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error Binding Parameter to Compiled Statement

[jonwood]

SimonDavies wrote:
> 
> Is sqlite3_reset() being called before looping around?
> 

Nope. And calling it resolved the issue. I didn't realize that was
necessary.

Thanks much!

Jonathan


-- 
View this message in context: 
http://www.nabble.com/Error-Binding-Parameter-to-Compiled-Statement-tp23099674p23100019.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] Error Binding Parameter to Compiled Statement

[Simon Davies]

2009/4/17 jonwood :
>
> Greetings,
>
> I'm getting an error compiling a parameter to a compiled statement.
> Unfortunately, since I'm using a customer wrapper class around sqlite,
> posting my code won't be much help. Here's what I'm doing:
>
> I create a compiled statement (two, actually).
>
> Then, each time through a loop, I bind an argument to the compiled
> statement, and then execute it.
>
> The first time through the loop, it works fine. But the second time through
> the loop, I get the following error when I attempt to bind the argument to
> the compiled statement.
>
> "Error binding integer parameter : library routine called out of sequence
> (error 21)"
>
> It may be something else wrong with my code but I just wanted to get a
> lucidity check on the approach I'm taking.
>
> Thanks.
> --

Is sqlite3_reset() being called before looping around?

viz:

sqlite3_prepare_v2()
while( moreWork )
{
int status = SQLITE_ROW;
sqlite3_bind_xxx()
while( SQLITE_ROW == status )
{
status = sqlite3_step()
processRowData()
}
sqlite3_reset()
}

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

[sqlite] Error Binding Parameter to Compiled Statement

[jonwood]

Greetings,

I'm getting an error compiling a parameter to a compiled statement.
Unfortunately, since I'm using a customer wrapper class around sqlite,
posting my code won't be much help. Here's what I'm doing:

I create a compiled statement (two, actually).

Then, each time through a loop, I bind an argument to the compiled
statement, and then execute it.

The first time through the loop, it works fine. But the second time through
the loop, I get the following error when I attempt to bind the argument to
the compiled statement.

"Error binding integer parameter : library routine called out of sequence
(error 21)"

It may be something else wrong with my code but I just wanted to get a
lucidity check on the approach I'm taking.

Thanks.
-- 
View this message in context: 
http://www.nabble.com/Error-Binding-Parameter-to-Compiled-Statement-tp23099674p23099674.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] Slim down join results (all fields returned)

[cmartin]

On Fri, 17 Apr 2009, flakpit wrote:

> Currently, I return any needed data like this.
>
>  select * from pubs,notes,publishers where pub_title like '%salem%'
>  and pubs.note_id=notes.note_id
>  and pubs.publisher_id=publishers.publisher_id
>
> And it works except for all fields in the matching tables being returned.

'*' means 'return all fields'. This has nothing to do with the joins, even 
if you are selecting from a single table '*' means return all fields:

select * from tableA

returns all columns from tableA.

If you only want some fields, just name them in the SELECT:

select pubs.note_id, pubs.publisher_id from ...

Chris

> Is there any way using joins (been playing but can't get them working) so
> that a statement like
>
> pubs.note_id=notes.note_id
>
> Would return only the contents of the notes.note_note field and not all the
> fields in the notes table?
> -- 
> View this message in context: 
> http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23098746.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] SQLITE_CANTOPEN error / bug?

[Filip Navara]

Proposed fix:

--- os_win.old  2009-04-09 20:41:18.0 +0200
+++ os_win.c2009-04-17 16:33:47.904710700 +0200
@@ -1317,7 +1317,7 @@
   }else{
 dwCreationDisposition = OPEN_EXISTING;
   }
-  if( flags & SQLITE_OPEN_MAIN_DB ){
+  if( !(flags & SQLITE_OPEN_EXCLUSIVE) ){
 dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE;
   }else{
 dwShareMode = 0;

Best regards,
Filip Navara

On Fri, Apr 17, 2009 at 3:30 PM, Filip Navara  wrote:
> Hi again,
>
> the bug was introduced in revision 1.573 of pager.c.
>
> Best regards,
> Filip Navara
>
> On Fri, Apr 17, 2009 at 2:26 PM, Filip Navara  wrote:
>>
>> Hello,
>>
>> today I tried to upgrade our software from SQLite version 3.6.3 to version 
>> 3.6.13 and I am hitting a race condition that I believe is a bug in the 
>> library. The library is compiled as thread-safe (and it's reproducible even 
>> with the precompiled DLL). Sometimes sqlite3_step fails with SQLITE_CANTOPEN 
>> error. I traced it with Process Monitor and each time the error happened, 
>> this could be seen in the log:
>>
>> (time / process / PID / function name / file name / result of the call / 
>> parameters / thread ID)
>> 12:27:55.1443678 PM eM Client.vshost.exe 3308 LockFile folders.dat SUCCESS 
>> Exclusive: True, Offset: 1,073,741,825, Length: 1, Fail Immediately: True 
>> 5052
>> 12:27:55.1443826 PM eM Client.vshost.exe 3308 UnlockFileSingle folders.dat 
>> SUCCESS Offset: 1,073,741,825, Length: 1 5052
>> 12:27:55.1443944 PM eM Client.vshost.exe 3308 QueryStandardInformationFile 
>> folders.dat SUCCESS AllocationSize: 32,768, EndOfFile: 21,504, 
>> NumberOfLinks: 1, DeletePending: False, Directory: False 5052
>> 12:27:55.1445976 PM eM Client.vshost.exe 3308 CreateFile folders.dat-journal 
>> SUCCESS Desired Access: Generic Read, Disposition: Open, Options: 
>> Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: 
>> None, AllocationSize: n/a, OpenResult: Opened 5052
>> 12:27:55.1447616 PM eM Client.vshost.exe 3308 CreateFile folders.dat-journal 
>> SHARING VIOLATION Desired Access: Generic Read, Disposition: Open, Options: 
>> Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: 
>> None, AllocationSize: n/a 5804
>> 12:27:55.1447930 PM eM Client.vshost.exe 3308 ReadFile folders.dat-journal 
>> SUCCESS Offset: 0, Length: 1, Priority: Normal 5052
>> 12:27:55.1448185 PM eM Client.vshost.exe 3308 UnlockFileSingle folders.dat 
>> SUCCESS Offset: 1,073,741,826, Length: 510 5804
>> 12:27:55.1448268 PM eM Client.vshost.exe 3308 CloseFile folders.dat-journal 
>> SUCCESS 5052
>> There are two threads accessing the folders.dat database at the same time, 
>> the first one (5052) succeeds, while the other one (5804) fails when 
>> checking for hot journal. Here are the stack traces of the two threads 
>> during the CreateFile calls (both against the sqlite3.c 3.6.13 amalgamation).
>>
>> Thread 5052:
>> 14 sqlite3.dll winOpen + 0x139, sqlite3.c(27454) 0x100067d6
>> 15 sqlite3.dll sqlite3OsOpen + 0x19, sqlite3.c(11655) 0x100095ed
>> 16 sqlite3.dll hasHotJournal + 0xce, sqlite3.c(33360) 0x1000ac42
>> 17 sqlite3.dll pagerSharedLock + 0x102, sqlite3.c(33499) 0x100093c0
>> 18 sqlite3.dll sqlite3PagerAcquire + 0x41, sqlite3.c(33732) 0x10008c21
>> 19 sqlite3.dll sqlite3BtreeGetPage + 0x19, sqlite3.c(37419) 0x1000c92a
>> 20 sqlite3.dll lockBtree + 0x16, sqlite3.c(38097) 0x1000c6ee
>> 21 sqlite3.dll sqlite3BtreeBeginTrans + 0xe5,sqlite3.c(38366) 0x1000c434
>> 22 sqlite3.dll sqlite3VdbeExec + 0x2e59, sqlite3.c(52191) 0x10019223
>> 23 sqlite3.dll sqlite3Step + 0x161,sqlite3.c(48601) 0x10014d9d
>> 24 sqlite3.dll sqlite3_step + 0x3d, sqlite3.c(48675) 0x10014aaf
>> Thread 5804:
>> 14 sqlite3.dll winOpen + 0x139, sqlite3.c(27454) 0x100067d6
>> 15 sqlite3.dll sqlite3OsOpen + 0x19, sqlite3.c(11655) 0x100095ed
>> 16 sqlite3.dll hasHotJournal + 0xce, sqlite3.c(33360) 0x1000ac42
>> 17 sqlite3.dll pagerSharedLock + 0x102, sqlite3.c(33499) 0x100093c0
>> 18 sqlite3.dll sqlite3PagerAcquire + 0x41, sqlite3.c(33732) 0x10008c21
>> 19 sqlite3.dll sqlite3BtreeGetMeta + 0x6e, sqlite3.c(42972) 0x1000ed7e
>> 20 sqlite3.dll sqlite3VdbeExec + 0x2f05, sqlite3.c(52230) 0x100192cf
>> 21 sqlite3.dll sqlite3Step + 0x161, sqlite3.c(48601) 0x10014d9d
>> 22 sqlite3.dll sqlite3_step + 0x3d, sqlite3.c(48675) 0x10014aaf
>> I believe it's incorrect behavior to return SQLITE_CANTOPEN to the 
>> application in that case. I'm not sure what would be the correct solution 
>> (setting sharing mode in winOpen?). Anyone can help or offer any insight?
>> Thanks,
>> Filip Navara
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slim down join results (all fields returned)

[Eric Minbiole]

> Currently, I return any needed data like this.
> 
>   select * from pubs,notes,publishers where pub_title like '%salem%' 
>   and pubs.note_id=notes.note_id
>   and pubs.publisher_id=publishers.publisher_id
> 
> And it works except for all fields in the matching tables being returned.
> 
> Is there any way using joins (been playing but can't get them working) so
> that a statement like 
> 
> pubs.note_id=notes.note_id
> 
> Would return only the contents of the notes.note_note field and not all the
> fields in the notes table?

Is this what you are looking for:

SELECT pubs.* from pubs, notes, publishers WHERE ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slim down join results (all fields returned)

[flakpit]

Currently, I return any needed data like this.

  select * from pubs,notes,publishers where pub_title like '%salem%' 
  and pubs.note_id=notes.note_id
  and pubs.publisher_id=publishers.publisher_id

And it works except for all fields in the matching tables being returned.

Is there any way using joins (been playing but can't get them working) so
that a statement like 

pubs.note_id=notes.note_id

Would return only the contents of the notes.note_note field and not all the
fields in the notes table?
-- 
View this message in context: 
http://www.nabble.com/Slim-down-join-results-%28all-fields-returned%29-tp23098746p23098746.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] sqlite3_interrupt() can interrupt query started after it's return

[Filipe AZEVEDO]

Hi,

I'm using sqlite as the sql driver of Qt 4.
I have a connection created in a thread and i call sqlite3_interrupt()  
from another one.
It's working great, perhaps i have not the behavior that say the doc :
"A call to sqlite3_interrupt() has no effect on SQL statements that  
are started after sqlite3_interrupt() returns."

I have a code like that :

void UICollection::executeQuery()
{
mContainer.sqlModel()->stopQuery(); // this member call the  
sqlite3_interrupt()
PageDatas page = mContainer.datas().pages().value( currentPageId() );
int viewId = page.views().first();
PageQueryGenerator generator(  );
QString sql = generator.generatedQuery( page.internalId(), viewId );
mContainer.sqlModel()->setQuery( sql );
}

In the generator object, there is a query that get all fields from a  
table, this query is interrupted by the previous stopQuery() call.
That's not what i want :(

Is it a know problem, or does it exists a workaround ?

PS: All my queries are "select" statements.

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

Re: [sqlite] SQLITE_CANTOPEN error / bug?

[Filip Navara]

Hi again,

the bug was introduced in revision 1.573 of pager.c.

Best regards,
Filip Navara

On Fri, Apr 17, 2009 at 2:26 PM, Filip Navara  wrote:
>
> Hello,
>
> today I tried to upgrade our software from SQLite version 3.6.3 to version 
> 3.6.13 and I am hitting a race condition that I believe is a bug in the 
> library. The library is compiled as thread-safe (and it's reproducible even 
> with the precompiled DLL). Sometimes sqlite3_step fails with SQLITE_CANTOPEN 
> error. I traced it with Process Monitor and each time the error happened, 
> this could be seen in the log:
>
> (time / process / PID / function name / file name / result of the call / 
> parameters / thread ID)
> 12:27:55.1443678 PM eM Client.vshost.exe 3308 LockFile folders.dat SUCCESS 
> Exclusive: True, Offset: 1,073,741,825, Length: 1, Fail Immediately: True 5052
> 12:27:55.1443826 PM eM Client.vshost.exe 3308 UnlockFileSingle folders.dat 
> SUCCESS Offset: 1,073,741,825, Length: 1 5052
> 12:27:55.1443944 PM eM Client.vshost.exe 3308 QueryStandardInformationFile 
> folders.dat SUCCESS AllocationSize: 32,768, EndOfFile: 21,504, NumberOfLinks: 
> 1, DeletePending: False, Directory: False 5052
> 12:27:55.1445976 PM eM Client.vshost.exe 3308 CreateFile folders.dat-journal 
> SUCCESS Desired Access: Generic Read, Disposition: Open, Options: Synchronous 
> IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: None, 
> AllocationSize: n/a, OpenResult: Opened 5052
> 12:27:55.1447616 PM eM Client.vshost.exe 3308 CreateFile folders.dat-journal 
> SHARING VIOLATION Desired Access: Generic Read, Disposition: Open, Options: 
> Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: None, 
> AllocationSize: n/a 5804
> 12:27:55.1447930 PM eM Client.vshost.exe 3308 ReadFile folders.dat-journal 
> SUCCESS Offset: 0, Length: 1, Priority: Normal 5052
> 12:27:55.1448185 PM eM Client.vshost.exe 3308 UnlockFileSingle folders.dat 
> SUCCESS Offset: 1,073,741,826, Length: 510 5804
> 12:27:55.1448268 PM eM Client.vshost.exe 3308 CloseFile folders.dat-journal 
> SUCCESS 5052
> There are two threads accessing the folders.dat database at the same time, 
> the first one (5052) succeeds, while the other one (5804) fails when checking 
> for hot journal. Here are the stack traces of the two threads during the 
> CreateFile calls (both against the sqlite3.c 3.6.13 amalgamation).
>
> Thread 5052:
> 14 sqlite3.dll winOpen + 0x139, sqlite3.c(27454) 0x100067d6
> 15 sqlite3.dll sqlite3OsOpen + 0x19, sqlite3.c(11655) 0x100095ed
> 16 sqlite3.dll hasHotJournal + 0xce, sqlite3.c(33360) 0x1000ac42
> 17 sqlite3.dll pagerSharedLock + 0x102, sqlite3.c(33499) 0x100093c0
> 18 sqlite3.dll sqlite3PagerAcquire + 0x41, sqlite3.c(33732) 0x10008c21
> 19 sqlite3.dll sqlite3BtreeGetPage + 0x19, sqlite3.c(37419) 0x1000c92a
> 20 sqlite3.dll lockBtree + 0x16, sqlite3.c(38097) 0x1000c6ee
> 21 sqlite3.dll sqlite3BtreeBeginTrans + 0xe5,sqlite3.c(38366) 0x1000c434
> 22 sqlite3.dll sqlite3VdbeExec + 0x2e59, sqlite3.c(52191) 0x10019223
> 23 sqlite3.dll sqlite3Step + 0x161,sqlite3.c(48601) 0x10014d9d
> 24 sqlite3.dll sqlite3_step + 0x3d, sqlite3.c(48675) 0x10014aaf
> Thread 5804:
> 14 sqlite3.dll winOpen + 0x139, sqlite3.c(27454) 0x100067d6
> 15 sqlite3.dll sqlite3OsOpen + 0x19, sqlite3.c(11655) 0x100095ed
> 16 sqlite3.dll hasHotJournal + 0xce, sqlite3.c(33360) 0x1000ac42
> 17 sqlite3.dll pagerSharedLock + 0x102, sqlite3.c(33499) 0x100093c0
> 18 sqlite3.dll sqlite3PagerAcquire + 0x41, sqlite3.c(33732) 0x10008c21
> 19 sqlite3.dll sqlite3BtreeGetMeta + 0x6e, sqlite3.c(42972) 0x1000ed7e
> 20 sqlite3.dll sqlite3VdbeExec + 0x2f05, sqlite3.c(52230) 0x100192cf
> 21 sqlite3.dll sqlite3Step + 0x161, sqlite3.c(48601) 0x10014d9d
> 22 sqlite3.dll sqlite3_step + 0x3d, sqlite3.c(48675) 0x10014aaf
> I believe it's incorrect behavior to return SQLITE_CANTOPEN to the 
> application in that case. I'm not sure what would be the correct solution 
> (setting sharing mode in winOpen?). Anyone can help or offer any insight?
> Thanks,
> Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit to database and/or blob size on Mac/Windows?

[Vinnie]

> From: John Machin 
> Irrespective of what people tell you and how authoritative
> they seem, I 
> would recommend that you do some simple tests:

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

Re: [sqlite] sqlite database to xml converter??

[Vinnie]

> From: candd 
> 
> Dear All!
> 
> I am new user of sqlite3
> I want to have a sample C source for a program that convert
> an sqlite 
> data base file to xml file.
> could you help me please!

Hi and welcome to the group. Your problem is very easy to solve, just rename 
your database file to have the extension ".xml" and you should be good to go.

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

[sqlite] SQLITE_CANTOPEN error / bug?

[Filip Navara]

Hello,

today I tried to upgrade our software from SQLite version 3.6.3 to version
3.6.13 and I am hitting a race condition that I believe is a bug in the
library. The library is compiled as thread-safe (and it's reproducible even
with the precompiled DLL). Sometimes sqlite3_step fails with SQLITE_CANTOPEN
error. I traced it with Process Monitor and each time the error happened,
this could be seen in the log:

(time / process / PID / function name / file name / result of the call /
parameters / thread ID)
12:27:55.1443678 PM eM Client.vshost.exe 3308 LockFile folders.dat
SUCCESS Exclusive:
True, Offset: 1,073,741,825, Length: 1, Fail Immediately: True 5052
12:27:55.1443826 PM eM Client.vshost.exe 3308 UnlockFileSingle folders.dat
SUCCESS Offset: 1,073,741,825, Length: 1 5052
12:27:55.1443944 PM eM Client.vshost.exe 3308 QueryStandardInformationFile
folders.dat SUCCESS AllocationSize: 32,768, EndOfFile: 21,504,
NumberOfLinks: 1, DeletePending: False, Directory: False 5052
12:27:55.1445976 PM eM Client.vshost.exe 3308 CreateFile folders.dat-journal
SUCCESS Desired Access: Generic Read, Disposition: Open, Options:
Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode:
None, AllocationSize: n/a, OpenResult: Opened 5052
12:27:55.1447616 PM eM Client.vshost.exe 3308 CreateFile
folders.dat-journal SHARING
VIOLATION Desired Access: Generic Read, Disposition: Open, Options:
Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode:
None, AllocationSize: n/a 5804
12:27:55.1447930 PM eM Client.vshost.exe 3308 ReadFile folders.dat-journal
SUCCESS Offset: 0, Length: 1, Priority: Normal 5052
12:27:55.1448185 PM eM Client.vshost.exe 3308 UnlockFileSingle folders.dat
SUCCESS Offset: 1,073,741,826, Length: 510 5804
12:27:55.1448268 PM eM Client.vshost.exe 3308 CloseFile folders.dat-journal
SUCCESS 5052

There are two threads accessing the folders.dat database at the same time,
the first one (5052) succeeds, while the other one (5804) fails when
checking for hot journal. Here are the stack traces of the two threads
during the CreateFile calls (both against the sqlite3.c 3.6.13
amalgamation).

Thread 5052:
14 sqlite3.dll winOpen + 0x139, sqlite3.c(27454) 0x100067d6
15 sqlite3.dll sqlite3OsOpen + 0x19, sqlite3.c(11655) 0x100095ed
16 sqlite3.dll hasHotJournal + 0xce, sqlite3.c(33360) 0x1000ac42
17 sqlite3.dll pagerSharedLock + 0x102, sqlite3.c(33499) 0x100093c0
18 sqlite3.dll sqlite3PagerAcquire + 0x41, sqlite3.c(33732) 0x10008c21
19 sqlite3.dll sqlite3BtreeGetPage + 0x19, sqlite3.c(37419) 0x1000c92a
20 sqlite3.dll lockBtree + 0x16, sqlite3.c(38097) 0x1000c6ee
21 sqlite3.dll sqlite3BtreeBeginTrans + 0xe5,sqlite3.c(38366) 0x1000c434
22 sqlite3.dll sqlite3VdbeExec + 0x2e59, sqlite3.c(52191) 0x10019223
23 sqlite3.dll sqlite3Step + 0x161,sqlite3.c(48601) 0x10014d9d
24 sqlite3.dll sqlite3_step + 0x3d, sqlite3.c(48675) 0x10014aaf

Thread 5804:
14 sqlite3.dll winOpen + 0x139, sqlite3.c(27454) 0x100067d6
15 sqlite3.dll sqlite3OsOpen + 0x19, sqlite3.c(11655) 0x100095ed
16 sqlite3.dll hasHotJournal + 0xce, sqlite3.c(33360) 0x1000ac42
17 sqlite3.dll pagerSharedLock + 0x102, sqlite3.c(33499) 0x100093c0
18 sqlite3.dll sqlite3PagerAcquire + 0x41, sqlite3.c(33732) 0x10008c21
19 sqlite3.dll sqlite3BtreeGetMeta + 0x6e, sqlite3.c(42972) 0x1000ed7e
20 sqlite3.dll sqlite3VdbeExec + 0x2f05, sqlite3.c(52230) 0x100192cf
21 sqlite3.dll sqlite3Step + 0x161, sqlite3.c(48601) 0x10014d9d
22 sqlite3.dll sqlite3_step + 0x3d, sqlite3.c(48675) 0x10014aaf

I believe it's incorrect behavior to return SQLITE_CANTOPEN to the
application in that case. I'm not sure what would be the correct solution
(setting sharing mode in winOpen?). Anyone can help or offer any insight?

Thanks,
Filip Navara
"Time of Day","Process Name","PID","Operation","Path","Result","Detail","TID"
"12:27:55.1394789 PM","eM 
Client.vshost.exe","3308","LockFile","C:\Users\Filip\AppData\Roaming\eM 
Client\folders.dat","SUCCESS","Exclusive: True, Offset: 1,073,741,824, Length: 
1, Fail Immediately: True","5052"
"12:27:55.1395073 PM","eM 
Client.vshost.exe","3308","LockFile","C:\Users\Filip\AppData\Roaming\eM 
Client\folders.dat","SUCCESS","Exclusive: False, Offset: 1,073,741,826, Length: 
510, Fail Immediately: True","5052"
"12:27:55.1395216 PM","eM 
Client.vshost.exe","3308","UnlockFileSingle","C:\Users\Filip\AppData\Roaming\eM 
Client\folders.dat","SUCCESS","Offset: 1,073,741,824, Length: 1","5052"
"12:27:55.1397821 PM","eM 
Client.vshost.exe","3308","QueryOpen","C:\Users\Filip\AppData\Roaming\eM 
Client\folders.dat-journal","FAST IO DISALLOWED","","5052"
"12:27:55.1399142 PM","eM 
Client.vshost.exe","3308","CreateFile","C:\Users\Filip\AppData\Roaming\eM 
Client\folders.dat-journal","SUCCESS","Desired Access: Read Attributes, 
Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: 
Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened","5052"
"12:27:55.1399563 PM","eM 

[sqlite] sqlite database to xml converter??

[candd]

Dear All!

I am new user of sqlite3
I want to have a sample C source for a program that convert an sqlite 
data base file to xml file.
could you help me please!

Many thanks!

--
This mail was scanned by BitDefender

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