Setting nullable string/varchar field value to NULL

Posts   
 
    
magic
User
Posts: 125
Joined: 24-Nov-2008
# Posted on: 05-Dec-2008 20:55:45   

As posted here (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9328) in an old thread, I have problems in setting the value of a nullable string/varchar field to NULL:

magic wrote:

I have a NULL value in the db for a field. When I read it, it is set to "" in the LGP entity/object. Now I can turn off the setting as Walaa suggested, but I have question about this just to be sure:

This flag will change the behavior of all new entities, or only of entities created from an existing row in the database?

I would like to have the functionality, that if I create a new entity and do not set a value of the a given column AND the column IS NOT allowed to be null, LGP will set the standard value for me. I assume it would do it immediately, cause the db would do it anyway upon inserting the entity into the db, right?

But if I create a new LGP entity by reading it from the db, a value that is NULL should not be changed to an empty string (or any other standard value). Because then obviously the field/column is a NULL column and I want to keep the NULL value.

I have been now trying to work around the problem like this:


if (string.IsNullOrEmpty(player.StandardPosition)) {
    player.StandardPosition = null;
}

But even after this, the value of player.StandardPosition is still "".

What makes the problem even worse, is the fact that player.StandardPosition has a foreign key to allowed player positions, where an id = "" does not exist.

Funny enough, I have another entity that has the same type of field (nullable string, FK to player positions) where I have managed to set the value to NULL. I just don't how / what I have done different there.

I would be really thankful for a hint here, cause I really don't have a clue how to move on with this without screwing some other functionality.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 06-Dec-2008 03:29:29   

Indeed the behavior works like you expect. Please post more info about the problem you faced, and the differences between the entity that is ok and the one that isn't.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Dec-2008 11:53:03   

(The thread you initially posted in is not about your problem, as that thread was that setting it to null in a non-new entity made it changed, which was wrong)

In general, you could consider a default value on the column in the database (e.g. ''), which will be inserted if NULL is inserted (or no value) into the column.

The value of "" is the value you read back from the property. That's not always the value the field has. For example, if the field has the value NULL, and the property of the field is a value type, it can't be NULL/null/nothing, it has to have a value. For strings, this is the same thing. So even if you set it to null, the value returned is not null, but a default value. entity.Fields[index].CurrentValue is the real value of the field. So even if the field's property returns 0, or "", it can be the field is actually NULL, but it might be the type of the property is not suitable for handling nulls.

THe flag suggested is for the value returned from the property IF the value is NULL and the type of the field is a value type or string. So setting it to false, and then regenerating the code will give you null for a nulled string typed field instead of ""

but... what I'm confused about is what you want in the case of a non-nullable field. The thing is that if a field is not nullable, it has to have a value. If you don't supply that value, it has to come from somewhere. So either use a default value on that column, or simply set it to a value.

Frans Bouma | Lead developer LLBLGen Pro
magic
User
Posts: 125
Joined: 24-Nov-2008
# Posted on: 07-Dec-2008 01:14:46   

daelmo wrote:

Indeed the behavior works like you expect.

but I'm not sure what I should expect wink So will it change the behavior of all new entities, or only of entities created from an existing row in the database?

daelmo wrote:

Please post more info about the problem you faced, and the differences between the entity that is ok and the one that isn't.

Unfortunately I can't get to the sources right now. I can get some more details when I'm at work on Monday.

Otis wrote:

In general, you could consider a default value on the column in the database (e.g. ''), which will be inserted if NULL is inserted (or no value) into the column.

The value of "" is the value you read back from the property. That's not always the value the field has. For example, if the field has the value NULL, and the property of the field is a value type, it can't be NULL/null/nothing, it has to have a value. For strings, this is the same thing. So even if you set it to null, the value returned is not null, but a default value. entity.Fields[index].CurrentValue is the real value of the field. So even if the field's property returns 0, or "", it can be the field is actually NULL, but it might be the type of the property is not suitable for handling nulls.

