Default new char fields to "" instead of null?

Mar 22, 2013 at 4:19 PM
Is there a way to automatically default empty char fields to an empty string instead of null when adding a new record?

Here's what I'm running into:

A new (non-nullable) char field is added to a table. Now if I attempt to save a new record to that table and I haven't updated my model, the save fails with a "Field 'xxxx' does not accept null values'.
Mar 22, 2013 at 4:35 PM
Now that I'm looking at this more, this seems to happen with other field types as well (ex. Logical). I would expect it to happen with Date or Date/Time fields, but other types have a reasonable default available.
Coordinator
Mar 22, 2013 at 5:05 PM
I'm not following your scenario. When you say a new char field is added to the table... is this after you created your EF model? Are you updating the model to include the new field?
Mar 22, 2013 at 5:54 PM
Scenario:

Desktop + web app are deployed. Model is already in place.
Dev. 1 - Adds a new field to SomeTable for a new feature in their desktop app.
Customer - In web app. does something that needs to add a new record to SomeTable. Fails with "Field 'xxxx' does not accept null values"
Dev. 2 - Gets bug report, gets new version of table, updates model, deploys new version.

The basic issue is that their is always going to be a time delay between when a table change is made and when that can be reflected in the web application. We can attempt to deploy new versions simultaneously, but honestly that's a huge pain. There is also the issue that you ALWAYS have to update it, otherwise it breaks (which makes the web app feel REALLY brittle). It should be safe for another dev. to add a new field without causing stuff to break.

(hopefully that makes sense)
Coordinator
Mar 22, 2013 at 6:13 PM
If the EF model doesn't include the new field then that field will not be included in the insert statement. In that case, your only option to avoid simultaneously updating is to set a default value for the field when adding it to the table.
Mar 25, 2013 at 4:14 PM
It appears to be a weird default of the OLE DB provider. Even if the field isn't referenced in the INSERT it wants to set the unreferenced fields to NULL.

See this link for a reference:

http://stackoverflow.com/questions/1876742/turning-automatic-nulls-off-for-inserts-to-foxpro-tables-through-nhibernate-usin


But obviously I'd prefer to not have to do something like this every time I need to insert/update a record <g>.

It seems like this should be settable via the connection string but I haven't figured out the syntax to make it work. Just adding something like NULL=False or NULL=OFF doesn't work. I attempted to use the extended properties DBPROP_VFPOLEDB_NULL, ex. Extended Properties=DBPROP_VFPOLEDB_NULL=False (and =OFF). It doesn't give me an error, but it definitely doesn't work. I'm guessing I may not have the syntax correct.

Any ideas?
Coordinator
Mar 25, 2013 at 5:22 PM
I couldn't get the NULL setting to work in the connection string either. The only other idea that I have for you is overriding the DbContext SaveChanges method.

Example:
        public override int SaveChanges() {
            try {
                Database.Connection.Open();

                using (var command = Database.Connection.CreateCommand()) {
                    command.CommandText = "SET NULL OFF";
                    command.ExecuteNonQuery();
                }

                return base.SaveChanges();
            }
            finally {
                Database.Connection.Close();
            }
        }
Mar 26, 2013 at 2:06 PM
Yeah, that's what I ended up doing. Thanks.
Coordinator
May 19, 2013 at 9:05 PM
I thought about this some more and decided that I should "fix" the "null=off" connection string setting. The fix will be in VfpClient which is what the VFP EF Provider and LINQ to VFP use. So you will not need to override SaveChanges in the next VFP EF Provider build.