how to database table clean??

how i clean my database Table in outsystems

Hello Hafiz,

Could you be more specific?

For general maintenance of the Environment Database, you can install a tool from Forge, like DB Cleaner or DB Cleaner on Steroids.

They will help you get rid of old versions of modules, deleted modules/entities/attributes, etc.

This way you can save some space. They will not deal with DATA, though.
And they can do only so much, as there are things you will not be able to delete, if they are tagged in Lifetime, for example, etc.

This is particularly complicated for Personal Environments, where you have very little space in database (around 2 GB shared between system metadata and application data).

Hope this helps you.
Cheers

Eduardo Jauch wrote:

Some components that may be of help: 

https://www.outsystems.com/forge/component-overview/5018/db-cleaner-on-steroids

https://www.outsystems.com/forge/component-overview/423/dbcleaner

https://www.outsystems.com/forge/component-overview/2176/oneclickcleanup

But there are others.


not clean my data table
like
how can empty my Database table like employe table i created when i practice now i delete the data in employe table how to do this??

You can use an SQL tool in an Action that executes a DELETE FROM {Employee}; query. This will clean the data in the entity if there are no protected references from other entities (protected FK).

Hi Hafiz,

You can either do the following:

However this is not considered best practice as it is better to use SQL Queries when performing bulk operations.

So something like this would be better:

Nordin Ahdi wrote:

Hi Hafiz,

You can either do the following:

However this is not considered best practice as it is better to use SQL Queries when performing bulk operations.

So something like this would be better:


@Nordin Ahdi: I want to do one correction here. Delete statement should be like below (without *) :)

DELETE FROM {TableName}

please check the oml file for the scenario.

Thank you,

Sudip

Sudip Dey wrote:

please check the oml file for the scenario.

Thank you,

Sudip

thanks to all and thanks a lot :) @supdip dey you always help me and understand me what I said and what my problem many thanks to you :)


@Nikhil you are absolutely right. I wrote that post in a hurry, it's not a SELECT statement :-P

not deleted my task tabel

showing all data

how to run this query


Solution

Hi Hafeez,

I can see you have selected Run and Debug in your Personal Area (F6). 

You need to publish your module to the Public area (F5) in order to able to execute the SQL when you call the Delete screen.

Regards,

Nordin

Solution

Hello Nordin, 

Database is the same for public or personal area. So, changes on any one of them will be effective in the database. 

Hafiz, 

As you placed the sql on the preparation of a page, just request that page from the browser is enough to run the query. 

If you are doing it and data is not being deleted, check for error messages in the logs (Service Center). 

Also, if you haven't done it yet, the online training and reading the documentation would help improve your OutSystems skills. 

Cheers 

P. S. Publishing to public area first is required when you make changes to the database structure, like adding/removing entities and attributes, etc. 

Thanks a lot for clearing that up Eduardo! It makes sense when I think about it.

Nikhil Gaur wrote:

@Nordin Ahdi: I want to do one correction here. Delete statement should be like below (without *) :)

DELETE FROM {TableName}

Hi Nikhil,

The * is unnecessary, but it does not cause harm :)
(I don't like it, but many people are used to it, it seems)


Eduardo Jauch wrote:

Nikhil Gaur wrote:

@Nordin Ahdi: I want to do one correction here. Delete statement should be like below (without *) :)

DELETE FROM {TableName}

Hi Nikhil,

The * is unnecessary, but it does not cause harm :)
(I don't like it, but many people are used to it, it seems)


whats your advise for me?? whats i do



Hi, 

How are you executing the action where the SQL with the DELETE? Are you calling it from a button within a Web Screen, from the browser? 

In this case, are you receiving any error in the screen? Is there any error on the Service Center Logs? 

If you have other entities with FK (Protected Delete Rule) pointing to this entity and they have data as well, the Delete will fail. In this case, you need to change the Delete rule of the FK to Delete (so this data is also delete), or execute a DELETE for this entities before the one for the actual entity you want to clean up. 

P. S. The Test button of the SQL tool will not work. 


Eduardo Jauch wrote:

Nikhil Gaur wrote:

@Nordin Ahdi: I want to do one correction here. Delete statement should be like below (without *) :)

DELETE FROM {TableName}

Hi Nikhil,

The * is unnecessary, but it does not cause harm :)
(I don't like it, but many people are used to it, it seems)


Hi Eduardo,

I think using * in DELETE statement will give error because this is not a correct SQL syntex. Are you sure this does not cause harm?


Eduardo Jauch wrote:

Nikhil Gaur wrote:

@Nordin Ahdi: I want to do one correction here. Delete statement should be like below (without *) :)

DELETE FROM {TableName}

Hi Nikhil,

The * is unnecessary, but it does not cause harm :)
(I don't like it, but many people are used to it, it seems)


Hi Eduardo,

I think Nikhil is right. As you surely know, in a SQL statement we can use * (which is a wildcard) in a SELECT statement in order to select all columns. I don't think we can use the wildcard in a DELETE statement.

Regards,

Nordin


Hello Nordin & Nikhil, 

I think the * will be ignored. But this may be my memory playing with me. 

If SQL Server does not accept it and raises an exception, and I am almost sure I already tested this syntax in the past, you would receive an error. 

Hence my question about errors, so that we can understand what is happening. 

I'm on my way to a classroom and can test this only after getting there, but maybe you could test it so that we may check if SQL Server indeed raises an error or not, because of the *, and if so, this probably would solve OPs question :) 

Hi Eduardo,

Here you go:

My memory has played the same way with me when I typed the DELETE statement with a * earlier in this post. :-)

Regards,

Nordin

Ah! 

Nice, thanks for doing the test. I was sure SQL would ignore the * and sure I had tested this in the past and it was ignored. My memory is a joker... ??

But against facts there are no arguments. This is probably be the problem, indeed. 

:) 

Cheers

It’s all good, happy to do it :-).

These kind of things keep us sharp right? ;-)

Regards,

Nordin