.TH db.execute 1grass "" "GRASS 6.4.4" "Grass User's Manual"
.SH NAME
\fI\fBdb.execute\fR\fR  - Executes any SQL statement.
.SH KEYWORDS
database, attribute table, SQL
.SH SYNOPSIS
\fBdb.execute\fR
.br
\fBdb.execute help\fR
.br
\fBdb.execute\fR [\-\fBi\fR]  [\fBinput\fR=\fIname\fR]   [\fBdriver\fR=\fIname\fR]   [\fBdatabase\fR=\fIname\fR]   [\-\-\fBverbose\fR]  [\-\-\fBquiet\fR] 
.SS Flags:
.IP "\fB\-i\fR" 4m
.br
Ignore SQL errors and continue
.IP "\fB\-\-verbose\fR" 4m
.br
Verbose module output
.IP "\fB\-\-quiet\fR" 4m
.br
Quiet module output
.PP
.SS Parameters:
.IP "\fBinput\fR=\fIname\fR" 4m
.br
Name of file containing SQL statements
.br
If not given or '-' read from standard input
.IP "\fBdriver\fR=\fIname\fR" 4m
.br
Driver name
.br
Options: \fIpg,dbf,ogr,odbc,mysql,sqlite\fR
.br
Default: \fIdbf\fR
.IP "\fBdatabase\fR=\fIname\fR" 4m
.br
Database name
.br
Default: \fI$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/\fR
.PP
.SH DESCRIPTION
\fIdb.execute\fR allows the user to execute SQL statements.  
.SH NOTES
\fIdb.execute\fR only executes SQL statements and does not return 
any data. If you need data returned from the database, use \fIdb.select\fR.
.PP
If parameters for database connection are already set with 
db.connect, they are taken as default values and
do not need to be specified each time.
.PP
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 \fIdb.execute\fR's
\fBinput\fR 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.
.PP
Please see the individual sql driver pages for how to create
a new database.
.SH EXAMPLES
\fICreate a new table with columns 'cat' and 'soiltype':\fR
.br
\fC
.DS
.br
echo 'create table soils (cat integer, soiltype varchar(10) )' | db.execute
.br
.DE
\fR
.PP
\fICreate a new table using a file with SQL statements:\fR
.br
\fC
.DS
.br
db.execute driver=odbc database=g60test input=file.sql
.br
.DE
\fR
.PP
\fIInsert new row into attribute table:\fR
.br
\fC
.DS
.br
echo "INSERT INTO nobugs (id,name,east_gb,north_gb) values (30,'Ala',1657340,5072301)" | db.execute
.br
.DE
\fR
.PP
\fIUpdate attribute entries to new value based on SQL rule:\fR
.br
\fC
.DS
.br
echo "UPDATE roads SET travelcost=5 WHERE cat=1" | db.execute
.br
.DE
\fR
.PP
\fIUpdate attribute entries to new value based on SQL rule:\fR
.br
\fC
.DS
.br
echo "UPDATE dourokukan SET testc=50 WHERE testc is NULL" | db.execute
.br
.DE
\fR
.PP
\fIDelete selected rows from attribute table:\fR
.br
\fC
.DS
.br
echo "DELETE FROM gsod_stationlist WHERE latitude < -91" | db.execute
.br
.DE
\fR
.PP
\fIAdd new column to attribute table:\fR
.br
\fC
.DS
.br
echo "ALTER TABLE roads ADD COLUMN length double" | db.execute
.br
.DE
\fR
.PP
\fIColumn type conversion - update new column from existing column (all drivers except for DBF):\fR
.br
\fC
.DS
.br
# 'z_value' is varchar and 'z' is double precision:
.br
echo "update geodetic_pts SET z = CAST(z_value AS numeric)" | db.execute
.br
.DE
\fR
.PP
\fIDrop column from attribute table:\fR
.br
\fC
.DS
.br
echo "ALTER TABLE roads DROP COLUMN length" | db.execute
.br
.DE
\fR
.PP
\fIDrop table (not supported by all drivers)\fR
.br
\fC
.DS
.br
echo "DROP TABLE fmacopy" | db.execute
.br
.DE
\fR
.PP
\fIUpdate attribute with multiple SQL instructions in file (e.g., file.sql,
instruction line must end with a semicolon):\fR
.br
\fC
.DS
.br
UPDATE roads SET travelcost=5 WHERE cat=1;
.br
UPDATE roads SET travelcost=2 WHERE cat=2;
.br
 
.br
cat file.sql | db.execute
.br
.DE
\fR
.PP
\fIJoin table 'myroads' into table 'extratab' based on common 'cat' column values (not supported by DBF driver):\fR
.br
\fC
.DS
.br
echo "UPDATE extratab SET names=(SELECT label FROM myroads WHERE extratab.cat=myroads.cat);" | db.execute
.br
.DE
\fR
.SH SEE ALSO
\fIdb.columns,
db.describe,
db.drivers,
db.droptable,
db.login,
db.select,
db.tables,
GRASS SQL interface\fR
.SH AUTHOR
CERL
.PP
\fILast changed: $Date: 2011-11-08 12:29:50 +0100 (Tue, 08 Nov 2011) $\fR
.PP
Full index
.PP
© 2003-2014 GRASS Development Team