How to pass an array object from jdbc code

Question

I have a stored procedure get_data(estargs set(char(1000) not null)) in the informix 11.5 database. I have to use this stored procedure in order to get a value from the database.

I tried using this way but it fails:

conn = dataSource.getConnection();
            String [] arrayObj={"and code = 'Value1'","and lmt= 10000.000"};
            CallableStatement test=conn.prepareCall("{call get_data(?)}");
            test.setObject(1, arrayObj);
            test.execute();
            ResultSet testrs = test.getResultSet();
            while (testrs.next()) {
                int data = testrs.getInt(1);
                System.out.println(data);

            }

This is not working. What do you think I am doing wrong?

Community · Accepted Answer · 2017-05-23 11:47:37Z

2

That's not possible. Replace

conn.prepareCall("{call get_data(?)}");

by

conn.prepareCall("{call get_data(?, ?)}");

and replace

test.setObject(1, arrayObj);

by

test.setObject(1, arrayObj[0]);
test.setObject(2, arrayObj[1]);

Related question:

How to set multiple values in IN clause?

Update: make it all more "dynamically", you'd like to generate and populate the placeholders yourself with help of the following two utility methods:

public static String preparePlaceHolders(int length) {
    StringBuilder builder = new StringBuilder(length * 2 - 1);
    for (int i = 0; i < length; i++) {
        if (i > 0) builder.append(',');
        builder.append('?');
    }
    return builder.toString();
}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

which can be used as follows:

private static final String SQL_CALL_GET_DATA = "{call get_data(%s)}";

// ...
String sql = String.format(SQL_CALL_GET_DATA, preparePlaceholders(arrayObj.length));
statement = connection.prepareCall(sql);
setValues(statement, arrayObj);
// ...

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Jul 14, 2010 at 17:48

BalusC

1.1m377 gold badges3.7k silver badges3.6k bronze badges

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

2 Comments

Sameer Malhotra Over a year ago

I cannot do that because I would know only at run time how many arguments I am passing in the stored procedure.

BalusC Over a year ago

Did you follow the "Related question" link? It contains an example how to generate ?,?,? etc and populate it dynamically.

Georgy Bolyuba · Accepted Answer · 2010-07-14 17:43:34Z

0

Have you tried using java.sql.Array?

answered Jul 14, 2010 at 17:43

Georgy Bolyuba

8,5717 gold badges31 silver badges39 bronze badges

1 Comment

Sameer Malhotra Over a year ago

I saw it but I dont know how to convert Java Array to java.sql.Array.

Collectives™ on Stack Overflow

How to pass an array object from jdbc code

2 Answers 2

Related question:

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Related question:

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related