TM1 Forum

Part 1: Introduction

In this article I wrote about using the TM1 .Net API to connect to TM1. At the end of that I said that I may get around to doing one on using the classic (VB6/VBA/C) TM1 API within the .Net environment.

That time has come. I do however warn you that this is not "An Introduction To The Classic API"; that's a vastly bigger subject, too big for a single post or even thread. If you're reading this article I expect that you already know the ropes (or at least what the ropes look like) when it comes to value capsules and value pools and user handles and the like, and just want some guidance on how to translate your existing VB6 / VBA API code into a VB.Net environment. No, I am not going to do another article on C. I haven't written a line of C code for a couple of decades and don't plan to start again now, aside from which .Net does not have plain ol' C as a native language (as opposed to C#).

The one down side to my C aversion is in relation to the callback functions which report the progress of longer running TM1 API calls. The method of using these in C is described in the documentation, but there's an embarrassed silence with regard to VB6/VBA. I've used callback functions in VB6 / VBA in relation to the Windows API, but have never had a need to use them in the TM1 API so I'm (a) not certain how well / whether they work there and consequently (b) how (or whether) they would translate to the .Net environment even if they worked in VB6/VBA. If I ever feel a moment of boredom I may explore this and report back to the three readers that this thread is likely to have. (Hi Mike Cowie and whoever else is out there!)

I'll work on the assumption that your exposure to VB.Net is basic and explain pretty much all that you need to know on that subject as we go though again, it's not "An Introduction To VB.Net".

A Slight Off Topic Diversion
Please don't ask me to put up any of the source code for download (other than that given in the body of the text) as refusal may offend. I (and I think that this also applies to the other admins, MVPs and senior members) have no issue giving other people a hand with problems that they face, or lighting the right pathway for them. If we did we wouldn't be here. However we've all invested a lot of effort to get the skills that we have, we all have roofs to put over our head and tables to put food on, and in the majority of cases other people to keep fed, clothed, sheltered and cared for as well.

Giving someone else a helping hand is one thing, but when they want us to start supplying end to end solutions, or even the skeletons of such solutions, that's when it's time to start crossing our palms with silver for our work as Harlan Ellison expressed rather forcefully here. While it's not unknown for members of the TM1 community to do that in a spirit of generosity (TM1 Tools being one example, some freeware TM1 Top-based apps being others), one problem is that in some quarters this generosity has become an expectation of entitlement. (The chestnut chant of "Information wants to be free", which is in fact a euphemism for "I want to be paid for what I do, I just don't want to have to pay for what anybody else does", encapsulating this quite well.)

