Skip to Content (Press Enter)
Back to Ideas
16
Like
Follow Idea
Portable SQL Functions In Advanced Query
1220Views
5Comments
New
Backend

There are some commonly-used SQL functions that are implemented differently by SQL vendors.   Example:

T-SQL -- @@ROWCOUNT
MySQL -- ROW_COUNT()

In Advanced Query, you can get around the T-SQL @@ by kludging input parameters, but why not have an OutSystems function --- maybe RowsAffected() -- that could be used in the OutSystems query definition dialog.  That way, the OutSystems SQL transpiler could output @@ROWCOUNT for MSSQL, ROW_COUNT() for MySQL and whatever the equivalent is for Oracle.  

Pretty much anywhere T-SQL uses @@ and MySQL uses a function call, this would be helpful and go a long way towards making Advanced Queries more vendor-neutral.

03 Apr 2018
Copy link to comment
2014-02-07 17-02-43
Vasco Pessanha
Changed the category to
Backend
05 Apr 2018
Copy link to comment
Merged this idea with 'Allow @@Rowcount being returned on a advanced query' (created on 22 Dec 2018 01:58:24 by Rodolfo Cardoso)
08 Apr 2019
Copy link to comment

When I try to make a massive update with an advanced query I cannot get the rowcount to know how many items were updated.


If I try the query below the platform will throw an 'Unkown Rowcount Parameter used'. when this is a valid SQL statement


UPDATE {Company} SET {Company}.[TaxNumber] = 1
SELECT @@Rowcount


I think that the advanced query should allow this sintax.



This comment was:
- originally posted on idea 'Allow @@Rowcount being returned on a advanced query' (created on 22 Dec 2018 by Rodolfo Cardoso)
- merged to idea 'Portable SQL Functions In Advanced Query' on 08 Apr 2019 21:03:15 by Craig St.Jean
22 Dec 2018
Copy link to comment
2014-02-07 17-02-43
Vasco Pessanha
Changed the category to
Database


This comment was:
- originally posted on idea 'Allow @@Rowcount being returned on a advanced query' (created on 22 Dec 2018 by Rodolfo Cardoso)
- merged to idea 'Portable SQL Functions In Advanced Query' on 08 Apr 2019 21:03:15 by Craig St.Jean
24 Dec 2018
Copy link to comment

There is a trick to make it work, but it's not pretty and not obvious.

  1. Add a text parameter to your query called RowCount
  2. Set RowCount to expand inline
  3. Set the value of RowCount to "@@ROWCOUNT"

Then you can do this:

UPDATE {Company} SET {Company}.[TaxNumber] = 1
SELECT @RowCount

That said, it would be wonderful to have an option that isn't a workaround.



This comment was:
- originally posted on idea 'Allow @@Rowcount being returned on a advanced query' (created on 22 Dec 2018 by Rodolfo Cardoso)
- merged to idea 'Portable SQL Functions In Advanced Query' on 08 Apr 2019 21:03:16 by Craig St.Jean
08 Apr 2019
Copy link to comment