GUID Data Type not available for SQL Server in Integration Studio

GUID Data Type not available for SQL Server in Integration Studio

  
I can't find the correct way to map my SQL Server GUID data type fields to the extension for my external database. The program suggests using text but that fails when the application attempts to update the table due to a type conversion error. My system uses GUID's extensively for primary keys so this is a must have critical problem. 

Hope someone has a solution or direction to point me in.

Best,

Dave
Hi David, and welcome to the OutSystems community forums.

From what you are describing, and since I have never worked with GUIDs in that scenario, it sounds like a bug, because it should work as expected.

Since GUIDs are, by definition, 16-byte binary numbers that are guaranteed to be unique, I am wondering if you can try  to import it as Binary Data instead of text... If integration Studio doesn't let you do so, because of it being the Id, try not setting it as an Id but as an index and see if it works.

I'm pretty sure this approach is not the ideal one, and will probably have performance problems, but for the sake of testing and until someone actually posts the right solution, it might do the trick as a workaround.

Let me know if that works for now.

Regards,

Paulo Tavares
Paulo,

Thank you for your response. I don't think that will be a good solution for me, more of a work around. I have around 500 tables with many relationships and all my keys are GUID's so not identifying them as primary keys reduces quite a bit of the built in functionality of the platform and would require me to expicitly define all those relationships. I would also loose the referential integrity checks. 

Since the integration studio asks which database I'm using, I would assume it to be aware of the native data types of the underlying database so why not support the GUID data type directly?

Thanks,

Dave
Hi David,


By design, the Agile Platform entitiy identifiers have the Integer or Text datatype. This is so to push a normalization and simplification of the datamodels.

In what regards the referential integrity of your database, it is not at stake. The referential integrity is maintained and enforced by your external database management system, not by the Agile Platform.

I'm sorry we cannot provide a better answer at the moment,


Rodrigo Castelo
Hi David,

Last time I tried to map GUID database type into Text type it worked perfectly. I performed several tests (inserts, updates, deletes) and I got no errors, as long GUID fields were correct GUIDs. I also tested some inserts and updates using invalid GUIDs and in these cases I got the convertion error from the SQL Server.

Could you provide details on the error and if possible a sample (eSpace and extension) that replicates the problem? I'll try to replicate the scenario in my side.

BTW, which version of the Agile Platfrom are you using?


Regards,
João Portela
Hi David,

I've just tested a insert, update and "invalid" update scenario, and I got the expected result: only the "invalid" update fails.

I've attached my test files. The zip contains a SQL script with table creation, the extension where table is defined and a sample oml.


Perhaps your scenario is a little different from my simple insert/update scenarios. So, I’ll probably need a sample from your side to check where’s the problem.


Regards,
João Portela
Joao,

Thank you for the feedback. In my particular case I'm using the GUID's as primary keys to all of my entities. I have mapped them to TEXT since that is the only way I can identify them as primary keys in Integration Studio. On Insert I have a SQL Function that generates the GUID for the record. 

Is any of this inconsistent with your test environment? 

I'll give it another try after I have had a chance to look at the files that you included in your response.

Thanks again,

David
Hi David,

The problem could be how you’re setting the GUID generator sql function. There are 2 ways you can do it:
  • Do database inserts using advance queries, which are not recommended since you’ll need to create the entire SQL. In the Advance Query sql you could add the GUID generator sql function.
  • Use entity create action but you use this action you’ll need to generate the GUID in the Service Studio action where you fill the entity record. You’ll need an extension to generate the GUIDs (I can provide this extension if you need).
If you provide a sample espace how you’re creating the records I can try to understand the error.

Regards,
João Portela
Joao,

Looks like I have it working now. I needed to set the NULL conversion on the database extension in integration studio to get the updates working and I used an Advanced Query to generate the GUID using (SELECT newid()) and then assigning the GUID to the PK field prior to standard call to create/update the entity. I had the newid() function as the default value for the PK field on the database but that didn't work due to a conversion error so I had to move it to the Service studio call as you suggested.

Thank you for your help,

Dave
Hi David,

Glad I could help.
 
Please let me know if you have any other problems related with GUID data type (I’m aware of some problems, due this type not being supported officially, and I know some workarounds).
 
Regards,
João Portela