Mediator server and xHarbour COM interface

Mediator server is a software which virtualizes SQL database server in such a way that navigational access to database is possible.

SQL databases work using SQL queries which produce the result sets. On the other hand, many programming languages expect ISAM (Indexed Sequential Access Method) access to the data they process. This includes such languages as Clipper, Harbour, xHarbour and Delphi BDE components using dBase, Paradox or FoxPro files. ISAM access is record-oriented. ISAM application see data as a set of tables with attached indexes. It processes data one record at a time and the most common operation are:

• moving to the top of open table
• moving to the bottom of open table
• moving to the next or previous record
• finding the record using specified index
• adding record
• modifying current record

Using ISAM data, all this operations are quick and simple - there is no need to buffer data on the client workstation for performing backward moves.

Mediator server was designed to provide you with the ideal illusion of working over ISAM data while in reality the data is located in Oracle, MS SQL, IBM DB2, PostgreSQL or MySQL database server.

This means, for example, that your DBF-based application may work with SQL databases in exactly the same way as with DBF files without the need to write and optimize SQL queries.

At the moment of this writing Mediator server can be accessed from the following environments:

• Borland Delphi
• Any COM-enabled environment
• Clipper 5.2E and 5.3B
• xHarbour
• Harbour

Mediator and xHarbour COM interface was designed to enable easy access to Mediator server and DBF files from any environment capable of creating and accessing COM (ActiveX) objects. This includes most of the modern programming languages such as:

• Borland Delphi and C++
• Java
• ASP+
• Visual Basic
• Visual C++
• C#
• Visual FoxPro
• scripting languages such as Visual Basic Script
• and many, many more

There are two types of COM objects used to interface with Mediator and xHarbour:

• XHbCom.HbConnection - the connection object. This object exposes all Mediator and xHarbour functions which are not related to workarea operation.
  
• XHbCom.HbTable - table object. It exposes all Mediator and xHarbour functions which operate on workareas. Use it for interacting with Mediator-managed tables and DBF tables managed by xHarbour kernel. When working with Mediator server this object can also be used to execute SQL queries.

Mediator COM interface is based upon xHarbour kernel. Most of the time, the mentioned objects provide a direct interface to existing Mediator and xHarbour functions. Using xHarbour kernel as a foundation for Mediator COM interface enables nearly 100% compatibility between DBF-based applications and Mediator-based applications.

Use these objects to write programs that access Mediator and DBF data in an easy, safe and unified way.

Mediator COM interface brings the new level of connectivity to all wishing to access Mediator server or DBF files. While it is possible to access Mediator-managed data via general purpose SQL interfaces such as ODBC, using our COM interface is preferable due to the following reasons:

• access methods familiar to all Clipper/xHarbour programmers
• easy and compatible lock management via appropriate HbTable methods (RLock, Unlock e.t.c.)
• no need to worry about additional Mediator fields such as RECNO, IS_DELETED or expression index fields. They are all managed for you automatically
• very efficient ISAM-like access to Mediator and DBF tables
• unified way of access to DBF and SQL data via Mediator makes it simple to write universal applications which can work with both DBF and SQL data. Save your time, look into the feature.

Making it simple: we offer you the possibility to use well-known Clipper/xHarbour/Mediator functions and logic from virtually any Windows programming language you wish. You are not longer limited to Clipper, xHarbour or Harbour.

If you've used xbApi or Mediator client installer for installing files, the necessary registration steps has already been done for you by installer.

If you've copied files manually or you experience problems (objects are not visible) please perform the following installations steps. For simplicity we assume Mediator client is installed in c:\medcl directory. Replace this directory with the one that match your installation environment.

Registering Mediator/xHarbour COM interface library in the system.
For the COM objects to be visible for applications they need to be registered within the system. To register XHbCom library, go to c:\mdecl\xbapi\bin\buildXXX directory and execute from console window
     regsvr32 xhbcom

If server has been succesfully registered, you should get the confirmation message.
If you get 'Function LoadLibrary("xhbcom") was not successful ..' message, make sure the following required dll files are present in the current directory:
     medhbw.dll
     medhbudf.dll
     harbour.dll

If above files are present but still unsuccessful with registration, make sure they are all from the same original directory of Mediator/xbapi installation.

Using Mediator COM objects

There are two types of COM objects used to interface with Mediator and xHarbour:

• XHbCom.HbConnection - the connection object. This object exposes all Mediator and xHarbour functions which are not related to workarea operation.
• XHbCom.HbTable - table object. It exposes all Mediator and xHarbour functions which operate on workareas. Use it for interacting with Mediator-managed tables and DBF tables managed by xHarbour kernel. When working with Mediator server this object can also be used to execute SQL queries.