but the field we are talking about (player.StandarPosition) is varchar(2) NULL, and not varchar(2) NOT NULL.

Otis wrote:

THe flag suggested is for the value returned from the property IF the value is NULL and the type of the field is a value type or string. So setting it to false, and then regenerating the code will give you null for a nulled string typed field instead of ""

I'm still not sure whether I understand (as posted above in the question to daelmo).

Otis wrote:

but... what I'm confused about is what you want in the case of a non-nullable field. The thing is that if a field is not nullable, it has to have a value. If you don't supply that value, it has to come from somewhere. So either use a default value on that column, or simply set it to a value.

ok, I will try to re-phrase what I am meaning.

1) World without LGP, just SQL: -) I have a field stringField VARCHAR(2) NOT NULL (default value "foobar"). -) INSERT INTO example (stringField) VALUES (NULL) -) SELECT stringField FROM example -> "foobar"

2) World with LGP (same stringField as above)

ExampleEntity example = new ExampleEntity();
adapter.SaveEntity(example);
printf(example.stringField); // prints "foobar"

I would like to be able to still do this. Will setting the flag as Walaa suggested in the other thread turn this functionality off? I guess not because this is coming from the db, right?

3) Same as above, but without the saving, so:

ExampleEntity example = new ExampleEntity();
printf(example.stringField); // prints "foobar"

At this stage, did LGP already set the standard value? In both cases? Independent of the flag?

Now, the question that I have is that in my current problem I have a standardPosition VARCHAR(2) NULL (no default value, FK to table playerPositions). So what I don't understand (flag turned on or off, doesn't matter), is why LGP would set the default value (that is not explicitly given for this field) to ""? Especially that the value is allowed to be NULL, and there is a foreign key on the field where the referencing table has no PK with value "".

I also don't understand the reasoning behind the idea to set a value if it was not specified for a given field. Independent of whether it is set to "", or something else. Independent of whether the field is NULL or NOT NULL. From my point of view, the value should be left at NULL, unless there is a default value set in the db design AND the field is a NOT NULL field.

If there is no default value, the field is a NOT NULL field, and no value is given, saving the entity should result in an exception. Similar to what happens if you do a direct db INSERT of a NULL value into such a field with a db query.

I hope my intentions are phrased more precise now and I'm looking forward to a solution with LGP that will achieve my goals.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 08-Dec-2008 10:30:07   

I have a standardPosition VARCHAR(2) NULL

If you don't set that field to any vlaue, LLBLGen Pro won't set it either.

therefore I don't know where the default value came from. Please check the database or the code the field must have been set somewhere.

Are you using any validators?

magic
User
Posts: 125
Joined: 24-Nov-2008
# Posted on: 08-Dec-2008 18:17:35   

Can somebody please respond to my questions from the earlier post, so that I know whether setting the flag will have the effects that I want/expect, or whether it will just cost me time because one thing will be solved, but another screwed? Thank you.

Walaa wrote:

If you don't set that field to any vlaue, LLBLGen Pro won't set it either.

but that's exactly what happens and what I don't understand. I'm not changing anything, I'm just reading it from the db.

Walaa wrote:

therefore I don't know where the default value came from. Please check the database or the code the field must have been set somewhere.

I don't know either. I have a nullable field that has value = NULL. I fetch it with LGP from the database and it becomes "".

So if that wouldn't be already bad enough, the second problem comes in: If I try to overwrite it with NULL (as stated above), it ignores this request and still stores "".

Walaa wrote:

Are you using any validators?

no

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Dec-2008 18:56:14   

