[sqlite] CSV excel import

Le jeudi 30 juillet 2015, Simon Slavin a ?crit : > > 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

[sqlite] Strange behaviour of sqlite3_stmt_busy

gwenn wrote: > sqlite3_stmt_busy returns true after sqlite3_step returns DONE. The documentation says: | The sqlite3_stmt_busy(S) interface returns true (non-zero) if the | prepared statement S has been stepped at least once using | sqlite3_step(S) but has not run to completion and/or has not

[sqlite] CSV excel import

On 2015-07-30 11:12 PM, Sylvain Pointeau wrote: > Le jeudi 30 juillet 2015, Simon Slavin a ?crit : > >> Yes, as I wrote, this bad behaviour (which you could reasonably call a >> bug) is documented. That's how Microsoft wrote Excel and that's the way >> they want it to work, and that's how it

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

>There cannot be a fully portable way, because path specifications are not >portable Which begs the question, why isn't there an xGetTempFilename VFS function in sqlite3_vfs? Wouldn't the simplify things? Have the VFS handle it apropos, with the default VFS' in SQLite (or an older VFS lacking

[sqlite] CSV excel import

Moving data from Excel directly to SQLite seems the best option to me as all the values are clearly separated. No need for XML. An Excel sheet range can be directly converted to a variant array and from there it is simple and fast to move the data to SQLite. I have written an Excel add-in that

[sqlite] CSV excel import

On 31 Jul 2015, at 1:32am, Bart Smissaert wrote: > Moving data from Excel directly to SQLite seems the best option to me as > all the values are clearly separated. > No need for XML. An Excel sheet range can be directly converted to a > variant array and from there it is simple and > fast to

[sqlite] Thanks SQLite

I dare to add my thanks here, with a much simpler example. Initially, for me, CTE was another thing to learn. Then I wanted SQLite to compute statistics on a simple two-column table. Not a big deal, I typed the equation and was done. Next day, I needed the same equation to be applied to

[sqlite] ATTACH Problem

Hi mailinglists, Please help. Good Day SQLite Mailing List, I have a VB.NET 2008 program running on Windows 7 which populates an SQLite3 database. After updates are made I need to populate a second SQLite database with some of the tables in the master database (by pressing a button). As there

[sqlite] ATTACH Problem

No, but I can do that On 31 July 2015 at 06:41, Joe Mistachkin wrote: > > 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

[sqlite] ATTACH Problem

The only parameters I can put in ar the DB name in the ATTACH and then the table name in the DELETE. Anyway it fails on the ATTACH SQLStmt = "ATTACH @DBName AS UPD" Dim dbcommand As SQLiteCommand = dbConnection.CreateCommand With dbcommand

[sqlite] ATTACH Problem

On 31 Jul 2015, at 5:32am, Chris Parsonson wrote: >SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD" This is not the correct syntax for the ATTACH command. The name of the file to be attached is not a value, it's just included in the command. I would expect you to have better

[sqlite] ATTACH Problem

That's exactly what I started with, then somebody suggested using a parameter On 31 July 2015 at 09:02, Simon Slavin wrote: > > On 31 Jul 2015, at 5:32am, Chris Parsonson wrote: > > >SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD" > > This is not the correct syntax for the ATTACH

[sqlite] ATTACH Problem

Chris, you have an extra pair of single quotes in your original code compared to Simon's suggestion. Also in your parameter-based attempt you have a typo when assigning the path (@DNName instead of @DBName). Hope that helps, -Rowan On 31 July 2015 at 15:09, Chris Parsonson wrote: > That's

[sqlite] ATTACH Problem

Not quite. Try printing the generated statement. It should read ATTACH myfilepath AS UPD; Intead of ATTACH 'myfilepath' AS UPD; -Urspr?ngliche Nachricht- Von: Chris Parsonson [mailto:z2668856 at gmail.com] Gesendet: Freitag, 31. Juli 2015 09:10 An: General Discussion of SQLite

[sqlite] ATTACH Problem

Thanks, Here is my code again, hopefully with typing errors corrected. The attach and the delete run without generating any errors, but the delete does not work On 31 July 2015 at 09:25, Rowan Worth wrote: > Chris, you have an extra pair of single quotes in your original code > compared to

[sqlite] ATTACH Problem

