This is a migrated thread and some comments may be shown as answers.

ORM Handling of SQL Default Values and Nulls

36 Answers 504 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
John
Top achievements
Rank 1
John asked on 02 Mar 2009, 01:41 AM

I have a SQL Server Database and one of my tables has an column type of uniqueidentifier with a default value of newid() and allow nulls set to true on the column.

Issue:

When adding a new record using ORM and not filling out a uniqueidentifier column, the record gets inserted into the database and a null is inserted into the database even when sql server has a default value defined.  What should happen is sql should insert the default newid() like in the case of records a,c,& d below.  ORM seems to be passing something other than null to sql.  Record C is the inserted record that ORM added.  all other records where inserted null using a different tool other than ORM.

A  df46590b-f7c4-47bf-90e4-ebf2409623e4 1 True Apple  2009-02-18 23:24:15.963
B  e53c92c8-bdd5-42b8-ae29-88812f8c8b74 1 True Big  2009-02-18 23:29:13.033
C  NULL 1 NULL Cat  NULL
D  f8923ced-6d67-4a52-bbe8-3f4339296564 1 NULL  Dog  2009-03-01 20:15:07.130

 

<Telerik.OpenAccess.FieldAlias(

"_sourceGUID")> _

 

 

 

Public Property SourceGUID() As Nullable(Of Guid)

 

 

 

Get

 

 

 

 

Return _sourceGUID

 

 

 

End Get

 

 

 

 

Set(ByVal Value As Nullable(Of Guid))

 

 

 

Me._sourceGUID = Value

 

 

 

End Set

 

 

 

 

End Property

 

 

36 Answers, 1 is accepted

Sort by
0
Jan Blessenohl
Telerik team
answered on 02 Mar 2009, 10:35 AM
Hi John,
OpenAccess does not support server calculated values during insert at the moment. The only place where this can be used is if the field is the identity column. Is you column the pk column or a normal data column?

The workaround is to set the Guid before you persist the new object. It doesn't matter if you do it on the client or the server .

Kind regards,
Jan Blessenohl
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
chris_cf
Top achievements
Rank 2
answered on 30 Mar 2009, 12:21 AM
Hi,
If I do not specify a Guid for the newly created object and add it to the db, it adds the PK column (Guid) record as 00000000-0000-0000-0000-000000000000. I have the PK column set to (newid()) which will generate a new Guid on every new record insert.

How do I get OA to recognize this and not insert the Guid with all zeros?
0
Robert
Top achievements
Rank 2
answered on 30 Mar 2009, 05:31 PM
Hey There,

Try setting the Key Generator mode to GUID for your pkey column in the forward mapping wizard class mapping settings.  That tells orm to generate the Guid for you.

Hope this helps,
Robert
0
chris_cf
Top achievements
Rank 2
answered on 30 Mar 2009, 05:38 PM
Hi Robert,

Thanks, but I am using reverse mapping so this will not work. Any other suggestions?
0
Robert
Top achievements
Rank 2
answered on 30 Mar 2009, 05:41 PM
That's ok, I'm using reverse mapping as well but OpenAccess uses some of the configuration in forward mapping to know what to do.  Try and see what happens :-)
0
chris_cf
Top achievements
Rank 2
answered on 30 Mar 2009, 06:07 PM
Thanks, I'll give this a try. I already switched the PK to int and it's auto incrementing. So, I will give it a shot on another table and let you know if it worked.
0
Jan Blessenohl
Telerik team
answered on 03 Apr 2009, 02:17 PM
Hello chris_cf,
Roberts suggestion is exactly the right way to do it. Sometimes you have to use the Forward Wizard in the reverse scenario as well.

Key generator is one example, the other is FetchPlans. You specify them forward to the xml file and the reverse process will just keep them.

Best wishes,
Jan Blessenohl
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.
0
John
Top achievements
Rank 1
answered on 01 Jun 2009, 08:35 PM
I was just getting back to you on the issue of not supporting calculated values on the insert.
Its currently a show stopper for us because we support some business logic at the db level to handle our batch processing, not all of our business processes/logic are in application code, and don't want to maintain it in two places
With that said, Is there plans to support this on the next release this summer?

thanks

john
0
Jan Blessenohl
Telerik team
answered on 03 Jun 2009, 10:33 AM
Hello John,
Can  you give us a concreate example? Which values do you want to set on server side? Are these additional fields, what do they have to do with the persistent object?

Thanks,
Jan Blessenohl
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
Geoff Davis
Top achievements
Rank 2
answered on 03 Feb 2010, 03:31 PM
Hi Jan,

I'm not sure if this has been resolved but I want to be able to use a default value in SQL Express 2008 for getutcdate(). It's vital that the database on my website generates this value on new records because my frontend db server is in the US and my backend db server is in the UK. I then use the utc date without having to take the timezone into consideration.

Other ORM's i've used in the past do this and I have just taken this for granted that OpenAccess does as well.
0
Jan Blessenohl
Telerik team
answered on 10 Feb 2010, 07:55 AM
Hello Geoff Davis,
We have just added a backend configuration setting that allows you to use a datetime with automatic calculated values on insert and update to generate utc dates. This will be available in the Q1 release in march.

All the best,
Jan Blessenohl
the Telerik team

Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Follow the status of features or bugs in PITS and vote for them to affect their priority.
0
Daniel Plomp
Top achievements
Rank 2
answered on 26 Oct 2010, 10:14 AM
So, what should I do now to set a Guid field in SQL?

Inside my SQL table I have a field that is of type 'GUID' and the default value is 'newid()'.
I now have set inside my Domain Model the field 'Kind' to 'Calculated' and inside my code I manually generate a new GUID.

Is this the way, or should I do it different?

Thanks,
Daniel
0
PetarP
Telerik team
answered on 29 Oct 2010, 05:35 PM
Hello Daniel,

 Currently the only way to work with default values would be to write some additional code that sets it in the constructor of the entity. This way you will not have to set the field to calculated and you will be able to remove the additional set in the code. We do plan to improve this in the future however I cannot provide you with an exact time frame.

