# Visual Basic Main API Reference

Using the Visual Basic Main API
Visual Basic Main API Declarations
Visual Basic Main API Functions (alphabetical)
Visual Basic Main API Functions (categorical)

## Using the Visual Basic Main API

- Visual Basic API Conventions
- Understanding Visual Basic Declarations
- Visual Basic Functions for Excel
- Handles
- File Objects
- Message Handling
- Calling API Functions

## Visual Basic API Function Call Sequence

- Typical API Task Sequence
- Initializing the Visual Basic API
- Logging in to an Essbase Server
- Selecting an Active Application and Database
- Retrieving and Updating Data
- Recalculating the Database
- Logging Out from the Essbase Server and Terminating the API
- Common Problems and Solutions

## Visual Basic Main API Conventions

Empty Strings in Visual Basic API

"Empty string" is either a fixed size string (ByVal As String * size) filled with spaces or a variable/fixed length string that has chr$(0) in the first position. As a rule, if an input parameter can be referred to as an "empty string," it could be either one of the above described. If the same applies to the return value, it can only be the second one (to allow easy testing for an empty string).

## Understanding Visual Basic API Declarations

In your programs, you use the Visual Basic equivalents of C language declarations. The following table shows C language declarations, the way they are declared in the Visual Basic `ESB32.BAS` file, and how to call them in a Visual Basic procedure.

C Declaration | Visual Basic Declaration | Variable to Use When Calling
`Pointer to string (LPSTR)` | `ByVal S As String` | Any String or Variant variable
`Pointer to integer (LPINT)` | `I As Integer` | Any Integer or Variant variable
`Pointer to long (LPDWORD)` | `L As Long` | Any Long or Variant variable
`Pointer to struct (such as, LPRECT)` | `S As Rect` | Any variable of that user-defined type
`Integer (INT, UINT, WORD, BOOL)` | `ByVal I As Integer` | Any Integer or Variant variable
`Handle (hWnd, hDC, hMenu, etc.)` | `ByVal h As Integer` | Any Integer or Variant variable
`Long (DWORD, LONG)` | `ByVal L As Long` | Any Long or Variant variable
`Pointer to integers` | `I As Integer` | The first element of the array, for example I(0)
`Pointer to void (void *)` | `As Any` | Any variable (use ByVal with strings)
`Void (function return value)` | `Sub procedure` | not applicable

In the main form of your program, include the following lines to set the Boolean variables ESB_TRUE and ESB_FALSE, which occur in user-defined types such as the initialization structure ESB_INIT_T:
`ESB_TRUE = 1
ESB_FALSE = 0`

Note: See the Microsoft Visual Basic Documentation for information about using C-language DLLs in Visual Basic programs.

## Visual Basic Functions for Excel

The Essbase Spreadsheet Toolkit package contains a library of Visual Basic (VB) functions for use in Excel for Windows. The Excel version of VB is commonly referred to as Visual Basic for Applications, or VBA.

The Essbase Spreadsheet Toolkit is a separately sold option in the Essbase Multidimensional Analysis System.

From a VBA program in Excel, you can also call VB functions in the API libraries. To learn how to use the API within VBA, see the Spreadsheet Add-in online help.

## Visual Basic API Handles

### Instance Handles

An instance handle (similar in concept to a file handle) represents a program's access to the API, and distinguishes the program-specific resources and settings used within the API. This identification is necessary for DLLs, which may be accessed by several different programs simultaneously. When a program initializes the API by calling EsbInit(), an instance handle is returned.

#### Using the Instance Handle in an Application

An instance handle is declared as type ESB_HINST_T in Visual Basic programs.

The instance handle must be passed to the EsbLogin() call, which returns a context handle, and also to the API terminate function EsbTerm() to free any program-specific resources used within the API.

Instance handles may be passed to other programs, child processes, or threads, which can then log in independently of the original using the same API resources and settings. Make sure that all programs, processes or threads using the same instance handle log out before they terminate the API.

Note: A thread may require its own instance handle (phInstance) to avoid overwriting another thread's networking status information.

### Context Handles

A context handle represents a single, valid login by a user onto the system. A successful call to EsbLogin() returns a context handle, which can be passed to other API calls which require a context handle as an argument.

