Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: stored function help???

    Hey all,

    Im having problems with my insert stored function when called from my java c
    ode (works fine when called in pgsql).

    Very new to this, and just cant seem to figure out what the problem is...

    here is an example of my stored function:

    CREATE OR REPLACE FUNCTION test (integer,integer) RETURNS INTEGER As $$

    DECLARE

    rid ALIAS FOR $1;
    rval ALIAS FOR $2;
    results INTEGER;
    BEGIN

    INSERT INTO test (col, col2) VALUES (rid, rval);
    GET DIAGNOSTICS results := ROW_COUNT;
    IF results = 0 THEN
    RAISE EXCEPTION 'Error adding additional record.';
    RETURN 0;
    END IF;
    RETURN rid;
    END;
    $$ language 'plpgsql';



    here is an example of my java code:

    private boolean insertData (String function)
    {
    CallableStatement stmt = null;
    Connection con = null;

    try {
    con = d.getConnection (u.getUserName(),u.getUserPassword());
    con.setAutoCommit (false);
    stmt = con.prepareCall (" { ? = call " + function + "(?,?) }");

    stmt.setInt(2,25);
    stmt.setInt(3,50);

    stmt. registerOutParameter
    (1,Types.INTEGER);
    stmt.execute();
    Integer x=stmt.getInt(1);

    System.out.println (x);

    stmt.close();
    con.close ();

    }
    catch (SQLException e)
    {
    System.out.println(e);

    }
    return true;
    }


    When function is called via the java code it returns the rid, but does not i
    nsert the row into the table....

    any ideas what the problem is???

    i dont have any problems with functions that return values etc, just when th
    ere is in parameters involved like in the above example.

    Any help would be truly appreicated!!!

    Thanks

    ev.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    You turned off auto commit for your connection object.
    Code:
    con.setAutoCommit(false);
    You need to call con.commit(); for the statements to be processed. Make sure this is what you actually want to be doing. AutoCommit should usually be on, unless you are trying to run a transaction.

    http://java.sun.com/j2se/1.5.0/docs/...Commit(boolean)

  3. #3
    Join Date
    May 2005
    Posts
    25
    Thanks Jfulton!

    Haven't tried it - but not commiting is def the problem - seems so obvious!!! ...I feel so silly for not spotting it!!!

    AutoCommit does need to be turned off - for the code that will actually be implemented - below was just a short example (or tried to be).....

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •