Problem with complex SQL query

[Volkov]

Hi everybody  
Got a trouble with complex SQL query execution. Using Repository. SQLQuery() call. Some queries are doing great but some are failing returning no data. Well, looks like only limited SELECT is supported but I hope for best since we need to run really complex queries sometimes.
This is the typical inoperable request (sorry for messing, I'm not the author!):

Code: [Select]

WITH Q_TREE (ID, Name) AS ( select po.Object_ID Child_ID, po.Name Child_Name from [dbo].[t_object] po WHERE po.Object_ID IN ( select do.Object_ID from dbo.t_diagramobjects do INNER JOIN dbo.t_object ob ON ob.Object_ID = do.Object_ID where do.Diagram_ID = 458 and ob.Stereotype = 'bobject' ) UNION ALL select cc.Object_ID Children_ID, cc.Name Child_Name from (select object_id, Name from [dbo].[t_object] where Stereotype = 'bobject' ) co INNER JOIN ( SELECT ci.End_Object_ID, pi.Object_ID, pi.Name, pi.Package_ID from dbo.t_connector ci INNER JOIN [dbo].[t_object] pi ON pi.Object_ID = ci.Start_Object_ID where ci.Connector_Type = 'Aggregation' ) cc ON co.object_id = cc.End_Object_ID INNER JOIN Q_TREE t ON t.ID = co.Object_ID) SELECT DISTINCT ID, Name FROM Q_TREEWell, I can predict that I'll be sent to the docs where written that Repository. SQLQuery() is for the SELECT queries only. Yet, perhaps anyone knows the way how to execute complex queries form the EA scripts?
Thanks!!!

[qwerty]

The SQL is cooked by EA before being sent to the RDBMS (though Sparx claims that the fire is turned low). Also different SQL dialects are used on different RDBSM (remember Animal Farm: All SQL are equal, but some are more equal than others).

q.

[Volkov]

So one can call SQLQuery for very limited types of queries, right?
Any detour or trick???
Is it possible to run select with stored procedure or function??

Thanks!!

[qwerty]

This is not really documented. Stored procedures might work. I haven' done that myself but remember others did so. You need to experiment a bit.

q.

[Volkov]

Thanks! Gonna give it a try tomorrow. Frankly, I've been thinking that EA sends SQL as is. Or probably, changing MS SQL Server OLE DB provider to ODBC or any other driver will be enough. The same request run in the MS SQL Server Management Console and the Oracle SQL Studio is working just fine.

Anyway, thanks !!!

[Eve]

Unless I'm mistaken the problem is that because it doesn't start with "select" EA won't even try sending it.

[Volkov]

Really good version, too! Will be tested today. Will be trying to rebuild the query.
Thanks!

[qwerty]

Oops. I assumed the above was just an excerpt  

q.

[Volkov]

Hi guys,
The query issue still alive. I've been trying to wrap that query with Transact SQL UDF returning table. No luck. Tested on some other user defined functions. Some of them are ok, some are not. So kinda unstable problem and there is no way to understand which queries are allowed and how the EA check them before execution.
So this code:

Code: [Select]

Repository.SQLQuery("SELECT * someUDF()")works time after time. Or doesn't. Depends. Can't find what's wrong. I can only suggest that EA's unable to parse the output from the function. But unable to find any clue or directives, how query result should be defined and supplied to the EA, so the latter would be able to transform it into the output XML.
Any ideas or references??
Thank you!

[qwerty]

Since SQLQuery is a supported function you should directly mail Sparx support with the issue.

q.

[Volkov]

Yep! But the time is very valuable and official reply usually flies back few days at least  

[qwerty]

They are quite responsive at Sparx. Also remember: it's holiday time and a lot of users are out of office.

q.