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.

  • What is the Select statement to return the column names in a table
  • Unable to connect to the report server 2008 r2
  • How to get the six week sunday count using the Weekno in SQL Server 2008?
  • Execute multiple SQL commands in one round trip
  • Using variables for asc and desc in order by
  • SQL Server Full text search error
  • 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

  • Problems with Date Java8 Hibernate5.2
  • No Dialect mapping for JDBC type: -9 with Hibernate 4 and SQL Server 2012
  • com.microsoft.sqlserver.jdbc.SQLServerException: The “variant” data type is not supported
  • stored procedure returns just one row of multiple rows
  • Jdbc Connection Pool with Sql Server 2008 fails
  • Problem using Hibernate and SQL Server 2008
  • 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.