The exact method used to create COM objects depends on the programming environment. Below you will find an example written in Visual Basic. In 'sample' directory of COM objects installation there are more examples using different programming environments.

Here are general rules for using Mediator COM objects:

• a) Create exactly one HbConnection object instance in your application. Do it before creating any HbTable object instances. One HbConnection instance is required even if you use only DBF tables.
• b) In most cases you will create one instance of HbTable component for each DBF or Mediator table you access. It is possible, however, to use the single instance of HbTable object to sequentially open and access many DBF and Mediator tables. This is exactly like Clipper/xHarbour workarea works. Usually you open only one table in a given workarea and close this table before end of the program. If you like however, you can close the table and open another one in the same workarea as many times as you wish. THbTable object is just the object abstraction of the xHarbour workarea and it can be used in the similar way.
• c) Always assign the existing HbConnection instance to HbTable.Connection property for every created HbTable instance. While not strictly necessary with this version of Mediator COM objects, this explicitly associates the HbTable object with one HbConnection object. This will ensure your programs will continue to work with future versions of COM library where multiply HbConnection objects will be allowed.
• d) To access columns in the HbTable object record use HbTable.Value("colname") or HbTable.Value(fieldNum) property. While you can also use HbTable.FieldGet and hbTable.FieldPut methods, HbTable.Value property will be faster and easier to use in most cases.

Below simple Visual Basic example illustrating how to open and access DBF table.
Note: you need to select Project->References and check 'xhbcom Type library' in order to the new HbConnection and HbTable types be vsible in your VB application.

Private Sub Test()
   Dim conn As HbConnection
   Dim cust As HbTable

   Set conn = New HbConnection ' create connection object
   Set cust = New HbTable ' create table object

   cust.Connection = conn ' associate table with connection
   cust.DbUseArea "DBFNTX", "Customer" ' open customer.dbf
   If cust.NetErr Then
      MsgBox ("Cannot open Customer.dbf")
      End
   End If

   cust.DbSetIndex "cust_rd" ' order by registration date

   While Not cust.EOF
      MsgBox(cust.Value("name") + " Reg date: " + Str(cust.Value("regdate")))
      cust.DbSkip
   Wend

   cust.DbCloseArea ' close table

   ' destroy objects
   Set cust = Nothing
   Set conn = Nothing

End Sub


All HbConnection and HbTable methods directly correspond to the same-named Mediator and xHarbour functions. The reference part of this documentation includes the formal description of each method available, including parameters and return value types and their attributes. For detailed description of each method please refer to Mediator, xHarbour and eventually Clipper documentation.

There are also two special properties of HbTable object implemented to make objects usage easier.

HbTable.Connection property

This property should be set to the dispatch interface of the HbConnection object.
Always assign the existing HbConnection instance to HbTable.Connection property for every created HbTable instance. While not strictly necessary with this version of Mediator COM objects, this explicitly associates the HbTable object with one HbConnection object. This will ensure your programs will continue to work with future versions of COM library where multiply HbConnection objects will be allowed.

VB example:

Set conn = New HbConnection ' create connection object
Set cust = New HbTable ' create table object
cust.Connection = conn ' associate table with connection

HbTable.Value property

Arguments: record column name (case insensitive) or column number (first column has number 1)

Use this property to access values stored in columns of the current record. This is a read/write property, so it can be used for both retrieving and setting the column value. Usage of this property is preferred over usage of HbTable.FieldGet and hbTable.FieldPut methods. It is faster and easier to use. Whenever possible use Value property with column name argument. Numeric argument is useful when copying full records of one table to identical records of the other table. See samples\vb6\copytab for example.

VB example:

MsgBox( "NAME=" + cust.Value("name") )
cust.Value("name") = "New name"
cust(1).Value = "New New name"

Default xHarbour error handling method (HbConnection.ErrorSilent=false)

By default, when xHarbour error is encountered during method execution, the appropriate error object is created and method returns E_FAIL. Depending on your programming environment, this error may be handled differently. In most programming environments, an error or exception is raised, so you can easily trap an error.

For instance in VB6 you can use:
  On Error GoTo myerrorhandler
  or
  On Error Resume Next
statement and check Err.Number property to check the error.

VB6 example:

On Error Resume Next
tab.DbUseArea "DBFNTX", "MYTABLE"

If Err.Number <> 0 Then
   MsgBox('Error opening table MYTABLE '+ Err.Description)
   Exit Sub
End If


In most environments it is convenient to use structured exception handling to catch xHarbour errors.

Text description of xHarbour error passed with exception object contains the basic description of the error. Additionally, HbConnection object contains properties which provides you with the detailed information about the last error. These are:

