How to call a pre configured postgres function when using jdbc?

Question

I am trying to call a pre configured function using java code. This is the code i am using:

Class.forName("org.postgresql.Driver");
c = DriverManager.getConnection("jdbc:postgresql://" + ip + ":5432/" + db,
                            username, password);
c.setAutoCommit(false);
stmt = c.createStatement();
stmt.executeQuery( "SELECT public.myFunc ("1","2","3"));

stmt.close();
c.close();

The function itself has some validation and in the end an INSERT command. When I run the query from pgAdmin, I can see that the row is added, but when I run the same query as written above, nothing happens.

Also tried replacing the executeQuery with execute and executeUpdate.

Youcef LAIDANI · Accepted Answer · 2018-07-24 08:32:19Z

3

You can read the documentation about the right way of Calling Stored Functions, in your case you can use :

CallableStatement callStmt = c.prepareCall("{call myFunc(?, ?, ?)}");
callStmt.setString(1, "1");
callStmt.setString(2, "2");
callStmt.setString(3, "3");
callStmt.execute();
callStmt.close();

Important about your code

If you mean to use Integer 1 instead of String "1" you have to change callStmt.setString to callStmt.setInt
The default schema of postgres is public so you don't need to mention it, you can mention it if you use another schema
The quote in Java " should be escaped by \"
Don't concatenate attributes with your query, this can cause Syntax error and SQL Injection

answered Jul 24, 2018 at 8:32

Youcef LAIDANI

60.2k21 gold badges111 silver badges178 bronze badges

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

3 Comments

MarkZvu Over a year ago

Thanks for the quick answer, I have applied your solution but the result is the same, I run the query successfully but the change is not reflected in the DB.

Youcef LAIDANI Over a year ago

Did you try it with setAutoCommit or without it, it seems you dont commit your statement

MarkZvu Over a year ago

THANKS!! that was the issue

Collectives™ on Stack Overflow

How to call a pre configured postgres function when using jdbc?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related