[sqlite] Thanks SQLite

I just wish to note something and display my gratitude (which I hope is shared by others) towards Richard, Dan and other SQLite devs for all the great functionality, but specifically CTE's. The reason I need to say this is that I was one of the people who, when we first had requests for CTE's

[sqlite] Thanks SQLite

On 29 Jul 2015, at 11:32pm, Richard Hipp wrote: > I'm looking for real-world (open-source) use cases for CTEs. I would like to see a demonstration of how non-recursive CTEs can be useful in simplifying a SQL command. Ideally in a plausible example rather than something obviously made up

[sqlite] Thanks SQLite

On 2015-07-30 12:41 AM, Simon Slavin wrote: > On 29 Jul 2015, at 11:32pm, Richard Hipp wrote: > >> I'm looking for real-world (open-source) use cases for CTEs. > I would like to see a demonstration of how non-recursive CTEs can be useful > in simplifying a SQL command. Ideally in a plausible

[sqlite] changed time zone

We use the fatastic ODBC implementation from Christian Werner (http://www.ch-werner.de/sqliteodbc/) with the IIS webserver. So we don't have any control over envir variables. I think SQLite should always use runtime information about the current time zone. -Urspr?ngliche Nachricht- Von:

[sqlite] Query tooooo sloooooooooow

Hi All, Here is my query: select count(*) from contact c left join history h on (h.elid=c.elid); Here are the table structures: sqlite> .schema history CREATE TABLE history (name varchar(32), email varchar(128) unique, elid int(12)); CREATE INDEX elid on history (elid) ; sqlite> .schema contact

[sqlite] Query tooooo sloooooooooow

James Qian Wang wrote: > select count(*) from contact c left join history h on (h.elid=c.elid); Please show the output of EXPLAIN QUERY PLAN for this query. If it does not look like this: 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 0|1|1|SEARCH TABLE history AS h USING COVERING

[sqlite] Query tooooo sloooooooooow

my desktop (windows 7) SQLite version 3.8.7.4 2014-12-09 01:34:36 and explain query plan showed: 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?) my linux box needs upgrade. regardless, both very slow On Thu, Jul 30,

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-29 08:58 PM, jose isaias cabrera wrote: >> >> >> There will be entries longer than 1 day, so I will have to address than. >> Thanks for the help, but this is great, though. If I need more help, I >> will come back, but I have enough, and I "I think" I can

[sqlite] Query tooooo sloooooooooow

How slow is too slow? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of James Qian Wang Sent: Thursday, July 30, 2015 5:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query

[sqlite] Read strings as they were

Here is my problem. I have strings that "looks" like a numbers. But they aren't (at least for me). I am aware that SQLite handles types on it's own and makes conversion to INTEGER or REAL if it is possible (sqlite.org/datatype3.html). The problem is that I got ".0" at every number I put into

[sqlite] Read strings as they were

On 7/30/2015 10:04 AM, hawk wrote: > Here is my problem. I have strings that "looks" like a numbers. But they > aren't (at least for me). > I am aware that SQLite handles types on it's own and makes conversion to > INTEGER or REAL if it is possible (sqlite.org/datatype3.html). The problem > is

[sqlite] Query tooooo sloooooooooow

James Qian Wang wrote: > 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 > 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?) There is no more efficient way to execute this query. > both very slow What file system? What disks? Any network? Regards, Clemens

[sqlite] Query tooooo sloooooooooow

I agree. Thank you all. On Thu, Jul 30, 2015 at 3:44 PM, Clemens Ladisch wrote: > James Qian Wang wrote: > > 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 > > 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?) > > There is no more efficient way to execute this

[sqlite] Read strings as they were

On 30 Jul 2015, at 3:04pm, hawk wrote: > CREATE TABLE IF NOT EXISTS 'test' (`testcol` INTEGER); > INSERT INTO 'test' (`testcol`) VALUES (''); > SELECT * from 'test'; > > and I get > .0 Igor pointed out one problem: you explicitly defined the column as INTEGER, therefore SQL is

