[sqlite] XSelect

Hi! Supose i have a table like this: Table - X Y Z 1|1|1 1|2|2 1|3|3 2|1|2 2|2|4 2|3|6 3|1|3 3|2|6 3|3|9 As far as i know there isn't a SQL statment to get next result in sqlite: Result --- M|1|2|3 1|1|2|3 2|2|4|6 3|3|6|9 I think the best approach is create a temporary table, so i can use

RE: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

out of curiocity, can you give a try to my delphi wrappers? http://www.sqlite.org/contrib (not Tdataset replacement, though, but they should be quite fast for this reason. also try the "serverCursor" setting, which allows you to step thorugh the result set one at a time, meaning there is no do

Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

No, the DLL wasn't the issue (heck the entire program is a DLL anyway and that's worked well for ages) We eventually discoverd through profiling the Delphi componants we had started using were the slow point, tossed them, and tried someone elses version (some stuff adapted by someone called Tim

Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

I wouldn't think DLL calling overhead would be significant when dealing with things as slow (relatively) as a database. Is it really necessary for it to be a DLL? You might be able to statically link it and remove that overhead. Are you using COM or ActiveX to call it? If I remember right they ha

[sqlite] Re: [Bulk] Re: [sqlite] Re: [Bulk] Re: [sqlite] How sqlite3_column_double works?

I've got it: in c doubles are passed to a function by pushing its two dword parts: first the high and then the low part, and when it's the result of a double type function is returned in the ST0 of the FPU. Thank you! Marcelo.

Re: [sqlite] Re: [Bulk] Re: [sqlite] How sqlite3_column_double works?

On Wed, 02 Feb 2005 19:10:58 -0300, Marcelo Zamateo <[EMAIL PROTECTED]> wrote: > Clay Dowling said: > > > > > double sqlite3_column_double(sqlite3_stmt*, int iCol); > > > > returns a double, not a pointer to a double. > > > Thank you Clay. > Excuse me, i'm not good programming in c. A double is 8

Re: [sqlite] CREATE TABLE $table_name (\%HASH)

--- [EMAIL PROTECTED] wrote: > > > > > Clark: > I ended up using the brute fore approach you suggested > with a small > variation. > Thank you for your feedback. Ah, yes, the trailing comma has bitten me more than once with looping string concatenation ;-) -Clark

Re: [sqlite] CREATE TABLE $table_name (\%HASH)

Clark: I ended up using the brute fore approach you suggested with a small variation. Thank you for your feedback. # PERL ### #note: @cleanenv = (fld-1 fld-n); eval { local $dbh -> {PrintError} = 0;

Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

> H The fact that the freelist size is different > suggests either a bug in sqlite3_analyzer or a corrupt > database file. What does "PRAGMA integrity_check" say? It returned a batch of Page not used (from about 4 to 48 I think).\ That was it > What OS did you say you are using? Windo

Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

> > CREATE INDEX myMyIndex ON myTable (myKey); > > CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); > > CREATE INDEX myNumOneIndex ON myTable (NumOne); > > Why did you chose these indexes? > Updates and Inserts are faster with fewer indices. > Perhaps they can be reduces? > > Yo

Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

> Out of curiousity, have you tried doing a plain INSERT instead of INSERT > OR REPLACE INTO? > Since you have no unique fields in your table, I'm not exactly sure how > or why you might get an > INSERT failure, but perhaps Sqlite is doing some sort of data comparison > on each insert. We have

[sqlite] Re: [Bulk] Re: [sqlite] How sqlite3_column_double works?

Clay Dowling said: double sqlite3_column_double(sqlite3_stmt*, int iCol); returns a double, not a pointer to a double. Thank you Clay. Excuse me, i'm not good programming in c. A double is 8 bytes width. How does a function return anything other than 4 bytes (in eax register) if not in a pointe

Re: [sqlite] CREATE TABLE $table_name (\%HASH)

--- [EMAIL PROTECTED] wrote: > > > > > Question: > I have a perl %HASH that contains the column names and > type for the TABLE I > am trying to create. > HOW can I achieve such a task? > I have looked into SQL::Abastract DBIx::Simple and DBI > without any success. > > Regards, > > Uriel_Car

[sqlite] all processes block, with attached databases

I am encountering an occasional blocking condition when multiple processes are concurrently updating database or beginning transactions where one or more of the processes has attached databases. I'm using 2.8.15, and all locks should be "exclusive" in this version, so at least one of the processes

Re: [sqlite] Cursors

Thank you for the feedback. I ended up reading one row at a time, changing the columns that needed to be changed, then updating as follows: ## perl $fetch = $db -> query('SELECT * FROM sardata ORDER BY s

[sqlite] CREATE TABLE $table_name (\%HASH)

