[sqlite] How to Embed SQLite in VC++

[Mahendra Batra]

Someone, plz tell me how could i embed SQLite. I included sqlite.h but getting 
unresolved errors i.e the definition of functions like sqlite3_open(..) and 
sqlite3_exec(..) can not be found.
Please favor me as soon as possible.
 
regards,
Mahendra Batra


-
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

[sqlite] How to Embed SQLite in VC++

[Mahendra Batra]

Someone, plz tell me how could i embed SQLite. I included sqlite.h but getting 
unresolved errors i.e the definition of functions like sqlite3_open(..) and 
sqlite3_exec(..) can not be found.
Please favor me as soon as possible.
 
regards,
Mahendra Batra


-
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

Re: [sqlite] Problem storing integers

[Nuno Lucas]

[15-04-2005 5:47, Gé Weijers escreveu]
Same thing on Mac OSX. Must be a platform-independent issue.
Same thing on v3.2.1 on linux (gentoo ebuild).
It looks like a bug...
~Nuno Lucas
Richard Boulton wrote:
CREATE TABLE test (a INTEGER);
INSERT INTO test VALUES(4294967295);
INSERT INTO test VALUES(1099511627775);
INSERT INTO test VALUES(281474976710655);
INSERT INTO test VALUES(72057594037927935);
SELECT * FROM test;
Results in:
4294967295
1099511627775
-1
72057594037927935
i.e. 281474976710655 is stored as -1

Re: [sqlite] Problem storing integers

[Gé Weijers]

Same thing on Mac OSX. Must be a platform-independent issue.

Gé

Richard Boulton wrote:

>Hi,
>
>I'm running the latest sqlite 3.2.1 command line tool on Windows XP and have
>noticed that I don't seem to be able to store 48bit integers anymore :-S
>
>CREATE TABLE test (a INTEGER);
>INSERT INTO test VALUES(4294967295);
>INSERT INTO test VALUES(1099511627775);
>INSERT INTO test VALUES(281474976710655);
>INSERT INTO test VALUES(72057594037927935);
>SELECT * FROM test;
>
>Results in:
>
>4294967295
>1099511627775
>-1
>72057594037927935
>
>i.e. 281474976710655 is stored as -1
>
>Regards,
>Rich
>
>  
>

Re: [sqlite] Copying a table between databases

[Dennis Volodomanov]

Thank you Derrell and Cory,
I can create triggers when I create the database file and I'm not using 
indexes in this particular program, so that seems like the way to go. 
Wouldn't a function like sqlite3_copytable(sqlite3 *pDest, sqlite3 *pSrc, 
...) be a good idea? Or it would make SQLite more complex and thus go 
against the main principle?

Just wondering... :-)
  Dennis
// MCP, MCSD
// ASP Developer Member
// Software for animal shelters!
// www.smartpethealth.com
// www.amazingfiles.com
- Original Message - 
From: "Cory Nelson" <[EMAIL PROTECTED]>
To: ; <[EMAIL PROTECTED]>
Sent: Friday, April 15, 2005 12:33 AM
Subject: Re: [sqlite] Copying a table between databases

