TM1 API in Visual Basic 2008 Express Edition

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

TM1 API in Visual Basic 2008 Express Edition

Post by Wim Gielis »

Hello all

I have a question regarding TM1 API when used in Visual Basic 2008 Express Edition.

I had a working piece of code to log in, which works perfectly in Excel VBA (Excel 2003).

My TM1 server runs as a service, to which I connect. It's all locally on my laptop.

Below is the code that works up until the function TM1SystemServerConnect. Then my .exe file crashes.

The arguments to TM1SystemServerConnect are all nice Long variables.

I also copied the module with TM1 API functions.

Anyone has a clue? Thanks a lot in advance!

Code: Select all

Module TM1_main

    Public hUser As Long
    Public pGeneral As Long
    Public voDatabase As Long

    Public hServer As Long
    Public vServerName As Long
    Public vClientName As Long
    Public vClientPassword As Long

    Dim strMachine As String

    <VBFixedString(75)> Dim strTM1server As String
    <VBFixedString(75)> Dim strUser As String
    <VBFixedString(75)> Dim strPassword As String
    <VBFixedString(75)> Dim sElementName As String

    Public Sub Update_TM1()

        Dim str As String

        'connect to TM1
        str = Connect()

        MessageBox.Show(str)

    End Sub

    Private Function Connect() As String

        '--------------------------------------------------------------------------------
        'CONSTANTS
        strMachine = "wigipc"
        strTM1server = "bv"
        strUser = "wim"
        strPassword = "blahblahblah"
        '--------------------------------------------------------------------------------

        ' initialize the API
        TM1APIInitialize()
        hUser = TM1SystemOpen()

        'Set the Admin Host Server Name
        TM1SystemAdminHostSet(hUser, strMachine)

        'Create a Pool Handle
        pGeneral = TM1ValPoolCreate(hUser)

        'Establish Login information
        vServerName = TM1ValString(pGeneral, strTM1server.Trim, 0)
        vClientName = TM1ValString(pGeneral, strUser.Trim, 0)
        vClientPassword = TM1ValString(pGeneral, strPassword.Trim, 0)

        'Log in to the TM1 Server
        hServer = TM1SystemServerConnect(pGeneral, vServerName, vClientName, vClientPassword)

        '''ERROR ==> CRASH!

        'Was login succesful?
        If TM1ValType(hUser, hServer) = TM1ValTypeError() Then
            Connect = "The server handle is an error code." & vbCrLf & "Please verify TM1 Servername, User and Password"
        Else
            Connect = "Connection succesful"
        End If

    End Function

End Module
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: TM1 API in Visual Basic 2008 Express Edition

Post by Mike Cowie »

Hi Wim,

I see a couple possible issues here.

1: Long in VB6/VBA and Long in VB.NET are not the same. A Long in VB6/VBA is a 32-bit integer whereas in VB.NET is a 64-bit integer. I would first suggest using Integer instead of Long in any code that you brought over from VBA. This is most likely the reason behind your crashing problem.

2: The password parameter, I believe, needs to be an encrypted string. To do this, you just need to call a different function:
vClientPassword = TM1ValStringEncrypt(pGeneral, strPassword.Trim, 0)
This may not matter in terms of crashing, but I believe if you don't use this your password would travel to the TM1 Server unencrypted.

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 API in Visual Basic 2008 Express Edition

Post by Wim Gielis »

Thanks Mike, the results will follow later today or so.

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 API in Visual Basic 2008 Express Edition

Post by Wim Gielis »

Hello

If I use Integer's or Short's, I get overflow errors: Arithmetic operation resulted in an overflow.

As thought correctly, the encrypted password does not help w.r.t. the crashes.

I will keep on searching, if anyone else has an idea... welcome.

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: TM1 API in Visual Basic 2008 Express Edition

Post by Mike Cowie »

Wim,

Did you also change your Tm1 API function declarations? You need to change those from Long to Integer as well. You're probably getting overflow errors because those functions are returning Long's to your Integers.

-Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 API in Visual Basic 2008 Express Edition

Post by Wim Gielis »

Good idea, I will try so.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 API in Visual Basic 2008 Express Edition

Post by Wim Gielis »

Connection successful... :D

For others with the same issue... replace Long variables with Integers. In your actual code, but also in the module where you have the list of TM1 API calls. All arguments and returned values should be Integers instead of Long.

Good work. Thanks a bunch, I got it now.

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply