[GRASS5] 5.7: dbf driver and 'double' initialization

There seems to be a problem with the initialization of
double (maybe also int) type in the DBF driver of 5.7:

echo "ALTER TABLE dourokukan ADD COLUMN forward double" | db.execute
echo "ALTER TABLE dourokukan ADD COLUMN backward double" | db.execute
echo "ALTER TABLE dourokukan ADD COLUMN length double" | db.execute

dbfdump dourokukan.dbf
        cat label forward backward length
          1 s3m (NULL) (NULL) (NULL)
          3 e3.0m (NULL) (NULL) (NULL)
          5 e5.5m (NULL) (NULL) (NULL)
         13 s13.0 (NULL) (NULL) (NULL)
         15 yes (NULL) (NULL) (NULL)

I assume that it should be set to zero (0).
At least I don't know how to access these (NULL) values in
a later SQL update :

echo "UPDATE dourokukan SET forward=50 WHERE forward=0" | db.execute
echo "SELECT * FROM dourokukan" | db.select
cat|label|forward|backward|length
1|s3m|||
3|e3.0m|||
5|e5.5m|||
13|s13.0|||
15|yes|||

Any advice is welcome,

Markus

Hi Gordon,
(cc grass5)

generally I agree, but:

echo "UPDATE dourokukan SET forward=50 WHERE forward is null" | db.execute
DBMI-DBF driver error:
SQL parser error in statement:
UPDATE dourokukan SET forward=50 WHERE forward is null

Error in db_execute_immediate()

WARNING: Error while executing: "UPDATE dourokukan SET forward=50 WHERE
         forward is null
         "

The problem for me is "how to tell GRASS?"...

Regards

Markus

On Tue, Sep 07, 2004 at 01:44:54PM +1000, Gordon Keith wrote:

Knowing nothing about dbf in grass, but a bit about SQL, the behaviour
indicated would seem to me to be absolutely correct.

The SQL update (assuming normal SQL rules) you want would be:

echo "UPDATE dourokukan SET forward=50 WHERE forward is null" | db.execute

NULL and zero are very different values.

Regards
Gordon

On Tue, 7 Sep 2004 12:55, Markus Neteler wrote:
> There seems to be a problem with the initialization of
> double (maybe also int) type in the DBF driver of 5.7:
>
> echo "ALTER TABLE dourokukan ADD COLUMN forward double" | db.execute
> echo "ALTER TABLE dourokukan ADD COLUMN backward double" | db.execute
> echo "ALTER TABLE dourokukan ADD COLUMN length double" | db.execute
>
> dbfdump dourokukan.dbf
> cat label forward backward length
> 1 s3m (NULL) (NULL) (NULL)
> 3 e3.0m (NULL) (NULL) (NULL)
> 5 e5.5m (NULL) (NULL) (NULL)
> 13 s13.0 (NULL) (NULL) (NULL)
> 15 yes (NULL) (NULL) (NULL)
>
> I assume that it should be set to zero (0).
> At least I don't know how to access these (NULL) values in
> a later SQL update :
>
> echo "UPDATE dourokukan SET forward=50 WHERE forward=0" | db.execute
> echo "SELECT * FROM dourokukan" | db.select
> cat|label|forward|backward|length
> 1|s3m|||
> 3|e3.0m|||
> 5|e5.5m|||
> 13|s13.0|||
> 15|yes|||
>
> Any advice is welcome,
>
> Markus
>
>
> _______________________________________________
> grass5 mailing list
> grass5@grass.itc.it
> http://grass.itc.it/mailman/listinfo/grass5

--

Gordon Keith
Programmer/Data Analyst
Marine Acoustics
CSIRO Marine Research
http://www.marine.csiro.au

"Computers in the future may weigh no more than than 1.5 tons."
- Popular Mechanics, 1949

--
Markus Neteler <neteler itc it> http://mpa.itc.it
ITC-irst - Centro per la Ricerca Scientifica e Tecnologica
MPBA - Predictive Models for Biol. & Environ. Data Analysis
Via Sommarive, 18 - 38050 Povo (Trento), Italy

Markus Neteler wrote:

generally I agree, but:

echo "UPDATE dourokukan SET forward=50 WHERE forward is null" | db.execute
DBMI-DBF driver error:
SQL parser error in statement:
UPDATE dourokukan SET forward=50 WHERE forward is null

Error in db_execute_immediate()

WARNING: Error while executing: "UPDATE dourokukan SET forward=50 WHERE
         forward is null
         "

The problem for me is "how to tell GRASS?"...

The SQL parser used by the DBF driver doesn't recognise the "IS"
keyword (or "ISNULL" or "NOTNULL"), nor does it understand the
"<expr> NOT NULL" syntax. The parser will accept:

  ... WHERE forward = NULL

However, that won't work; all operators return NULL if any of their
operands are NULL, so "NULL = NULL" is NULL rather than TRUE.