field types are retained but indexes and triggers won't be copied
On 4/14/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
"Dennis Volodomanov" <[EMAIL PROTECTED]> writes:
> So, "CREATE TABLE AS" will basically duplicate the table that I have (if
> I say for example, "CREATE TABLE AS myNewTable AS SELECT * FROM
> myOldTable")? Sounds good if that's true :-)
You have one too many "AS" in your example.  (No "AS" between CREATE TABLE 
and
the table name.  Try it this way:

  CREATE TABLE myNewTable AS SELECT * FROM myOldTable;
In sqlite 2.8.* you lose the field types (which were just comments in the 
2.8
series anyway).  I suspect that in 3.0.* the field types are retained, but 
I
haven't tested it.  You can verify with something like this:

% sqlite :memory:
SQLite version 2.8.16
Enter ".help" for instructions
sqlite> create table x (i integer);
sqlite> create table y as select * from x;
sqlite> .dump
BEGIN TRANSACTION;
create table x (i integer);
CREATE TABLE y(i);
COMMIT;
sqlite>
Derrell

--
Cory Nelson
http://www.int64.org

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 14-Apr-05

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 14-Apr-05

Re: [sqlite] db not writable

[tom]

Thanks Dan,

That's all it was.  Knock on wood (tap head).

On 4/14/05, Dan Kennedy <[EMAIL PROTECTED]> wrote:
> SQLite needs write permission to the directory as well. It could be
> that.
>

Re: [sqlite] Multiple Tables on one Flat File

[Jay Sprenkle]

The sqlite_master table keeps the sql used to create the table automatically.
check out 
select * from sqlite_master;

On 4/14/05, Eric Bohlman <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > I ended up creating a %dbh hash with $table as the index along with one
> > Operating System file for each table.
> > I was hoping there was a way not to create so many Operating System files
> > because of the extra Administration they require.
> > can you think of any way around this?
> > I read all my data from a log and I am creating multiple Tables based on
> > the content of the data in the log.
> 
> Each time you create a table, squirrel away the text of the INSERT
> statement (and any other table-related queries) by storing it in a hash
> keyed by the table name or the like.  Then, when you're done creating
> the tables, loop over the stored queries creating prepared sth's.
> 


-- 
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net

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

Re: [sqlite] Multiple Tables on one Flat File

[Eric Bohlman]

[EMAIL PROTECTED] wrote:
I ended up creating a %dbh hash with $table as the index along with one
Operating System file for each table.
I was hoping there was a way not to create so many Operating System files
because of the extra Administration they require.
can you think of any way around this?
I read all my data from a log and I am creating multiple Tables based on
the content of the data in the log.
Each time you create a table, squirrel away the text of the INSERT 
statement (and any other table-related queries) by storing it in a hash 
keyed by the table name or the like.  Then, when you're done creating 
the tables, loop over the stored queries creating prepared sth's.

[sqlite] Problem storing integers

[Richard Boulton]

Hi,

I'm running the latest sqlite 3.2.1 command line tool on Windows XP and have
noticed that I don't seem to be able to store 48bit integers anymore :-S

CREATE TABLE test (a INTEGER);
INSERT INTO test VALUES(4294967295);
INSERT INTO test VALUES(1099511627775);
INSERT INTO test VALUES(281474976710655);
INSERT INTO test VALUES(72057594037927935);
SELECT * FROM test;

Results in:

4294967295
1099511627775
-1
72057594037927935

i.e. 281474976710655 is stored as -1

Regards,
Rich

AW: [sqlite] bug in ORDER BY ?

[Christian Schwarz]

Does "select * from mactor order by id desc limit 1" and
"select * from mactor order by id limit 1" not work?

Greetings, Christian

Re: [sqlite] bug in ORDER BY ?

[Thomas Steffen]

On 4/14/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

> EXPLAIN is your friend.  As can be seen by EXPLAINing each query (see below),
> there are fewer instructions involved in the one with the subquery, and no
> sorts or loops as are done in the initial method.

Well, not everybody is a specialist on SQLite bytecode, I guess. It
looks a lot like RISC code to me, which means that it is mostly
illegible :-)

Anyway, I have compared statements with identical functions, and my
result is rather different. I don't know about loops, and I can't do a
real performance test at the moment, but at least the bytecode is a
lot longer for the subquery (as I would expect). I did:

sqlite> CREATE TABLE test (id INTEGER, addr CHAR(10), rest CHAR(20));
sqlite> CREATE INDEX testindex ON test (addr,id);
sqlite> EXPLAIN SELECT rest FROM test WHERE id>100 AND addr='1234'
ORDER BY id LIMIT 1;
...
39
sqlite> EXPLAIN SELECT rest FROM test WHERE id=(SELECT min(id) FROM
test WHERE id>100 AND addr='1234') AND addr='1234';
...
72

So which one is more efficient? Adding LIMIT 1 to the second one adds
5 more bytecodes (weird?).

That having said SQLite seems to match the performance of MySQL pretty
well in this task. I am positively surprised.

Thomas

 (Note that I've changed the
> maximum value to what fits in a signed 32-bit field since I'm doing this with
> 2.8.16.  You could try a similar experiment with 3.0.x.  Actually, it looks
> like you don't even need the WHERE clause in the original query, and I've
> tested that modification at the end of the EXPLAINation below, as well.
>
> > What if id is not unique, and I may have rows with identical id?
>
> The original poster had 'id' as INTEGER PRIMARY KEY so that wouldn't be
> possible.  If it is possible, then you'd have to decide what you wanted to do
> with multiple results.
>
> % sqlite :memory:
> SQLite version 2.8.16
> Enter ".help" for instructions
> sqlite> CREATE TABLE Mactor
>...> (
>...>   id INTEGER PRIMARY KEY,
>...>   name TEXT,
>...>   -- any other fields
>...>   comment TEXT
>...> );
> sqlite> explain SELECT * FROM Mactor WHERE id < 2147483647 ORDER BY id 
> DESC LIMIT 1;
> addr|opcode|p1|p2|p3
> 0|ColumnName|0|0|id
> 1|ColumnName|1|0|name
> 2|ColumnName|2|1|comment
> 3|Integer|-1|0|
> 4|MemStore|0|1|
> 5|ColumnName|3|0|INTEGER
> 6|ColumnName|4|0|TEXT
> 7|ColumnName|5|0|TEXT
> 8|Integer|0|0|
> 9|OpenRead|0|3|Mactor
> 10|VerifyCookie|0|31|
> 11|Rewind|0|25|
> 12|Integer|2147483647|0|2147483647
> 13|MemStore|1|1|
> 14|Recno|0|0|
> 15|MemLoad|1|0|
> 16|Ge|0|25|
> 17|Recno|0|0|
> 18|Column|0|1|
> 19|Column|0|2|
> 20|SortMakeRec|3|0|
> 21|Recno|0|0|
> 22|SortMakeKey|1|0|-
> 23|SortPut|0|0|
> 24|Next|0|14|
> 25|Close|0|0|
> 26|Sort|0|0|
> 27|SortNext|0|32|
> 28|MemIncr|0|31|
> 29|SortCallback|3|0|
> 30|Goto|0|27|
> 31|Pop|1|0|
> 32|SortReset|0|0|
> 33|Halt|0|0|
> sqlite> explain SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM 
> Mactor);
> addr|opcode|p1|p2|p3
> 0|VerifyCookie|0|31|
> 1|Integer|0|0|
> 2|OpenRead|1|3|Mactor
> 3|Last|1|0|
> 4|Recno|1|0|
> 5|MemStore|0|1|
> 6|Goto|0|7|
> 7|Close|1|0|
> 8|ColumnName|0|0|id
> 9|ColumnName|1|0|name
> 10|ColumnName|2|1|comment
> 11|ColumnName|3|0|INTEGER
> 12|ColumnName|4|0|TEXT
> 13|ColumnName|5|0|TEXT
> 14|Integer|0|0|
> 15|OpenRead|0|3|Mactor
> 16|MemLoad|0|0|
> 17|MustBeInt|1|23|
> 18|NotExists|0|23|
> 19|Recno|0|0|
> 20|Column|0|1|
> 21|Column|0|2|
> 22|Callback|3|0|
> 23|Close|0|0|
> 24|Halt|0|0|
> sqlite>explain SELECT * FROM Mactor ORDER BY id DESC LIMIT 1;
> addr|opcode|p1|p2|p3
> 0|ColumnName|0|0|id
> 1|ColumnName|1|0|name
> 2|ColumnName|2|1|comment
> 3|Integer|-1|0|
> 4|MemStore|0|1|
> 5|ColumnName|3|0|INTEGER
> 6|ColumnName|4|0|TEXT
> 7|ColumnName|5|0|TEXT
> 8|Integer|0|0|
> 9|OpenRead|0|3|Mactor
> 10|VerifyCookie|0|31|
> 11|Rewind|0|20|
> 12|Recno|0|0|
> 13|Column|0|1|
> 14|Column|0|2|
> 15|SortMakeRec|3|0|
> 16|Recno|0|0|
> 17|SortMakeKey|1|0|-
> 18|SortPut|0|0|
> 19|Next|0|12|
> 20|Close|0|0|
> 21|Sort|0|0|
> 22|SortNext|0|27|
> 23|MemIncr|0|26|
> 24|SortCallback|3|0|
> 25|Goto|0|22|
> 26|Pop|1|0|
> 27|SortReset|0|0|
> 28|Halt|0|0|
> sqlite>
>

Re: [sqlite] bug in ORDER BY ?

[Derrell . Lipman]

Thomas Steffen <[EMAIL PROTECTED]> writes:

> On 4/14/05, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
>> How about these:
>> 
>>   SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor);
>>   SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor);
>
> I am working on a similar problem at the moment, but unless I missed
> something, ORDER BY  id LIMIT 1 works fine for me. Is there any
> benefit of one formulation against the other? Is the nested SELECT
> less efficient?  Or are they identical in bytecode? 

