Geografic-Information-System/db/db.execute/db.execute.html

<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 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=g60test input=file.sql
</pre></div>

Insert new row into attribute table:
<div class="code"><pre>
db.execute sql="INSERT INTO nobugs (id,name,east_gb,north_gb) 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 &lt; -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., <tt>file.sql</tt>, 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' into 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="sql.html">GRASS SQL interface</a>
<em>

<p>
<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>

<h2>AUTHOR</h2>

CERL

<p>
<i>Last changed: $Date$</i>