Intersystems caché - relationale mapping (custom sql storage)

Question

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.

score 2 · Accepted Answer · answered Feb 18 '15 at 21:12

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

score 0 · Answer 2 · answered Feb 18 '15 at 14:23

0

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.

answered Feb 18 '15 at 14:23

Stephen Canzano

296
1
3

I updated my question. I exactly don't know how you mean it, but my question should be clear now. – mrfazolka Feb 18 '15 at 14:41
Based on your update it looks like your global are identical in structure but the names of your globals are different as you have the globals glob1, NextGlob, and another. Even if you made a single global that exposed each of the three globals I imagine you would force the user to write a SQL statement that somehow directed the engine to know which global you wanted. I guess I would back to one of my earlier question "Do you have an existing data dictionary" that describes these globals? – Stephen Canzano Feb 18 '15 at 18:25
I am not sure if I know what you mean by data dictionary, but if you mean informations used when using %Dictionary, so yes, I have this informations. I know number of properties and their data type and so on... – mrfazolka Feb 23 '15 at 13:40
What I mean is do you have a global that tells you the structure of your globals. – Stephen Canzano Feb 23 '15 at 17:52
Yes, it's exactly what i have – mrfazolka Feb 23 '15 at 17:56
1

Then I would write a conversion that reads the meta-data global you have and build the specific classes you desire. For example if you have a data dictinary global that looks something like ^glo(id)="GlobalName" ^glo(id,ColumnId)="ColumnName^Node^Piece" then I would convert this structure to a cache class with sql storage mapping. – Stephen Canzano Feb 23 '15 at 21:27
ok, I am really happy that you say this. Look here please: http://stackoverflow.com/questions/28607416/intersystems-cach%C3%A9-programmatically-create-new-class I figured, that this should help me to solve my problem. Is it that what are you talking about? – mrfazolka Feb 23 '15 at 21:33

Intersystems caché - relationale mapping (custom sql storage)

2 Answers2