The other problem is the recent trend of certain businesses in certain parts of the world employing TM1 "specialists" who wouldn't know a TI loop from a hole in their head. These businesses then undercut consultants who really do have the required skills. Their "specialists" then have the barefaced gall to try to get, through forums such as this one, those same skilled and undercut professionals to help them do the work that they don't know how to do themselves... which seems to be most of it. (Which is even more choice when they then complain that they aren't getting sufficient support from the Forum.) This hurts our livelihoods in two ways; one by debasing the value of the market for real TM1 specialists, and second by damaging the reputation of TM1 in the business community. The aforementioned "specialists" will often deliver woeful "solutions" which those who employ them may well blame on TM1 rather than on the principle of "You sometimes get what you pay for... which in this case ain't much".

That doesn't mean that I (or most likely anyone else) will stop giving a hand when a hand is needed, or from releasing the occasional goodies as a freebie, but personally I'm finding it time to wind it back just a little bit. If I had made the decision to release a complete body of source code on this or any other thread... it would already be posted here.

And now back to the subject at hand.

The Background
VB6 was released in 1998, some 15 years ago when the computing world (and to some extent the world in general) was a very different place. It may still be used to maintain some legacy applications but it's unlikely to be used to develop new ones since it's been out of support for quite a while now.

PC application development has moved into more "managed" environments such as the Java Runtime Virus, er, I mean, Engine ("Write Once, Debug Everywhere") and the .Net Framework.

Unfortunately the TM1 APIs have been less than assiduous about keeping up with this trend. I don't know what if anything Iboglix has done with the Java API over the last few versions. And if the Java-based 10.1 applications (no, the other type of "applications") like Performance Muddler are any guide, I don't want to know. However I do know that they have left those of us who are from a VB background (which would probably be most TM1 developers via Excel) swinging in the wind. The TM1 .Net API is of course the Forrest Gump of APIs in that it can perform certain simple tasks well enough, but don't ask it to perform any administrative tasks. (In fact even retrieving a set of values from a view is more painful than I expected it to be.) It has seen pretty much no development since it was released, and is not showing any signs of that changing in the future.

Which brings us back to the only relatively full featured API (if you ignore the odd gaping hole), the classic one which was designed for VB6. The problem being, of course, that VB.Net is about as similar to VB6 as Jessica Alba is to Granny from the Beverly Hillbillies.

So let us begin our journey and find a way to shoehorn the old API into a new language.

Configuring The Client: The Path Pain Remains
Whichever way you create the project, you do still need to change the Path environment variable on the client computer to ensure that the application knows the way to the API libraries. (The manual approach: Right click on Computer, select Properties, then Advanced System Settings, then Environment Variables.) That's because IBM still can't be bothered doing this as part of the TM1 client installation process, despite having a shiny, bloaty new install package in 10.1. And yes, you should have the TM1 client software installed on the machine where your application will run. Without those libraries, it won't.

Yes, yes, I know, there are a couple of sneaky ways that you can avoid doing that. Don't do it. Remember that the TM1 client software is likely to change over the life of your application, and that IBM seems to move the default install path between versions almost on a whim; 3 times in the last 4 or 5 versions. Not to mention the fact that not all workplaces will install to the default path anyway. It's far better and safer to just point the thing to this week's correct library path using the Path variable.

You can of course include code to do this in your VB.Net app via the Environment.SetEnvironmentVariable method. However this presupposes that you actually know where the installation was done to, and as we shall see, you may not. (Which is why Iboglix should have included this in their own install package.)

The process for setting the path is almost on page 1 of the TM1 API manual, and I'd advise you to refer to that for guidance. I say "almost" given the claim (even in the 10.1 manual) that:

The following procedure assumes that you accepted the default settings during the installation, and that the IBM Cognos TM1 API .dll files are in C:\Cognos\TM1\bin.

Yyyyeah. I suspect that someone just "search and replace"d "Applix" with "Cognos" in this document. Oh, certainly, the libraries probably were installed in C:\Applix\TM1\bin, some time back around version 7.1 in the grand old nostalgic days of Windows 98. Hey look, a new episode of Seinfeld is on! If you're reading this article and haven't already fallen asleep, I have confidence in you being bright enough to figure out what you need to do based on the information provided in the documentation.

On a 32 bit Win 7 notebook using TM1 10.1 the libraries are really found in: Under 9.5.2 the path would have been the same except that there was no "ibm" folder above the "cognos" one. (Which means that any API applications that were written for 9.5.2 automatically broke on machines which were upgraded to 10.1 unless the path variable was also updated. Thanks for that one, IBM.)

On a 64 bit machine they're likely to be in a similar path to the one specified above but in the Program Files (x86) folder instead of the Program Files one.

Given the variability of the install location these days, you do need to plan for how you're going to update the Path when the user does an upgrade, including moving from 32 bit to 64 bit Windows. However this is no different to the situation that you'd face with a VB6 TM1 API application.

Update the Path on your machine (if you need to) before you go any further.

Part 2: Creating The Project

Creating The Project: The Kobyashi Maru Option
When I first attempted this exercise I decided not to create a completely new project directly in .Net. Instead I "cheated the system" and created a simple (and it had to be simple for reasons described below) TM1 API project in VB6, and then used the VB6 upgrade wizard to convert it to .Net. In this way I was hoping that any heavy lifting involving the creation of nasty, esoteric interop code would be handled automagically.

Fortunately, for plain, vanilla TM1 API content there isn't any such code. (If your code uses API callback functions or has some sexy interaction with Office applications it may be a different matter.) The only changes that needed to be made were to the data types and to some of the variable names in the standard tm1api.bas module (which of course became tm1api.vb upon conversion). I'll discuss these in more detail below. The reason that it's fortunate is twofold. First, the upgrade wizard was removed from Visual Studio 2010 and is unlikely to ever return. I had an old copy of VS2008 on my PC so I still had access to it. You may not (VS2008 was three versions back now), and may have to wing it manually. The second reason is that for anything other than a very simple project (which is why I kept my VB6 one rudimentary), the upgrade wizard is one step short of useless. It may as well tell you "Hey, howzabout we just rewrite the code from scratch?" which is probably why MS dumped it. (That, and they probably figured that any VB6 projects that were ever going to be converted to .Net will have been converted already.)

Creating The Project: The Montgomery Scott Option
Aye, this is where we buuld the thing from th' groond up, Caapt'n.

The first decision is obviously the project type. I'm going for a Visual Basic Windows Forms application given that WPF (the only viable alternative in VB) seems to be dead in the water in terms of its future. (And because the fact that WPF can make some difficult tasks simple does not excuse the fact that it makes many simple tasks difficult.) Aside from which I would assume that most readers who are dipping their toes into this are more familiar and comfortable with Windows Forms from VBA. There are some (OK, a lot of) differences between VB6 Windows Forms projects and VB.Net ones (almost entirely for the better, it must be said), but it's easy to feel at home here.

The next choice is the Framework version. I'm going to go with 3.5 for one reason; that's the version that you need if you want to work with the TM1 .Net API. (And yes, it's possible to work with both in the one project though I've yet to find a need to.) 3.5 is past the first flush of youth in terms of its development cycle but probably isn't going away any time soon. It's your call, though.

Remember to enter the project name in the box below your selection to avoid being lumbered with a project named "WindowsApplication1" or similar and then having to go back and fix the name in many and varied places later. That one is way too easy to overlook.
Similarly I don't like having object names like "Form1" or "Class1" or what have you, so the first thing I do with a new Forms project is to rename the main form; not only the form itself but also the source file name. I use frm_Main for mine. To change the file name, single click on the form's node in the Solution Explorer which will bring up the file's properties dialog. (If Solution Explorer isn't visible, bring it up from the View menu.) To change the form caption and so on, double click on the form's node in Solution Explorer. (That only applies to forms, not classes.)
I would recommend saving the project straight away, which can of course be done through either the file menu (File -> Save All), [Ctrl]+[Shift]+[S], or through the multiple floppy disk icon on the toolbar. (Not the single floppy disk, that saves only the current file.) I recommend ensuring that the "Create Directory For Solution" box is checked so that you can keep all related files in a single location, and not be cluttered up by other unrelated files.
The Standard Declaration Module
The next thing that you need is the TM1API.bas module which contains the standard list of (it would be untrue to say "all") TM1 API library function declarations. This should be found somewhere in your TM1 installation folder. In the "Path Pain Remains" section earlier in this thread I gave the path to the bin folder. If you go up one level back to the TM1 folder, then down to API\TM1API you should find it. Alternatively go to the TM1 folder and have Windows do a search for the file tm1api.bas.