Regards,
Petar
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
KevinFarrow
Top achievements
Rank 1
answered on 17 Dec 2010, 02:25 AM
I too need to use default values on the server and was wondering how Open Access generates the insert statement. It looks like it is very inefficient if it always inserts a value into every field. If a default is changed in the database, we don't want to have to recompile the whole system and deploy new DLL's (not to mention that the system could fall over if we didn't make the code change). We also don't want to have to manage the defaults in code and in the database - that would be a recipe for disaster!

In other ORM's that we've used they internally have a dirty flag for each field which means that any inserts or updates are only applied to fields that have actually changed or have new values. There's no point in generating an insert statement consisting of all fields for a table that has, say, 30 fields if I have only added a value for one of the fields. If a Customer table has 30 fields and I insert a new record, for example setting just the 'FirstName' field, then the corresponding update should be something like INSERT INTO Customer(FirstName) VALUES('JOHN')

I noticed this post has been ongoing for a long time and need to know if this is something that will be implemented soon (or at least before I retire :-)
0
PetarP
Telerik team
answered on 20 Dec 2010, 03:05 PM
Hi RDNZL,

 We do plan to implement support for default values however no real work has been thrown into that area as for now. I will escalate the issue to be rediscussed in our team so that we can give it a proper time estimate and a possible implementation. 

Greetings,
Petar
the Telerik team
Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
0
KevinFarrow
Top achievements
Rank 1
answered on 25 Mar 2011, 05:33 PM
Hi,

Has there been any development on adding support for default values?

You said it was going to be discussed but i have not heard anything for over 3 months, so I presume letting customers know what is happening isn't a priority for you guys.

Regards,
Kevin Farrow
0
PetarP
Telerik team
answered on 30 Mar 2011, 04:56 PM
Hello RDNZL,

 Its still on our ToDo list however the low interest in this feature has influenced our decision to postpone the development for the future. I will add a public item for this feature so that others people can vote for it and if it gains some popularity we will certainly rise the priority of implementing it.
Please do let me know if that works for you.

Regards,
Petar
the Telerik team
0
Patrice Boissonneault
Top achievements
Rank 2
answered on 31 Mar 2011, 01:57 AM
Low popularity?!  I think this is a must feature.  Just started implementation of the ORM with OA and hit this show stopper bug.

RDNZL: You mention other ORM were handling that properly, can you please let us know which one is it?

THanks.
0
PetarP
Telerik team
answered on 05 Apr 2011, 01:23 PM
Hello Patrice Boissonneault,

 The pits id of this feature request is 5399. Please vote there so that we can get an actual status update on how important this feature is.

Best wishes,
Petar
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
KevinFarrow
Top achievements
Rank 1
answered on 15 Apr 2011, 03:36 PM
Hi Patrice,

We use EntitySpaces ORM (www.entityspaces.net) which is excellent and works with most databases. You can interrogate the outputted SQL and it works as it should with default columns. For example the following code:

var customer = new Customer();
customer.FirstName = "Kevin";
customer.LastName = "Farrow";
customer.Save();

produces the following SQL:

INSERT INTO Customer(FirstName, LastName) VALUES('Kevin', 'Farrow')

I cant understand why openAccess cannot do the same. I can only image that is is highly inefficient and produces a lot more SQL than is needed.
0
KevinFarrow
Top achievements
Rank 1
answered on 15 Apr 2011, 03:52 PM
Hi Patar,

I too am amazed that this is a low priority for Telerik. We are regularly asked to use existing databases and cannot guarantee that they don't have any default values set in any of the tables. Also, what happens if we develop a system and someone adds a new column to a table and sets a default value for that field. With Open Access it looks like we'd screw the system up and have to look through all of our code for places that were inserting new records into the table, make the necessary changes to the code, recompile and redeploy.

Also we have a real world situation whereby we want people to sign up to a web site by only entering their name, email address and password. The table we use has 25 columns and our columns have defaults and do not allow null values (e.g. CurrencyCode column defaults to 'GBP') - do we have to write code to enter default values into the other 22 columns?

If we create a new record in Open Access and only insert the 3 fields mentioned above, what SQL is generated?

Should it not be the following:

INSERT INTO Customer(Name, Email, Password) VALUES('myName', 'emailAddress', 'myPassword')

What code would Open Access create?
0
PetarP
Telerik team
answered on 20 Apr 2011, 09:54 AM
Hi RDNZL,

 There is a setting in the dsl designer that instructs that a field will get a default value from the database. You can use it when you are doing your inserts however modifying the values later will not be available. The only real workaround would be to manually add those properties to your default constructor so that your objects gets initialized with the correct values. We will re-discuss the priority of this item in our team and perhaps we will start implementing a possible solution for one of our next service packs.

Best wishes,
Petar
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Ivailo
Telerik team
answered on 13 Oct 2011, 03:42 PM
Hi Guys,

I would like to shed some light on how this issue is progressing.

We have discussed and analyzed it, without underestimating the severity of the problem. Considering the growing interest in the issue raised in PITS, we have decided to implement some solution to allow automated usage of default values defined in the database. Due to the heavy development needed to introduce such functionality, I will be introduced in Q1 2012.

Until then, please accept our apologies for the inconvenience caused. The temporary workaround as described is to set the default values manually in a partial class constructor (not in the auto-generated class, since it might be replaced).

I will post any news that we have on this feature in this thread and in PITS.


Kind regards,
Ivailo
the Telerik team

Check out the latest stable build of Telerik OpenAccess ORM. Download it and benefit from our new Project Templates.

0
sitefinitysteve
Top achievements
Rank 2
Iron
Veteran
answered on 01 Dec 2011, 03:44 PM
@Ivalio
  Can you elaborate on the workaround?...If I'm using the OpenAccessLinqDataSource to bind my objects and do auto CRUD operations on the RadControls, how would that work?

Can't really do a partial class constructor override right...

Steve
0
PetarP
Telerik team
answered on 06 Dec 2011, 11:49 AM
Hi Steve,

 Unfortunately the workaround is not applicable to the OpenAccessLinqDataSource. The workaround suggests that you create a partial class to your class that is mapped to a table with default values and in that partial class create a constructor that would assign the default values on each entity create. While the workaround works in most cases it will not work for the data source because the data source always uses the parameterless constructor and that cannot be overwritten. 

All the best,
Petar
the Telerik team

Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

0
Keith Heilveil
Top achievements
Rank 1
answered on 23 Feb 2012, 10:02 PM
I just installed Q1 2012, and this still doesn't appear to be resolved. This is making using the product very difficult.
Is there a real ETA to getting this functionality resolved?
0
KevinFarrow
Top achievements
Rank 1
answered on 24 Feb 2012, 02:22 PM
Hi Keith,

We have been waiting for this to be implemented for over 2 years so I presume this is a low priority for the Open Access team.  The ORM is unfortunately not very good at producing efficient SQL code and from what I can gather attempts to insert NULL's into fields that you have not explicitly entered data for. We use the Entity Spaces ORM which allows us to have fields with default values e.g. our Customer table has over 20 columns, some of which have default values e.g. the SignUpDate column defaults to "getUTCDate()" - to add a quick signup from a web page we have:

var customer = new Customer();
customer.FirstName = "Kevin";
customer.LastName = "Farrow";
customer.EmailAddress = "kevin@revisionsoftware.com";
customer.Save();

the SQL produced is:

INSERT INTO Customer(FirstName, LastName, EmailAddress) VALUES('Kevin', 'Farrow', 'kevin@revisionsoftware.com')

with no silly workarounds!!!!

Kind Regards,
Kevin Farrow
0
Ivailo
Telerik team
answered on 01 Mar 2012, 10:28 AM
Hello,

We agree that this development took too long and we have to think about releasing the support for default values. Unfortunately no ORM can offer you all the features in the same time and there will always be some limitations. We are going to try bringing those limitations down to minimum in 2012 as this is one of our goals for this year.

We strongly believe that Telerik OpenAccess ORM is a mature product that can really help you in the process of creating your Data Access Layer, but of course you are free to choose the most suitable solution for your particular scenario. I hope that you will find plenty of other useful features in OpenAccess that cannot be found in other similar products - such as the Visual Designer, support for a huge list of databases, the Batch Operations dialog, Profiler, automated generation of Stored Procedure methods, etc.

We will keep the PITS item for the default values updated so those of you that need the default values feature will be notified as soon as we plan its release. 

Greetings,
Ivailo
the Telerik team
Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
0
Ivailo
Telerik team
answered on 27 Apr 2012, 04:18 PM
Hi,

We are happy to announce that support for Default Values is now implemented in OpenAccess. Take a look at the relevant blog post, download the 2012.1.427.1 internal build to try the new functionality before the official Q2 2012 release and do not hesitate to share your comments.

 

Regards,
Ivailo
the Telerik team
Follow @OpenAccessORM Twitter channel to get first the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
sitefinitysteve
Top achievements
Rank 2
Iron
Veteran
answered on 27 Apr 2012, 06:52 PM
Nice!  Finally!

Please make sure the Q2 makes it out with Sitefinity 5.1 :)  I want to use this, but am held to their release cycle.
0
Dru Snyder
Top achievements
Rank 1
answered on 19 Jun 2012, 03:37 PM
I have the latest version, but I'm still getting an error when I don't specify a value.  It is defaulted in the database.  I even deleted my rlinq file and recreated it using the new version.

