Re: [sqlite] Reset the cursor

Igor Korot wrote: > res = sqlite3_step( stmt ); > > Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 > so I can process those records again. Strictly speaking, it is not possible to go back in the _same_ cursor. You'd have to execute the query again (by calling

Re: [sqlite] Sqlite delete too slow in 4 GB database

On Sun, 3 Jun 2018 22:59:05 -0700 (MST) ayagmur escribió: > I have been using ext2 file system. I have a database which has 4 GB size. > Database consist of 1 parent table and 1 child table. Parent table has 10 > rows and child table has 4000 rows. 1 row of child table has 1 MB size. when > I

Re: [sqlite] Reset the cursor

Hi, Clemens et al, On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch wrote: > Igor Korot wrote: >> res = sqlite3_step( stmt ); >> >> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 >> so I can process those records again. > > Strictly speaking, it is not possible

Re: [sqlite] Reset the cursor

On 6/4/2018 12:31 AM, Igor Korot wrote: Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 so I can process those records again. I thought that this will be a job of sqlite_reset(), but when I called it and started re-processing the recordset I got SQLITE_DONE on the

Re: [sqlite] Sqlite delete too slow in 4 GB database

Hello, > Sqlite delete too slow in 4 GB database What does: 'pragma secure_delete;' and 'pragma auto_vacuum;' say, on that db? -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list

Re: [sqlite] Reset the cursor

Hi, On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot wrote: > Hi, Clemens et al, > > On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch wrote: >> Igor Korot wrote: >>> res = sqlite3_step( stmt ); >>> >>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 >>> so I can

Re: [sqlite] Sqlite delete too slow in 4 GB database

Have you made sure aut_ovacuum is disabled? pragma *auto_vacuum * = 0 have you got a nice large pagesize if your records are that big? Paul www.sandersonforensics.com SQLite Forensics Book On 4 June 2018 at 13:01, Olivier Mascia wrote: >

Re: [sqlite] Sqlite delete too slow in 4 GB database

If you don't already do this, you'll want to be sure the large blob(s) are the *last* fields in the table definition. On Mon, Jun 4, 2018 at 8:49 AM Paul Sanderson wrote: > Have you made sure aut_ovacuum is disabled? > > pragma *auto_vacuum * = 0 > > have you got a nice large pagesize if

[sqlite] Sqlite delete too slow in 4 GB database

I have been using ext2 file system. I have a database which has 4 GB size. Database consist of 1 parent table and 1 child table. Parent table has 10 rows and child table has 4000 rows. 1 row of child table has 1 MB size. when I delete a row in parent table, deletion cascades 1MB-sized child

Re: [sqlite] Reset the cursor