#### Using Context Handles in an Application

Context handles are defined as type ESB_HCTX_T in Visual Basic programs.

In general, a context handle is valid for as long as the user remains logged in to that server (that is, until after a successful EsbLogout() call). However, in case such as a server shutdown, a context handle can become invalid. Your program should therefore provide some way for the user to log back in during a session (for example, via a menu option or function key).

Note: A context handle is specific to an instance of the API, and contains an implied reference to the resources and settings for the appropriate instance.

#### Multiple Context Handles

A single instance of an API program may make multiple calls to EsbLogin(), using the same user name or different user names on one or more Essbase servers. Each call to EsbLogin() returns a unique context handle, and your program must keep track of each context handle returned. You may have up to 255 context handles per client application in use simultaneously, but if a program performs all its processing on a single server, in general it is easier to use only one context handle and to switch between different applications and/or databases as required, using either the EsbSetActive() function or the EsbAutoLogin() function.

#### Local Context Handles

Operations on local objects and files (objects on the client) can use a local context handle. Refer also to Local Contexts.

#### Sharing Context Handles

In general, it is not advisable to share context handles between multiple programs, processes, or threads, unless such use is guaranteed to be exclusive. A better approach is to use the same instance handle and log in each process separately. Essbase ensures that multiple logins using the same user name on the same server will only occupy one port on that server.

## Visual Basic API File Objects

An Essbase object is simply a file Essbase uses, such as a database outline, a calc script, or other data. Essbase has an object system which allows you to refer to such files through the API simply by the name, the file type, and the application and database with which they are associated. This allows objects to be manipulated independently of the underlying file system (which may vary between different platforms and implementations of Essbase).

Objects can reside on any Essbase server or client. A locking mechanism on the server controls access to objects, so that users with sufficient privileges can lock server objects and copy them to the client (using the EsbGetObject() function), edit them, and save them back to the server (using the EsbPutObject() function). Server objects can also be opened without locking for read-only access, but then cannot be saved back to the server. A user can also create or edit objects on client workstations for their personal use, or save them to the server for other users to share. Clients cannot make server to server copies.

### Accessing Objects

When accessing objects through the API, the object name refers to the file name of the object (without any extension). The object types are declared in the API header file in the form ESB_OBJTYPE_xxx (where xxx represents the specific type, as in ESB_OBJTYPE_REPORT). Most objects are associated with an application and database, but some objects such as calc scripts, and rules files can be stored at the application level and used with any database within the application.

Server object files are physically located in the corresponding application or database sub-directory. However, it is not generally advisable to manipulate server object files directly. Always use the appropriate API functions to copy the files locally. Client object files are also stored by default in application and database sub-directories of the directory specified by the LocalPath setting of ESB_INIT_T. These files may be freely manipulated and edited, but you should ensure your program is well-behaved when locking and unlocking server objects which are being edited on the client (always lock an object before editing and unlock it afterwards, whether or not changes are saved).

### Local Contexts

If you intend to access file objects on a client machine through the API, you need to create a local context for the API object functions to use. To create a local context, use the EsbCreateLocalContext() function, which returns a context handle. This handle can be passed to any of the object API functions instead of a login context handle, and causes the API to perform the requested operation on the local client object system instead of the server. You only need to create a local context once, immediately after your program first initializes the API.

If you create a local context, your program should clean up by calling the EsbDeleteLocalContext() function before terminating the API.

## Visual Basic API Message Handling

When your program calls the API, system messages and error messages are generated. Some of those messages are returned by the Essbase server, and others are internal to the API. Your program must process these messages in some way, and if there is an error which causes the operation in progress to abort, the user may need to be informed.

This section explains the API's message handling scheme, and then shows what C and Visual Basic developers can do to implement custom message processing in their programs.

### How the Essbase API Handles Messages

The following message levels are supported in Essbase:

- Information messages (for information only)
- Warning messages (operation will continue)
- Error messages (operation aborted)
- Serious (operation aborted - system is unstable)
- fatal (operation aborted - system is halting)

When your program uses Essbase API default message handling, all messages of level Error or higher (Serious or Fatal) are displayed on the current application screen.

### Using Message Handling in Visual Basic Programs

To implement message handling in Visual Basic Programs, first follow these steps as you code your program.

1. Set the `ClientError` field of the ESB_INIT_T structure to ESB_TRUE, and define a value for the `ErrorStack` field of ESB_INIT_T when it calls the initialization function EsbInit().
2. Call the function EsbGetMessage() to retrieve any information returned after a call to the Essbase API. After the information is retrieved, your program can display or handle the information as necessary.

#### Setting the ClientError and ErrorStack Fields

The following code fragment shows the `ClientError` and `ErrorStack` fields set.

    Dim Init As ESB_INIT_T

    .

    .

    .

    Init.ClientError = ESB_TRUE

    Init.ErrorStack = 100

#### Calling EsbGetMessage()

All information, warning, and error messages accumulate in a message stack when an API function executes. When ClientError is set to ESB_TRUE, EsbGetMessage() can be used to retrieve the top message from the stack. When successful, EsbGetMessage() returns a pointer to a message level, a pointer to a message number, and a message string. It also decrements the internal message stack pointer. To ensure that no data is lost, you should call EsbGetMessage() until the function returns an empty string in the message parameter, and zeros in the number parameter. See EsbGetMessage() for more details.

Visual Basic programs should call EsbGetMessage() to retrieve any information that may be generated by a call to the API. It is important to do this when the return code generated by an API call is not zero, to obtain any error or status information returned. Additionally, you should call EsbGetMessage() when the return code is zero when there is likely to be additional information returned. For example, successful calls to EsbLogin() or EsbAutoLogin() return useful information about the last login.

## Visual Basic API Function Calls

This section describes calling API functions, using instance and context handles, and handling return codes.

### Function Declarations

If you are using the API with Visual Basic, you need to include the correct function and constant declaration in your program. The file `ESB32.BAS` in `\ESSBASE\API\INCLUDE` contains the correct function and constant declarations.

The `ESB32.BAS` file is required for 32-bit Visual Basic programs because 32-bit programs use two bytes per character instead of one. Since some Essbase Visual Basic data structures use one-byte data types, `ESB32.BAS` changes these to use two bytes.

Add `ESB32.BAS` to your project, or, if you are using your own file for global declarations, copy the declarations included in `ESB32.BAS` into it.

### Passing the Instance Handle or Context Handle

You must pass the instance handle returned by the initial call to EsbInit() in calls to EsbLogin() or EsbTerm(). You must pass the context handle returned by EsbLogin() in any function calls associated with a specific login.

### Handling the Return Code

All Essbase API functions return a status code of type ESB_STS_T. A return code of zero indicates that the function was executed successfully, and a non-zero value indicates an error condition. A full list of error return constants is contained in the header files `ESSERROR.H` and `ESBERROR.BAS`.

Note: You should always check the return code from any Essbase API function. If the return code is non-zero, any pointers or values returned by the function are undefined.

## Visual Basic API Function Call Sequence

The API requires that your program call certain functions before others. The basic ordering rules are:

- A program must call EsbInit() before calling any other API functions.
- A program must call EsbLogin() or EsbAutoLogin() before calling any API functions which require a context handle argument (most API functions). Additionally, if you need to create a local context for API object functions to use, you must call EsbCreateLocalContext() before calling any API functions requiring a context handle argument.
- Some API functions require an active application and database to be set. This is done by having the program call EsbSetActive() or EsbAutoLogin() before they are called.
- A program must not pass a context handle to any API functions after calling EsbLogout() for that handle.
- A program must not call any API functions except EsbInit() after calling EsbTerm().

### A Typical API Task Sequence

This is the typical order of operations for a simple API application:

1. Create and initialize an ESB_INIT_T structure.
2. Initialize the API by calling EsbInit().
3. Log in to the required server by calling EsbLogin() or EsbAutoLogin().
4. Select an active application and database by calling EsbSetActive() or EsbAutoLogin().
5. Retrieve (or lock) data by calling EsbReport() or related functions.
6. Update data by calling EsbUpdate() or related functions.
7. Recalculate the database by calling EsbCalc() or related functions.
8. Produce reports against the data by calling EsbReport() or related functions.
9. Log out from the server by calling EsbLogout().
10. Terminate the API by calling EsbTerm().

