I am connected to external database and I am getting this error when I am trying to execute this query! 

Does anyone has an idea about why is this happening?

Hi Mohamed,

I am not sure, but maybe its something to do with the output structure, and the delete not returning anything.

Try adding a select that will populate the structure after the delete.

eg

delete from {CITIES} where City_Id = @CityId;

select 'ABC';


Otherwise, some more information may be helpful, such as

* What type of database is the external database?

* What attributes and type does Structure1 have?

* Do you get a similar error with a query that returns values?


Hope this helps!

Kind regards,

Stuart


Stuart,

No need to add a select. Although the delete does not return anything Outsystems requires an output structure. Anyone will do.

The problem is the SQL it self

instead of City_Id = you need to code {CITIES.[City_Id] =

Also as it is an external database, ensure that you have the proper permissions on the table to delete records.

Regards,

Daniel 

Ah yes, I missed that. Daniël is correct.

Also, just a typo above, it should be {CITIES}.[City_Id] = 

To give you more explanation (over Daniël's response) on how SQL queries works in OutSystems. 

  • To reference an entity in your SQL query write it between  { curly brackets (e.g. {CITIES}), and
  • To reference an entity attribute write it between  [ square brackets (e.g. [City_Id]), and
  • To reference an input parameter in your SQL statement use a @ prefix, e.g. @CityId.

So, your query should be 

 delete from {CITIES} where {CITIES}.[City_Id] = @CityId


Daniël Kuhlmann wrote:

Stuart,

No need to add a select. Although the delete does not return anything Outsystems requires an output structure. Anyone will do.

Daniel 

Delete, insert and update statement return something. The number of rows affected by the operation. Since advanced queries require a structure on these situations I create a structure with a integer attribute


Hello Nelson. That is a really good idea. I never though on it. Can who give some more details how you fill it?

TIA


Hi all,


Thanks for your answers, but I don't think the syntax is the problem:


Also, I have an integere attribute in my output structure.


Answering some questions:

I am using EXASOL as an external Database.

I don't get this error with Select Query for example.

Alberto Ferreira wrote:

Hello Nelson. That is a really good idea. I never though on it. Can who give some more details how you fill it?

TIA


Check this post https://www.outsystems.com/forums/discussion/45595/getting-total-affected-rows-in-advanced-update-query/

Thanks Nelson. Very Useful