[sqlite] Problem with insert

I have a file named "stock.txt" with datas seperated by "|" symbol.I want insert these datas into a table named "stock".How can i do this in c(in linux) with sqlite. Please help me... -- With Regards, *Bageesh.M.Bose* ** ___ sqlite-users mailing list

Re: [sqlite] db->pVtabCtx and xCreate accessing a virtual table

Richard Hipp writes: > On Thu, Apr 26, 2012 at 5:07 PM, Steinar Midtskogen > wrote: > >> My xCreate has to run some queries in order to build its declare >> statement. If a virtual table is queried inside xCreate causing a >> another xCreate to be called,

[sqlite] Mail ID

bageesh...@gmail.com -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the xRead method in sqlite3_io_methods

Ok, thanks for the reply :-) -- Best Regards BaiYang baiy...@263.net.cn http://baiy.cn < END OF EMAIL > From: Pavel Ivanov Date: 2012-04-27 09:03 To: General Discussion of SQLite Database Subject: Re: [sqlite] the xRead method in sqlite3_io_methods > 1. The sqlite engine will

Re: [sqlite] Query Planner Bug With Simple Reproduction on 3.7.11

On Thu, Apr 26, 2012 at 7:05 PM, Kyle McKay wrote: > Credit to Pete Hardman who posted the original thread: > > > http://thread.gmane.org/gmane.**comp.db.sqlite.general/73931 > > Here's a much simpler reproduction

Re: [sqlite] the xRead method in sqlite3_io_methods

> 1. The sqlite engine will discard ALL of the partially read data returned by > xRead? > For example, if the sqlite engine request 2096 bytes and xRead only got 1234 > bytes, does the engine will ALWAYS discard the 1234 bytes of data ? > i.e: SQLITE_IOERR_SHORT_READ constantly means "no data"

Re: [sqlite] the xRead method in sqlite3_io_methods

> All these codes are dealing with the header? They are not about header but they are essentially dealing with fatal errors. First example is (as comment says) executed when database tries to recover after a crashed process. And short read here means that journal is corrupted and should be

Re: [sqlite] the xRead method in sqlite3_io_methods

And according to the official document: If xRead() returns SQLITE_IOERR_SHORT_READ it must also fill in the unread portions of the buffer with zeros. A VFS that fails to zero-fill short reads might seem to work. However, failure to zero-fill short reads will eventually lead to database

Re: [sqlite] the xRead method in sqlite3_io_methods