EXPLAIN is your friend.  As can be seen by EXPLAINing each query (see below),
there are fewer instructions involved in the one with the subquery, and no
sorts or loops as are done in the initial method.  (Note that I've changed the
maximum value to what fits in a signed 32-bit field since I'm doing this with
2.8.16.  You could try a similar experiment with 3.0.x.  Actually, it looks
like you don't even need the WHERE clause in the original query, and I've
tested that modification at the end of the EXPLAINation below, as well.

> What if id is not unique, and I may have rows with identical id?

The original poster had 'id' as INTEGER PRIMARY KEY so that wouldn't be
possible.  If it is possible, then you'd have to decide what you wanted to do
with multiple results.

% sqlite :memory:
SQLite version 2.8.16
Enter ".help" for instructions
sqlite> CREATE TABLE Mactor
   ...> (
   ...>   id INTEGER PRIMARY KEY,
   ...>   name TEXT,
   ...>   -- any other fields
   ...>   comment TEXT
   ...> );
sqlite> explain SELECT * FROM Mactor WHERE id < 2147483647 ORDER BY id DESC 
LIMIT 1;
addr|opcode|p1|p2|p3
0|ColumnName|0|0|id
1|ColumnName|1|0|name
2|ColumnName|2|1|comment
3|Integer|-1|0|
4|MemStore|0|1|
5|ColumnName|3|0|INTEGER
6|ColumnName|4|0|TEXT
7|ColumnName|5|0|TEXT
8|Integer|0|0|
9|OpenRead|0|3|Mactor
10|VerifyCookie|0|31|
11|Rewind|0|25|
12|Integer|2147483647|0|2147483647
13|MemStore|1|1|
14|Recno|0|0|
15|MemLoad|1|0|
16|Ge|0|25|
17|Recno|0|0|
18|Column|0|1|
19|Column|0|2|
20|SortMakeRec|3|0|
21|Recno|0|0|
22|SortMakeKey|1|0|-
23|SortPut|0|0|
24|Next|0|14|
25|Close|0|0|
26|Sort|0|0|
27|SortNext|0|32|
28|MemIncr|0|31|
29|SortCallback|3|0|
30|Goto|0|27|
31|Pop|1|0|
32|SortReset|0|0|
33|Halt|0|0|
sqlite> explain SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM 
Mactor);
addr|opcode|p1|p2|p3
0|VerifyCookie|0|31|
1|Integer|0|0|
2|OpenRead|1|3|Mactor
3|Last|1|0|
4|Recno|1|0|
5|MemStore|0|1|
6|Goto|0|7|
7|Close|1|0|
8|ColumnName|0|0|id
9|ColumnName|1|0|name
10|ColumnName|2|1|comment
11|ColumnName|3|0|INTEGER
12|ColumnName|4|0|TEXT
13|ColumnName|5|0|TEXT
14|Integer|0|0|
15|OpenRead|0|3|Mactor
16|MemLoad|0|0|
17|MustBeInt|1|23|
18|NotExists|0|23|
19|Recno|0|0|
20|Column|0|1|
21|Column|0|2|
22|Callback|3|0|
23|Close|0|0|
24|Halt|0|0|
sqlite>explain SELECT * FROM Mactor ORDER BY id DESC LIMIT 1;
addr|opcode|p1|p2|p3
0|ColumnName|0|0|id
1|ColumnName|1|0|name
2|ColumnName|2|1|comment
3|Integer|-1|0|
4|MemStore|0|1|
5|ColumnName|3|0|INTEGER
6|ColumnName|4|0|TEXT
7|ColumnName|5|0|TEXT
8|Integer|0|0|
9|OpenRead|0|3|Mactor
10|VerifyCookie|0|31|
11|Rewind|0|20|
12|Recno|0|0|
13|Column|0|1|
14|Column|0|2|
15|SortMakeRec|3|0|
16|Recno|0|0|
17|SortMakeKey|1|0|-
18|SortPut|0|0|
19|Next|0|12|
20|Close|0|0|
21|Sort|0|0|
22|SortNext|0|27|
23|MemIncr|0|26|
24|SortCallback|3|0|
25|Goto|0|22|
26|Pop|1|0|
27|SortReset|0|0|
28|Halt|0|0|
sqlite> 

