Active users in Interbase 6

[Dali]

Hello. I'm developing a small application which must be run against a pre-existent Interbase 6 database. At this time, the app works fine (Lazarus+Zeos 6.6.6), but I'd like to have controlled the active connections to avoid data corruption if my customer's old app tries to access any data while modifications are taking place.

In Firebird 2.1+ that's quite easy to achieve by accessing the metadata, but I don't know how to do it with this old server. I browsed all the RBL$ tables, but I couldn't find anything useful for this matter.

What I'm doing now is to monitor the Windows processes, looking for their app process name and halting if it's present, but I don't like this approach, which, in fact, does not guarantee that the connections aren't still active after the application shutdown.

Since upgrading it is not an option, because their old app would stop working if the server is not Interbase 6, any ideas, anyone?

[seawolf]

I know upgrading old apps can ve very difficult, but Interbase 6 = Firebird 1.
So can't you try upgrading to Firebird 1.x? I think changes should be very fews (apart the new dll).
Apart form that which kind transact isolation level have you set? You can set in a way to serialize any commit.

Anyway Are there no way to establish a communication channel between apss?

[Dali]

Thanks for dropping by so quickly, seawolf. I'm stuck with this one and I really appreciate any help. I searched everywhere, but I can't find a method, dirty or not, which can tell me the number of current attachments to the underlying database. Maybe some of the libraries or command-line tools bundled with Interbase? I just don't know.

Firebird 1.0 won't do, because the situation would be pretty much the same. I'd need 2.1+ for the MON$ATTACHMENTS system table being provided.

I don't know what kind of transaction isolation level is being used in the old app, but I have not much hope, since the database is being corrupted day after day.

Also, they have an UDF file with tons of custom functions and no source code at all, which I can hence not recompile, and it fails randomly when I try to host the database under Firebird (strong dependencies with the GDS32.dll, I guess).

This is a huge, monolithical, prehistorical app which has no support anymore because the company behind it went down some years ago. I'm adding some new custom functions for convenience and minimize errors, but trying to communicate anyhow with it or replicate its whole functionality is off the record, because the customer won't pay that much for the titanic work that it would imply.

By the way, I just forgot to point out that it's a multi-terminal app, so the approach of looking into server processes won't work, since there might still be some client machines connected. Of course, my app is working, and the users have been warned to exit the old application in every machine before starting the new one, but I can't trust them, and neither I can trust the connections are closed for real when the last application closes.

[seawolf]

Check the TZConnection.TransactIsolationLevel property
By default is set to tiReadCommited but changing it is possbile modify
the Transaction behaviour in a more strict way

[Dali]

Yes, mine is set to serializable, but, as I said, I still don't know what their old app's isolation level is set to (I guess it is set to "none", if their access component even allows any setting, which I ignore). Quick example: The last time I dared to run a batch process using my app with theirs still open (even if it was doing nothing at all!!), the database ended with many duplicate primary keys in the table my app was updating... How? I don't know. I swear my app didn't insert twice the same rows. The same process worked as a charm when it was run as "standalone".

I was digging into the GDS32.DLL API, and it's possible to get the number of attachments. They even include some example coded in plain C... Which, honestly, I don't know how to translate into Lazarus because, for a start, I don't even know how to load this DLL in Lazarus. Do I need translated headers to do it? What "uses" clauses do I need? What if I want to do the same thing under Linux? Maybe anyone ported this already and I can take a rest?

Here is the code:

Code: Select all

case isc_info_svc_svr_db_info:
{
  printf ("Database information:\n");
  do {
    switch (*p++)
    {
      case isc_spb_dbname:
      {
        /* Database names in use */
        path_length = (unsigned short)
          isc_vax_integer(p, sizeof(unsigned short));
        p += sizeof (unsigned short);
        buffer = (char*) malloc (path_length);
        strncpy (buffer, p, path_length);
        buffer [path_length] = ’\0’;
        printf ("Database in use: %s\n", buffer);
        p += path_length;
        break;
      }
      case isc_spb_num_att:
      {
        /* Num Attachments */
        unsigned long nAttachments;
        p+= sizeof (unsigned short);
        nAttachments = (unsigned long)
        isc_vax_integer(p, sizeof (unsigned long));
        printf ("\tNumber of attachments: %d\n",
          nAttachments);
        p += sizeof(unsigned long);
        break;
      }
      case isc_spb_num_db:
      {
        /* Num databases */
        unsigned long nDatabases;
        p+= sizeof (unsigned short);
        nDatabases = (unsigned long)
        isc_vax_integer(p, sizeof(unsigned long));
        printf ("\tNumber of databases: %d\n",
          nDatabases);
        p += sizeof(unsigned long);
        break;
      }
    }
  } while (*p != isc_info_flag_end);
  break;
}

[seawolf]

Unfortunately services (like isc_info_svc_svr_db_info) are, at the moment, not supported on Zeos. Thus, if you can wait some days I can take at look into the code and try to add it. Otherwise there is no solution I can suggest you

[Dali]

Of course I can wait, but please don't feel forced to do anything if you can spend your time doing any other thing which would benefit more people (or you, BTW).

Though it'd be cool to have an easy way to accomplish this, you have been extremely kind already, the thing is already running, and the users have been warned much more than once: It's not my fault, Interbase 6 is dead since ten years ago, the people who coded their app didn't care much about concurrency, stability, consistancy or integrity, so don't run it if there are other active concurrent connections to the same database, back the data up often, etc. (I tried it again today with both apps open simultaneously and, guess what? Duplicate primary keys again, which, of course rendered both apps unusable...)