Forgot to add the code Public Function SynchroniseDB() As Boolean Dim dbConnection As New SQLiteConnection Dim dbDataSet As New DataSet Dim SQLStmt As String Dim sDBPath As String = Application.StartupPath & "\wolfpro.db3" Dim sUpdateDBPath As String =

[sqlite] CSV excel import

Never considered that option but I think it will be a lot slower. Currently I work with these 2: https://sqliteforexcel.codeplex.com/ http://www.vbrichclient.com/#/en/About/ RBS On Fri, Jul 31, 2015 at 1:47 AM, Simon Slavin wrote: > > On 31 Jul 2015, at 1:32am, Bart Smissaert > wrote: > > >

[sqlite] ATTACH Problem

If I leave out the apostrophes then because the path to the database contains 'C:' in it I get an SQL error saying the ':' is unknown On 31 July 2015 at 09:26, Hick Gunter wrote: > Not quite. Try printing the generated statement. It should read > > ATTACH myfilepath AS UPD; > > Intead of > >

[sqlite] ATTACH Problem

If I leave out the apostrophes then because the path to the DB contains 'C:\' I get an SQL error 'unknown character :' On 31 July 2015 at 09:26, Hick Gunter wrote: > Not quite. Try printing the generated statement. It should read > > ATTACH myfilepath AS UPD; > > Intead of > > ATTACH

[sqlite] Strange behaviour of sqlite3_stmt_busy

On Thu, Jul 30, 2015 at 11:35 PM, Clemens Ladisch wrote: > gwenn wrote: > > sqlite3_stmt_busy returns true after sqlite3_step returns DONE. > > The documentation says: > | The sqlite3_stmt_busy(S) interface returns true (non-zero) if the > | prepared statement S has been stepped at least once

[sqlite] Thanks SQLite

On 07/30/2015 01:32 AM, Richard Hipp wrote: > On 7/29/15, R.Smith wrote: >> >> I just wish to ... display my gratitude ... for ... CTE's. >> > > Glad you find them useful. > > I'm looking for real-world (open-source) use cases for CTEs. Please > send me links find any. We use CTE in Skylable

[sqlite] Read strings as they were

Yes, I am stupid... My example was as I thought it works. In fact there was one more step. CREATE TABLE IF NOT EXISTS 'test' (`testcol2` INTEGER); ALTER TABLE 'test' ADD COLUMN `testcol` float; INSERT INTO 'test' (`testcol`) VALUES (''); SELECT * from 'test'; Yes, I know that table was

[sqlite] Strange behaviour of sqlite3_stmt_busy

SQLITE_DONE means that there are no (more) rows to be retrieved. -Urspr?ngliche Nachricht- Von: Stephan Beal [mailto:sgbeal at googlemail.com] Gesendet: Freitag, 31. Juli 2015 10:12 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Strange behaviour of sqlite3_stmt_busy On

[sqlite] Strange behaviour of sqlite3_stmt_busy

On Fri, Jul 31, 2015 at 10:51 AM, Hick Gunter wrote: > SQLITE_DONE means that there are no (more) rows to be retrieved. > So that's the difference (for a SELECT) between that and "running to completion"? i think that's the source of the confusion. -- - stephan beal

[sqlite] datetime result help

On Thu, Jul 30, 2015 at 10:59 PM, jose isaias cabrera < jicman at cinops.xerox.com> wrote: > Ok, I have one more option for you, and since you think it's fun, I need > to exclude Saturdays and Sundays. I have a hack in the programming side of > things, but I would like to do it right from

[sqlite] ATTACH Problem

On Fri, Jul 31, 2015 at 3:38 AM, Chris Parsonson wrote: > Forgot to add the code > Public Function SynchroniseDB() As Boolean > Dim dbConnection As New SQLiteConnection > Dim dbDataSet As New DataSet > Dim SQLStmt As String > Dim sDBPath As String =

[sqlite] Strange behaviour of sqlite3_stmt_busy

Stephan Beal wrote: > On Thu, Jul 30, 2015 at 11:35 PM, Clemens Ladisch > wrote: >> gwenn wrote: >>> sqlite3_stmt_busy returns true after sqlite3_step returns DONE. >> >> The documentation says: >> | The sqlite3_stmt_busy(S) interface returns true (non-zero) if the >> | prepared statement S has

[sqlite] ATTACH Problem