[sqlite] Performance regression between SQLite 3.8.4.1 and 3.8.8

Hi, we encountered an issue where a query that performed well using SQLite 3.8.4.1 (i.e. execution time way below 1 second) suddenly took several seconds to execute. Unfortunately, I could not yet reduce the query and database to a sample that I could send along and the real query / database

[sqlite] Performance regression between SQLite 3.8.4.1 and 3.8.8

On 7/30/15, Jens Miltner wrote: > Hi, > > we encountered an issue where a query that performed well using SQLite > 3.8.4.1 (i.e. execution time way below 1 second) suddenly took several > seconds to execute. > Unfortunately, I could not yet reduce the query and database to a sample > that I could

[sqlite] Performance regression between SQLite 3.8.4.1 and 3.8.8

On 30 Jul 2015, at 5:38pm, Jens Miltner wrote: > I can send you the query and information on the tables involved off list as > well as "explain query", "explain query plan", etc. results - just let me > know what you'd need (although I probably cannot send the actual database > involved).

[sqlite] CSV excel import

I understood from the mailing list, that CSV is not a defined format, then let's propose another format, well defined, the Excel one (which is in my experience a format to is good every time I had to exchange CSV files). Then why don't you propose an import of CSV from Excel (or similar)?

[sqlite] CSV excel import

On 7/30/15, Sylvain Pointeau wrote: > I understood from the mailing list, that CSV is not a defined format, then > let's propose another format, well defined, the Excel one (which is in my > experience a format to is good every time I had to exchange CSV files). > > Then why don't you propose an

[sqlite] CSV excel import

On 30-7-2015 20:07, Richard Hipp wrote: > On 7/30/15, Sylvain Pointeau wrote: >> I understood from the mailing list, that CSV is not a defined format, then >> let's propose another format, well defined, the Excel one (which is in my >> experience a format to is good every time I had to exchange

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 8:07 PM, Richard Hipp wrote: > > Then why don't you propose an import of CSV from Excel (or similar)? > > csv(excel) > An Excel-to-SQLite converter utility sounds like it would be a great > open-source project. Why don't you start it up? > -- > D. Richard Hipp > drh

[sqlite] CSV excel import

I can remember two times when my life would have been easier if I could throw big .ods into sqlite3 dbs. So I would also like such a project.

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 8:17 PM, Luuk wrote: > On 30-7-2015 20:07, Richard Hipp wrote: > >> On 7/30/15, Sylvain Pointeau wrote: >> >>> I understood from the mailing list, that CSV is not a defined format, >>> then >>> let's propose another format, well defined, the Excel one (which is in my >>>

[sqlite] CSV excel import

> My point is that I have seen so many emails regarding this incorrect csv > import, that it would be so easy for us if it just simply works in the CLI > and delivered in standard in the sqlite3 executable. I don't think I understand what you mean by this. Also, most of the problems seems to

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 8:32 PM, Bernardo Sulzbach < mafagafogigante at gmail.com> wrote: > > My point is that I have seen so many emails regarding this incorrect csv > import, that it would be so easy for us if it just simply works in the CLI > and delivered in standard in the sqlite3

[sqlite] CSV excel import

Instead of trying to conform to MS-Excel's csv format, wouldn't it be better to write an import from .xls (or .ods if that is an open standard) directly? That way each cell's value can be bound to a position holder in a query. No more fussing with "In this country we use this symbol to denote

[sqlite] CSV excel import