Perhaps. In order for the sqlite3_errcode(db) to have any meaning then the immediately preceding API call must have failed with an error (that is, returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE). The sqlite3_errcode is *ONLY* updated when there is an error (ie, the return

Re: [sqlite] Reset the cursor

On 4 Jun 2018, at 7:15pm, Igor Korot wrote: > Is my assumption correct that sqlite3_errcode() returning 0, indicate > there was no error? Correct. It might return 0 (SQLITE_OK == "no error") 1 to 99 (primary error code) 100 (SQLITE_ROW == "here's a row of data you asked for")

Re: [sqlite] Repair corrupt database results in 0KB file

Do you have a repeatable way to corrupt the database using the command line tool? We'd like to fix the main problem rather than fix a workaround. What are the signs of database corruption? Error messages, weird results, output of pragma integrity_check, etc? Do you have a sample corrupted

Re: [sqlite] Reset the cursor

Keith, On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf wrote: > > Perhaps. In order for the sqlite3_errcode(db) to have any meaning then the > immediately preceding API call must have failed with an error (that is, > returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE). The >

Re: [sqlite] Reset the cursor

On 4 Jun 2018, at 8:09pm, Igor Korot wrote: > The second cycle' sqlite3_step() returns 1, but immediately calling > sqlite3_errcode() returns 0. I don't like that. You should almost never see a result code of 1. It's SQLite telling you "Something went wrong but I don't know what.". It a bad

Re: [sqlite] Reset the cursor

Hi, Igor, On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik wrote: > On 6/4/2018 12:31 AM, Igor Korot wrote: >> >> Now I'd like the cursor in the recordset of the "stmt" to go to the record >> 1 >> so I can process those records again. >> >> I thought that this will be a job of sqlite_reset(), but

Re: [sqlite] Reset the cursor

x, On Mon, Jun 4, 2018 at 9:42 AM, x wrote: > int result = 0, res3 = SQLITE_OK; > for( ; ; ) > { > res3 = sqlite3_step( stmt3 ); > if( res3 == SQLITE_ROW ) > > > As I said in previous post a successful

Re: [sqlite] Reset the cursor

int result = 0, res3 = SQLITE_OK; for( ; ; ) { res3 = sqlite3_step( stmt3 ); if( res3 == SQLITE_ROW ) As I said in previous post a successful sqlite3_step doesn’t return SQLITE_OK so res3 == SQLITE_ROW is

Re: [sqlite] Reset the cursor

Sorry, I didn’t notice res3 was reassigned just before the comparison. I was confused by you setting res3 = SQLITE_OK on the first line as I can see no purpose to that. From: sqlite-users on behalf of Igor Korot Sent: Monday, June 4, 2018 3:44:57 PM To:

Re: [sqlite] Sqlite delete too slow in 4 GB database

On 4 Jun 2018, at 6:59am, ayagmur wrote: > When I try to delete 100 MB data by cascade (1 parent record - 100 child > records) it takes too long time (almost 10 minute) to complete, and the > duration increase/decrease by size of data (100 Mb: 10 minute, 300 MB: 30 > minute,etc) What medium

Re: [sqlite] Reset the cursor

x, On Mon, Jun 4, 2018 at 9:12 AM, x wrote: > Sorry, just looked that up. 1 is SQLITE_ERROR. 100 is SQLITE_ROW > > > > But note that a successful sqlite3_step does not return SQLITE_OK (0). Are > you maybe converting the result to Boolean? Please see the code I posted in reply to Igor's post.

Re: [sqlite] Repair corrupt database results in 0KB file

On 4 Jun 2018, at 11:21pm, Rael Bauer wrote: > On tree page 51 cell 1: invalid page number 302055426 Erm ... I don't know your pagesize and page count, but that looks like an incorrectly high page number to me. If you have checked that your recovery method does recover all the data you'd

Re: [sqlite] Repair corrupt database results in 0KB file

@david: I am not corrupting the database myself. Users of my application have sent in corrupted databases (on very rare occassion). I do not have permission to send you corrupted database at this time. I can tell you the following: This problem starts with v3.18. It works ok with v3.17

[sqlite] 3rd Call For Papers - 25th Annual Tcl/Tk Conference (Tcl'2018)

Hello SQLite Users, fyi ... 25th Annual Tcl/Tk Conference (Tcl'2018) http://www.tcl.tk/community/tcl2018/ October 15 - 19, 2018 Crowne Plaza Houston River Oaks 2712 Southwest Freeway, 77098 Houston, Texas, USA [ Reminder * [Registration is

[sqlite] How to avoid the fts rebuilding when add a column on fts table?

we are using FTS5 to support my business. But find it so intolerable that Every time when I wanr to add one business column which will also be added on fts table, I need to drop all the fts.table and rebulid again. Can.you help me or if you have any develop plan about this problem? Thank you!

[sqlite] PRAGMA writable_schema and schema_version: changes are ignored on the same connection

This started off as a bug report about the writable schema method recommended in https://www.sqlite.org/lang_altertable.html, but I realised the problem is a bit broader. It seems SQLite is ignoring manual changes to sqlite_master when combined with increments to schema_version. The alter table

Re: [sqlite] Reset the cursor

Works just fine for me ... #include "sqlite3.h" #include void main(int argc, char** argv) { sqlite3* db = 0; sqlite3_stmt* stmt = 0; char* rest = 0; int rc = 0; int value = 0; sqlite3_open(":memory:", ); rc = sqlite3_prepare_v2(db, "select value from generate_series

Re: [sqlite] Reset the cursor

Hi, Keith, On Mon, Jun 4, 2018 at 11:45 AM, Keith Medcalf wrote: > Works just fine for me ... > > > #include "sqlite3.h" > #include > > void main(int argc, char** argv) > { > sqlite3* db = 0; > sqlite3_stmt* stmt = 0; > char* rest = 0; > int rc = 0; > int value = 0; >

[sqlite] Repair corrupt database results in 0KB file

Hello, I having been using the following method to repair corrupt databases using sqlite3.exe: Simply call: sqlite3 mydata.db ".dump" | sqlite3 new.db (source: https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database) I have been using an old version of

Re: [sqlite] Reset the cursor

>Currently running w/MSVC 2010 under Win 8.1. >I also presume you are testing under the latest SQLite source? Yes, I believe so ... SQLite 3.24.0 2018-06-02 19:14:58 1ecb3aa13de5c8dc611b814ff34010de0bd90aae73d88aa37a59c4627be4alt2 Using GCC (MinGW-w64 8.1.0) on Windows 10 Pro for Workstations

Re: [sqlite] Repair corrupt database results in 0KB file

On 4 Jun 2018, at 6:00pm, Rael Bauer wrote: > Now I updated to the latest version (3.23.1), Which did you update ? The command-line tool you call as sqlite3, or the library used in the program which uses the database file ? Or both ? > and using the above method results in a 0 KB file.

Re: [sqlite] Reset the cursor

Igor, if sqlite3_step is successful it returns SQLITE_ROW which is 1. From: sqlite-users on behalf of Igor Korot Sent: Monday, June 4, 2018 1:52:05 PM To: SQLite mailing list Subject: Re: [sqlite] Reset the cursor Hi, On Mon, Jun 4, 2018 at 7:54 AM, Igor

Re: [sqlite] Reset the cursor

Sorry, just looked that up. 1 is SQLITE_ERROR. 100 is SQLITE_ROW But note that a successful sqlite3_step does not return SQLITE_OK (0). Are you maybe converting the result to Boolean? From: sqlite-users on behalf of Igor Korot Sent: Monday, June 4, 2018

Re: [sqlite] "cursored" queries and total rows

I'm using also paged queries. I'm adding an OFFSET to the select-limit query. Works for me. Am 03.06.2018 um 14:16 schrieb R Smith: On 2018/06/03 1:13 PM, Wout Mertens wrote: Hi all, To do paged queries on a query like SELECT colVal FROM t WHERE b=? LIMIT 10 I keep track of column

Re: [sqlite] Reset the cursor

If the first loop exits with res3 == SQLITE_DONE then !result will be true and the second loop should process exactly the same (assuming underlying data is unchanged). I can’t see why the code below wouldn’t work although I’m confused by the fact you say that sqlite3_step(stmt3) returns

Re: [sqlite] Reset the cursor

I'm using also paged queries. I'm adding an OFFSET to the select-limit query. Works for me. Am 04.06.2018 um 17:54 schrieb x: If the first loop exits with res3 == SQLITE_DONE then !result will be true and the second loop should process exactly the same (assuming underlying data is

Re: [sqlite] Reset the cursor

ooops... last answer mail was on the wrong topic. Am 04.06.2018 um 17:57 schrieb heribert: I'm using also paged queries. I'm adding an OFFSET to the select-limit query. Works for me. Am 04.06.2018 um 17:54 schrieb x: If the first loop exits with res3 == SQLITE_DONE then !result will be true

Re: [sqlite] Reset the cursor

x, On Mon, Jun 4, 2018 at 10:54 AM, x wrote: > If the first loop exits with res3 == SQLITE_DONE then !result will be true > and the second loop should process exactly the same (assuming underlying data > is unchanged). I can’t see why the code below wouldn’t work although I’m > confused by

Re: [sqlite] Reset the cursor

Note also that you do not need to do an sqlite3_reset after sqlite3_step returns SQLITE_DONE as reset is called automatically the next time you call sqlite3_step. You only need to call sqlite3_reset if you want to reset the statement before all the rows have been retrieved (this is documented

Re: [sqlite] Repair corrupt database results in 0KB file

@Simon: I did not mention any sqlite3.dll. I am only talking about sqlite3.exe command line tool. The problem occurs using the command line. Yes, I tried your idea. The last line in the dump using the old version is: COMMIT; The last line in the dump using the latest version (3.23.1) is:

Re: [sqlite] Reset the cursor

#include "sqlite3.h" #include void main(int argc, char** argv) { sqlite3* db = 0; sqlite3_stmt* stmt = 0; char* rest = 0; int rc = 0; int value = 0; sqlite3_open(":memory:", ); rc = sqlite3_prepare_v2(db, "select value from generate_series where start=1 and

Re: [sqlite] Repair corrupt database results in 0KB file

On 4 Jun 2018, at 6:32pm, Rael Bauer wrote: > The last line in the dump using the latest version (3.23.1) is: > > ROLLBACK; -- due to errors Then there are errors, and the data in your database may be corrupt, and the information you're getting from it can be wrong. Use the command-line tool

Re: [sqlite] Reset the cursor

Keith, On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf wrote: > > #include "sqlite3.h" > #include > > void main(int argc, char** argv) > { > sqlite3* db = 0; > sqlite3_stmt* stmt = 0; > char* rest = 0; > int rc = 0; > int value = 0; > sqlite3_open(":memory:", ); > rc

Re: [sqlite] Reset the cursor

Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE error on the 3rd loop because the statement was not reset. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated

Re: [sqlite] Reset the cursor

Keith, On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf wrote: > > Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was compiled with > SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE error on the 3rd loop > because the statement was not reset. One more thing: Is my assumption