Use table-object as a part of SQL Query

Question

Question

Eduard Lebedyuk · Apr 1, 2020

During runtime I build an object which is essentially a wrapper over in-memory table:

col1	...	colN
val11	...	valN1
val12	...	valN2

I want to use this object as a part of INSERT or UPDATE queries, based on a value of some column (the main use case one of the columns is an ID value)

What's the best way to expose the object to SQL?

I have complete control over object class, so if I need to add something (or even change runtime internals), it's no problem.

Object may have different columns depending on runtime situation, but during one query execution the fields are constant.

I'm interested in the most performant solution.

I want to write queries like this

INSERT INTO table (Name,Age) SELECT Name,Age FROM myObject

And:

UPDATE table AS t
SET t.Name = obj.Name
FROM myObject AS obj
WHERE t.Id = obj.Id

Discussion (13)1

Log in or sign up to continue

Julius Kavay · Apr 1, 2020

Create a temp table with all properties you need, store it in a Cachte/IRIS-Temp unde $J of the running job (it could be, your applcation runs in several instances at the same time) an use it in your INSERT / UPDATE.

This is your Table

Class My.Table1 Extends %Persistent
{
Property Name As %String;
Property Age As %Numeric;

ClassMethod Test()
{
  d ##class(My.Temp).%DeleteId($j)
  s tmp=##class(My.Temp).%New()
  s tmp.TempID=$j, tmp.Name="Paul", tmp.Age=69
  d tmp.%Save()
  // or popolate the My.Temp via INSERT...  

  &sql(INSERT INTO My.Table1 (Name,Age)
       SELECT Name,Age FROM My.Temp WHERE TempID=$j
      )
}
}

and this is the Temporary Table

Class My.Temp Extends %Persistent
{
  Parameter DEFAULTGLOBAL = "^CacheTemp.TempTable"; Property TempID As %Integer;
  Property Name As %String;
  Property Age As %Numeric;

  Index main On TempID [ IdKey ];

}

0 0

Dan Pasco · Apr 1, 2020

You didn't specify a version so I'll use IRIS 2020.1. This should also work in 2019 versions. My example uses a Table-Valued Function. Any query can potentially be used as a table valued function. %SQL.CustomQuery simply generates all the infrastructure for you. Custom query works quite simply and is very similar to an embedded SQL cursor - you implement Open, Fetch and Close yourself (%OpenCursor, %FetchCursor, %CloseCursor). You define properties that correspond to columns in your row. You define private properties to hold the cursor state. Open typically binds the source data to the instance of your custom query and close releases any resources. Fetch simply advances the cursor to the next row, populating the properties that represent columns in your table. Look at the class doc for %SQL.CustomQuery for more details.

select * from TVF_TEST('[["one",2,"three"],["first",0,"third"]]')

col1	col2	col3
one	2	three
first	0	third

The class is simple:

Class User.TVF Extends %SQL.CustomQuery [ Language = objectscript ]
{

Parameter SQLNAME As String = "TVF_TEST";

Property data As %Library.DynamicArray [ Private ];

Property iterator As %Iterator.Array [ Private ];

Property col1 As %String;

Property col2 As %Integer;

Property col3 As %String;

Method %OpenCursor(data As %Library.DynamicArray) [ Private ]
{
    if $isobject(data) {
        set ..data = data
    } else {
        set ..data = [].%FromJSON(data)
    }
    set ..iterator = ..data.%GetIterator()
}

Method %FetchCursor() As %Library.Integer
{
    if ..iterator.%GetNext(.key,.value) {
        set ..col1 = value.%Get(0)
        set ..col2 = value.%Get(1)
        set ..col3 = value.%Get(2)
        return 1
    } else {
        set ..col1 = ""
        set ..col2 = ""
        set ..col3 = ""
    }
    return 0
}

}

1 0

Julius Kavay Apr 2, 2020 to Eduard Lebedyuk

You could try this way:

