How can I insert/modify a record in my external SQL database with NULL values in uniqueidentifier columns?
The scenario is that I read the records from the database, update other fields, but leave the field BCProductInstanceId untouched.
But for those records where that field contained a NULL value in the external SQL database, I get the error 'Conversion failed when converting from a character string to uniqueidentifier'. I can't find a way to 'restore' a NULL value. Of course I have tried several NULL-types, including empty strings, but no luck.
On the other hand, for the FK field CompanionProductId I used a dropdown widget with a list, allowing empty values:
When a record has a NULL value, or when I assign the NULL value by selecting the empty string, all works fine and the result is NULL in the external SQL table, so it seems that OutSystem has a way to work with NULL values.
How can I avoid the error on saving a record that seems to be perfectly valid in the external database (in the sense of accepting NULL values in that column)? FWIW: I noticed that the problematic field had the following defenition in OS which seems a bit odd:
And a final note: when I assign another identifier value (like copying the record's own ID value), all works fine as well.
PS: Providing an OML was difficult since I needed an external database to reproduce the issue.
Hello SilverFox,
Foreign Keys (reference attributes), that are NOT mandatory, will also receive NULL from OutSystems if the value on them is the Default Value (0 for numerical identifiers, empty string for text identifiers).
That ONLY happens for Non mandatory Foreign Keys.
But you have an External Database.So, your entities come from an extension. In the extension, each entity that represents a table in the external database have an option to define what to do in case of "NULL".
The option you should have is Convert To/From NULL.
This will make the platform send NULL to the database when the attribute have the "default" value defined. This is automatically done by the platform and works for all attributes, not only Foreign keys.
So, first, I would check if the extension has the correct option selected for the Conversion to and from NULL, that in your case, should be enough to solve the issue.
By the way, the uniqueidentifier data type is not a Foreign key, it is a GUID. You can check this here.
Cheers!
Thx Eduardo, it seems that converting the NULLs did not work, but this might be due to the fact that I have not used the right default GUID value, I'll try that and post the result here (for future reference if others run into the same problems)I just figured out that when I change the default setting in the Integration Builder to keep the NULLs it works for me.
Regarding the FK and uniqueidentifier data type, I know, but I wanted to point out that the other (FK) field seemed to behave slightly different with respect to NULLs as it was able to send back NULLs to the db.
"it seems that converting the NULLs did not work, but this might be due to the fact that I have not used the right default GUID value"
It will not work, as that attribute is a normal attribute (not a reference), so, whatever is the value there, it will be sent to the database, unless it is an external database and you set the NULL option in the integration studio or integration builder, as you did.
In my other comment I explained why it works for FK's (if they are set to not mandatory) :)
Hi Kilian, that is correct, the external table does not have the FK property on this column. But sometimes changing the design of external tables lies beyond your power, in this case I could give it a try, but I also wanted to understand how and why thing work the way they work ;-)
But regardless being an identifier or not, IMHO it should be possible to keep/assign NULL values. Just found out that it is possible in Integration Builder to change the NULL value handling: And that seems to do the trick :-)Still I find it strange that OS handles the NULL values differently for FK fields with the (default) Convert Database NULLs setting
Hi,
Have you try this way for your null value record-
IntegerToIdentifier(TextToInteger("0"))
it will convert as identifier type so it will not a problem.
Regards
Rahul
Thanks Rahul, I just tried that one also, and I get the same error 'Conversion failed when converting from a character string to uniqueidentifier'.
Just curious - while I'm still learning, why should that yield a better result than for example using NullIdentifier() or NullTextIdentifier() ?
The funny thing though is that OS seems to recognize this column as an identifier since I can put a filter on the column in the aggregate:
NullTextIdentifier() will gracefully cast to Text in case it's compared to a Text variable, so it doesn't recognize it as an identifier. And in fact, judging your image:
it's not an identifier at all, at least not a foreign key (as opposed to CompanionProductId and CompanyId). So I think the problem is already in the definition of the external table.
"Still I find it strange that OS handles the NULL values differently for FK fields with the (default) Convert Database NULLs setting" - the Platform only has the concept of NULL values for identifiers and foreign keys (Nulldentifier() and NullTextIdentifier()). All other values in a native OS database can't be NULL. So in this case, since the external database column isn't an identifier, it can't be NULL (unless the setting you discovered is set).
Sorry, I made an answer and we posted almost at the same time.
Didn't know you was using the Integration Builder (nice).
The reason why Non Mandatory Foreign Keys are treated differently than other attributes is because while OutSystems does not have the NULL concept (OutSystems side), the databases have, and while it can send a "default value", and it sends, for normal attributes, it can't do that for a non mandatory FK.
The reason is that being relational databases, anything other than NULL that the database receives as the value for hte FK, it will try to validade if it exists in the referenced table (trying to keep referential integrity). That is done by the database only for FKs.
So, in the case the FK is NOT mandatory, the platform HAS to send NULL, or the database would raise an Exception.
Why this is that way, different from when you are integrating with External databases?
Because OutSystems does not have the concept of NULL, but other applications using the external database may have, and NULL may be significant in that database.
So, When you are dealing with an OutSystems database, the platform will send NULL only as necessary (FK not mandatory), when you are dealing with external, you have to decide what to do, in order to make your app work with the requirements of the external database
Hi SilverFox
Maybe this article can help you. But basically Outsystems do not have the concept of NULLS
https://www.outsystems.com/forums/discussion/41593/convert-to-from-null-value-in-database/#:~:text=In%20OutSystems%2C%20the%20value%20will,as%20False%2C%20and%20so%20on.
Thx BabyBear, indeed, this post helped me understand how OS handles NULLS.As I posted in a reply, in Integration Builder you can specify the NULL handling behavior.When time permits I'll dive a bit deeper into this as I feel that OS is not handling this consistently in all aspects.
Thanks all for taking the time to reply and helping me to find a solution and better understand the why. Great forum, great people! :-)