Columns display in list layout could use a column indicating there is a default set.

I came to this conclusion because of the following: I was just scrolling through a table using the arrow keys. Hit the bottom and had a nice error

---------------------------
ElevateDB Manager
---------------------------
ElevateDB Error #1011 An error occurred with the value Administrator (The value would result in truncation)
---------------------------
OK  
---------------------------


The only way out of this is to click OK, close the table and reopen since every row, every column is set to null. (Tim I regard this as something that needs sorting)

I eventually traced it to having a field default set to CURRENT_USER. Users on my system will have a user ID no longer than 5 characters (the length of the field) and for convenience I'm logged onto ElevateDB as Administrator. I accept I'm going to have to suffer the slings and arrows of outrageous SQL but can EDBManager fail a bit more gracefully please.

Roy Lambert

Roy,

<< I eventually traced it to having a field default set to CURRENT_USER.
Users on my system will have a user ID no longer than 5 characters (the
length of the field) and for convenience I'm logged onto ElevateDB as
Administrator. I accept I'm going to have to suffer the slings and arrows of
outrageous SQL but can EDBManager fail a bit more gracefully please. >>

Unfortunately this is the nature of the TDataSet architecture.  If an
exception is raised in any area of the engine when the grid is retrieving
data due to a repaint, then it will behave like that.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< I eventually traced it to having a field default set to CURRENT_USER.
>Users on my system will have a user ID no longer than 5 characters (the
>length of the field) and for convenience I'm logged onto ElevateDB as
>Administrator. I accept I'm going to have to suffer the slings and arrows of
>outrageous SQL but can EDBManager fail a bit more gracefully please. >>
>
>Unfortunately this is the nature of the TDataSet architecture. If an
>exception is raised in any area of the engine when the grid is retrieving
>data due to a repaint, then it will behave like that.

So an exception is raised and it sets the entire grid to NULLs requiring a close and reopen. I know what my customers reactions would be. It does need sorting rather than excusing.

Roy Lambert

Tim

On the positive side (having just checked) you're preventing excess characters being entered in the grid by hand.

Roy Lambert

Roy,

<< So an exception is raised and it sets the entire grid to NULLs requiring
a close and reopen. I know what my customers reactions would be. It does
need sorting rather than excusing. >>

I'm not excusing anything.  I'm trying to explain to you what is happening.
ElevateDB is not setting the grid to NULLs, the base TDataSet component is
doing so because it isn't handling an exception very nicely in this case.
There's not much we can do about it - if we trap the exception and don't
show anything, then you're left wondering why your default value isn't
appearing.  If we let the exception bubble up, then you get the issue that
you're seeing.  The solution is to fix your default value or column size so
that it doesn't cause truncation.  With the exception, ElevateDB is trying
to tell you that you're doing something wrong.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I don't know what the exception is but its your error message that's appearing. Its behaviour that's been introduced in ElevateDB (ie not right truncating) that causes it.

Nothing I can try using DBISAM causes the same error, nor do I remember any error of this nature using the BDE. However, trying with just a standard vcl grid and ElevateDB components does cause it.

This now means that its not just an annoying "feature" in EDBManager but more serious. Some way is needed to handle this without having to validate all user input (and I have no idea where I would validate the default value for a field) because users are notoriously good at finding little gems like this.

What about an OnTruncate event?

Roy Lambert

Tim


Just continuing to think about this issue. If you can't manage an OnTruncate because its not under your control event how about an OnSetDefaults event which should be?

Roy Lambert

Roy,

<< I don't know what the exception is but its your error message that's
appearing. Its behaviour that's been introduced in ElevateDB (ie not right
truncating) that causes it. >>

Yep.  ElevateDB follows the SQL 2003 standard behavior in terms of raising
an error when there is truncation.  DBISAM did not do this because it wasn't
compliant.

<< This now means that its not just an annoying "feature" in EDBManager but
more serious.>>

How on earth is it serious ?  You have complete control over how the default
value is defined for the table, as well as the size of the target column
that you're defining it for.  All you need to do is either make the default
value:

LEFT(CURRENT_USER(), <Column Length>)

or make the column be large enough to be able to fit any user name, which
means making it 40 characters or longer.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Forget this specific example, concentrate on the general principle. There is an error which I cannot trap for being allowed out into the wild.

The ONLY way I can see to prevent it is to check every input into a varchar (I don't use fixed length string fields) from any source. If I allow users to assign defaults to certain fields that again has to be checked, or adjusted to make sure its OK.

The behaviour of the error differs depending on what controls are present on a form. With just one DBGrid it zaps the contents of the grid, if two grids are present, with both hooked up to the same table, both hooked up to different instances of the same table, or each hooked up to a different table then you get a duff row inserted. Attempts to delete or amend that row generate a further error.

---------------------------
Project1
---------------------------
ElevateDB Error #601 The table ELN is corrupt (Invalid row number specified for retrieval from cache)
---------------------------
OK  
---------------------------


If there's a TEDBEdit on the form its left pointing to a different row than the erroneous one shown in the grid. I haven't yet bottomed the behaviour totally, especially a DBNavigator. Different things seem to happen and I'm not sure yet just what's causing what.

I think its serious.

Roy Lambert

Roy,

<< Forget this specific example, concentrate on the general principle. There
is an error which I cannot trap for being allowed out into the wild. >>

Yes, because there's really no way of interrupting the process of assigning
the default values other than via an exception.  The TDataSet architecture
is such that we get one method call (InternalInitRecord) to initialize a
row, and we use it to assign the default values from the columns so that
they are visible at insert time.  Now, you may want to suppress this
exception, but I still think that's a bad idea since it simply kicks the can
down the road and causes the user to wonder where the default value has
gone, or why it is truncated for no apparent reason.

In summary, even if there were an event handler that trapped such an
exception, it would be forced to:

1) Simply adjust the column default value so that it fit the column (not
exactly easy to do since you would need to know what the default expression
is to start with, and have a method of evaluating the SQL to generate a
value).

2) Exchange the real exception for a silent abort exception, thus
encountering the same grid display issues that you're seeing elsewhere.

You simply cannot have an exception in the InternalInitRecord without it
causing weird issues with the TDataSet component.  The design is such that
is does not expect exceptions in this method, and there's nothing I can do
about that short of telling you how to modify the db.pas to have it trap for
such an occurrence and handle it properly:

procedure TDataSet.Append;
var
 Buffer: PChar;
begin
 BeginInsertAppend;
 ClearBuffers;
 Buffer := FBuffers[0];
 InitRecord(Buffer);  <<<<<<<<<< This is where the exception occurs.
Notice that there is *zero* exception handling here and that the buffers are
already moved around in a way that assumes that there won't be any
exceptions
 SetBookmarkFlag(Buffer, bfEOF);
 FRecordCount := 1;
 FBOF := False;
 GetPriorRecords;
 InternalInsert;
 EndInsertAppend;
end;

It seems to me that you should be using the OnNewRecord event instead of the
built-in SQL defaults if you want to assign default values to columns that
exceed the column length.  The built-in defaults must issue an exception if
there is truncation, and that isn't what you want.  You're also free to make
a descendant of TEDBTable that simply eats any exceptions in the
InternalInitRecord method.

--
Tim Young
Elevate Software
www.elevatesoft.com