Execute sql query from javascript in scripter

[utkarsh]

Hi,

I want to execute a sql inline query from the script(preferbly javascript) provided by EA. I tried connecting to DB from javascript in a webpage(using asp.net) and it works fine.

However, if i try to run the same script under scripter it is not working.
It says ActiveXObject is not defined.
Can't I create a ActiveXObject in script (in EA) ?

Please guide.
Thanks.

[Geert Bellekens]

Can't you use Repository.SQLquery() ?
That way you don't have to worry about connectionstrings and stuff.

Geert

[utkarsh]

Thanks for your reply.

I want to execute a CTE. I have seen earlier that CTE/function calls(complex queries) do not work when I use Repository.SQLEquery().

Can you please guide me why it does not work with Repository.SQLEquery()?

Thanks.

[Geert Bellekens]

I use the SQLQuery method quite often, and I've never had any problems with it.
Every query that ran in SQL server management studio also worked when passed to SQLQuery.

Do you have an example of a query that doesn't work using SQLQuery and does when running directly on the database?

Geert

[utkarsh]

Hi,

//Query starts here
with prnt as
( select Package_id, Parent_ID
from t_package
where parent_id =
(select package_ID from t_package where ea_guid='{2A8D50E1-0682-4fed-92F6-98A6C080FF94}')
union all  Select t_package.Package_id, t_package.Parent_ID  
from t_package join prnt on t_package.parent_id = prnt.package_id )  

Select * from prnt

//Query ends here

Please run it in SQL studio(2005). It works. However it do not works in Repository.SqlQuery();

Can you provide me alternate solution for achiving this result set?
Thanks for your help.

[Geert Bellekens]

I see, you are right, it doesn't work.
Seems like EA is doing something with the query before it is passed to the database.

I think you better ask Sparx support for help, I'm interested in seeing the solution as I'll probably need it in the near future.

We have just upgraded our database from SQL2000 to SQL2008, so I was never able to do CTE before.
As a dirty workaround I used a query with "enough" self joins to t_package to be reasonably sure I had all levels.
Something like this:

Code: [Select]

SELECT o.Object_ID as ID,o.Object_Type as Type,isNull(o.Name,'') as Name,ISNULL(t.value,0) as ImportanceLevel,isnull(o.Author,'') as Author,o.CreatedDate as Created,o.ModifiedDate as Modified,isnull(o.Note,'') as Documentation,isnull(o.Package_ID,'') as ParentID,isnull(p.name,'') as ParentName
                 ,isnull(p9.Name +'.','') + isnull(p8.Name+'.','')+ isnull(p7.Name+'.','')+ isnull(p6.Name+'.','')  +
                 + isnull(p5.Name+'.','')+ isnull(p4.Name+'.','')+ isnull(p3.Name+'.','')+ isnull(p2.Name+'.','')+ isnull(p.Name,'') as QualifiedName  
                 FROM t_object as o  
                 left join t_objectproperties t on t.Object_ID = o.Object_ID
                 left join t_package p on o.Package_ID = p.Package_ID
                 left join t_package p2 on p.Parent_ID = p2.Package_ID
                 left join t_package p3 on p2.Parent_ID = p3.Package_ID
                 left join t_package p4 on p3.Parent_ID = p4.Package_ID
                 left join t_package p5 on p4.Parent_ID = p5.Package_ID
                 left join t_package p6 on p5.Parent_ID = p6.Package_ID
                 left join t_package p7 on p6.Parent_ID = p7.Package_ID
                 left join t_package p8 on p7.Parent_ID = p8.Package_ID
                 left join t_package p9 on p8.Parent_ID = p9.Package_ID
under the motto 10 levels of nesting packages should be enough for everyone  

Geert

[utkarsh]

Thanks Greet.

But my original problem was I am not able to connect to DB from script(in EA).
If I connect to DB from javascript from a web page it works but it does not work from inside EA.

I am not able to create an ActiveX object. It says:
"ActiveXObject is not defined"

Any sugegstions?
Thanks once again!!

[Geert Bellekens]

No sorry, never used scripting from within EA.

Geert

[msherman]

Just guessing here, but wouldn't you need to create an add-in to get an ActiveX object?

[Geert Bellekens]

I don't think so.
Here someone did it with vbscript, so I guess it should be similar with Javascript:
http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1271067418/2#2

Geert

[Eve]

I'm afraid I just use VBScript if I need an ActiveX object.

[mrf]

If you want to create an ActiveX Object in JScript, you can use the following syntax:

Code: [Select]

var xmlDOM = new ActiveXObject( "MSXML2.DOMDocument.4.0" );

[johann]

Just read the release notes for Version 8.0 of EA. They mention a mozilla javascript engine that was updated.
So, maybe the reason for your problem is: there are probably two different scripting engines available: one from Microsoft that executes JScript files (and knows how to deal with ActiveX objects) and one engine from the Mozilla corporation that executes JavaScript files and has no ActiveX support?!

[mrf]

Just read the release notes for Version 8.0 of EA. They mention a mozilla javascript engine that was updated.
So, maybe the reason for your problem is: there are probably two different scripting engines available: one from Microsoft that executes JScript files (and knows how to deal with ActiveX objects) and one engine from the Mozilla corporation that executes JavaScript files and has no ActiveX support?!

Oh oh oh. Yes. JScript and Javascript, while syntatically similar, are two totally different languages and engines. And, as you stated, only VBScript and JScript have the ability to instantiate ActiveX objects.