1

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

Malcolm Salvador
  • 1,476
  • 2
  • 21
  • 40

1 Answers1

2

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 enter image description here

DAiMor
  • 3,185
  • 16
  • 24
  • thanks, but can the Cache eXTreme library be used for Cache 2010? – Malcolm Salvador Mar 22 '16 at 07:19
  • I think no, server side also should support it as I think. But any way, you can try, and you can get latest version of Caché in [WRC](http://wrc.intersystems.com/). – DAiMor Mar 22 '16 at 07:44
  • Sir, do you have a direct example of how I can tun a GLOBAL into a custom storage and query to ODBC – Malcolm Salvador Mar 22 '16 at 08:21
  • It is depends on your data, and you have to know what every part of values actually mean. As I see you have delimited value, and We should define properties for all of them, and defined it in a storage too. – DAiMor Mar 22 '16 at 08:37
  • Well, I added example – DAiMor Mar 22 '16 at 09:38
  • Thank you, will try this example and get back to you as soon as I can – Malcolm Salvador Mar 23 '16 at 02:03
  • The problem though.. is that you as a developer have to manually do this to all your globals, correct? how can I do this class generation programatically? – Malcolm Salvador Mar 23 '16 at 04:19
  • While you create new class for new data, it will be done programmatically. But in this case, only who knows how it stores can do so. It is possible make such custom storage programmatically, all classes can be generated. – DAiMor Mar 23 '16 at 04:23
  • Hi DAiMor, how about if I use the VisM control? I have access to it via adding it as reference and including it in my toolbox as an activeX control. Will this work? – Malcolm Salvador Mar 26 '16 at 08:06
  • I'm not so familiar with VisM, but I'm sure that's possible too. – DAiMor Mar 26 '16 at 10:17