Eclipse Community Forums: EclipseLink » getting PSQLException Can't infer the SQL type to use with Native Query call

Home » Eclipse Projects » EclipseLink » getting PSQLException Can't infer the SQL type to use with Native Query call(getting PSQLException Can't infer the SQL type to use with Native Query call)

getting PSQLException Can't infer the SQL type to use with Native Query call [message #667885]

Mon, 02 May 2011 11:16

Eclipse User

In trying to call a postgresql 8.4 stored function I am passing a class as well as a List (I have to save a master record as well as the detail records). This is being done with a native query using EclipseLink 2.1. However, I am getting back a message for the first item:

PSQLException: Can't infer the SQL type to use for an instance of com.hwcs.veri.shared.dto.MyClass. Use setObject() with an explicit Types value to specify the type to use.

I am calling the query setting up the parameter with:

query.setParameter( 1, myClass );

The receiving stored function has the following:

FUNCTION perform_schema.saveInfo( newrec boolean,
myClass <user defined type>,
myDtl <user defined type>[] )

I am unclear how to setup the pass fromm eclipselink to postgresql. Does anyone have any suggestions or can point to docs which speak to this specifically?

Thanks

Re: getting PSQLException Can't infer the SQL type to use with Native Query call [message #667924 is a reply to message #667885]

Mon, 02 May 2011 15:09

Eclipse User

You cannot pass a Java object directly to PostgreSQL. Custom types in PostgreSQL require usage of java.sql.Struct types in JDBC.

You need to pass a Struct type directly, or use EclipseLink's object-relational data-type support to map the Struct to a class using an ObjectRelationalDataTypeDescriptor.

Re: getting PSQLException Can't infer the SQL type to use with Native Query call [message #667939 is a reply to message #667924]

Mon, 02 May 2011 16:38

Eclipse User

I was wondering if you knew any docs or examples that discusses this?

Thanks

Re: getting PSQLException Can't infer the SQL type to use with Native Query call [message #668118 is a reply to message #667939]

Tue, 03 May 2011 15:55

Eclipse User

I have been chasing my tail trying to pull this together. Everything I read comes in snippets and most examples point to using stored procedures not stored functions which is what is used with Postgresql (cf. http://www.eclipse.org/forums/index.php?t=msg&goto=65118 4&S=2e33754641d6b4641dbf92071b8add6f). In the end I cannot pull this together and pass an object (as well as an array of objects) into Postgresql. Is there any tutorial that covers this ground thoroughly?

Re: getting PSQLException Can't infer the SQL type to use with Native Query call [message #668377 is a reply to message #668118]

Thu, 05 May 2011 09:41

Eclipse User

Usage of stored functions and usage of user defined data-types are pretty rare, so there is not a lot of documentation of this.

What do you have so far, and what error are you getting?

There is better support for structs in the trunk/2.3 stream. There some tests for calling stored functions with structs in Oracle through PLSQL, similar, but not exactly like PostgreSQL will be.

http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/tr unk/jpa/eclipselink.jpa.test/src/org/eclipse/persistence/tes ting/models/jpa/plsql/

http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/tr unk/jpa/eclipselink.jpa.test/src/org/eclipse/persistence/tes ting/tests/jpa/plsql/

Previous Topic:	OrderBy problem when traversing properties
Next Topic:	@ElementCollection List<String> dont delete first record on merge

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 12:10:52 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter