I want to transfer tables from an existing MySQL Database to Outsystems.

I tried to export to Excel and Bootstrap the Entities from Excel, but the existing tables already have an Id Column, and I do not want to use the Id column created by Outsystems by default.

Is there a way to overcome this or any other way to import the tables without any issues?


Regards,


Marios

Hello Mario,

In OutSystems, when creating the entities (and before publishing the first time), change the property AutoNumber o the ID attribute to False.

Now you have to provide an ID for each field you create.

I would avoid this, thou... As you will become dependent on creating your own IDs.

What's the problem with the OutSystems IDs?
You can have lookup tables (just for the import of data) and fix any FK in a table to point to the new ID, for example.

Cheers.

Hi Marios Andrea,

Just a suggestion try to upload the excel which you have , then change that identifier attribute to the attribute what you need as a identifier , then delete the outsystems Default Id.


Manikandan K wrote:

Hi Marios Andrea,

Just a suggestion try to upload the excel which you have , then change that identifier attribute to the attribute what you need as a identifier , then delete the outsystems Default Id.


As Far as I know, this will not work, Manikandan.
When you delete an attribute, the field is not deleted from the physical table.

As far as I know, the old ID will keep it's constraint... 

At least in the past, I wasn't able to delete the ID of an entity... Didn't tested this on newer versions thou...

Did you tested it?


Manikandan K wrote:

This solution works, but I can't set the existing ID column from Integer to Autonumber.

This is the error I get when I try to publish it:

Database Upgrade Error
Column 'OSUSR_QHA_MVSTUDIO.ID' exists in database as Integer (int) but the new version is defined as Integer (Autonumber). Database int columns can not be changed to Autonumber.


The reason I want to have the Id column created by the platform deleted is that I already have an ID column in the existing database, which is the identifier and auto-increment as well.


Is there any other way to import tables from existing databases into Outsystems?


Thanks!


Marios

Solution

Hello Marios,

My experience is: Once you publish an entity, you can't change the Primary Key anymore, in any way.

Not even deleting it.

At least through normal ways using Service Studio or Service Center. There is a paid tool to migrating data. You can find it on Forge. it's from InfoSystema if my memory is ok. Never used it.

Other than that, I tend to create an import app to import creating new IDs, with help of lookup tables to make the connection between old and new ids to fix FKs.

Cheers

Solution

Thanks Eduardo,


I think I will create the tables manually and then Create An Action to Bootstrap Data from Excel.

I just wanted to find a way to avoid all that :)


Regards,


Marios

We are running into this issue now, needing to import existing IDs from a legacy system, and OutSystems has told us it is NOT possible to import existing ID values into the ID of records in OutSystems.

However, it could be possible if OutSystems would allow us to set IDENTITY_INSERT on:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017

Depending on your environment, you may or may not have the permissions to do this before you import your data. See this thread for more info:

https://www.outsystems.com/forums/discussion/18594/etl-identity-insert/




I have imported IDs as integer data type and then 'setting it as an identifier' (right click on the entity attribute) after the excel bootstrap, should have a similar result to the auto-number suggested by Eduardo... so not sure if this will solve your issue but give it a go.


Check this post:
https://www.outsystems.com/forums/discussion/33907/bootstrapping-with-identifiers/

Hi Mario,

1. Set Auto Number to False

2. Import your excel as is

3. Set Auto Number to True

4. Find Sequence table for that Entity in Outsytems schema

5. Set the sequence to the last ID


regards

RI wrote:

Hi Mario,

1. Set Auto Number to False

2. Import your excel as is

3. Set Auto Number to True

4. Find Sequence table for that Entity in Outsytems schema

5. Set the sequence to the last ID


regards

Hey RI,


You mean creating the entities and their attributes manually and then try to do what you are suggesting?


Marios


Hello all,

I tried to change the identifier and delete Outsystems' default Id attribute before publishing and it has worked. 

I'll try it with more Entities before I call this solved. I'll get back to you.

Thanks!


Marios


Hello everyone,

Issue solved.


Solution:

  1. Bootstrap Entity from Excel
  2. Delete Id Attribute automatically created by Outsystems
  3. Set Old ID as Identifier
  4. (Optional) Set "Is Autonumber" To "No"
  5. Publish 


Thanks, Everyone for your help!


Marios