GUID for existing SQL Server DB

  

Hi to everybody,

I'm trying to deal with existing SQLServer database and GUIDs within which are used as primary keys- the integration was a charm until I tried the CREATE operation. This is my setup:

  • primary keys of type 'uniqueidentifier' in SQLServer database are set to be 'text' in my extension
  • foreign keys of type 'uniqueidentifier' in SQLServer database are set to be "entity Identifier" in my extension
  • in web application, I scaffolded the list and details screens for the entity (SILK UI)
  • in the Save action I added an Assign action which sets the record's Id to "TextToIdentifier(GenerateGuid())" 

After CreateOrUpdateEntity action (within Save action) the exception happens with the message "Conversion failed when converting from a character string to uniqueidentifier."

Debugger shows that the GUID is nicely written into record's Id attribute (i.e. EntityForm.Record.Entity.Id).

Is my approach too simplistic to work..? 

Thanks!



Hi Tomaz

I couldn't replicate your problem in OutSystems 10 and SQL Server 2014. Which version of the platform and which version of SQL Server are you using?

Solution

Hi Joao,

I just checked everything twice and now it works nicely. 

I missed one mandatory foreign key responsible for relationship to one of the tables in DB which weren't linked to my project (intentionally), so the resulting details screen and form didn't have the necessary combo box, and nothing provided for the mandatory value of this key. 

The error message was a bit misleading, though    As I understand, the empty string (i.e. Null) got translated into uniqueidentifier which is mandatory and probably the SQLServer made a complaint, but I didn't check the logs on it. Setting "Default value behavior" in Integration Studio didn't influence the outcome.

So, the "TextToIdentifier(GenerateGuid())" just before CreateOrUpdateEntity works just fine. Many thanks for your help!

Solution

Hi Tomaz

Glad you found the solution. 

Just so I understand, setting the "Default Value behavior" to "Convert to/from NULL value in Database" is not working? It's trying to insert the empty string instead of the NULL value?

Hi Joao,

No, I just said that it didn't have any influence on the error - in one of the posts about GUIDs it was mentioned that solving the problems with GUID involves Advanced Queries and setting the "Default Value behavior" (here).