• HbConnection.Error -> lWasError
  Returns true if there was an xHarbour error and error info is available. Use HbConnection.ErrorReset to reset an error condition.
• HbConnection.Description -> cDescription
  Returns the text describing error.
• HbConnection.Filename -> cFilename
  Returns the name of the file associated with the error, if any.
• HbConnection.Operation -> cOperation
  Returns the name of the xHarbour operation executed when error was encountered.
• HbConnection.Subsystem -> cSubsystem
  Returns the name of the subsystem which raised an error.
• HbConnection.Flags -> nFlags
  Returns the error flags. See CA-Clipper/xHarbour reference for error flags description.
• HbConnection.Gencode -> nGencode
  Returns the error general code. See CA-Clipper/xHarbour reference for gencode description.
• HbConnection.Gencode -> nOscode
  Returns the error operating system code. See CA-Clipper/xHarbour reference for oscode description.
• HbConnection.Severity -> nSeverity
  Returns the error severity code. See CA-Clipper/xHarbour reference for severity description.
• HbConnection.Subcode -> nSubcode
  Returns the error subcode. See CA-Clipper/xHarbour reference for subcode description.
• HbConnection.Procline -> nProcline
  Returns the line of PRG code where error was encountered.

You can use all the described methods to better handle xHarbour error or provide application user with additional error information.

Silent error handling method

By setting HbConnection.ErrorSilent property to true, you switch off the default COM error handling method. With this setting, all HbTable and HbConnection methods will return S_OK (success) on return, even in case when xHarbour error was encountered. That means no exception will be raised by programming environment to signal an error.

Using this approach, you need to check for eventual errors by checking HbConnection.Error property after calling any method which can generate an error. If HbConnection.Error returns true, it means an error was encountered and detailed error info is available through the methods described in the previous paragraph. Error information is available until next error happens, which overwrites the information with a new one, or HbConnection.ErrorReset method is executed to reset an error condition.

VB6 example of silent (manual) error handling:

conn.ErrorSilent = true
tab.DbUseArea "DBFNTX", "MYTABLE"

If conn.Error Then
   MsgBox('Error opening table MYTABLE')
   MsgBox (conn.ErrorDescription)
   MsgBox (conn.ErrorFilename)
   MsgBox (conn.ErrorOperation)
   MsgBox (conn.ErrorSubsystem)
   MsgBox (conn.ErrorProcname)
   MsgBox (Str(conn.ErrorFlags))
   MsgBox (Str(conn.ErrorGencode))
   MsgBox (Str(conn.ErrorOscode))
   MsgBox (Str(conn.ErrorProcline))
   MsgBox (Str(conn.ErrorSeverity))
   MsgBox (Str(conn.ErrorSubcode))
   conn.ErrorReset ' reset error condition
End If


Note: only xHarbour (and Mediator) errors can be handled in silent mode. If an internal automation error happen, it will be reported in standard way used for COM errors, regardless of HbConnection.ErrorSilent setting. For non-xHarbour/Mediator errors no info is available via HbConnection.Error... properties and HbConnection.Error property is not set.

Internally, all parameters and return values are COM variants. Most of the programming environments (such as VB) will automatically wrap your arguments into Variant before calling object method. Return Variant values will also be processed by runtime to extract the value of the expected type. In some programming environments (such as VC++) you will need to put your parameters into COM variants yourself before calling any method.

Some methods accept and return array parameters. There are several rules which must be followed:

• 1. All arrays returned are Variant arrays
• 2. All [in,out] array parameters must be Variant arrays
• 3. [in] array parameters can be of any base type

[in] parameters cannot be modified by the xHarbour/Mediator function to which they are passed to
[in,out] parameters can be modified by the xHarbour/Mediator function to which they are passed to

To determine, whether parameter is [in] or [in,out] please refer to the reference part of this documentation.

Generally, there are two methods of COM object creation

• a) Include type information from xhbcom.dll into your programming environment and create objects using your programming language mechanism
• b) Dynamically create COM objects using CreateObject() or similar COM methods.

Some programming environments (such as Visual Basic) allow both methods to be used while other environments (like Visual Basic Script) allow only dynamic object creation. Whenever possible, method a) is preferable as it provides programming environment with as much type information as possible about the used objects. VB, for instance, use this information to display hints about the available methods and parameters. Also, it performs same basic checks of your code, which are not possible if objects are created dynamically.

VB example with static object declarations:

Dim conn As HbConnection
Dim cust As HbTable

Set conn = New HbConnection ' create connection object
Set cust = New HbTable ' create table object