Re: [sqlite] Multiple Tables on one Flat File

[John LeSueur]

[EMAIL PROTECTED] wrote:

Eric:
thank you for your reply.
I ended up creating a %dbh hash with $table as the index along with one
Operating System file for each table.
I was hoping there was a way not to create so many Operating System files
because of the extra Administration they require.
can you think of any way around this?
I read all my data from a log and I am creating multiple Tables based on
the content of the data in the log.
Regards,
[EMAIL PROTECTED]
NCCI
Boca Raton, Florida
561.893.2415
greetings / avec mes meilleures salutations / Cordialmente
mit freundlichen Grüßen / Med vänlig hälsning
 

If you look at Dr Hipp's mail, the problem is actually the order of your 
operations. You need to drop all your prepared statements and reprepare 
them after you do any CREATE, ALTER, DROP or VACUUM statements.

John

Re: [sqlite] api questions about data lifetimes

[Austin Gilbert]

Not to discourage you from rolling your own, but what about CppSQLite??
www.codeproject.com/database/CppSQLite.asp
(the site is down at the moment, here is the google cache:  
http://64.233.167.104/search?q=cache:qULjzgqRl0oJ:www.codeproject.com/ 
database/CppSQLite.asp+CppSQLite=en=safari )

It has been posted in the past also  
(http://www.mail-archive.com/sqlite-users@sqlite.org/msg02067.html)

Austin
On Apr 14, 2005, at 11:21 AM, Brian Swetland wrote:
I'm wrapping sqlite3 with a lightweight little C++ API to allow me to
use it more easily from C++ code and I have a couple questions about
the lifespan of data passed into and received from the sqlite3 API:
1. Will the const char* returned by sqlite3_column_name() persist
   until the statement is finalized, or are there situations under
   which sqlite could free it before then?
2. Will SQLITE_STATIC data bound to text or blob parameters (using
   the sqlite3_bind_text/blob() functions) ever be touched by sqlite
   outside of calls to sqlite3_step()?
3. Is my belief that text or blob data returned by
   sqlite3_column_text/blob() does not need to be free()'d and is
   only valid until the next sqlite3_step/reset/finalize() on that
   statement correct?
Thanks,
Brian

[sqlite] Question regarding Memory Tables

[Brandon, Nicholas]

Hello,

I was thinking about using memory tables for short term data and was
wondering whether SQLite does anything to stop the OS paging the memory to
disk?

I know there is a POSIX function "mlock" that stop memory being paged to
disk but I believe the program has to run as root/admin since this technique
can be used to exhaust all the memory on a computer.

Any thoughts?

Many Thanks
Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.

Re: [sqlite] bug in ORDER BY ?

[Thomas Steffen]

On 4/14/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> How about these:
> 
>   SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor);
>   SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor);

