Intersystems CACHESQL Database Globals to ODBC or

Question

I have a Intersystems CacheSQL Database which I have to work with in .net (VB or C#). The problem though, is that most data are not stored in Intersystem's CacheSQL, but rather in it's GLOBALS storage (which I think are arrays).

A GLOBALS array looks like this:

    ^BACKTR("INDX","COMPANY",1,63543,5870)
    ^BACKTR("DATA","STATISTICS",5870)   =   "613"_$c(1)_"3503||0"_$c(1)_"82"_$c(1)_"1"_$c(1)_"49"_$c(1)_"1"_$c(1)_"1950"_$c(1)_"63543"_$c(1)_"11301"_$c(1)_"3"_$c(1)_"CORP-A1"_$c(1)_"1656.06"_$c(1)_"150"_$c(1)_"0"_$c(1)_"N"_$c(1)_"82.8"_$c(1)_"198.72"_$c(1)_"12.42"_$c(1)_"N"_$c(1)_"0"_$c(1)_"0"_$c(1)_"0"_$c(1)_"N"_$c(1)_"Y"_$c(1)_"1"

Is there a way to call Database GLOBALS in Intersystems cacheSQL to VB.net or C#?

I am Using CACHE2010

Answer 1

You can get direct access to you globals with Caché eXTreme and Globals API but it is available since 2013.1
Or you could define custom Storage for your globals in you 2010 version, and get SQL access to your data and use it through ODBC or any other ways. You can find a lot examples here, how to do so.

UPD: You may try this class as an example for access to your data.

Class malky.test Extends %Persistent [ StorageStrategy = NewStorage1 ]
{

Property ID As %Integer;

Index ID On ID [ IdKey, Unique ];

Property prop1 As %String;

Property prop2 As %String;

Property prop3 As %String;

Property prop4 As %String;

Property prop5 As %String;

Property prop6 As %String;

Property prop7 As %String;

Property prop8 As %String;

Property prop9 As %String;

Property prop10 As %String;

Property prop11 As %String;

Property prop12 As %String;

Property prop13 As %String;

Property prop14 As %String;

Property prop15 As %String;

Property prop16 As %String;

Property prop17 As %String;

Property prop18 As %String;

Property prop19 As %String;

Property prop20 As %String;

Property prop21 As %String;

Property prop22 As %String;

Property prop23 As %String;

Property prop24 As %String;

Property prop25 As %String;

<Storage name="NewStorage1">
<ExtentSize>100000</ExtentSize>
<SequenceNumber>28</SequenceNumber>
<SQLMap name="Map1">
<Data name="prop1">
<Delimiter>$c(1)</Delimiter>
<Piece>1</Piece>
</Data>
<Data name="prop10">
<Delimiter>$c(1)</Delimiter>
<Piece>10</Piece>
</Data>
<Data name="prop11">
<Delimiter>$c(1)</Delimiter>
<Piece>11</Piece>
</Data>
<Data name="prop12">
<Delimiter>$c(1)</Delimiter>
<Piece>12</Piece>
</Data>
<Data name="prop13">
<Delimiter>$c(1)</Delimiter>
<Piece>13</Piece>
</Data>
<Data name="prop14">
<Delimiter>$c(1)</Delimiter>
<Piece>14</Piece>
</Data>
<Data name="prop15">
<Delimiter>$c(1)</Delimiter>
<Piece>15</Piece>
</Data>
<Data name="prop16">
<Delimiter>$c(1)</Delimiter>
<Piece>16</Piece>
</Data>
<Data name="prop17">
<Delimiter>$c(1)</Delimiter>
<Piece>17</Piece>
</Data>
<Data name="prop18">
<Delimiter>$c(1)</Delimiter>
<Piece>18</Piece>
</Data>
<Data name="prop19">
<Delimiter>$c(1)</Delimiter>
<Piece>19</Piece>
</Data>
<Data name="prop2">
<Delimiter>$c(1)</Delimiter>
<Piece>2</Piece>
</Data>
<Data name="prop20">
<Delimiter>$c(1)</Delimiter>
<Piece>20</Piece>
</Data>
<Data name="prop21">
<Delimiter>$c(1)</Delimiter>
<Piece>21</Piece>
</Data>
<Data name="prop22">
<Delimiter>$c(1)</Delimiter>
<Piece>22</Piece>
</Data>
<Data name="prop23">
<Delimiter>$c(1)</Delimiter>
<Piece>23</Piece>
</Data>
<Data name="prop24">
<Delimiter>$c(1)</Delimiter>
<Piece>24</Piece>
</Data>
<Data name="prop25">
<Delimiter>$c(1)</Delimiter>
<Piece>25</Piece>
</Data>
<Data name="prop3">
<Delimiter>$c(1)</Delimiter>
<Piece>3</Piece>
</Data>
<Data name="prop4">
<Delimiter>$c(1)</Delimiter>
<Piece>4</Piece>
</Data>
<Data name="prop5">
<Delimiter>$c(1)</Delimiter>
<Piece>5</Piece>
</Data>
<Data name="prop6">
<Delimiter>$c(1)</Delimiter>
<Piece>6</Piece>
</Data>
<Data name="prop7">
<Delimiter>$c(1)</Delimiter>
<Piece>7</Piece>
</Data>
<Data name="prop8">
<Delimiter>$c(1)</Delimiter>
<Piece>8</Piece>
</Data>
<Data name="prop9">
<Delimiter>$c(1)</Delimiter>
<Piece>9</Piece>
</Data>
<Global>^BACKTR</Global>
<RowIdSpec name="1">
<Field>ID</Field>
</RowIdSpec>
<Subscript name="1">
<Expression>"DATA"</Expression>
</Subscript>
<Subscript name="2">
<Expression>"STATISTICS"</Expression>
</Subscript>
<Subscript name="3">
<Expression>{ID}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^malky.TableS</StreamLocation>
<Type>%CacheSQLStorage</Type>
</Storage>
}

And result will looks like on a picture