Problem accessing indexed tables - xHarbour & OTC Mediator

[JockMullin]

Greetings.

I have been experimenting with using the OTC Mediator database server v4.2.6.0 to access tables residing in MySQL 5.0 database. I am running xHarbour 1.0 beta and also 0.99.7.

Basically everything works very well, with the weird exception that if I index a table (index on ...), it works fine and I can access the data via the index in the session that created the index completely normally.

However, when attempting to re-open a previously-indexed table in subsequent sessions, I get the following error:

Error SRV/1401 Server Error (ORDRCONF: No table) : ChkRspErr

Error at ...: DBUSEAREA(0) in Module:
Called from : AKORDERCONF(16) in Module: akorderconf.prg

where line 16 in akorderconf.prg is the use command:
use ordrconf via 'MEDNTX' exclusive. It acts like the table no longer exists. Yet it is there and I can open it and access the data using MySQL table browser no problem.

The only thing I can do at that point is drop the table and rebuild without the index. As long as it is not indexed I can reconnect normally.

I can readily program new apps without using indexes; using SQL to select and sequence records works great. However porting existing apps to Mediator/MySQL would be a lot easier with indexes, obviously.

Brgds
Jock

 

[TonHu]

Is that index unique? as that's a requirement for a MySQL prime index, AFAIK.
Maybe you should open the index in that same USE statement.

HTH
TonHu

 

[JockMullin]

Hi, TonHu

Many thanks for your response.

The index in question is being created by a clipper command (index on orderno to indxname).

As I understand it, the prime index is built and maintained by Mediator, and it is by the recno field that mediator adds to the layout automatically so it can emulate record numbers. Any user-created indexes are secondary.

I have tried including the index in the use statement but the error still occurs. I have also tried creating the index with the 'unique' option, but the same thing still happens.

My current thinking is MySQL is set to the default database engine of InnoDB, and maybe Mediator needs its tables to be the older MyISAM engine. However that is just a shot in the dark. Meanwhile working without indexes is OK for my current project because the filter on server and use of SQL select with order clause are efficient.

However down the road I want to migrate some legacy apps and I don't want to have to recode if possible.

Brgds
Jock