<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>GRASS GIS: db.execute</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="grassdocs.css" type="text/css">
</head>
<body bgcolor="white">
<img src="grass_logo.png" alt="GRASS logo"><hr align=center size=6 noshade>
<h2>NAME</h2>
<em><b>db.execute</b></em> - Executes any SQL statement.
<h2>KEYWORDS</h2>
database, SQL
<h2>SYNOPSIS</h2>
<b>db.execute</b><br>
<b>db.execute help</b><br>
<b>db.execute</b> [-<b>i</b>] [<b>input</b>=<em>name</em>] [<b>driver</b>=<em>name</em>] [<b>database</b>=<em>name</em>] [--<b>verbose</b>] [--<b>quiet</b>]
<h3>Flags:</h3>
<DL>
<DT><b>-i</b></DT>
<DD>Ignore SQL errors and continue</DD>
<DT><b>--verbose</b></DT>
<DD>Verbose module output</DD>
<DT><b>--quiet</b></DT>
<DD>Quiet module output</DD>
</DL>
<h3>Parameters:</h3>
<DL>
<DT><b>input</b>=<em>name</em></DT>
<DD>Name of file containing SQL statements</DD>
<DT><b>driver</b>=<em>name</em></DT>
<DD>Driver name</DD>
<DD>Options: <em>odbc,dbf</em></DD>
<DD>Default: <em>dbf</em></DD>
<DT><b>database</b>=<em>name</em></DT>
<DD>Database name</DD>
<DD>Default: <em>$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/</em></DD>
</DL>
<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>db.select</em>.
<P>
If parameters for database connection are already set with
<a HREF="db.connect.html">db.connect</a>, 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 <em>db.execute</em>'s
<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 <a href="sql.html">sql driver</a> pages for how to create
a new database.
<h2>EXAMPLES</h2>
<em>Create a new table with columns 'cat' and 'soiltype':</em><br>
<div class="code"><pre>
echo 'create table soils (cat integer, soiltype varchar(10) )' | db.execute
</pre></div>
<p>
<em>Create a new table using a file with SQL statements:</em><br>
<div class="code"><pre>
db.execute driver=odbc database=g60test input=file.sql
</pre></div>
<P>
<em>Insert new row into attribute table:</em><br>
<div class="code"><pre>
echo "INSERT INTO nobugs (id,name,east_gb,north_gb) values (30,'Ala',1657340,5072301)" | db.execute
</pre></div>
<P>
<em>Update attribute entries to new value based on SQL rule:</em><br>
<div class="code"><pre>
echo "UPDATE roads SET travelcost=5 WHERE cat=1" | db.execute
</pre></div>
<P>
<em>Update attribute entries to new value based on SQL rule:</em><br>
<div class="code"><pre>
echo "UPDATE dourokukan SET testc=50 WHERE testc is NULL" | db.execute
</pre></div>
<P>
<em>Delete selected rows from attribute table:</em><br>
<div class="code"><pre>
echo "DELETE FROM gsod_stationlist WHERE latitude < -91" | db.execute
</pre></div>
<P>
<em>Add new column to attribute table:</em><br>
<div class="code"><pre>
echo "ALTER TABLE roads ADD COLUMN length double" | db.execute
</pre></div>
<P>
<em>Column type conversion - update new column from existing column (all drivers except for DBF):</em><br>
<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
</pre></div>
<P>
<em>Drop column from attribute table:</em><br>
<div class="code"><pre>
echo "ALTER TABLE roads DROP COLUMN length" | db.execute
</pre></div>
<P>
<em>Drop table (not supported by all drivers)</em><br>
<div class="code"><pre>
echo "DROP TABLE fmacopy" | db.execute
</pre></div>
<p>
<em>Update attribute with multiple SQL instructions in file (e.g., file.sql,
instruction line must end with a semicolon):</em><br>
<div class="code"><pre>
UPDATE roads SET travelcost=5 WHERE cat=1;
UPDATE roads SET travelcost=2 WHERE cat=2;
cat file.sql | db.execute
</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>,
<a HREF="sql.html">GRASS SQL interface</a></em>
<h2>AUTHOR</h2>
CERL
<p><i>Last changed: $Date: 2007-10-09 01:30:41 +0200 (Tue, 09 Oct 2007) $</i></p>
<HR>
<P><a href="index.html">Main index</a> - <a href="database.html">database index</a> - <a href="full_index.html">Full index</a></P>
<P>© 2003-2008 <a href="http://grass.osgeo.org">GRASS Development Team</a></p>
</body>
</html>