In the Kobyashi Maru method I imported that file straight into the VB6 project and let the Wizard translate it. However VB.Net does not give you the option of adding .bas files to your project. (Not in VS2010 at least, which I used for everything aside from the upgrade wizard. You can add an existing file of a valid type by right clicking on the project (not the My Project node, which simply takes you to the project's properties, but the top level node which is named TM1ClassicAPIDemo in my screenshots), then "Add -> Existing Item..." You'll note that you can import existing .vb files (I hesitate to say ".vb modules" and you'll see why shortly), but there is no option to import a .bas file.)

SIDENOTE: If you import a .vb file in this way then a copy of that file is taken and transferred to your project folder. You can't share a single source file between multiple projects. If you want to do something like that you need to create a library and reference that library instead. Accordingly even if you create a .vb file incarnation of the TM1API.bas module (which we'll be doing in a moment) and use that in subsequent projects you will still be adding copies of the file to those projects and will have to update each copy as needed. (Not that it should be needed very often in practice.)

In VB6 one module was in one file. In VB.Net, though, a module is simply a block of code contained within a file. A new file can be added through right clicking on the top level project node (as described above) and selecting "Add -> New Item..." You'll have the option of adding (amongst other things) a Module (i.e., a standard module) or a Class. It doesn't seem to matter greatly if you choose the wrong one as they're both files with a .vb extension, and have no discernible difference in their file properties. Only the default code block (explained below) is affected.

Both standard Modules and Class modules are defined within the .vb file by using or statements, as the case may be. These create a "block" of code. (If you select a Module from the Add -> New Item dialog then a Module block will be automatically created for you. If you select a Class, a Class block will be.) As with other blocks (such as procedures, loop blocks within procedures, and regions that you can define yourself) these blocks are expandable and collapsible. I despair of ever seeing equivalent functionality in the TI Editorsaurus. The compiler doesn't seem to throw a wobbly if you have both class and module declarations in the one file either, but from a housekeeping perspective I wouldn't regard that as being a good practice.

Some declarations are made at the top of a file, outside any module or class blocks. Two examples of these are the Option Explicit and Option Strict (discussed later) statements. Another example is the Imports statement which brings in namespaces of some of the .Net libraries. If you're really, really new to .Net and your eyes glazed on the word "namespaces", think of it this way:

• Pretty much everything in .Net is a class, even what you used to think of as standard modules.
• Classes are contained within an assembly, which for our purposes you can regard as being either an executable or a .dll. (If you don't know what a .dll is you're so very much in the wrong place reading this article.)
• Namespaces are used to group and identify the classes within an assembly.

When you import a namespace by putting the declaration at the top of the .vb file you no longer have to refer to objects in that namespace (and some methods that are contained in the objects) by putting the full, lengthy path telling .Net which library it will find the object in.

Another way of importing namespaces without doing it file by file is to go to the project's properties (which I'll describe later when we get to the topic of Strings), go to the References tab, and scroll to the bottom of it. There you can select the namespaces which are imported across all of your files.

Highly experienced .Net developers may feel that I oversimplified there and perhaps I did. If so, please feel free to bite me. This also applies to any other oversimplifications that I may make in other parts of this article; for present purposes our objective is just to get this crate off the ground. We can worry about the niceties later.

For housekeeping rather than technical purposes I would be inclined to create a new Module file to hold the standard TM1 API function declarations. Again, when you go to "Add -> New..." remember to enter the name of the file in the box at the bottom to avoid having to rename the thing later. (This will also ensure that the default Module block in the file is correctly named. However one of the beautiful things about .Net is that when you do rename an object, all of the code that references it seems to rename automatically.) From there it's a case of copying the content from the TM1API.bas module that Iboglix supplied (just open it in a text editor like Notepad; remember to exclude the first Attribute VB_Name = "tm1api" line in your copy action obviously), and pasting it into your new module.

The question of course is which TM1API.bas module to copy from? IBM has in fact added a few new functions over the versions in response to new or changed functionality in the server, so you need to be wary of using (say) the 10.1 module if most of your clients are on 9.5.2 and the function in question doesn't exist in their libraries. (In reality it will only matter if you use those functions. The presence of function declarations which aren't used is pretty much immaterial; they're just dead text. However it's probably safer to use the file from the lowest version that your clients will have.)

At this point you may be thinking "Hey Alan, rather than blathering on telling us how to update the file, why not put the modified file(s) up for download?" And I may well have done that had more recent versions (certainly from 9.5.2) not incorporated a declaration that the file is "IBM Confidential". In this I smell the dead hand of Iboglix's legal department given that the function declarations (the ones that they could bother documenting, anyway) are contained in the TM1 API documentation which can be downloaded by any member of the public. This makes the confidentiality claim nonsensical, knee-jerk bovine-waste, probably drafted and copied and pasted in by some junior legal officer in Armonk in the invalid assumption that s/he is doing something productive with their life. Rule #13: Never, ever involve a lawyer. However given that, you're just going to have to follow along with me.

The three files that I have access to are for versions 9.0, 9.5.2 and 10.1. I'm not going to try to dig out the ones in between, much less earlier ones. The differences between them I'll summarise as follows:

9.0 vs 9.5.2: Added Functions by the time of 9.5.2:
TM1DataReservationAcquire
TM1DataReservationRelease
TM1DataReservationGetAll
TM1DataReservationValidate
TM1DataReservationReleaseAll
TM1DataReservationGetConflicts
TM1ServerBatchUpdateGetCommitTime
TM1SystemServerStartEx
TM1CubeDataReservationMode
TM1ProcessGrantSecurityAccess
TM1ErrorCubeNoMeasuresDimension
TM1ErrorCubeCellWriteStatusNoReservation
TM1ErrorCubeCellWriteStatusCellReserved
TM1ErrorViewNotMdxView
TM1ErrorViewNotNativeView

9.5.2 vs 10.1: Functions Added In 10.1
TM1ChoreExecutionMode
TM1ErrorChoreModifiedDuringExecution
TM1ErrorChoreDeleted

Scope Changes
At the head of the standard module there are three variables declared. Presumably the intent of these was to provide a place for you to store a user handle (huser), a handle to a value pool (pGeneral), and a handle to a server (voDatabase). You may have used them; I never have, preferring instead to declare any such variables with my actual code. Aside from which, you can hit an issue with using only a single user handle for servers which are on different Admin Hosts. (Think about it a little... especially if you have development environments with the same name registered with different Admin Hosts, and you want to access them simultaneously through your application.) I therefore prefer to use one user handle per Admin Host with one general pool handle per user handle. That having been said in this article we'll be assuming for the sake of simplicity that only a single Admin Host is involved, and that all of our cows are perfectly spherical and happily contained within a vacuum.)

In keeping with IBM's policy of maintaining the TM1 APIs on the bleeding edge of technology these are all declared as "Global" variables. The Global keyword (which kinda-sorta but not quite equates to "Public") became obsolete in VB 4, if memory serves me correctly, but don't quote me. I know that I never used it in VB 5. I do recall using it in Access 2.0's then Access Basic language (forerunner of VBA), the first version that I worked with, but that it had been depreciated in favour of Public by the release of Access 95. A language from the late 90's like VB6 will support one from the early 90's but backward compatibility 15 years later is asking a bit much. You'll get a syntax error if you leave these declarations as they are. Either change the variables to Public, or better still delete them and ensure that you have the right number of variables for the Admin Host(s) and server(s) that you're working with.

Parameter Name Changes
A couple of the function declarations use argument names which violate the keyword restrictions of VB.Net. Specifically: You can't use str as the argument name. The upgrade wizard renamed it as str_renamed; you can of course call it any valid name that you want.

There's a similar problem with Val (the second argument) is not permitted as an argument name. Again the upgrade wizard changed it to val_renamed.

