Re: [sqlite] unixGetPageSize needed but undefined

On 07/27/2014 09:30 PM, Alan Hourihane wrote: Hi all, I logged this bug via Gentoo, but it should be done here... https://bugs.gentoo.org/show_bug.cgi?id=517110 Is this still present on the trunk? It may have been fixed here: http://www.sqlite.org/src/info/612b6d1b1f Dan. " This

Re: [sqlite] Slow response using WHERE - IN

jose isaias cabrera wrote: > This command, > INSERT OR REPLACE INTO LSOpenProjects > SELECT * FROM client.LSOpenProjects as A > WHERE id IN > ( > SELECT id from LSOpenProjects > WHERE login != 'user1' AND Date != A.Date > ); > > is about 20 slower than if

[sqlite] sqlite3 has stopped working

I'm doing my project by using sqlite with code block.. and it create connection properly with sqlite... but while runing,I got the following message and do not know what I am to do : "sqltext.exe(my database file name) has stopped working A problem caused the progrm to stop working correctly.

[sqlite] Variable values in Views

Hi List, I have a view that works fine as-is, but I want to make it dynamic, replacing a few values with variables. SELECT > service, > sum( num ) AS num, > round( ( sum( num ) * 100.0 ) /( > SELECT sum( num ) > FROM* [$table_prefix]*_wms__getmap > WHERE

[sqlite] System.Data.Sqlite

Hi, I have a problem using System.Data.Sqlite in a C# application. I have 2 tables, each table with a geometry field and a spatial index, so the following request give me the results I want using Spatialite GUI or QGIS : SELECT * FROM Pylones P JOIN Lignes L ON L.lig_id = P.lig_id AND

Re: [sqlite] sqlite3 has stopped working

On Sun, 27 Jul 2014 23:16:05 -0700 (PDT), suparna wrote: >I'm doing my project by using sqlite with code block.. >and it create connection properly with sqlite... >but while runing,I got the following message and do not know what I am to do >: >"sqltext.exe(my database

Re: [sqlite] Variable values in Views

> On 28 Jul 2014, at 12:41pm, Jonathan Moules > wrote: > > *$table_prefix* which will be a number indicating which table set to look You can't have variable table names in a prepared statement. The process which does preparation has to know which table it

Re: [sqlite] System.Data.Sqlite

On 28 Jul 2014, at 1:01pm, Eric DAVID wrote: > I have a problem using System.Data.Sqlite in a C# application. I have 2 > tables, each table with a geometry field and a spatial index, so the > following request give me the results I want using Spatialite GUI or QGIS : > >

Re: [sqlite] Variable values in Views

On 2014/07/28 15:53, Jonathan Moules wrote: Fair question, but I'm doing log analysis. Each set of tables will be for a given server that's being analysed. The application that uses the data is a read-only web-app, so my database needs to be optimised for SELECT queries. I don't anticipate

Re: [sqlite] Variable values in Views

On 28 Jul 2014, at 2:53pm, Jonathan Moules wrote: > That said, I don't suppose there's any option for the other variable > ($date_string)? No. I suppose you could store the calculated date in another table, then use another JOIN to retrieve it. I have no

Re: [sqlite] Variable values in Views

Hi Simon, Thanks for your response. Good point about the preparation, didn't know that. > On the other hand, the fact that you're numbering tables suggests a badly formed schema. Are you sure you shouldn't merge the tables and move that number to a column inside the table ? Fair question, but

Re: [sqlite] System.Data.Sqlite

Well, I tried some different requests to simplify. This one is OK in all cases : SELECT * FROM Pylones P JOIN Lignes L ON L.lig_id = P.lig_id JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid; But this one gives correct results only with Spatialite GUI (Sqlite version 3.7.3) or QGIS (Sqlite

Re: [sqlite] Entry Points missing from sqlite3ext.h

2014-07-22 0:13 GMT+02:00 Peter Aronson : > Found in 3.8.4.3. Missing from sqlite3ext.h are sqlite3_auto_extension() and > sqlite3_cancel_auto_extension(). > > Now, you might ask, why would I need these functions in an extension? Well, > it turns out I'm writing a virtual

Re: [sqlite] System.Data.Sqlite

Eric DAVID wrote: > But this one gives correct results only with Spatialite GUI (Sqlite version > 3.7.3) or QGIS (Sqlite version 3.7.17) but wrong results with > System.Data.Sqlite (version 3.8.5). > > SELECT * FROM Pylones P > JOIN Lignes L ON L.lig_id = P.lig_id > JOIN idx_Pylones_pyl_geom I ON

[sqlite] ISO time leap second.

According to , the following should be perfectly valid: $ fossil new --date-override 2012-06-30T23:59:60 foo.fossil unrecognized date format (2012-06-30T23:59:60): use "-MM-DD HH:MM:SS.SSS" Suggested patch to fossil (and SQLite trunk, with adapted

Re: [sqlite] ISO time leap second.

Better to keep the code simpler and just say that SQLite (and hence Fossil) does not know about leap seconds. On Mon, Jul 28, 2014 at 10:49 AM, Jan Nijtmans wrote: > According to , the > following should be perfectly valid: > >

Re: [sqlite] ISO time leap second.

2014-07-28 16:53 GMT+02:00 Richard Hipp : > Better to keep the code simpler and just say that SQLite (and hence Fossil) > does not know about leap seconds. If you can do that in two bytes ;-) Regards, Jan Nijtmans ___

Re: [sqlite] ISO time leap second.

On 7/28/2014 10:58 AM, Jan Nijtmans wrote: 2014-07-28 16:53 GMT+02:00 Richard Hipp : Better to keep the code simpler and just say that SQLite (and hence Fossil) does not know about leap seconds. If you can do that in two bytes ;-) All your fix does is have the

Re: [sqlite] ISO time leap second.

2014-07-28 17:10 GMT+02:00 Igor Tandetnik : > All your fix does is have the parser accept "60" as valid seconds field. > That's not very interesting. Yes, that's exactly all that I'm after. ISO 8601 does not specify how leap seconds are handled, it just specified which

Re: [sqlite] ISO time leap second.

On 7/28/2014 11:49 AM, Jan Nijtmans wrote: 2014-07-28 17:10 GMT+02:00 Igor Tandetnik : All your fix does is have the parser accept "60" as valid seconds field. That's not very interesting. Yes, that's exactly all that I'm after. ISO 8601 does not specify how leap seconds

Re: [sqlite] ISO time leap second.

"Igor Tandetnik" wote... On 7/28/2014 11:49 AM, Jan Nijtmans wrote: 2014-07-28 17:10 GMT+02:00 Igor Tandetnik : All your fix does is have the parser accept "60" as valid seconds field. That's not very interesting. julianday('2012-06-30T23:59:60'), and how should it

Re: [sqlite] ISO time leap second.

On 7/28/2014 12:37 PM, jose isaias cabrera wrote: I claim that I am not an expert, but is this one a valid ISO time stamp? If so, then that ISO must be revised, as that time does not really exists ever. That time does really exist, occasionally: http://en.wikipedia.org/wiki/Leap_second --

Re: [sqlite] System.Data.Sqlite

You are right, your example works correctly. I created my database again with the script below and I tried some changes. The error still occurs with System.Data.Sqlite 3.8.5. If I drop the index idx_Pylones_lig_id, the results are good again. So I think there is something wrong with

Re: [sqlite] System.Data.Sqlite

On 28 Jul 2014, at 5:51pm, Eric DAVID wrote: > You are right, your example works correctly. > I created my database again with the script below and I tried some changes. > The error still occurs with System.Data.Sqlite 3.8.5. If I drop the index > idx_Pylones_lig_id, the

Re: [sqlite] ISO time leap second.

2014-07-28 18:28 GMT+02:00 Igor Tandetnik : > What do you mean "ignore"? Can you specify precisely what the semantics of > such a timestamp should be, in all the date/time manipulation operations > SQLite supports? Just as julianday('2012-06-30T24:00:00') is exactly equal to

Re: [sqlite] ISO time leap second.

It may or may not be a valid timestamp depending on what your time representation is. SQLite does not use UTC (which is an artificial timescale maintained by a bunch of atomic clocks). SQLite (and most other things that keep time, other than artificial atomic clocks) use Mean Solar Time.

Re: [sqlite] Slow response using WHERE - IN

Clemens Ladisch wrote... The first command is slow because the subquery generates lots of results, but because of the reference to A.Date, it is a correlated subquery and must be re-executed for each row in A. If a have understood the query correctly, you want to check whether a corresponding

[sqlite] SQL Join question

All, Can someone tell me what the purpose of line 2 is in the following example? It seems redundant to me since what is wanted from the Customers table is specified on line 1. 1: SELECT Customers.CustomerName, Orders.OrderID 2: FROM Customers 3: INNER JOIN Orders 4: ON

Re: [sqlite] SQL Join question

On Mon, Jul 28, 2014 at 3:10 PM, Drago, William @ MWG - NARDAEAST wrote: > All, > > Can someone tell me what the purpose of line 2 is in the following example? > It seems redundant to me since what is wanted from the Customers table is > specified on line 1. > > > 1:

Re: [sqlite] SQL Join question

On 7/28/2014 4:10 PM, Drago, William @ MWG - NARDAEAST wrote: Can someone tell me what the purpose of line 2 is in the following example? It seems redundant to me since what is wanted from the Customers table is specified on line 1. Line 1 - the SELECT clause - specifies what data you want

Re: [sqlite] SQL Join question

On Mon, Jul 28, 2014 at 3:10 PM, Drago, William @ MWG - NARDAEAST wrote: > All, > > Can someone tell me what the purpose of line 2 is in the following example? > It seems redundant to me since what is wanted from the Customers table is > specified on line 1. > > > 1:

Re: [sqlite] Variable values in Views

On Jul 28, 2014, at 3:53 PM, Jonathan Moules wrote: > Fair question, but I'm doing log analysis. Each set of tables will be for a > given server that's being analysed. Alternatively, you could setup your tables as a set of distinct databases, one per

Re: [sqlite] System.Data.Sqlite

Eric DAVID wrote: > > SELECT * FROM Pylones P > JOIN Lignes L ON L.lig_id = P.lig_id > JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid WHERE L.lig_id = 3; > Is the above query the one that has the issue? What is the output you expect from it? -- Joe Mistachkin

Re: [sqlite] ISO time leap second.

On Mon, Jul 28, 2014 at 12:20 PM, Keith Medcalf wrote: > It may or may not be a valid timestamp depending on what your time > representation is. SQLite does not use UTC (which is an artificial timescale > maintained by a bunch of atomic clocks). SQLite (and most other

Re: [sqlite] System.Data.Sqlite

Yes, it is this one. With the new script I gave in my last mail it has changed to SELECT * FROM Pylones P JOIN Lignes L ON L.lig_id = P.lig_id JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid WHERE L.lig_id = 1; And the expected result is : 1 1 1 BLOB sz=60 GEOMETRY 1

Re: [sqlite] System.Data.Sqlite

Eric DAVID wrote: > > Yes, it is this one. With the new script I gave in my last mail it has > changed to > > SELECT * FROM Pylones P > JOIN Lignes L ON L.lig_id = P.lig_id > JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid WHERE L.lig_id = 1; > So far, I'm unable to reproduce your results

Re: [sqlite] SQL Join question

On 28 Jul 2014, at 9:10pm, Drago, William @ MWG - NARDAEAST wrote: > Can someone tell me what the purpose of line 2 is in the following example? > It seems redundant to me since what is wanted from the Customers table is > specified on line 1. > > > 1: SELECT

Re: [sqlite] System.Data.Sqlite

> On 28 Jul 2014, at 10:58pm, Joe Mistachkin wrote: > > Eric DAVID wrote: > >> Yes, it is this one. With the new script I gave in my last mail it has >> changed to >> >> SELECT * FROM Pylones P >> JOIN Lignes L ON L.lig_id = P.lig_id >> JOIN idx_Pylones_pyl_geom I ON

Re: [sqlite] SQL Join question

Excellent explanation. Thank you! -Bill > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Monday, July 28, 2014 6:20 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQL Join

Re: [sqlite] ISO time leap second.

"Igor Tandetnik" wrote... On 7/28/2014 12:37 PM, jose isaias cabrera wrote: I claim that I am not an expert, but is this one a valid ISO time stamp? If so, then that ISO must be revised, as that time does not really exists ever. That time does really exist, occasionally:

Re: [sqlite] SQL Join question

On Mon, Jul 28, 2014 at 4:20 PM, Simon Slavin wrote: > First and most important, a "FROM" clause is required for every "SELECT" > command [1]. So the statement, whatever it's meant to do, definitely needs > to be "FROM" something. There's only two tables used in the

Re: [sqlite] SQL Join question

On 29 Jul 2014, at 12:27am, Scott Robison wrote: > On Mon, Jul 28, 2014 at 4:20 PM, Simon Slavin wrote: > >> First and most important, a "FROM" clause is required for every "SELECT" >> command [1]. So the statement, whatever it's meant to do,

Re: [sqlite] System.Data.Sqlite

Here is the list of all sql statements in the file Base.sql, included INSERT Statements. I think the issue comes from the index idx_Pylones_lig_id. Eric DAVID -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Joe Mistachkin

Re: [sqlite] System.Data.Sqlite

Eric DAVID wrote: > > Here is the list of all sql statements in the file Base.sql, included > INSERT Statements. > Unfortunately, the mailing list strips most attachments. Can you include the information within the message itself? -- Joe Mistachkin

Re: [sqlite] System.Data.Sqlite

Well, It seems to be impossible to join files to mails. So, here are the statements : CREATE TABLE Lignes (lig_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, lig_nom TEXT NOT NULL); SELECT AddGeometryColumn('Lignes', 'lig_geom', 2154, 'LINESTRING', 'XY'); SELECT CreateSpatialIndex('Lignes',

Re: [sqlite] SQL Join question

On Mon, Jul 28, 2014 at 5:33 PM, Simon Slavin wrote: > > While it is true most (all?) useful SELECT statements will have a FROM > > clause, it is not true that all SELECT statements must have a FROM > clause: > > > > SELECT 1.0/3.0 > > SELECT sqlite_version(); > > True.

Re: [sqlite] System.Data.Sqlite

On 29 Jul 2014, at 12:43am, Eric DAVID wrote: > Well, It seems to be impossible to join files to mails. So, here are the > statements : Unfortunate I'm not willing to download SpatiaLite just to test this. But I'm still suspecting that the problem is in your

Re: [sqlite] ISO time leap second.

On Mon, 28 Jul 2014 16:32:42 -0500 Nico Williams wrote: > The word "solar" does not appear on > http://sqlite.org/lang_datefunc.html . Instead it's explicitly stated > that "Universal Coordinated Time (UTC) is used". Quite. > Still, ISTM that "UTC is used" implies

Re: [sqlite] SQL Join question

On Mon, 28 Jul 2014 16:20:38 -0400 Igor Tandetnik wrote: > On 7/28/2014 4:10 PM, Drago, William @ MWG - NARDAEAST wrote: > > Can someone tell me what the purpose of line 2 is in the following > > example? It seems redundant to me since what is wanted from the > > Customers

Re: [sqlite] Variable values in Views

On Mon, 28 Jul 2014 14:53:34 +0100 Jonathan Moules wrote: > Fair question, but I'm doing log analysis. Each set of tables will be > for a given server that's being analysed. The application that uses > the data is a read-only web-app, so my database needs to

Re: [sqlite] ISO time leap second.

On Mon, Jul 28, 2014 at 7:54 PM, James K. Lowden wrote: > On Mon, 28 Jul 2014 16:32:42 -0500 > Nico Williams wrote: >> Raising errors is not that useful here: most users who ever run into >> such timestamps will not really know what to do with