[GRASS5] Re: [GRASSLIST:2571] v.in.ogr --- what does this error mean, and where's it coming from?

[Radim Blazek]

On Monday 22 March 2004 00:15, Tom Russo wrote:
> Radim:
>
> A very long time ago (in early February) you and I corresponded about
> a problem I had importing a shapefile using v.in.ogr.  At that time I
> was getting no useful information out of the db.execute that could
> help me track things down, then I ran out of time to work on it.
> Today I updated grass57 and rebuilt, and now I get some more
> meaningful output.  Instead of giving me the "(null)" error it said
> there was an SQL parse error.
>
> I have cut-and-pasted the "create table" line that is output by
> v.in.ogr and done the "echo "create table..." | db.execute".
>
> The full command is:
>
> echo "create table trails (cat integer, FNODE_ integer, TNODE_ integer,
> LPOLY_ integer, RPOLY_ integer, LENGTH double precision, TRAIL_ integer,
> TRAIL_ID integer, TYPE varchar ( 1 ), CODE varchar ( 2 ), CODE_MEMO varchar
> ( 80 ), METHOD varchar ( 2 ), MISC varchar ( 1 ), TE_UNIT varchar ( 1 ),
> EXISTVEG varchar ( 1 ), FS_OWN varchar ( 1 ), TRAIL varchar ( 1 ), ROAD
> varchar ( 1 ), WATER varchar ( 1 ), STREAM varchar ( 1 ), PLSS varchar ( 1
> ), ADM_DIST varchar ( 1 ), ADM_UNIT varchar ( 1 ), PASTURE varchar ( 1 ),
> SPEC_MGT varchar ( 1 ), HRTGSRVY varchar ( 1 ), RD_RTE_NO varchar ( 35 ),
> TR_RTE_NO varchar ( 35 ), STRM_NAME varchar ( 80 ), SURVEY_NUM varchar ( 13
> ), CFF1 varchar ( 3 ), CFF2 varchar ( 3 ), CFF3 varchar ( 3 ), CFF4 varchar
> ( 3 ), CFF5 varchar ( 3 ), CFF6 varchar ( 3 ), CFF7 varchar ( 3 ), CFF8
> varchar ( 3 ), CFF9 varchar ( 3 ), CFF10 varchar ( 3 ), NUMBER varchar ( 35
> ), NUMBER0 varchar ( 35 ), LEVE varchar ( 20 ), TYPE0 varchar ( 20 ),
> ROAD_TYPE varchar ( 20 ), LANES varchar ( 20 ), SURFACE varchar ( 20 ),
> PROBLEM varchar ( 20 ), CLOSURE varchar ( 20 ), DATE integer, TIME varchar
> ( 20 ), OPERATOR varchar ( 20 ), COMMENTS varchar ( 50 ), MAX_PDOP double
> precision, GPS_DATE integer, GPS_TIME varchar ( 20 ), GPS_LENGTH double
> precision, HORZ_PREC double precision, VERT_PREC double precision,
> HORZ_PRE0 double precision, VERT_PRE0 double precision, TRAIL_NO varchar (
> 10 ), SOURCE varchar ( 10 ), MAPUSE varchar ( 25 ), NAME varchar ( 30 ),
> MILES2 double precision, COOID varchar ( 10 ), STATUS integer)" |
> db.execute
>
> When I execute this command with g.gisenv set=DEBUG=3 I get:
>
>   D3/3: sql: create table trails ([...all those fields deleted for
> brevity])
>
>   DBMI-DBF driver error:
>   SQL parser error in statement:
>   create table trails ([...all the attributes...])
>
>
>   Error in db_execute_immediate()
>
>   WARNING: Error while executing: "create table trails ([...])"
>
> In the end, I found that the entire problem is due to the presence of
> an attribute in the dbf file called "DATE".  As long as the "create
> table" includes this field, I get an error.  If I remove it, or just
> rename it to "DATE_" then there is no failure in the db.execute.
>
> Unfortunately, I don't see an easy way of renaming the "DATE" field to
> "DATE_" in the dbf file so that v.in.ogr can read it and produce the
> right database.  Do you know what library or function is responsible
> for considering the "DATE" field an error?  Is it Postgres that's used
> to parse the statement?  You once said that you were able to execute
> that db.execute statement with no problem, so somewhere there must be
> a difference in our codes that parse the SQL statements.  Could that
> be a difference in Postgres library versions?  I'm running postgres
> version 7.3.4.

If DBF driver is used, Postgres is not involved.

'DATE' is reserved word in SQL standard. DBF considers DATE to be 
key word and does not accept it as field name.
It may happen, that column names in input format do not comply with 
SQL standard. We need some function which converts column names 
to names accepted by SQL standard. Until this function 
is ready, there is a hack in v.in.ogr/main.c 447-466 which does  
partialy this transformation.

Radim