Dunno but I changed it and ran the code. It goes through without error but the DELETE still has not worked On 31 July 2015 at 11:36, Kevin Benson wrote: > On Fri, Jul 31, 2015 at 3:38 AM, Chris Parsonson > wrote: > > > Forgot to add the code > > Public Function SynchroniseDB() As Boolean

[sqlite] Thanks SQLite

I have also used CTE so that I could generate a "report" just using SQL. Something like (simplified): WITH summary AS ( SELECT var, count(var) as total FROM table GROUP BY key ) SELECT a.var, 100*a.var/b.total as percentage FROM table AS a INNER JOIN summary AS b ON a.key=b.key ORDER BY key ;

[sqlite] Thanks SQLite

On 31 Jul 2015, at 1:26pm, John McKown wrote: > WITH summary AS > ( SELECT var, count(var) as total FROM table GROUP BY key ) > SELECT a.var, 100*a.var/b.total as percentage FROM table AS a > INNER JOIN summary AS b ON a.key=b.key > ORDER BY key Thank you at last for an example of

[sqlite] datetime result help

On 2015-07-30 10:59 PM, jose isaias cabrera wrote: > > Ok, I have one more option for you, and since you think it's fun, I > need to exclude Saturdays and Sundays. I have a hack in the > programming side of things, but I would like to do it right from > SQLite. Thougths? WITH

[sqlite] Thanks SQLite

On 2015-07-31 02:40 PM, Simon Slavin wrote: > > I am noting an overlap with the things SQLite users have been using > sub-SELECTs for. Quite, with the added advantage that you can reference the same CTE multiple times in later parts of the query, plus have two or more different aggregations

[sqlite] Thanks SQLite

On Fri, Jul 31, 2015 at 7:40 AM, Simon Slavin wrote: > > On 31 Jul 2015, at 1:26pm, John McKown > wrote: > > > WITH summary AS > > ( SELECT var, count(var) as total FROM table GROUP BY key ) > > SELECT a.var, 100*a.var/b.total as percentage FROM table AS a > > INNER JOIN summary AS b ON

[sqlite] CSV excel import

I use the Ruby scripting language and its abundance of libraries to read excel files then write the output to sqlite. Those libraries, whether they r/w csv or excel files are robust, sometime robust beyond belief. They are likely more robust than what applications developers write into their

[sqlite] FW: SQLite 4 compilation errors in Windows

I have recently downloaded SQLite4 sources and successfully produced both SQLite4 binary and SQLlite4.c/h sources for Ubuntu 15.04 using gcc 4.9.2. I used the following sources http://www.sqlite.org/src4/vinfo?name=90c09b04513a4087

[sqlite] Thanks SQLite

On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin wrote: > > I am noting an overlap with the things SQLite users have been using > sub-SELECTs for. > > Here is a self contained example that can be fed into the sqlite3 command line tool. It uses WITH to factor out the subquery; however, the annoying

[sqlite] bug in query optimizer

I have a table, a partial index and a query, which (somewhat simplified) are: create table Objs (Seqnum int primary key, Status, Timeout int) without rowid; create index DIndex on Objs (Seqnum) where Status == 0; select Seqnum from Objs where Status == 0 and strftime('%s','now') >= Timeout

[sqlite] bug in query optimizer

On 7/31/15, Allen wrote: > I have a table, a partial index and a query, which (somewhat simplified) > are: > > create table Objs (Seqnum int primary key, Status, Timeout int) without > rowid; > > create index DIndex on Objs (Seqnum) where Status == 0; > > select Seqnum from Objs where Status == 0

[sqlite] Thanks SQLite

On Fri, Jul 31, 2015 at 8:40 AM, Gabor Grothendieck wrote: > > > Here is a self contained example that can be fed into the sqlite3 command > line tool. It uses WITH to factor out the subquery; however, the annoying > part about it is that even though we have factored out the subquery we > still

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

Update: I don't have a problem compiling under centos 7 (gcc 4.8.3), but with centos 6 (gcc 4.4.7) I do get this error. fts5_main.c:30: error: redefinition of typedef 'Fts5Global' fts5Int.h:83: note: previous declaration of 'Fts5Global' was here Unfortunately, I still have to support centos 6.

[sqlite] Thanks SQLite

