Trigger to PostgreSQL JDBC writes successfuly first time, fails after

Question

I have a project for sending data from DB2 on an IBM system over to a PostgreSQL server on a RHEL system. I am using a trigger that sends information to a data queue, which then gets read and sent over to the PostgreSQL server using a SQL statement through a JDBC connection on RPGLE.

The code is (more or less) as follows (I had to remove actual column and table names for security reasons):

dcl-proc doPostgreConn export;
  dcl-pi doPostgreConn like(connection) end-pi;
  //Code to change and set CLASSPATH variable, if necessary is here
  //...

  prop = JDBC_Properties();
  JDBC_setProp(prop: 'user'         : 'user');
  JDBC_setProp(prop: 'password'     : 'password');
  JDBC_setProp(prop: 'databaseName' : 'database');
  JDBC_setProp(prop: 'loggerLevel'  : 'TRACE'     );
  JDBC_setProp(prop: 'loggerFile'   : '/home/PostgreSQL/log');

  pgconn = JDBC_ConnProp('org.postgresql.Driver'
                     :'jdbc:postgresql://[some_IP]:5432/database'
                     : prop );
  JDBC_freeProp(prop);
  return pgconn;
end-proc;


dcl-proc doPGWriteMyTable export;
  dcl-pi doPGWriteMyTable like(success);
    i#schm char(10);
    i#rec char(334);
  end-pi;

  dcl-ds record extname('MYTABLE') end-ds;
  dcl-s prepStmtTxt varchar(10000);

  record = i#rec;

  pgconn = doPostgreConn;
  if pgconn = *NULL;
    //Custom Error Handling
  endif;

  prepStmtTxt = 'INSERT INTO ' + %trim(i#schm) + '.MYTABLE ' +
    '  VALUES (?, ?, ?) ';

  if PGWriteMYTABLEPrep = *NULL;

    PGWriteMYTABLEPrep = JDBC_PrepStmt(pgconn:prepStmtTxt);

    if PGWriteMYTABLEPrep = *NULL;
        
    endif;
  endif;

  JDBC_setString (PGWriteMYTABLEPrep: 1: StrCol);
  JDBC_setDecimal (PGWriteMYTABLEPrep: 2: DecCol);
  JDBC_setDate (PGWriteMYTABLEPrep: 75: DateCol);

  if JDBC_execPrepUpd( PGWriteMYTABLEPrep ) < 0;
    //Custom Error Handling
  endif;

  JDBC_Close(pgconn);

  return *on;
end-proc;


dcl-proc doPGDeleteMYTABLE export;
  dcl-pi doPGDeleteMYTABLE like(success);
    i#schm char(10);
    i#rec char(334);
  end-pi;

  dcl-ds record extname('MYTABLE') end-ds;
  dcl-s sqlstmt varchar(32000);
  dcl-s deleteSuccess ind;

  record = i#rec;

  sqlstmt = 'DELETE FROM ' + %trim(i#schm) + '.MYTABLE WHERE '; //Basically the key

  pgconn = doPostgreConn;
  if JDBC_ExecUpd(pgconn:sqlstmt) < 0;
    //Custom error handling
  endif;

  DoPostgreClose(pgconn);

  return *on;
end-proc;

The data queue read program essentially calls DoPGDeleteMYTABLE and then DoPGWriteMYTABLE, in that order (There is no unique key, so we simply delete all of the matching records on the PostgreSQL server and then re-add them).

The problem is, while the data queue read program is running, the first loop works perfectly fine, and then fails. The order goes like this:

1. Record updated
2. Delete any existing PG records: successful
3. Prepare the write statement: successful
4. Write any existing DB2 records to PG: successful
5. Record updated
6. Delete any existing PG records: successful
7. Prepare the statement: successful
8. Write any existing DB2 records to PG: unsuccessful
9. repeat 5 through 8 until data queue job is restarted

The errors I receive are not very helpful. The job log on the AS400 simply tells me

org.postgresql.util.PSQLException: This connection has been closed.

even though I can see the open connection on the PostgreSQL server, and closing it from RPGLE does still work.

The JDBC job log does not tell me any information around the time the write happens. It just says that the prepare was successful, and then nothing.

Version information:

IBM OS 7.4

PostgreSQL 13.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 11.2.1 20220127 (Red Hat 11.2.1-9), 64-bit

PostgreSQL JDBC Driver postgresql-42.2.19

RPGLE is utilizing Scott Klement's JDBCR4

Nothing I have found online has yet to help with the issue. If there is anything else I can provide or try in order to get more information, please let me know.

Answer 1

I finally got it figured out. It was a dumb thing that I didn't realize I needed to do - turns out you have to free the prepared statement after using it the first time.

Using JDBCR4, you just call (using my example)

JDBC_FreePrepStmt(PGWriteMYTABLEPrep);

Which looks like this, if anybody needs info that doesn't use JDBCR4:

      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      * JDBC_FreePrepStmt(): Free prepared statement
      *
      *    prep = (input) Prepared Statement to Free
      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     P JDBC_FreePrepStmt...
     P                 B                   export
     D JDBC_FreePrepStmt...
     D                 PI
     D   prep                              like(PreparedStatement)
      /free
         stmt_close(prep);
         DeleteLocalRef(JNIENV_P: prep);
         prep = *NULL;
      /end-free
     P                 E

In the end, a very poorly worded error, with a very simple solution.