How to call stored procedure with multiple values in Java?

Question

I need to call a SQL Server stored procedure from Java. This is the stored procedure:

 DECLARE @t_certificate v.vt_VidaDollarsCertificates
 INSERT INTO @t_certificate VALUES(1,710335,'FFRLODOLAR',363)
 INSERT INTO @t_certificate VALUES(1,710335,'FFRLODOLAR05',363)
 INSERT INTO @t_certificate VALUES(1,710335,'FFRLODOLAR06',363)
 EXEC v.vprdl_CertificateInsert @t_certificate

What I do is to create a raw query and then with a PreparedStatement add the information.

I create the raw query like this:

StringBuilder query = new StringBuilder();
query.append(
        "DECLARE @t_certificate v.vt_VidaDollarsCertificates;"
        + "INSERT @t_certificate VALUES(?,?,?,?)");
for (int i = 0; i < arrCertificates.size() - 1; ++i) {
    query.append(",(?,?,?,?) ");
}
query.append("; EXEC v.vprdl_CertificateInsert @t_certificate");

This is the raw query that is formed

DECLARE @t_certificate v.vt_VidaDollarsCertificates;INSERT @t_certificate VALUES(?,?,?,?),(?,?,?,?) ,(?,?,?,?) ; EXEC v.vprdl_CertificateInsert @t_certificate

Then I create the PreparedStatement.

PreparedStatement preparedStmt = con.prepareStatement(query.toString());
for (int i = 0; i < arrCertificates.size(); ++i) {
    preparedStmt.setInt(1, arrCertificates.get(i).getCertificateTypeID());
    preparedStmt.setInt(2, arrCertificates.get(i).getContratoID());
    preparedStmt.setString(3, arrCertificates.get(i).getFolio());
    preparedStmt.setInt(4, Integer.parseInt(arrCertificates.get(i).getID()));

}

After that I use executeUpdate.

preparedStmt.executeUpdate();
ResultSet rs = preparedStmt.getGeneratedKeys();

But it is not working, it throws an exception

the value is not set for the parameter number

Youcef LAIDANI · Accepted Answer · 2017-06-23 16:27:08Z

2

It seems you set the wrong index to the prepapred statement because in all iteration you set the same values :

VALUES(?,?,?,?),(?,?,?,?) ,(?,?,?,?)
       1 2 3 4   1 2 3 4    1 2 3 4

it seems this is not correct, instead you can use :

int j = 1;
for (int i = 0; i < arrCertificates.size(); ++i) {
    preparedStmt.setInt(j++, arrCertificates.get(i).getCertificateTypeID());
    preparedStmt.setInt(j++, arrCertificates.get(i).getContratoID());
    preparedStmt.setString(j++, arrCertificates.get(i).getFolio());
    preparedStmt.setInt(j++, Integer.parseInt(arrCertificates.get(i).getID()));
}

So for 3 inputs it should gives you :

VALUES(?, ?, ?, ?),(?, ?, ?, ?) ,(?, ?, ?, ?)
       1  2  3  4   5  6  7  8    9  10 11 12

edited Jun 23, 2017 at 16:27

answered Jun 23, 2017 at 15:48

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

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

2 Comments

Youcef LAIDANI Over a year ago

you are welcome @mavi, you can make it with the new way, check my edit

mavi Over a year ago

Great! I like your solution

Collectives™ on Stack Overflow

How to call stored procedure with multiple values in Java?

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