Am I missing a setting somewhere?

An unexpected error occurred: Insert of '581475937-' failed: Telerik.OpenAccess.RT.sql.SQLException: Cannot insert the value NULL into column '', table ''; column does not allow nulls.
0
PetarP
Telerik team
answered on 22 Jun 2012, 12:43 PM
Hello Dru,

 Is it possible that your property is not nullable? In order to take advantage of the default values your properties that correspond to the default value column should be marked as nullable. 

All the best,
Petar
the Telerik team
OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
0
Dru Snyder
Top achievements
Rank 1
answered on 22 Jun 2012, 12:48 PM
That would do it.  I couldn't find any documentation on this new feature yet.  Can you please explain why this is necessary?  I can't think of a reason, so I'm curious.

Thanks,
Dru
0
sitefinitysteve
Top achievements
Rank 2
Iron
Veteran
answered on 22 Jun 2012, 12:52 PM
BAM <- that just happened
0
KevinFarrow
Top achievements
Rank 1
answered on 25 Jun 2012, 11:47 AM
Could you please explain why the columns have to be nullable in order to use default values?

In order for Open Access to be a viable solution we need to use it against existing databases and not have to make any changes.

For example, we have a column called Gender in our Customer table that we don't ever want to be null. We also set '3' as the default value (possible values are: 1=Male, 2-Female, 3=Unknown). This business rule means that no one should ever be able to add a new Customer record without setting this column, either from an application OR by importing records OR by directly inserting using SQL.

