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.