> It is a fatal error. The only time it is okay is after the file is first > created (zero length) ... Really? But I have seen many location seems not like you sad. For example: In pager_playback: }else if( rc==SQLITE_IOERR_SHORT_READ ){ /* If the journal has been truncated,

Re: [sqlite] the xRead method in sqlite3_io_methods

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 26/04/12 15:27, BaiYang wrote: > I'm confused, does you mean a xRead should NEVER return a > "SQLITE_IOERR_SHORT_READ" ? It is a fatal error. The only time it is okay is after the file is first created (zero length) and SQLite tries to read the

[sqlite] Query Planner Bug With Simple Reproduction on 3.7.11

Credit to Pete Hardman who posted the original thread: http://thread.gmane.org/gmane.comp.db.sqlite.general/73931 Here's a much simpler reproduction of the query planner bug: $ /var/tmp/sqlite3 testview.sq3 SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL

Re: [sqlite] db->pVtabCtx and xCreate accessing a virtual table

On Thu, Apr 26, 2012 at 5:07 PM, Steinar Midtskogen wrote: > The sqlite3 struct has a pVtabCtx pointer. It seems to me that it > will be shared between multiple xCreate in action simultaniously and > in that case cause disaster. This can happen even if there is no >

Re: [sqlite] the xRead method in sqlite3_io_methods

> If your internal code results in less data being read then you'll need to > keep repeating until you have the amount requested. I'm confused, does you mean a xRead should NEVER return a "SQLITE_IOERR_SHORT_READ" ? -- Best Regards BaiYang baiy...@263.net.cn http://baiy.cn < END OF

Re: [sqlite] the xRead method in sqlite3_io_methods

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 26/04/12 14:54, BaiYang wrote: > The new 'piRealAmt' could be used to report the actual read data, Why? The requirements are that you read the amount of data requested or return an error. If your internal code results in less data being read

[sqlite] the xRead method in sqlite3_io_methods

We should add a new output argument for xRead method which is in sqlite3_io_methods structure like this: int (*xRead)(sqlite3_file*, void*, int iAmt, sqlite3_int64 iOfst, int* piRealAmt); The new 'piRealAmt' could be used to report the actual read data, this argument will greatly boost the

Re: [sqlite] EXT :Re: Re Query planner creating a slow plan

I must be blind (something my wife would agree with)...but I did use "order by transfer_date"the "asc" making no difference. I showed you mine...you show me yours (on the same database please). sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE regn_no =

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

On Thu, Apr 26, 2012 at 5:15 PM, Brad House wrote: > On 04/26/2012 04:38 PM, Jos Groot Lipman wrote: > >> As far as I understand this means: you will not see changes made by other >> connections (committed or uncommited) after your transaction started. >> If another connections

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

On 04/26/2012 04:38 PM, Jos Groot Lipman wrote: As far as I understand this means: you will not see changes made by other connections (committed or uncommited) after your transaction started. If another connections commits a change, you will not see it. I would expect: If another connections

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

On 04/26/2012 05:11 PM, Richard Hipp wrote: On Thu, Apr 26, 2012 at 4:38 PM, Jos Groot Lipman wrote: From the docs: 'The default isolation level for SQLite is SERIALIZABLE' As far as I understand this means: you will not see changes made by other connections (committed or

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

On Thu, Apr 26, 2012 at 4:38 PM, Jos Groot Lipman wrote: > From the docs: 'The default isolation level for SQLite is SERIALIZABLE' > > As far as I understand this means: you will not see changes made by other > connections (committed or uncommited) after your transaction

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 21:39: My fault...I thought I had extracted it under another name...turns out I was using a different db...duh... Looks like it uses the indexes just fine. I'm using the Window's EXE from the website. Also got the same result on Unix. Did you

[sqlite] db->pVtabCtx and xCreate accessing a virtual table

The sqlite3 struct has a pVtabCtx pointer. It seems to me that it will be shared between multiple xCreate in action simultaniously and in that case cause disaster. This can happen even if there is no thread concurrency going on using the same database connection. My xCreate has to run some

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 21:39: My fault...I thought I had extracted it under another name...turns out I was using a different db...duh... Well we're none of us perfect. Only the female of the species can do more than two things at once... Yes, that query is fine. But add

[sqlite] Re Query planner creating a slow plan

My fault...I thought I had extracted it under another name...turns out I was using a different db...duh... Looks like it uses the indexes just fine. I'm using the Window's EXE from the website. Also got the same result on Unix. Did you compile your own? Or did you check to see that the

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

>From the docs: 'The default isolation level for SQLite is SERIALIZABLE' As far as I understand this means: you will not see changes made by other connections (committed or uncommited) after your transaction started. If another connections commits a change, you will not see it. I would expect: If

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

On 04/26/2012 04:09 PM, Black, Michael (IS) wrote: Would WAL mode prevent this? We're using WAL mode :) -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

On 04/26/2012 03:44 PM, Richard Hipp wrote: On Thu, Apr 26, 2012 at 3:34 PM, Brad House wrote: I've got 2 threads with different connections to the same database. This is approximately what I am seeing: Thread 1: SELECT bar,baz FROM foo WHERE ...; Thread 2: BEGIN

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 19:00: Sqliteman must be pointing to the wrong database. sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '039540'. Error: no such table: transfer_history Hmm. I've just done the following - cut & paste from my terminal:

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

Would WAL mode prevent this? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

On Thu, Apr 26, 2012 at 3:34 PM, Brad House wrote: > > I've got 2 threads with different connections to the same database. > > This is approximately what I am seeing: > Thread 1: SELECT bar,baz FROM foo WHERE ...; > Thread 2: BEGIN IMMEDIATE TRANSACTION; >

[sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

We just noticed a behavioral change in SQLite 3.7.11 that caused a regression in our software. This change did not exist in 3.7.10. Looking back at the changelog, I notice this: "Pending statements no longer block ROLLBACK. Instead, the pending statement will return SQLITE_ABORT upon next

Re: [sqlite] Re Query planner creating a slow plan

Sqliteman must be pointing to the wrong database. sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '039540'. Error: no such table: transfer_history Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop

[sqlite] Two errors compiling for Windows CE

Hello, I have tried to generate the executable "sqlite3.exe" appropiate for Windows CE (ARM processor). I used the CeGcc compiler from http://cegcc.sourceforge.net ("cegcc_mingw32ce_cygwin1.7_r1399.tar.bz2" or "cegcc_mingw32ce_cygwin1.7_r1375.tar.bz2"). I executed the compiler under Cygwin

Re: [sqlite] sqlite3 column widths

Hi TIm, sqlite3 already includes the ability to define the width manually with the .width command. I was hoping that there might be a way to use the defined string length as part of the default when .width is not used. Pete On Thu, Apr 26, 2012 at 9:00 AM,

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 18:21: There is no transfer_table_new in that database. Or any view named "transfer" anything. So what query are you running on this one? Sorry, that should be transfer_history. The name seems to have got corrupted during our email exchanges. I've

Re: [sqlite] Re Query planner creating a slow plan

There is no transfer_table_new in that database. Or any view named "transfer" anything. So what query are you running on this one? Here's all the VIEWs. CREATE VIEW current_flock_owner AS SELECT latest.flock_no, latest.owner_person_id, latest.ow FROM flock_owner even_later WHERE

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 17:05: What version are you using? Can you extract an example of all your tables/indexes/data to demonstrate? I'm on 3.7.11 (Arch-Linux). You can grab a database at http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip You will need to use a

Re: [sqlite] Re Query planner creating a slow plan

What version are you using? Can you extract an example of all your tables/indexes/data to demonstrate? One more idea. Explicitly ask for the indexes...and a compound index might be nice to have around. I dont' think you can ask for more than one index without doing subselects, can you?

Re: [sqlite] Re Query planner creating a slow plan

Simon Slavin wrote, On 26/04/12 16:39: On 26 Apr 2012, at 4:24pm, Peter wrote: > There are indexes on all the fields used in the tables of the transfer_history view. > > While tinkering I have discovered something: > > If instead of > SELECT transfer_date

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 16:38: And does this also work? Sounds like the planner isn't seeing all the columns in the view unless in the select. SELECT transfer_date,regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date<= '2012-05-01' order by

Re: [sqlite] Re Query planner creating a slow plan

Marc L. Allen wrote, On 26/04/12 16:38: Out of curiosity, try... SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date<= '2012-05-01' order by transfer_date asc Is the problem that combining the order by with having transfer_date as the only

Re: [sqlite] Re Query planner creating a slow plan

On 26 Apr 2012, at 4:24pm, Peter wrote: > There are indexes on all the fields used in the tables of the > transfer_history view. > > While tinkering I have discovered something: > > If instead of > SELECT transfer_date FROM transfer_history_new > WHERE regn_no

Re: [sqlite] Re Query planner creating a slow plan

And does this also work? Sounds like the planner isn't seeing all the columns in the view unless in the select. SELECT transfer_date,regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date <= '2012-05-01' order by transfer_date asc Michael D. Black Senior Scientist

Re: [sqlite] Re Query planner creating a slow plan

Out of curiosity, try... SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date <= '2012-05-01' order by transfer_date asc Is the problem that combining the order by with having transfer_date as the only returned item make it use the transfer_date

Re: [sqlite] Re Query planner creating a slow plan

Marc L. Allen wrote, On 26/04/12 15:57: What indexes are on the underlying tables? There are indexes on all the fields used in the tables of the transfer_history view. While tinkering I have discovered something: If instead of SELECT transfer_date FROM transfer_history_new WHERE regn_no =

Re: [sqlite] Re Query planner creating a slow plan

What indexes are on the underlying tables? > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Peter > Sent: Thursday, April 26, 2012 10:55 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Re Query

Re: [sqlite] Re Query planner creating a slow plan

Jim Morris wrote, On 26/04/12 15:36: It is possible using an alias would force better behavior: selsect theDate from (select transfer_date as theDate from transfer_history where regn_no='039540' and transfer_date<= '2012-05-01') order by theDate Once again, it makes no difference - the

Re: [sqlite] Re Query planner creating a slow plan

Marc L. Allen wrote, On 26/04/12 15:30: I suppose using a temporary table is out of the question? It's much simpler (and probably quicker though I didn't test it) to read the three rows, sort them by date, pick the first row and use that. No need to generate random table names, create and

Re: [sqlite] Re Query planner creating a slow plan

It is possible using an alias would force better behavior: selsect theDate from (select transfer_date as theDate from transfer_history where regn_no='039540' and transfer_date<= '2012-05-01') order by theDate ___ sqlite-users mailing list

Re: [sqlite] Re Query planner creating a slow plan

I suppose using a temporary table is out of the question? But, then again, that only solves the specific issue. I guess the more general question is how views with unions interact with aggregates and order by. What happens if you don't use the view, but perform the query using the actual

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

On Thu, Apr 26, 2012 at 10:09 AM, Stephan Beal wrote: > On Thu, Apr 26, 2012 at 4:05 PM, Pavel Ivanov wrote: >> amalgamation. If yes then these errors fall under the "unsupported >> options" statement Stephan points you to. But probably SQLite team >>

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 15:11: select transfer_date from (select transfer_date from transfer_history where regn_no='039540' and transfer_date<= '2012-05-01') order by transfer_date; Makes no difference. Pete -- Peter Hardman ___

[sqlite] Re Query planner creating a slow plan

What happens if you use a subselect? selsect transfer_date from (select transfer_date from transfer_history where regn_no='039540' and transfer_date <= '2012-05-01') order by transfer_date; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

On Thu, Apr 26, 2012 at 4:05 PM, Pavel Ivanov wrote: > amalgamation. If yes then these errors fall under the "unsupported > options" statement Stephan points you to. But probably SQLite team > will fix this in some future release if they have some spare time and > the fix is

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

> To clarify, the below steps to reproduce include building the amalgamation > from scratch, and the errors I demonstrate occur after building and > compiling a new amalgamation with -DSQLITE_OMIT_ALTERTABLE and > -DSQLITE_OMIT_FOREIGN_KEY. It's still not clear, did you define these OMITs while

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

On Thu, Apr 26, 2012 at 1:11 AM, nn6eumtr wrote: > In Stepheen Beal's earlier comments he appears to have not realized that I > was rebuilding the amalgamation and not trying to use the -DSQLITE_OMIT_* > flags with the distributed amalgamation. I still need some assistance.

Re: [sqlite] catastrophic installation on Ubuntu 11.10

Hello Richard How I did it: ./configure make make install I join the config.log. If it is not the file you need, please tell me what to do, so I can make a new one. Best Regards Jean-Christophe Le 26-04-2012 13:04, Richard Hipp a écrit : On Thu, Apr 26, 2012 at 2:37 AM,

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

To clarify, the below steps to reproduce include building the amalgamation from scratch, and the errors I demonstrate occur after building and compiling a new amalgamation with -DSQLITE_OMIT_ALTERTABLE and -DSQLITE_OMIT_FOREIGN_KEY. In Stepheen Beal's earlier comments he appears to have not

[sqlite] Query planner creating a slow plan

Hi, I have a view 'transfer_history' which aggregates records from 3 tables using UNION ALL. the aggregate is about 102k records. I have a query: SELECT transfer_date from transfer_history WHERE regn_no = '039540' and transfer_date <= '2012-05-01' This returns three records and takes a

Re: [sqlite] Logging of sqlite3 commands

Richard, This is excellent news. Thank you so much for such a wonderful tool and support. Best regards, Champ Lee On Wed, Apr 25, 2012 at 8:47 PM, Richard Hipp wrote: > On Wed, Apr 25, 2012 at 4:59 PM, Champ Lee wrote: > > > Hello List, > > > > I am

Re: [sqlite] catastrophic installation on Ubuntu 11.10

On Thu, Apr 26, 2012 at 2:37 AM, Jean-Christophe wrote: > Hello, > > I have to tell that I didn't knew SQLite at all when I started this! > On an Ubuntu 11.10, I have installed: > command line interface for SQLite > Navigateur de bases de données SQLite > Sqliteman > >

[sqlite] catastrophic installation on Ubuntu 11.10

Hello, I have to tell that I didn't knew SQLite at all when I started this! On an Ubuntu 11.10, I have installed: command line interface for SQLite Navigateur de bases de données SQLite Sqliteman Afterwards, I have downloaded the last version from your website and install it with make install.