Data Type Changes
This is of course the biggest issue. In VB6 a Long was a 32 bit integer, an Int a 16 bit one. In the .Net Framework a Long is a 64 bit integer (regardless of whether you are using 32 or 64 bit Windows), an Integer is a 32 bit Integer, and the former Int 16 bit value is now a Short.
Obviously you have to change all of those Longs in the declarations to Integers and all of the Integers to Shorts for your code to work without overstepping memory location boundaries.

(Strings? We need to have a little chat about those later, but you don't need to change anything in this module.)

Obviously the way you do this is in the reverse order; do a find and replace renaming all of your Integers as Shorts, leaving you with only Shorts and Longs, and then all of your Longs as Integers leaving you with only Shorts and Integers. Obviously nothing is left as a Long since the TM1 client software is currently 32 bit only and the sight of a 64 bit integer coming at it would put it into brown coloured trousers.
Missing Function
There is a function which is referred to in the API manual but not documented, and not found anywhere in the standard declarations. I'd advise you to add it in: Be aware that this returns the actual array size, not a handle to a value capsule containing the array size. I discovered that one when I was running some tests in VBA to confirm what I suspected that the function's arguments are, and encountered some of the API's standard and rigorous error handling. (That is, the Excel session metaphorically exploding and crashing to the ground in flames.)

Actually there are a few functions which are not documented and/or included in the standard list, but this one matters more than most for any non-trivial code. That having been said as far as I can recall when I've called both this one and the TM1ValArrayMaxSize function (which is declared in the standard module), they've both returned the same value. I can't guarantee that that will always be the case, though. Certainly the manual claims (to the extent that it refers to the functions at all) that there is a difference in their usage.

Part 3: Syntax And Setting Issues

Let's Talk About Strings
If you've worked with the classic API extensively you'll be aware that it makes extensive use of fixed length strings... which do not exist, as such, in .Net.

There are a few ways around this. One is apparently to use character arrays. I haven't tried this and would be a bit hesitant to do it given that the TM1 API is hardly bulletproof when it comes to error handling. (As noted earlier, the API's preferred method of handling invalid arguments in an Excel TM1 API application is to send Excel plummeting into a flaming pile of wreckage onto your desktop.)

In the earlier days of my work with the API in .Net I went with another option, one which I consider is most likely the safest one. That's to make use of the VB6 Compatibility library (the full name of which is Microsoft.VisualBasic.Compatibility). This library has a VB6.FixedLengthString method in one of its classes. To add the library to your project either:

• Double click on the My Project node in Solution Explorer which will open the project's properties. Go to the References tab (from the tabs on the left hand side of the dialog) and scroll down if necessary until you see the [Add...] button. Click that to bring up the Add Reference form, then and go to the .Net tab, sort the libraries in name order and find Microsoft.VisualBasic.Compatibility. Double click on it to add it to the project. OR
• Right click on the project name in Solution Explorer and choose "Add Reference..." which will take you straight to the Add Reference dialog that I referred to above.

There is of course no such thing as a free lunch, and the cost of this one is this; the Compatibility library, unlike the standard Visual Basic library, is not (I understand) considered to be one of the core libraries of the .Net Framework. A number of experienced and respected VB.Net gurus and authors discourage its use on the basis that it was primarily intended to support code which was generated by the VB6 upgrade wizard and that the library may be pulled by MS in the future, just as they pulled the wizard itself in Visual Studio 2010. And that is true enough. However given my experience of the TM1 API being damn fussy, I (initially at least) preferred to take the risk of that than try to make (say) character arrays work, much less attempting to check that they will work under all circumstances. Aside from which MS is certainly not going to retrospectively pull the library from the .Net 3.5 (or 4.0) Framework, even if the time comes when they don't include it in (say) the version 6 or version 7 Framework; something which is pure speculation anyway. If I revisit this topic in 5 years I may have a different opinion but for now I'd say that if you want the safest way you should probably use what is known to work, and what is closest to the legacy code that the API was designed for.

Let's look at these strings in action.

I have a number of standard modules that I use for .Net API projects. These typically contain functions which wrap around common tasks, such as obtaining an object's name from a handle to it. The specific function that I'll discuss below is normally used when I iterate through an array of objects (cubes, dims, etc) using the array's index, and obtain a handle to the object from TM1ObjectListHandleByIndexGet. That handle is passed to my function as one of its arguments along with the corresponding user handle and pool handle.

We need some variables to begin with. The first will store a handle to the object's name property: Stuff to note about this declaration:

• Microsoft may have dumped Hungarian notation, but I still use a version of it because I prefer to be able to tell at a glance what a variable is supposed to contain. If you happen to feel that this is some kind of archaic, reactionary, behind the curve, atavistic policy you are again invited to bite me. Most of my Hungarian style tags are followed by an underscore; s_ for string, i_ for integer, is_ for short integer and so on. I don't use the underscores on TM1 API variables simply because again that tells me at a glance that it is indeed a TM1 API-related variable. h is used as a handle to an object or a user handle. vh is used for a handle to a value. s, i, is etc are used without an underscore for the raw values that are returned from an API function, and which may need additional processing. (The other advantage of using h and vh is that a lot of the code in the manual uses those prefixes, which minimises the amount of modification needed when I copy and paste from the documentation.)
• Remember that the change of long integer length in .Net from 32 bit (in VB6) to 64 bit requires that the value handle be declared as an Integer rather than a Long.
• Note that the variable is initialised with a value of 0 at the same time as it's declared. The ability to do this is a vast improvement over classic VB6 but there is a HUGE gotcha that you have to be aware of here. One concept that you need to wrap your head around in .Net (rapidly) is that variables are also objects. And objects can be Null. In VB6 a declaration of such a variable would cause it to be created with the default value of 0. But in VB.Net, it's Null until you assign it a value. Consequently if you don't initialise the variable as I've done here then you can, if things don't work out properly, run smack bang into a Null exception runtime error. Consequently you should always initialise when you declare. (At least for "normal" variables like strings and integers.)

Next, we need a variable to hold the name when it's returned from TM1ValStringGet_VB. This of course is supposed to be a fixed length string so as per what I've said above I declare it as: Things to note about this one:

• Remember that you'll get a syntax error if you haven't added a reference to the Compatibility library. You should also have

  Code: Select all

  Imports Microsoft.VisualBasic.Compatibility

  At the head of your file or have specified it as a globally imported namespace in the lower part of the References tab of your project's properties;