Question: I have a perl %HASH that contains the column names and type for the TABLE I am trying to create. HOW can I achieve such a task? I have looked into SQL::Abastract DBIx::Simple and DBI without any success. Regards, Uriel_Carrasquilla

Re: [sqlite] SQLite Advocacy

Scott Chapman said: > approachable full web stack that may ease this problem. We'll see how it > matures. IMHO, sticking with Python is worth getting over the initial > confusion. Python offers a lot of choices, which is great, but you have > to do more digging initially. Scott, I might say t

Re: [sqlite] SQLite Advocacy

> Let's be careful out there. I have found rookies tend to blindly > evangelically tout their first learned tool as the one and only path of > light to truth and world peace. Fred, I think you just hit a good part of the reason that PHP and MySQL have mind-share out there when they are lousy too

RE: [sqlite] What has changed for AUTOINCREMENT in 3.1?

On Wed, 2005-02-02 at 15:15 -0500, Andrew Shakinovsky wrote: > >>With just INTEGER PRIMARY KEY, if you delete the largest > >>key in the table then insert a new row with a NULL key, > >>the key that was deleted will get reused. If you add > >>AUTOINCREMENT, keys will never be reused this way. Th

RE: [sqlite] What has changed for AUTOINCREMENT in 3.1?

>>With just INTEGER PRIMARY KEY, if you delete the largest >>key in the table then insert a new row with a NULL key, >>the key that was deleted will get reused. If you add >>AUTOINCREMENT, keys will never be reused this way. The value >>inserted will be one more than the largest key >>that has

Re: [sqlite] What has changed for AUTOINCREMENT in 3.1?

On Wed, 2005-02-02 at 15:00 -0500, Ned Batchelder wrote: > I see from the 3.1 release notes that AUTOINCREMENT is now supported as a > keyword on integer columns. But has anything actually changed about the > semantic of auto increment columns? 3.0 had them as INTEGER PRIMARY KEY. > With just I

Re: [sqlite] How sqlite3_column_double works?

Marcelo Zamateo said: > Hi everybody! > > I need a tip about how sqlite3_column_double works. I hoped it returns a > pointer to a 8-byte-real, but it don't. double sqlite3_column_double(sqlite3_stmt*, int iCol); returns a double, not a pointer to a double. That seems pretty straight forward to

[sqlite] How sqlite3_column_double works?

Hi everybody! I need a tip about how sqlite3_column_double works. I hoped it returns a pointer to a 8-byte-real, but it don't. Here is what i do: CREATE TABLE [tblTyped] ([a] TEXT, [n] NUMERIC, [i] INTEGER, [b] BLOB) Insert into tblTyped values('helo,,,', 1.1121, '8.0001', 'blob!!!') then sqlite

[sqlite] What has changed for AUTOINCREMENT in 3.1?

I see from the 3.1 release notes that AUTOINCREMENT is now supported as a keyword on integer columns. But has anything actually changed about the semantic of auto increment columns? 3.0 had them as INTEGER PRIMARY KEY.

Re: [sqlite] Cursors

--- [EMAIL PROTECTED] wrote: > > > > > > Hello list! > I have run into problems trying to use SQLite via perl > DBA. > According to the SQLite.org web site, DECLARE CURSOR > should be supported > (actually, it is not in the list of the only SQL not > supported). > I get a error saying "error

RE: [sqlite] SQLite Advocacy

-Original Message- From: Andrew Piskorski [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 02, 2005 1:08 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite Advocacy On Wed, Feb 02, 2005 at 08:18:21PM +0200, Stanislav Malyshev wrote: > AP>>:) Yes. AFAICT, the only excuse fo

Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?

Eric Scouten wrote: James, thanks for that update. I've filed a new ticket for this issue, #1096 (http://www.sqlite.org/cvstrac/tktview?tn=1096). I hope you don't mind that I quoted your response and implementation suggestion in the body of that ticket. There you express some concerns about perf

Re: [sqlite] SQLite Advocacy

On Wed, Feb 02, 2005 at 08:18:21PM +0200, Stanislav Malyshev wrote: > AP>>:) Yes. AFAICT, the only excuse for the existence of MySQL and PHP at > AP>>all is the ignorance of their creators. They are poor tools for the > Thanks for extending horizons of my knowledge. Why, you're quite welcome.

Re: [sqlite] quick question regarding INTEGER PRIMARY KEY default value

On Wed, 2 Feb 2005 09:22:09 -0800 (PST), you wrote: > >You really should not rely on implicit assumptions in your code, >such as 'the first value of an autoincrement field is 1'. If you >use a different database, or different version of this one, that >may be wrong and a nightmare to fix. True, b

Re: [sqlite] Make error installing sqlite3.0.8 on Solaris 9 (Sparc)