[Just like r.mapcalc; except r.mapcalc has an isnull() function, which
is an exception to the "result is NULL if any arguments are NULL"
rule. The DBF driver doesn't have any exceptions.]

At a glance, it looks like it would be fairly straightforward to fix.
Try the attached (and completely untested) patch.

--
Glynn Clements <glynn.clements@virgin.net>

(attachments)

dbf.diff (2.39 KB)

'NULL' as default is OK, I think. 'IS NULL' is not supported
by dbf driver. In this special case, why do you need to use
'WHERE'? All values are 'NULL' so you can maybe just
'UPDATE dourokukan SET forward=50'?

Radim

On Tuesday 07 September 2004 04:55, Markus Neteler wrote:

There seems to be a problem with the initialization of
double (maybe also int) type in the DBF driver of 5.7:

echo "ALTER TABLE dourokukan ADD COLUMN forward double" | db.execute
echo "ALTER TABLE dourokukan ADD COLUMN backward double" | db.execute
echo "ALTER TABLE dourokukan ADD COLUMN length double" | db.execute

dbfdump dourokukan.dbf
        cat label forward backward length
          1 s3m (NULL) (NULL) (NULL)
          3 e3.0m (NULL) (NULL) (NULL)
          5 e5.5m (NULL) (NULL) (NULL)
         13 s13.0 (NULL) (NULL) (NULL)
         15 yes (NULL) (NULL) (NULL)

I assume that it should be set to zero (0).
At least I don't know how to access these (NULL) values in
a later SQL update :

echo "UPDATE dourokukan SET forward=50 WHERE forward=0" | db.execute
echo "SELECT * FROM dourokukan" | db.select
cat|label|forward|backward|length
1|s3m|||
3|e3.0m|||
5|e5.5m|||
13|s13.0|||
15|yes|||

Any advice is welcome,

Markus

_______________________________________________
grass5 mailing list
grass5@grass.itc.it
http://grass.itc.it/mailman/listinfo/grass5

On Tue, Sep 07, 2004 at 10:02:41AM +0200, Radim Blazek wrote:

'NULL' as default is OK, I think. 'IS NULL' is not supported
by dbf driver. In this special case, why do you need to use
'WHERE'? All values are 'NULL' so you can maybe just
'UPDATE dourokukan SET forward=50'?

For the simple example which I have sent the WHERE is not
required. But it was just a simple example to illustrate
the problem (it was working some time ago...).

Markus

PS: will try Glynn's patch later today

On Tuesday 07 September 2004 04:55, Markus Neteler wrote:
> There seems to be a problem with the initialization of
> double (maybe also int) type in the DBF driver of 5.7:
>
> echo "ALTER TABLE dourokukan ADD COLUMN forward double" | db.execute
> echo "ALTER TABLE dourokukan ADD COLUMN backward double" | db.execute
> echo "ALTER TABLE dourokukan ADD COLUMN length double" | db.execute
>
> dbfdump dourokukan.dbf
> cat label forward backward length
> 1 s3m (NULL) (NULL) (NULL)
> 3 e3.0m (NULL) (NULL) (NULL)
> 5 e5.5m (NULL) (NULL) (NULL)
> 13 s13.0 (NULL) (NULL) (NULL)
> 15 yes (NULL) (NULL) (NULL)
>
> I assume that it should be set to zero (0).
> At least I don't know how to access these (NULL) values in
> a later SQL update :
>
> echo "UPDATE dourokukan SET forward=50 WHERE forward=0" | db.execute
> echo "SELECT * FROM dourokukan" | db.select
> cat|label|forward|backward|length
> 1|s3m|||
> 3|e3.0m|||
> 5|e5.5m|||
> 13|s13.0|||
> 15|yes|||
>
> Any advice is welcome,
>
> Markus
>
>
> _______________________________________________
> grass5 mailing list
> grass5@grass.itc.it
> http://grass.itc.it/mailman/listinfo/grass5

Markus Neteler wrote:

thanks for the patch. A minor thing might be missing:

echo "UPDATE dourokukan SET testc=50 WHERE testc is NULL" | db.execute
DBMI-DBF driver error:
Unknown operator 18

Right; there are two switch statements which examine the operator. I
extended the one in eval_node(), but missed the one in
eval_node_type(). Try the attached patch.

--
Glynn Clements <glynn.clements@virgin.net>

(attachments)

dbf2.diff (322 Bytes)

On Wed, Sep 08, 2004 at 08:47:26AM +0100, Glynn Clements wrote:
Content-Description: message body and .signature

Markus Neteler wrote:

> thanks for the patch. A minor thing might be missing:
>
> echo "UPDATE dourokukan SET testc=50 WHERE testc is NULL" | db.execute
> DBMI-DBF driver error:
> Unknown operator 18

Right; there are two switch statements which examine the operator. I
extended the one in eval_node(), but missed the one in
eval_node_type(). Try the attached patch.

Thank you very much, Glynn. Now it works perfectly.
Please submit to CVS, I don't have access to my office
machine (being far away and traveling) in the next days.

Markus