On Wed, Mar 26, 2014 at 9:47 PM, Muharrem Bilgin (Bright Software) < mbil...@brightsoft.com.au> wrote:
> Hi Joe, > > The following are the log entries, which confirm your suspicion (the log > entry format : time, error description, error code). > > As suggested, we are already setting the temp store directory as soon as a > database connection is opened (after sqlite3_open16). Now, the question is > when to use the "PRAGMA temp_store_directory='...';". Do we have to call it > as a specific time ? (i.e. every time before executing a SELECT statement?) > > 27/03/2014 12:26:50 PM sqlite3_temp_directory variable should be set for > WinRT,1 > 27/03/2014 12:26:50 PM os_win.c:35713: (0) winGetTempname4() - The > operation > completed successfully.,1 > 27/03/2014 12:26:50 PM statement aborts at 40: [SELECT > > PRD_DETAILS.APN1,PRD_DETAILS.APN2,PRD_DETAILS.APN3,PRD_DETAILS.EX_GST_ITEM_C > > OST,PRD_DETAILS.FRANCHISE_CODE,PRD_DETAILS.GST_ITEM_COST,PRD_DETAILS.ITEM_CO > ST,PRD_DETAILS.NOTES,PRD_,1 > Instruction 40 is SorterInsert, the opcode that tries to put an output row into the sorter. If you have some very large output rows, this would be the time when it tries to spill content to disk, in the temporary directory. So, as Joe hypothesized, the inability to create temporary files is a likely explanation for this problem. > > (BTW, we temporarily reverted back to 3.7.17, and everything worked fine.) > The reason for this is that 3.7.17 is extracting the rows in PRIMARY KEY order, so that no sorting is necessary. Please verify this by confirming that if you change the query to read: "... ORDER BY +PRD_CODE;" (adding a "+" before the PRD_CODE column name in the ORDER BY clause) then 3.7.17 also fails. SQLite 3.8.* is not using the PRIMARY KEY to sort in this case because its cost function thinks that there is no performance advantage to using the PRIMARY KEY to sort rather than sorting in a separate step. We might need to tweak the cost function a little to fix this in 3.8.5. Investigating... > > Thanks a lot in advance. > > MB > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin > Sent: Thursday, 27 March 2014 11:45 AM > To: 'General Discussion of SQLite Database' > Subject: Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) > onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1 > > > Muharrem Bilgin wrote: > > > > Thank you for your input. We are not using the SQLite wrapper. The > > sqlite source code is compiled into a Windows Store/Phone DLL and used > > in our application. > > > > That's fine, setting the temporary directory is always required on WinRT > (and Windows Phone 8). > > > > > I have tried your suggestion anyway, but no result. > > > > Can you enable the SQLite error logging mechanism, which may provide more > details as to the root cause of the issue? It's described here: > > https://www.sqlite.org/errlog.html > > -- > Joe Mistachkin > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
