Postgresql Procedure not getting called from JDBC

Question

I am working with postgresql procedures and trying to call a procedure from my JDBC program. But getting runtime exception saying procedure doesn't exist eventhough I cross-checked and verified that the procedure name is correct. This is what I am doing

 CallableStatement   cs = connection.prepareCall("{call proc1()}");
 cs.executeUpdate();

And here's my proc1 procedure

   create or replace procedure proc1()
as

begin

insert into employee_info values(1,'johnny','1111',43);
-----

end

This is what the output is

   Connection Failed! ERROR: function proc1() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I dont understand why its not working eventhough proc1() exists in database. And what should i Cast?

oh.Is it? Then how should I create procedure. Actually I am familiar with mysql , oracle and other databases. and i am new to postgres..Can you direct me to a good link which explains postgresql procedures — Stunner
– Stunner, Commented Oct 16, 2013 at 10:32
@a_horse_with_no_name No. The procedure is working fine when I execute it manually in database. So nothing wrong with procedure syntax. — Stunner
– Stunner, Commented Oct 16, 2013 at 10:49
Then you are not using Postgres. Postgres does not have procedures, only functions, and thus it only has a create or replace function statement. — user330315
– user330315, Commented Oct 16, 2013 at 10:59
create procedure is a syntax accepted by PostgresPlus, a postgres fork aimed at oracle compatibility. — Daniel Vérité
– Daniel Vérité, Commented Oct 16, 2013 at 21:46

SpringLearner · Accepted Answer · 2013-10-16 10:33:14Z

2

Add correct schema name to callable statement and it shall work. Please refer to below code for example.

CallableStatement   cs = connection.prepareCall("{call yoursSchema.proc1()}");

edited Oct 16, 2013 at 10:33

SpringLearner

13.9k20 gold badges82 silver badges117 bronze badges

answered Oct 16, 2013 at 10:32

Dark Knight

8,3574 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stunner · Accepted Answer · 2013-10-17 12:54:30Z

0

Finally, I got the solution. The main problem was with JDBC Driver which I downloaded from the official website. I was using the postgresql driver. I dont know what's wrong with it but It seems like it is not supporting proedures. So I switched to EnterpriseDB(EDB) driver. Now the same program works fine and procedures are getting executed.

I just made these changes 1)Changing Driver 2)Changing Driver Class url from "org.postgresql.Driver" to "com.edb.Driver" 3)Dabase url "jdbc:postgresql://host:port/db to "jdbc:edb://host:port/db"

That's all. Now the procedures works too.

edited Oct 17, 2013 at 12:54

answered Oct 17, 2013 at 12:44

Stunner

1,2114 gold badges25 silver badges49 bronze badges

1 Comment

Lukas Eder Over a year ago

While this may have circumvented your problem, this answer is not really correct. The "standard" JDBC driver for PostgreSQL supports procedures perfectly well. In jOOQ, we've been running integration tests for PostgreSQL for ages now. It's probably due to your "procedure" having been created through PostgresPlus, I believe? It would be great to have an updated answer if you still know what the real problem was.

Sylas Wu · Accepted Answer · 2025-02-13 09:15:00Z

0

I encounterd the same problems.

I use spring and ibatis to map the sql, when i call procedure in PostgreSQL, it always say that "no function matches the given name and argument types. you might need to add explicit type casts."

I feel so confused, cause I always use {call "schema"."procedure name"(?,?,?)} to interact SQL. Why does output say no "function" brabrara.

finally, I realized the problems occur in JDBC driver , the driver may invoke the old calling mode in bottom level.

So you may try this ：

find your applicationContent
find the datasource bean in applicationContent
add ?escapeSyntaxCallMode=call behind datasource's url.

it may solve your problem.

Sorry, English is not my native tongue. so it will be a little messy.

answered Feb 13 at 9:15

Sylas Wu

1

1 Comment

Sylas Wu Feb 13 at 9:18

oh, if you worry if it influence your funciton in postgreSQL , you can use "?escapeSyntaxCallMode=callIfNoReturn" also !

Collectives™ on Stack Overflow

Postgresql Procedure not getting called from JDBC

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related