Hi All,

I have two questions:

Firstly is there a NewId function which will allow me to create new GUIDs in a sproc?

Secondly what is the best and FASTEST way to insert a record and if that record already exists do an update instead, my choices are:
1) First check by looking through indexed records to see if the Id already exists and if it does update it else insert it
2) Always insert the record, but the insert should be in a try catch block and wait for the duplicate key error, at which point i then do an update. All in a sproc so that we dont have slow clr exceptions.
3) some other way.

I want to stress that I am looking for the fastest way and that the id fields are indexed.

A little code to accompany the answer would be great.

Regards,

David

David,

<< Firstly is there a NewId function which will allow me to create new GUIDs
in a sproc? >>

Yes:

http://www.elevatesoft.com/edb1sql_current_guid.htm

<< Secondly what is the best and FASTEST way to insert a record and if that
record already exists do an update instead, my choices are:
1) First check by looking through indexed records to see if the Id already
exists and if it does update it else insert it
2) Always insert the record, but the insert should be in a try catch block
and wait for the duplicate key error, at which point i then do an update.
All in a sproc so that we dont have slow clr exceptions.
3) some other way. >>

The fastest way would be for EDB to handle this as part of the INSERT (on
our enhancements list).  However, the only safe way to do so currently is
via 2).   However, if you're using the local engine under .NET, then any
procedure exceptions in a begin..exception..end block will still use CLR
exceptions under the hood to perform the actual exception processing.
There's no way around that other than to use the Win32 ElevateDB Server
instead.

You can find an example of a begin..exception..end block here:

http://www.elevatesoft.com/edb1sql_exception.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for that.

One of the main reasons I am wating for the new 1.07 version with its ADO.net drivers is because the speed that Sql CE (our current DB) inserts records and raises errors is far too slow, I understood
that this would be faster in ElevateDB. Is it possible to have this new feature out any time soon. I noticed that it was mentioned in a forum post in June '07. I know you are a busy man, but I thought a
small nag could not hurt

Regards,

David

David,

<< One of the main reasons I am wating for the new 1.07 version with its
ADO.net drivers is because the speed that Sql CE (our current DB) inserts
records and raises errors is far too slow, I understood that this would be
faster in ElevateDB. Is it possible to have this new feature out any time
soon. I noticed that it was mentioned in a forum post in June '07. >>

Actually we've had this request for even longer than that.  It's never
really been an issue in the Win32 world, however (catching the exception and
trying an update instead).

--
Tim Young
Elevate Software
www.elevatesoft.com

So in the managed code this will be a problem, Sorry Chris but Im a little unclear, as I can only find one version of 1.6, I think its win32. Also you left this unanswered. its a request which has become
more valid due to the speed managed code raises exceptions, will it be part of 1.07, which I have read from another thread will be out this weekend?

David,

<< So in the managed code this will be a problem, Sorry Chris but Im a
little unclear, as I can only find one version of 1.6, I think its win32.
Also you left this unanswered. its a request which has become more valid due
to the speed managed code raises exceptions, will it be part of 1.07, which
I have read from another thread will be out this weekend? >>

I'm starting on the 1.07 builds tomorrow.  Today I'm running the tests.

As for the INSERT/UPDATE issue, I did some more looking into this and the
only way we can implement such an item would be to try the INSERT and then
revert to an UPDATE if there is a constraint violation for the primary key.
Due to the way the row locking needs to operate, this is the only way it can
be done in EDB.  So, any way that you slice it, EDB will have to respond to
an exception in order to convert an INSERT into an UPDATE.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> As for the INSERT/UPDATE issue, I did some more looking into this and the
> only way we can implement such an item would be to try the INSERT and then
> revert to an UPDATE if there is a constraint violation for the primary
> key. Due to the way the row locking needs to operate, this is the only way
> it can be done in EDB.  So, any way that you slice it, EDB will have to
> respond to an exception in order to convert an INSERT into an UPDATE.

I do not like the idea for an INSERT that fails to automatically change into
an UPDATE statement. It would certainly break my code. But as I am not the
only user of ElevateDB (fortunately)  I guess this feature, if implemented
at all, will be done by extending the INSERT statement with an additional
parameter such as AUTOUPDATE (just example) for those who wish this kind of
automation.

Best regards,
Fons

Yes it makes sense to have a INSERTUPDATE command.

V. Excited for tommorrow.

Regards,

David

Fons,

> I do not like the idea for an INSERT that fails to automatically change
> into an UPDATE statement. It would certainly break my code. But as I am
> not the only user of ElevateDB (fortunately)  I guess this feature, if
> implemented at all, will be done by extending the INSERT statement with
> an additional parameter such as AUTOUPDATE (just example) for those who
> wish this kind of automation.

Its a bit of a strange feature. I've only seen it once before and that
was in Paradox for DOS V4. There you could append a 'FORCEPOST' flag to
a post record command and it would overwrite any existing resord that
caused key violation (assuming it could get a lock of course).

That was a bit weird, but at least behaviour was consistent because
PdoxDOS only supported the concept of primary keys - not foreign keys.
What happens if a new record conflicts with two other existing records,
one on the PX and another on a FX? Which one is it going to overwrite?
Presumably the PX gets checked first, but what if overwriting that still
causes an FX conflict? I smell a can of worms here. This sort of thing
is better handled in application level code that "understands" the
logical semantics of the data it is dealing with.

Eryk

Eryk,

Your points are valid, my only concern with having to deal with the exception is the speed c# takes to raise the exception, this means adding\updating records which may or may not already be in the
DB takes ages, since every time an exception is raised it takes time.

Tim can choose a solution according to whichever design his experience tells him is best, my only issue here is the speed it takes to get to the update once the insert has failed.

regards,

David

Eryk Bottomley <no@way.com> wrote:

Fons,

> I do not like the idea for an INSERT that fails to automatically change
> into an UPDATE statement. It would certainly break my code. But as I am
> not the only user of ElevateDB (fortunately)  I guess this feature, if
> implemented at all, will be done by extending the INSERT statement with
> an additional parameter such as AUTOUPDATE (just example) for those who
> wish this kind of automation.

Its a bit of a strange feature. I've only seen it once before and that
was in Paradox for DOS V4. There you could append a 'FORCEPOST' flag to
a post record command and it would overwrite any existing resord that
caused key violation (assuming it could get a lock of course).

That was a bit weird, but at least behaviour was consistent because
PdoxDOS only supported the concept of primary keys - not foreign keys.
What happens if a new record conflicts with two other existing records,
one on the PX and another on a FX? Which one is it going to overwrite?
Presumably the PX gets checked first, but what if overwriting that still
causes an FX conflict? I smell a can of worms here. This sort of thing
is better handled in application level code that "understands" the
logical semantics of the data it is dealing with.

Eryk