(first: if you say 'it stores ""', does it do that (did you check the table) or do you assume it did that based on the value returned from the property ?

magic wrote:

Otis wrote:

In general, you could consider a default value on the column in the database (e.g. ''), which will be inserted if NULL is inserted (or no value) into the column.

The value of "" is the value you read back from the property. That's not always the value the field has. For example, if the field has the value NULL, and the property of the field is a value type, it can't be NULL/null/nothing, it has to have a value. For strings, this is the same thing. So even if you set it to null, the value returned is not null, but a default value. entity.Fields[index].CurrentValue is the real value of the field. So even if the field's property returns 0, or "", it can be the field is actually NULL, but it might be the type of the property is not suitable for handling nulls.

but the field we are talking about (player.StandarPosition) is varchar(2) NULL, and not varchar(2) NOT NULL.

I am talking about the entity object. When a field's value is NULL, the field in the entity object isn't set to a value. This means that if you read the PROPERTY, it will return either "" or null/Nothing depending on the flag value (which affects code generation, so after setting the flag in the project properties (which inherit from the preferences when the project is created). Be Sure you set the flag in the project properties as well, as you already created a project.

Otis wrote:

THe flag suggested is for the value returned from the property IF the value is NULL and the type of the field is a value type or string. So setting it to false, and then regenerating the code will give you null for a nulled string typed field instead of ""

I'm still not sure whether I understand (as posted above in the question to daelmo).

It's easy: Table Test's fields (ID, Foo, Bar). ID is not nullable, rest is. A row is inserted: 1, NULL, NULL. An entity is fetched from that table into a TestEntity instance, t, namely the row with ID 1. When I now do string s = t.Foo; I read the property 'Foo'. As Foo is NULL, "" is returned by default so s here will be set to "". The field is still null (t.Fields["Foo"].CurrentValue is null).

After you've set the flag ConvertNulledReferenceTypesToDefaultValue in the Project properties to true AND you regenerated the code, the code: string s = t.Foo; will set 's' to null instead.

Otis wrote:

but... what I'm confused about is what you want in the case of a non-nullable field. The thing is that if a field is not nullable, it has to have a value. If you don't supply that value, it has to come from somewhere. So either use a default value on that column, or simply set it to a value.

ok, I will try to re-phrase what I am meaning.

1) World without LGP, just SQL: -) I have a field stringField VARCHAR(2) NOT NULL (default value "foobar"). -) INSERT INTO example (stringField) VALUES (NULL) -) SELECT stringField FROM example -> "foobar"

2) World with LGP (same stringField as above)

ExampleEntity example = new ExampleEntity();
adapter.SaveEntity(example);
printf(example.stringField); // prints "foobar"

I would like to be able to still do this. Will setting the flag as Walaa suggested in the other thread turn this functionality off? I guess not because this is coming from the db, right?

As NULL is inserted into the field in the TABLE, the field will be set to "foobar" by the database engine, so fetching the row afterwards (you've to specify to refetch the entity by specifying true in the SaveEntity call), will make the field become set to "foobar" and your code will print "foobar".

3) Same as above, but without the saving, so:

ExampleEntity example = new ExampleEntity();
printf(example.stringField); // prints "foobar"

At this stage, did LGP already set the standard value? In both cases? Independent of the flag?

Your 3rd example is by definition 'illegal', as you confused entity instance with entity class instance. The class instance doesn't contain an entity, so it will return an undefined value (null). The default value is never set, as I described above. The property returns a default value IF the field is null. You can tweak this by setting the described flag in the project properties and regenerating the code.

Now, the question that I have is that in my current problem I have a standardPosition VARCHAR(2) NULL (no default value, FK to table playerPositions). So what I don't understand (flag turned on or off, doesn't matter), is why LGP would set the default value (that is not explicitly given for this field) to ""? Especially that the value is allowed to be NULL, and there is a foreign key on the field where the referencing table has no PK with value "".

It doesn't set the default value to "". The property returns that value as often code requires that value types and strings have a value other than null. I hope this describes better what you were asking.

Again, make sure you set the flag in project properties and you've re-generated the code.