Consider the following code that creates a new record into a table that has 4 columns, CustomerId (auto generated identity), FirstName, LastName, Gender:

Customer customer = new Customer();
customer.FirstName = 'Kevin';
customer.LastName = 'Farrow';
customer.Save();

then I would expect your code to generate the following SQL (or something similar)

INSERT INTO Customer(FirstName, LastName) SELECT 'Kevin', 'Farrow'

From the error message 'Cannot insert the value NULL into column' it appears you are trying to insert a value into the Gender field even though the above code hasn't referenced that field. If the Customer table has 50 columns the that means you update ALL 50 columns on each insert or update which would be very inefficient.
0
PetarP
Telerik team
answered on 27 Jun 2012, 11:50 AM
Hello Kevin,

 Consider the following situation:
You have an integer field in your database that has a default value of 10. You are creating a new instance of the type holding the integer field and you are not setting this field (the field is initialized to 0 from the constructor). Later on you are inserting that instance in the database. What you would expect is that the value of 10 is inserted in the column corresponding to the integer field. Now consider the same scenario but you explicitly set the value of 0 to the integer field and then insert it. Normally you would expect that the default value would be omitted and a 0 will be inserted. 
Here is where the actual problem begins. Currently OpenAccess does not have means to tell the difference between a zero that has been set by the constructor and a 0 that has been explicitly set. That is why we only support default values for fields that are nullable. This way you will still be able to set 0 to an integer field and the null would mean that the field was not initialized.

Kind regards,
Petar
the Telerik team
OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
Tags
General Discussions
Asked by
John
Top achievements
Rank 1
Answers by
Jan Blessenohl
Telerik team
chris_cf
Top achievements
Rank 2
Robert
Top achievements
Rank 2
John
Top achievements
Rank 1
Geoff Davis
Top achievements
Rank 2
Daniel Plomp
Top achievements
Rank 2
PetarP
Telerik team
KevinFarrow
Top achievements
Rank 1
Patrice Boissonneault
Top achievements
Rank 2
Ivailo
Telerik team
sitefinitysteve
Top achievements
Rank 2
Iron
Veteran
Keith Heilveil
Top achievements
Rank 1
Dru Snyder
Top achievements
Rank 1
Share this question
or