RDBPROD: Calling an external procedure/function using JDBC gives %RDB-E-BAD_DB_HANDLE, Invalid Database Handle (Doc ID 1442398.1)

Last updated on AUGUST 03, 2016

Applies to:

Oracle Rdb Server on OpenVMS - Version 7.2 to 7.2.5.1 [Release 7.2]
Oracle JDBC for Rdb - Version 7.2 and later
Information in this document applies to any platform.

Symptoms

In an Rdb database, an external function is defined that itself uses the same Rdb database. The external function performs some DML and a COMMIT or ROLLBACK.

This external function is used in a JAVA program or application that uses JDBC to connect to the database using a normal RdbThin server. The first session that uses this external function in the database works correctly each time the function is used.

However a second (and third and so on) session always results in the error:

%RDB-E-BAD_DB_HANDLE, Invalid Database Handle


An example of such a function:

create function GET_COUNT
  STORED NAME GET_COUNT
  (IN INTEGER by reference)
  returns CHAR(60) by reference;
  external name GET_COUNT
  location 'SYS$SHARE:GET_COUNT.EXE'
  language COBOL
  general parameter style
  not variant;

The function starts a transaction, does a select count(*) from rdb$database and commits the transaction. The return value of the function is the error message resulting from the SQL statement. This example is used only to demonstrate the error message. The function, written in COBOL, is:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. GET_COUNT.
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SPECIAL-NAMES.
           DECIMAL-POINT IS COMMA.
       DATA DIVISION.
      *
       WORKING-STORAGE SECTION.
      *
       01 SQLCODE-WS PIC S9(04) COMP.
       01 SQLCODE-ED PIC -(04)9.
       01 SQLMESSAGE-WS PIC X(300).
       01 LENGTH-SQLMESSAGE-WS PIC S9(04) COMP.
       01 NULL-INDICATORS.
       02 NULL-INDICATOR OCCURS 100 PIC S9(04) COMP.
      *
       LINKAGE SECTION.
       01 TEKST-LK PIC X(60).
       01 NUMBER-LK PIC 9(07) COMP.
      *
       PROCEDURE DIVISION USING TEKST-LK,NUMBER-LK.
      *
       MAIN SECTION.
      *
       get_count_entry.
       move spaces to sqlmessage-ws
      *
       move "trans" to tekst-lk.
       call "c$read_only" using sqlcode-ws.
         perform check_sql
      *
       move "read" to tekst-lk.
       call "c$check_database" using sqlcode-ws, number-lk.
         perform check_sql
      *
       call "c$commit" using sqlcode-ws.
      *
       get-count-exit.
       exit program.

       check-sql.
       move sqlcode-ws to sqlcode-ed
       call "sql$get_error_text" using
           by descriptor sqlmessage-ws
           by reference length-sqlmessage-ws
[M]    move sqlmessage-ws(1:lengte-sqlmessage-ws)
           to tekst-lk(10:).

In the function, the following SQLMOD procedures are used:

MODULE SQL_COBOL
LANGUAGE COBOL
AUTHORIZATION RDB$DBHANDLE
---
DECLARE ALIAS FOR FILENAME 'COUNT_DB'
--
PROCEDURE C$READ_ONLY
SQLCODE;
SET TRANSACTION READ ONLY;
--
PROCEDURE C$CHECK_DATABASE
SQLCODE
DATA_COUNT INTEGER;
SELECT count(*) INTO data_count from rdb$database;
--
PROCEDURE C$COMMIT
SQLCODE;
COMMIT;

It is compiled and linked with:

$      sql$mod:==$sql$mod
$!
$      cobol/ANSI/LIST/COPY/DEBUG GET_COUNT.COB
$      SQL$MOD SQL_COBOL.SQLMOD
$!
$      link/share GET_COUNT, SQL_COBOL, sql$user/lib, sys$input/option
SYMBOL_VECTOR=(GET_COUNT=procedure)
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
$

Using interactive SQL, calling this function returns:

$ mc sql$
SQL> att 'f count_db';
SQL> select get_count(5) from rdb$database;

 read     %SYSTEM-S-NORMAL, normal successful completion
1 row selected
SQL>

If executing a JAVA program or application that uses a normal JDBC Thinserver (type RdbThinsrv), then the first connection using this server to execute the SQL statement:

select get_count(5) from rdb$database

returns:

read     %SYSTEM-S-NORMAL, normal successful completion

Using the same JAVA program or application in another session while the first session is still active, which causes both sessions to use the same Thinserver process, results in:

read     %RDB-E-BAD_DB_HANDLE, Invalid Database Handle

This happens for a 3th, 4th, and subsequent sessions, as long as the same Thinserver process is used.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms