[sqlite] Execute scripts on SQLITE password protected database
From: Navyatha Ummineni Sent: Saturday, October 05, 2013 10:58 AM To: 'sqlite-users-requ...@sqlite.org' Subject: Execute scripts on SQLITE password protected database Hi Sqlite Users, I have used System.Data.Sqlite.dll in my application to create password protected database. My application saves the data in this database file. Now I want to run a script every day which fetches data from this database and creates a file to store the data for analysis. I have tried using sqlite.exe to run some commands to open this password protected database, but it didn't work. Can you please tell me how to open a password protected database using sqlite.exe from command prompt so that we can create a script or .bat file to do the same? Thanks in advance, Navyatha ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Execute scripts on SQLITE password protected database
On 7 Oct 2013, at 7:07am, Navyatha Ummineni unavya...@steelwedge.com wrote: I have used System.Data.Sqlite.dll in my application to create password protected database. Having used SQLITE.EXE to option the database try executing PRAGMA key=your password here and see if that lets you access your data. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] updating using a value from another table
Here it is tbl itms (std_nm text, raw_nm text) tbl aliases (std_nm text, raw_nm text) Id like to whip through table itms which has a blank col std_nm and fill in as many fields as I can by reading each itms' raw_nm fld finding any matching rec (with the same raw_nm value) in aliases updating the std_nm fld in itms with corresponding std_nm value in the matching fld in aliases Here's my miserable attempt gDb eval UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm); Any help much appreciated ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Your query looks good to me, which probably means I'm missing the same thing you are. What happens when you run this query? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dean gwilliam Sent: Monday, October 07, 2013 7:48 AM To: sqlite-users@sqlite.org Subject: [sqlite] updating using a value from another table Here it is tbl itms (std_nm text, raw_nm text) tbl aliases (std_nm text, raw_nm text) Id like to whip through table itms which has a blank col std_nm and fill in as many fields as I can by reading each itms' raw_nm fld finding any matching rec (with the same raw_nm value) in aliases updating the std_nm fld in itms with corresponding std_nm value in the matching fld in aliases Here's my miserable attempt gDb eval UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm); Any help much appreciated ___ 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
Re: [sqlite] updating using a value from another table
On 7 Oct 2013, at 1:59pm, Rob Richardson rdrichard...@rad-con.com wrote: gDb eval UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm); What happens if you use the SQLite shell tool to open your database and execute this command ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Simon, Rob Thanks very much for your responses. I downloaded and used the shell...it gives the same results i.e. the first std_itm fld in aliases irrespective of what the value of raw_fld is in itms SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter .help for instructions Enter SQL statements terminated with a ; sqlite UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); sqlite select std_nm from itms limit 10 ... ; sales sales sales sales sales sales sales sales sales sales sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Thank you. Now, can you show us sample data from your tables before this query is run? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dean gwilliam Sent: Monday, October 07, 2013 10:45 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] updating using a value from another table Simon, Rob Thanks very much for your responses. I downloaded and used the shell...it gives the same results i.e. the first std_itm fld in aliases irrespective of what the value of raw_fld is in itms SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter .help for instructions Enter SQL statements terminated with a ; sqlite UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); sqlite select std_nm from itms limit 10 ... ; sales sales sales sales sales sales sales sales sales sales sqlite ___ 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
Re: [sqlite] updating using a value from another table
On 7 Oct 2013, at 3:45pm, dean gwilliam mgbg25...@blueyonder.co.uk wrote: sqlite UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); looking at it without the 'AS' ... UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = aliases.raw_nm); I'm wondering whether you actually mean UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = itms.raw_nm); or something like that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
RobR Sorry for the delay...I needed to regenerate the database which took several minutes and processes after I messed it up. Here's the completed itms table. I don't understand why sales is written to the std_nm column in itms when each rows corresponding raw_nm field does not appear in aliases and particularly with a corresponding sales field. Hope I've made my self clear and thank you for your help SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select * from aliases; 1|sales|sales 2|sales|turnover 3|sales|revenue 4|sales|revenues 5|cogs|cost_of_sales sqlite select * from itms limit 10; 1|dummy|2005|inc|sales|for_the_year_ended_30_April|2005.0 2|dummy|2005|inc|sales|Turnover|150645.0 3|dummy|2005|inc|sales|Cost_of_sales|-6327.0 4|dummy|2005|inc|sales|Gross_profit|144318.0 5|dummy|2005|inc|sales|Selling_and_distribution_costs|-48106.0 6|dummy|2005|inc|sales|Research_and_development|-23407.0 7|dummy|2005|inc|sales|Amortisation_of_goodwill|-3769.0 8|dummy|2005|inc|sales|Share-based_compensation_payments|-3581.0 9|dummy|2005|inc|sales|Reorganisation_costs|-2302.0 10|dummy|2005|inc|sales|Other_administrative_expenses|-27229.0 sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Thanks Simon I tried that but it writes nothing to itms.std_nm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
I seem to have cracked it with a cut-down example package require sqlite3 set gDb {} set db_fl_nm [pwd]\\test.db ;# :memory: proc mbx {x} { set answer [tk_messageBox -message $x -type yesno -icon question] switch -- $answer { yes {} no exit } } proc mfl {x} {global gOfl; puts $gOfl $x} proc tbl_app {tbl data} { set max_id [expr [gDb eval select max(id) FROM $tbl;]] if {$max_id == } {set max_id 0} set id [expr $max_id + 1] gDb eval insert into $tbl values ( $id, $data ) } proc doit {db_pth} { global gDb sqlite3 gDb $db_pth gDb eval drop table if exists itms gDb eval drop table if exists aliases gDb eval create table if not exists itms (id integer primary key, std_nm text, raw_nm text); gDb eval create table if not exists aliases (id integer primary key, std_nm text, raw_nm text); tbl_app itms null, 'aaa' tbl_app itms null, 'bbb' tbl_app itms null, 'ccc' tbl_app aliases 'std_nm1', 'aaa' tbl_app aliases 'std_nm2', 'bbb' gDb eval UPDATE itms SET std_nm= (SELECT std_nm FROM aliases WHERE itms.raw_nm = aliases.raw_nm); #(SELECT std_nm FROM aliases WHERE itms.raw_nm = 'bbb'); gDb close } doit $db_fl_nm exec SQLiteSpy $db_fl_nm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
It's not working in my big program though so... I'll try to see what's different i.e. I'm getting nothing in itms.std_nm using the test program update query despite it working in my test program ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
It's working now I just needed to use a capital first character in alias.raw_nm. Now it's working in the big program too. Simon, Rob...thanks for your help/reassurance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Issue with GetSchema (ADO.NET provider)
When the GetSchema(string collectionName) method is called on the SQLiteConnection object using the argument ReservedWords, the returned DataTable has the wrong TableName. It should be ReservedWords, but is MetaDataCollections instead. The columns and data contained in the DataTable are correct. This appears to be the only collection with this problem. --- Jay D Zimmerman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Execute scripts on SQLITE password protected database
Hi, I have tried PRAGMA key command but it didn't work. PRAGMA key=your password here Is there anything else we can try to open the encrypted database? Thanks, Navyatha -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of sqlite-users-requ...@sqlite.org Sent: Monday, October 07, 2013 9:30 PM To: sqlite-users@sqlite.org Subject: sqlite-users Digest, Vol 70, Issue 7 Send sqlite-users mailing list submissions to sqlite-users@sqlite.org To subscribe or unsubscribe via the World Wide Web, visit http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users or, via email, send a message with subject or body 'help' to sqlite-users-requ...@sqlite.org You can reach the person managing the list at sqlite-users-ow...@sqlite.org When replying, please edit your Subject line so it is more specific than Re: Contents of sqlite-users digest... Today's Topics: 1. Re: SQLite4 release date and how to compile on windows platform (Gabriel Corneanu) 2. Re: Tool for extracting deleted data from unvacuumed SQLite files (James K. Lowden) 3. Re: Tool for extracting deleted data from unvacuumed SQLite files (Paul L Daniels) 4. How to run sqlite just in memory - pragma cache_size issue (Kf Lee) 5. Execute scripts on SQLITE password protected database (Navyatha Ummineni) 6. Re: Execute scripts on SQLITE password protected database (Simon Slavin) 7. updating using a value from another table (dean gwilliam) 8. Re: updating using a value from another table (Rob Richardson) 9. Re: updating using a value from another table (Simon Slavin) 10. Re: updating using a value from another table (dean gwilliam) 11. Re: updating using a value from another table (Rob Richardson) 12. Re: updating using a value from another table (Simon Slavin) 13. Re: updating using a value from another table (dean gwilliam) 14. Re: updating using a value from another table (dean gwilliam) -- Message: 1 Date: Sun, 06 Oct 2013 18:34:18 +0200 From: Gabriel Corneanu gabrielcorne...@gmail.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite4 release date and how to compile on windows platform Message-ID: 5251910a.8020...@gmail.com Content-Type: text/plain; charset=ISO-8859-1; format=flowed Simon, I have asked several times similar questions, and got similar replies. This answer is simply NOT correct. While I understand nobody wants to commit to a release/stable version, I really wanted to evaluate how it fits my needs. Therefore I took some time to understand how could I compile it. I found the unpleasant truth: at this time (ok, a few weeks ago) Windows is not supported at all. The file lsm_unix.c is not ported to Windows (contains low level file access, shared memory, memory mapped files, etc). Regards, Gabriel -- Message: 2 Date: Sun, 6 Oct 2013 17:31:54 -0400 From: James K. Lowden jklow...@schemamania.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files Message-ID: 20131006173154.139cf171.jklow...@schemamania.org Content-Type: text/plain; charset=US-ASCII On Sun, 6 Oct 2013 22:58:28 +1000 Paul L Daniels pldani...@pldaniels.com wrote: Undark is only at v0.2 at this time, it's still quite coarse around some corners and it does not differentiate between deleted and undeleted rows. It also has a limitation of only decoding what it finds within the SQLite page (ie, 4K in most cases), if the row extends beyond the page the data won't be retrieved ( I am working on this for v0.3 ), finally, it doesn't decode or dump BLOB data yet, simply not sure how to represent that sanely on a CSV dump. If Undark decodes the metadata, it might be more useful to create a new database instead of a CSV file. The new database would be a mirror of the input, except that its tables would hold deleted rows (or both deleted and extant, with a column signifying which). That way no information is lost, re-insertion is simple, and representation questions are mooted. --jkl -- Message: 3 Date: Mon, 7 Oct 2013 10:11:47 +1000 From: Paul L Daniels pldani...@pldaniels.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files Message-ID: 20131007101147.4310b...@pldaniels.com Content-Type: text/plain; charset=US-ASCII If Undark decodes the metadata, it might be more useful to create a new database instead of a CSV file. The new database would be a mirror of the input, except that its tables would hold deleted rows (or both deleted and extant, with a column signifying which). I may look in to adding such a feature in the
Re: [sqlite] updating using a value from another table
Simon Slavin slav...@bigfraud.org wrote: On 7 Oct 2013, at 3:45pm, dean gwilliam mgbg25...@blueyonder.co.uk wrote: sqlite UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); looking at it without the 'AS' ... UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = aliases.raw_nm); I'm wondering whether you actually mean UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = itms.raw_nm); or something like that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users sqlite create table t (k integer primary key, d integer); sqlite insert into t (k) values (1); sqlite insert into t (k) values (2); sqlite insert into t (k) values (3); sqlite create table t2 (k integer primary key, d integer); sqlite insert into t2 (d) values (101); sqlite insert into t2 (d) values (102); sqlite select * from t; k d -- -- 1 2 3 sqlite select * from t2; k d -- -- 1 101 2 102 sqlite update t set d = (select d from t2 where t2.k = t.k); sqlite select changes(); changes() -- 3 sqlite select * from t; k d -- -- 1 101 2 102 3 sqlite update t set d = null; sqlite update t set d = 103 where k = 3; sqlite select * from t; k d -- -- 1 2 3 103 sqlite update t set d = (select d from t2 where t2.k = t.k); sqlite select changes(); changes() -- 3 sqlite select * from t; k d -- -- 1 101 2 102 3 sqlite update t set d = null; sqlite update t set d = 103 where k = 3; sqlite select * from t; k d -- -- 1 2 3 103 sqlite update t set d = (select d from t2 where t2.k = t.k) ... where t.k in (select k from t2); sqlite select changes(); changes() -- 2 sqlite select * from t; k d -- -- 1 101 2 102 3 103 In the original post, the columns raw_nm and x.raw_nm are the same column, so the condition is always true. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue with GetSchema (ADO.NET provider)
Jay Zimmerman wrote: When the GetSchema(string collectionName) method is called on the SQLiteConnection object using the argument ReservedWords, the returned DataTable has the wrong TableName. It should be ReservedWords, but is MetaDataCollections instead. The columns and data contained in the DataTable are correct. This appears to be the only collection with this problem. Thanks for the report. Fixed now: https://system.data.sqlite.org/index.html/ci/e1c3a2bc9c?sbs=0 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Visual Studio file path-length limitation
We have previously seen reports from a user who ran into the Visual Studio/.NET 260 character path-length limit. As users of other platforms know this is not something inherent in SQLite. Someone raised the subject with the Visual Studio development team and this was the result: http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/2156195-fix-260-character-file-name-length-limitation Just thought it might be worth putting a pointer into the archives of this list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
On Mon, 07 Oct 2013 12:48:10 +0100 dean gwilliam mgbg25...@blueyonder.co.uk wrote: Here's my miserable attempt gDb eval UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm); Oh, so close! An update statement without a WHERE clause updates the whole table. In your case, any rows in itms not matching in aliases will result in a itms.std_nm becoming NULL. You need two subqueries: one to set the value, and the other to restrict the rows updated. Here's a simpler example. -- table to update sqlite create table a (a int, i int); sqlite insert into a (a) values (1), (2); sqlite insert into a values (3, 'three'); sqlite select * from a; a i -- -- 1 2 3 three -- table to update from sqlite create table b as select * from a where a 3; sqlite update b set i = 'one' where a = 1; sqlite update b set i = 'two' where a = 2; sqlite select * from b; a i -- -- 1 one 2 two sqlite begin transaction; -- illustrate error sqlite update a set i = (select i from b where b.a = a.a); sqlite select * from a; a i -- -- 1 one 2 two 3 sqlite rollback; -- oops sqlite begin transaction; -- do it right this time sqlite update a set i = (select i from b where b.a = a.a) where exists (select 1 from b where a.a = b.a); sqlite select * from a; a i -- -- 1 one 2 two 3 three sqlite commit; -- ta da On a side note, items is only one letter longer than itms, and you can read one and not the othr. If you use whole words for your table and column names, you'll save yourself remembering what abbreviation you used, and of conflicting/inconsistent abbreviations. You'd be in good company. Brian Kernighan, on being asked what he'd change about Unix given the chance, supposedly said, I'd add an 'e' to 'creat' (refering to the creat(2) syscall). --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
On 10/7/2013 7:41 PM, James K. Lowden wrote: On Mon, 07 Oct 2013 12:48:10 +0100 dean gwilliam mgbg25...@blueyonder.co.uk wrote: Here's my miserable attempt gDb eval UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm); Oh, so close! An update statement without a WHERE clause updates the whole table. In your case, any rows in itms not matching in aliases will result in a itms.std_nm becoming NULL. You need two subqueries: one to set the value, and the other to restrict the rows updated. sqlite begin transaction; -- do it right this time sqlite update a set i = (select i from b where b.a = a.a) where exists (select 1 from b where a.a = b.a); Or alternatively, without a WHERE clause: update a set i = coalesce((select i from b where b.a = a.a), i); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Mayhaps, update itms set std_nm = (select std_nm from aliases where raw_nm=itms.raw_nw) where exists (select 1 from aliases where raw_nm=itms.raw_nw); which translates to english as: for each row in itms where there exists a row in aliases where aliases.raw_nm=itms.raw_nm set itms.raw_nm to aliases.std_nm where aliases.raw_nm=itms.raw_nm This UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm) translates to english as: for each row in itms set itms.std_nm to the std_nm located in the first row of aliases where aliases.raw_nm=aliases.raw_nm -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of dean gwilliam Sent: Monday, 7 October, 2013 05:48 To: sqlite-users@sqlite.org Subject: [sqlite] updating using a value from another table Here it is tbl itms (std_nm text, raw_nm text) tbl aliases (std_nm text, raw_nm text) Id like to whip through table itms which has a blank col std_nm and fill in as many fields as I can by reading each itms' raw_nm fld finding any matching rec (with the same raw_nm value) in aliases updating the std_nm fld in itms with corresponding std_nm value in the matching fld in aliases Here's my miserable attempt gDb eval UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm); Any help much appreciated ___ 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
Re: [sqlite] updating using a value from another table
Igor Tandetnik-2 wrote Or alternatively, without a WHERE clause: update a set i = coalesce((select i from b where b.a = a.a), i); What I have used similar to this is: UPDATE a SET i = ifnull((select i from b where b.a = a.a), i); Tom -- View this message in context: http://sqlite.1065341.n5.nabble.com/updating-using-a-value-from-another-table-tp71588p71607.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Write-ahead logging issue on Android
Ok I was able to make some progress on this issue. Per Richard's recommendation I hooked into the error callback and started seeing errors like this (original sql statement removed from log output since it is not important here): SqliteLibrary::Log( Error=14, Message=cannot open file at line 28203 of [527231bc67] ) SqliteLibrary::Log( Error=14, Message=os_unix.c:28203: (30) open(./etilqs_xetmTmd7oYLmidY) - ) SqliteLibrary::Log( Error=14, Message=statement aborts at 36: [statement here] ) It looks like Richard's initial hypothesis turns out to be true, SQLite is trying to create temporary files in . which doesn't work on Android, so it fails. I have since tried to find out what the correct way is to point SQLite to the right place for creating temporary files in Android, with no luck. There was a bug in Google Chrome regarding the same issue, and the solution they chose was to use -DSQLITE_TEMP_STORE=3 to force temporary file creation in memory for the same reason. I found the PRAGMA temp_store_directory in the docs but unfortunately it says this pragma is deprecated and using it is discouraged. Right now it looks like my options are either going the memory only way as well or using a deprecated pragma to set the temp directory to where my Android app can actually write. Any other ideas? // Sascha On Fri, Oct 4, 2013 at 3:10 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Oct 4, 2013 at 2:55 PM, Sascha Sertel sascha.ser...@gmail.com wrote: I'm working on a multi-platform app that uses a cross-platform library with SQLite 3.7.16.1 built into it (i.e. not using the built-in SQLite version in Android or other platforms). We make (re)use of prepared statements for INSERT and UPDATE on the database, and we use FULLMUTEX serialized threading mode. We use explicit transactions for some bulk INSERT operations, but most other calls use implicit transactions. Everything worked fine until I switched journal_mode from MEMORY to WAL. Now when I run the app I started seeing SQLite errors pop up, the first error is sqlite3_step failed: unable to open database file (error code: 14) Please activate the error and warning log ( http://www.sqlite.org/errlog.html) and let us know what you see there. One possible problem: SQLite needs to create a temporary file so that a multi-row update can be backed out if a constraint fails, but you are out of temporary file space, or maybe you don't have access rights on the temporary file space. -- D. Richard Hipp d...@sqlite.org ___ 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
Re: [sqlite] Write-ahead logging issue on Android
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/13 18:37, Sascha Sertel wrote: I have since tried to find out what the correct way is to point SQLite to the right place for creating temporary files in Android, with no luck. https://developer.android.com/reference/android/content/Context.html#getDir(java.lang.String, int) Pass in sqlite_tmp as the string. Every app also has a cache dir. Note that Android can clear this at any arbitrary point if storage is getting low so it isn't the most robust locaton for temp files. https://developer.android.com/reference/android/content/Context.html#getCacheDir() Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlJTb3YACgkQmOOfHg372QTbUwCguSVoQ7AcJrGCk7qwwhzFQOqQ z5YAoIFNQxhOTeP8/0Aq2lEpOD0Lc+GT =VUgP -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users