2
 Followers
15
 Likes

Portable SQL Functions In Advanced Query

Backend
New

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.

Created on 3 Apr 2018
Comments (5)

Changed the category to Backend


Merged this idea with 'Allow @@Rowcount being returned on a advanced query' (created on 22 Dec 2018 01:58:24 by Rodolfo Cardoso)

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

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

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
views
827
Followers
2