## Initializing the Visual Basic API

A program must initialize the API by calling the EsbInit() function before calling any other Essbase API functions. EsbInit() initializes all internal API state variables, and also allows you to tailor the API to your program's requirements.

The only function you can call before EsbInit() is EsbGetAPIVersion().

The calling program must pass the EsbInit() function an initialization structure. This structure is defined in `ESB32.BAS` as ESB_INIT_T. It contains a series of fields which are used to customize the API and set up certain API defaults. You must declare an instance of this structure and initialize the relevant fields before calling EsbInit().

The EsbInit() function returns an instance handle, which should then be passed as an argument to the API login function.

#### Declaring the Visual Basic Initialization Structure

In Visual Basic, you can declare the initialization structure in the procedure that uses it. The structure shown below is taken from `ESB32.BAS`.

    Type ESB_INIT_T
       Version     As Long                  ' version of API
       MaxHandles  As Integer               ' maximum number of context handles required
       LocalPath   As String * ESB_PATHLEN  ' local path to use for file operations
       MessageFile As String * ESB_PATHLEN  ' full path name of message database file
       HelpFile    As String * ESB_PATHLEN  ' full path name of help file
       ClientError As Integer               ' allows use of a pseudo client error handler
       ErrorStack  As Integer               ' size of the error message stack
    End Type

In this code, the fields are defined as follows:

- The `Version` field indicates the current version of the API.
- The `MaxHandles` field contains the maximum number of simultaneous context handles required by the program. The default is 255.
- The `LocalPath` field contains the default local path name to use for file and object operations on the client. The default is `$ARBORPATH\CLIENT`, where `$ARBORPATH` is defined by the ARBORPATH environment variable. If the `ARBORPATH` variable is not set, `\ARBOR32\CLIENT` is used.
- The `MessageFile` field contains the fully qualified path name of the message database file `ESSBASE.MDB`. If this is not set explicitly, Essbase first tries to use the fully qualified path in the `ARBORMSGPATH` environment variable. Otherwise, it uses `$ARBORPATH\BIN\ESSBASE.MDB`, where `$ARBORPATH` is defined by the ARBORPATH environment variable. If the `ARBORPATH` variable is not set, an error message is returned at run time.
- The `HelpFile` field contains the fully-qualified name of the application help file. The default help file is `\$ARBORPATH\BIN\ESSAPIW.HLP`, where `$ARBORPATH` is defined by the ARBORPATH environment variable. If the `ARBORPATH` variable is not set, the help file name is set to null.
- The `ClientError` field contains the value ESB_FALSE or ESB_TRUE to indicate whether EsbGetMessage() can be used to retrieve messages.
- The `ErrorStack` field contains the size of the message stack used by EsbGetMessage(). The default value is 100.

## Logging In to an Essbase Server

In general, the first thing your program should do after calling EsbInit() is to prompt the user for a server name, user name, and password (or use predefined defaults), then attempt to log in to that server by calling EsbLogin(). Alternatively, use the encapsulated login function, EsbAutoLogin(). If this call is successful, then the returned context handle should be stored and used for all subsequent API calls.

## Selecting an Active Application and Database

In addition to the context handle, the login functions also return a list of the applications and databases to which the logged in user has access (a program can obtain this list at any time by calling the EsbListDatabases() function). The program allows the user to select a specific application and database by calling the EsbSetActive() function.

If EsbAutoLogin() is used to log in, it can optionally set the active application and database.

To get information about an Essbase application (for example, whether or not it is already loaded), call the EsbGetApplicationState() or EsbGetApplicationInfo() functions. To get information about a specific database, call the EsbGetDatabaseState() or EsbGetDatabaseInfo() functions. You can call these functions before setting the active application and database.

## Retrieving and Updating Data

### Retrieving Data

To retrieve data from an Essbase database, either for reporting or for subsequent updating, your program needs to use a report specification. Report specifications can be in the form of a single text string (if it is less than 64 Kbytes in length), a series of text strings, or a file. Report files can reside either on the client machine, or on the Essbase server.