• gISC_TM1STRING_SIZE_DEFT is a short integer constant that I have declared earlier in the module. It's defined as 255 because it's pointless fiddling around trying to anticipate what length to use. Really, who cares? Set it for the maximum size that an object name can be and you never have to worry about it. (Yes, technically the manual states that the maximum length is only 128 characters. The manual has lied to me so often, and is so incomplete and frequently flat out wrong that I'd rather trust my instincts and allow the extra space.)

The last variable that I use is: From the underscore you can see that this is one of my standard string variables. I use it to store a trimmed version of the fixed width string above.
Note again that I've initialised it with an empty string to prevent null errors.

Next, we need to pack the VB6 Fixed Length string with null characters, just as we had to (or generally did) in VBA/VB6. Note that the fixed length string is an object but it doesn't have a default property the way some VB6/VBA objects had. We must therefore initialise the correct property of the object, which is the .Value property: Hard core VB6/VBA-ers may recall that the Chr() function was less desirable than the Chr$() function since the latter returned a string data type, and the former returned a Variant in string format which involved a couple of extra microseconds of conversion work under the hood. Micro-optimisers would therefore always go for the chr$() type, but it doesn't exist in VB.Net. (Nor do Variants, of course.) Chr() does and it's safe to use indefinitely since it's in the Microsoft.VisualBasic.Strings library, not the Compatibility one. The statement above obviously creates a new string consisting of 255 null characters and assigns it to the Value property of my VB6 Fixed length String object.

The next line of code obtains the handle to the name property; there is nothing new here, this is just vanilla Tm1 API code. hPool is a handle to a value pool that was passed to the function as one of its arguments. Mine is stored as a property of a custom TM1 Connection object and that property is passed to my function, yours may be a Public variable; it really doesn't matter, as long as it's valid. hObjectHandle is a handle to the object that I'm obtaining the name for, which has also been passed to my procedure as an argument: Next, I call a function which checks the returned value handle and makes sure that it contains a string and not an error. I'll omit that line here since you obviously won't have that function to call, but I do recommend that you write your own code to make the same check.

After that, we extract the string from the handle to the ObjectName property using the standard function: The first argument is a user handle which has been passed to the function. The second is the value capsule handle that we obtained from the TM1ObjectPropertyGet function. The third is our VB6 fixed length string's .Value property (which will receive the extracted value), and the last argument is my custom constant giving the value of the size of that string, which is of course 255.

Finally, I call another custom function to get rid of the excess null characters: TrimNull is another function that you won't have since it's a custom one in one of my classes. However all it does is look for the first null character in the fixed length string, and return any characters to the left of that. (Or an empty string if there aren't any.) I assign the return value from that function that to my standard string variable.

Next, that value is returned to the calling procedure. In VB6/VBA this would be done through assigning the value to the function name and while that still does work in VB.Net (you'll see an example of where I still use it later) the more conventional .Net way is to use the Return statement: Yes, I could omit the use of the s_ObjectName variable and return directly from the TrimNull function but depending on how edgy I'm feeling at the time I don't usually like returning a value directly from another function. On the off chance that anything goes wrong in the function call I'd rather that the error in the function call be separate and distinct from the value assignment. Again, if you think that's being then over-cautious then let the biting begin.

Regardless of what I have said above, there is also a third option that I experimented with and which seems to work and which, to be honest, I've been using more commonly in my more recent code.

Another of my standard functions (the one which checks the data type of a returned value handle, actually) uses the TM1ValErrorString_VB function to extract any error string that may be embedded in the value capsule. As with TM1ValStringGet_VB, the API documentation claims that the argument that receives the value needs to be a fixed length string. Mind you, it looks like Iboglix just copied and pasted from the TM1ValStringGet_VBdocumentation into the TM1ValErrorString_VB documentation, as a result of which they nominate the wrong function. However the principle still applies:

Returns the string Str padded with blanks. It must be declared with a fixed maximum length. The length should match the value of Max. For example:

Code: Select all

Dim Str as String * 75
TM1ValStringGet_VB( hUser, vValue, Str, 75)

In my function I decided to use a standard string variable and simply pre-populate it with the specified number of null characters, like so: Thus far I've had no issues with this variable being used directly in the TM1ValErrorString_VB function: Or, indeed, with the TM1ValStringGet_VB function either. But again, if you want to do that... test, test, and test again because you're still using a data type that is different to the one that is specified by the documentation, so there is some risk involved.

One last trap that is dangerously easy to fall into; remember that while TM1 is usually case insensitive, VB.Net (like VB6 before it for that matter) is not. Thus the expression: will return FALSE. (Note the lower case "a" in the second "and".) If you want a case insensitive comparison, convert both expressions to upper or lower case first.

Error Handling
This isn't intended to be a tutorial on VB.Net so I won't be covering this in any great detail, but I'll mention it in passing.

Those who have read some of my other posts will know that IMHO anyone who does not use error handling needs a good shootin'. An unhandled, environment-crashing, cryptic error blasting out into some poor hapless end user's face is the epitome of bad programming.

Traditional VB6 error handling still works in .Net, which is to say that you can:

• Put a label in your procedure (or in your top level procedure at least), just below an Exit Sub or Exit Function statement;
• Put a block of error handling code into the procedure below that label;
• Put an On Error Goto statement at the top of the procedure which will redirect the code to the error handling block in the event of a runtime error.

You can raise a custom error through the .Raise method of the Err object.

In .Net an alternative method called Try / Catch blocks is used. I didn't take to this method initially but have started to warm to it. These blocks take the form: They can get more sophisticated than this; for example you can nest Try / Catch blocks.
You can use On Error Goto code in some procedures and Try / Catch ones in others within the one project or module, but you can't mix and match within one procedure.

The other big difference is that rather than the error codes / descriptions that we had in VB6/VBA and which could be generated using the .Raise method of the rather basic Err object, .Net uses Exceptions. This isn't just a change in terminology; Exceptions are fully formed objects, capable of having distinctive properties which go beyond mere description text. (This includes embedding other exceptions within an exception to allow a trail of errors to be built up, though that's seldom necessary.) There is a hierarchy of exception objects, and you can also build your own if you want. You can also have multiple Catch statements, each intended to catch a specific type of exception, as long as you go from the more specific ones to the more general ones.

As with VB6/VBA legacy errors, if you don't have a Catch block within a procedure then the exception will flow back up the call stack. To ensure that the user doesn't get an unhandled error slamming into their face you therefore only really need a Try Catch block in the procedure at the top of the stack.

To give an example, let's look at a block of code from my procedure which checks the data type of a Value Capsule. The user handle (hUser) is passed as an argument to the function. gSC_TM1APIERR_HUSER_INVALID_30001 was a string constant which contained a description of the error: Things to note about this:

• Although this point belongs in the previous section it's worth noting that by putting the ampersand (&) at the end of each line you no longer need to use the VB line continuation character (that is, the underscore). The underscore will still work, you just don't need it. I always preferred to put the ampersands at the front of a string continuation line to emphasise the fact that it's a continued line, but unfortunately you don't have that option unless you want to stick with having underscores at the end of the line.
• Blessed be VB.Net, for we finally have arguments that you can pass when you create a new object. In VB6/VBA you have the Class_Initialize() procedure but you can't pass any arguments to it which led to all manner of ridiculous and unsafe workarounds to populate some of a class object's properties when a new object was created. Classes in VB.Net have real constructor procedures which allow you to pass arguments to them.
• The ArgumentException object (and indeed other Exception family objects) is no different. The constructor function is overloaded meaning that you have a range of different arguments that you can pass, but for this specific Exception class it comes down to three arguments in varying combinations:
  • Message, a description of the error;
  • ParamName, which allows you to pass back the parameter that had the invalid value; and
  • InnerException, which allows you to pass back any existing Exception object as a property of your own one.

In my case I've used the description only. Other Exception objects may have different sets of arguments, appropriate to the errors that they're describing.

Option Explicit And Option Strict
All VB6 / VBA coders should already be familiar with Option Explicit. It's the declaration which is made at the head of a module requiring you to declare any variables before using them so that you don't mistype the variable name MyStr as MySr and then have to spend two hours figuring out why your code gives unexpected results. Anyone who writes code for me without using Option Explicit also needs a good shootin', ideally simultaneously with anyone who doesn't use error handling. The only difference in VB.Net is that it's declared outside of the module block.

Option Strict is new to VB.Net. In theory it will generate compile errors if you try to assign a value in a way which results in a narrowing of the value; for example, if you assign a long integer value to a standard integer variable. Even if (at run time) the Long contained a value which is small enough to be stored in an Integer, you'd still get a compile error since obviously there is no way of the compiler validating what the Long might or might not contain when the program is run 6 months or a year from now.

In VB6 Option Explicit had to be stated at the head of the module though there was a VBE option which allowed the statement to be automatically added to any new modules. (Again, work for me, don't use it, shootin' time.)

In .Net You can still add Option Explicit and now Option Strict to the head of your files (before any Module or Class declaration) but there is also another way; if you double click on the My Project node to open the project properties and select the Compile tab you can set either or both to apply to your entire project. These settings won't add the keywords to the head of your files, but the whole project will behave as if they were there.

My recommendation is that you have Option Explicit on but think a little bit about what you want to do with Option Strict. The reason is that some TM1 API functions are inconsistent in the value types returned, and Option Strict can rapidly become a pain in your backside.

To give an example (and this one is down to inconsistency on Iboglix's part), the TM1ValType() function returns a Short (formerly Integer). However the functions that return the data type values, and which you have to compare TM1ValType()'s return value to (like TM1ValTypeIndex) return an Integer (formerly a Long).

In the procedure that I use to check a value capsule's type (called, poetically enough, CheckValueCapsuleType) I extract the value type by assigning the return value from TM1ValType() to a variable named is_ValTypeReturned. I then check it against the various API functions to determine what type it is. I also have a Public enumeration called TM1API_ValTypes. (In .Net you access each specific enumeration value via the dot operator, as you'll see in a moment.) I compare the value that is stored in is_ValTypeReturned against each TM1 API data type function using a Select Case block. Now if I do this:
then as soon as I turn on Option Strict, boom! I get a lengthy list of "Option Strict disallows implicit conversions from Integer to Short" messages in the errors display. No, I'm not assigning the Int value to a Short, and I have no idea why the compiler can't just treat the Short as an Int for the purposes of comparison since it clearly can't contain a value larger than the ones that it's being compared to.

This type of pedantry on the part of Option Strict can cause your program to transform itself from a sleek, sweetly compiling piece of code to a wizened, wrinkled, black clad, foul-breathed shrew which keeps waving a metaphorical arthritic finger in your face and squawking "Uh-uh, not allowed, uh-uh!!!" The options that you have to deal with this are as follows:

• Punch the monitor and yell "**** OFF!" at it. While this option is gratifying, surprisingly the other options are slightly more efficacious.
• Turn off Option Strict. You can do it for just in the file(s) which contain your TM1 API module(s) if you wish to (by setting it to Off in the heading of the file), and leave the global setting on for the rest of the project. This is perhaps a bit of a cheat but not a wholly invalid one because this isn't the only instance where Option Strict will throw up errors that really are complete bullsh..., er, which have no real world validity. Another random example is Registry.GetValue. This reads a value from the Registry into a variable. You can specify a default value so that if there is no such registry value, a value will still be returned. However with Option Strict on you still have to explicitly convert the return value of that function to the variable's data type otherwise "Option Strict On disallows implicit conversion from Object to {whatever}. The worst example of all is si_Idx += 1 Yes, I tell you truthfully that this will generate a compile error because the compiler will not recognise that the constant value 1 is just as valid as a short integer as it is as a standard integer. As far as it is concerned 1 can only be only a 32 bit integer and must therefore be explicitly converted to a 16 bit integer before you can use it in this increment statement. This kinda kills the mood of having that nice increment shortcut that we lacked in VB6. "Uh-uh, not allowed, uh-uh!!!" (As a digression there are two other code elements that you're going to become real close buddies with if you use Option Strict. The first is the .ToString method of most variable types, which allow you to convert (say) a numeric value to a string before displaying it. The other is the TryCast() function, which allows you to try to convert one type of object (as opposed to a simple variable) to another.) It's up to you whether you think that the safety net provided by Option Strict outweighs the irritating nitpicking that it sometimes (OK, often) pulls on you.
• The third is to convert the values returned by the various TM1ValType functions to Shorts using either the CShort method, or the more .Netish Convert.ToInt16() method from the System.Object library. However this can involve a substantial amount of typing. OR
• Finally, the shortest and easiest option if you elect to leave Option Strict on is...

  Code: Select all

  Select Case Convert.ToInt32(is_ValTypeReturned)
      Case 0
          CheckValueCapsuleType = TM1API_ValTypes.TM1VTUnknown
      Case TM1ValTypeReal()
  {Etcetera}
  

Upon doing any of these all of my compile errors immediately evaporated and I had my sweetly functioning code back.

XML Comments
Before we move on to connecting to the server, I want to mention something about commenting in VB.Net. You can of course still create conventional header blocks in your procedures, but you can also create XML comments.

I would have to say that I'm not generally a fan of XML. In a lot of cases it's just clouds of metadata bloat encasing a small amount of content which could more easily, and far more readably, be contained in plain text format. However it does have one big advantage here.

If you type three single quotes on the line immediately above a Module block, a procedure, or indeed a number of other code elements, an XML template will appear. (The nodes are the default ones, but you can customise them by adding additional ones.)
In the case of a procedure, you get nodes for a summary of the procedure, for each of the parameters / arguments that you pass to it, for the return value, and for any remarks that you have. When you fill in the nodes, then when you call the procedure you will have a rich Intellisense prompt that gives you the details of the procedure and its arguments, the summary of it, and as you move from one argument to another the description that you've added of what the argument is for right at the bottom.
Obviously this feature becomes exponentially more useful as your project becomes larger and the number of people who work on it increases.

It's as useless as chicken dung on a pump handle for producing human-friendly documentation in any kind of help file format, though, at least without some kind of external utility to lend it a hand.

Part 4: Let's Get Connected

OK, it's time to do something with our project. I've actually given you enough to go on with (because from hereon in it's pretty much the vanilla API code that you're hopefully familiar with, but let's take a look at some basic operations.

Getting Some Inputs
To start off with we need to gather some data from the user. The first thing we need is their Admin Host. In this simplified example they can enter only a single host and will enter that into a Text Box. (I trust that you'll indulge me putting this in as masked textbox rather than a standard one.)
In a real world example you would allow the user to specify multiple Admin Hosts (even by entering a delimited string, just as you can in the Options dialog in Perspectives), and you would store that in either the Registry or an .ini file to be retrieved at runtime so that the user didn't have to enter it all the time.

Since this is a bare bones example, however, we offer no such luxuries. (Though eventually I did enter some default entries for myself since I got sick of entering them every time.)

The next is a command button which will fetch the list of servers and populate the Servers List listbox from it.

After that we have the aforementioned listbox.

Then we have text boxes for the client login and password, as well as a button to log in... though these will be disabled initially. Again the client login and password would be stored and loaded in the real world, but that's an exercise for the reader.

Just so that you know where I'm going with this:

• What Do You Want To See is also a list box. In this simple example I'll be returning a list of cubes and views, and a list of chores and processes, and I'll flip between the two using this listbox.
• The white box at the left bottom is a treeview that I'll use to display my lists. These are really easy to work with in .Net. Their main weakness is that they don't easily allow multiple selections to be done, but that's by the bye here.
• The grey box at bottom right is in fact two controls overlaid on each other. At the bottom I have a rich text box which I'll use to display information about a selected chore, and over the top of that is a data grid view that I'll use to display information about the values in a selected view. (The one weird thing about those controls are that they use a (Column, Row) method of cell referencing rather than the (Row, Column) method that damn-near every other grid in the world, including Excel, uses.

Gathering The Servers
The first thing we need is a place to keep the user handle. In this demonstration I'm using a public variable declared in a standard module for this, something that I do not recommend that you do in practice for the reasons that I described earlier in this article. (Specifically, you may need different handles for servers that are registered with different Admin Hosts. In this example I'm assuming, for the sake of simplicity, that we are dealing with only one Admin Host so I can get away with the one user handle.)

The next thing we need is a place to store the list of the servers. Of course you can use an old fashioned array, but thankfully there are things which are hugely more powerful in VB.Net; generic collections objects, which allow you to create strongly typed collections of pretty much any data type with ease. They are So. Choice. When you've bashed your head for so long against the limitations of VBA arrays and even Collections (or even the slightly more advanced Scripting Library objects like Dictionaries) you will luuuurve generics in VB.Net, though time and space does not permit more than a cursory discussion of them here.

All we really need to do is store the name of the server but I'm going to do something a little more elegant and use custom objects which I've called called TM1A_ServerConnection. I want to be able to access those objects by server name so I'll need a key. For this, a Dictionary generics object (a distant cousin of the one that was in the old Scripting library) should do nicely. Again, I've declared this in my main standard module: This declaration tells it that I want to hold a collection of objects of the type TM1A_ServerConnection, and that I want to access them using a string key which will, of course, be the server name.

Now it's time to return to the GetServers button's Click event handler on the form. I've set up a Catch block to display any exception messages to the user, but I'll leave that as an exercise for the user.

Before doing anything else, we need to check that there is an entry in the Admin Host text box. If there isn't, we throw an exception and tell the user.
The next thing I do is to check whether there is already a connection open (by checking the value of the user handle) and closing it if there is. However let's not get sidetracked; you should already know how to do that. (It's in the manual.)

From this point it's more or less vanilla TM1 API work. We begin with: to get the one user handle that we'll use in this example.

If the number that gi_hUser contains is zero, I throw an exception; the most likely cause is that the user does not have the API libraries installed or does not have the path environment variable set.

Otherwise, I just clear the list box and the existing Dictionary of servers to get them ready to receive the new list of servers. (Any live server connections (this simple example allows only one) will have already been closed. Now it's time to set the Admin Host. This is why I warn you against using only a single user handle; by executing this command you essentially bind the user handle to a specific admin host, which means that you can't really iterate through all of the admin hosts setting it in turn. If you do, you may learn a lesson later on that is not necessarily to your advantage. But for demonstrating principles, this is all we need. (Remember that the code is in the form's code module, so the "Me." Reference refers to the form itself. You're getting the contents of the textbox that the Admin Host is entered into. The fact that this is a raw string rather than one that is inside a value capsule seems not to matter to the API.)

Next, we reload the list of servers that are registered with the admin host that we have set. This is equivalent to pressing [F5] in Server Explorer. After that we return the number of servers that are registered with that Admin Host. I have already declared is_ServersCnt as a Short: The consistency of the API ensures that you often have to guess whether a function returns a value capsule handle or an actual value, and in this case it's the latter. If the number is 0, I throw an exception.

Otherwise it's time to loop through the array of servers to get their names. Remember that although arrays in .Net are always zero based, an array in the TM1 API is not a .Net array so we go from 1 to the number of servers found.

This is another instance where I'm going to live life on the edge and use a standard string variable, which I will simply initialise with null characters.
The way you connect to a server will of course vary according to whether you are using integrated login or not. I'm going to assume that it's the classic TM1 login method.

I have a handler for the lst_Servers.SelectedIndexChanged event to detect when a server has been selected. (sData is of course the standard sample database that IBM supplies. RPO will be familiar to viewers of my TM1 Bytes videos.) Once that happens it enables the login and password dialogs. The user can enter those fields and then click the login button.
What you do from here is up to you; everyone will probably have a different way of handling this. In this example I have the previously-mentioned object which represents a server connection (TM1A_ServerConnection). When I iterated the list of servers I created one of these TM1A_ServerConnection objects for each server found. When the user selects a server and clicks the login button, I access the relevant object from the dictionary using its key. (Unless they've failed to enter a user name, in which case I spit an exception back at them. But let's assume that they know what they're doing.)

I've put a LogOnToServer method into the object: Another way of doing this would have been to put the login code into the constructor event of the class, but that would presuppose that the program already had all of the relevant information at the time that it creates the object. As I said, there's no One Single Right Way (a.k.a."Best Practice") to do this.

Assuming that all of the inputs pass muster, that method is called. The code in it should be pretty familiar to you if you've ever read the manual: Indeed, this is so familiar (aside from the change from Longs to Integers) that one might wonder why I bothered including it at all. The reason is to emphasise that by the time you reach this point, you're back on familiar ground (if you know the API, that is); there's nothing new or strange that you have to do aside from remembering to change your data types. So at this point, I shall almost wish you bon voyage as you sail upon the seas of the API in .Net.

Just for chuckles, though, before saying farewell I'll demonstrate the outputs of my sample project.

For chores, when the user selects the chore some information about the chore pops up into the rich text box (which, I must confess, I haven't bothered formatting) like so:
For views, it shows the values returned. (Granted, I haven't actually converted the subset indexes to names in this example, so they just appear as numbers. I thought I'd written a standard procedure to do that but apparently I haven't, and now it's time for me to move on to other things. Accordingly this is what it looks like for now, and I'll probably update it with a new screenshot when I need the relevant procedure and whack it into my sample):
Again, the purpose of these shots is not to highlight what the sample project has done or how it has done it... merely to highlight the fact that thus far I've yet to come across anything that I can do in the API in VB6 that I can't in VB.Net.

Happy coding...

Thanks a lot Alan, I could follow the explanations and rework some earlier TM1 API code in VBA to VB.Net.
Much appreciated!

Most helpful indeed as I am just starting a VS2010 project with the VB6 API. The issue about Longs vs Integers hit me when the debugger reported stack inconsistencies for every API call. I even tried the .NET API as an alternative, only to find that exceptions did not seem to happen where expected, the app hung in a "Running" state and ......fortunately thanks to you I can go back to the the tried and tested (haha) VB6 API.

Thank you for so generously posting this - it will, I am sure, help a lot of people.

Ian

An interesting wee problem:

All TM1 API and .NET functionality working as per Alan's guide (thanks Alan) but when passing an array of .NET Integers (being valid handles that in VB6 are Longs) to TM1ValArray() in Visual Studio 2008, I found the size of the passed array is truncated to 1, though it contains 9 items. Any ideas?

Regards, Ian

iansdigby wrote:An interesting wee problem:

All TM1 API and .NET functionality working as per Alan's guide (thanks Alan) but when passing an array of .NET Integers (being valid handles that in VB6 are Longs) to TM1ValArray() in Visual Studio 2008, I found the size of the passed array is truncated to 1, though it contains 9 items. Any ideas?

Would need to see the actual code. I know that I've had TM1ValArrays both inbound and outbound in VS2010 without any issues.

Hi Alan, thank you so for posting this to the forum. It's great to get so detailed professional help on tm1api from you. I do benefit a lot from reading this article.

I've done a little tm1api programming last month, really a simple one using Excel vba to dynamically extract the data from several cube views using TM1ViewArrayConstruct and TM1ViewArrayValueGet before populating into one worksheet. It works pretty well.

Yesterday, I was trying to migrate this to VB.NET using the same api (this generic one), however, TM1ViewArrayValueGet always returns TM1ValTypeError. The strange thing is that I could successfully connect, login, construct the view array and even get the number of rows correctly (TM1ViewArrayRowsNof)...but I just cannot get the correct result value from this TM1ViewArrayValueGet.

Here are parts of my codes:

......

vcResult = TM1ViewArrayConstruct(hPool, hView)
vResult = TM1ValBoolGet(hUser, vcResult) ' The result being 1 indictes the ViewArray is constructed successfully.

Dim hViewArrayRowsNof As Integer
Dim iViewArrayRowsNof As Integer

hViewArrayRowsNof = TM1ViewArrayRowsNof(hPool, hView)
iViewArrayRowsNof = TM1ValIndexGet(hUser, hViewArrayRowsNof)

MsgBox("The Number of Customers in the View is: " & (iViewArrayRowsNof - 1).ToString)

Dim index As Integer
Dim hCustomer As Integer
Dim iColumn, iRow As Integer

For index = 2 To iViewArrayRowsNof ' This is the start of the loop to get the whole list of customers from a view constructed.

iColumn = TM1ValIndex(hPool, 1)
iRow = TM1ValIndex(hPool, 1)

hCustomer = TM1ViewArrayValueGet(hPool, hView, iColumn, iRow) ' This hCustomer always returns TM1ValTypeError in VB.NET but returns value correccly in VBA.

Select Case TM1ValType(hUser, hCustomer)
Case TM1ValTypeError()

......

Have you ever used TM1ViewArrayValueGet previously? What do you think the issue might be? Thanks a lot in advance!

Terry Liu

landxterry wrote: Have you ever used TM1ViewArrayValueGet previously? What do you think the issue might be? Thanks a lot in advance!

I'll have to get back to you on this. I know I've used the function in some API coding in the past (obviously) but I'm not sure whether I've needed to in .Net or whether it was back in VB6/VBA. None of my active projects have had a need yet (there's one in development that will) so when I've had a chance to write that I'll let you know what I find.