[GRASS-user] How To Identify db.copy Error?

rshepard · March 17, 2011, 2:17pm

One of the tables I'm copying from dbf to the postgres database threw an
error:

DBMI-Postgres driver error:
Cannot create table:
create table hydro_pointEvent ( cat integer, ComID integer, Permanent_
varchar(40), EventDate varchar(10), ReachCode varchar(14), ReachSMDat
varchar(10), ReachResol integer, FeatureCom integer, Feature_Pe varchar(40),
FeatureCla integer, Source_Ori varchar(130), Source_Dat varchar(100),
Source_Fea varchar(40), FeatureDet varchar(254), Measure double precision,
Offset double precision, EventType integer )
ERROR: syntax error at or near "Offset"
LINE 1: ...eatureDet varchar(254), Measure double precision, Offset dou...
^
WARNING: Unable to create table <hydro_pointEvent>
WARNING: Copy table failed

Is there a protocol I can use within GRASS to identify the syntax error?

TIA,

Rich

neteler · March 17, 2011, 3:04pm

On Thu, Mar 17, 2011 at 3:17 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

One of the tables I'm copying from dbf to the postgres database threw an
error:

DBMI-Postgres driver error:
Cannot create table:
create table hydro_pointEvent ( cat integer, ComID integer, Permanent_
varchar(40), EventDate varchar(10), ReachCode varchar(14), ReachSMDat
varchar(10), ReachResol integer, FeatureCom integer, Feature_Pe varchar(40),
FeatureCla integer, Source_Ori varchar(130), Source_Dat varchar(100),
Source_Fea varchar(40), FeatureDet varchar(254), Measure double precision,
Offset double precision, EventType integer )
ERROR: syntax error at or near "Offset"
LINE 1: ...eatureDet varchar(254), Measure double precision, Offset dou...
^
WARNING: Unable to create table <hydro_pointEvent>
WARNING: Copy table failed

"Offset" is a reserved word in PostgreSQL:

"SQL Reserved Words Checker"
http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=Offset

Is there a protocol I can use within GRASS to identify the syntax error?

Within GRASS no since it comes from (and depends on) the backend.
Above link seems to be useful.

Markus

rshepard · March 17, 2011, 3:29pm

On Thu, 17 Mar 2011, Markus Neteler wrote:

"Offset" is a reserved word in PostgreSQL:

Thanks, Markus. I've not bumped against it before. I'll use a bit editor
to change it.

Rich

Micha_Silver · March 17, 2011, 4:37pm

Rich Shepard wrote:

One of the tables I’m copying from dbf to the postgres database threw an
error:

DBMI-Postgres driver error:
Cannot create table:
create table hydro_pointEvent ( cat integer, ComID integer, Permanent_
varchar(40), EventDate varchar(10), ReachCode varchar(14), ReachSMDat
varchar(10), ReachResol integer, FeatureCom integer, Feature_Pe varchar(40),
FeatureCla integer, Source_Ori varchar(130), Source_Dat varchar(100),
Source_Fea varchar(40), FeatureDet varchar(254), Measure double precision,
Offset double precision, EventType integer )
ERROR: syntax error at or near “Offset”
LINE 1: …eatureDet varchar(254), Measure double precision, Offset dou…

“Offset” is a postgres (SQL) reserved word. Can you change that column name in the dbf?

rshepard · March 17, 2011, 4:53pm

On Thu, 17 Mar 2011, Micha Silver wrote:

"Offset" is a postgres (SQL) reserved word. Can you change that column
name in the dbf?

Yep. Emacs (of course!) works well. I changed it to setoff and postgres
is happy. I've a couple of other files that throw errors, but the issue is
not reserved words. Later today I'll go look for a dbf->csv filter. Then I
can clean the text file and copy it into postgres.

Thanks, Micha,

Rich

Rebecca_Bennett · March 17, 2011, 5:55pm

Hello all,

I have been having a go at upgrading a basic bash script that I made to run a series of commands in GRASS. I would like the script to ask the user for two input files (one internal to the location i.e. an @PERMENANT and the other and external .asc) and an prefix to the output files of the process.

So far I have tried this bit of script to enter the GRASS raster, but it fails to prompt. All other commands (i.e. g.list rast) work fine.

g.list rast
g.findfile type=old elem=cell_misc prompt="Enter raster name " unixfile=cur_raster
. cur_raster
if [ ! “$file” ]
then
exit
fi
RAST=${name}

