Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 21 total |
NewId and Duplicates |
Wed, Dec 12 2007 8:37 AM | Permanent Link |
DavidS | 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 |
Wed, Dec 12 2007 2:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Dec 12 2007 3:25 PM | Permanent Link |
DavidS | 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 |
Fri, Dec 14 2007 12:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sun, Dec 16 2007 9:49 AM | Permanent Link |
DavidS | 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? |
Mon, Dec 17 2007 3:42 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Dec 17 2007 4:25 PM | Permanent Link |
"Fons Neelen" | 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 |
Mon, Dec 17 2007 6:39 PM | Permanent Link |
DavidS | Yes it makes sense to have a INSERTUPDATE command.
V. Excited for tommorrow. Regards, David |
Tue, Dec 18 2007 9:24 AM | Permanent Link |
Eryk Bottomley | 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 |
Tue, Dec 18 2007 12:13 PM | Permanent Link |
DavidS | 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 |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |