The TM1 API - VBA

[nhavis]

Has anyone found it very hard to debug code that makes use of the API?

Has anyone discovered that the Visual Basic section of the API in the help file seems to be a copied+edited version of the C section with a lot of inconsistencies (eg. code that works in C and not in VBA)?

Here's my current trouble: I'm simply trying to grab data out of one cell:
Cube: grid
Dimension1: x Element: 0
Dimension2: y Element: 0

The problem arises towards the end as I check the result of this call with TM1ValType and find that it returns 6 (The error code).
TM1CubeCellValueGet(pPoolHandle, hCubeObject, elementArrayCapsule)

Sub Connect_TM1_Database()

Dim sServerName As String
Dim sUsername As String
Dim sPassword As String
Dim hUserHandle As Long
Dim pPoolHandle As Long
Dim vPassword, vServerName, vUserName As Long
Dim vStringLength As Long
Dim RetVal As String * 75

Dim hCubeObject As Long
Dim vCell As Long
Dim hDimension1 As Long
Dim hDimension2 As Long
Dim vArrayLength As Long
Dim vZero As Long
Dim vElement1 As Long
Dim vElement2 As Long

'
' Each Visual Basic application begins by calling TM1APIInitialize,
' TM1SystemAdminServerHostSet, and TM1SystemServerConnect.
'

TM1APIInitialize

hUser = TM1SystemOpen()
TM1SystemAdminHostSet hUser, ""

pPoolHandle = TM1ValPoolCreate(hUser)

'
' We have to take the strings containing the login information (such as the
' user name and password) and turn them into TM1 value capsules. First
' establish the maximum length of the string as 10 characters.
'
vStringLength = TM1ValIndex(pPoolHandle, 10)
vArrayLength = TM1ValIndex(pPoolHandle, 2)

'
' Next, use this string length to build value capsules for the
' user name, password, and TM1Server name. We can reuse the pool handle
' for these functions.
'
vUserName = TM1ValString(pPoolHandle, "admin", vStringLength)
vPassword = TM1ValString(pPoolHandle, "", vStringLength)
vServerName = TM1ValString(pPoolHandle, "utopia", vStringLength)

vServerHandle = TM1SystemServerConnect(pPoolHandle, vServerName, vUserName, vPassword)

Dim numberOfCubes As Integer
Dim CubeCount As Long
CubeCount = TM1ObjectListCountGet(pPoolHandle, vServerHandle, TM1ServerCubes())
numberOfCubes = TM1ValIndexGet(hUser, CubeCount)
Debug.Print numberOfCubes

ReDim elementArray(2) As Long
' vZero = TM1ValIndex(pPoolHandle, 0)
' Open up dimension x
hDimension1 = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, "x", 0))
vElement1 = TM1ObjectListHandleByNameGet(pPoolHandle, hDimension1, TM1DimensionElements(), TM1ValString(pPoolHandle, "0", 0))

hDimension2 = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, "y", 0))
vElement2 = TM1ObjectListHandleByNameGet(pPoolHandle, hDimension2, TM1DimensionElements(), TM1ValString(pPoolHandle, "0", 0))

Dim retval2 As String * 10
Dim vVar2 As Variant

elementArray(1) = vElement1
elementArray(2) = vElement2
'
' vVar2 = TM1ObjectPropertyGet(pPoolHandle, vElement1, TM1ObjectName())
' MsgBox TM1ValType(hUser, vVar2)
' Call TM1ValStringGet_VB(hUser, vVar2, retval2, 1)
' MsgBox retval2 & "."


Dim elementArrayCapsule As Long

elementArrayCapsule = TM1ValArray(pPoolHandle, elementArray, 2)

hCubeObject = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerCubes(), TM1ValString(pPoolHandle, "grid", 0))
Dim something As Variant
something = TM1CubeCellValueGet(pPoolHandle, hCubeObject, elementArrayCapsule)
MsgBox TM1ValType(hUser, something)
'
' To log out and disconnect from the API, you must
' call TM1SystemServerDisconnect, TM1SystemClose, then TM1APIFinalize.
'
' In addition, best practice dictates that all TM1 Value Pools used
' in your program be destroyed by calling TM1ValPoolDestroy().
'
vResult = TM1SystemServerDisconnect(pPoolHandle, vServerName)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add Code to delete all TM1 Value Pools here.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
TM1ValPoolDestroy (pPoolHandle)

TM1SystemClose hUser
TM1APIFinalize

End Sub

[nhavis]

What am I suppose to use to get the integer out of a "TM1 value capsule containing an integer"?
There certainly isn't a TM1ValIntegerGet()