I am working on a similar problem at the moment, but unless I missed
something, ORDER BY  id LIMIT 1 works fine for me. Is there any
benefit of one formulation against the other? Is the nested SELECT
less efficient? Or are they identical in bytecode? What if id is not
unique, and I may have rows with identical id?

And can this be generalised for other databases?

Yours,
Thomas

[sqlite] api questions about data lifetimes

[Brian Swetland]

I'm wrapping sqlite3 with a lightweight little C++ API to allow me to
use it more easily from C++ code and I have a couple questions about
the lifespan of data passed into and received from the sqlite3 API:

1. Will the const char* returned by sqlite3_column_name() persist
   until the statement is finalized, or are there situations under
   which sqlite could free it before then?

2. Will SQLITE_STATIC data bound to text or blob parameters (using
   the sqlite3_bind_text/blob() functions) ever be touched by sqlite
   outside of calls to sqlite3_step()?

3. Is my belief that text or blob data returned by 
   sqlite3_column_text/blob() does not need to be free()'d and is
   only valid until the next sqlite3_step/reset/finalize() on that
   statement correct?
   
Thanks,

Brian

Re: [sqlite] bug in ORDER BY ?

[Derrell . Lipman]

"Miha Vrhovnik"<[EMAIL PROTECTED]> writes:

> SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1;
>
> where 9223372036854775807 is Maximum value of signed Int64.
>
> P.S. If anybody has better Idea of how to get the last/first row (the one
> with highest/lowest ID) then comments are welcome.
>

How about these:

  SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor);
  SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor);

Derrell

Re: [sqlite] bug in ORDER BY ?

[Xavier Aguila]

try
SELECT * FROM Mactor WHERE id=(Select max(id) from Mactor); //with this you get 
last id.
SELECT * FROM Mactor WHERE id=(Select min(id) from Mactor); //with this you get 
first id.
Xavier
Miha Vrhovnik wrote:
Hi,
sqlite dll is 3.2.1
I have the folowing query:
SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1;
where 9223372036854775807 is Maximum value of signed Int64.
Table is defined as:
CREATE TABLE Mactor (
id INTEGER PRIMARY KEY, 
name TEXT, 
birthName TEXT, 
birthday DATE, 
gender INTEGER, 
idCountry INTEGER, 
idProvince INTEGER, 
imdb TEXT, 
url TEXT, 
otherWork TEXT, 
biographiy TEXT, 
comment TEXT, 
pictures TEXT, 
custom TEXT);

There are two records in table one with id = 1 and other with id = 2
The query retuns an empty "dataset" BUT it works as soon as I remove ORDER BY 
clause.
P.S. If anybody has better Idea of how to get the last/first row (the one with 
highest/lowest ID) then comments are welcome.
Regards,
Miha 

+*+
It's time to get rid of your current e-mail client ...
... and start using si.Mail.
It's small & free. ( http://simail.sourceforge.net/ )
+*+
 

[sqlite] bug in ORDER BY ?

[Miha Vrhovnik]

Hi,

sqlite dll is 3.2.1

I have the folowing query:

SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1;

where 9223372036854775807 is Maximum value of signed Int64.

Table is defined as:

CREATE TABLE Mactor (
id INTEGER PRIMARY KEY,
name TEXT,
birthName TEXT,
birthday DATE,
gender INTEGER,
idCountry INTEGER,
idProvince INTEGER,
imdb TEXT,
url TEXT,
otherWork TEXT,
biographiy TEXT,
comment TEXT,
pictures TEXT,
custom TEXT);