#### Sending a Report Specification as a Single String

To send a report specification as a single string, have the program call EsbReport() and pass the entire report string, not greater than 32 Kbytes long, as an argument. If the `Output` flag is set to TRUE in the call to EsbReport(), the program must also read the returned report data by calling EsbGetString() repeatedly until a NULL string is returned. The returned data can then be displayed, written to a file, or printed, as required.

#### Sending a Report Specification as a Series of Strings

To send a report specification as a series of strings, first call EsbBeginReport(), then call EsbSendString() repeatedly to send each string in the report specification (note that in Windows, each individual string must not be greater than 32 Kbytes long). Finally, terminate the report specification by calling EsbEndReport(). If the `Output` flag is set to TRUE in the call to EsbBeginReport(), the program must also read the returned report data by calling EsbGetString() repeatedly until a NULL string is returned. The returned data can then be displayed, written to a file, or printed, as required.

#### Sending a File as a Report Specification

To send a file as a report specification, use the EsbReportFile() function, passing the report file name. If the `Output` flag is set to TRUE in the call to EsbReportFile(), the program must also read the returned report data by calling EsbGetString() repeatedly until a NULL string is returned. The returned data can then be displayed, written to a file, or printed, as required.

### Updating Data

To update data in the database, you should first lock the blocks in the database which you are going to update. To do this, do either of the following:

- Send a report specification as described above, with the `Output` flag set to TRUE and `Lock` flag also set to TRUE. The data output by this report can be modified, then sent back to the database as an update.
- Alternatively, if there is new or modified data ready to be loaded, a program can first use it as a report specification to lock the data blocks by setting the `Output` flag to FALSE and setting the `Lock` flag to TRUE when calling the appropriate report function.

The database can be updated either from a single string, a series of strings, or a file. Update data files can reside either on the client machine, or on the Essbase server.

#### Sending Update Data as a Single String

To send an update as a single string, call EsbUpdate() passing the entire string as an argument. (Note that in Windows, the string must not be greater than 32 Kbytes long). Set the `Store` flag to TRUE in the call to EsbUpdate() so that the database will be updated. If the `Unlock` flag is also set to TRUE, any locked data blocks in the database will be unlocked once the data is updated, to allow other users to update those blocks.

#### Sending Update Data as a Series of Strings

To send an update as a series of strings, first call EsbBeginUpdate(), then call EsbSendString() repeatedly to send all the data (note that in Windows, each individual data string must not be greater than 32 Kbytes long). Finally, terminate the update by calling EsbEndUpdate(). Set the `Store` flag to TRUE in the call to EsbUpdate() so that the database will be updated. If the `Unlock` flag is also set to TRUE, any locked data blocks in the database will be unlocked once the data is updated.

#### Sending Update Data as a File

To send an update as a file, use the EsbUpdateFile() function, passing the data file name. Set the `Store` flag to TRUE in the call to EsbUpdate() so that the database will be updated. If the `Unlock` flag is also set to TRUE, any locked data blocks in the database will be unlocked once the data is updated.

## Recalculating the Database

After updating any data in the database it is essential to perform a recalculation to ensure that the consolidated totals are correct. To recalculate a database, you can either perform the default calculation, or send a specific calculation script. You can also set a calculation script to be the default calc script. Calc scripts can be sent either as a single string, a series of strings, or a file. Calc script files can reside either on the client machine, or on the Essbase server.

### Sending a Calc Script as a Single String

To send a calc script as a single string, call EsbCalc() passing the entire string as an argument (note that the string must not be greater than 32 Kbytes long). Set the `Calculate` flag to TRUE in the call to EsbCalc() so that the calc script will be executed. You then need to check on the progress of the calculation at regular intervals.

### Sending a Calc Script as a Series of Strings

To send a calc script as a series of strings, first call EsbBeginCalc(), then call EsbSendString() repeatedly to send all the strings in the calc script (note that each individual string must not be greater than 32 Kbytes long). Finally, terminate the script by calling EsbEndCalc(). Set the `Calculate` flag to TRUE in the call to EsbBeginCalc() so that the database will be recalculated. You then need to check on the progress of the calculation at regular intervals.

