Tim

>
> Well, it's always possible to add the functionality for custom *server*
> functionality to EDB, which I think is what you're asking for.  They would
> not have any security at all and would simply be a way of doing RPC with the
> database server, thus making them much, much different than an SQL stored
> procedure that is managed by the engine in terms of access and security,
> etc.
>

is this the description of your current server procedures? So why is it
difficult to keep this functionality?

Arnd

Tim,

> Well, it's always possible to add the functionality for custom *server*
> functionality to EDB, which I think is what you're asking for.

Correct.

> They would
> not have any security at all and would simply be a way of doing RPC with the
> database server,

For stored procedures, yes, but not for triggers. I don't see the
security implications as being significant since in most cases
'database' level security is too crude and has to be augmented in the
'business' layer anyway. Virtually no database can implement 'real
world' rules like: "Dave is allowed to update the ORDERS table but only
on Fridays and only in respect of customers based in Gabon that are not
currently flagged by credit control."

> thus making them much, much different than an SQL stored
> procedure that is managed by the engine in terms of access and security,
> etc.

Stored procedures of that sort are a "me too" feature that almost all
C/S databases have. Certainly it is great idea to support them, but it
isn't a differentiating factor elevating EDB above the competition. The
current engine component events on the other hand are such a feature and
knock all competitors bar one out of consideration (for those of us with
a need for such capabilities).

Eryk

Arnd,

<< is this the description of your current server procedures? >>

Pretty much, yes.  They do have security in DBISAM, but it's kind of a pain
in the butt to manage.

<< So why is it difficult to keep this functionality? >>

It's not.  However, I can't promise that it will be in ElevateDB out of the
gate because it's not in there now.

--
Tim Young
Elevate Software
www.elevatesoft.com

Eryk,

<< For stored procedures, yes, but not for triggers. I don't see the
security implications as being significant since in most cases 'database'
level security is too crude and has to be augmented in the 'business' layer
anyway. Virtually no database can implement 'real world' rules like: "Dave
is allowed to update the ORDERS table but only on Fridays and only in
respect of customers based in Gabon that are not
currently flagged by credit control." >>

Of course you can - just define an updateable view that uses the criteria
that you specified and only give Dave UPDATE privileges on that view instead
of on the real table.

<< Stored procedures of that sort are a "me too" feature that almost all C/S
databases have. Certainly it is great idea to support them, but it isn't a
differentiating factor elevating EDB above the competition. The current
engine component events on the other hand are such a feature and knock all
competitors bar one out of consideration (for those of us with a need for
such capabilities). >>

Coming from your perspective, this is the case.  There's a reason why
stored procedures are necessary and are widely implemented.  It's the same
reason that triggers are around, as well as RI, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Of course you can - just define an updateable view that uses the criteria
> that you specified and only give Dave UPDATE privileges on that view instead
> of on the real table.

I meant it can't be done via mouse clicks in SrvAdmin. Sure, you can
'code' security like that via SQL, but then you can code security into
the engine component event handlers as well.

> Coming from your perspective, this is the case.  There's a reason why
> stored procedures are necessary and are widely implemented.  It's the same
> reason that triggers are around, as well as RI, etc.

Sure, I agree. Having said that, there does seem to be a move towards
allowing SPs to be written in languages more capable than pure SQL
lately (SQL Server and .NET languages, for example). In that sense the
big boys are only now catching up with a feature DBISAM has had for years

Eryk

Tim,

>
> << So why is it difficult to keep this functionality? >>
>
> It's not.  However, I can't promise that it will be in ElevateDB out of the
> gate because it's not in there now.
>

you should make it because it is blistering good stuff!!!

Arnd

Tim,

> Yes, EDB's temporary tables don't have this problem anymore.  Temporary
> tables are not stored in the catalog, and the lock file doesn't have any
> data in it at all anymore.

Thank you.  That alone is worth the upgrade!

> RI is supported, yes.  Initially you will not be able to disable the RI at
> any time short of dropping the constraints from the table.  Question,
> though - when you say "merging primary keys", do you mean merging multiple
> rows with the same primary key, or something else ?  I reviewed the
> scenarios under which not being able to disable RI would be an issue, and
> they were very limited in the sense that even if it were an issue you
> could easily do a couple of INSERT/UPDATE statements that accomplished the
> same thing.

Sorry for the delay in getting back to you.  Been a busy couple of days.

I have a table called SKU that has a primary key on the code field with a
restrict RI rule on deletes to prevent deletion of a primary key with
references in other tables.  The other RI rule of consequence is that
changes to key values are cascaded throughout all of the tables with foreign
keys to this field.

The situation that I would like to work around comes in when someone makes a
mistake and creates two separate entries in the SKU table where there should
have been only one, such as INTEL-DUO-2-E6700 and INTEL-DUO2-E6700 (and yes,
I have been shopping for a new development system).  The correct entry was
the second one and the error was not caught until hundreds of invoices were
done and thousands of product was received.

In order to clean this up, I would have to build and execute query to update
code field value INTEL-DUO-2-E6700 to INTEL-DUO2-E6700 for each table that
has references back to SKU.CODE, then delete the incorrect entry from the
SKU table.   At this point, I have about 35 tables that would have to be
updated manually before I could get rid of the duplicate.  And each time I
add a new table to the application, I have to remember to go back and add
another statement to the list of queries required to merge two primary keys.

Since the RI functionality of the database engine would already handle
updating all of the values in the related tables if I change a key value, I
was hoping that there would be some function or call in DBISAM that would
allow me to rename a key field value into an EXISTING key value, have the RI
update all of the related tables, without throwing up a duplicate key
violation.  The other way of handling it would be to temporarily disable
the restrict RI rule on the SKU table and do one rename to process all of
the reference updates by RI.

Any possibility of this type of functionality in EDB without having to write
multiple update queries?

--

---------------------------------------
 Terry Swiers
 Millennium Software, LLC
 http://www.1000years.com
 http://www.atrex.com

 Atrex Inventory Control/POS -
    Big business features without spending big business bucks!

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Terry,

<< Thank you.  That alone is worth the upgrade! >>

There's a lot of little improvements like that throughout EDB.

<< Since the RI functionality of the database engine would already
handleupdating all of the values in the related tables if I change a key
value, >>

Actually, it won't, at least not initially - EDB only supports the RESTRICT
option.  There are concurrency issues with the locking involved with
cascading updates and deletes, so I had to punt for the time being.  It
*could* do the cascades with straight row locks, which is how the restrict
works, but it would be fairly slow because it would be a row-by-row update
(especially if you're trying to cascade thousands and thousands of rows).
Given these circumstances, I figured it would be best to simply leave it up
to the developer to handle inside of a transaction as needed.

--
Tim Young
Elevate Software
www.elevatesoft.com