Re: Re: Inputs Appreciated - Calling pgsql stored proc with user defined sql types from java using pgsql-jdbc

You might try this driver: https://github.com/impossibl/pgjdbc-ng

It supports UDTs and even registerOutParameter according to the JDBC 4.1 spec.

On Mar 28, 2014, at 4:24 AM, Venkateswar Reddy Melachervu <vmelachervu(at)gmail(dot)com> wrote:

> Dave,
> When I tried with type map approach as suggested by you, it throws a run time exception : "(org.postgresql.util.PSQLException) org.postgresql.util.PSQLException: Custom type maps are not supported". Snipper below
> Connection physicalDBConn = ((javax.sql.PooledConnection)dbConn).getConnection();
> Map<String,Class<?>> udtMap = new HashMap<>();
> udtMap.put("type_customer_details_for_subscription",
> com.brillium.vcossPortalServer.datalayer.pgsql.udt.CreatedCustomerMVCAccountDetails.class);
> udtMap.put("type_mvc_plans_details",
> com.brillium.vcossPortalServer.datalayer.pgsql.udt.MVCPlanDetails.class);
> udtMap.put("type_mvc_countries_map",
> com.brillium.vcossPortalServer.datalayer.pgsql.udt.CountryCodesMap.class);
> //myMap.put("mySchemaName.ATHLETES", Athletes.class);
> physicalDBConn.setTypeMap(udtMap);
>
> V
>
>
> On Fri, Mar 28, 2014 at 2:26 PM, Venkateswar Reddy Melachervu <vmelachervu(at)gmail(dot)com> wrote:
> Thanks again. Here is the snipper causing the cast error with jdbc41 pgsql driver
>
> //code snippet -begin
> dbConn = VCOSSDBUtils.getVCOSSDBConnection();
> dbConn.setAutoCommit(true);
> Connection physicalDBConn = ((javax.sql.PooledConnection)dbConn).getConnection(); //get the real conn from the pooled conn
> ((org.postgresql.PGConnection)physicalDBConn).addDataType("type_customer_details_for_subscription",
> com.brillium.vcossPortalServer.datalayer.pgsql.udt.CreatedCustomerMVCAccountDetails.class);
> ((org.postgresql.PGConnection)physicalDBConn).addDataType("type_mvc_plans_details",
> com.brillium.vcossPortalServer.datalayer.pgsql.udt.MVCPlanDetails.class);
> ((org.postgresql.PGConnection)physicalDBConn).addDataType("type_mvc_countries_map",
> com.brillium.vcossPortalServer.datalayer.pgsql.udt.CountryCodesMap.class);
> //code snippet - end
>
> When I run, the exception occurs at first cat statement above!
>
> V
>
>
> On Fri, Mar 28, 2014 at 12:48 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> The cast should work but while you wait for someone to confirm/comment you
> might just try using the setTypeMap(...) method of the Jdbc4Connection
>
> Supplying minimal code showing the problem might help too...
>
> David J.
>
>
> Venkateswar Reddy Melachervu wrote
> > ok. Thanks for the quick response Dave.
> >
> > This post provides a for achieving what my solution requires - passing
> > user
> > defined data types to stored
> > procedures<https://groups.google.com/forum/#!topic/pgsql.interfaces.jdbc/Tj-LUrhFowQ>.
> > I followed this post ( of course, adapted it to suit for jdbc41 pgsql
> > driver). Per this to add the data type, the jdbc connection needs to be
> > cast to pgconnection and this casting is resulting in a run time exception
> > - (java.lang.ClassCastException) java.lang.ClassCastException:
> > org.postgresql.jdbc4.Jdbc4Connection cannot be cast to
> > org.postgresql.PGConnection.
> >
> > This was not the case with pgsql8.3 with jdbc3/4 pgsql driver.
> >
> > Any pointer/inputs to resolve this are highly appreciated.
> >
> > V
> >
> >
> > On Fri, Mar 28, 2014 at 10:52 AM, David Johnston <
>
> > polobo@
>
> > > wrote:
> >
> >> Venkateswar Reddy Melachervu wrote
> >> > Hello All,
> >> > Any inputs/pointers for the below are highly appreciated.
> >> >
> >> > Problem
> >> > When trying to conn.registerOutParameter(2,
> >> > Types.STRUCT,"type_customer_details_for_subscription") for a pgsql
> >> stored
> >> > proc with user defined type from java1.7 using jdbc41 pgsql driver to
> >> > pgsql9.3 is resulting in error when type maps are used -
> >> > SQLFeatureNotSupportedException: Method
> >> >
> >> org.postgresql.jdbc4.Jdbc4CallableStatement.registerOutParameter(int,int,String)
> >> > is not yet implemented.
> >> >
> >> > Somehow I was under the impression that struct for sql is base type and
> >> > pgsql jdbc driver can support it. But going by above exception message,
> >> it
> >> > it not. am I doing something wrong (have to create some struct pointers
> >> > etc. before registering the type) or it is the way pgsql jdbc driver is
> >> > built. Is there any way out - any pointer?
> >> >
> >> > --
> >> > Regards,
> >> > Venkateswar Reddy Melachervu
> >> > "...dare to dream, care to win..."
> >> > www.linkedin.com/in/vmelachervu
> >>
> >> registerOutParameter(...) is not implemented - it doesn't matter what the
> >> actual argument values are.
> >>
> >> Sorry, no help on the solution.
> >>
> >> David J.
> >>
> >>
> >>
> >>
> >>
> >> --
> >> View this message in context:
> >> http://postgresql.1045698.n5.nabble.com/Inputs-Appreciated-Calling-pgsql-stored-proc-with-user-defined-sql-types-from-java-using-pgsql-jdbc-tp5797743p5797744.html
> >> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
> >>
> >>
> >> --
> >> Sent via pgsql-jdbc mailing list (
>
> > pgsql-jdbc@
>
> > )
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-jdbc
> >>
> >
> >
> >
> > --
> > Regards,
> > Venkateswar Reddy Melachervu
> > "...dare to dream, care to win..."
> > www.linkedin.com/in/vmelachervu
>
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Inputs-Appreciated-Calling-pgsql-stored-proc-with-user-defined-sql-types-from-java-using-pgsql-jdbc-tp5797743p5797751.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
> --
> Regards,
> Venkateswar Reddy Melachervu
> "...dare to dream, care to win..."
> www.linkedin.com/in/vmelachervu
>
>
>
> --
> Regards,
> Venkateswar Reddy Melachervu
> "...dare to dream, care to win..."
> www.linkedin.com/in/vmelachervu