On 2015-07-31 03:40 PM, Gabor Grothendieck wrote: > On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin wrote: > >> I am noting an overlap with the things SQLite users have been using >> sub-SELECTs for. >> >> > Here is a self contained example that can be fed into the sqlite3 command > line tool.

[sqlite] bug in query optimizer

Dear Dr. Hipps My bug report could not have been more clear. (And so is your response, BTW--I will not waste my time sending you bug reports in the future). Best Regards.

[sqlite] Tomcat 6 consumes too much memorys

The same query and database are fine if running from the command line sqlite3 or from java command line (via jdbc). Any one has the same issue please? Thanks a lot in advance -- James Qian Wang Mobile: 44 7986 099 233

[sqlite] Thanks SQLite

Both the solutions transformed the correlated subquery into a join prior to forming the CTE. Can we conclude, in general, that CTEs do not support correlated subqueries? On Fri, Jul 31, 2015 at 11:30 AM, R.Smith wrote: > > > On 2015-07-31 03:40 PM, Gabor Grothendieck wrote: > >> On Fri, Jul

[sqlite] Thanks SQLite

On 7/31/15, Gabor Grothendieck wrote: > Can we conclude, in general, that CTEs do not support > correlated subqueries? > For a recursive CTE, the recursive table reference must occur exactly once at the top-level, never in a subquery. Other than that, there are no restrictions on the use of

[sqlite] Strange behaviour of sqlite3_stmt_busy

Sorry, I misread the documentation. And do you recommend the strategy used in tclsqlite.c: rcs = sqlite3_step(pStmt); if( rcs==SQLITE_ROW ){ return TCL_OK; } ... rcs = sqlite3_reset(pStmt); ... Reset the stmt as soon as possible after sqlite3_step

[sqlite] Strange behaviour of sqlite3_stmt_busy

On 31 Jul 2015, at 6:03pm, gwenn wrote: > And do you recommend the strategy used in tclsqlite.c: > > rcs = sqlite3_step(pStmt); > if( rcs==SQLITE_ROW ){ >return TCL_OK; > } > ... > rcs = sqlite3_reset(pStmt); > ... > > > Reset the stmt as soon as

[sqlite] sqlite 3.8.11.0 will be in next Windows release of Python3.4 / 3.5

Hi all, Just a link to this commit: http://bugs.python.org/msg247538 Regards,

[sqlite] Thanks SQLite

On 2015-07-31 06:22 PM, Gabor Grothendieck wrote: > Both the solutions transformed the correlated subquery into a join prior to > forming the CTE. Can we conclude, in general, that CTEs do not support > correlated subqueries? Slow down please! - Your example did work and did get the correct

[sqlite] bug in query optimizer

On 2015-07-31 06:16 PM, Allen wrote: > Dear Dr. Hipps > > My bug report could not have been more clear. (And so is your response, > BTW--I will not waste my time sending you bug reports in the future). If I may - there is no reason to be dismayed. The suggestion for optimization seems a great

[sqlite] Strange behaviour of sqlite3_stmt_busy

On 7/30/15, gwenn wrote: > Hello, > sqlite3_stmt_busy returns true after sqlite3_step returns DONE. Fixed at https://www.sqlite.org/src/info/047d3475e93d08cf -- D. Richard Hipp drh at sqlite.org

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

On Thu, 30 Jul 2015 23:29:49 + Howard Kapustein wrote: > >There cannot be a fully portable way, because path specifications > >are not portable > Which begs the question, why isn't there an xGetTempFilename VFS > function in sqlite3_vfs? > > Wouldn't the simplify things? Have the VFS handle

[sqlite] CSV excel import

> =CONCATENATE("INSERT INTO myTable VALUES (",a1,","a2");") This is so neat. Supposing you are not migrating from a spreadsheet because it got too big (millions of rows will take a time for this to finish) and that your data respects a logical grouping, this is very handy.

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-30 10:59 PM, jose isaias cabrera wrote: >> >> Ok, I have one more option for you, and since you think it's fun, I need >> to exclude Saturdays and Sundays. I have a hack in the programming side >> of things, but I would like to do it right from SQLite.

[sqlite] Bug in sqlite3_trace/trigger/delete

Hello? ! ? I'm using sqlite for a project and with this specific database https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB uncompressed) this is happening: ? -1 Registering an sqlite3_trace function when trying to delete a record just inserted on the table "res_users" the registered