TM1ValIndexGet
Purpose Retrieves the contents of an index value capsule.
Definition Declare Function TM1ValIndexGet Lib "tm1api.dll" (ByVal hUser As Long, ByVal vIndex As Long) As Long
Parameters hUser is a long. It is a valid user handle obtained with “Integrated Login”.
vIndex is a long. This is the value capsule whose contents is to be retrieved.
Result The function returns a long integer. This long is a handle to a TM1 value capsule containing an integer.
If the function returns 0, the operation was not successful. The function returns the index contents of the value capsule.
Security None.
Errors None.
See Also Other TM1ValIndex functions.

[Alan Kirk]

What am I suppose to use to get the integer out of a "TM1 value capsule containing an integer"?
There certainly isn't a TM1ValIntegerGet()


TM1ValIndexGet
Purpose Retrieves the contents of an index value capsule.
Definition Declare Function TM1ValIndexGet Lib "tm1api.dll" (ByVal hUser As Long, ByVal vIndex As Long) As Long
Parameters hUser is a long. It is a valid user handle obtained with “Integrated Login”.
vIndex is a long. This is the value capsule whose contents is to be retrieved.
Result The function returns a long integer. This long is a handle to a TM1 value capsule containing an integer.
If the function returns 0, the operation was not successful. The function returns the index contents of the value capsule.
Security None.
Errors None.
See Also Other TM1ValIndex functions.

I'd like to be able to be of more help to you on this but unfortunately I'm pressed for time at the moment and it's been a while since I've done any API coding. And of course the API is as easy to remember as the grammar of Sumerian, so...

Anyway, I found an instance of using that function in my own code. I was able to read the number concerned directly from the return value:

Code: Select all

    
l_Result = TM1ObjectListCountGet(mhtm1p_PoolGeneral, xh_Cube, _
     TM1CubeDimensions())
    
xl_DimCount = TM1ValIndexGet(mhtm1u_User, l_Result)

That was used to get the number of dimensions in the cube (to which I had a handle via xh_Cube, obtained via the TM1ObjectListHandleByNameGet function), mhtm1p_PoolGeneral was obtained by TM1ValPoolCreate, mhtm1u_User was obtained from TM1SystemOpen.

If I have some more time later today I'll take a look at your original question, but hopefully someone else will be along before then.

Hope this turns out to be of some help at least...

[nhavis]

I found some code (The samples provided by Cognos) that made use of the function TM1CellValueSet which is essentially the same in terms of arguments:

It turns out that I needed to make these two calls in addition to everything else -
Call TM1ValArraySet(elementArrayCapsule, elementArray(1), 1)
Call TM1ValArraySet(elementArrayCapsule, elementArray(2), 2)

Alan: Thanks Alan, that's what I initially thought however came over some troubles when debugging with that function.

[Alan Kirk]

Alan: Thanks Alan, that's what I initially thought however came over some troubles when debugging with that function.

I don't blame you. I'm fairly anal about putting error handling code into my own code, but unfortunately when you're working with the TM1 API it doesn't help. The most common form of debugging (the type that will tell you if you've passed an invalid argument) is "run the code until Excel crashes on you, and hope that you've saved your code beforehand".

[nhavis]

That's exactly what I've been having to do...

Do you know anything about retrieving the name of say a dimension object with TM1ObjectPropertyGet and TM1ValueStringGet_VB?
When I don't know the exact length of the name (practically always), the TM1ValueStringGet_VB function is returning the entire allocated space (which includes jargon on the end).

I previously looked at code examples from Cognos (In the API folder) and their output actually includes rubbish aswell (they must have released it knowing so).

[nhavis]

Here is the picture of output of sample code provided with the API.

As you can see - left over characters are still on the string.

A solution is to ReDim the String, however the proper length must be known.

Does the API have a method for getting the length of the string it gives with TM1ObjectPropertyGet( , ,ObjectName()) and TM1ValStringGet_VB()?

[Alan Kirk]

Here is the picture of output of sample code provided with the API.

As you can see - left over characters are still on the string.

A solution is to ReDim the String, however the proper length must be known.

No it doesn't; the returned string will just have null characters at the end (chr(0) characters) (possibly just one, possibly padded out, I can't recall which), so you just set it for the maximum length that you expect.

You can blow away the string variable's value in between calls so that you don't have those "leftovers"

I just set my call to TM1ValStringGet_VB to use a final argument of 255 then trim the fool using my TrimNull function.

[nhavis]

Take a look at the screenshot I posted - the strings are _not_ terminated with a null.

For example: cube1 is appearing as cube1ings_Bonuses

[Alan Kirk]

Take a look at the screenshot I posted - the strings are _not_ terminated with a null.

Really? And null characters are always visible now?

[nhavis]

If my strings were null terminated then 'cube1ings_Bonuses' would appear as 'cube1 ings_Bonuses'.

[Alan Kirk]

Take a look at the screenshot I posted - the strings are _not_ terminated with a null.

For example: cube1 is appearing as cube1ings_Bonuses

I don't see that example in the screenshot, but from the looks of it you simply aren't clearing the values of your variables during the course of a loop and ending up with shorter names overwriting only the first few characters of the earlier, longer names. Reset the variable to empty before each call, and you shouldn't have that problem.

As for the absence or presence of nulls, I was going to demonstrate an example of what gets returned, but I can't run the actual code again until tomorrow and I don't have time to put together demonstration code. Except this. The procedure shown in the attached graphic was run twice.

Do you see any null characters? No, because they don't show up in the Immediate window.

Are they there? If the code itself doesn't convince you, you may wonder what all that empty space at the end of the first output line is.

They're there. And you use them to trim the return value, provided that you make sure that the return variable has a nice, blank space to write into.

[Alan Kirk]

If my strings were null terminated then 'cube1ings_Bonuses' would appear as 'cube1 ings_Bonuses'.

OK, fine, every C function (which is what the API effectively is) uses null terminated strings except when you call them.

Try clearing your variables between calls and see whether you get your "cube1ersses" value. You might be pleasantly surprised.

[nhavis]

I imagine this will fix things also...

Why is it that VBA will 'print' the null characters instead of just taking it as a string terminator?

Edit: Erm, is there a function to null out a string?

[Alan Kirk]

I imagine this will fix things also...

Why is it that VBA will 'print' the null characters instead of just taking it as a string terminator?

Because VBA and C string data types are different in nature. In fact, strictly and technically speaking, C doesn't actually HAVE a string data type; a string in C is a byte array which is terminated by a null byte. A VBA string variable has no such need of a terminator, and sees the null as just an extra character rather than as a terminator. A byte with an ASCII value of 0 has no special meaning to a VBA string, it's just another character.

Many API calls (in Windows, and I think but couldn't swear that this is the case with the TM1 API as well) work a little differently again in that you specify the number of characters that you expect the API function to return, and it returns exactly that many. Any characters which exceed the "real" value that the function returns will be nulls. HOWEVER that's merely padding; if you feed in (and again this may vary with some individual functions) a string which already HAS (say) 255 characters, then the function won't need to add any nulls to the end of it since the string is already as long as it needs to be.

[Alan Kirk]

Edit: Erm, is there a function to null out a string?

You don't need a function.

I found that I passed a fixed length (rather than dynamic length) string to TM1ValStringGet_VB; it's been too long since I wrote the code to recall whether that was a necessity or just something that I did for safety. However you can still assign an empty string to a fixed width string and have it effectively cleared. (In reality a fixed width string in VBA will also be padded, but by spaces rather than by nulls.) Certainly it will remove all of the content from the previous iteration.

For example:

Code: Select all

Sub ClearString()

Dim s_RV As String * 255

s_RV = "Blah blah blah"

Debug.Print s_RV

s_RV = ""

Debug.Print s_RV

End Sub

The fact that I used a string which was already 255 characters in size meant that mine probably didn't need null padding either (out of curiosity I'll check that when I run the program tomorrow), and the standard Trim() function was also part of my TrimNulls function which would get rid of the surplus spaces.

Edit: Obviously if you ARE using a dynamic string, assigning "" to it will also get rid of the contents.

[nhavis]

Regards.

[Alan Kirk]

The fact that I used a string which was already 255 characters in size meant that mine probably didn't need null padding either (out of curiosity I'll check that when I run the program tomorrow), and the standard Trim() function was also part of my TrimNulls function which would get rid of the surplus spaces.

This is a "just for the sake of completeness" thing in case anyone (possibly me, in which case "hi" to me) is looking up this thread at some future time...

The TM1ValStringGet_VB function did in fact pad out all of the passed string with null characters beyond the actual content. The content of the cell that was read was 10 characters long. (I've had to x out the returned characters in the screen shot for confidentiality reasons.) The first null character was found at position 11, and there was also a null at position 255; although I didn't test all of the characters in between, it's a pretty fair bet that they were all nulls as well. As previously noted they don't show up in the Immediate window, but you can see a whole bunch of little square "control code" characters when you look at the variable contents via a tooltip.