SQL in Outsystems

Hi @all

I am new in Outsystems. How to do CRUD operations in outsystems using SQL quries instead of using entity actions.

Please share .oml of it

Hi @Rohan Roy,

You can use an advanced SQL widget to use custom queries in OutSystems. Remember Advanced SQL is a server action widget.

Attached CRUD oml using AdvancedSQL widget for your reference.

Moreover that you can use the below query to update single field of multiple records.

UPDATE {Entity1} SET {Entity1}.[IsActive] = 'False' WHERE {Entity1}.[Id] IN (1,2,3) 

Thanks, Aadhavan S

CRUDAdvancedSQLv28.oml

mvp_badge
MVP

Hi Aadhavan,

Why you use a text string instead of a boolean for the "IsActive"??? If you create an IsActive as a Boolean in your Entity, the right check in a SQL is:

{Entity1}.[IsActive] = 0

Hi Killian, I agree with you. Query can be used as

UPDATE {Entity1} SET {Entity1}.[IsActive] = 0 WHERE {Entity1}.[Id] IN (1,2,3) 


Hi Rohan,

how to use sql node in an Outsystems project is covered in the beginners guided paths.  Most noteworth for CRUDs is that you'll have to use some (dummy) export structure, as it is not allowed to have sql node with no export.

see for example this course

how to do CRUD's with SQL, is not an Outsystems  question, so you can google anything you need to know for that.

Dorine

EDIT : a tip :  I just went to Learn zone of Outsystems, and typed sql in the search box.

Hi @Rohan Roy , you can SQL widget which you can find in server actions and you can write custom logic.



Thanks,

Sandeep.

HI @Rohan Roy , as a suggestion we must use Entity Actions provided by the platform if we can get done the job with those actions, we should go for Custom SQL whenever bulk operations are required.

Anyways I'm providing an OML which consists of basic CRUD Operations.

let me if anything is needed I'll add it and re-upload the OML.



Thanks,

Sai.

SQL.oml

Hi @Rohan Roy,

You can use an advanced SQL widget to use custom queries in OutSystems. Remember Advanced SQL is a server action widget.

Attached CRUD oml using AdvancedSQL widget for your reference.

Moreover that you can use the below query to update single field of multiple records.

UPDATE {Entity1} SET {Entity1}.[IsActive] = 'False' WHERE {Entity1}.[Id] IN (1,2,3) 

Thanks, Aadhavan S

CRUDAdvancedSQLv28.oml

mvp_badge
MVP

Hi Aadhavan,

Why you use a text string instead of a boolean for the "IsActive"??? If you create an IsActive as a Boolean in your Entity, the right check in a SQL is:

{Entity1}.[IsActive] = 0

Hi Killian, I agree with you. Query can be used as

UPDATE {Entity1} SET {Entity1}.[IsActive] = 0 WHERE {Entity1}.[Id] IN (1,2,3) 


mvp_badge
MVP

Hi Rohan,

May I inquire why you want to do this? Because the only good reason to use an SQL for CRUD actions would be for mass delete/update. But for one record, always use the available Entity Actions.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.