How to call stored procedure with multiple values in Java?

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.

  • Error when changing datatype of column in SQL Server DB - Java
  • How to use UNION while inserting in a table using SELECT clause from many tables in SQL Server 2000
  • rowversion in SQL Azure
  • How can I write a query that returns “between” to return alphabetic data, not just numeric?
  • why? connection reset by jdbc (ms sqlserver)
  • MS Access Queries Conversion to Sql Server
  • 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

  • Crazy MS SQL Server JDBC driver at datetime field or time zone issue?
  • SQOOP SQLSERVER Failed to load driver “ appropriate connection manager is not being set”
  • java code make a stored procedure return a resultset in DAO layer
  • Database call stuck at java.net.SocketInputStream.socketRead0(Native Method)
  • SQLException during executeBatch() when I'm handling BatchUpdateException
  • How to write same value into 2 Databases?
  • One Solution collect form web for “How to call stored procedure with multiple values in Java?”

    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 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.