Frans Bouma | Lead developer LLBLGen Pro
magic
User
Posts: 125
Joined: 24-Nov-2008
# Posted on: 08-Dec-2008 20:15:55   

Otis wrote:

(first: if you say 'it stores ""', does it do that (did you check the table) or do you assume it did that based on the value returned from the property ?

It really stored the "" in the database. I was reading from one entity and writing into another (I know, it doesn't sound like it would make sense, but it does. It's a bit too complicated to explain it here). So the first entity had a NULL value on that column, but when I read it, it was converted to "" and then stored as "" instead of NULL in the other entity. I only figured out because the FK was failing on the insert.

Otis wrote:

I am talking about the entity object. When a field's value is NULL, the field in the entity object isn't set to a value. This means that if you read the PROPERTY, it will return either "" or null/Nothing depending on the flag value (which affects code generation, so after setting the flag in the project properties (which inherit from the preferences when the project is created). Be Sure you set the flag in the project properties as well, as you already created a project.

I changed the flag and it works now! simple_smile Thank you. I still wanted to ask if you could give me an example where this functionality comes in handy? Is this for example meant to set a default value for forms? I would understand the concept, if the database field had a default value, but since it does not have ... ? And it's even more confusing for me that I have set the value explicitly to NULL in the .NET application, and it still refused to acknowledge it.

Otis wrote:

Your 3rd example is by definition 'illegal', as you confused entity instance with entity class instance. The class instance doesn't contain an entity, so it will return an undefined value (null). The default value is never set, as I described above. The property returns a default value IF the field is null. You can tweak this by setting the described flag in the project properties and regenerating the code.

now I have the full picture (I hope) wink

Otis wrote:

It doesn't set the default value to "". The property returns that value as often code requires that value types and strings have a value other than null. I hope this describes better what you were asking.

From my point of view, if this shouldn't be allowed, the developer should take care about this. I can imagine, you guys are experts with this and know why you developed it this way, but I can't quite picture a scenario, where the flag should come in handy as a "global (standard) setting". I'm looking forward to learn from an example.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Dec-2008 21:20:27   

magic wrote:

Otis wrote:

(first: if you say 'it stores ""', does it do that (did you check the table) or do you assume it did that based on the value returned from the property ?

It really stored the "" in the database. I was reading from one entity and writing into another (I know, it doesn't sound like it would make sense, but it does. It's a bit too complicated to explain it here). So the first entity had a NULL value on that column, but when I read it, it was converted to "" and then stored as "" instead of NULL in the other entity. I only figured out because the FK was failing on the insert.

It is in theory impossible that that happens (there's no code in the library which sets fields to default values), so I'm very interested in the scenario when this happens. Could you elaborate a bit about the scenario, e.g. specify a unittest/entity when this happens?

Otis wrote:

I am talking about the entity object. When a field's value is NULL, the field in the entity object isn't set to a value. This means that if you read the PROPERTY, it will return either "" or null/Nothing depending on the flag value (which affects code generation, so after setting the flag in the project properties (which inherit from the preferences when the project is created). Be Sure you set the flag in the project properties as well, as you already created a project.

I changed the flag and it works now! simple_smile Thank you. I still wanted to ask if you could give me an example where this functionality comes in handy? Is this for example meant to set a default value for forms? I would understand the concept, if the database field had a default value, but since it does not have ... ? And it's even more confusing for me that I have set the value explicitly to NULL in the .NET application, and it still refused to acknowledge it.

It's basicly backwards compatibility simple_smile We released in 2003 our first v1 version of LLBLGen Pro when there was just .net 1.1 and 1.0, so no nullable types. Also in general it avoids dreaded null checks on string values if it's never null, but always "". In v1 the fields did get set to the default value at initialization, however in v2.0 we changed that (to leave them at null).

Otis wrote:

It doesn't set the default value to "". The property returns that value as often code requires that value types and strings have a value other than null. I hope this describes better what you were asking.

From my point of view, if this shouldn't be allowed, the developer should take care about this. I can imagine, you guys are experts with this and know why you developed it this way, but I can't quite picture a scenario, where the flag should come in handy as a "global (standard) setting". I'm looking forward to learn from an example.

Well, it was enabled by default in the first versions several years ago, so we couldn't just remove it. Also, string values in normal .NET code are often better of being initialized with string.Empty instead of null, as you then can just check the length for the string and never have to check for null AND the length of the string, it's just less code).

Type default values is a template. You can alter it and define your own if you want to simple_smile

Frans Bouma | Lead developer LLBLGen Pro
magic
User
Posts: 125
Joined: 24-Nov-2008
# Posted on: 08-Dec-2008 22:36:42   

Otis wrote:

It is in theory impossible that that happens (there's no code in the library which sets fields to default values), so I'm very interested in the scenario when this happens. Could you elaborate a bit about the scenario, e.g. specify a unittest/entity when this happens?

I don't have too much time for this since I'm behind with the development, but ... as stated I have two entities with almost the same setup, the discussed flag is on true. When requested, I copy the data from one entity to the other, for example:


player.StandardPosition = livePlayer.StandardPosition;

Since the livePlayer had the standardPosition with value "" (previously read from the db), LGP told the db to store "" in the database. The db refused because there is no position with PK "". Other entities/fields that didn't have a FK where stored with "" (instead of NULL).

Does this make the problem reproducible for you?

Otis wrote:

It's basicly backwards compatibility simple_smile We released in 2003 our first v1 version of LLBLGen Pro when there was just .net 1.1 and 1.0, so no nullable types. Also in general it avoids dreaded null checks on string values if it's never null, but always "". In v1 the fields did get set to the default value at initialization, however in v2.0 we changed that (to leave them at null).

ok, understood. But wouldn't it still be better to have to have the flag on false as standard?

Otis wrote:

Well, it was enabled by default in the first versions several years ago, so we couldn't just remove it. Also, string values in normal .NET code are often better of being initialized with string.Empty instead of null, as you then can just check the length for the string and never have to check for null AND the length of the string, it's just less code).

erm ... string.isNullOrEmpty(value) and the problem is solved ... ? wink

Otis wrote:

Type default values is a template. You can alter it and define your own if you want to simple_smile

no, thank you. I'm happy for now. wink Have to make some progress in development ...

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-Dec-2008 09:43:02   

I copy the data from one entity to the other, for example: Code: player.StandardPosition = livePlayer.StandardPosition;

Since the livePlayer had the standardPosition with value "" (previously read from the db), LGP told the db to store "" in the database. The db refused because there is no position with PK "". Other entities/fields that didn't have a FK where stored with "" (instead of NULL).

Does this make the problem reproducible for you?

No. Please post a simple repro solution, based on Northwind or any simple database for your own. Otherwise we will keep going in circles.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Dec-2008 10:48:33   

Walaa wrote:

I copy the data from one entity to the other, for example: Code: player.StandardPosition = livePlayer.StandardPosition;

Since the livePlayer had the standardPosition with value "" (previously read from the db), LGP told the db to store "" in the database. The db refused because there is no position with PK "". Other entities/fields that didn't have a FK where stored with "" (instead of NULL).

Does this make the problem reproducible for you?

No. Please post a simple repro solution, based on Northwind or any simple database for your own. Otherwise we will keep going in circles.

I think understand what's going on -> livePlayer.StandardPosition, if it's null, returns "" as the flag was set to true.

This means that player.StandardPosition is set to "" because livePlayer.StandardPosition is returning "" instead of null. simple_smile

So that indeed causes a "" being inserted in the row for player.

magic wrote:

Otis wrote:

It's basicly backwards compatibility simple_smile We released in 2003 our first v1 version of LLBLGen Pro when there was just .net 1.1 and 1.0, so no nullable types. Also in general it avoids dreaded null checks on string values if it's never null, but always "". In v1 the fields did get set to the default value at initialization, however in v2.0 we changed that (to leave them at null).

ok, understood. But wouldn't it still be better to have to have the flag on false as standard?

Perhaps, however it's also convenience for people who don't like string values to be null. I for example never work with string typed variables which are null, they're either string.Empty or have another value.

Otis wrote:

Well, it was enabled by default in the first versions several years ago, so we couldn't just remove it. Also, string values in normal .NET code are often better of being initialized with string.Empty instead of null, as you then can just check the length for the string and never have to check for null AND the length of the string, it's just less code).

erm ... string.isNullOrEmpty(value) and the problem is solved ... ? wink

true, but that method was introduced in .NET 2.0 wink . And even then, it contained a bug where in an edge case it could crash. (was fixed in sp1 for .net 2.0 if I'm not mistaken)

Otis wrote:

Type default values is a template. You can alter it and define your own if you want to simple_smile

no, thank you. I'm happy for now. wink Have to make some progress in development ...

Good luck simple_smile

Frans Bouma | Lead developer LLBLGen Pro
magic
User
Posts: 125
Joined: 24-Nov-2008
# Posted on: 09-Dec-2008 15:04:29   

Otis wrote:

I think understand what's going on -> livePlayer.StandardPosition, if it's null, returns "" as the flag was set to true.

This means that player.StandardPosition is set to "" because livePlayer.StandardPosition is returning "" instead of null. simple_smile

So that indeed causes a "" being inserted in the row for player.

exactly! simple_smile Does this mean you guys now know everything and I don't have to play around with an example in terms of Northwind?

Otis wrote:

Perhaps, however it's also convenience for people who don't like string values to be null. I for example never work with string typed variables which are null, they're either string.Empty or have another value.

well ... it's a matter of definition. Since the SQL Server does not treat "" as NULL, I think LGP shouldn't convert one into the other. If SQL Server was set to insert NULL into the field when a "" comes around, then it would be ok for me.

I think you can set this in the DB prefs somewhere (it's set in Oracle as far as I can remember), but the standard settings of LGP and SQL Server should go hand in hand I think.

Otis wrote:

true, but that method was introduced in .NET 2.0 wink . And even then, it contained a bug where in an edge case it could crash. (was fixed in sp1 for .net 2.0 if I'm not mistaken)

something that Microsoft developed crashes?! smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Dec-2008 16:21:35   

magic wrote:

Otis wrote:

I think understand what's going on -> livePlayer.StandardPosition, if it's null, returns "" as the flag was set to true.

This means that player.StandardPosition is set to "" because livePlayer.StandardPosition is returning "" instead of null. simple_smile

So that indeed causes a "" being inserted in the row for player.

exactly! simple_smile Does this mean you guys now know everything and I don't have to play around with an example in terms of Northwind?

Indeed, we don't need a repro, it's clear why this happened. simple_smile (and not a bug as well, just an inconvenient circumstance, as the code worked as intended)

Otis wrote:

Perhaps, however it's also convenience for people who don't like string values to be null. I for example never work with string typed variables which are null, they're either string.Empty or have another value.

well ... it's a matter of definition. Since the SQL Server does not treat "" as NULL, I think LGP shouldn't convert one into the other. If SQL Server was set to insert NULL into the field when a "" comes around, then it would be ok for me.

Oracle does threat "" as NULL btw for varchar types.

It's about the difference between working on the RDBMS level vs. working on the .NET code level. These don't necessary have to be 1:1 projections of eachother.

I think you can set this in the DB prefs somewhere (it's set in Oracle as far as I can remember), but the standard settings of LGP and SQL Server should go hand in hand I think.

It's a choice we made a long time ago, and as such has helped a lot of people out (e.g. in databinding, it's often easier to have "" instead of null). Admitted, writing a copy routine for entities like you did could give side-effects which were unexpected.

Frans Bouma | Lead developer LLBLGen Pro