PostgreSQL stored procedure not working

Question

I have created a stored procedure inside Postgres DBMS and a Java function which calls that procedure.

After running the Java function I received an error:

org.postgresql.util.PSQLException: L'indice de la colonne est hors limite : 1, nombre de colonnes : 0.
    at org.postgresql.core.v3.SimpleParameterList.registerOutParameter(SimpleParameterList.java:49)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.registerOutParameter(AbstractJdbc2Statement.java:1968)
    at org.postgresql.jdbc3.AbstractJdbc3Statement.registerOutParameter(AbstractJdbc3Statement.java:1511)
    at DAO.FournisseurDAO.getLastId(FournisseurDAO.java:192)
    at CONTROLLER.FournisseurController.getLastIdInDataBase(FournisseurController.java:96)
    at VIEW.FournisseurUi.spinnerProperties(FournisseurUi.java:183)
    at VIEW.FournisseurUi.componentsProperties(FournisseurUi.java:139)
    at VIEW.FournisseurUi.<init>(FournisseurUi.java:100)
    at VIEW.FournisseurUi$2.run(FournisseurUi.java:542)
    at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:744)
    at java.awt.EventQueue.access$400(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:697)
    at java.awt.EventQueue$3.run(EventQueue.java:691)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:714)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Here is the procedure code :

CREATE OR REPLACE FUNCTION getlastfournisseurid(OUT lastid integer)
  RETURNS integer AS
$BODY$
begin 
       select last_value into lastid from fournisseur_fournisseurid_seq ; 
end;
$BODY$
  LANGUAGE plpgsql;

Here is the function code:

public int getLastId() {
        String fournisseurLastId = "{ call getLastFournisseurId() }";
        int lastid = -1 ; 
        // Get Connection 
        Connection connecte = utility.DatabaseConnection.getInstance();

        try {
            // Create a  callableStatement
            CallableStatement clblStmt = connecte.prepareCall(fournisseurLastId);
            // Process query
            clblStmt.registerOutParameter(1, java.sql.Types.INTEGER);
            ResultSet result = clblStmt.executeQuery();
            result.next();
            lastid = clblStmt.getInt(1); 

            // close used resources             
            clblStmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return -1 ; 
        } finally {
            try {
                if (connecte != null) {
                    connecte.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            return lastid;
        }
    }

Did you try calling your stored procedure from an SQL command interface (psql or pgAdmin3 etc.) directly to see if it works? — RealSkeptic
– RealSkeptic, Commented Jan 14, 2015 at 20:26
yes it works perfectly from pgAdmin3 : select getlastfournisseurid() ; — abdou amer
– abdou amer, Commented Jan 14, 2015 at 20:27

RealSkeptic · Accepted Answer · 2015-01-14 23:00:23Z

2

You are supposed to communicate with a parameter to that function (in fact, its return value), but you defined your call as:

String fournisseurLastId = "{ call getLastFournisseurId() }";

You should instead define it as:

String fournisseurLastId = "{ ? = call getLastFournisseurId() }";

Otherwise, JDBC cannot register the first placeholder as an out parameter - there is no placeholder there for it to register.

edited Jan 14, 2015 at 23:00

answered Jan 14, 2015 at 20:34

RealSkeptic

34.8k7 gold badges56 silver badges83 bronze badges

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

3 Comments

Erwin Brandstetter Over a year ago

An OUT parameter is not supposed to be passed to a plpgsql function. It's returned instead.

RealSkeptic Over a year ago

@ErwinBrandstetter Right. Changed the syntax.

Erwin Brandstetter Over a year ago

Not sure about the JDBC / Java side, but the Postgres side is fixed now. You may want to clarify your first sentence, too.

score 2 · Accepted Answer · 2015-01-14 20:40:04Z

2

Postgres functions are just that: functions. There is no need to use JDBC's CallableStatement which was designed for real stored procedures.

Your function can also be simplified. There is no need for a PL/pgSQL function. A simple SQL function will do:

CREATE OR REPLACE FUNCTION getlastfournisseurid()
  RETURNS integer AS
$BODY$
    select lastvalue from fournisseur_fournisseurid_seq; 
$BODY$
  LANGUAGE sql;

(This assumes that there is never more than one row in the table fournisseur_fournisseurid_seq, if there is, you need to add a limit 1 to the select statement)

Then call it like this:

Statement stmt = connecte.createStatement();
ResultSet rs = stmt.executeQuery("select getlastfournisseurid()");
rs.next();
lastid = rs.getInt(1);

edited Jan 14, 2015 at 20:40

answered Jan 14, 2015 at 20:34

user330315

Collectives™ on Stack Overflow

PostgreSQL stored procedure not working

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related