2

We're loading data into CRM via SSIS, and our database contains nulls in string columns. We need to determine how to handle these, and if there's no difference between the empty string and null in CRM, we'd prefer to treat nulls as empty strnigs.

Does CRM contain nulls? Is there a practical difference between nulls and empty strings in CRM?

EDIT: Ok I got it backwards - when we store empty strings in CRM, we're getting NULL back as a result - does CRM store empty strings as nulls, or is it just weirdness with out SSIS connector?

Jeff
  • 12,555
  • 5
  • 33
  • 60
  • 1
    Yes, there is a difference between `NULL` and `""`... – nhgrif Sep 28 '15 at 22:51
  • @nhgrif so it's just weirdness with our connector that's causing it either to convert empty strings to null when inserting to CRM or when downloading from CRM? – Jeff Sep 28 '15 at 22:53
  • In MySQL inserting `""` into field with default value `NULL`, field becomes `""` - empty string. – Tpojka Sep 28 '15 at 23:11
  • @Tpojka Yup, I'm aware of how default values work in MySQL - note that this question is about Dynamics CRM Online though; it is not behaving the way I would expect nulls to behave from my experience as a .net, web and SQL developer. – Jeff Sep 29 '15 at 00:07
  • 1
    @Tpojka Inserting `""` into field => field becomes `""`. It does what you tell it. Nothing to do with `NULL` or default values. – user207421 Sep 29 '15 at 01:04
  • 2
    Depending on how you connect to CRM, I believe it will return nulls for empty string values. – Daryl Sep 29 '15 at 22:12
  • when manipulating values using the REST APIs, I found that empty values are always returned as null, whether you put them in as null or empty string. This behaviour doesn't happen in the underlying SQL server (though oddly it does in Oracle RDB - maybe Dynamics uses Oracle behind the scenes!!) – Andy Jan 26 '21 at 18:53

2 Answers2

2

CRM appears to store empty strings as null.

Ran some tests creating records via the SOAP web service, then checking in SQL which seems to confirm it.

enter image description here

James Wood
  • 17,286
  • 4
  • 46
  • 89
  • Hey thanks for not only doing some tests for me (I know very little about CRM online, AFAIK there isn't a way to replicate this test there, and I don't really have the expertise to do it...) but also for actually reading my question. – Jeff Sep 29 '15 at 07:53
  • @Jeff, you are correct, I ran this test on premise. No way to run this exact test online as you cant access the database (easily - I think Microsoft will provide the database on request). – James Wood Sep 29 '15 at 10:17
  • I'm using the web API (AKA Common Data Services) and I can confirm this also has the same behaviour - if you set a text field to an empty string when updating the object, it will be set to null on retrieval – Andy Jul 21 '20 at 12:18
-1

Oh yes! There is absolutely difference between null & empty string. For example: you have a variable named x. If you write in JS, var x = ""; this means that you have assigned a value which is empty string (length is 0). Actually this is like something but which is feel of nothing :) On the other hand, var y = null; this means you've not assigned a value to y that clearly said by writing null to y at the time of declaration. If you write y.length; it will throw an error which indicates that no value assigned to y and as a result can't read length of y.

Rashed Rahat
  • 2,357
  • 2
  • 18
  • 38
  • Hi Rashed, I am well aware of what the difference is between null and the empty string in general, but this was a question about CRM specifically where it appears they do not make the distinction. – Jeff Jan 30 '20 at 05:17