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 future, though it'd require a few more layers of funtionality within Undark to be able to facilitate it. It should be cautioned that due to the statistical probabilities dumped data should always still be vetted by a human for now to weed out false rows that arise due to the limited size of the probability space ( the fewer columns in a table the higher the chance of a false hit ). Regards, Paul -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ------------------------------ Message: 4 Date: Mon, 7 Oct 2013 12:04:35 +0800 From: Kf Lee <kflee2...@gmail.com> To: sqlite-users@sqlite.org Subject: [sqlite] How to run sqlite just in memory - pragma cache_size issue Message-ID: <capbqyuqqpxw6znsqczbne5taxdhedzeakdwfxks2p6etokj...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Hi, Richard, Thanks for the advice, I have look at the link and experiment with it. The example loadOrSaveDb is pretty clear and straightforward. However I did not get it working with the following example: sqlite3 *db; char *zErrMsg = 0; void main(void) { int rc; char oph_sql_db[32], oph_sql_bk[32], s[10]; strcpy(oph_sql_db, "oph.bk3"); strcpy(oph_sql_bk, "oph.sav"); rc = sqlite3_open(":memory:", &db); if( rc!=SQLITE_OK ) fprintf(stderr, "ER! - Open in Memory DB : %s\n", zErrMsg); else printf("\nOpen Memory DB ok\n"); rc=loadOrSaveDb(&db, oph_sql_db, 0); if( rc!=SQLITE_OK ) fprintf(stderr, "ER! - Load DB from disk to Memory : %s\n", zErrMsg); else printf("\nLoad DB ok\n"); printf("\nHit any key + return to save back to hard disk\n"); scanf("%s", s); rc=loadOrSaveDb(&db, oph_sql_bk, 1); if( rc!=SQLITE_OK ) fprintf(stderr, "ER - Save in Memory to disk : %s\n", zErrMsg); else printf("\nSave Memory ok\n"); } it reports that the "Open Memory DB OK" and the other two statements return with NOT SQLITE_OK. Further more, if I open the DB normally and just call to save a backup copy, it actually save the db onto hard disk with file of identical size but it also return NOT SQLITE_OK. I am using sqlite 3.7.9 on Linuxmint, as I can not see any obvious reason for reporting error I am wondering could it be an issue of sqlite version? Please advice, thanks. kfl (Hong Kong). -- ----------------------------------------------------------------------------------------------------------- ------------------------------ Message: 5 Date: Mon, 7 Oct 2013 06:07:08 +0000 From: Navyatha Ummineni <unavya...@steelwedge.com> To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org>, "'sqlite-users-requ...@sqlite.org'" <sqlite-users-requ...@sqlite.org> Subject: [sqlite] Execute scripts on SQLITE password protected database Message-ID: <125633db8723461ca6dc69e7b1327...@by2pr08mb128.namprd08.prod.outlook.com> Content-Type: text/plain; charset="us-ascii" 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 ------------------------------ Message: 6 Date: Mon, 7 Oct 2013 09:01:36 +0100 From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Execute scripts on SQLITE password protected database Message-ID: <bcb77028-1649-4483-a2a0-ab878e0c6...@bigfraud.org> Content-Type: text/plain; charset=us-ascii 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. ------------------------------ Message: 7 Date: Mon, 07 Oct 2013 12:48:10 +0100 From: dean gwilliam <mgbg25...@blueyonder.co.uk> To: sqlite-users@sqlite.org Subject: [sqlite] updating using a value from another table Message-ID: <52529f7a.8040...@blueyonder.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed 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 ------------------------------ Message: 8 Date: Mon, 7 Oct 2013 12:59:33 +0000 From: Rob Richardson <rdrichard...@rad-con.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] updating using a value from another table Message-ID: <67D108EDFAD3C148A593E6ED7DCB4BBDC3FB5190@RADCONWIN2K8PDC.radcon.local> Content-Type: text/plain; charset="us-ascii" 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 ------------------------------ Message: 9 Date: Mon, 7 Oct 2013 14:02:40 +0100 From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] updating using a value from another table Message-ID: <df6a83a1-5e61-4777-ab68-d19478dfa...@bigfraud.org> Content-Type: text/plain; charset=us-ascii 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. ------------------------------ Message: 10 Date: Mon, 07 Oct 2013 15:45:27 +0100 From: dean gwilliam <mgbg25...@blueyonder.co.uk> To: sqlite-users@sqlite.org Subject: Re: [sqlite] updating using a value from another table Message-ID: <5252c907.6050...@blueyonder.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed 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> ------------------------------ Message: 11 Date: Mon, 7 Oct 2013 14:56:35 +0000 From: Rob Richardson <rdrichard...@rad-con.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] updating using a value from another table Message-ID: <67D108EDFAD3C148A593E6ED7DCB4BBDC3FB51DA@RADCONWIN2K8PDC.radcon.local> Content-Type: text/plain; charset="us-ascii" 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 ------------------------------ Message: 12 Date: Mon, 7 Oct 2013 16:00:46 +0100 From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] updating using a value from another table Message-ID: <6bddb208-eba8-4633-bd66-c909ea208...@bigfraud.org> Content-Type: text/plain; charset=us-ascii 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. ------------------------------ Message: 13 Date: Mon, 07 Oct 2013 16:10:17 +0100 From: dean gwilliam <mgbg25...@blueyonder.co.uk> To: sqlite-users@sqlite.org Subject: Re: [sqlite] updating using a value from another table Message-ID: <5252ced9.7030...@blueyonder.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed 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> ------------------------------ Message: 14 Date: Mon, 07 Oct 2013 16:23:16 +0100 From: dean gwilliam <mgbg25...@blueyonder.co.uk> To: sqlite-users@sqlite.org Subject: Re: [sqlite] updating using a value from another table Message-ID: <5252d1e4.7070...@blueyonder.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed 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 End of sqlite-users Digest, Vol 70, Issue 7 ******************************************* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