Class My.Table2 Extends %Persistent
{

Property Name As %String;

Property Age As %Numeric;

Property City As %String;

Property Phone As %String;

ClassMethod Test()
{
    // This is your Data-Object...
    set data=[]
    do data.%Push({"Name":"Joe", "Age":44, "City":"Boston", "Phone":"1-234-4567"})
    do data.%Push({"Name":"Ron", "Age":48, "City":"Dallas", "Phone":"1-234-5678"})
    do data.%Push({"Name":"Eve", "Age":40, "City":"Miami",  "Phone":"1-234-4567"})
    
    do data.%Push($lb("Tommy", 50, "New York", "1-345-6789"))
    do data.%Push($lb("Alexa", 35, "Portland", "1-567-8901"))

    // Now insert all the above data into your table...
    if 'data.%Size() quit
    
    set cnt=0, size=data.%Size()
    while $i(cnt)<=size {
        set rowData=data.%Get(cnt-1)
        &sql(
            INSERT INTO My.Table2 (Name,Age,City,Phone)
            VALUES (My.Table2_DataProvider(:rowData), :row(2), :row(3), :row(4))
        )
    }
} 

ClassMethod DataProvider(rowData) As %String [ PublicList = row, SqlProc ]
{
    kill row
    if $isobject(rowData) {
        set it=rowData.%GetIterator()
        while it.%GetNext(,.val) { set row($i(row))=val } 

    } else {
        for i=1:1:$ll(rowData) { set row(i)=$lg(rowData,i) }
    }
    quit row(1)
}

Storage Default
{
<Data name="Table2DefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>Name</Value>
</Value>
<Value name="3">
<Value>Age</Value>
</Value>
<Value name="4">
<Value>City</Value>
</Value>
<Value name="5">
<Value>Phone</Value>
</Value>
</Data>
<DataLocation>^My.Table2D</DataLocation>
<DefaultData>Table2DefaultData</DefaultData>
<IdLocation>^My.Table2D</IdLocation>
<IndexLocation>^My.Table2I</IndexLocation>
<StreamLocation>^My.Table2S</StreamLocation>
<Type>%Library.CacheStorage</Type>
} }

Create your data for insert and then

INSERT into yourtable (Prop1, Prop2, ...)

VALUES (sqlProcForTheFirstValue(), :localVarForOtherValues(2), :localVatForOtherValues(3),...)

see the above example.

Take care of the sequence of INSERT names and row(i) values.

0 0

Vitaliy Serdtsev Apr 30, 2020 to Eduard Lebedyuk

Thank you for asking.
It seems that no one has tried my code in all this time, or it's just that no one is interested in it.

There are two ways to solve the issue:

configure Undefined=2. I have historically set this value. This can be done programmatically or via SMP

fix the code:

ClassMethod NewQuery1Fetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  if qHandle>1 {
    set Row=qHandle($increment(qHandle,-1))
  } else {
    set AtEnd=1
  }

  quit $$$OK
}

1 0

score 0 · Answer 1 · 2020-04-01T11:51:31-04:00

Eduard Lebedyuk Apr 1, 2020 to Julius Kavay

Interesting! Tank you!

I can also use PPG for runtime storage.

0 0

score 0 · Answer 2 · 2020-04-15T08:30:09-04:00

coming late to the party, but just as a small and likely irrelevant note: if you use a PPG for storage and at some point (because the table appears big) the query optimizer thinks it's worth using parallel query execution, it won't see a thing in those worker processes. You should be able to avoid that using the %NOPARALLEL keyword though.

score 0 · Answer 3 · 2020-04-01T11:50:18-04:00

Thank you for the info, Daniel!

Can I implement %GetProperty instead of specifying the list of properties explicitly?

I don't want to compile a new class for each new table-object.

score 0 · Answer 4 · 2020-04-01T12:02:46-04:00

That is a problem. SQL requires metadata to be provided at statement prepare time. Any SQL-Invokable-Routine (function - including Table-Valued Function - and procedure as invoked by CALL) must provide that metadata and that metadata is generated by the compiler. There is no dynamic prepare-time hook unfortunately.

There is syntax in standard SQL that allows you to provide "value to column binding" within the statement (late-schema binding) but we do not support that.

score 0 · Answer 5 · 2020-04-01T12:25:52-04:00

Eduard Lebedyuk Apr 1, 2020 to Dan Pasco

I suppose I can have one Id column and several col1, col2 ..., colN columns, so losing the names.

0 0

score 1 · Answer 6 · 2020-04-02T15:53:43-04:00

Or something like that:

