Custom Query that Returns a RANDOM number of columns

Databases, JDBC, ObjectScript, ODBC, SQL, Caché, InterSystems IRIS

Hi!

I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns.

Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.

Here is my code:

Class Test.Test
{
	ClassMethod MyCustomQueryClose(ByRef qHandle As %Binary) As %Status
	{
	    Quit $$$OK
	}
	
	ClassMethod MyCustomQueryExecute(ByRef qHandle As %Binary, ByVal pCubeName As %Library.String) As %Status
	{
	     Quit $$$OK
	}
	
	ClassMethod MyCustomQueryFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
	{
		Set Row=$ListBuild(1,"Amir",2,"Test")
		Set AtEnd=1
	    
	    Quit $$$OK
	}
	
	ClassMethod MyCustomQueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, ByRef extinfo As %List) as %Status
	{
		Set colinfo=$lb($lb("C1","10","C1"),$lb("C2","10","C2"),$lb("C3","10","C3"),$lb("C4","10","C4"))
		Set parminfo=$lb($lb("pCubeName","10"))
		Set idinfo=$lb(0,"")
		If extoption 
		{
			Set extinfo=$lb("","")
		}	
		Quit $$$OK
	}

	
	Query MyCustomQueryCube(ByVal pCubeName As %Library.String) As %Query [ SqlProc, SqlName = "GetFeaturesFromCube" ]
	{
	} 
}

I wonder if I am doing something wrong or if there is another way of doing this.

Answers

Answer by Vitaliy Serdtsev 8 May 2019

Made a small demo with two options.

Include %occResultSet

Class dc.test [ Abstract ]
{

ClassMethod MultiRS(i As %Integer) [ ReturnResultsets, SqlProc ]
{
  i i#2 {
    $$$ResultSet("select 'Name1_1' s1,'Name2_1' s2 union all select 'Name1_2','Name2_2'")
  } else {
    $$$ResultSet("select 1 i1,4 i2,3 i3 union all select 2,5,6 union all select 3,6,9")
    $$$ResultSet("select '+1' n union all select '-2' union all select '3.1' union all select '4_4'")
  }
}

Query MyCustomQueryCube(pCubeName As %String) As %Query [ SqlProc ]
{
}

ClassMethod MyCustomQueryCubeExecute(
  ByRef qHandle As %Binary,
  pCubeName As %String) As %Status
{
  s qHandle("pCubeName")=pCubeName

  i pCubeName="a" {
    s N=2 f i=1:1:N s qHandle(N+1-i)=$lb("Name1_"_i,"Name2_"_i)
  }else{
    s N=3 f i=1:1:N s qHandle(N+1-i)=$lb(i,i+3,i*3)
  }
  s qHandle=N
  q $$$OK
}

ClassMethod MyCustomQueryCubeFetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = MyCustomQueryCubeExecute ]
{
  i qHandle=0 {
    s Row=""
    s AtEnd=1
  }else{
    s Row=qHandle(qHandle)
    s qHandle=qHandle-1
  }
  q $$$OK
}

ClassMethod MyCustomQueryCubeClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = MyCustomQueryCubeFetch ]
{
  k qHandle
  q $$$OK
}

ClassMethod MyCustomQueryCubeGetInfo(
  ByRef colinfo As %List,
  ByRef parminfo As %List,
  ByRef idinfo As %List,
  ByRef qHandle As %Binary,
  extoption As %Integer = 0,
  ByRef extinfo As %List) As %Status
{
  i qHandle("pCubeName")="a" {
    s colinfo=$lb($lb("s1",10,"s1"),$lb("s2",10,"s2"))
  }else{
    s colinfo=$lb($lb("i1",5,"i1"),$lb("i2",5,"i2"),$lb("i3",5,"i3"))
  }
  s parminfo=$lb($lb("pCubeName","10"))
  s idinfo=$lb(0,"")
  q $$$OK
}

ClassMethod MyCustomQueryCubeGetODBCInfo(
  ByRef colinfo As %List,
  ByRef parminfo As %List,
  ByRef qHandle As %Binary) As %Status
{
  i qHandle("pCubeName")="a" {
    s colinfo=$lb(2,
                  "s1",12,50,0,2,"s1","test","dc","",$c(0,0,0,0,0,0,0,0,0,0,0,0),
                  "s2",12,50,0,2,"s2","test","dc","",$c(0,0,0,0,0,0,0,0,0,0,0,0)
                 )
  }else{
    s colinfo=$lb(3,
                  "i1",4,10,0,2,"i1","test","dc","",$c(0,0,0,0,0,0,0,0,0,0,0,0),
                  "i2",4,10,0,2,"i2","test","dc","",$c(0,0,0,0,0,0,0,0,0,0,0,0),
                  "i3",4,10,0,2,"i3","test","dc","",$c(0,0,0,0,0,0,0,0,0,0,0,0)
                 )
  }
  s parminfo=$lb(1,12,50,0,2,"pCubeName",1)
  q $$$OK
}

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  try{

    s rs=##class(%SQL.Statement).%New()

    $$$ThrowOnError(rs.%PrepareClassQuery("dc.test","MyCustomQueryCube"))
    f i="a","b" d rs.%Execute(i).%Display()
    
    w !!,"=============="
    
    $$$ThrowOnError(rs.%Prepare("call dc.test_MultiRS(?)"))
    f i=0,1 d rs.%Execute(i).%Display()
    
  }catch(ex){
    w "Error ", ex.DisplayString(),!
  }
}

}

USER>d ##class(dc.test).Test()
 
 
Dumping result #1
s1      s2
Name1_1 Name2_1
Name1_2 Name2_2
 
2 Rows(s) Affected
 
Dumping result #1
i1      i2      i3
1       4       3
2       5       6
3       6       9
 
3 Rows(s) Affected
 
==============
 
Dumping result #1
i1      i2      i3
1       4       3
2       5       6
3       6       9
 
3 Rows(s) Affected
 
Dumping result #2
n
+1
-2
3.1
4_4
 
4 Rows(s) Affected
 
Dumping result #1
s1      s2
Name1_1 Name2_1
Name1_2 Name2_2
 
2 Rows(s) Affected

Accepted answer

Comment by Amir Samary 8 May 2019

Thank you! That helps a lot. The problem I was having is that I was implementing XXXGetInfo but not XXXGetODBCInfo().

Comments

Comment by Eduard Lebedyuk 7 May 2019

SQL is rather static and wants to be resolved at compile time.

What do you want to achieve?

You can have a classmethod that returns resultset(s). Check Sample.Person for example.

Comment by Amir Samary 7 May 2019

Hi!

I thought of that. But I really wanted to write a custom ObjectScript code instead of relying on a %SQL.Statement or %ResultSet. That is because the data I want to aggregate and return is not easily searchable with a single statement.

But I think I am going to be using %Dictionary.* to generate the code dynamically.

Kind regards,

Comment by Daniel Pasco 12 days ago

Amir,

If you want the results of your query to be usable by the outside world then you can easily do this by generating a custom query (subclass %SQL.CustomQuery), compile and execute it. I probably have some examples of doing this somewhere. %SQL.CustomQuery is a bit more convenient than defining a query in a class - it basically accomplishes the same thing. %SQL.CustomQuery will generate all of the relevant metadata from your property definitions and you do not have to be concerned with ROWSPEC, etc. It is fully compatible with SQL and can even be used as a TVF in other queries.

-Dan