Actually there exists an open source tool that convert Excel data into SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library (http://www.gdal.org/). ?You do need a version of GDAL built with the SQLite and XLSX and/or XLS drivers. ?Actually, if you don't mind adding SpatiaLite

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 8:43 PM, Adam Devita wrote: > Instead of trying to conform to MS-Excel's csv format, wouldn't it be > better to write an import from .xls (or .ods if that is an open > standard) directly? > > That way each cell's value can be bound to a position holder in a > query. No

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson wrote: > Actually there exists an open source tool that convert Excel data into > SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library ( > http://www.gdal.org/). You do need a version of GDAL built with the > SQLite and XLSX

[sqlite] CSV excel import

A trick that works great most of the time with ODS is when exporting to CSV select the option to quote all fields. One problem with CSV is that many exports quote strings but not numbers. If everything is quoted then it is much simpler to process. But would need at least several options on

[sqlite] CSV excel import

On 30 Jul 2015, at 7:48pm, Sylvain Pointeau wrote: > really cool, but I would like to have a solution directly in the sqlite3 > executable If you're talking about the SQLite shell tool then Excel import will never be integrated into it. There's no way to know when Microsoft is going to

[sqlite] CSV excel import

Le jeudi 30 juillet 2015, Scott Doctor a ?crit : > > A trick that works great most of the time with ODS is when exporting to > CSV select the option to quote all fields. One problem with CSV is that > many exports quote strings but not numbers. If everything is quoted then it > is much simpler

[sqlite] CSV excel import

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/30/2015 10:58 AM, Sylvain Pointeau wrote: > is it possible? in a lot of cases, I cannot use sqlite (executable) > because of the lack of a good CSV import. It would really great if > this could be addressed. Use the APSW shell:

[sqlite] CSV excel import

Le jeudi 30 juillet 2015, Simon Slavin a ?crit : > > On 30 Jul 2015, at 7:48pm, Sylvain Pointeau > wrote: > > > really cool, but I would like to have a solution directly in the sqlite3 > > executable > > If you're talking about the SQLite shell tool then Excel import will never >

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 1:48 PM, Sylvain Pointeau < sylvain.pointeau at gmail.com> wrote: > On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson wrote: > > > Actually there exists an open source tool that convert Excel data into > > SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL

[sqlite] CSV excel import

Le jeudi 30 juillet 2015, John McKown a ?crit : > On Thu, Jul 30, 2015 at 1:48 PM, Sylvain Pointeau < > sylvain.pointeau at gmail.com > wrote: > > > On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson > wrote: > > > > > Actually there exists an open source tool that

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 2:13 PM, Sylvain Pointeau < sylvain.pointeau at gmail.com> wrote: > > > > > My answer saying that I would like to have it in sqlite implied a correct > csv import. In a previous email, I stated that it would be unreasonable to > ask sqlite to have an xml parser etc. > I

[sqlite] Strange behaviour of sqlite3_stmt_busy

Hello, sqlite3_stmt_busy returns true after sqlite3_step returns DONE. Here is the code: #include #include #include "sqlite3.h" int main(int argc, char **argv) { sqlite3 *db = NULL; sqlite3_stmt *stmt = NULL; char *zErrMsg = NULL; const char *z; int rc = 0; rc =

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 12:58 PM, Sylvain Pointeau < sylvain.pointeau at gmail.com> wrote: > I understood from the mailing list, that CSV is not a defined format, then > let's propose another format, well defined, the Excel one (which is in my > experience a format to is good every time I had to

[sqlite] CSV excel import

> > > ?I am replying to your original message rather that later ones because I'm > curious about the CSV file which is giving you a problem. Using the sqlite3 > command on Linux Fedora 22 (64 bit), I get the following (transcript): > > $sqlite3 > SQLite version 3.8.10.2 2015-05-20 18:17:19 > Enter

[sqlite] CSV excel import

On 2015-07-30 09:05 PM, Sylvain Pointeau wrote: > Le jeudi 30 juillet 2015, Simon Slavin a ?crit : > >> >> >> The CSV importing part of the SQLite shell tool implements this very well. >> >> Simon. >> > > No it does not implement the rule 5 correctly: > >

[sqlite] CSV excel import

Ha.. ignore my previous reply then. Glad it works for you! On 2015-07-30 10:40 PM, Sylvain Pointeau wrote: >> >> ?I am replying to your original message rather that later ones because I'm >> curious about the CSV file which is giving you a problem. Using the sqlite3 >> command on Linux Fedora 22

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 3:40 PM, Sylvain Pointeau < sylvain.pointeau at gmail.com> wrote: > > Well ... I am glad that you are right. > > When I saw your message, I wondered why it didn't work for me. > > I have taken back my samples: > > $ cat test.csv > A,B,C > T,TI,TIT > "A,B",C,D > "1st line >

[sqlite] FTS5 Porter extra arguments not passed through

As per the documentation, extra arguments to the Porter stemmer are handed on to the underlying tokenizer: http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=544-546 Example syntax a few lines below: http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=556 However, the code

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 9:00 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/30/2015 10:58 AM, Sylvain Pointeau wrote: > > is it possible? in a lot of cases, I cannot use sqlite (executable) > > because of the lack of a good CSV import. It would really great if

[sqlite] CSV excel import

On 30 Jul 2015, at 9:50pm, Sylvain Pointeau wrote: > leading > 0 are removed when opening a csv file by double clicking on it to open it > in excel. This is documented behaviour in Excel, which assumes that all cells contain numbers, and therefore that leading zeros can be removed. If you

[sqlite] CSV excel import

On Thu, Jul 30, 2015 at 10:52 PM, Simon Slavin wrote: > > On 30 Jul 2015, at 9:50pm, Sylvain Pointeau > wrote: > > > leading > > 0 are removed when opening a csv file by double clicking on it to open it > > in excel. > > This is documented behaviour in Excel, which assumes that all cells >

[sqlite] CSV excel import

On 30 July 2015 at 13:52, Simon Slavin wrote: >> leading >> 0 are removed when opening a csv file by double clicking on it to open it >> in excel. > > This is documented behaviour in Excel, which assumes that all cells contain > numbers, and therefore that leading zeros can be removed. If you

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-29 08:58 PM, jose isaias cabrera wrote: >> >> >> There will be entries longer than 1 day, so I will have to address than. >> Thanks for the help, but this is great, though. If I need more help, I >> will come back, but I have enough, and I "I think" I can

[sqlite] CSV excel import

On 30 Jul 2015, at 9:57pm, Sylvain Pointeau wrote: > no it does not work double clicking on the csv to open it in excel, I am > 100% sure (I just tried again), you have to go through the data->import and > set up the columns as text for it to work Yes, as I wrote, this bad behaviour (which

[sqlite] CSV excel import

Another option is to save the Excel file as tab separated values. Using tabs as delimiters avoids some of the comma issues. Jim On Jul 30, 2015 6:07 PM, "R.Smith" wrote: > > > On 2015-07-30 11:12 PM, Sylvain Pointeau wrote: > >> Le jeudi 30 juillet 2015, Simon Slavin a ?crit : >> >> Yes, as I

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

Passing NULL to xOpen()'s zName parameter opens a temp file. -scott On Thu, Jul 30, 2015 at 4:29 PM, Howard Kapustein < Howard.Kapustein at microsoft.com> wrote: > >There cannot be a fully portable way, because path specifications are not > portable > Which begs the question, why isn't there

[sqlite] ATTACH Problem

Chris Parsonson wrote: > > > The first thing I need to do with the second database is ATTACH it > to the first one. This is what I haven't been able to do successfully. > > Have you tried using a SQLiteParameter for the file name for the database to attach? -- Joe Mistachkin

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.98.0 have begun...

If you have any issues with the current code, please report them via this mailing list (and/or by creating a ticket on "https://system.data.sqlite.org/;) prior to Monday, August 3rd. Thanks. -- Joe Mistachkin

[sqlite] CSV excel import

Sylvain Pointeau writes: > > I understood from the mailing list, that CSV is not a defined format, then > let's propose another format, well defined, the Excel one (which is in my > experience a format to is good every time I had to exchange CSV files). > > Then why don't you propose an

[sqlite] shell: better handling of multi-line commands and command history

I often paste in multi-line sql statements from elsewhere directly into the sqlite shell. But when I want to run that command again later, the command history only allows me to select a single line from the command history. I suggest using the technique described here [1] to convert the