There are two records in table one with id = 1 and other with id = 2

The query retuns an empty "dataset" BUT it works as soon as I remove ORDER BY 
clause.

P.S. If anybody has better Idea of how to get the last/first row (the one with 
highest/lowest ID) then comments are welcome.

Regards,
Miha

+*+
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )
+*+

RE: [sqlite] Indexing problem

[Thomas Briggs]

   Without having seen the EXPLAIN output for the query both with and
without the indexes present: the indexes you've created don't really
support your query very well.  Of the six indexes that you've created, I
believe that only one can be used, so I'd speculate that the cause of
the slowdown is the back-and-forth nature of looking up data in the
index, then the table, then the index, etc.  Creating a compound index
of (SyntheticOutboundID, SyntheticInboundID, Season) on the YM203 table
would make a huge difference in how this query will perform, I think.

   -Tom

> -Original Message-
> From: John Proudlove [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 14, 2005 10:26 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Indexing problem
> 
> Hello,
> 
> Can anyone shed light on the following problem,
> experienced with the SQLite command line utility
> (v3.0.8) on Solaris/SPARC?
> 
> The query below hangs (fails to complete within 5
> minutes) using the indices shown, but after removing
> the index on the Season column (used in the WHERE
> condition), it completes within seconds.
> 
> Regards,
> John
> 
> SELECT
> count(*)
> FROM
> YM207, YM203
> WHERE
> YM203.Season = "W2004"
> AND YM207.OutboundFlightSeriesID
> = YM203.SyntheticOutboundFlightSeriesID
> AND YM207.InboundFlightSeriesID
> = YM203.SyntheticInboundFlightSeriesID
> ;
> 
> CREATE INDEX YM203_SSID ON YM203 ( SyntheticSellingID
> );
> CREATE UNIQUE INDEX YM203_index ON YM203 (
> SyntheticPackageID );
> CREATE INDEX YM203_index_2 ON YM203 (
> SyntheticOutboundFlightSeriesID );
> CREATE INDEX YM203_index_3 ON YM203 (
> SyntheticInboundFlightSeriesID );
> CREATE INDEX YM203_index_5 ON YM203 ( SubProductCode
> );
> CREATE INDEX YM203_index_4 ON YM203 ( Season );
> 
> 
> Send instant messages to your online friends 
> http://uk.messenger.yahoo.com 
> 

Re: [sqlite] Copying a table between databases

[Cory Nelson]

field types are retained but indexes and triggers won't be copied

On 4/14/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> "Dennis Volodomanov" <[EMAIL PROTECTED]> writes:
> 
> > So, "CREATE TABLE AS" will basically duplicate the table that I have (if
> > I say for example, "CREATE TABLE AS myNewTable AS SELECT * FROM
> > myOldTable")? Sounds good if that's true :-)
> 
> You have one too many "AS" in your example.  (No "AS" between CREATE TABLE and
> the table name.  Try it this way:
> 
>   CREATE TABLE myNewTable AS SELECT * FROM myOldTable;
> 
> In sqlite 2.8.* you lose the field types (which were just comments in the 2.8
> series anyway).  I suspect that in 3.0.* the field types are retained, but I
> haven't tested it.  You can verify with something like this:
> 
> % sqlite :memory:
> SQLite version 2.8.16
> Enter ".help" for instructions
> sqlite> create table x (i integer);
> sqlite> create table y as select * from x;
> sqlite> .dump
> BEGIN TRANSACTION;
> create table x (i integer);
> CREATE TABLE y(i);
> COMMIT;
> sqlite>
> 
> Derrell
> 


-- 
Cory Nelson
http://www.int64.org

[sqlite] Indexing problem

[John Proudlove]

Hello,

Can anyone shed light on the following problem,
experienced with the SQLite command line utility
(v3.0.8) on Solaris/SPARC?

The query below hangs (fails to complete within 5
minutes) using the indices shown, but after removing
the index on the Season column (used in the WHERE
condition), it completes within seconds.

Regards,
John

SELECT
count(*)
FROM
YM207, YM203
WHERE
YM203.Season = "W2004"
AND YM207.OutboundFlightSeriesID
= YM203.SyntheticOutboundFlightSeriesID
AND YM207.InboundFlightSeriesID
= YM203.SyntheticInboundFlightSeriesID
;

CREATE INDEX YM203_SSID ON YM203 ( SyntheticSellingID
);
CREATE UNIQUE INDEX YM203_index ON YM203 (
SyntheticPackageID );
CREATE INDEX YM203_index_2 ON YM203 (
SyntheticOutboundFlightSeriesID );
CREATE INDEX YM203_index_3 ON YM203 (
SyntheticInboundFlightSeriesID );
CREATE INDEX YM203_index_5 ON YM203 ( SubProductCode
);
CREATE INDEX YM203_index_4 ON YM203 ( Season );


Send instant messages to your online friends http://uk.messenger.yahoo.com 

Re: [sqlite] Multiple Tables on one Flat File

[Uriel_Carrasquilla]

Eric:
thank you for your reply.
I ended up creating a %dbh hash with $table as the index along with one
Operating System file for each table.
I was hoping there was a way not to create so many Operating System files
because of the extra Administration they require.
can you think of any way around this?
I read all my data from a log and I am creating multiple Tables based on
the content of the data in the log.

Regards,

[EMAIL PROTECTED]
NCCI
Boca Raton, Florida
561.893.2415
greetings / avec mes meilleures salutations / Cordialmente
mit freundlichen Grüßen / Med vänlig hälsning




  Eric Bohlman  

  <[EMAIL PROTECTED]To:   
sqlite-users@sqlite.org
  lobal.net>   cc:  

   Subject:  Re: [sqlite] Multiple 
Tables on one Flat File  
  04/13/2005 06:59  

  PM

  Please respond to 

  sqlite-users  









[EMAIL PROTECTED] wrote:

> I am running into a situation that does not make sense.
> I have allocated a flat file under the Operating System as follows
(notice
> that autocommit is off):
>   $dbh = DBI->connect('dbi:SQLite:' . $dbms_file , "", "",
>   { RaiseError => 1,AutoCommit => 0 });
> Then, I create multiple tables in a loop with with the sequence:
> 1) CREATE TABLE 
> 2) $sql = SQL::Abstract->new;
> 3) ($sql,@bind) = $sql->insert($table,\%rec);
> 4) $sth{$table} = $dbh->prepare($sql);

