1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
<h2>DESCRIPTION</h2>
<em>db.execute</em> allows the user to execute SQL statements.
<h2>NOTES</h2>
<em>db.execute</em> only executes SQL statements and does not return
any data. If you need data returned from the database, use
<em><a href="db.select.html">db.select</a></em>.
<p>
If parameters for database connection are already set with
<em><a href="db.connect.html">db.connect</a></em>, they are taken as default
values and do not need to be specified each time.
<p>
If you have a large number of SQL commands to process, it is much
faster to place all the SQL statements into a text file and
use <b>input</b> file parameter than it is to process each statement
individually in a loop. If multiple instruction lines are given, each
SQL line must end with a semicolon.
<p>
Please see the individual <em><a href="sql.html">GRASS SQL interface</a></em>
for how to create a new database.
<h2>EXAMPLES</h2>
Create a new table with columns 'cat' and 'soiltype':
<div class="code"><pre>
db.execute sql="CREATE TABLE soils (cat integer, soiltype varchar(10))"
</pre></div>
Create a new table using a file with SQL statements
<div class="code"><pre>
db.execute driver=odbc database=grassdb input=file.sql
</pre></div>
Insert new row into attribute table:
<div class="code"><pre>
db.execute sql="INSERT INTO mysites (id,name,east,north) values (30,'Ala',1657340,5072301)"
</pre></div>
Update attribute entries to new value based on SQL rule:
<div class="code"><pre>
db.execute sql="UPDATE roads SET travelcost=5 WHERE cat=1"
</pre></div>
Update attribute entries to new value based on SQL rule:
<div class="code"><pre>
db.execute sql="UPDATE dourokukan SET testc=50 WHERE testc is NULL"
</pre></div>
Delete selected rows from attribute table:
<div class="code"><pre>
db.execute sql="DELETE FROM gsod_stationlist WHERE latitude < -91"
</pre></div>
Add new column to attribute table:
<div class="code"><pre>
db.execute sql="ALTER TABLE roads ADD COLUMN length double"
</pre></div>
Column type conversion - update new column from existing column (all
drivers except for DBF):
<div class="code"><pre>
# 'z_value' is varchar and 'z' is double precision:
echo "UPDATE geodetic_pts SET z = CAST(z_value AS numeric)" | db.execute input=-
</pre></div>
Drop column from attribute table:
<div class="code"><pre>
db.execute sql="ALTER TABLE roads DROP COLUMN length"
</pre></div>
Drop table (not supported by all drivers):
<div class="code"><pre>
db.execute sql="DROP TABLE fmacopy"
</pre></div>
Update attribute with multiple SQL instructions in file
(e.g., <code>file.sql</code>, instruction line must end with a semicolon):
<div class="code"><pre>
UPDATE roads SET travelcost=5 WHERE cat=1;
UPDATE roads SET travelcost=2 WHERE cat=2;
db.execute input=file.sql
</pre></div>
Join table 'myroads' to table 'extratab' based on common 'cat' column
values (not supported by DBF driver):
<div class="code"><pre>
db.execute sql="UPDATE extratab SET names=(SELECT label FROM myroads WHERE extratab.cat=myroads.cat)"
</pre></div>
<h2>SEE ALSO</h2>
<em>
<a href="db.columns.html">db.columns</a>,
<a href="db.describe.html">db.describe</a>,
<a href="db.drivers.html">db.drivers</a>,
<a href="db.droptable.html">db.droptable</a>,
<a href="db.login.html">db.login</a>,
<a href="db.select.html">db.select</a>,
<a href="db.tables.html">db.tables</a>,
</em>
<p>
<em>
<a href="sql.html">GRASS SQL interface</a>
</em>
<h2>AUTHOR</h2>
CERL
|
