[HOW TO] Use queries with variables in APIs

[lrobinet]

What are some methods to manipulate queries with parameters (variables) while in API code? 

Did you know that you can perform a GetCollection on an object, passing it a query with a variable and the query will be resolved with the object on which the GetCollection was executed ?

In other cases, you may treat the text stored in the "_Select" property of a Query (such as queries with multiple variables). In these cases you attain the "_Select "content from the query object via a GetProp. Using string functions you find and replace the variable text with the name of the object taking on the role of that variable.

There is also the ExecuteSel function.

MyQuery has an _Select of the following :
SELECT [Business Process] WHERE [Org-unit]=&"ManagerOrgUnit" AND [Organizational Process]=&"OrgProc"

Example of API code calling this selector in MEGA:

'get the query and set values for parameters
Set vSel = GetCollection("Query").Item("MyQuery")
v1="ManagerOrgUnit='" & [Org-unit].Item(1).Name & "'"
v2="OrgProc='" & [Organizational Process].Item(1).Name & "'"
vParmList = v1 & "" & v2
MsgBox vParmList

'run the query with parameters to give results
vSel.ExecuteSel vParmList BPCol

'display collection
For Each oBP in BPCol
   Print oBP.Name
Next

[DEB]

Hello,

I'm trying to make work your example with my query

Select [Cadre réglementaire] Where [Cadre réglementaire Composé ].[Nom]=&"name"

Here is the code in the script console:

'get the query and set values for parameters
Set vSel = GetCollection("Query").Item("MyQuery")
v1="name='ObjectName'"
vParmList = v1 & ""

'run the query with parameters to give results
vSel.ExecuteSel vParmList , col

'display collection
For Each obj in col
   Print obj.Name
Next

And I only get this error message:

Error(0x800a01b6) : Object doesn't support this property or method: 'vSel.ExecuteSel'

I've tried with vSel.Execute but dont manage to find the correct way to do it:

Set col = vSel.Execute(vParmList)

i get this error message:

Error(0x800a01a8) : Object required: 'vSel.Execute(...)'

Thanks

Best regards

[agiovannetti]

Hello,

I I'm trying to run in a VB MAcro Excel, the same scenario you explained .
I get my query, set vParmList variable, but due of a syntax error, I cannot run line vSel.ExecuteSel vParmList BPCol.

Any advice?
Thank you

[cpucci]

As a general note, when in Java you do not have an explicit method to access a "VB-only" function you can use:

• obj.callFunction("function name", parameters, ...) (if your method returns something, will be always a generic Object in Java, up to you to cast it to the right type)
• obj.callMethod("method name", parameters, ...) (if your method will not return anything (void))

As example: root.callMethod("messagebox", "Hello world from MEGA!");

[pabobo]

Here is a translation of this VB code on the Java API : 

MegaObject vSel = root.getCollection("~aHXaGCfyhuF0[Query]").get("MyQuery");
String v1 = "ManagerOrgUnit='" + root.getCollection("~QrUiM9B5iCN0[Org-Unit]").get(1).megaUnnamedField() + "'";
String v2="OrgProc='" + root.getCollection("~gsUiU9B5iiR0[Organizational Process]").get(1).megaUnnamedField() + "'";
String vParamList = v1 + v2;
System.out.println(vParamList);
                       
MegaCollection bpCol = (MegaCollection) vSel.callFunction("ExecuteSel",vParamList);

for(MegaObject oBP : bpCol)
{
	System.out.println(oBP.megaUnnamedField());
}

Regards 

[SKumar]

How does one do this using Java API?