Your problem is that each new CREATE invalidates all the statement
handles you previously prepared.

> Finally, right after the loop, i do the following for one table:
> 1) $#bind = -1;
> 2) foreach $item (sort keys %rec)
> {
>   push(@bind, $rec{$item});
> }
> 3) $sth{$table} -> execute(@bind);  # this is line 697 in the error
message

It's not your problem, but that's rather bizarre Perl; use the slice, Luke:

@bind = @rec{sort keys %rec);

> 4) Sometime later, I do the $dbh->commit().
>
> When I run my loop with only one table, it works.  When I run the loop
with
> multiple tables, it fails on the following message:
> DBD::SQLite::st execute failed: database schema has changed(1) at
dbdimp.c
> line 389 at ./sarparsed.pl line 697.
> DBD::SQLite::st execute failed: database schema has changed(1) at
dbdimp.c
> line 389 at ./sarparsed.pl line 697.

It works fine with only one table because your only active statement
handle was prepared *after* you stopped changing the schema.

>
> I suspect that I will need to have multiple $dbh (as in $dbh{$table}) but
I
> don't want to connect to multiple Operating System files, just one.
> am I on the right track?

Nope.  I confirmed the problem by creating the following test case:

#!perl
use strict;
use warnings;
use DBI;

unlink 'dummy.db';
my $dbh=DBI->connect(
   'dbi:SQLite:dummy.db',"","",{RaiseError=>1,AutoCommit=>0});
$dbh->do('create table t1 (a integer,b integer)');
my $sth1=$dbh->prepare('insert into t1 values (?,?)');
$dbh->do('create table t2 (c integer,d integer)');
my $sth2=$dbh->prepare('insert into t2 values (?,?)');
$sth1->execute(1,2); # this fails because $sth1 is now stale
$sth2->execute(3,4); # this succeeds because $sth2 is still fresh
$dbh->commit();
$dbh->disconnect();

This will fail, but moving the preparation of $sth1 to after the second
CREATE will make it succeed.





The information contained in this e-mail message is intended only for 
the personal and confidential use of the recipient(s) named above. This 
message may be an attorney-client communication and/or work product and 
as such is privileged and confidential. If the reader of this message 
is not the intended recipient or an agent responsible for delivering it 
to the intended recipient, you are hereby notified that you have 
received this document in error and that any review, dissemination, 
distribution, or copying of this message is strictly prohibited. If you 
have 

Re: [sqlite] Copying a table between databases

[Derrell . Lipman]

"Dennis Volodomanov" <[EMAIL PROTECTED]> writes:

