Hibernate Community • View topic - Passing array as parameter to a pl/sql function

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Passing array as parameter to a pl/sql function

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

rvallee

Post subject: Passing array as parameter to a pl/sql function

Posted: Wed Sep 20, 2006 4:22 pm

Newbie

Joined: Wed Sep 20, 2006 4:06 pm
Posts: 3

I'm having a hard time passing an array parameter to a pl/sql function on a PostgreSQL server.

My pl/sql function's signature is:

Code:

FUNCTION find_professionals(int4[], int4[], int4[], int4, int4).

I tried using some combination of the following (where divisions, types and areas are java.util.Collection)

Code:

final String sql = "SELECT * FROM find_professionals(:divisions, :types, :areas, 25, 0)";
        final SQLQuery query = session.getSession().createSQLQuery(sql);
        query.setParameterList("divisions", divisions);
        query.setParameterList("types", types);
        query.setParameterList("areas", areas);

But I always get the following error:

Quote:

ERROR: function find_professionals(character varying, character varying, character varying, integer, integer) does not exist

Which is somewhat normal if my arrays are transformed into "character varying", which seems to be the signature to a string (varchar type).

The correct query (which works fine when querying from the SQL window in pgAdmin) I should be sending to PostgreSQL is:

Code:

SELECT organizationid FROM find_professionals('{15416}', '{4}', '{21,22,23}', NULL, NULL) AS tab(organizationid int4)

Thanks in advance, this is a pretty major problem for me right now.

P.S.: post might be in double. I pressed 'submit' then cancelled to tick email notification and submitted again.

Top

rvallee

Post subject: Found it

Posted: Thu Sep 21, 2006 1:33 pm

Newbie

Joined: Wed Sep 20, 2006 4:06 pm
Posts: 3

Well the problem was only with using request.setParameter of any kind.

So instead of:

Code:

final String sql = "SELECT * FROM find_professionals(:divisions, :types, :areas, 25, 0)";
final SQLQuery query = session.getSession().createSQLQuery(sql);
query.setParameterList("divisions", divisions);
query.setParameterList("types", types);
query.setParameterList("areas", areas);

This should be used:

Code:

final String sql = "SELECT * FROM find_professionals(" + transformCollectionToSqlArray(divisions) + ", " + transformCollectionToSqlArray(types) + ", " + transformCollectionToSqlArrayareas) + ", " 25, 0)";
final SQLQuery query = session.getSession().createSQLQuery(sql);

(Do not take as is, I retyped it not exactly as it is in my code, as I use a StringBuffer).

Where transformCollectionToSqlArray would simply take a collection and create a string with the sql array format: func('{1,2,3}', '{4,5,6}');

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum