ORM Handling of SQL Default Values and Nulls

37 posts, 0 answers
  1. Steve
    Steve avatar
    1851 posts
    Member since:
    Dec 2008

    Posted 27 Apr 2012 Link to this post

    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.
  2. Dru Snyder
    Dru Snyder avatar
    4 posts
    Member since:
    Jan 2010

    Posted 19 Jun 2012 Link to this post

    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.
  3. DevCraft banner
  4. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 22 Jun 2012 Link to this post

    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.
  5. Dru Snyder
    Dru Snyder avatar
    4 posts
    Member since:
    Jan 2010

    Posted 22 Jun 2012 Link to this post

    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
  6. Steve
    Steve avatar
    1851 posts
    Member since:
    Dec 2008

    Posted 22 Jun 2012 Link to this post

    BAM <- that just happened
  7. KevinFarrow
    KevinFarrow avatar
    7 posts
    Member since:
    Apr 2007

    Posted 25 Jun 2012 Link to this post

    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.
  8. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 27 Jun 2012 Link to this post

    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.
Back to Top
DevCraft banner