Can anyone point out where I’m doing wrong and/or point me in the direction of some more advanced tutorials and information for GRASS scripting than I have found here http://grass.fbk.eu/gdp/grass5tutor/HTML_en/c1806.html

Many thanks for reading,
Rebecca

From: Rich Shepard rshepard@appl-ecosys.com
To: grass-users@lists.osgeo.org
Sent: Thu, 17 March, 2011 16:53:56
Subject: Re: [GRASS-user] How To Identify db.copy Error?

On Thu, 17 Mar 2011, Micha Silver wrote:

“Offset” is a postgres (SQL) reserved word. Can you change that column
name in the dbf?

Yep. Emacs (of course!) works well. I changed it to setoff and postgres
is happy. I’ve a couple of other files that throw errors, but the issue is
not reserved words. Later today I’ll go look for a dbf->csv filter. Then I
can clean the text file and copy it into postgres.

Thanks, Micha,

Rich

grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

neteler · March 17, 2011, 8:51pm

On Thu, Mar 17, 2011 at 6:55 PM, Rebecca Bennett <rabennett@ymail.com> wrote:

Hello all,

I have been having a go at upgrading a basic bash script that I made to run
a series of commands in GRASS. I would like the script to ask the user for
two input files (one internal to the location i.e. an @PERMENANT and the
other and external .asc) and an prefix to the output files of the process.

So far I have tried this bit of script to enter the GRASS raster, but it
fails to prompt. All other commands (i.e. g.list rast) work fine.

g.list rast
g.findfile type=old elem=cell_misc prompt="Enter raster name "
unixfile=cur_raster

The command to ask is g.ask, not g.findfile:
http://grass.osgeo.org/grass64/manuals/html64_user/g.ask.html

Hope this helps
Markus

Hamish · March 18, 2011, 2:20am

Rich wrote:

Later today I'll go look for a dbf->csv filter. Then I
can clean the text file and copy it into postgres.

try "dbview -bt filename.dbf"

Hamish

Rebecca_Bennett · March 18, 2011, 11:24am

Thanks for the heads up Marcus, g.ask works like a charm now.

Part of my script includes a choice of external. asc files, is there an equivalent to g.ask command the user to enter this file path/ name?

Many thanks for reading,
Rebecca

From: Markus Neteler neteler@osgeo.org
To: Rebecca Bennett rabennett@ymail.com
Cc: GRASS Users grass-users@lists.osgeo.org
Sent: Thu, 17 March, 2011 20:51:48
Subject: Re: [GRASS-user] Editing a bash script

On Thu, Mar 17, 2011 at 6:55 PM, Rebecca Bennett <rabennett@ymail.com> wrote:

Hello all,

I have been having a go at upgrading a basic bash script that I made to run
a series of commands in GRASS. I would like the script to ask the user for
two input files (one internal to the location i.e. an @PERMENANT and the
other and external .asc) and an prefix to the output files of the process.

So far I have tried this bit of script to enter the GRASS raster, but it
fails to prompt. All other commands (i.e. g.list rast) work fine.

g.list rast
g.findfile type=old elem=cell_misc prompt="Enter raster name "
unixfile=cur_raster

The command to ask is g.ask, not g.findfile:
http://grass.osgeo.org/grass64/manuals/html64_user/g.ask.html

Hope this helps
Markus

neteler · March 18, 2011, 12:19pm

On Fri, Mar 18, 2011 at 12:24 PM, Rebecca Bennett <rabennett@ymail.com> wrote:

Thanks for the heads up Marcus, g.ask works like a charm now.

Excellent.

Part of my script includes a choice of external. asc files, is there an
equivalent to g.ask command the user to enter this file path/ name?

Not sure if I fully understand. But perhaps it is the "read" shell function
which you mean:

echo -n "Enter file name: "
read USERFILE

Then $USERFILE contains the file name (or whatever the user entered).

Markus

rshepard · March 18, 2011, 7:06pm

On Thu, 17 Mar 2011, Hamish wrote:

try "dbview -bt filename.dbf"

Hamish,

Sigh. The database is essentially empty. From the tail of the above:

510:0.000000:0.000000:::5:::::::::::::::
511:0.000000:0.000000:::5:::::::::::::::
512:0.000000:0.000000:::5:::::::::::::::
513:0.000000:0.000000:::5:::::::::::::::
514:0.000000:0.000000:::5:::::::::::::::
515:0.000000:0.000000:::5:::::::::::::::
516:0.000000:0.000000:::5:::::::::::::::

How useless! I'll contact the originating agency and see if they can fix
the problem.

Thanks for the pointer to this tool,

Rich