Re: [sqlite] Write-ahead logging issue on Android

[Roger Binns]

-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

Re: [sqlite] Write-ahead logging issue on Android

[Sascha Sertel]

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: [] " )
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  wrote:

> On Fri, Oct 4, 2013 at 2:55 PM, Sascha Sertel  >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] updating using a value from another table

[ve3meo]

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] updating using a value from another table

[Keith Medcalf]

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]

On 10/7/2013 7:41 PM, James K. Lowden wrote:

On Mon, 07 Oct 2013 12:48:10 +0100
dean gwilliam  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

[James K. Lowden]

On Mon, 07 Oct 2013 12:48:10 +0100
dean gwilliam  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

[sqlite] Visual Studio file path-length limitation

[Simon Slavin]

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:



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] Issue with GetSchema (ADO.NET provider)

[Joe Mistachkin]

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

Re: [sqlite] updating using a value from another table

[Kurt Welgehausen]

Simon Slavin  wrote:

>
> On 7 Oct 2013, at 3:45pm, dean gwilliam  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] Execute scripts on SQLITE password protected database

[Navyatha Ummineni]

Hi,

I have tried PRAGMA key command but it didn't work. 

PRAGMA key=
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 
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" 
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  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 
To: General Discussion of SQLite Database 
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 

[sqlite] Issue with GetSchema (ADO.NET provider)

[Jay Zimmerman]

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] updating using a value from another table

[dean gwilliam]

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

Re: [sqlite] updating using a value from another table

[dean gwilliam]

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

[dean gwilliam]

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

[dean gwilliam]

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

[dean gwilliam]

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

[Simon Slavin]

On 7 Oct 2013, at 3:45pm, dean gwilliam  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

[Rob Richardson]

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

[dean gwilliam]

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

[Simon Slavin]

On 7 Oct 2013, at 1:59pm, Rob Richardson  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

[Rob Richardson]

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

[sqlite] updating using a value from another table

[dean gwilliam]

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] Execute scripts on SQLITE password protected database

[Simon Slavin]

On 7 Oct 2013, at 7:07am, Navyatha Ummineni  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=

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] Execute scripts on SQLITE password protected database

[Navyatha Ummineni]

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