CODEWITHSUNDEEP
javapostgresql
Santhucool
Santhucool

Reputation: 1706

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

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?

Upvotes: 2

Views: 5747

Answers (1)

Alexandros
Alexandros

Reputation: 2200

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.

Upvotes: 2

Related Questions