### Sending a Calc Script as a File

To send a calc script as a file, use the EsbCalcFile() function, passing the calc script file name. Set the `Calculate` flag to TRUE in the call to EsbCalcFile() so that the database will be recalculated. You then need to check on the progress of the calculation at regular intervals.

### Using the Default Calc Script

To recalculate a database using the current default calc script, use the EsbDefaultCalc() function. To set the default calc script for a database, use EsbSetDefaultCalc(), passing the calc script as a single string. To set the default calc script from a file, use the EsbSetDefaultCalcFile() function, passing the calc script file name. Use EsbGetProcessState() to determine when the calculation is finished.

### Checking the Progress of Calculations

After a database calculation is started, you should check the progress of the calculation at regular intervals (five seconds is recommended) by calling the EsbGetProcessState() function. This function returns a structure indicating the calculation state. You should call EsbGetProcessState() until it indicates that the calculation is complete or that an error has occurred. You may also cancel a calculation in progress at any time by using the EsbCancelProcess() function.

Warning: While a calculation is in progress, do not attempt to call any API functions other than EsbGetProcessState() or EsbCancelProcess() using the same context handle, until the calc operation has completed successfully or has been canceled. After EsbGetProcessState() indicates the calc has finished, your program may continue performing other API operations with that context handle.

## Logging Out from the Essbase Server and Terminating the API

When all database operations are complete, the application should first log out by calling EsbLogout(). This frees up any internal resources reserved within the database, and may also free the login port on the server for use by another user.

When an application program is about to terminate, it should call the EsbTerm() function, passing the instance handle which was returned from the original call to EsbInit(). This releases all resources used by the Essbase API. After calling this function, no other API calls can be made, unless EsbInit() is called again to reinitialize the API.

## Common Problems and Solutions

If the API function you are calling is likely to generate a lot of data in a return parameter, such as calls to EsbLogin(), EsbAutoLogin(), EsbGetString(), and EsbListDatabases(), you should ensure that you have reserved enough buffer space to receive the data.

This section might assist you in identifying and solving problems.

Problem Your program is generating protection faults.

Solution If you are having this problem with a Visual Basic program, check the declared indirection level of any pointers being passed to the API.

Problem Your program generates an Essbase error when calling an API function.

Solution Most of the Essbase error messages are self-explanatory, and it should be fairly obvious where the problem lies. However a couple of common errors to watch out for are (%n indicates a message argument which is replaced by a context-specific string):

- "NULL argument (%1) passed to ESSAPI function %2". This message indicates that one or more arguments passed to the API function %2 were NULL. The %1 indicates the number of the first null argument (1-based).
- "Invalid call sequence in ESSAPI function %1". This message indicates that you have made a call to an API function (%1) when another function call was required. For example, if you have executed a report function, such as EsbReport(), make sure that you call EsbGetString() repeatedly until a NULL string is returned; or if you have executed a calculation function, e.g. EsbCalc(), that you repeatedly check the calculation state by calling EsbGetProcessState() until the returned value indicates that the calc has completed.
- "Local operation not allowed in ESSAPI function %s". You have passed a local context handle to a function which does not allow it; use a login context handle instead.
- "Cannot open message database %s". The message database is not accessible on the machine on which your program is running. Ensure that the message database is where Essbase expects to find it. Essbase first examines the `MessagePath` field of the initialization structure passed to EsbInit(), then the directory and file name specified by the ARBORMSGPATH environment variable, and finally, the `$ARBORPATH\BIN` directory where $ARBORPATH is an environment variable. If the message database is not available in any of these directories, Essbase returns an error message at run time. Verify which setting Essbase uses, and then verify that the message database is located where specified. See Integrating Essbase with Your Product for more information.

Problem Your program is consistently receiving an Essbase error return code from an API function, but no message is displayed, or a message saying "No message for message #%1 in message database" is generated.

Solution Certain internal API errors cannot display a message, typically because the user's context information is not available when the message occurs. In these cases, make a note of the error code returned from the function, then refer to the list of error messages in the header file `ESSERROR.H` to find the corresponding message text.