1.02

update tbl set veld2 = veld2 + 'a' where veld1 = 1  //veld 2 is a char(10)
field

the query plan tells me that one row is updated, but that's not right, the
row is unchanged!
...either the statment is invalid (and an error should be raised), or is it a
bug?

Regards, Harry

============================================================================
====
SQL Update (Executed by ElevateDB 1.02 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

============================================================================
====


UPDATE "tbl"
SET "veld2" = "veld2" + 'a'
WHERE "veld1" = 1

Target Table
------------

tbl: 1011204 rows

Filtering
---------

The following filter condition was applied to the tbl table:

"veld1" = 1 [Index scan: 1 keys, 4096 bytes estimated cost]

============================================================================
====
1 row(s) updated in 0,015 secs
============================================================================
====

the same happens with update tbl set veld2 = veld2 || 'a' where veld1 = 1

//instead + here the || is used. The query plan shows exactly the same (so
with +)

but the row did not change at all

Regards, Harry

Harry,

<< update tbl set veld2 = veld2 + 'a' where veld1 = 1  //veld 2 is a
char(10) field

the query plan tells me that one row is updated, but that's not right, the
row is unchanged! ..either the statment is invalid (and an error should be
raised), or is it a bug? >>

CHAR columns are fixed-length columns, meaning that they are padded to the
length of the column with spaces.  To append a string to the end of an
existing value, you'll need to trim the column value like this:

update tbl set veld2 = RTRIM(veld2) + 'a' where veld1 = 1

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< CHAR columns are fixed-length columns, meaning that they are padded to
the length of the column with spaces.  To append a string to the end of an
existing value, you'll need to trim the column value like this: >>

This is a good example of the implications/disadvantages of silently
truncating data instead of raising a proper exception, such that the user is
informed that the assignment source does not fit the target.

Ole Willy Tuv

Tim,

I changed the fieldtype to VARCHAR(10) but that doesn't change it. You still
can't use "update tbl set veld2 = veld2 + 'a' where veld1 = 1". My guess was
that varchar isn't fixed so it should work. Is this proper behavior and
should one always use TRIM functions.

Also, the lack of an error (or warning) here is not the nicest way of
handling this I think (set me on the wrong foot -still limping).

Still, what I have been testing now is keeping me very happy so far.
ElevateDB is very fast and has lots of functionality (we are still -but very
satisfied- 'on' dbIsam 3.3). Pitty we have to wait till summer to see SQL
scripting again, but as I read there is lots of new things to come there
too, so the expectations are high  

Regards, Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:DAEDCE53-FF97-4759-9223-EB03C1EF78E3@news.elevatesoft.com...
> Harry,
>
> << update tbl set veld2 = veld2 + 'a' where veld1 = 1  //veld 2 is a
> char(10) field
>
>  the query plan tells me that one row is updated, but that's not right,
the
> row is unchanged! ..either the statment is invalid (and an error should be
> raised), or is it a bug? >>
>
> CHAR columns are fixed-length columns, meaning that they are padded to the
> length of the column with spaces.  To append a string to the end of an
> existing value, you'll need to trim the column value like this:
>
> update tbl set veld2 = RTRIM(veld2) + 'a' where veld1 = 1
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

<< I changed the fieldtype to VARCHAR(10) but that doesn't change it. You
still can't use "update tbl set veld2 = veld2 + 'a' where veld1 = 1". My
guess was that varchar isn't fixed so it should work. >>

You changed the column type, but the column data still contains padded data.
You'll need to update the data so that it doesn't contain trailing spaces
anymore.

<< Also, the lack of an error (or warning) here is not the nicest way of
handling this I think (set me on the wrong foot -still limping). >>

It's only an issue with CHAR columns, which should only be used sparingly.

<< Still, what I have been testing now is keeping me very happy so far.
ElevateDB is very fast and has lots of functionality (we are still -but very
satisfied- 'on' dbIsam 3.3). Pitty we have to wait till summer to see SQL
scripting again, but as I read there is lots of new things to come there
too, so the expectations are high  >>

Good to hear.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< This is a good example of the implications/disadvantages of silently
truncating data instead of raising a proper exception, such that the user is
informed that the assignment source does not fit the target. >>

I understand, but again, you're looking at it from the outside and not with
any understanding of how EDB is designed.  The way it works right now, it is
possible that such an exception would be going off like a faulty car alarm
on a noisy street during most operations.  IOW, it would take some time to
put something in that wasn't more trouble than it was worth.  All values in
EDB descend from the same object(s), so there is a lot of overlap into areas
where we don't want such exceptions.

--
Tim Young
Elevate Software
www.elevatesoft.com