Author Topic: Recursive SQL Query in Sparx  (Read 923 times)

sravang

  • EA User
  • **
  • Posts: 32
  • Karma: +0/-0
    • View Profile
Recursive SQL Query in Sparx
« on: February 18, 2021, 05:13:51 pm »
Hi ,

I am using sparx with SQLServer Database and I am trying to get all packages under my selected package to process all the elements under that package.

I am using a recursive query for the same. This is the query I am using -
WITH PackageData AS (
SELECT Parent_ID, Name, Package_ID
FROM t_package
Where Package_ID = 6155
UNION ALL
SELECT p.Parent_ID,p.Name, p.Package_ID
FROM t_package p INNER JOIN PackageData pd
ON p.Parent_ID = pd.Package_ID
) SELECT Package_ID AS PACKAGEID  FROM PackageData


This query works fine in sql management studio but the same doesn't work with repository.SQLQuery(SQLQuery).This returns empty string for the above query.

Is there any particular reason why this doesn't work through code.

Thanks,
Sravan

PeterHeintz

  • EA User
  • **
  • Posts: 894
  • Karma: +55/-18
    • View Profile
Re: Recursive SQL Query in Sparx
« Reply #1 on: February 18, 2021, 06:58:53 pm »
Hi Sravan,
as far as I know, Sparx intend not to allow DB changes by direct SQL queries and this is done by only forwarding SELECT statements to the SQL server.

This solution unfortunately filters out your WITH statement as well.

What you can do, is storing your "WITH" SQL into a MS SQL view and call this view from EA with a SELECT statement.
Best regards,

Peter Heintz

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11251
  • Karma: +415/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Recursive SQL Query in Sparx
« Reply #2 on: February 18, 2021, 07:29:58 pm »
There was a way, I believe.

Someone has posted a solution to this a while ago.

IIRC you have to wrap the with statement in a select statement.

The key is to start the query with SELECT

Code: [Select]
Select * from
  ( With ....)

Geert

sravang

  • EA User
  • **
  • Posts: 32
  • Karma: +0/-0
    • View Profile
Re: Recursive SQL Query in Sparx
« Reply #3 on: February 18, 2021, 11:04:41 pm »
Tried to wrap the statement inside SELECT, but it didn't work.