Class dc.test Extends %Persistent
{

Property Name As %String;

Property Age As %Numeric;

Property City As %String;

Property Phone As %String;

/// d ##class(dc.test).Test()
ClassMethod Test()
{
    do ..%KillExtent()
    
    // This is your Data-Object...

    ;set data="",data=[],data=..%New()

    set data=[
      {"Name":"Joe", "Age":44, "City":"Boston", "Phone":"1-234-4567"},
      {"Name":"Ron", "Age":48, "City":"Dallas", "Phone":"1-234-5678"},
      {"Name":"Eve", "Age":40, "City":"Miami",  "Phone":"1-234-4567"},
      ($listbuild("Tommy", 50, "New York", "1-345-6789")),
      ($listbuild("Alexa", 35, "Portland", "1-567-8901"))
    ]

    // Now insert all the above data into your table...
    &sql(
      INSERT INTO dc.test(Name,Age,City,Phone)
      SELECT $list(row,1),$list(row,2),$list(row,3),$list(row,4) 
      FROM dc.DataProvider(:data)
    )
    
    zwrite ^dc.testD

    // Now update your table...
    set data=[($listbuild("Vitaliy", 35, "Moscow", "1-999-1234"))]

    &sql(
      UPDATE dc.test t
      SET t.Name=obj.Name
      FROM (SELECT $list(row,1) Name,$list(row,2) Age  FROM dc.DataProvider(:data)) obj
      WHERE t.Age=obj.Age
    )
    
    zwrite ^dc.testD
}

Query NewQuery1(data) As %Query(ROWSPEC = "row:%List") [ SqlName = DataProvider, SqlProc ]
{
}

ClassMethod NewQuery1Execute(
  ByRef qHandle As %Binary,
  data) As %Status
{
  if $IsObject(data),data.%IsA("%Library.DynamicArray"),data.%Size() {

    set qHandle=data.%Size()+1

    for i=1:1:data.%Size() {
      set r=data.%Get(i-1)

      if $IsObject(r) {
        set it=r.%GetIterator(),row=""
        while it.%GetNext(,.val) { set row=row_$listbuild(val) } 

      } else {
        set row=r
      }
      set qHandle(qHandle-i)=$listbuild(row)
    }

  }

  quit $$$OK
}

ClassMethod NewQuery1Fetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  if qHandle {
    set Row=qHandle($increment(qHandle,-1))
  } else {
    set AtEnd=1
  }

  quit $$$OK
}

ClassMethod NewQuery1Close(ByRef qHandle As %Binary) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  quit $$$OK
}

Storage Default
{
<Data name="testDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>Name</Value>
</Value>
<Value name="3">
<Value>Age</Value>
</Value>
<Value name="4">
<Value>City</Value>
</Value>
<Value name="5">
<Value>Phone</Value>
</Value>
</Data>
<DataLocation>^dc.testD</DataLocation>
<DefaultData>testDefaultData</DefaultData>
<IdLocation>^dc.testD</IdLocation>
<IndexLocation>^dc.testI</IndexLocation>
<StreamLocation>^dc.testS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

Result:

USER>d ##class(dc.test).Test()
^dc.testD=5
^dc.testD(1)=$lb("","Joe",44,"Boston","1-234-4567")
^dc.testD(2)=$lb("","Ron",48,"Dallas","1-234-5678")
^dc.testD(3)=$lb("","Eve",40,"Miami","1-234-4567")
^dc.testD(4)=$lb("","Tommy",50,"New York","1-345-6789")
^dc.testD(5)=$lb("","Alexa",35,"Portland","1-567-8901")
^dc.testD=5
^dc.testD(1)=$lb("","Joe",44,"Boston","1-234-4567")
^dc.testD(2)=$lb("","Ron",48,"Dallas","1-234-5678")
^dc.testD(3)=$lb("","Eve",40,"Miami","1-234-4567")
^dc.testD(4)=$lb("","Tommy",50,"New York","1-345-6789")
^dc.testD(5)=$lb("","Vitaliy",35,"Portland","1-567-8901")

score 0 · Answer 7 · 2020-04-30T06:05:51-04:00

Does not work for me:

d ##class(dc.test).Test()
^dc.testD=5
^dc.testD=5
 
PYTHON>w $zv
IRIS for Windows (x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:14:33 EDT

score 0 · Answer 8 · 2020-04-30T16:39:45-04:00

Eduard Lebedyuk Apr 30, 2020 to Vitaliy Serdtsev

Thanks Vitaliy, it works now!

0 0

score 0 · Answer 9 · 2020-04-30T16:49:26-04:00

Evgeny Shvarov Apr 30, 2020 to Vitaliy Serdtsev

wow. Never knew we have this: Undefined=2.

Thanks, Vitaly!

0 0