Hi Mike, Rightmire, Mike wrote: -Original Message- From: Rightmire, Mike Sent: Thursday, January 27, 2005 3:31 PM To: 'sqlite-users@sqlite.org' Subject: Make error installing sqlite3.0.8 on Solaris 9 (Sparc) I am trying to install SQlite3.0.8 on Solaris 9 Sparc. I have no issues when con

Re: [sqlite] SQLite Advocacy

Yes, you'll catch more flies with honey than with vinegar as they say. FWIW, the only way I even _found_ SQLite was because of PHP5. I'm quite pleased with both, though if you have tools that suit your needs better, then by all means: use them. On Wed, 2 Feb 2005 20:18:21 +0200 (IST), Stanislav

Re: [sqlite] SQLite Advocacy

AP>>:) Yes. AFAICT, the only excuse for the existence of MySQL and PHP at AP>>all is the ignorance of their creators. They are poor tools for the That's very funny to see how thread named "SQLite Advocacy" is dedicated to bashing MySQL and PHP. I guess there's some meaning of the word "advoca

Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?

James, thanks for that update. I've filed a new ticket for this issue, #1096 (http://www.sqlite.org/cvstrac/tktview?tn=1096). I hope you don't mind that I quoted your response and implementation suggestion in the body of that ticket. -Eric James Berry wrote: Eric, No, you're not missing anythin

Re: [sqlite] SQLite Advocacy

On Wed, Feb 02, 2005 at 08:52:17AM -0800, Scott Chapman wrote: > I have found that MySQL should not be used for data driven web sites as well. > > There is no advantage to using MySQL over Postgres. > I talked with the author of the GIS extension to MySQL and found that it is a > poorly writ

Re: [sqlite] quick question regarding INTEGER PRIMARY KEY default value

Jay wrote: You really should not rely on implicit assumptions in your code, such as 'the first value of an autoincrement field is 1'. If you use a different database, or different version of this one, that may be wrong and a nightmare to fix. There are calls that will return the value of the field

Re: [sqlite] quick question regarding INTEGER PRIMARY KEY default value

You really should not rely on implicit assumptions in your code, such as 'the first value of an autoincrement field is 1'. If you use a different database, or different version of this one, that may be wrong and a nightmare to fix. There are calls that will return the value of the field after it'

Re: [sqlite] SQLite Advocacy

On Wednesday 02 February 2005 7:52 am, John Dean wrote: > At 14:57 02/02/2005, Paul Malcher wrote: > >Scott Chapman wrote: > >>Regarding the issue of SQL Server vs. SQLite: > >>If the choice were between SQL Server and SQLite, and the need came up > >>that SQLite could not meet, I'd pitch for Postg

Re: [sqlite] quick question regarding INTEGER PRIMARY KEY default value

It is documented to start at 1: If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. [...] If no ROWID is specified on the insert, an appropriate ROWID is created automatically. The usual algorithm is to g

[sqlite] quick question regarding INTEGER PRIMARY KEY default value

Will the default value of an INTEGER PRIMARY KEY column always start at 1? Example: CREATE TABLE xyz (id INTEGER PRIMARY KEY); INSERT INTO xyz VALUES (NULL); Will the value of the id column always be 1 in this example? I tried numerous tests, and it seems to always start at 1, I wanted to just mak

Re: [sqlite] SQLite Advocacy

At 14:57 02/02/2005, Paul Malcher wrote: Scott Chapman wrote: Regarding the issue of SQL Server vs. SQLite: If the choice were between SQL Server and SQLite, and the need came up that SQLite could not meet, I'd pitch for PostgreSQL (_not_ MySQL). PostgreSQL version 8.0 has been released and runs

Re: [sqlite] Handling database updates in my program updates

Don't forget to check the version numbers of the DLL's and such that your program calls. I've had a LOT of grief in the past because users installed a new program, after installing my program, which placed newer (or older) incompatible DLL's on their system. My program just started mysteriously cr

Re: [sqlite] SQLite Advocacy

Scott Chapman wrote: Regarding the issue of SQL Server vs. SQLite: If the choice were between SQL Server and SQLite, and the need came up that SQLite could not meet, I'd pitch for PostgreSQL (_not_ MySQL). PostgreSQL version 8.0 has been released and runs natively on Windows. When it comes to

Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

> The schema is VERY basic: > > CREATE TABLE [myTable] ( > [myKey] [bigint] NULL , > [NumOne] [int] NULL , > [NumTwo] [real] NULL , > [NumThree] [real] NULL , > [NumFour] [real] NULL , > [NumFive] [real] NULL , > [NumSix] [float] NULL , >

Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

> The myMyIndex will only slow you down. myNumOneIndex > will help if you have queries that use the numOne column. Hurm, yes that makes sense. it wasn't supposed to be there, but was added just incase it helped, which it didn't > What is the output from sqlite3_analyzer? Unfortunatly didn't s