> So, "CREATE TABLE AS" will basically duplicate the table that I have (if
> I say for example, "CREATE TABLE AS myNewTable AS SELECT * FROM
> myOldTable")? Sounds good if that's true :-)

You have one too many "AS" in your example.  (No "AS" between CREATE TABLE and
the table name.  Try it this way:

  CREATE TABLE myNewTable AS SELECT * FROM myOldTable;

In sqlite 2.8.* you lose the field types (which were just comments in the 2.8
series anyway).  I suspect that in 3.0.* the field types are retained, but I
haven't tested it.  You can verify with something like this:

% sqlite :memory:
SQLite version 2.8.16
Enter ".help" for instructions
sqlite> create table x (i integer);
sqlite> create table y as select * from x;
sqlite> .dump
BEGIN TRANSACTION;
create table x (i integer);
CREATE TABLE y(i);
COMMIT;
sqlite> 

Derrell

RE: [sqlite] Some Functional Questions

[Griggs, Donald]

Hi Ken,

I don't know enough to respond to all of your questions, but maybe the
following will help for a few of them.


Locking and concurrancy info:
http://www.sqlite.org/lockingv3.html

Date/timestamp variables:
http://www.sqlite.org/lang_createtable.html

Date/time manipulation
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions



Donald Griggs

Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.



-Original Message-
From: Ken & Deb Allen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 13, 2005 8:59 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Some Functional Questions


I have been experimenting with SQLITE for a little over a week now, and 
I must say that I am fairly impressed with many of its capabilities. I 
have been experimenting with performance from several aspects, and the 
numbers are quite respectable. I have several years experience with 
Sybase, Access, Oracle, SQL Server and other relational databases, so I 
am fairly comfortable with evaluation features.

While I intend to write some more programs to test more facilities, I 
do have some questions that others may be able to answer, or provide 
comments, and thereby save me some considerable amount of time.

1. I notice that there are only four (4) data types in SQLITE, which is 
OK, but there is no TIMESTAMP type, which is an incredibly useful type. 
Using this type makes it very easy to test whether a specific record 
has been updated or not, since the database automatically updates the 
value to a unique value (at least within that table) each time a record 
is inserted or updated. I suspect that if I want this capability in 
SQLITE I shall have to resort to some form of trigger or manually 
control an incrementing field value. Are there any other options?

2. How does SQLITE handle the case where one program or thread (using 
its own open handle) attempts to read records that are being modified 
within a transaction from another program or thread? Will the rows read 
be the original values, the values from the transaction, or will the 
query fail?

3. One of the projects were I am considering using SQLITE is from 
within a Windows filter driver, to act as an intelligent repository for 
control information (of which there may be a significant amount). I 
know that the code as provided will not compile within the Windows 
kernel, as it depends on user runtime calls like 'malloc', 'free' and 
'FlushFileBuffers', but I am considering changing these calls to more 
internal forms (sqlite3_malloc, sqlite3_free and 
sqlite3_flushfilebuffers, for example), and then creating conditional 
compilation to define these to use either the user mode call or a 
kernel level call. Does anyone know if this has been attempted before, 
or if there are likely to be considerable problems with attempting 
this?

4. In some circumstances the information I want to store may represent 
a million or more records in each of a dozen or more tables. Is the 
organization of the data more efficient if I store each table in its 
own database file, or if I use a single file?

5. I also have cases where I may have a significant number of deleted 
records, which will produce a significant amount of free space that can 
be used for new records. I know that VACUUM can be used to dump the 
contents of the database to a clone, drop the database and rename it, 
but that takes a considerable amount of disk space, and in some cases 
that disk space may not be available. How 'expensive' is using 
auto-vacuum mode? I understand that this is going to attempt to reduce 
the size of the database file by releasing unused 'pages', but does 
that not require that data be moved around so that the empty pages are 
at the end of the database file?

Thanks in advance for any and all assistance.

-Ken

Re: [sqlite] How to Install SQLite

[Cory Nelson]

just put sqlite3.exe in your path (like windows folder), then you can
open a command window and play with it from there.

On 4/14/05, Mahendra Batra <[EMAIL PROTECTED]> wrote:
> I refered www.sqlite.org for dowloading sqlite and i am working in Windows. I 
> followed this link :-
> 
> http://www.sqlite.org/download.html
> 
> and i downloaded sqlite-3_2_1.zip, which gave just sqlite.exe on unzipping.
> And when i downloaded the source code i am unable to compile it. Kindly, help 
> me in installing it.
> regards,
> Mahendra Batra
> 
> 
> -
> Do you Yahoo!?
>  Yahoo! Small Business - Try our new resources site!
> 


-- 
Cory Nelson
http://www.int64.org

Re: [sqlite] Cross compiling SQLite for Power PC

[F.W.A. van Leeuwen]

Why not have config.h statically contain:

#define  SQLITE_PTR_SZ  (sizeof(char*))

Wouldn't that be much easier?

Best regards,
Frank.