Need to delete rows in OSUSR_2RY_ADDRESSES

I have over 400K of data in OSUSR_2RY_ADDRESSES that I need to delete.  I am using a personal environment on the latest build.  Can someone please point me in the right direction on where to go to delete this data?  I found this by using the forge module db cleaner on steroids.  I am at 65% capacity with my db and I really need to free up space.  I also notice that I have a ton of versions sitting out there that I had deleted in service center but they show up as a total count in this db cleaner.  Any help would be appreciated.

Hi Peter,

You can do below

1.You can directly run Advance sql to delete record from OSUSR_2RY_ADDRESSES table
2.Delete old version of module from service studio
3.Use DB cleaner component to delete you unused data/versions.
(https://www.outsystems.com/forge/component-overview/423/dbcleaner)

Hello Peter.

For most cases, the DB Cleaner does that. If you have unused tables, they can be deleted.


For your situation:

If you need the module, you have to do an advanced SQL with a delete command. Be sure to remove it after so you don't delete data after by accident.

If you don't need the module, delete it and DBCleaner will throw away the related data.


Nuno Reis wrote:

Hello Peter.

For most cases, the DB Cleaner does that. If you have unused tables, they can be deleted.


For your situation:

If you need the module, you have to do an advanced SQL with a delete command. Be sure to remove it after so you don't delete data after by accident.

If you don't need the module, delete it and DBCleaner will throw away the related data.


Thank you for replying Nuno.  I absolutely cannot delete it, I just need to delete all the data in it.  Since this is a new to outsystems forum I am hoping someone can guide me and wont take offense in questions.  Please help me if you can.


Sure.

You need to create a server action with the following content.


An Advanced SQL with Addresses in Output Entities.

Then write the SQL script to delete it.


This action needs to be called somewhere. I'd advise a timer, but you can do it in a button.

Could I just create a screen that holds buttons like this one, each performing a different function.  Only an admin would have access to it.

Yes, of course.

But I hope you don't need to run such actions many times!:) 

Nuno Reis wrote:

Yes, of course.

But I hope you don't need to run such actions many times!:) 

Its just for our MVP, I will put it inside an existing server action going forward : )  Do you have an example you can share?


Solution

I don't have one here, but I did a small one that reads sample data.

I disabled the delete queries to make sure you see the logic before losing all test data.

Rules for a real project:

  • Check permissions to see page
  • Recheck permissions on action before executing.
  • Log everything.
  • Refresh data on screen to confirm that you have 0.

This is just to help you think.



Solution

Nuno Reis wrote:

I don't have one here, but I did a small one that reads sample data.

I disabled the delete queries to make sure you see the logic before losing all test data.

Rules for a real project:

  • Check permissions to see page
  • Recheck permissions on action before executing.
  • Log everything.
  • Refresh data on screen to confirm that you have 0.

This is just to help you think.



Thank you.  Will I be able to delete from OSUSR?  I get this.


All tables related with OutSsytems are saved as OS something. The ones you use are probably in OSUSR_


For this case OSUSR means "table create by a module" "2RY" is the unique prefix of the module that owns the entity. "Addresses" is the entity. All together you have a table OSUSR_2RY_ADDRESSES


The table name you write is only the entity. Addresses should be enough.


Tip 1: Use autocomplete in Advanced SQL to check if it exists.

Tip 2: And the action must be in the same module as the entity.

Nuno Reis wrote:

All tables related with OutSsytems are saved as OS something. The ones you use are probably in OSUSR_


For this case OSUSR means "table create by a module" "2RY" is the unique prefix of the module that owns the entity. "Addresses" is the entity. All together you have a table OSUSR_2RY_ADDRESSES


The table name you write is only the entity. Addresses should be enough.


Tip 1: Use autocomplete in Advanced SQL to check if it exists.

Tip 2: And the action must be in the same module as the entity.


Ok that makes sense.  How can I copy your oml into mine?  So I dont recreate it from scratch, this is all dummy date in that table and I dont mind losing it : )  just trying to learn.

I think Im almost there, this is the last of the errors


The output structure is just dummy. The return of the DELETE command is null.


You can't test a delete command in Studio. It is that way to prevent big mistakes.

Nuno Reis wrote:

The output structure is just dummy. The return of the DELETE command is null.


You can't test a delete command in Studio. It is that way to prevent big mistakes.

I think Im almost there ; )


This is the last error any thoughts?



Move the SQL so it gets in the middle of the line.

Right now it is only over it and not connected.

Ok so I incorporated everything and published it, I didnt add it to any menus.  I figured I could just put the screen name in the URL and it would take me to it  : )  it takes me to the login screen.

Peter Goyal wrote:

Ok so I incorporated everything and published it, I didnt add it to any menus.  I figured I could just put the screen name in the URL and it would take me to it  : )  it takes me to the login screen.

Hi Peter,

You can give anonymous role to the screen you created and you should be able to access it without loggin in. Alternatively you can use your own credentials that you use to login into service studio environment and see the screen.

Thanks

Ravi


If you followed my example, you need Admin role to execute the deletion so you really must login.

Thank you gentlemen.  I had to use the debugger because it keeps going down the false path.  Any suggestions, the data is there as you can see from the screen shot.


Hello.

You are almost there!

First your DeleteAllAddresses seems a local action. Database operations should be server actions (security, speed, etc).

Then your GetAddresses list is outdated. You need to refresh the query to count what is in the DB. Right now you are only counting what was there last time OS checked the table.

Nuno Reis wrote:

Hello.

You are almost there!

First your DeleteAllAddresses seems a local action. Database operations should be server actions (security, speed, etc).

Then your GetAddresses list is outdated. You need to refresh the query to count what is in the DB. Right now you are only counting what was there last time OS checked the table.

Thank you, I'm learning a lot!  So I changed it to a server action which then calls that workflow from your example which has the SQL. It came back with the same error


Can you share the oml or a print of the actions?

The logic seems fine.

is there any error? Maybe the addresses are linked to some other entity?

Nuno Reis wrote:

The logic seems fine.

is there any error? Maybe the addresses are linked to some other entity?

Got it to work!  Thank you Nuno!!!!  I just deleted the can login for now, I'll add it at a later time for some reason thats not working.  I already logged in as an admin into the app so not sure why.  So I went to service center and it definitely deleted the rows, I checked using db cleaner on steroids as well.  Issue is I'm still using 62% of the db, all of it is system except for 4% application.  On a positive note the db I was at 75% before so thats good.  Not sure what else to delete since most of the stuff is from service center.


That's great news.

On the disk there are also old versions of modules (that you can delete with db cleaner) and a lot of sql logs from all those inserts and deletes that will take days to go away.

But at least you get the hang of it and if your MVP works, you will get a decent server to develop.


Good luck with the demo!