java postgresql- No function matches the given name and argument types. You might need to add explicit type casts

Question

I have a stored procedure as follows in postgresql:

CREATE OR REPLACE FUNCTION insert_orderline(order_id integer, prod_id integer, prodname text, rate numeric, ordered_qty numeric, dispatched_qty numeric, balance_qty numeric, photonum text, remarks text, create_station_id integer, create_stationtype text, create_time text, create_user_id integer, tran_time text, tran_user_id integer)
  RETURNS void AS
$BODY$
   INSERT INTO bakingfactory.orderline
     (orderline_id, order_id, prod_id, prodname, rate, ordered_qty, 
       dispatched_qty, balance_qty, photonum, remarks, create_station_id, 
       create_stationtype, create_time, create_user_id, tran_time, tran_user_id)
   values 
     (default,order_id, prod_id, prodname, rate, ordered_qty, 
       dispatched_qty, balance_qty, photonum, remarks, create_station_id, 
       create_stationtype, create_time, create_user_id, tran_time, tran_user_id) 
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION insert_orderline(integer, integer, text, numeric, numeric, numeric, numeric, text, text, integer, text, text, integer, text, integer)
  OWNER TO postgres;

I am trying to insert it using java as follows:

CallableStatement cstorderline = conn.prepareCall("{call insert_orderline(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                            cstorderline.setInt(1, lastinsertid);
                            cstorderline.setInt(2, prodid);
                            cstorderline.setString(3, proname);
                            cstorderline.setDouble(4, rate);
                            cstorderline.setDouble(5, ordered_qty);
                            cstorderline.setDouble(6, dispatched_qty);
                            cstorderline.setDouble(7, balanced_qty);
                            cstorderline.setString(8, photonum);
                            cstorderline.setString(9, notes);
                            cstorderline.setInt(10, create_station_id);
                            cstorderline.setString(11, create_station_type);
                            cstorderline.setString(12, timewithmilsec);
                            cstorderline.setInt(13, create_user_id);
                            cstorderline.setString(14, timewithmilsec);
                            cstorderline.setInt(15, trans_user_id); 
                            cstorderline.executeUpdate();

I am getting an error as follows:

org.postgresql.util.PSQLException: ERROR: function bakingfactory.insert_orderline(integer, integer, character varying, double precision, double precision, double precision, double precision, character varying, character varying, integer, character varying, character varying, integer, character varying, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 15

I have type numeric in my database with precision values. For type numeric I am simply using double. Is that the right way?

For which value are you getting an error, and you can insert double for numeric. But why are you inserting values this way? Looks very buggy. — We are Borg
– We are Borg, Commented Oct 19, 2015 at 15:51

Alexandros · Accepted Answer · 2015-10-19 17:11:20Z

2

There are multiple explanations for your error.

First explanation

You have created a function insert_orderline but the Java code expects that this function in included in the bakingfactory schema (Probably you have setup bakingfactory as the default schema of the connected user of the Java app).

So, create the function as:

CREATE OR REPLACE FUNCTION bakingfactory.insert_orderline

instead of

CREATE OR REPLACE FUNCTION insert_orderline

Second explanation

You should use double precision in your function definition instead of numeric data types.

edited Oct 19, 2015 at 17:11

answered Oct 19, 2015 at 17:06

Alexandros

2,2004 gold badges27 silver badges57 bronze badges

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

2 Comments

Christian Over a year ago

I bet $5 the reason is the first one! :)

Santhucool Over a year ago

@Alexandros changed as per your suggestions the error still persists. The error code specifies Position: 15 is that a clue?

Collectives™ on Stack Overflow

java postgresql- No function matches the given name and argument types. You might need to add explicit type casts

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related