pass java integer array as arguments to postgres procedure

Question

Defined java integer array as below,

Integer[] details = {5097, 5098, 5099, 5100, 5101, 5102, 5121, 5122, 5123, 5124, 5125, 5126, 5127, 5128, 5129, 5130};

Then converted java array to postgres type array using connection object as below,

connection = (Connection) DriverManager.getConnection(MySQLURL,"postgres","admin");
Array dArray = connection.createArrayOf("INT", details);

After that passed dArray as argument to postgres procedure as below,

String simpleProc = "{ call SMS.P_DELETE_DATA("+dArray+") }";
        CallableStatement cs = connection.prepareCall(simpleProc);
        cs.execute();

I wrote postgres procedure signature as below,

CREATE FUNCTION SMS.P_DELETE_WORKPLAN_FOR_LESSON(P_LESSONS INT[]) 
 RETURNS VOID AS $$

But when i run the java code it throws Malformed procedure / function call ......

Some one advice me to sort out this issue.

Thanks in advance.

k_rams · Accepted Answer · 2017-05-20 05:18:46Z

2

Hełm, you should set the Parameter for Sql Statement with a Method and not in the sql String. Try something like this:

        connection = (Connection) DriverManager.getConnection(MySQLURL,"postgres","admin");
        Array dArray = connection.createArrayOf("INT", details);
        String simpleProc = "{ call SMS.P_DELETE_DATA(?) }";
        CallableStatement cs = connection.prepareCall(simpleProc);
        cs.setArray(1, dArray);
        cs.execute();
        cs.close();

answered May 20, 2017 at 5:18

k_rams

767 bronze badges

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

Collectives™ on Stack Overflow

pass java integer array as arguments to postgres procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related