Calling a PostgreSQL stored procedure in a transaction group fails

naturesbeast · January 30, 2023, 4:06pm

I have a transaction group that is calling a PostrgreSQL stored procedure that is getting an error message:
Error during group execution. Error registering parameter 'datetime'. Verify that the parameter name and type are correct. Method org.postgresql.jdbc.PgCallableStatement.setObject(String,Object,int) is not yet implemented.
The definition of the stored procedure:
create or replace procedure sp_record_kpi ( serialNo int , kpiId int , dateTime timestamptz , sampleValue float )

I've modified the translator to use timestamptz for the the Datetime mapping.
When I call the stored procedure in python (using runPrepUpdate), the call works flawlessly. Any help would be greatly appreciated. Thanks.

pturmel · January 30, 2023, 4:43pm

Your column name has a capital "T", probably to avoid clashing with the reserved word "datetime". If you use anything but lower case in PostgreSQL identifiers (table names, column names, schema names, etc.), you must quote the name with double quotes.

PGriffith · January 30, 2023, 5:16pm

Huh. I don't really understand how Postgres' driver works at all, to be honest.
PgCallableStatement throws the 'not yet implemented' error for any of the 'setX' methods:

github.com

pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgCallableStatement.java#L762


      
          }
          
          public void setBinaryStream(String parameterName, @Nullable InputStream x, int length) throws SQLException {
            throw Driver.notImplemented(this.getClass(), "setBinaryStream(String,InputStream,int)");
          }
          
          public void setObject(String parameterName, @Nullable Object x, int targetSqlType, int scale)
              throws SQLException {
            throw Driver.notImplemented(this.getClass(), "setObject(String,Object,int,int)");
          }
          
          public void setObject(String parameterName, @Nullable Object x, int targetSqlType) throws SQLException {
            throw Driver.notImplemented(this.getClass(), "setObject(String,Object,int)");
          }
          
          public void setObject(String parameterName, @Nullable Object x) throws SQLException {
            throw Driver.notImplemented(this.getClass(), "setObject(String,Object)");
          }
          
          public void setCharacterStream(String parameterName, @Nullable Reader reader, int length)
              throws SQLException {

But that's how they say to use it in their documentation:
https://jdbc.postgresql.org/documentation/callproc/

naturesbeast · January 30, 2023, 5:16pm

It doesn't really seem to matter what I do, I'm receiving that same message. I created a test procedure:
create or replace procedure sp_test ( string_data varchar(255) ) as $$ begin return; end; $$ language plpgsql;
and get the same error when the transaction group executes

naturesbeast · January 30, 2023, 5:17pm

That's exactly what I'm finding during my tests.

pturmel · January 30, 2023, 5:20pm

Try using parameter numbers instead of names.

naturesbeast · January 30, 2023, 5:25pm

Do you mean this?

naturesbeast · February 1, 2023, 2:19pm

After about an hour on the phone with tech support, I was told that there's a known bug with executing a stored procedure with named parameters in Postgresql from a transaction group. The developers are unable to fix the issue. Ugh.