2 Replies Latest reply on Jun 11, 2014 8:12 AM by shawkins

Stored procedure out parameter types

lewis.watson Jun 9, 2014 10:54 AM

Hello All,

I have a simple PostgreSQL stored procedure called delete_case which deletes rows from a table based on a primary key

CREATE OR REPLACE FUNCTION delete_case(integer)
  RETURNS void AS
$BODY$DELETE FROM cases WHERE id=$1
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION delete_case(integer)
  OWNER TO postgres;

I can import into Teiid Designer easily enough. Note: "RETURNS void" has been interpreted as "returnValue: object"

I then wrap the source model in a view model

With the following transformation procedure

BEGIN
    SELECT Cases1.delete_case.returnValue FROM Cases1.delete_case WHERE Cases1.delete_case.id = Cases1_view.delete_case.id;
END

However, when I preview the procedure I get the following errors:

SQL Results:

TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 Cases1: TEIID11004 Error executing statement(s): {?= call "public"."delete_case"(?)}

Server Console:

15:21:03,934 INFO  [org.teiid.CONNECTOR] (Worker24_QueryProcessorQueue108) PostgreSQLExecutionFactory Commit=true;DatabaseProductName=PostgreSQL;DatabaseProductVersion=9.3.4;DriverMajorVersion=9;DriverMajorVersion=3;DriverName=PostgreSQL Native Driver;DriverVersion=PostgreSQL 9.3 JDBC4.1 (build 1101);IsolationLevel=2
15:21:03,958 WARN  [org.teiid.CONNECTOR] (Worker24_QueryProcessorQueue108) Connector worker process failed for atomic-request=Ro8OcSrf7oSC.0.3.28: org.teiid.translator.TranslatorException: TEIID11004 Error executing statement(s): {?= call "public"."delete_case"(?)}
    at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)
    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:312) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:113) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_60]
    at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_60]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_60]
    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_60]
Caused by: org.postgresql.util.PSQLException: A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=1111 however type java.sql.Types=2000 was registered.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:468)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
    at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:787)
    at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)
    ... 12 more

15:21:03,961 WARN  [org.teiid.PROCESSOR] (Worker23_QueryProcessorQueue109) TEIID30020 Processing exception for request Ro8OcSrf7oSC.0 'TEIID30504 Cases1: TEIID11004 Error executing statement(s): {?= call "public"."delete_case"(?)}'. Originally TeiidProcessingException 'A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=1111 however type java.sql.Types=2000 was registered.' AbstractJdbc2Statement.java:468. Enable more detailed logging to see the entire stacktrace

From what I can gather from the error the procedure is returning a value of type java.sql.Types=1111 (aka OTHER) whereas it was expecting java.sql.Types=2000 (aka JAVA_OBJECT). The PostgreSQL delete_case is returning void so I'm not sure why a return parameter is there in the first place. I've tried removing the parameter from both the source and the view model but then Teiid produces a "TEIID30492 Command must project at least one symbol " error. All I really want the virtual procedure to do is call the underlying PostgreSQL procedure and report any errors.

I've had a look at the procedure argument settings for both the view and the source models but nothing I've tried so far gets round the error. Am I on the right track or am I going about this in the wrong way?

My eventual goal with this is to create a model view table called "cases" which will have SELECT, INSERT, UPDATE, DELETE procedures that call appropriate PostgreSQL stored procedures.

Regards

Lewis

1. Re: Stored procedure out parameter types

lewis.watson Jun 11, 2014 4:01 AM (in response to lewis.watson)

Hello again, sorry for the delay in the update but I seem to be restricted to one post a day on this forum.

As an experiment I changed my stored procedure to always return an int

CREATE OR REPLACE FUNCTION delete_case2(integer)

  RETURNS integer AS

$BODY$DELETE FROM cases WHERE id=$1;

SELECT 0;

$BODY$

  LANGUAGE sql VOLATILE

  COST 100;

ALTER FUNCTION delete_case2(integer)

  OWNER TO postgres;

And changed my view model to expect an int

I got a similar error

16:25:23,999 INFO  [org.teiid.CONNECTOR] (Worker26_QueryProcessorQueue112) PostgreSQLExecutionFactory Commit=true;DatabaseProductName=PostgreSQL;DatabaseProductVersion=9.3.4;DriverMajorVersion=9;DriverMajorVersion=3;DriverName=PostgreSQL Native Driver;DriverVersion=PostgreSQL 9.3 JDBC4.1 (build 1101);IsolationLevel=2

16:25:24,073 WARN  [org.teiid.CONNECTOR] (Worker26_QueryProcessorQueue112) Connector worker process failed for atomic-request=ZpDvg4HUT2XD.0.3.29: org.teiid.translator.TranslatorException: TEIID11004 Error executing statement(s): {?= call "public"."delete_case"(?)}

    at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)

    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:312) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:113) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_60]

    at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]

    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_60]

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_60]

    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_60]

Caused by: org.postgresql.util.PSQLException: A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=1111 however type java.sql.Types=4 was registered.

    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:468)

    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)

    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)

    at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:787)

    at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)

    ... 12 more

16:25:24,076 WARN  [org.teiid.PROCESSOR] (Worker25_QueryProcessorQueue113) TEIID30020 Processing exception for request ZpDvg4HUT2XD.0 'TEIID30504 Cases1: TEIID11004 Error executing statement(s): {?= call "public"."delete_case"(?)}'. Originally TeiidProcessingException 'A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=1111 however type java.sql.Types=4 was registered.' AbstractJdbc2Statement.java:468. Enable more detailed logging to see the entire stacktrace.

The main difference being that it is now getting a java.sql.Types=4 (aka INTEGER) back but for some reason its still expecting a java.sql.Types=2000 (aka JAVA_OBJECT).

I'm not sure where JAVA_OBJECT is coming from...

However! When I create the same stored procedure using PostgreSQL's plpgsql language

CREATE OR REPLACE FUNCTION delete_case_plpgsql(param_id integer)
  RETURNS text AS
$BODY$
BEGIN
    DELETE FROM cases WHERE id = param_id;
    RETURN 'done';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

I can successfully call it from Teiid as long as it returns something. In this case I'm simply returning 'done'

Transformation:

BEGIN
    SELECT * FROM (EXEC Cases_source.delete_case_plpgsql2(param_id => Cases_view.delete_case_plpgsql2.param_id)) AS returnValue;
END

Regards

Lewis

2. Re: Stored procedure out parameter types

shawkins Jun 11, 2014 8:12 AM (in response to lewis.watson)

> From what I can gather from the error the procedure is returning a value of type java.sql.Types=1111 (aka OTHER) whereas it was expecting java.sql.Types=2000 (aka JAVA_OBJECT)

A change to the pg translator may be needed for Teiid to bind an unknown object type as OTHER. I'll see if I can confirm that.

> The main difference being that it is now getting a java.sql.Types=4 (aka INTEGER) back but for some reason its still expecting a java.sql.Types=2000 (aka JAVA_OBJECT).

From the error message it looks like it is still requiring 1111, which would be unexpected given that you changed the procedure to return an integer. So it's not clear what is going on there unless pg is inferring that it's still a void result.

> I can successfully call it from Teiid as long as it returns something. In this case I'm simply returning 'done'

Yes, it's expected that the procedure call should work. In the void case, could you try simply removing the returnValue from the physical stored procedure?

It also looks like the Designer metadata for the virtual procedure is a little off. It shouldn't have both a result set and return value, so an issue may be needed there.
Actions

Go to original post