Adam,

<< When I try to run this code I get the error:

"ElevateDB Error #1004 The primary key constraint ID for the table Staff
has been violated (NULL values are not allowed in a primary key)" >>

Your function doesn't *ever* return a value.  You need a RETURN in
there:

RETURN Result

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< In ElevateDB none of the columns in a primary key are allowed to be NULL
(gave me some grief when it was introduced) but maybe Tim's just being
protective. >>

It's in the SQL standard.  Plus, it makes sense - you'd only get to have one
NULL primary key value, so which coveted row gets the "NULL slot" ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< or whatever the syntax really is. I know DEFAULT can't call external
>functions from a dll but I think it can from sql functions. >>
>
>DEFAULT expressions can use any user-defined function, including those that
>refer to external modules. Same with GENERATED expressions. COMPUTED
>expressions can not, however, use user-defined functions due to the remote
>client-side/no catalog access constraint.

Sorry. I obviously need a memory upgrade!

Roy Lambert

Tim

>It's in the SQL standard.

I know, I wasn't complaining (much)

>Plus, it makes sense - you'd only get to have one
>NULL primary key value, so which coveted row gets the "NULL slot" ?

I was talking multi part keys eg

1-NULL
2-NULL

Which me (failing) memory tells me were allowed initially then were banned and that transition is what caused me grief.

Roy Lambert

Adam,

I have a project coming up which I think has a similar requirement to yours.

Users will work on a local database and the data will be periodically uploaded to a central database.  I am thinking of
using a guid as the primary key to avoid having to worry about conflicts between different sites.  I know they will take
up some additional space but I don't know if there are other potential performance issues.

Has anyone got experience of this with EDB/DBIsam ?

John

>>Users will work on a local database and the data will be periodically uploaded to a central database.  
>>I am thinking of using a guid as the primary key to avoid having to worry about conflicts between
>>different sites.  I know they will take up some additional space but I don't know if there are other
>> potential performance issues.

Hi John,

This thread

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&msg=13392&page=1

covers a fair bit on replication / merging etc. & Cornelius, who set up his system uses GUIDs & says they work well. They are well supported by EDB anyway, so you won't have a problem there. You are right that a GUID is the "easy" answer & I have used these in other contexts, they are brilliant. The performance overheads are apparently no longer really an issue.

The particular framework I am designing in this case really needs INTEGERs for the primary key because of the way that the main application is programmed, otherwise I might use GUIDs.

While it isn't good from a Cod/Normalization perspective, I still like the fact that an INTEGER has an order, so you can look at it & know which other records were inserted before / after. Our can use TIMESTAMPs for this too of course, the but ordinality of the INT is somehow reassuring.

Adam

Thanks for the link.  Definitely food for thought!

John

Adam,

<< While it isn't good from a Cod/Normalization perspective, I still like
the fact that an INTEGER has an order, so you can look at it & know which
other records were inserted before / after. Our can use TIMESTAMPs for this
too of course, the but ordinality of the INT is somehow reassuring. >>

I should see about surfacing the lamport timestamp counters that we have in
EDB now for the replication:

http://en.wikipedia.org/wiki/Lamport_timestamps

Each EDB engine instance has the ability to dish out a proper incrementing
64-bit timestamp that is loosely based upon the current timestamp
(date/time) and any interactions with other EDB systems via replication.
It's how EDB orders all insert/update/delete operations across the
distributed systems.  However, because this is used as an ordering mechanism
and not a unique identity mechanism, there is still the possibility of
conflicts.  Creating a compound primary key on site ID + timestamp would
resolve this, though.

Just using timestamps, by themselves, is tough - by default there is a 15ms
resolution, so there can be a lot of duplicate timestamps if inserts are
coming in rapidly.

--
Tim Young
Elevate Software
www.elevatesoft.com

>>Each EDB engine instance has the ability to dish out a proper incrementing
>>64-bit timestamp that is loosely based upon the current timestamp
>>(date/time) and any interactions with other EDB systems via replication.

Can these be called within an EDB Function & if so how?
Also, can they be read as INTEGER types?

Adam

>>>Each EDB engine instance has the ability to dish out a proper incrementing
>>>64-bit timestamp that is loosely based upon the current timestamp
>>>(date/time) and any interactions with other EDB systems via replication.
>
>Can these be called within an EDB Function & if so how?
>Also, can they be read as INTEGER types?


To answer your question read this bit again

<<I should see about surfacing the lamport timestamp counters that we have in
EDB now for the replication:>>

ie not yet.

Roy Lambert