New post
Question
· May 7, 2019

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.

Discussion (5)1
Log in or sign up to continue
Dan Pasco · May 10, 2019

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

0 0
Vitaliy Serdtsev · May 8, 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
2 0