One more: Function does not exist error
От | Shah, Anuj R |
---|---|
Тема | One more: Function does not exist error |
Дата | |
Msg-id | 42C7E766869C42408F0360B7BF0CBD9B024916B3@pnlmse27.pnl.gov обсуждение исходный текст |
Список | pgsql-jdbc |
Hi, I am using jdbc to talk to a PostgresQL 8.0 database. I am using the same driver as provided in the jdbc directory of the installation. Here's my function definition. CREATE OR REPLACE FUNCTION "public"."updatevaluesfordataset" (text, text, text, text, text) RETURNS integer AS $body$ /* New function body */ DECLARE datasetid ALIAS FOR $1; valuesString ALIAS FOR $2; delimited ALIAS FOR $3; colStart ALIAS FOR $4; totColumns ALIAS FOR $5; column_values text[]; LB1 integer; UB1 integer; updateStatement text; BEGIN column_values := string_to_array(valuesString, '\b'); IF totColumns == 1 THEN LB1 := array_lower(column_values, 1); UB1 := array_upper(column_values, 1); FOR i IN LB1..UB1 LOOP updateStatement := "UPDATE ds_data SET col" || qoute_literal(colStart) || " = '" || quote_literal(column_values[i]) || " WHERE datasetid =" || quote_literal(datasetid) || " \"rowNo\" =" || quote_literal(i) EXECUTE updateStatement; END LOOP; ELSE END IF; RETURN 1; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; And my java code that is supposed to call it. Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/cce_new_ds","us er",""); CallableStatement cs = conn.prepareCall("{? = call updatevaluesfordataset(?,?,?,?,?)}"); cs.registerOutParameter(1, Types.INTEGER); cs.setString(2, "1080"); cs.setString(3, "2"); cs.setString(4, "FirstValue"); cs.setString(5, "1080"); cs.setString(6, "1080"); cs.execute(); cs.close(); conn.close(); However on running this java code, I get an error saying java.sql.SQLException: ERROR: function updatevaluesfordataset(character varying, character varying, character varying, character varying, character varying) does not exist at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu torImpl.java:1365) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp l.java:1160) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java: 172) .... I tried changing my parameters to varchar as well and it still would give me the same error. Any ideas? The other unrelated thing is that it was a little unintuitive to me as to how to create a stored procedure rather than a function? I do not want to return any value while executing these updates. Is there a way? I am sure there would be one, I just haven 't been able to figure it out. Thanks a bunch, Anuj
В списке pgsql-jdbc по дате отправления: