How To Clear Junk Data In Entity During Developement

How To Clear Junk Data In Entity During Developement

  

Hi,

During development, i have a lot of junk data created while testing.

This is my first time to clear the junk data in OutSytems.

May i know how you guys do that?

1.Clear though SQL Management Studio?

2.Purposely create a button, writes the SQL in to clear the data?

Can someone advise?


Solution

Hi Johnson,

Typically, the junk data lives on our development environment, not the production environment, so not really a problem there. There's a number of things you could do, like indeed using SQL Management Studio (MSSQL) or Toad (Oracle), but a simple SQL node with a DELETE FROM {MyTable} that you "Test" may also work (I'm not 100% sure the Platform doesn't do a rollback, otherwise a simple Timer will do as well). The only downside to deleting is that Auto-numbers don't get reset. A third option could be a Ctrl-X/Ctrl-V of the Entitie(s), but the downside to that is that the data doesn't get purged, but keeps residing in the database.

Alternatively, if the database size is not the biggest concern, you could have an IsActive flag in each table (or at least the main table) that you could reset, which in general is a good idea to have to avoid "hard" deletes, and update the flag for all records.

So there's basically all kinds of possibilities, depending on your data and the structure of the database.


Solution

Thanks Kilian

You're welcome Johnson. Happy coding!

Hi,

Just to add to Killian's answer, about using an SQL node and "test" it.
Yes, the platform will do a roll back, so nothing will be deleted.
In order to make the deletion permanent, you need to do a "commit".

Ex.:

DELETE FROM {Table1};
DELETE FROM {Table2}; /* that has FK to Table1 */
DELETE FROM {Table3}; /* that has FK to Table1 and Table2 */
COMMIT;

After testing, you will see an error about NULL, but the delete will be effectively done.

Cheers,
Eduardo Jauch


Thanks for the useful addition Eduardo!

Eduardo Jauch wrote:

Hi,

Just to add to Killian's answer, about using an SQL node and "test" it.
Yes, the platform will do a roll back, so nothing will be deleted.
In order to make the deletion permanent, you need to do a "commit".

Ex.:

DELETE FROM {Table1};
DELETE FROM {Table2}; /* that has FK to Table1 */
DELETE FROM {Table3}; /* that has FK to Table1 and Table2 */
COMMIT;

After testing, you will see an error about NULL, but the delete will be effectively done.

Cheers,
Eduardo Jauch


Good Day Eduardo,


Upon using the script same as above, we keep on receiving error such as screenshot below. Hope you can help on our error.


Thanks,

Percie

Percival Parco wrote:

Eduardo Jauch wrote:

Hi,

Just to add to Killian's answer, about using an SQL node and "test" it.
Yes, the platform will do a roll back, so nothing will be deleted.
In order to make the deletion permanent, you need to do a "commit".

Ex.:

DELETE FROM {Table1};
DELETE FROM {Table2}; /* that has FK to Table1 */
DELETE FROM {Table3}; /* that has FK to Table1 and Table2 */
COMMIT;

After testing, you will see an error about NULL, but the delete will be effectively done.

Cheers,
Eduardo Jauch


Good Day Eduardo,


Upon using the script same as above, we keep on receiving error such as screenshot below. Hope you can help on our error.


Thanks,

Percie

Hello Percival,

Not an Oracle guy, but from here (and from my experience), I would say that if you copy/paste the text in the screen, depending on where you were typing, you can have some weird character that is not visible. 

Try typing by hand and see if it works.

Cheers

P.S.

Not being an ORACLE guy, do not know if it is necessary to do something different than what you are doing in terms of script, like have a BEGIN and an END or something like that.

Cheers.

If you are not yet on q&a and production...


you also can be radical by simply deleting the espace and publishing it again :D


J. wrote:

you also can be radical by simply deleting the espace and publishing it again :D

This is too radical. 

Why not just ctr-x ctr-v the entities? They will be wiped out also (I think...)


I'm not sure the actual database tables are removed then. I think the junk data still sits in the database, without any way to clean it up...

Kilian Hekhuis wrote:

I'm not sure the actual database tables are removed then. I think the junk data still sits in the database, without any way to clean it up...

Yes. I was thinking in the "entities", not the old tables itself.
But than those tables can be removed with DbCleaner.


Eduardo Jauch wrote:

J. wrote:

you also can be radical by simply deleting the espace and publishing it again :D

This is too radical. 

Why not just ctr-x ctr-v the entities? They will be wiped out also (I think...)



You can cut paste the entities and all data will be lost but if you deploy same application till Production, data will be lost in Production also. So this habit is good when your application is still till PPR.

Suraj Borade wrote:

Eduardo Jauch wrote:

J. wrote:

you also can be radical by simply deleting the espace and publishing it again :D

This is too radical. 

Why not just ctr-x ctr-v the entities? They will be wiped out also (I think...)



You can cut paste the entities and all data will be lost but if you deploy same application till Production, data will be lost in Production also. So this habit is good when your application is still till PPR.

Well remembered, Suraj :)
This (behavior) is one of those things we (many people) would like to change... Too risk ;)


Also, again, though the data will be lost, it will still occupy your database.

Kilian Hekhuis wrote:

Also, again, though the data will be lost, it will still occupy your database.

But I'm pretty sure that DBCleaner does the drop of tables associated with deleted entities... 


Probably, but then you need to run that as well.

Yes.

Is this an oracle database ?

then replace 

DELETE FROM {Table1};

by

DELETE FROM Table1;

@Rui: I don't think that is the solution (or will work at all)? The curly-braces entity names are replaced by the Platform by the actual table name, as can be viewed in the "Executed SQL" tab. By removing the curly braces, the command is sent to the database as-is, and will fail since there's no "Table1" in there (but rather, something like OSUSR_xxx_Table1, and we're not even talking about schemas and whatnot)?

Kilian Hekhuis wrote:

@Rui: I don't think that is the solution (or will work at all)? The curly-braces entity names are replaced by the Platform by the actual table name, as can be viewed in the "Executed SQL" tab. By removing the curly braces, the command is sent to the database as-is, and will fail since there's no "Table1" in there (but rather, something like OSUSR_xxx_Table1, and we're not even talking about schemas and whatnot)?

Yes , you are right. Just pointing out that oracle does not recognize the "{" characters as valid.


True, but neither does MSSQL. The {} are replaced by the Platform.

Rui Madaleno wrote:

Kilian Hekhuis wrote:

@Rui: I don't think that is the solution (or will work at all)? The curly-braces entity names are replaced by the Platform by the actual table name, as can be viewed in the "Executed SQL" tab. By removing the curly braces, the command is sent to the database as-is, and will fail since there's no "Table1" in there (but rather, something like OSUSR_xxx_Table1, and we're not even talking about schemas and whatnot)?

Yes , you are right. Just pointing out that oracle does not recognize the "{" characters as valid.


I'm almost sure it is a copy paste problem. Happens a lot. Some "hidden" characters go together and the database does not understand them. The { } will not be present in the query being sent to the database.


A bit to the side of the current discussion, but for anyone that may be having trouble with deleting test data from Entities check out the FAQ about How to delete data from Entities.

I wish we would have default rights to truncate the data.

less logging, less worry about timeouts