CODEWITHSUNDEEP
odbcmappingstorageintersystems-cache
mrfazolka
mrfazolka

Reputation: 790

Intersystems caché - relationale mapping (custom sql storage)

I have more globals in caché db with same data structure. For each global I defined class with SQL storage map, but I need to do it generically for all globals. Is it possible to define one class with sql storage map which will be used for mapping before every SQL query execution? I need to avoid class declaration for each global which I need to be accessible via SQL. I use ODBC for execute SQL statements.

If someone can help me, i will very appreciate it

My globals looks like this:

^glob1("x","y","SL",1)  =   "Name"
^glob1("x","y","SL",1,"Format") =   "myFormat"
^glob1("x","y","SL",1,"Typ")    =   "my Type"
^glob1("x","y","SL",2)  =   "Name2"
^glob1("x","y","SL",2,"Format") =   "myFormat2"
^glob1("x","y","SL",2,"Typ")    =   "Type2"

^nextGlob("x","y","SL",1)   =   "Next Name"
^nextGlob("x","y","SL",1,"Format")  =   "Next myFormat"
^nextGlob("x","y","SL",1,"Typ") =   "my Type"

^another("x","y","SL",13)   =   "Another Name"
^another("x","y","SL",13,"Format")  =   "Another myFormat"
^another("x","y","SL",13,"Typ") =   "Another Type"

I want to have sql access to globals using one ObjectScript class.

Upvotes: 1

Views: 347

Answers (2)

DAiMor
DAiMor

Reputation: 3205

If you needed only read data from Caché by ODBC. So, in ODBC you can use CALL statement. And you can write some SqlProc, which can be called by ODBC.
As I can see, all of your globals with the same structure. If it so, it will be easy. You can put something like this, in your class.

Query Test() As %Query(ROWSPEC = "ID:%String,Global:%String,Name:%String,Typ:%String,Format:%String") [ SqlProc ]
{
}

ClassMethod TestExecute(ByRef qHandle As %Binary) As %Status
{
    #; Initial settings
    #; List of Globals
    set $li(qHandle,1)=$lb("glob1","nextGlob","another")
    #; Current Global index
    set $li(qHandle,2)=1
    #; Current ID in global
    set $li(qHandle,3)=""
    Quit $$$OK
}

ClassMethod TestClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = TestExecute ]
{
    Quit $$$OK
}

ClassMethod TestFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = TestExecute ]
{
    set globals=$lg(qHandle,1)
    set globalInd=$lg(qHandle,2)
    set id=$lg(qHandle,3)
    set AtEnd=1
    for {
        set global=$lg(globals,globalInd)
        quit:global=""
        set globalData="^"_global
        set globalData=$na(@globalData@("x","y","SL"))
        set id=$o(@globalData@(id),1,name)
        if id'="" {
            set AtEnd=0
            set typ=$get(@globalData@(id,"Typ"))
            set format=$get(@globalData@(id,"Format"))
            set Row=$lb(id,global,name,typ,format)
            set $li(qHandle,3)=id
            quit
        } elseif $i(globalInd) {
            set id=""
            set $li(qHandle,2)=globalInd
        }
    }
    Quit $$$OK
}

And then you can execute statement like this

CALL pkg.classname_test()

And as a result it will be something like on this picture enter image description here

Upvotes: 2

Stephen Canzano
Stephen Canzano

Reputation: 326

If all of the globals are the same then you could do this but it is likely that your globals are all different making a single storage map unlikely. Do you already have a data dictionary/meta data system that describes your existing globals? If so I would consider writing a conversion from your existing data dictionary definition to cache classes.

Upvotes: 0

Related Questions