Page 2 of 3
Posted: Mon Jun 29, 2009 1:12 pm
by srod
Well, COMatePLUS should definitely display the error message then; the debugger is getting that message from COMate itself! I suspect you are misusing COMate a little bit.
Place a Debug COMate_GetLastErrorDescription() after each method / property call then.
If the problem is the objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") line then I suspect that the server wants an indirect pointer to the iDispatch object. If this is the case then we will need to modify the code - but first you need to confirm that this is the problem line (with some Debug COMate_GetLastErrorDescription() lines)!
My OLE viewer tool tells me that we definitely need to be using SetPropertyRef() here.
Code: Select all
IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"
Define.COMateObject objMDX, objADOMD
objMDX=COMate_CreateObject("ADODB.Connection")
If objMDX
objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog
If objADOMD
vCube.s = "zxCube"
vCompany.s = "[Company].[Company 200].[Level 1].&[200]"
vCurrency.s = "[Currency].[Currencies].[EUR]"
vCustomer.s = "[Customer].[Customers].[All]"
vDatasource.s = "[DataSource].[DataSources].[DMC]"
vGCN.s = "NET"
vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]"
vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]"
vPeriod.s = "P4"
vYear.s = "2009"
vForeCastType.s = "1"
mdx.s = "with "
mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) "
mdx.s = mdx.s + "select [Act1] on columns, [Account].[Accounts].[304] on rows from "
mdx.s = mdx.s + "( select {" + vCompany + "} on columns from "
mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from "
mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from "
mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from "
mdx.s = mdx.s + "( select " + vDatasource + " on columns from "
mdx.s = mdx.s + "( select " + vCurrency + " on columns from "
mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from "
mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from "
mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value "
If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
objADOMD\SetProperty("Source = '"+mdx+"'")
Debug COMate_GetLastErrorDescription()
objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject")
Debug COMate_GetLastErrorDescription()
objADOMD\Invoke("Open")
Debug COMate_GetLastErrorDescription()
Debug objADOMD\GetIntegerProperty("(0,0)")
Else
Debug COMate_GetLastErrorDescription()
EndIf
objADOMD\Release()
Else
Debug "Could not instantiate the ADOMD.Cellset object"
EndIf
objMDX\Release()
Else
Debug "ADODB.Connection"
EndIf
Posted: Mon Jun 29, 2009 1:27 pm
by srod
Aha.... change the following line
to
Code: Select all
objADOMD\Invoke("Open(#Optional, #Optional)")
Try that!
I'll get this damn thing working if I have to chew my way through my coffee table first!
Posted: Mon Jun 29, 2009 1:46 pm
by CNESM
I slim the code a little bit with the COMate_GetLastErrorDescription:
Code: Select all
Define.COMateObject objMDX, objADOMD
objMDX=COMate_CreateObject("ADODB.Connection")
If objMDX
objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog
If objADOMD
mdx.s = "with "
mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 2009])"
mdx.s = mdx.s + "select [Act1] on columns, [Account].[Accounts].[304] on rows from"
mdx.s = mdx.s + "(select [Company].[Company 200].[Level 1].&[200] on columns from"
mdx.s = mdx.s + "(select [PRODLINE].[ProdLines].[Level 3].&[3] on columns from"
mdx.s = mdx.s + "(select [Customer].[Customers].[All] on columns from"
mdx.s = mdx.s + "(select [METALTYPE].[METALTYPES].[ALL] on columns from"
mdx.s = mdx.s + "(select [DataSource].[DataSources].[DMC] on columns from"
mdx.s = mdx.s + "(select [Currency].[Currencies].[EUR] on columns from"
mdx.s = mdx.s + "(select [GCN].[GCN].[NET] on columns from"
mdx.s = mdx.s + "(select [TCurrency].[Switch].[TCurrency] on columns from"
mdx.s = mdx.s + "[FIBI WCH])))))))) cell properties value"
If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
objADOMD\SetProperty("Source = '"+mdx+"'")
Debug COMate_GetLastErrorDescription()
objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject")
Debug COMate_GetLastErrorDescription()
objADOMD\Invoke("Open(#Optional, #Optional)")
Debug COMate_GetLastErrorDescription()
Debug objADOMD\GetIntegerProperty("(0,0)")
Debug COMate_GetLastErrorDescription()
Else
Debug COMate_GetLastErrorDescription()
EndIf
objADOMD\Release()
Else
Debug "Could not instantiate the ADOMD.Cellset object"
EndIf
objMDX\Release()
Else
Debug "ADODB.Connection"
EndIf
I get back following debugs:
Okay.
Okay.
Okay.
0
One or more arguments are invalid. Possibly a numerical overflow or too many nested objects, -if so, try splitting your method call into two or more subcalls.
Well, COMatePLUS should definitely display the error message then; the debugger is getting that message from COMate itself! I suspect you are misusing COMate a little bit.
If i disable first line error message on the MDX BI Server, COMate give back only "Okey." after the last GetLastErrorDescription function. if i activate this options, the debugger gave me the error "One or more arguments are invalid. Possibly a numerical overflow or too many nested objects, -if so, try splitting your method call into two or more subcalls." etc.
Posted: Mon Jun 29, 2009 1:53 pm
by srod
The problem is :
Code: Select all
objADOMD\GetIntegerProperty("(0,0)")
There is no property name here!
I don't know enough about ADOMD, but there must be additional objects we need to retrieve in order to retrieve the data etc. I'll have a look around - very difficult though when I cannot test.
**EDIT : yes you need to trawl through the result-set using various objects and (possibly) safearrays. The VBA code you posted does a lot of it in the background.
http://support.microsoft.com/kb/828279
Posted: Mon Jun 29, 2009 2:13 pm
by srod
Try the following code to display the various 'axis'. Does anything of sense get displayed? If so, then it is indeed the case that you have to take this kind of approach to get the data you are after and we are on the right lines!
Code: Select all
IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"
Define.COMateObject objMDX, objADOMD
Define.COMateEnumObject enAxes
Define.COmateObject obAxis
objMDX=COMate_CreateObject("ADODB.Connection")
If objMDX
objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog
If objADOMD
vCube.s = "zxCube"
vCompany.s = "[Company].[Company 200].[Level 1].&[200]"
vCurrency.s = "[Currency].[Currencies].[EUR]"
vCustomer.s = "[Customer].[Customers].[All]"
vDatasource.s = "[DataSource].[DataSources].[DMC]"
vGCN.s = "NET"
vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]"
vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]"
vPeriod.s = "P4"
vYear.s = "2009"
vForeCastType.s = "1"
mdx.s = "with "
mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) "
mdx.s = mdx.s + "select [Act1] on columns, [Account].[Accounts].[304] on rows from "
mdx.s = mdx.s + "( select {" + vCompany + "} on columns from "
mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from "
mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from "
mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from "
mdx.s = mdx.s + "( select " + vDatasource + " on columns from "
mdx.s = mdx.s + "( select " + vCurrency + " on columns from "
mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from "
mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from "
mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value "
If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
objADOMD\SetProperty("Source = '"+mdx+"'")
objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject")
If objADOMD\Invoke("Open(#Optional, #Optional)") = #S_OK
enAxes = objADOMD\CreateEnumeration("Axes")
If enAxes
obAxis = enAxes\GetNextObject()
While obAxis
Debug obAxis\GetStringProperty("Name")
Debug "===="
obAxis\Release()
obAxis = enAxes\GetNextObject()
Wend
enAxes \Release()
EndIf
EndIf
Else
Debug COMate_GetLastErrorDescription()
EndIf
objADOMD\Release()
Else
Debug "Could not instantiate the ADOMD.Cellset object"
EndIf
objMDX\Release()
Else
Debug "ADODB.Connection"
EndIf
If this works then I think we can get the 'cell data'.
Posted: Mon Jun 29, 2009 2:26 pm
by CNESM
Hi srod
The code debuggs following data:
Posted: Mon Jun 29, 2009 2:31 pm
by srod
Excellent - we are on the right path then.
The problem all along has been this "(0, 0)" business!
I think with this, VBA generates an array and retrieves all the data accordingly. We of course have to do it by hand!
Let me see if I can find some code for retrieving the cell data; complicated because I cannot run the code. I may simply point you towards some code to convert yourself etc.
Posted: Mon Jun 29, 2009 2:37 pm
by CNESM
Maybe this help:
Code: Select all
If oCompCn.State = ADODB.ObjectStateEnum.adStateClosed Then
oCompCn.Open "Provider=" & sPROVIDER & ";Data Source=" & sSERVER & ";Initial Catalog=" & sINITIALCATALOG & ";"
End If
Set cst = New ADOMD.Cellset
cst.Open mdx, oCompCn
rows = cst.Axes(1).Positions.Count - 1
cols = cst.Axes(0).Positions.Count - 1
ReDim ar(1 To rows + 3, 1 To cols + 2)
Set sh = Sheets("Data")
sh.Cells.Clear
For i = 0 To rows
For k = 0 To cols
ar(i + 3, k + 2) = cst(k, i)
Next
Next
For i = 0 To rows
ar(i + 3, 1) = cst.Axes(1).Positions(i).Members(0).Caption
Next
For k = 0 To cols
ar(1, k + 2) = cst.Axes(0).Positions(k).Members(0).Caption
Next
cl = sh.Cells(rows + 3, cols + 2).Address
sh.Cells.Range("A1:" & cl).Value = ar
This is a vba code, that i found online.
Posted: Mon Jun 29, 2009 2:44 pm
by srod
Its the "cst(k, i)" which again refers to an array..... ah, I see how to do it. We need to package up the index (row, column) into a safe array and then pass that to the "get_Item" property. This then returns a cell object from which we can get the required value - yes it all makes sense now.
I'll need to roll my Safearray sleeves up.... God I hate safearrays! grrrr........
Posted: Mon Jun 29, 2009 3:00 pm
by CNESM
I think you are not wrong with you answer!
We had to go on this way:
Code: Select all
IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"
Define.COMateObject objMDX, objADOMD
Define.COMateEnumObject enAxes
Define.COmateObject obAxis
objMDX=COMate_CreateObject("ADODB.Connection")
If objMDX
objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog
If objADOMD
vCube.s = "zxCube"
vCompany.s = "[Company].[Company 200].[Level 1].&[200]"
vCurrency.s = "[Currency].[Currencies].[EUR]"
vCustomer.s = "[Customer].[Customers].[All]"
vDatasource.s = "[DataSource].[DataSources].[DMC]"
vGCN.s = "NET"
vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]"
vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]"
vPeriod.s = "P4"
vYear.s = "2009"
vForeCastType.s = "1"
mdx.s = "with "
mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) "
mdx.s = mdx.s + "select [Act1] on columns, [Account].[Accounts].[304] on rows from "
mdx.s = mdx.s + "( select {" + vCompany + "} on columns from "
mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from "
mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from "
mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from "
mdx.s = mdx.s + "( select " + vDatasource + " on columns from "
mdx.s = mdx.s + "( select " + vCurrency + " on columns from "
mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from "
mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from "
mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value "
If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
objADOMD\SetProperty("Source = '"+mdx+"'")
objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject")
If objADOMD\Invoke("Open(#Optional, #Optional)") = #S_OK
enAxes = objADOMD\CreateEnumeration("Axes")
obAxis = enAxes\GetNextObject()
obAxis1 = enAxes\GetNextObject()
Debug obAxis\GetStringProperty("Name")
Debug obAxis1\GetStringProperty("Name")
rows = obAxis\GetIntegerProperty("Positions\Count")
cols = obAxis1\GetIntegerProperty("Positions\Count")
Debug rows
Debug cols
rows = obAxis\GetIntegerProperty("Positions(1)\Members(0)\Caption")
cols = obAxis1\GetIntegerProperty("Positions(1)\Members(0)\Caption")
EndIf
Else
Debug COMate_GetLastErrorDescription()
EndIf
objADOMD\Release()
Else
Debug "Could not instantiate the ADOMD.Cellset object"
EndIf
objMDX\Release()
Else
Debug "ADODB.Connection"
EndIf
The debugger give back:
God I hate safearrays! grrrr........
Met too, srod, me too
Posted: Mon Jun 29, 2009 3:05 pm
by srod
Nearly there...... few more minutes...
Posted: Mon Jun 29, 2009 3:14 pm
by srod
Try the following which is hard-coded for a 2-dimensional recordset. I try and retrieve the data in cell (1, 1).
Code: Select all
IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"
;-Safe array structures - taken from "VariantHelper_Include.pb"
;===============================================================================
Structure SAFEARRAYBOUND
cElements.l
lLbound.l
EndStructure
Structure SAFEARRAY
cDims.w
fFeatures.w
cbElements.l
cLocks.l
*pvData.pData
rgsabound.SAFEARRAYBOUND[0]
EndStructure
;===============================================================================
;-Set up a 1-d safearray of signed 16-bit integers with two elements (col, row).
;===============================================================================
;Begin with an array of SAFEARRAYBOUND structures; one for each dimension.
Dim safeArrayBound.SAFEARRAYBOUND(1)
With safeArrayBound(0)
\lLbound = 0
\cElements = 2
EndWith
;Now create the array and check for success.
*safeArray.SAFEARRAY = SafeArrayCreate_(#VT_I2, 1, @safeArrayBound())
If *safeArray = 0
MessageRequester("COMatePLUS!", "Couldn't create the Safearray!")
End
EndIf
;===============================================================================
;Populate the array to point to cell (1, 1) in the 'Cellset' recordset object.
;===============================================================================
index = 0
column = 1
SafeArrayPutElement_(*safeArray, @index, @column)
index = 1
row = 1
SafeArrayPutElement_(*safeArray, @index, @row)
;Bundle the Safearray up into a variant suitable for passing to a COM method.
Define var.VARIANT
With var
\vt = #VT_ARRAY|#VT_I2
\parray = *safeArray
EndWith
;===============================================================================
Define.COMateObject objMDX, objADOMD, obAxis, obCell
Define.COMateEnumObject enAxes
objMDX=COMate_CreateObject("ADODB.Connection")
If objMDX
objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog
If objADOMD
vCube.s = "zxCube"
vCompany.s = "[Company].[Company 200].[Level 1].&[200]"
vCurrency.s = "[Currency].[Currencies].[EUR]"
vCustomer.s = "[Customer].[Customers].[All]"
vDatasource.s = "[DataSource].[DataSources].[DMC]"
vGCN.s = "NET"
vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]"
vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]"
vPeriod.s = "P4"
vYear.s = "2009"
vForeCastType.s = "1"
mdx.s = "with "
mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) "
mdx.s = mdx.s + "select [Act1] on columns, [Account].[Accounts].[304] on rows from "
mdx.s = mdx.s + "( select {" + vCompany + "} on columns from "
mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from "
mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from "
mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from "
mdx.s = mdx.s + "( select " + vDatasource + " on columns from "
mdx.s = mdx.s + "( select " + vCurrency + " on columns from "
mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from "
mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from "
mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value "
If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
objADOMD\SetProperty("Source = '"+mdx+"'")
objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject")
If objADOMD\Invoke("Open(#Optional, #Optional)") = #S_OK
numCells = 1
enAxes = objADOMD\CreateEnumeration("Axes")
If enAxes
obAxis = enAxes\GetNextObject()
While obAxis
numCells * obAxis\GetIntegerProperty("Positions.Count")
Debug obAxis\GetStringProperty("Name")
Debug "===="
obAxis\Release()
obAxis = enAxes\GetNextObject()
Wend
enAxes \Release()
Debug "There were " + Str(numCells) + " cell(s) retrieved."
Debug "===="
;Now get some cell data! We use cell (1, 1).
obCell = objADOMD\GetObjectProperty("Item(" + Str(var) + " as VARIANT)")
If obCell
Debug "Cell (1, 1) as string : " + obCell\GetStringProperty("FormattedValue")
Else
Debug "Problem getting cell object!"
Debug COMate_GetLastErrorDescription()
EndIf
EndIf
EndIf
Else
Debug COMate_GetLastErrorDescription()
EndIf
objADOMD\Release()
Else
Debug "Could not instantiate the ADOMD.Cellset object"
EndIf
objMDX\Release()
Else
Debug "ADODB.Connection"
EndIf
;Now free the Safearray. Either use SafeArrayDestroy_() or VariantClear_() on any variant containing the array.
VariantClear_(var)
Posted: Mon Jun 29, 2009 3:20 pm
by CNESM
You wroght this code in this few minutes?! Wooowo
Debugger:
Code: Select all
Axis(0)
====
Axis(1)
====
There were 0 cell(s) retrieved.
====
Problem getting cell object!
The arguments are of the wrong type, lie outside of the range of validity or are incompatibly
Posted: Mon Jun 29, 2009 3:22 pm
by srod
Well, that basically says that there is no data being returned from your SQL statement. You'd better check that carefully.
Posted: Mon Jun 29, 2009 3:23 pm
by CNESM
You had an error in you code:
numCells * obAxis\GetIntegerProperty("Positions.Count")
to
numCells * obAxis\GetIntegerProperty("Positions\Count")
Debugger:
Axis(0)
====
Axis(1)
====
There were 1 cell(s) retrieved.
====
Problem getting cell object!
The arguments are of the wrong type, lie outside of the range of validity or are incompatibly