cust.Connection = conn ' associate table with connection
cust.DbUseArea "DBFNTX", "Customer" ' open customer.dbf
. . .
cust.DbCloseArea ' close table

' destroy objects
Set cust = Nothing
Set conn = Nothing


VB example with anonymous objects declarations and dynamic creation:

Dim conn As Object
Dim cust As Object

Set conn = CreateObject("Xhbcom.HbConnection") ' create connection object
Set cust = CreateObject("Xhbcom.HbTable") ' create table object

cust.Connection = conn ' associate table with connection
cust.DbUseArea "DBFNTX", "Customer" ' open customer.dbf
. . .
cust.DbCloseArea ' close table

' destroy objects
Set cust = Nothing
Set conn = Nothing


When using static object declarations such as described in previous chapter (preferred), you need to import xhbcom.dll type information into your VB project. To import type information do the following:

• 1. Select Project->References. If Mediator COM objects are properly registered in a system, you should see 'xhbcom Type library' among other libraries.
• 2. Check 'xhbcom Type library' position

Now HbConnection (XHBCOMLib.HbConnection) and HbTable (XHBCOMLib.HbTable) types should be visible in your VB application.

Debug applications which use Mediator COM objects in exactly the same way as any other database applications.

There is one think you should remember when debugging applications which open connection to Mediator server. To ensure maximum realiability, HbConnection object send a keep-alive message to Mediator server every 40 seconds. This allows Mediator server to quickly detect network or client application failures, close the connection and free all resources allocated by application. When debugging, the thread sending keep-alive messages can be stopped. When no more keep-alive messages are sent, Mediator server will close the connection and you will receive the "Network connection lost" exception. To eliminate this problem, call

HbConnection.MedDiscTm(65535);

right after opening the connection with the MedLogin() function. This will turn off the keep-alive mechanism and Mediator server will not disconnect application even if it is stopped in debugger. For production applications, remove the above call to ensure best realiability in multi-user environments.

If you wish to integrate xHarbour code with your application using Mediator COM objects, you can do so. This process include several steps.

• 1. Put your xHarbour code into DLL file. MedHbUDF directory contains a simple example illustrating how to turn hbudf.prg file into DLL using xHarbour and Visual C++.
• 2. Load your DLL file into application before using its internal functions. This can be done by using HbConnection.LibLoad method.
• 3. Call the required DLL functions using HbConnection.HbExecute method.
• 4. Unload your DLL file when no longer needed. Use HbConnection.LibFree method for this purpose.

If you wish, you may load more than one DLL file into your application.

MedHbUdf.Dll

This special DLL file is automatically loaded into application when HbConnection object is created. It is also automatically unloaded on cleanup. If you wish MedHbUdf.Dll to be automatically loaded, it should be available in current application directory or in any directory included in system path. If MedHbUdf.dll is not found, it is not loaded but no error is raised.

Due to its automatic load/unload management and ready-to-use makefile, MedHbUdf file is a place where you can easily add xHarbour code required by your application. If your expression indexes make use of user defined functions (UDFs) this is a proposed place to put their code.

Note: If your expression indexes contain references to UDFs and their code is not available, when first attempt to update table data will generate an error.

All HbConnection and HbTable methods directly correspond to the same-named Mediator and xHarbour functions. The reference part of this documentation includes the formal description of each method available, including parameters and return value types and their attributes. For detailed description of each method please refer to Mediator, xHarbour and eventually Clipper documentation.

Please note, that some methods exposed by Mediator COM objects may slightly differ in parameter list from the original Clipper/xHarbour functions. For instance, Mediator function MedIsFltr() does not require passing workarea number as parameter. It is determined by the object whose MedIsFltr() method is activated. When in doubt, always consult this reference.

For your convenience, function reference is grouped by object (HbConnection, HbTable) and lists separately xHarbour and Mediator methods.

Two special properties of HbTable object (Hbtable.Connection and HbTable.Value) are described in 'Special object properties' chapter.

Reference part lists all available methods with their parameters and return values.

Parameters.

Optional parameters are surrounded by brackets such as in [<cParam>]

Parameter types:
	Variant	- expression of any type expected
	Character	- string expected
	Number	- number (integer or float) expected
	Date	- date expected
	Logical	- logical value expected
Parameter attributes:
	array	- array parameter expected
	in	- [in] parameter expected
	in/out	- [in,out] parameter expected
	optional	- parameter is optional
Return value types:
	Variant	- function returns expression of any type
	Character	- function returns string value
	Number	- function returns number (integer or float) value
	Date	- function returns date value
	Logical	- function returns logical value
	NIL	- function does not return any value
Return value attributes:
	array	- function returns an array

Copyright (c) 2004 Era-Software s.c., All rights reserved.