Обсуждение: arrays as parameters in callableStatements

I've got a PL/pgSQL stored function in my database and I'm trying to figure out how to call it from my admin script.  Some of the parameters are ArrayList objects of strings and it returns an array that I want to read to determine what the function actually did.  Here is the function header(?):

CREATE OR REPLACE FUNCTION add_thing(
varchar,     -- name:varchar              ($1)
integer,     -- itemNum:integer               ($2)
varchar,     -- owner:varchar           ($3)
varchar,     -- image_url:varchar         ($4)
varchar,     -- specs_url:varchar        ($5)
varchar[],   -- categories[]:varchar           ($6)
varchar[],   -- component_name[]:varchar ($7)
varchar[],   -- component_id[]:varchar   ($8)
date[],      -- component_date[]:date            ($9)
varchar[]    -- componentImage_url[]:varchar   ($10)
) RETURNS varchar[] as '

Here is how I'm trying to call it in Java:

            // Turn transactions off.
            connection.setAutoCommit(false);
            // Procedure call.
            CallableStatement stmt = connection.prepareCall("{ ? = call add_thing( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");
            stmt.registerOutParameter(1, Types.ARRAY);
            stmt.setString(2, name);
            stmt.setInt(3, itemNum);
            stmt.setString(4, owner);
            stmt.setString(5, item_url);
            stmt.setString(6, specs);
            stmt.setObject(7, compNames);
            stmt.setObject(8, compIds);
            stmt.setObject(9, compDates);
            stmt.setObject(10, compURL);
            stmt.execute();
            String[] results = stmt.getArray(1);
            stmt.close();

I'm getting a compiler error "Incompatible Types" on the second to last line.  Looking at the API to me it seems like that's how it should work.  I'm thinking of trying getResultSet() instead, but since this is only the compile stage I'm asking for some advice in case I'm way off base (in particular, aside from the error mentioned I'm concerned about IN params 7-10 being written correctly).  Thanks.

-Jason

---

Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.

On Sun, 14 May 2006, J. wrote:

> I've got a PL/pgSQL stored function in my database and I'm trying to
> figure out how to call it from my admin script.  Some of the parameters
> are ArrayList objects of strings and it returns an array that I want to
> read to determine what the function actually did.  Here is the function
> header(?):
>
>              String[] results = stmt.getArray(1);
>
>  I'm getting a compiler error "Incompatible Types" on the second to last
> line.

getArray returns a java.sql.Array, not a String[].  Also the JDBC driver
does not know how to interpret array parameters that are not sent in via
setArray or setObject with a java.sql.Array.

Kris Jurka

Thanks, I thought that might be an issue, but looking at the API, I don't see how to convert an array or ArrayList to an Array object or back.  Any tips on where to look?

Kris Jurka <books@ejurka.com> wrote:

On Sun, 14 May 2006, J. wrote:

> I've got a PL/pgSQL stored function in my database and I'm trying to
> figure out how to call it from my admin script. Some of the parameters
> are ArrayList objects of strings and it returns an array that I want to
> read to determine what the function actually did. Here is the function
> header(?):
>
> String[] results = stmt.getArray(1);
>
> I'm getting a compiler error "Incompatible Types" on the second to last
> line.

getArray returns a java.sql.Array, not a String[]. Also the JDBC driver
does not know how to interpret array parameters that are not sent in via
setArray or setObject with a java.sql.Array.

Kris Jurka


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

---

Get amazing travel prices for air and hotel in one click on Yahoo! FareChase

On Sun, 14 May 2006, J. wrote:

> Thanks, I thought that might be an issue, but looking at the API, I
> don't see how to convert an array or ArrayList to an Array object or
> back.  Any tips on where to look?
>

For java.sql.Array -> type[] it's easy, the Array.getArray call will
return a String[] or int[] or whatever typed array is appropriate.

The type[] -> java.sql.Array side is more complicated, searching this
lists archives will give you some example, but the basic idea is that you
create a class that implements java.sql.Array and can therefore accept any
input format you need.

Kris Jurka